Context switching can be hard to understand and measure.  As it turns out, you can use the PerfMon counter System >> Context switches/sec to count all of the switches occurring in and managed by SQLOS, regardless of the application(s) where it originated.  Note that this counter tracks all context switches UNLESS you’re using lightweight pooling, in which case some context switching may not be counted.

Here’s another couple quick PerfMon tips.  Still like to look at Buffer Cache Hit ratio and expect it to stay high for most applications (> 90%), but I know in my heart it’s largely a waste of time.  Some really strong combination of counters to monitor as an indication of poor query performance include low Page Life Expectancy (less than the default of 300), a very high number of key Locks, and long Average Wait Time for relatively low number of Batches/Sec. 

Thanks to Greg Linwood for the tip!  Check out Greg’s cool script for IO by databases at http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/03/15/59.aspx. 

And in case you’re looking for good SQL Server 2008 forums, now that the product is RTM, go to: http://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=428&SiteID=1.

Thanks,

-Kev

Q: I have inherited a legacy applications of which I am certain there are many unneccesary tables and indexes.  I am slightly paranoid about ripping them out however as I am not 100% sure that they are not used.  Is there any way that I can determine which indexes and tables are safe to drop?

A: As long as your application is running on SQL Server 2005+, you have access to a Dynamic Management View (DMV) that displays exactlky this information.  Querying from sys.dm_db_index_usage_stats will tell you how many times a given index/table has been scanned, seeked, looked up, updated, and even gives you the last date when an operation happened.  Browsing through this DMV will allow you to determine with confidence any index/table that has never been touched (since SQL Server was last restarted).  There are many exampes of some elegant queries that people have written that can be found by searching the DMV name but its a fairly straight forward one to understand.

One got-chya, the data in sys.dm_db_index_usage_stats will show tables/indexes across all databases where sys.indexes only shows you data from the database you are in.  This creates some challenging hurdles if you are trying to join on those tables to lookup the index name.  It’s best to keep it simple and filter dm_db_index_usage_stats to only show you data from the database you are in.

A fairly simple example with explanation can be found here.

This is not an entirely new post, I won’t lie. Lately I’ve been asked about signal waits quite a few times, so I wanted to point to a past post. In Scheduler Yields = CPU Pressure…or do they? I discussed signal waits and talked a bit about wait event analysis. Here’s some additional and summary information… Wait event analysis is the practice of looking at SQL Server wait events to determine the cause of specific instance/database/statement/application behavior. Microsoft refers to this as analyzing waits and queues. Basically, the execution model for a session dictates that each session is either running, waiting or sleeping. The queues organize sessions into buckets of running, runnable or suspended. If a session is running, it is actively running and using CPU resources. If a session is runnable, it is waiting for CPU time. If a session is suspended, it is waiting for another physical resource to become available. This is best described using the [fantasy version of a] supermarket analogy where CPU is a cashier, a session is a person in line (in the queue to use the UK vernacular) and a resource is anything from change to preferred shopper cards to coupons:

• When a customer gets to the cashier, he/she is running (using CPU)
• Everyone else in line is runnable, waiting for time with the cashier
• As soon as the customer needs to reach for change, dig for coupons, he/she is immediately moved to the back of the line (put in the suspended queue) and the next customer in line (in the runnable queue) gets to move up to the cashier

Where people get confused is when signal waits come into play. Signal waits are actual waits for CPU time - pure CPU wait measurements. So, if you are analyzing wait events, and are trying to come up with an aggregated time for instance/database/statement/application activity, you need to add up the wait event time AND the signal wait time to get a complete picture. I’ll post more on this topic at a later time to go into more detail. If you have any questions or want more information on specific information, please comment.

A lot of people sent me emails asking for a way to get ahold of Quest Software’s new DMV poster.  The poster shows all of the major DMVs in SQL Server 2005/2008, what they’re used for, and how to query them.

You can get the poster (for free, of course) by signing up for and attending the accompanying webcast about DMV’s that I’m doing next Thursday, July 10th.  If you don’t know much about Dynamic Management Views (DMVs), then you’ll want to attend this informative webcast with me and my PASS cohort Tom LaRock.  Get all the details about the webcast at http://www.quest.com/backstage/pow.aspx.

I hope this helps,

-Kevin

There is a lot to talk about here…since this is a database blog, I’m going to provide the links to Microsoft’s published documentation so anyone who wants to get some good background information can do so. It’s certainly worth reading if you want to bone up on your Windows internals knowledge:
RAM, Virtual Memory, Pagefile and all that stuff
How to determine the appropriate page file size for 64-bit versions of Windows Server 2003 or Windows XP
Memory Performance Information

Hope that helps!

I find that auto_update statistics in SQL Server is a really good thing.  Without it, many third-party applications would simply fall over from lack of preventative maintenance.  With it, they are able to run for extraordinarily long periods of time without really needing a full-time DBA to check up on the databases.

Having said all of that, auto_update statistics is a mystery for many of us.  This is reflected in the fact that I get lots of questions about auto_update statistics when I speak publicly (which seems to be at least once per month these days).  The most common question is “why isn’t auto_update statistics running?”

