Benefits of partitioning a CCI:
Query performance can be improved because a minimum level of rowgroup elimination is guaranteed, despite how the data gets loaded or modified. Most of the generic SQL Server partitioning guidance out there doesn't take this into account.
Improved flexibility with...
@ErikDarling repro needed. generally though, tables accessed in the indexed view maintenance portion of the plan use serializable. this may not always result in range locks, depending on the definition of the view.
@PaulWhite It's not documented that I've been able to find.
I think I was able to turn it on (ALTER DATABASE SCOPED CONFIGURATION SET ACCELERATED_PLAN_FORCING = ON;), confirmed by the value in sys.database_scoped_configurations changing.
I tried the setup from Forrest's blog, the query takes about 4 seconds to compile if I force the plan in query store.
I was hoping that would drop significantly when I turned on ACCELERATED_PLAN_FORCING, but it didn't have any effect on the compile time.
@JoshDarnell That looks like your plan qualified for the new behaviour, avoiding full optimization. You should compare that with compilation time with APF off.
@PaulWhite Cool, thanks! I personally don't need it, I tried to answer a question regarding stats & read only databases and wondered if it was possible
We have a database in primary DC around 15 TB with partitioned tables with largest one around 7 TB.
To avoid users from reporting a LS set up was done for above database in different DC where the LS restore job runs once nightly.
on Primay- update stats and update stats AYNC are both set to Tru...
actually I'm implying you should capture the callstack with an XE to find the right breakpoint, then step through the code in windbg to see if it does anything
Montana is possible, but not nearly warm enough for me. The lack of people is a positive note. Not sure on their income tax but that could be a deal breaker.
Both Dakotas are a Hard Pass.
Idaho is another possible for the same reasons as Montana
Basically I'm looking at states that have no income tax, low sales tax, and low property taxes.
SELECT *
FROM sys.dm_tran_active_transactions AS tat
JOIN sys.dm_tran_session_transactions AS tst
ON tst.transaction_id = tat.transaction_id
WHERE tat.name = 'implicit_transaction';
@JoeObbish that sounds really easy, maybe I'll drive the route
Only if I can make dad jokes the whole way
In other news, someone was all up in arms today about the terms "blacklisted and whitelisted" being racist.
I don't know how this person has a job, since apparently everything triggers them and they are offended at everything. That in of itself is a 24/7 job, to just walk around being offended and triggered by everything.
Benefits of partitioning a CCI:
Query performance can be improved because a minimum level of rowgroup elimination is guaranteed, despite how the data gets loaded or modified. Most of the generic SQL Server partitioning guidance out there doesn't take this into account.
Improved flexibility with...
We have a database in primary DC around 15 TB with partitioned tables with largest one around 7 TB.
To avoid users from reporting a LS set up was done for above database in different DC where the LS restore job runs once nightly.
on Primay- update stats and update stats AYNC are both set to Tru...
Reboot 3x? This typically works for all IT cases in our organization.
