
SQL Server is a very complex product. And when it comes to performance tuning, many DBAs simply don’t know where to start. We’re going to get back to the basics of performance tuning by learning 10 tips that will help you get off on the right foot.
Tip 1: Stop Waiting Around
Every time that SQL Server tries to do something but gets held up for any reason, it tracks the incident in the form of something known as wait statistics.
Every time that SQL Server tries to do something but gets held up for any reason, it tracks the incident in the form of something known as wait statistics.
Tip 2: Locate I/O Bottlenecks
I/O bottlenecks are one of the key reasons why performance suffers SQL Server.
I/O bottlenecks are one of the key reasons why performance suffers SQL Server.
Tip 3: Root Out Problem Queries
In any given SQL Server instance, there are probably 8 to 10 queries or stored procedures that are responsible for 80 to 90 percent of the poorly tuned activity that you see throughout the day.
In any given SQL Server instance, there are probably 8 to 10 queries or stored procedures that are responsible for 80 to 90 percent of the poorly tuned activity that you see throughout the day.
Tip 4: Plan To Reuse
Ad hardware gets more powerful and less expensive, todays modern applications are packing an ever increasing transaction rate. One of the most expensive operations in terms of memory and CPU usage in the SQL Server instance is the compiling of query plans. Applications that don’t use stored procedures or that don’t correctly
parameterize their queries place the most load on the server.
Ad hardware gets more powerful and less expensive, todays modern applications are packing an ever increasing transaction rate. One of the most expensive operations in terms of memory and CPU usage in the SQL Server instance is the compiling of query plans. Applications that don’t use stored procedures or that don’t correctly
parameterize their queries place the most load on the server.
Tip 5: Monitor Index Usage
The sys.dm_db_index_operational_stats() DMF is a widely underutilized source of information. It can provide you valuable information about your index usage.
The sys.dm_db_index_operational_stats() DMF is a widely underutilized source of information. It can provide you valuable information about your index usage.
Tip 6: Separate Data and Log Files
One of the most basic but often disregarded rules for good performance is to separate the data and the log files onto separate physical drive arrays whenever possible.
One of the most basic but often disregarded rules for good performance is to separate the data and the log files onto separate physical drive arrays whenever possible.
Tip 7: Use Separate Staging Databases
Far too often data, that is temporary in natured, is imported and manipulated in a production database, before being written to a final table. The problem with this is that most production databases are in full recovery mode. This means that practically all activity is fully logged in the transaction log, and these operations can be expensive.
Far too often data, that is temporary in natured, is imported and manipulated in a production database, before being written to a final table. The problem with this is that most production databases are in full recovery mode. This means that practically all activity is fully logged in the transaction log, and these operations can be expensive.
Tip 8: Pay Attention to Log Files
Too many people underestimate the importance of the transaction log file in relation to performance. The most common mistake people make is not leaving enough free space in the transaction log file for your normal operations to occur without forcing an auto grow operation.
Too many people underestimate the importance of the transaction log file in relation to performance. The most common mistake people make is not leaving enough free space in the transaction log file for your normal operations to occur without forcing an auto grow operation.
Tip 9: Minimize tempdb Contention
If your application makes heavy use of tempdb, it’s possible that you could run into some contention regarding internal structures that are associated with the tempdb files.
If your application makes heavy use of tempdb, it’s possible that you could run into some contention regarding internal structures that are associated with the tempdb files.
Tip 10: Change the MAX Memory Limit
There have been improvements in the 64bit versions of SQL Server regarding memory allocation and sharing with the OS and other applications, but I’ve yet to see where leaving the MAX Memory setting at the default is ideal in real life. Even though your host server might be dedicated to SQL Server, there are always other applications or parts of the OS that require memory from time to time or even all the time. Do yourself a favor and set the MAX memory setting to at least 1 to 2GB less than the total amount of memory on the server.
There have been improvements in the 64bit versions of SQL Server regarding memory allocation and sharing with the OS and other applications, but I’ve yet to see where leaving the MAX Memory setting at the default is ideal in real life. Even though your host server might be dedicated to SQL Server, there are always other applications or parts of the OS that require memory from time to time or even all the time. Do yourself a favor and set the MAX memory setting to at least 1 to 2GB less than the total amount of memory on the server.
Bonus Tip: Just Say No to Shrinking Data Files
OK, this makes 11 tips. But shrinking data files has been a general bad practice for a long time, and it can really impact performance. The shrinking can be very painful to begin with and can cause a lot of fragmentation, your subsequent queries might suffer as a result.
Source credit: sqlmag.com