There are many elements to the answer to this question.  Naturally, you’re not going to have any new statistics in a database that is marked as readonly.  However, you might not know that approximately 20% of a table needs to change before triggering an auto_update statistics run for the given table.  If the table is really big, it’s possible that you’re simply not changing enough data.

Also remember that statistics are not the same in SQL Server 2005 as they are in SQL Server 2000 and that they must be upgraded using the sp_updatestats stored procedure after you migrate from version 2000 to 20005.  Otherwise, the old statistics are unusable by the query engine.  Once you’ve run sp_updatestats, the engine will maintain them automatically.  Be sure to check the article “After Upgrading the Database Engine” at http://msdn2.microsoft.com/en-s/library/ms143695.aspx to make sure you covered all the bases.

Thanks to Connor Cunningham and Simon Sabin (http://sqlblogcasts.com/blogs/simons) for help on this post.

First things first - depending on the version of SQL Server that you’re using, SQL Server could be creating this problem for itself. See these articles if you’re running SQL Server 2005 pre-SP2:

• SQL Server becomes sluggish or appears to stall on 64 bit installations
• A significant part of sql server process memory has been paged out. This may result in performance degradation
• How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005
• The SQL Server Working Set Message

So what’s going on? First we need to address two concepts: Virtual Address Space and Working Set. Windows uses virtual address space (VAS) to allocate memory to applications (that’s as deep as I’m going here). A program’s working set is the collection of VAS pages that have been recently referenced. When a process is created, it is assigned a minimum and maximum working set size. As the working set size increases, thereby increasing memory demand, the virtual memory manager will attempt to keep up to the maximum working set size (memory) resident without exceeding the maximum threshold.

One common misconception is that there is as much virtual memory available to a process as there is physical memory (RAM) on a machine. Not so (or I wouldn’t be going there); you can get much more depth by reading Slava OKs’s blog on VAS. One very important point from a SQL Server perspective, however, is that when you’re configuring min and max server memory, you have to take into account the MemToLeave area. MemToLeave is VAS that’s left unused when SQL Server starts so that external components have address space to consume when they’re loaded. Typically, the majority of memory allocations from MemToLeave are from non-SQL Server memory consumers like COM objects, extended stored procedures, and linked servers. You can use DBCC MEMORYSTATUS to see these, but bear in mind that what’s reported will only reflect allocations made by SQL Server directly (SQL Server has no knowledge of outside memory requests).

So, when troubleshooting out of memory errors, do your homework.

• Check the list above to see whether your SQL Server is suffering from a known SQL Server bug, and understand (at least a little bit about) VAS and memory allocations.
• You can use Performance Monitor (Task Manager won’t help here) to get an idea of how much of VAS is currently consumed inside of your process by looking at the virtual bytes counter.
• If you believe that a 3rd party application is loading DLLs into SQL Server’s VAS, or you want to see if your own CLR or external procedures are doing so use SQL Server’s DMV sys.dm_os_loaded_modules to see what’s been loaded into SQL Server’s address space.

Oh, and by the way, for anyone who’s used to configuring the working set size parameter with sp_configure, you need to be aware that it’s been deactivated in SQL Server 2005.

Hope that helps.

You’d think an article called “NASTY RUMORS ABOUT MAXDOP” would have something to do with Britney Spears or maybe Robert Downy Jr, but in that case it’d be total fiction (at least, it would be coming from my pen).

So, I was en route to the 2008 Microsoft MVP Summit yesterday and I had a chance to catch up on my reading.  You may have heard some rumors that you should only ever set MAXDOP (maximum degrees of parallelism) to an even number.  I can neither confirm nor deny these rumors since I haven’t had time to test this yet myself.  But I’m curious if any readers out there have definitive information one way or the other.

Evidently, there are two roles involved in parallel processing, a writer and a consumer.  Naturally, when two roles are at work, the MAXDOP setting doesn’t have to share resources when it is set in increments of two.

Hope this helps,

-Kevin

Hi everybody, I just wanted to go ahead and post the third part of the four part “screencast” series that I did through TechTarget.  Again, if you’ve ever struggled with knowing which PerfMon counters to track for SQL Server performance, then check out this series and get answers to questions including:

I hope it’s helpful to you and would welcome your feedback, so feel free to leave your comments.

 Thanks alot and you can find this third part of the series here titled “Monitor SQL Server disk I/O with PerfMon counters“.

First of all you should absolutely consider upgrading your SQL Server instance. If you can afford to migrate to SQL Server 2005 SP2 there are loads of performance and administration enhancements you can take advantage of; if you want to (or need to) stay with SQL Server 2000 you should seriously consider upgrading to SP4 (especially to protect yourself from some very well publicised security vulnerabilities).

As for how SQL Server utilizes resources, I would definitely need more information to provide any definitive advice. What does your application workload look like? Do you have a large number of concurrent connections? What type of an application are you running? Are there a large number of calculations being performed in the database? You should create a performance baseline using PerfMon counters to determine the normal operating behavior for your application. Next, you can read articles like Optimizing SQL Server CPU Performance to determine whether some basic tuning can help, or if you need more detailed advice.