Esat Erkec
Schedule a job to capture the SQL server activities and metrics for SQL Server Performance Tuning

SQL Server Performance Tuning tips for beginners

May 12, 2021 by

This article intends is to give helpful SQL Server performance tuning advice to those who want to learn performance tuning.

Introduction

One day a rooky database administrator has said to me: Understanding and solving SQL performance issues require an enormous amount of work.

Me: There is no doubt, SQL Server performance tuning issues are very complicated problems and required extra labor to resolve them but the key point is to determine the performance problem properly. How do you monitor and detect your database system?

Junior DBA: We are using SQL Server’s Activity Monitor

Me: It is not a good choice to monitor the SQL Server activities because it does not show all issues clearly and renames and groups some of the wait statistics. I think you have to learn to use sp_WhoisActive.

In this little conversation, we can realize that dealing with performance tuning seems a very struggle task for the junior database administrators or newbies but if we know how to diagnose the problem, we can try to find out a proper solution for the problem. In general, the following reasons can affect SQL Server performance negatively:

  • Improper SQL Server instance or database settings
  • Insufficient hardware resources or incorrect settings
  • I/O problems
  • Incorrect TempDb settings
  • Lock or deadlock problems
  • Poor performer queries

In order to resolve these types of performance problems, we need to monitor SQL Server activities, and if we don’t have enough budget for the advanced 3rd party SQL Server performance monitoring tools, we can take advantage of the sp_WhoIsActive stored procedure.

Why do we need to monitor SQL Server?

Monitoring the database activities is an integral part of the SQL Server performance tuning to understand and identify the performance problems more properly. Monitoring the SQL Server metrics on a regular basis will always help to obtain a reasonable clue to diagnose the problem.

In this context, we have numerous tool choices but if we want to use a free tool sp_WhoIsActive can be the best choice to monitor the SQL Server activities. For this reason, learning the sp_WhoisActive usage and interpret the results of it always helps to identify and troubleshoot performance problems. At the same time, sp_WhoIsActive helps to collect the activities of the SQL Server stores them into a table through a scheduled job. So that we can obtain a little warehouse that helps to analyze a specific time period or historical activities of the SQL Server.

Monitoring SQL Server activities through sp_WhoisActive

As we stated before, sp_WhoisActive is a comprehensive and helpful monitoring tool. WhoisActive is similar to a Swiss army knife because it allows us to customize it with various parameters so that we obtain different information according to our requirements. The following are the most useful parameters and can help to resolve the performance and lock issues.

@get_plans = 1: enables to show execution plans for the running queries

@get_locks = 1: enables to show a detailed XML snippet which includes all details about the locked objects

@get_additional_info = 1: enables to show a detailed XML snippet that includes details about the running process.

We can execute the WhoIsActive quickly to show the current activities of the SQL Server as follows.

Using sp_whoisactive for SQL Server Performance Tuning

At the same time, WhoisActive allows us to insert its result set into a table. To enable this feature of the WhoisActive, we will execute the following script and it will create a table that is needed to store the data.

Collect SQL Server activities with SQL Server Agent Job

After the table creation, we need a job to collect data automatically and on a scheduled basis. At first, we open up a new SQL Server Agent Job and give a name to it.

Create job to capture the sql server activities and metrics

We click the Steps menu and click the New button to add a new step. On this new step screen, we give paste the following query into the Command box.

Create a job step

As the last step, we navigate the Schedules menu and schedule the job to run every 30 minutes.

Schedule a job to capture the SQL server activities and metrics for SQL Server Performance Tuning

After the creation of the job, it will start automatically and begin to collect the SQL Server activities. Now it’s time to analyze the data which is collected by the job so that we can resolve the SQL Server performance tuning issues.

Analyze

After collecting database activities, we can analyze a particular time period that the queries were running slowly or we can find out which queries CPU, read or write utilizations are very highest. Through the following query, we can detect which queries have used the most CPU in the last two days.

Analyzing the captured SQL Server activities for the resolve SQL Server Performance Tuning issues

The locks column gives us all the locked object details.

Sp_whoisactive lock column

The additional_info column gives us connection and session settings details.

Sp_whoisactive additional_info column

Conclusion

In this article, we have learned a very helpful tip about SQL Server performance tuning. Monitoring the SQL Server activities is very important to overcome the performance issues and we can customize the sp_WhoisActive to collect the SQL Server activities so that we can analyze the collected information later.

Esat Erkec
Monitoring, Performance

About Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert. Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn. View all posts by Esat Erkec

168 Views