Step 7: Signal Waits - CPU pressure

The DMV called sys.dm_os_wait_stats can also be used to determine how much time is spent swapping the different SQL jobs/threads between the CPUs i.e. if there is CPU pressure. Typically a value of over 15% - 20% tends to be viewed as the point at which you might need more CPUs or better scheduling of your SQL batch.

Having looked at the SQL Server version, the hardware, and the SQL Server from a server-level perspective, we’ll now look at database-specific settings. Often there are many databases on a given SQL Server. You will need to know which database is most active, and potentially the cause of the underlying problems. It may be that a very active database should be moved to its own server. It is right to examine all the databases on the server since a problematic database can have detrimental consequences for all the other databases on the server. Remember the CPUs, memory tempdb, and IO subsystem are resources that are shared by all the databases on the server.

Step 8: sys.databases

Having looked at the general server and hardware details, we’ll take a general look at the databases on the server. The catalog view called sys.databases is used to obtain the general database information.

The script will show you:

• Name of database
• Compatibility level
• Recovery model
• Page verify option
• Is read committed snapshot on

Firstly, the output shows you what databases exist on the SQL Server, these will be sharing the same CPUs, memory, tempdb, and IO subsystem.

The compatibility level tells you what version of SQL Server the database runs as. When you upgrade your version of SQL Server, you need to ensure the compatibility level reflects the upgraded version, unless you have reasons for running as a previous version. I have seen databases running unknowingly as SQL Server 2005 on a 2008 server.

The recovery model tells you about how backups and restores are handled. Typically on production system it is set to FULL or BULK_LOGGED, on non-production systems SIMPLE mode is often used.

The page verify option tells you how internal database corruption problems are handled. You typically want this value set to CHECKSUM, this ensures problems are reported sooner – rather than continue using a corrupt database.

The ‘is read committed snapshot on’ value shows you how blocking and concurrency are handled. There has been a move to enable this value, since this tends to reduce blocking (and deadlocks), at the expense of greater use of tempdb space.

Step 9: CPU usage by database

Examining the DMV called sys.dm_exec_query_stats allows you to determine how much CPU each database has used, thus identifying the activity/importance of each database. This will allow us to drill down further on the important databases.

Step 10: Memory usage by database

Similarly, examining the DMV called sys.dm_os_buffer_descriptors allows us to determine how much memory each database is currently using, thus identifying the activity/importance of each database. This too will allow us to drill down further on the important databases.

So far we’ve looked at the version of SQL Server on the box (and service pack), gathered details of the hardware (CPUs, amount of memory), important common configuration settings, the reasons why SQL queries are waiting, identified CPU pressure, identified CPU and memory usage per database. That’s some pretty impressive information. In the next article I’ll dive a bit deeper and further.

You can download the code for this program from the CodeBin (note you have to register first).

Ian Stirk is the author of SQL Server DMVS in Action (see side panel, and our review)  and  the following articles:

• The First Things I Look At On A SQL Server – Part 1
• The First Things I Look At On A SQL Server – Part 2

• Improve SQL Performance – Know Your Statistics
• Improve SQL performance – An Intelligent Update Statistics Utility
• Identifying your slowest SQL queries
• Improve SQL performance – find your missing indexes
• A Generic SQL Performance Test Harness
• A Generic SQL Server Compression Utility

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info

To be informed about new articles on I Programmer, subscribe to the RSS feed, follow us on Google+, Twitter, Linkedin or Facebook or sign up for our weekly newsletter.

<ASIN:1935182730>