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): –
- Total Server Memory
- Buffer Cache Hit Ratio
- % Processor Time
- Avg. Disk Sec/Write
- SQL Server metrics
- Page life expectancy
- Batch Requests/Sec
- Full Scans/sec