AUTO_UPDATE_STATISTICS_ASYNC is the setting that cleared a new wait type, WAIT_ON_SYNC_STATISTICS_REFRESH, for my client. With auto update statistics on and stale statistics, the query halts while the statistics update. Switching the update to asynchronous removed the wait.
Find All Columns With a Specific Name in SQL Server
To find all columns with a specific name, query sys.columns and join sys.types for the data type. This post lists the matches with their schema, leaves out system objects, and then uses the same catalog to find names that appear with different data types.
How to Recompile Stored Procedure? – Interview Question of the Week #260
Question: How to Recompile Stored Procedure?
Answer: The answer to this question is here:
EXEC sp_recompile ‘NameofSP’
Let us read more about it.
SQL SERVER – Temp Table vs Table Variable – Cardinality Estimation
I had a very interesting conversation with one of my clients about Cardinality Estimation for Temp Table vs Table Variable. Let us discuss it today.
SQL Agent Error 7000: The System Cannot Find the File Specified
SQL Agent error 7000 (event ID 7000) stopped the SQL Server Agent service from starting after an operating system restart. The service’s path to its executable pointed to the wrong drive. Correcting the ImagePath value in the registry let the service start.
Error 15141: Upgrade Fails Because a Login Owns an Availability Group
Error 15141 means the server principal owns one or more availability groups and cannot be dropped. The msdb110_upgrade.sql script failed on a DROP LOGIN, and the script level upgrade for master failed with it. Changing the group owner to sa let the upgrade finish.
What is TempDB Spill in SQL Server? – Interview Question of the Week #259
When SQL Server uses TempDB when any query does not have enough memory to do its operation, it is called TempDB Spill. Let’s learn today.