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“.

Technorati Tags: counters, diagnostics, I/O, monitoring, PerfMon, performance, sql server

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.

First, the quick answer to your question: use SQL Server Profiler, or the SQL server 2005 database management views (DMVs) for this type of problem. PerfMon can help, but not directly.

As Kevin’s recent posts illustrate, there is certainly no shortage of resources out there that can help you use PerfMon to performance tune your application environment. I’m not going to go into which counters you should use because there’s a limit to what PerfMon can do. Still, it’s a valuable tool, but you should use it more to:

1. Create a performance baseline
2. Determine how your application interacts with the operating system
3. Tune database and instance-level configurations.

In your case, unless the problem query is the only statement running PerfMon won’t [directly] help you here; SQL Server Profiler is better suited to this task, but bear in mind that the performance overhead of tracing statement executions is high (read: be careful when using Profiler to trace SQL executions as it can adversely affect instance performance).

If you’re using SQL Server 2005 you can use the database management views (DMVs) to find more information. Specifically, you can use the sys.dm_exec_query_nnnn views to see cached query execution specifics. I would start with sys.dm_exec_query_plan to see the statement’s plan. Using the XML Showplan information in this view you can determine whether the statement is being recompiled, if it’s performing table scans, and glean lots of other supporting information to see what your next steps should be.

Quest and others have tools that take the guesswork out of this investigation, but I have also posted a number of articles on this site explaining how to leverage these tables to get to the bottom of your bottleneck if you want to roll your own solution. You should also make use of the information in the following publications to ensure you’re aware of what Profiler and the DMVs are telling you:

• SQL Server I/O Basics
• SQL Server I/O Basics Chapter 2
• Troubleshooting Performance Problems in SQL Server 2005
• Statistics Used by the Query Optimizer in Microsoft SQL Server

If you’re well-versed in using PerfMon, I’d like to hear about the counters that you use regularly and what constitutes a good or bad value. 

However, it’s a rare individual who knows their way around PerfMon and its multitude of performance objects and counters.  Which ones should you track?  And even if you know the right ones to track, what values indicate good or bad performance? 

If you’ve ever struggled with knowing which PerfMon counters to track for SQL Server performance, take a look at my TechTarget series of screencasts at SearchSQLServer.com.  They’ll give you most everything you need to know about using PerfMon and assessing PerfMon counters to learn about the behavior of SQL Server.  It’s a four-part series with the first two sessions, PerfMon Counters for Tracking Memory and PerfMon Counters for the Windows OS, already posted.  These screencasts not only tell you which counters to track and why, but also what are the general rules of thumb for good performance when using these counters.

Enjoy!

-Kevin

Fragmentation: The scattering of parts of a file throughout a disk, as when the operating system breaks up the file and fits it into the spaces left vacant by previously deleted files.

That’s how the dictionary defines fragmentation, which can be a very real performance problem for certain databases. But when is fragmentation truly a problem, and how can you deal with it? The folks posting on the SQL Server Storage Engine blog have a nice series on fragmentation that can help debunk some myths and show you how to evaluate and fix problems once they arise.

On that note, Kalen Delaney has also posted some good information on why going to lengths to defragment system tables is often effort best expended elsewhere. See her posts on this subject here and here.

Finally, you can read the MSDN article Microsoft SQL Server 2000 Index Defragmentation Best Practices for the official word on the subject.

SQL Server’s memory limits are determined both by the Operating System limits and by the Edition of SQL Server. You should read the following articles to understand the differences between SQL Server 2000 [Edition] Features and SQL Server 2005 [Edition] Features. Then, you can move on to Server Memory Options and How to configure SQL Server to use more than 2 GB of physical memory.

To see how much memory your SQL Server is consuming, you can use Performance Monitor, specifically looking at the SQL Server: Memory Manager: Total Server Memory (KB) counter, among others. A full article is available on MSDN called Monitoring Memory Usage.

Hope that helps.