Logging SQL Performance Statistics

Logging SQL Performance Statistics

technology

​​​​​​

One of the most important tools in analyzing a SQL Server performance problem is a good understanding of the normal behaviour your system exhibits. To do this you must have weeks (and better yet – months) of counter data as the system functions under different load rates.

You can purchase 3rd party tools to help you in this area or you can use the ones that Microsoft has already provided and with a little judicial selection of Perfmon counters, come up with a historical database of SQL Performance information that you can use in a pinch.

The tool to use is Logman. This will start a Perfmon counter session using counters from a configuration file and log the results automatically to a SQL database.

The first thing to do is to create a DSN connection to you performance monitoring database. This can be called anything, but assume here the DSN is call “PerfMon”.

Starting Logman

The next step is to create a little batch job that starts a counter job using your configured counter list. That job might look like this.

Logman Stop PerfMon
Logman Delete PerfMon
logman Create Counter PerfMon
-cf "C:\Perflogs\Perfmon Configuration.txt" -si 00:15:00 -f sql
-o Perfmon!BaseLine –u myDomain\performanceUser password
logman Start PerfMon

In this job we first stop the PerfMon job if it is currently running and delete any old definition, recreate the counter job and then start the collection.

  • The Logman “Create” function creates a counter job called PerfMon.
  • The “-cf C:\Perflogs\Perfmon Configuration.txt” will use the counters in the named file in the collection.
  • The “-si 00:15:00” parameter set the refresh rate to every 15 minutes.
  • The “-f sql” parameter indicates that the output will be to a sql database.
  • The “-o Perfmon!BaseLine” parameters indicates that the DSN called PerfMon should be used and this dataset will be identified with the name “BaseLine”.
  • The “-u” parameter is a domain userid and password used to run the counter job.

If you spell one of the parameters wrong or for some reason Perfmon can’t find the counter, an entry is added to the Application error log.

Conclusions

This process will run continually, adding records every 15 minutes to the database until you stop the counter job. The result is a database of important performance numbers for your SQL Server system. Because this counter job runs 24/7 you will have baseline numbers you can retrieve for every workload your system normally encounters.

A good example list of parameters is shown here (the quotes are necessary).

The Network Interface counters will have to be set to the actual name of the card in your target system. For example my card is called “SMC EZ Card 10_100 PCI[SMC1211TX] - Virtual Machine Network Services Driver”.

"\Memory\Available MBytes"
"\Memory\Cache Bytes"
"\Memory\Free System Page Table Entries"
"\Memory\Page Faults/sec"
"\Memory\Pages/sec"
"\Memory\Pool NonPaged Bytes"
"\Memory\Pool Paged Bytes"
"\Memory\System Cache Resident Bytes"
"\Paging File(_Total)\% Usage"
"\Paging File(_Total)\% Usage Peak"
"\Network Interface(network card)\Bytes total/sec"
"\Network Interface(network card)\Current Bandwidth"
"\Network Interface(network card)Output Queue Length"
"\PhysicalDisk(_Total)\% Disk Time"
"\PhysicalDisk(_Total)\% Idle Time"
"\PhysicalDisk(_Total)\Avg. Disk sec/Read"
"\PhysicalDisk(_Total)\Avg. Disk sec/Transfer"
"\PhysicalDisk(_Total)\Avg. Disk sec/Write"
"\PhysicalDisk(_Total)\Disk Bytes/sec"
"\PhysicalDisk(_Total)\Disk Read Bytes/sec"
"\PhysicalDisk(_Total)\Disk Write Bytes/sec"
"\PhysicalDisk(_Total)\Disk Reads/sec"
"\PhysicalDisk(_Total)\Disk Transfers/sec"
"\PhysicalDisk(_Total)\Disk Writes/sec"
"\PhysicalDisk(_Total)\Split IO/sec"
"\LogicalDisk(_Total)\% Disk Time"
"\LogicalDisk(_Total)\% Free Space"ec"
"\LogicalDisk(_Total)\Disk Writes/sec"
"\LogicalDisk(_Total)\% Idle Time"
"\LogicalDisk(_Total)\Avg. Disk sec/Read"
"\LogicalDisk(_Total)\Avg. Disk sec/Transfer"
"\LogicalDisk(_Total)\Avg. Disk sec/Write"
"\LogicalDisk(_Total)\Disk Bytes/sec"
"\LogicalDisk(_Total)\Disk Read Bytes/sec"
"\LogicalDisk(_Total)\Disk Write Bytes/sec"
"\LogicalDisk(_Total)\Disk Reads/sec"
"\LogicalDisk(_Total)\Disk Transfers/sec"
"\LogicalDisk(_Total)\Disk Writes/sec"
"\LogicalDisk(_Total)\Split IO/sec"
"\Process(_Total)\% Processor Time"
"\Process(_Total)\Page Faults/sec"
"\Process(_Total)\Handle Count"
"\Process(_Total)\IO Data Operations/sec"
"\Process(_Total)\IO Other Operations/sec"
"\Process(_Total)\Private Bytes"
"\Process(_Total)\Thread Count"
"\Process(_Total)\Working Set"
"\Processor(_Total)\% Privileged Time"
"\Processor(_Total)\% Processor Time"
"\Processor(_Total)\% User Time"
"\Processor(_Total)\Interrupts/sec"
"\System\Context Switches/sec"
"\System\Processor Queue Length"
"\SQLServer:Access Methods\Full Scans/sec"
"\SQLServer:Access Methods\Index Searches/sec"
"\SQLServer:Access Methods\Mixed Page Allocations/sec"
"\SQLServer:Access Methods\Page Splits/sec"
"\SQLServer:Access Methods\Mixed Page Allocations/sec"
"\SQLServer:Access Methods\Workfiles Created/sec"
"\SQLServer:Access Methods\Worktables Created/sec"
"\SQLServer:Access Methods\Worktables from Cache Ratio"
"\SQLServer:General Statistics\Temp Tables Creation Rate"
"\SQLServer:General Statistics\Temp Tables for Destruction"
"\SQLServer:Buffer Manager\Buffer Cache Hit Ratio"
"\SQLServer:Buffer Manager\Checkpoint pages/sec"
"\SQLServer:Buffer Manager\Database Pages"
"\SQLServer:Buffer Manager\Lazy writes/sec"
"\SQLServer:Buffer Manager(_Total)\Page Life Expectancy"
"\SQLServer:Buffer Manager\Total pages"
"\SQLServer:Plan Cache(_Total)\Cache Pages"
"\SQLServer:Plan Cache(_Total)\Cache Hit Ratio"
"\SQLServer:Databases(_Total)\Log Flushes/sec"
"\SQLServer:Databases(_Total)\Transactions/sec"
"\SQLServer:Latches\Average Latch Wait Time (ms)"
"\SQLServer:Latches\Latch Waits/sec"
"\SQLServer:Latches\Total Latch Wait Time (ms)"
"\SQLServer:Memory Manager\Memory Grants Pending"
"\SQLServer:Memory Manager\Target Server Memory (KB)"
"\SQLServer:Memory Manager\Total Server Memory (KB)"
"\SQLServer:SQL Statistics\Batch Requests/sec"
"\SQLServer:SQL Statistics\SQL Compilations/sec"
"\SQLServer:SQL Statistics\SQL Re-Compilations/sec"

These counters are good baseline indicators as to how your system uses resources under normal loads. Some of them however, have critical values.

Counter Name Notes
"\Memory\Available MBytes" < 100MB is a concern; < 10MB is fatal
"\Memory\Free System Page Table Entries" < 7000 remove the /3GB parameter if you are using it; < 5000 is bad; < 3000 is very bad;
"\Memory\Page Faults/sec" < a few hundred is desirable; a few thousand is acceptable if other memory counters are not critical
"\Memory\Pages/sec" >2500 is bad for current servers; older servers can handle much less
"\Memory\Pool NonPaged Bytes" 32 bit: 100MB to 260MB; 64 bit: 1GB – 128GB
"\Memory\Pool Paged Bytes" 32 bit: 100MB to 260MB; 64 bit: 1GB – 128GB
"\Network Interface(network card)\Bytes total/sec" <40% is good; 40% - 60% caution; 65% - 100% critical
"\Network Interface(network card)\Current Bandwidth" <80%
"\Network Interface(network card)\Output Queue Length" 0 healthy; 1-2 caution; >2 critical
"\PhysicalDisk(_Total)\% Disk Time" >85% consider adding spindles
"\PhysicalDisk(_Total)\Disk Write Bytes/sec" ~150
"\PhysicalDisk(_Total)\Disk Reads/sec" ~150
"\PhysicalDisk(_Total)\Disk Transfers/sec" ~150
"\PhysicalDisk(_Total)\Split IO/sec" The fewer the better
"\LogicalDisk(_Total)\Disk Reads/sec" ~150
"\LogicalDisk(_Total)\Disk Transfers/sec" ~150
"\LogicalDisk(_Total)\Disk Writes/sec" ~150
"\LogicalDisk(_Total)\Split IO/sec" The fewer the better
"\Process(_Total)\% Processor Time"
"\Process(_Total)\Page Faults/sec" < a few hundred is desirable; a few thousand is acceptable if other memory counters are not critical
"\System\Context Switches/sec" 5000 * # of CPU’s see here for more information
"\System\Processor Queue Length" 2 * # of CPU’s ; >2 is a CPU Bottleneck
"\SQLServer:Access Methods\Full Scans/sec" (Index Searches/sec) / (Full Scans/sec) > 1000
"\SQLServer:Access Methods\Index Searches/sec" See here for more information
"\SQLServer:Access Methods\Page Splits/sec" < 20 per 100 Batch Requests/sec
"\SQLServer:Buffer Manager\Buffer Cache Hit Ratio" 98% is acceptable; 99.8 is OK
"\SQLServer:Buffer Manager\Database pages" <300
"\SQLServer:Buffer Manager(_Total)\Page Life Expectancy" 300 seconds is the official threshold; 1000 or 10000 is better. See here for more information
"\SQLServer:Plan Cache(_Total)\Cache Pages" Values should be stable
"\SQLServer:Plan Cache(_Total)\Cache Hit Ratio" <98 is bad; >99 is good
"\SQLServer:Latches\Average Latch Wait Time (ms)" (Total Latch Wait Time) / (Latch Waits/Sec) < 10. See here for more information.
"\SQLServer:Memory Manager\Memory Grants Pending" >0 is a cause for concern. See here for more information
"\SQLServer:Memory Manager\Target Server Memory (KB)" High or rising value indicates insufficient memory
"\SQLServer:SQL Statistics\Batch Requests/sec" A current server should support ~2500/s per core
"\SQLServer:SQL Statistics\SQL Compilations/sec" If compiles > 10% of Batch Requests then apps are not caching effectively
"\SQLServer:SQL Statistics\SQL Re-Compilations/sec" Recompiles should be under 10% of Compilations.
​​

 Recent Posts

select to filter posts

Gamification: Coming to a workplace near you!

by Scott Johnston

Video games aren't just a wave of the future; they're the current reality for millions of players who take to their consoles and PCs every day.

read more

Managing Voter Experience

by Jim Ward

I had the chance to run a political campaign for a friend in the last civic election in Edmonton. A terrific experience, and full of surprises. But how could a computer guy possibly be qualified to run a campaign? If you’ve never been involved in one, ...

read more

Red Adair Rings True In IT

by Kerry Wiebe - Director of Business Development

“If you think it's expensive to hire a professional to do the job, wait until you hire an amateur.” - Red Adair This quote resonated with me when I recently took on a do-it-yourself (DIY) renovation project at my home. I decided to do the project ...

read more

My History with Umbraco

by Terence Jee

Umbraco is an open source, free CMS that allows users to download it and use for their own needs.

read more

Prototyping with Twitter Bootstrap

by Davis Levine

Front end frameworks can be powerful tools in a designer's development process. It can save a ton of time in the project process, and with most frameworks employing a fluid grid system, scaling to different devices becomes less of a headache.

read more

Knowledge of Possibilities

by Ammneh Azeim

Recently, I read a great blog post by Daniel Burrus which states “Give your customers the ability to do what they can’t currently do but would want to if they only knew it was possible.” The key here is knowledge of possibilities and knowledge which comes

read more

Safety Codes Council Adopts Microsoft Dynamics CRM

by Andrew Slessor

Recently, the Alberta Safety Codes Council adopted Microsoft Dynamics CRM as their platform for the Master Electrician Management System.

read more

Legal Aid Alberta Implements Microsoft Dynamics® CRM

by Andrew Slessor

Legal Aid Alberta Improves Client, Case and Financial Management with Microsoft Dynamics® CRM Legal Aid Alberta (LAA) provides cost-effective services to 200,000 Albertans in need each year.

read more

World Usability Day

by iomer

​​ This Thursday, November 7 is World Usability Day, a day dedicated to making the world a more user friendly place.

read more

PanEx 2011 Unites Developers and UX Professionals

by iomer

Last night I attended PanEx 2011 organized by UX YEG, Edmonton's User Experience community group.

read more

Committed to Disruption

by Jim Ward

​ One of the things I love about our industry is the counterintuitive truths that make themselves evident on a regular basis.

read more

SharePoint 2010 Data Access with LINQ to SharePoint

by Anthony Haxby

In SharePoint 2007 a popular and (relatively) straight-forward method of querying SharePoint Foundation data from lists was using the SPQuery class and involved writing the query using the Collaborative Application Markup Language (CAML).

read more

Case Study: Human Security Report Project

by iomer

The Human Security Report Project (HSRP) is an independent research centre affiliated with Simon Fraser University’s School for International Studies in Vancouver, Canada.

read more

Exciting new features in Dynamics CRM 2011

by Andrew Slessor

​​​​ We have been implementing Microsoft Dynamics CRM 2011 for a few months now and I am excited with many of the new features that have been added to the system.

read more

Bringing Teams Together: Calendar Overlays in SharePoint 2010

by iomer

Calendar aggregation is a topic that will come up time and time again when discussing desired functionality for a collaborative environment.

read more

An Opportunity to Pause

by Jim Ward

I was at the Microsoft Canadian Leadership Summit in Redmond last week. It's a great event for learning about future trends and technologies, as well as considering and applying the lessons presented by Microsoft experts and futurists.

read more

SQL Server Replication

by Mike Hebert - solution architect

Database replication is all about distributing data. There are many common scenarios where replication may make sense, whether it is to enable a mobile workforce, to allow intensive reporting or data mining without interfering with day-to-day operations,

read more

Stop Chasing Butterflies

by Jim Ward

Had some great learnings last week. Picture this. You've put your strategic plan together, and your team is busy doing the right things.

read more

The Coffee Still Matters

by Jim Ward - Founder

Former Starbucks CEO Howard Behar penned a wonderful book on leadership called “It’s Not about the Coffee”, based on his experiences building the Starbucks brand.

read more

Top Ten Reasons for Collaborative Governance

by Andrew Slessor

Collaborative Governance is the principal of sharing ownership of the intranet with various groups or individuals in an organization. Instead of no owner, or a single one, a collaborative team governs through the application of policies, standards and tem

read more

Website Sustainability

by iomer

​Website sustainability may sound like a complex topic but the concepts are quite straightforward and are fundamental to successful website development and growth.

read more

Diagnostic Logging

by Steven Sirockman - solution manager

As a SharePoint Technology Specialist, I have had many phone calls from clients asking me to come in and troubleshoot their SharePoint Server environment. One of the first questions I ask them is if they have configured diagnostic logging.

read more

Intranet Users Roles and Responsibilities

by Andrew Slessor

In order to manage a sucessful intranet, users must be certain of what tasks they are responsible for on the site. The roles for intranet users can be seperated into seven categories; these are described below.

read more

Snippet Compiler versus LINQPad

by Mike Hebert

Jeff Key’s Snippet Compiler is a popular developer tool that has been around for years – I first learned about it in a 2004 MSDN article titled “Ten Must-Have Tools Every Developer Should Download Now”.

read more

Dashboarding SQL Performance Counters

by Gord Barker - solution architect

In a previous blog I wrote about benchmarking SQL performance. This is important for those rare occasions when someone says the system is not running fast enough.

read more

3 Tools for Clearer, More Effective Writing

by Kael Nicholson

Need to convey a wealth of information in a tiny space? When working in the technical world, writers are constantly torn between the need to accurately describe complex ideas and the desire to make them clear and accessible.

read more

Integrating AJAX and Telerik with SharePoint

by Gord Barker

​To extend your SharePoint site to include Ajax, you will need to perform a few steps

read more

Logging SQL Performance Statistics

by Gord Barker - solution architect

One of the most important tools in analyzing a SQL Server performance problem is a good understanding of the normal behaviour your system exhibits.

read more

Five Things I'm Looking Forward to in SharePoint 2010

by iomer

Microsoft has released some Sneak Peek videos giving a preview of what we can expect to see in SharePoint 2010.

read more

SharePoint Application Performance

by iomer

Recently we were approached by a customer who was having performance issues with a custom application built on top of SharePoint.

read more

A Program to add files to a Zip file

by Gord Barker - solution architect

I recently had to retrieve 45 days worth of Air Quality data from a web site. I used WGET to retrieve 35000 tiny text files to a sub directory.

read more

The Top 10 SQL Scripts You Must Have

by Gord Barker - solution architect

Here is a collection of important SQL Script gathered from around the net (including the Steve and Chris Show on SSWUG). These are scripts you should know about or have in your toolkit.

read more

Edmonton Technology Community

by Christina Gray

As technology continues to advance, keeping up and prioritizing what you ‘need to know’ becomes more of a challenge.

read more