Running an SQL server is highly complex. Performance tuning is by no means simple and the truth is that in many cases people starting out as database administrators do not really know much about what should be done. On the other hand, we are faced with many different SQL performance tuning tips and so many small tasks that would make everything faster, that it’s difficult to know where to start.
Here are a few SQL server performance tuning tips that you can take into account right now, so you can have a good jumping off point.
Do Not Wait
When the SQL server attempts to do a task and is held up, monitoring tools will track what happened. You want to look at the records as fast as possible so that you can figure out what is slowing down the server. As an example, in the event that most of the waits are connected to page_IO_latch, the bottleneck is almost surely linked with I/O.
Locate The I/O Bottlenecks
Among the many reasons that SQL servers may be having problems, I/O bottlenecks make up a large part. There are different ways to determine if performance tuning is needed with the I/O bottlenecks:
- Checking page_IO_latch wait
- Using sys.dm_io_virtual_file_stats() in order to find the areas that present physical I/O that is excessive or I/O excessive stalls.
- Using PerfMon counters.
Whenever there are multiple I/O bottlenecks happening, try to address as many as you can before you consider adding more hardware. High latency is particularly important in log writes. This is something that needs to be modified in SQL server monitoring.
Modify The Problem Queries
When doing SQL performance tuning there are normally close to 10 stored procedures or queries that will be responsible for over 80% of the tuned activities that are poorly done. Whenever this happens, you want to tune all of them. That will create a huge performance impact on the SQL server, leading to operations that are much faster than they used to be.
Monitor The Index Usage
You need to look at sys.dm_db_index_operational_Stats() DMF because it offers useful information about index usage. The DMF helps you to decipher a ton of data enabling you to determine the indexes used or not used. You can learn the time between processes which will help with locking and latching timings. Additionally, the SQL performance tuning process has to include index monitoring to remove what you do not actually use.
Separating Log And Data Files
SQL server performance tuning should always implement good performance rules. One such rule is separating log and data files whenever possible. That is normally a lot more important when you are utilizing DAS, but it can also be applied to SAN. Try to separate the random data files access from the access that is sequential and that appears when the transaction logs are being written.
All the tips above can help you out a lot at the end of the day, but honestly there are so many other tasks associated with performance tuning. If the SQL server is not operating properly and what you try doesn’t work, consult with a database expert.
