Baselining

Introduction

Before starting any performance tuning you need to baseline your SQL Server performance. This is for the following reasons: –

  • You can only gauge the effectiveness of any performance tuning techniques if you can quantifiably demonstrate the performance improvement. Therefore you need to compare performance metrics pre and post the change.
  • Performance tuning needs to begin somewhere with the biggest pain point in your system. With a baseline of performance metrics you can see where the issues lies, CPU, memory etc.
  • When you are alerted to a performance issues “it’s going slow”! You are able to compare the current state to your baseline to see which metric differs from normal to assist you in identifying the root cause of the problem.

How to Baseline

After reading numerous productivity and self-help books, the best method to improve your productivity is to eliminate those tasks from your life which are possible to do so.

Seriously, don’t roll your own baseline tools via Perfmon, extended events, DMV queries or some other tool then attempt to graph trends yourself using Excel or Power BI. Simply buy a third party product to monitor performance over time if at all possible. You really don’t want to be bug-fixing your monitoring solution when robust products are already available. This leaves you free to concentrate your efforts on what is important.

What to Baseline

At a high level, the metrics to baseline fall into the following categories (some examples of each are included): –

  • Memory
    • Total Server Memory
    • Buffer Cache Hit Ratio
  • CPU
    • % Processor Time
  • I/O
    • Avg. Disk Sec/Write
  • Disk
    • Space
  • SQL Server metrics
    • Page life expectancy
    • Batch Requests/Sec
    • Full Scans/sec
Advertisements

One thought on “Baselining

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