Category: Uncategorized

Frequently Asked Questions

Q: Why is the same query sometimes fast and sometimes slow, depending on where I run it?

It’s most likely parameter sniffing.

Q: What should you do when your index maintenance jobs take forever?

When you rebuild a 50GB table’s indexes, you’re basically reloading that table from scratch. SQL server has to make a brand-new copy of the table on new data pages, and it logs all this stuff in the transaction log – which means your backups take longer and your Availability Group gets way far behind.

If you’ve been rebuilding indexes nightly, consider easing that off to weekends instead. If you’re worried that will affect performance, you’re probably mixing up the difference between rebuilding indexes and updating statistics. Consider doing daily stats update jobs rather than rebuilding your indexes.

In general, statistics updates are way better than daily index rebuilds.

Q: Which cloud provider should you use to host SQL server?

  • If you run SQL Server in a VM, you can get m ore VM selection & performance at AWS
  • If you want to rent SQL server as a service, Microsoft’s Azure SQL DB Managed instances are quite a bit ahead of Amazon RDS

Find outdated statistics

How often do you update your statistics?

Here is my general advise: https://vinayrahulare.wordpress.com/2020/12/08/database-maintenance-best-practices/

Everyone’s criteria is different for how often you want to update the statistics.

If you like to find outdated statistics. Here is an useful script which shows you the last time your statistics were updated and the number of modifications made on a column. Based on these two parameters you can decide if you would like to update the statistics for a particular table.

SELECT DISTINCT
OBJECT_NAME(s.[object_id]) AS TableName,
c.name AS ColumnName,
s.name AS StatName,
STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated,
DATEDIFF(d,STATS_DATE(s.[object_id], s.stats_id),getdate()) DaysOld,
dsp.modification_counter,
s.auto_created,
s.user_created,
s.no_recompute,
s.[object_id],
s.stats_id,
sc.stats_column_id,
sc.column_id
FROM sys.stats s
JOIN sys.stats_columns sc
ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id
JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
JOIN sys.partitions par ON par.[object_id] = s.[object_id]
JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
CROSS APPLY sys.dm_db_stats_properties(sc.[object_id], s.stats_id) AS dsp
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND (s.auto_created = 1 OR s.user_created = 1)
ORDER BY DaysOld;