The purpose of this article is to give newbies some basic advice about SQL performance tuning that helps to improve their query tuning skills in SQL Server.
Introduction
We will cover topics such as indexing, statistics, and query optimization to help you get the most out of your SQL Server. By following these tips, you can improve the performance of your SQL Server and provide a better experience for your end users.
Measure the Query Statistics
In this part, we will look at the oldie-but-goodie tools which are very beneficial for SQL performance tuning :
- TIME STATISTICS
- IO STATISTICS
TIME STATISTICS is a feature in SQL Server that allows us to collect and view statistics about the amount of time that is spent executing a query. We can enable time statistics by using the SET STATISTICS TIME ON statement, and we can view the time statistics by looking at the messages that are returned by SQL Server after executing a query.
SET STATISTICS TIME ON
SELECT P.Name AS [Product Name],
SOH.OrderDate AS [Order Date]
FROM Sales.SalesOrderHeader AS SOH
INNER JOIN Sales.SalesOrderDetail AS SOD
ON SOH.SalesOrderID = SOD.SalesOrderDetailID
INNER JOIN Production.Product AS P ON SOD.ProductID = P.ProductID
SET STATISTICS TIME OFF
We can find the time statistics report on the Message tab of the SQL Server Management Studio.
CPU time: The total time which is spent by the CPU
Elapsed time: The total time which is spent by SQL Server.
Parse and compile time and SQL Server Execution Times. The parse and compile time statistics show how much time is spent to parse and compile a query. If we see these times as zero, it indicates that the optimizer has found a cached query plan for the executed query.
IO STATISTICS
shows the physical and logical activity of a query. We can enable the IO statistics like time statistics.
SET STATISTICS IO ON
SELECT P.Name AS [Product Name],
SOH.OrderDate AS [Order Date]
FROM Sales.SalesOrderHeader AS SOH
INNER JOIN Sales.SalesOrderDetail AS SOD
ON SOH.SalesOrderID = SOD.SalesOrderDetailID
INNER JOIN Production.Product AS P ON SOD.ProductID = P.ProductID
SET STATISTICS IO OFF
Scan count: Number of index or table scans performed.
Logical reads: Number of pages read from the data cache.
Physical reads: Number of pages read from disk.
Read-ahead reads: Number of pages placed into the cache for the query
It is important to note that when time statistics are enabled, they will be included in the output of each query, which can impact the performance of the system, so it is recommended to use it only when needed and turn it off when not in use.
Learn to Interpret Query Execution Plans
Learning to interpret the query plans are the most important point to figuring out how the query optimizer is decided to access the data and which steps are performed during this process. An execution plan is a very good pathfinder for SQL performance tuning because, through the execution plan of a query, we can identify the bottlenecks and inefficiencies of the query. There are two types of the execution plan:
- The Estimated Execution Plan shows the estimated steps and information of a query and it does not include runtime statistics about the query. We can generate this plan without executing the query
- The Actual Execution Plan includes all runtime statistics, warnings, and steps after the execution of the query.
We can enable the query plans easily in SQL Server Management Studio with help of the Include Actual Execution Plan or Actual Execution Plan buttons which are located in the Query Menu toolbar.
The first thing to know when interpreting a query plan is, the graphical query plans should read top to bottom and right to left.
Learn the Usage Details of the sp_whoisactive
The sp_whoisactive is a stored procedure that allows us to view information about currently running queries and ongoing processes in the database engine. It provides a wealth of information about what is happening on your SQL Server, including details about active queries, blocked processes, and resource usage. It also allows you to filter the results by various criteria, such as database, username, and program name. Therefore, knowing about sp_whoisactive will always give us an advantage in SQL performance tuning operations. After installing the sp_whoisactive we can easily monitor the activities in the database engine.
Learn to Usage Details of the Query Store
Query Store is a feature in Microsoft SQL Server 2016 and later versions that allows you to track, report and analyze executed query performance over time. It captures a history of executed queries, their execution plans, runtime statistics, and query wait statistics, and stores all these data in a repository called the Query Store. This allows you to easily identify and troubleshoot SQL performance tuning issues. Additionally, Query Store includes a set of built-in reports and tools to help you analyze and optimize your queries. The Query Store reports various reports that help SQL query tunning operations:
- Regressed Queries
- Overall Resource Consumption
- Top Resource Consuming Queries
- Queries With Forced Plans
- Queries With High Variation
- Query Wait Statistics
- Tracked Queries
Such as, we use the Top Resource Consuming Queries report to identify the queries which are consuming more resources. After navigating to the Query Store folder in the SSMS, we can open this report.
In this report screen, we can view a sorted list of queries according to selected metrics, and also, we can view the execution plan and query text of the top resource consuming.
Conclusion :
In this article, we took a glance at some useful tools which can be very useful for performance tuning.
