The Planet Blog

“What counters are important when troubleshooting SQL Server with Windows’ Perfmon (Performance Monitor)?”

As a database administrator architect, I can’t tell you how many times I’ve gotten that question from Windows administrators and new database administrators, and it’s a pretty daunting question to answer. When a SQL instance is installed on a server, there may be more than a thousand new performance counters added to Perfmon and to the Performance Monitor section of the Reliability and Performance Monitor in Windows 2008, so where does one start?

When it comes to performance tuning and troubleshooting, SQL Server has not changed much over the years. In a nutshell, it is all about CPU, memory and I/O. To reduce the time it takes to determine the bottleneck in a given environment, we should isolate the counters to these specific areas.

Of the many performance counters that can be selected when troubleshooting a SQL Server, these key indicators can help to quickly isolate bottlenecks and direct your investigation to the appropriate resources for corrective action:

At the Operating System Level

CPU Counter

Server Work Queues > Queue Lengths
Description: Queue Length is the current length of the server work queue for this CPU.
Value: A sustained queue length greater than twice the number of CPU cores might indicate processor congestion.

Memory Counter

Memory > Pages/Sec
Description: A page fault occurs when the operating system (OS) cannot find the requested information in its physical memory, forcing the OS to seek the information at the disk level. A soft page fault is when a page is found elsewhere in the physical memory, and a hard fault requires disk access. Most processors can handle large numbers of soft faults without significant consequence. However, hard faults, which require disk access, can cause significant delays.
Value: This value should stay below 20-25 pages per second.

I/O Counters

Physical Disk > Average Read Queue Length
Physical Disk > Average Write Queue Length
Description: Avg. Disk Queue Length is the average number of read or write requests that were queued for the selected disk during the sample interval.
Value: The value for this counter should always be under 2. This is the most reliable counter to use when the SQL instance is using external SAN storage for its databases.

Determining Application Resource Usage

Process Counter:

From Perfmon, select “Process” then select the application in question – in this case, sqlservr.

This will add all of the operating system counters associated with the application.

For SQL Server, the counters usually reflect information about the internal operations of the SQL instance that may not show up as a problem at the operating system level. For example, a customer might call to report that the server hangs for several moments throughout the day, but there are no errors in the application or system logs, and the OS Performance counters show all counters within normal limits.

These SQL performance counters can provide deeper insight into how the database engine is working against the customer’s application to help in your investigation.

SQL Server

SQL Server: General Statistics > User Connections
Description: Number of users connected to the system.
Value: This number will vary between applications on a given server.

SQL Server

SQL Server: Locks > Lock Waits/Sec
Description: Number of lock requests that could not be satisfied immediately and required the caller to wait before being granted the lock.
Value: This number should be near 0. If the value is greater than 0 for a sustained period, the “system hanging” as described by the customer is most likely due to locking and blocking issues within the database.

SQL Server

SQL Server: Buffer Manager > Buffer cache hit ratio
Description: The percentage of database pages that were found in physical memory. SQL Server stores information inside of 8KB data pages.
Value: The buffer pool cache hit ratio should be 100%. Values below 90% indicate that SQL Server is experiencing memory pressure.

SQL Server

SQL Server: Buffer Manager > Page life expectancy
Description: The number of seconds that a database page remains in physical memory.
Value: Values under 300 seconds indicate that the SQL instance is experiencing memory pressure.

SQL Server

SQL Server: Buffer Manager > Page reads/sec
SQL Server: Buffer Manager > Page writes/sec
Description: These values show the number of database reads and writes requested.
Value: Values for these counters will vary between database applications, but this information is useful when determining if SQL Server is the primary application using the disk. If the Buffer Manager page read-writes are low but disk-queue lengths are high, there might be a disk bottleneck. If the Page read-writes are higher than normal, a memory shortage is likely to exist.

SQL Troubleshooting Summary Chart

The performance counter suggested may not help resolve complex situations that involve application-level performance issues, but in my experience, it is a great starting point.

-George

