Most important SQL Server wait types

Before I discuss the most important wait type list , I want to say that really there is no definitive list of most important sql wait types. Understanding what is normal and the context of the wait type is essential when discussing wait types. For example , you may see the sql wait type WRITELOG as high on the list of waits on a SQL Server. This may or may not be important depending on the "normal" behaviour. Benchmarking your systems is a great way of understanding the patterns your systems. Based on monitoring and analysing various SQL Server performance... Read more →

SELECT 1 FROM TABLE

Question: I'm working on an Enterprise application upgrade and have hit some problems. As part of the debugging process I'm inspecting the sql queries executing. The program is currently running SELECT 1 FROM TABLE. (replace TABLE with actual table name). What is the purpose of SELECT 1 FROM TABLE ? Answer: The SELECT 1 FROM TABLE technique is often used in certain products. The first question is what does this sql statement actually do? When you execute SELECT 1 FROM TABLE , there will be a Scan , because the purpose of SELECT 1 FROM TABLE is to return a... Read more →

Change Data Capture

There’s a number of things to consider in deciding on Change Data Capture (CDC) and deciding on the ideal use cases for CDC is very much dependant on the specific requirements. So it is important to understand in detail the project requirements. This should be weighed up against other solutions such as Triggers Some initial thoughts for discussion: For Auditing – CDC is an appropriate tool , as it can present the complete history of the data. Deciding on whether you’d rather use triggers will depend on reviewing the data in the change table and assessing whether it’s appropriate for... Read more →

UPDATE STATISTICS GETTING SLOWER

Question: The Statistics tasks in the SQL Server Maintenance plan is getting slower? When I first implemented the task it used to take 1 hr – recently it’s built up 4-5 hrs. I’ve checked underlying resource issues – and everything looks OK. Could you advise on how I can progress with troubleshooting. I’ve included a screeshot of Update Statistics Task. Answer: It is not unusual to find UPDATE STATISTICS getting slower over time. Looking at the way you have this configured All existing statistics – this includes all index and table statistics Sample size of 25 % It is very... Read more →

SQL Server , Meltdown and Spectre FAQ

I'm getting various questions about the Spectre and Meltdown impact on SQL Server. I'll start listing the most popular questions and the response. There is a lot of activity in progress across all the communities about mitigating the impact of Meltdown and Spectre . Typically there will be patching by different vendors and this needs to be managed properly. Where is the Microsoft SQL Server guideance documentation for Meltdown and Spectre ? The main link I reference is Protect SQL Server from attacks on Spectre and Meltdown side-channel vulnerabilities . The main advice is start planning for patching. There is... Read more →