Recovery is writing a checkpoint in database

Question: In daily scans of the SQL Server message logs there is an informational message Recovery is writing a checkpoint in database. What does this mean? Answer: There is normally an added text "No user action is required" - meaning the DBA doesn't need to take any action. But it's useful to understand the checkpoint process. The Checkpoint process writes sql dirty pages (in memory) and transaction logs to the physical disk. Checkpoints are useful as they assist in decreasing the recovery time in a system outage. The checkpoint process is regularly occuring . Different events can trigger a database... Read more →

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 →

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 →

How to calculate last time Wait Statistics were cleared

Question: I’m trying to figure out how to calculate when SQL Server wait statistics were last cleared. ? My goal is to try and understand performance patterns on some SQL Servers. I have some automated processes to gather wait statistics , but it’s not always clear if any other DBAs are running the DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR) command Is there a dependable way to produce this timing estimate? Answer: An effective method to estimate the last time wait statistics was cleared is to use the SQLTRACE_INCREMENTAL_FLUSH_SLEEP wait type. When you execute this sql statement you’ll get back some details.One of... Read more →

Wait Stats

Backupthread,latch_ex,pageiolatch,cxpacket ,lck_m_is,logmgr Read more →