It’s been a whirlwind of a month already. We released several new server promotions, launched Alpha Professional “managed dedicated” services for private and virtual racks, had company-wide kickoff meetings in Houston and Dallas, and to top it all off, we pushed out a brand new Web site.

Just writing down the rundown of the first 23 days at The Planet this year makes me yearn for a George Costanza-inspirednap-friendly desk. A few weeks ago, I could have caught some shut-eye in my “seasonal office,” but now that my area is unwrapped, I’ll just power through this blog post … full pot of coffee in tow.

Avid blog readers will have already seen and loved Steve’s post about the new Web site, so I’m going to ride his coattails and focus on the message we built our site around: “The Power to Choose.”

They say a picture is worth a thousand words, but I’ll try to boil it down to a few: The Planet offers an unrivaled hosting product and service continuum. If you are in the market for any flavor of hosting, you can find it at The Planet. In that service spectrum, you see our primary lines of business: colocation, dedicated servers, managed services (monitoring, security, backup, migration) and fully managed hosting. With that foundation, we are able to incorporate new products and services like our Content Delivery Network and Storage Cloud offerings.

Now for a fun little interactive game: Click on the above image to get to our Hosting Solutions page where you can go through our “Which hosting solution is right for you?” wizard … Don’t worry, I’ll be here when you get back …

See. I told you I’d still be here. If you’re in the market for a specific kind of hosting, the wizard probably told you exactly what you already knew … unless you just clicked through it with a few “what if” answers to see what our super-secret, proprietary and complex artificial intelligence would suggest for you. Maybe that was just me.

The power to choose is central to our hosting message, so if you can think of anything we should add or change to include in that message, let us know!

-Kevin

P.S. If you’re interested in learning more about our continuum of products and services, head to our Hosting Services page to get a detailed comparison of our colocation, dedicated servers, managed dedicated servers and managed hosting offerings.

Drum roll, please … it’s a momentous day for The Planet!

This morning we’ve launched an awesome new Web site that will make it far easier for you – our customer – to learn about and purchase The Planet’s products and services. We’ve made the site easier to navigate, backed by a wealth of new features for visitors who aren’t certain which services are best for their business.

Prominently featured is our messaging based on “The Power to Choose.” We offer the broadest range of options in the industry. Whether you’re looking for dedicated servers, management services, fully managed hosting or colocation, you’ll find a solution that meets your needs.

You may have noticed advertising from The Planet on many new properties, as well as a new campaign that has The Planet ads showing up many places you have never seen us before, based on your having visited our site in the past … and this is just the beginning.

Go ahead and take a test drive through the new site and let us know what you think!

-Steve

After a long summer break, your favorite television shows are finally starting new seasons, and you are prepared: You have a 15-gallon bucket full of Kettle Corn ready for your prime time couch-potato enjoyment; you’ve made space on your Tivo by deleting old episodes since you’ve got them neatly stored on the last season’s DVDs you just bought; and you’ve set recurring calendar appointments to remind you of your “commitments” (Yes, we know that “Go to the gym” means “Call in an order at Domino’s and watch the pizza tracker like a hawk.”).

As your incessant withdrawal symptoms are approaching unbearable levels, I’ve got a treat for you that will temporarily satiate your desire for entertaining content: The Return of This Week* in Vending.

It’s been almost six months since you joined Christian and I on our taste-test of the vending machine chili cheese dogs, so your mouth is either watering for more or your stomach has finally settled … either way, you’ll enjoy this installment.

This week’s victim participant is Planet Northstar’s Development Manager Clayton Spivey, and he is forced gets to try a vending machine cheeseburger. Making a cameo in the episode: our first vending survivor, Todd Mitchell. (Editor’s Note: Make sure you watch Todd’s reaction when Clayton takes the burger out of the wrapper.)

When we finally get down to the meat of the interview (very punny, I know), you’ll have a chance to learn about Planet Northstar — The Planet’s Managed Hosting division — and the development work required to create systems to accommodate the division’s ambitious goals.