Skip to main content
7 votes
Accepted

Why does adding an empty columnstore index to my tables consistently make execution plans ignore bitmaps?

how can I prevent the introduction of batch mode removing excellent bitmaps? Short answer You can't. You'll need to upgrade from Standard Edition. Long answer They might both be called bitmaps, but ...
Paul White's user avatar
  • 86.9k
6 votes
Accepted

Best Way to Determine if a Table has a Long Running Transaction

The best way to test if a lock can be acquired is to try to acquire it. Use LOCK_TIMEOUT to give up after waiting a specified time, or immediately if a conflicting lock is encountered, as you prefer. ...
Paul White's user avatar
  • 86.9k
6 votes
Accepted

Is sys.dm_os_memory_clerks a good source to use when setting max server memory (MB)?

bad news Yeah, dm_os_memory_clerks is a pretty bad single-source to figure out what max server memory should be set to. It just shows you what's currently consuming memory, which can shift and bob and ...
Erik Darling's user avatar
4 votes
Accepted

Does it make sense to Full Back up weekly or Back up Transaction Log every 15 min?

Which option makes more sense / easier? First question is what problem you are going to solve? How are you going to use your backup? Are you setting log shipping to other party? Do you need point in ...
SergeyA's user avatar
  • 831
3 votes

Does it make sense to Full Back up weekly or Back up Transaction Log every 15 min?

Yes. Refer to How to Configure Ola Hallengren’s Database Maintenance Scripts for Backups To summarize: Run Maintenance Solution.sql Configure the Agent Job(s) with the correct database name(s) & ...
Peter Vandivier's user avatar
2 votes

Does it make sense to Full Back up weekly or Back up Transaction Log every 15 min?

First thing you need to consider is your tolerance to data loss in case of a disaster. RTOs/RPOs define your backup strategy and not the overhead of management. For example : If you have a server ...
Ramakant Dadhichi's user avatar
2 votes
Accepted

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS fails when run on Secondary replica

I´ve had an Ticket filed to Microsoft, because we were running into this "issue" in our production environment. I hope this clarifies it for anyone, because the documentation were not that ...
TheLowox's user avatar
2 votes

Best Way to Determine if a Table has a Long Running Transaction

You could in theory check sys.dm_tran_locks to see if there is an existing lock, and otherwise wait and loop again. DECLARE @startLoop datetime2 = SYSUTCDATETIME(); WHILE 1=1 BEGIN IF NOT EXISTS (...
Charlieface's user avatar
  • 13.6k
2 votes

Are unique filtered indexes considered an antipattern for enforcing constraints?

They are not quite considered an anti-pattern, but they are to some extent a denormalization. Under very strict 6th Normal Form rules, you should really take out that bit column and instead make a ...
Charlieface's user avatar
  • 13.6k
1 vote

Does SQL Server application database and tempdb collation mismatch affect performance

I believe the more likely potential problem you could run into would be a collation mismatch error. That would occur if the ERP software executes a query that does something like a JOIN or ...
Solomon Rutzky's user avatar
1 vote

import extended properties of sql server database from xls file

The TSQL script way of adding an Extended property is via sp_addextendedproperty Slightly altered example from the documentation: USE AdventureWorks2022; GO EXEC sp_addextendedproperty @name = N'...
Zikato's user avatar
  • 5,102
1 vote
Accepted

Backups/CHECKDB errors - "Cannot find server certificate"

The way that rotating DEK protecting certificates work and the way TDE works requires that the old certificate be available for some time after the change, unless you've properly planned log ...
Sean Gallardy's user avatar
1 vote
Accepted

"Long asynchronous API Call" After Sql Server Update

MSFT has added it as a feature which moreover is an issue now. They are well aware of this and will most likely fix this in next CU as mentioned in their website "Known issues in this update ...
user2325606's user avatar
1 vote

"Long asynchronous API Call" After Sql Server Update

This is a known issue with the Patch CU27. See: https://learn.microsoft.com/en-us/troubleshoot/sql/releases/sqlserver-2019/cumulativeupdate27#2901635
Jan Olbrecht's user avatar
1 vote
Accepted

When would a columnstore index be unsuitable for an OLAP environment with large and wide tables?

Suppose that you retrieve most of the rows and most of the columns. Are there any circumstances at all where the above conditions apply and a traditional rowstore index would be preferable to a ...
J.D.'s user avatar
  • 38.3k
1 vote

Why is SQLPackage suddenly including every scalar function in my SSDT project for ALTER in my deployment?

The Data Service Provider - - in the SqlProj file had been changed from Sql140 to Sql150. I reverted that change and the noise disappeared. The question of how to properly move the provider forward ...
John Hurtubise's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible