SQL Server: collecting and analysing performance data – part 1


In my quest for “MCSE: Business Intelligence – Solutions Expert” certification, I build my knowledge on debugging and optimization server performance. Almost every enterprise solutions are using a kind of database.  Microsoft technologies such as SharePoint and Dynamics CRM are using SQL Server as storage platform.

When the amount of data and users increase, the server performance need to be optimal to offer users good performance. This can either be done be scaling up the running hardware or optimizing current hardware and software.

There exist lots of tool to collect and analyze performance data, but Windows Server offers a standard solution in “Performance Monitor”, and the use of “Performance Counters”. This tool can be used to monitor performance real-time, or capture metrics over a period of time. There exists counters for OS and SQL Server in my case. The data collection can be performed in real-time or scheduled to run automatically as storing performance data to files or database. There also exist tool for processing and analyzing the data.

The lists below show some counters I have found after heavy googling for the last few weeks. Until now, I have created Collector groups for these counters and run them in Performance Monitor.

Generic OS counters (StdWinSrv):

  • Processor – % Processor Time
  • Processor – % Privileged Time
  • System – Processor Queue Length
  • Memory – Available Mbytes
  • Physical Disk – Avg Disk sec/Read
  • Physical Disk – Avg Disk sec/Write
  • Network Interface – Current Bandwidth
  • Network Interface – Bytes Sent/sec
  • Network Interface – Bytes Received/sec

Some basic SQL Server counters (SQLBasicCounters):

  • SQL Server: Cache manager – Cache Hit ratio
  • SQL Server: Databases – Transactions/sec
  • SQL Server: General statistics – User connections
  • SQL Server: Buffer Manager – Page Life Expectancy
  • SQL Server: Locks – Number of Deadlocks/sec
  • SQL Server: Access Methods – Full Scans/sec
  • SQL Server: Access Methods – Index Searches/sec

Some extended SQL Server counters (SQLExtCounters):

  • SQL Server: Access Methods – Forwarded Records/sec
  • SQL Server: Buffer Manager – Free List Stalls/sec
  • SQL Server: Buffer Manager – Lazy Writes/sec
  • SQL Server: Buffer Manager – Page Reads/sec
  • SQL Server: Buffer Manager – Page Writes/sec
  • SQL Server: Memory Manager – Total Server Memory (KB)
  • SQL Server: Memory Manager – Target Server Memory (KB)
  • SQL Server: SQL Statistics – Batch Requests/sec
  • SQL Server: SQL Statistics – SQL Compilations/sec
  • SQL Server: SQL Statistics – SQL Recompilations/sec
  • SQL Server: Latches – Latch Waits/sec
  • SQL Server: Locks – Lock Waits/sec

After watching a Pluralsight video presented by Erin Stellato from SQLskills, I became aware of a pretty neat tool “Performance Analysis of Logs” (PAL – http://pal.codeplex.com/). This is a free tool available made and used by Microsoft Field Engineers that analyzes perfmon logs based on predefined and customizable thresholds templates. The PAL application has a GUI and generates HTML reports that can easily be used for management reporting.

If we need more custom analysis, we probably want to store the perfmon data in a SQL database, and make our own analysis. The process of collecting data from a SQL server, store data into daily files, copying the files on a file share, and put these data into a database for closer analysis is the topic of my next blog post.

 

Advertisements

2 thoughts on “SQL Server: collecting and analysing performance data – part 1

  1. Pingback: Perfmon – Configuring counter collector sets | Sveroa's Developer Blog

  2. Pingback: Perfmon – store counter data in SQL | Sveroa's Developer Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s