Query tuning is often the most rapid way to accelerate an SQL Server performance. Yeah, we know that performance tuning in SQL Server is not an easy task, especially if you are working with a large amount of data. However, it is not rocket science either.
So let’s begin with understanding what an SQL Server is and then we will move on to provide you with some tips and tricks on performance tuning in SQL Server.
What is An SQL Server?
SQL Server is a relational database management system (RDBMS), created and marketed by Microsoft. It is typically designed for the enterprise environment and it runs on T-SQL (Transact-SQL), an array of programming extensions from Microsoft and Sybase that add to the existing features of standard SQL.
It was Sybase, which developed the original SQL Server code. In the late 1980s, Microsoft, Sybase, and Ashton-Tate conjured a collaboration to produce SQL Server 4.2 for OS/2, the first version of the product. Subsequently, both Microsoft and Sybase continued to offer other SQL Server products.
What Does Performance Tuning in SQL Server Mean?
SQL Server performance tuning is the process of ensuring that the SQL statements issued by an application run in the fastest possible time. In other words, tuning SQL statements is finding and taking the fastest route to answer your query, just like discovering the fastest route to your home after work.
In medium and large companies, SQL database tuning is usually handled by a Database Administrator (DBA) and many a time developers also do the job of performance tuning in SQL Servers.
There are several ways to tune the performance of an SQL Server. Here, we will share a few tips and tricks to help you optimize the process of performance tuning in SQL Server.
SQL Server Performance Tuning Tips That Will Help Your Database Soar
Database tuning is both an art and a science. There is no right answer when it comes to tuning queries in an SQL Server. The issues you have solved on one system may not be a problem for another, and vice versa.
Therefore, performance tuning in SQL Server can become an incredibly daunting task. Sometimes, even the minutest change can have a dramatic impact on the database performance. However, with a few tweaks in your approach, you can make slow SQL Servers go faster.
Performance tuning aids in fixing queries that have been poorly written and indexes that are inefficient. The following techniques will give you a heads up and help you improve your query performance in a measurable way:
Improve Your Indexes
Indexes are data structures that increase the speed of data retrieval operations on a database table. Creating useful indexes is one of the most important steps for performance tuning in SQL Server. Useful indexes provide rapid random lookups and help you locate data that has fewer disk I/O operations and less system resource usage.
It is important to understand the type of queries and the frequency with which they are run before you create efficient indexes. Try to index the major searching and ordering columns. However, indexing may hinder the performance of your database if the tables are constantly loaded with INSERT, UPDATE, and DELETE.
Separate Data and Log Files
You should keep the data and log files separated into different physical drives arrays when using DAS or SAN. Although this practice is often ignored, there is no good reason to overlook it.
The main purpose of this practice is to separate the random access of data from the sequential access, which occurs while writing the transaction logs. This technique will amaze you with the difference it can make when the volume of your transactions increases.
Reduce the Use of Temp Tables
Temporary tables tend to increase the complexity of a query. If your application makes use of tempdb too often or creates too many temp tables, it may lead you to run into some contention related to internal structures that are associated with the tempdb files.
When you absolutely need to use temp tables, create an index within the temp tables to improve performance. More importantly, do not wait for the temp tables to be deleted automatically rather delete it immediately after you are done with the table to clear tempdb resources.
Locate I/O Bottlenecks
I/O bottlenecks are one of the key factors that hinder performance in SQL Server. Detect the I/O issues by using one of the following techniques:
Check to find high page_IO_latch waits or log_write waits in your wait statistics.
Use the DMF sys.dm_io_virtual_file_stats() to detect any areas where there is excessive physical I/O or excessive stalls on that I/O.
Use the trusty PerfMon counters or use the Avg. Disk sec/Read and Avg. Disk sec/Write counters to check the latency of the reads and writes.
Once you find the I/O bottlenecks, detect the queries that are contributing to the physical I/O, and try to tune them before adding more hardware.
Avoid Coding Loops for Performance Tuning in SQL Server
Imagine the following scenario in which about 1000 queries are directed to your database in sequence:
To tune the performance of your SQL Server, you should avoid such loops in your code. Instead, you can modify the code by using a unique INSERT or UPDATE statement with multiple rows and values:
Do keep it in mind to ensure that the WHERE clause does not update the stored value if it matches the existing value. This technique can dramatically enhance the performance of SQL Server.
Avoid the Use of SELECT
One important tip to optimize the performance tuning in SQL Server is to avoid using SELECT *. Instead, try to individually include the specific columns that you need. A query runs faster when there is fewer data to be retrieved. For instance, use the code –
in place of –
Avoid Correlated Subqueries
A correlated subquery uses values from the parent query. It usually runs row-by-row, once for each row returned by the outer query, thereby decreasing SQL query performance. The following is an example of a correlated subquery:
In the above example, the inner query (SELECT CompanyName…) is run for each row returned by the outer query (SELECT c.Name…). Why go over the same row again and again and slow down the SQL Server?
Instead, refactor the correlated subquery as a join:
This lets the query go over the Company table just once, at the onset, and JOIN it with the Customer table.
Do Not Shrink Data Files
Shrinking data files can negatively impact the performance of an SQL Server. First of all, the process of shrinking can get very painful at times. It also causes a lot of fragmentation, which results in the poor performance of the subsequent queries. If Instant File Initialization is not turned on, the resultant growth later may potentially cause timeouts and can also affect the performance.
At times shrinking a file might be absolutely necessary. However, do not forget to analyze the impact before you try it.
We hope these do(s) and don’t(s) will help you when writing queries or processes and will aide you for performance tuning in SQL Server. However, remember to evaluate each situation individually to comprehend which method works best for your database.