Show Menu

Boosting SQL Server Performance Cheat Sheet (DRAFT) by [deleted]

Enhance SQL Server performance

This is a draft cheat sheet. It is a work in progress and is not finished yet.


When working with SQL Server, there are correct and incorrect ways to set up, configure and maintain systems. Tradit­ion­ally, systems are expected to work properly after the initial set-up. However, this is rarely the case.

A system’s perfor­mance decreases over time, often due to unforeseen stress loads imposed on the system. These stress loads include increased data volumes, variety and velocity levels that exceed the initial design of the hardware.

Here are five perfor­mance techniques and best practices designed to succes­sfully aid DBAs in their system implem­ent­ations:


Data and log files are the fundam­ental building blocks for any DBA activity. Begin by placing data and log files in their proper locations. SQL Server has two distinct I/O patterns for data and log I/O. In general, data I/O patterns are random in nature, but SQL Server spends a great deal of time writing transa­ction logs sequen­tially. Therefore, as a best practice, data and log files should reside on separate drives to prevent interf­erence with one another.


Once the I/O load is generated by SQL Server, developers should focus on tuning the queries. Often time-c­ons­uming, this process may involve a signif­icant level of experience depending on the design of the applic­ation.


Checkp­oints are designed to run period­ically and are controlled by a recovery interval. You can measure the Checkpoint I/O by looking at the perfor­mance counter “Buffer Manager: Checkpoint pages/­sec.” It is important to note, that developers will need to take this number and multiply it by eight and then divide by 1024 to get to Megabytes.


New to SQL Server 2012, indirect checkp­oints provide config­urable databa­se-­level altern­atives to automatic checkp­oints. In the event of a system crash, indirect checkp­oints provide potent­ially faster, more predic­table recovery time than automatic checkp­oints.

DB Artisan

DBArtisan is a tool that can help with your turning. Learn more at http:/­/ww­w.e­mba­rca­der­


Filtered Statistics are computed over a subset of rows in a table, contain histog­rams, densities and tries. Filtered Statistics with highly selective predicates will be faster to build and update, and end up more accurate when they are created with a full scan sampling option.


Latch contention that hinders perfor­mance in OLTP enviro­nments is usually caused by high concur­rency related to one or more of the following factors:
• Schema Design & Access Patterns
• High Degree of Concur­rency at the Applic­ation Level
• Layout of Logical Files Used by SQL Server Databa­ses
• I/O Subsystem Perfor­mance

Excessive page latch contention typically occurs in conjun­ction with a high level of concurrent requests from the applic­ation tier. There are certain progra­mming practices that can also introduce a high number of requests for a specific page.

Whether you are a seasoned DBA or just starting out, these five techniques will add value to your overall perfor­mance optimi­zation process.


As a leader in database admini­str­ation, DBArtisan® arms you with the toolset necessary to monitor cross-­pla­tform DBMSs from a single UI while performing intuitive analytics to proact­ively identify issues paving the way for fast and efficient perfor­mance resolu­tions.