Show Menu

Boosting SQL Server Performance Cheat Sheet by

Enhance SQL Server performance
sql     database     programming     server     performance     optimize

Introd­uction

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 & LOG FILES

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.

TUNING QUERIES

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

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.

INDIRECT CHECKP­OINTS

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­o.com/

FILTERED STATISTICS

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 FACTOR

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.

MAXIMIZE YOUR DATABASE PERFOR­MANCE

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.

Download the Boosting SQL Server Performance Cheat Sheet

1 Page
//media.cheatography.com/storage/thumb/davidpol_boosting-sql-server-performance.750.jpg

PDF (recommended)

Alternative Downloads

Share This Cheat Sheet!

 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          SQL Server Cheat Sheet

          More Cheat Sheets by Davidpol