Prove them wrong: Using Dynamic Management Views and Functions in SQL Server 2005

I will spend the next couple of blog posts looking at performance tuning and troubleshooting in SQL Server 2005, although I may insert a few posts about SQL Server 2008 and probably SharePoint 2007.

Having to work in an organization that provides enterprise hosting services, I regularly deal with customers who blame our infrastructure whenever their application runs slow. Having just upgraded some servers on a new hardware and on a new platform - from Windows 2000/SQL Server 2000 to Windows Server 2003/SQL Server 2005 - I have to prepare a few bullets to shoot to our customers if they start complaining about performance issues. My take on this is that no amount of hardware upgrade is more than enough to compensate for poorly written application (scripts included). Let me start by introducing SQL Server 2005's dynamic management views (DMV) and functions (DMF). DMVs (not the Department of Motor vehicles) return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. In SQL Server 2000, you will have to use a ton of tools just to get these information. Not so with SQL Server 2005. Let me start by looking at the sys.dm_os_schedulers view. Note that DMVs belong to the sys schema and have dm_ as their prefix. This view returns one row per scheduler in SQL Server. Each scheduler is mapped to an individual processor in SQL Server (in multi-core, hyper-threaded processors, this count is on a per-core/thread basis). You can use this view to monitor the condition of a scheduler or to identify runaway tasks. When do you use this view? You use it when you feel that the CPU is where your bottleneck happens. To illustrate, let's take a look at a sample script below

SELECT scheduler_id, current_tasks_count, runnable_tasks_count, active_workers_count, work_queue_count, current_workers_count, is_idle, pending_disk_io_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255

Some pointers about the script - since the scheduler is represented by the number of processors in your server, you should technically have the same number for the same number of processors. On an 8-processor, dual-core server, you will have 16 schedulers. The reason we limit our query to those with scheduler_id value less than 255 is that anything IDs greater than or equal to 255 are used internally by SQL Server, such as the dedicated administrator connection scheduler. How can we use the information returned by this query? The number of runnable tasks is generally a non-zero value; a nonzero value indicates that tasks have to wait for their time slice to run. Imagine having to wait for your turn just to get some ice cream. Performance wise, the ice cream vendor (your scheduler, in this case) is the bottleneck. With runnable_tasks_count returning 0, there are no actively running tasks. However, active sessions may exist like probably a dedicated administrator connection. High values for this counter are a symptom of a CPU bottleneck. The "high value" term here is probably relative as some say "high value" means greater than 2 or greater than 10. Whatever the values may be, a recommendation would be to come up with a baseline as there are different baseline values for different systems. There are other information you can get out of this DMV. For example, you can look at the pending_disk_io_count column to determine if you have a disk IO problem. Similar to the runnable_tasks_count column, the higher the value for this, the more likely that your IO is your bottleneck. A whitepaper to troubleshoot performance issues in SQL Server 2005 is available from TechNet and provides a better insight. We'll look at the other DMVs and DMFs* in future posts

*Note: DMFs are also the former acronym for SQL Server 2008's Declarative Management Framework. To minimize confusion, they now call DMFs as Policy Based Management in SQL Server 2008

Diagnose Authentication and Authorization issues in IIS 5/6 using AuthDiag 1.0

I was stuck with authorization issues with a web application trying to call COM+ applications in IIS. But when the security hops go beyond 2 levels of access, it becomes more difficult to find the root cause. Good thing I found this tool from Microsoft called AuthDiag 1.0. This tool is designed to aid customers in effectively troubleshooting and determining the root cause of the problem. It works with IIS 5.x and 6.0 (sad to say it doesn't work with IIS 4.0 which runs on top of Windows NT 4.0). It will analyze metabase configuration and system-wide policies and warn users of possible points of failure and guide them to resolving the problem. AuthDiag 1.0 also includes a robust monitoring tool called AuthMon designed at capturing a snapshot of the problem while it occurs in real-time. AuthMon is robust and specially designed for IIS servers removing any information not pertinent to the authentication or authorization process.

To know more about this tool, you can download a Microsoft webcast that focuses on how to use this tool