Performance By Design

This is the 2nd post in a series on SQL Server performance monitoring, emphasizing the use of key Dynamic Management View. The series starts here : OS Waits  The consensus among SQL Server performance experts is that the best place to start looking for performance problems is the OS Wait stats from the sys.dm_os_wait_stats DMV. Whenever it is running, the SQL Server database Engine dispatches worker threads from a queue of ready tasks that it services in a round-robin fashion. (There is evidently some ordering of the queue based on priority –background tasks with lower priority that defer to foreground tasks with higher priority.) The engine records the specific wait reason for each task waiting for service in the queue and also accumulates the Wait Time (in milliseconds) for each Wait reason. These Waits and Wait Time statistics accumulate at the database level and reported via the sys.dm_os_wait_stats DMV. Issuing a Query like the following on one of my SQL Server test...

Introduction. This series of blog posts explores a new topic – something that arose in the course of working on a recent project, namely a collection agent to gather performance counters from several key  SQL Server Dynamic Management views  (DMVs). The new project, which we plan to make available as Open Source, connects to an instance of SQL Server, queries key DMVs on a systematic basis, and then turns that data into Windows performance counters. Having the DMV counter data re-formatted as Windows performance counters then allows me to use the full set of tools my company has developed over the years for gathering, analyzing and reporting on these counters. Being primarily a developer and not a Database Administrator (DBA), I knew the DMV performance data existed, but I wasn’t familiar with its uses. As I begin to understand its value, I see the potential to use this counter data to help DBAs address issues in physical database design that play a pivotal role in applic...