Overview on SQL Server Performance Tuning
SQL Server Performance Tuning is an important subject and it is essential to implement various measures at regular intervals of time to optimize the scalability and performance of the Server. There are multiple areas which we analyze to understand a performance problem. Here, i will discuss all those areas that are important considering performance tuning. I will not explain them in detail but you will get an idea to start learning performance tuning from DBA standpoint after reading this article.
Server Level Configuration Check
To deal with SQL Server Performance Tuning, it is required to review and check all the server level configuration settings of the SQL Server on periodic basis. Some of these settings may vary depending on the workload and criticality of the application. You can have a look at all the configurational settings by running below command.
sp_configure'show advanced options',1 GO RECONFIGURE GO sp_configure
The role of indexes in SQL Server performance tuning is very crucial. To achieve better Server performance during data retrieval, it is important to create useful indexes. Proper indexing helps in executing tasks with minimal disk Input\ Output operations and fewer system resources.
To implement this approach, it is important to understand how indexes are used by ‘Query Processor’ for executing quick data search operation. Even excessive indexes or poor indexes can harm your database performance so better to analyze the data and query before creating any indexes on your table. Read below articles to understand more on SQL Server Indexes.
SQL Wait Stat Analysis
“Wait Stat Analysis” is an alternate way to identify the symptoms that cause impact on the Server performance. SQL Server constantly keeps track of the reasons for which execution tasks wait or gets delayed; so to detect the reason, one can refer to the Server logs. You can read more about SQL Server Wait types in below attached articles.
- Understanding SQL Server Wait Types and How to get wait type information?
- How to Reduce PREEMPTIVE_OS_WRITEFILEGATHER Wait type in SQL Server?
- Should We Clear Wait Stats Data from DMV sys.dm_os_wait_stats?
- Understanding Resource_Semaphore wait type
- How to Fix BACKUPBUFFER Wait Type?
TempDB Space Review
Another way for optimizing Server performance tuning is through proper analysis of the usage as well as empty and occupied space statistics of TempDB database. Make sure that you TempDB database files are placed separately on different drive. If you place user database files and TempDB database files on same drive you might face high disk IO that will cause performance degradation.
Index Fragment\ Defragment Operation
This approach can better be implemented through analyzing the indexes to detect the extent of fragmentation and to identify which indexes and databases need to be defragmented for ensuring the smooth performance of these components. You can run SQL Server dashboard report to check which indexes are highly fragmented and need to rebuild or reorganize to improve the performance. Read below articles to understand Index rebuild and Reorganize operation in SQL Server.
- Difference between Index Rebuild and Index Reorganize Operation
- How to use Resumable Online Index Rebuild Operation?
- Disk and Performance Impact of running Online Index Rebuild Operation
By referring to the “Windows Event Log” or “SQL Server Error Log”, the actual cause for the inappropriate functionality of either database or SQL Server can be detected. This could help in identifying and rectifying the existing errors that lead to files inaccessibility.
Backup & Recovery Parameters
For better performance tuning, analyze the backup and recovery settings to ensure if they are implemented appropriately. So that the backup files can be restored successfully in case of any unexpected circumstances.
Review Database Files
Review entire database files including primary, secondary as well as log files (MDF, NDF and LDF respectively) to analyze and detect the actual cause that is responsible for the inappropriate functionality of database. Make a practice to keep data and log files on separate drives to reduce the disk IO pressure. You can also analize which data or log file is having highest IO operating, this way you can plan to place that file in a faster disk.
- Identify Disk Bottleneck in SQL Server using Perfmon Disk Counters
- Understanding Data Files, Pages & Extents
Blocking and Deadlock Detection
Detecting Deadlocks is not a major task but often requires proper understanding and hands-on experience on the concept to implement it in an accurate manner. To implement the task, it is important to understand the locking mechanism along with its resolution. You can also identify if there is poor application design or coding by analyzing blocking and deadlock occurrence. If it occurs frequently, you can seriously think about optimizing your application or T-SQL coding. Identifying blocking issues will also help you in SQL Server performance tuning.
Regular examination test over the various components of the SQL Server machine is required to ensure smooth functioning of all the elements as well as to prevent the adverse circumstances that can take place due to major hardware breakdown.
It is very important to understand the nature of Database Console Commands as some of them are very critical and sometimes requires additional attention while execution. Some of these commands include DBCC SRHINKDATABASE, DBCC FREEPROCCACHE, DBCC SHRINKFILE, DBCC REINDEX and DBCC DROPCLEANBUFFER along with some stored procedures such as SP_UPDATESTATS.
So, these are some of the major mechanisms of SQL Server Performance Tuning concept and when implemented with proper consideration and attention, help in optimizing the performance of the Server. By executing these actions on regular instance of time; the performance issues with the Server and the incorporated databases can be resolved.