Beginning Performance Tuning with SQL Server

I am very glad to write this very first blog post for SQL Server Magazine. I have been thinking on this for a long time regarding what exactly to write and what can interest the readers. I quickly discussed regarding various ideas with few of my friends, and finally, I have come up with the idea of starting with performance tuning. Performance tuning has been my favorite subject, and I have written many articles on this subject on my blog, Journey to SQL Authority with Pinal Dave.

When I get any assignment on performance tuning or query optimization, I follow a systematic method to find out their issues and propose the resolutions of the same. Every consultant has his/her own methods and own style. We will talk about various methods during the course of these blog posts.

Today, I would like to start something very much fundamental. We will talk about SQL Server Management tools and T-SQL command, which I use for performance tuning. I will describe my usual scenario where I am asked to provide consultation for performance tuning.

Ice Breaker

This is a very interesting phase. This is the stage at which the client is convinced that I can help them and I am also equally convinced that I can help them. The real story and challenges start from here – how to start? I maintain a pre-consultation chart and send it to my clients. I also request the client to fill it up for me. After few consultation engagements, I realize that it is not going to work as my chart does not cover everything on which I had requested for. I keep on adding more and more options and very soon I realize that clients now do not want to fill it up as it is too long and many things do not apply to them. Finally, I had to get rid of the checklist idea.

I needed something new - which can be useful to me and at the same time help me to get going with the client. I used to ask if they have schema designs for their databases and log of SQL Server changes (hot fixes, configuration changes, etc). Earlier, to my surprise, many Multi-National Companies and large organizations did not seem to have anything related to this. In contrast, I noticed that I have seen the same information maintained in many smaller organizations. I will not go on reasoning the same as this is not the right place to do so. Very soon, I stopped asking for the same as well.

Summing up, today I use very different methods such as Ice-Breaker; it may seem to be very orthodox, but this procedure has been working well for me. Here is the quick survey checklist I give to my users and it seems that they are happy to fill it up for me.

A Quick Survey

1. Server/Instance Name:

2. Purpose of the server:

a. Production

b. Development

c. Staging

d. High Availability Secondary Server

e. Other

3. Issues faced with performance (in English):

4. Please send me spreadsheet with results of following queries:

a. Query 1: System Configurations:

SELECT * FROM sys.configurations GO

b. Query 2: System Wait Stats:

SELECT *FROM sys.dm_os_wait_statsORDER BY wait_time_ms DESCGO 

5. Contact Person:

If you look at the above list of questions, they may appear to be very simple; but believe me, this questionnaire is helping me very well! The real information, to which I pay most attention in the queries, will be given as an answer to question 4 by the clients.

System Configurations

There are two queries in my quick survey: one query is for system configuration. This query is very important for obtaining an overview of the system. System Configurations information contains various information like minimum and maximum memory, CPU, Fill Factor and various information, which once configured properly can make big difference for any server. In a future blog post, we will go over various very important indications and methods retrieved from system configuration values.

Wait Stats and Types

There may be many reasons for bad performance of the system. However, sometimes a powerful hardware can successfully hide the performance issues. Even though there are always cases in which no matter how powerful the system is, such performance issues usually crop up. More CPU, more memory, and more bandwidth have been common in powerful system, but the nature of the programming is such that code always needs more resources. There are cases when the code needs more resources to perform certain tasks; but there are plenty of the resources already available and adding more will not help.

It is possible and important to understand the resources bottlenecks and properly optimize them. SQL Server waits stats provide a lot of such information. Recently, I became a big fan of the wait stats, and I have used it extensively to troubleshoot bottlenecks as well understand the past of the server. In a future blog post, we will go over many important wait stats, their implications and resolutions.

We will talk about various tools, performance tuning tips, methodology and tricks in the future blog post. If you have any particular interest in any subject, then leave your comment and I will surely see where it fits in my story.

Author Bio: Pinalkumar Dave is a Microsoft SQL Server MVP, mentor for Solid Quality India, and Microsoft Certified Trainer (MCT). He has written more than 1,500 articles on his blog, Journey to SQL Authority with Pinal Dave. He is a dynamic and proficient principal database architect, corporate trainer, and project manager, who specializes in SQL Server programming and has seven years of hands-on experience. He holds a Master of Science degree and several certifications, including MCTS, MCDBA, and MCAD (.NET). He is also Regional Mentor for PASS Asia.