As anyone who manages databases knows all too well, SQL Server performance tuning is a critical function to ensuring optimal performance. With performance dependent upon various factors such as memory, configuration, query design and resource usage, isolating the root cause of performance degradation is no small feat.
Rather than waiting for performance problems to occur, proactively tuning SQL Server will ensure your SQL statements run as efficiently as possible by helping SQL find the fastest route in and out to deliver your query results.
If you’re struggling with sluggish performance—or you’re not one to just wait for issues to arise—here are three key areas to focus your SQL Server performance tuning to achieve optimal performance and healthier systems.
Tip #1: Optimize your TempDB
Improperly configured TempDB is a common culprit when looking at performance degradation. If you are frequently filling up your TempDB, it’s time to take a look at what needs to change.
First, check out TempDB size. There is no hard and fast rule about how large it should be, but a good rule of thumb is to keep TempDB at 25 percent of your largest database or the same size as your largest index. This prevents having to increase TempDB during rebuilds.
With TempDB, the faster the drive the better. When TempDB is placed on a slow drive or the same drive as the OS, you are sure to see database performance problems. If possible, keep TempDB on a dedicated local SSD. If that’s not possible, your next best option is to keep it on its own dedicated volume with sufficient pre-allocated disk space.
It’s also important to keep data and log files separate and to set a large fixed value for TempDB autogrowth. Otherwise, you will be hit with unnecessary overhead every time TempDB fills up.
Controlling the number of TempDB data files contributes to TempDB optimization. But the big question is, how many TempDB data files do you need? Ideally, you will have one TempDB data file for every logical CPU, but not more than eight total (with some exceptions). For example, if you have four logical CPUs, you need four TempDB data files. If you have 12 logical CPUs, you can have eight TempDB data files.
Tip #2: Prevent performance bottlenecks
There are three main types of SQL Server performance bottlenecks that contribute to poor performance: CPU, memory and I/O. The causes, symptoms and diagnostics differ by the type of bottleneck, so here’s an at-a-glance guide for what to watch out for:
CPU Bottlenecks
Cause: Insufficient hardware resources
Symptoms: Constantly high processor usage
Metrics to monitor: % Processor Time, Batch Requests/Sec, SQL Compilations/Sec and SQL Recompilations/Sec
Memory Bottlenecks
Cause: Limitations in available memory and memory pressure caused by SQL Server, system or other application activity
Symptoms: Slow application responsiveness, overall system slowdown and application crashes
Metrics to monitor: Memory Available (KB), Total Server Memory (KB), Target Server Memory (KB), Pages/Sec, Checkpoint Pages/Sec, Lazy Writes/Sec and Buffer Cache Hit Ratio
I/O Bottlenecks
Cause: Excessive reading and writing of database pages from and onto disk
Symptoms: Long response times, application slowdowns and task time-outs
Metrics to monitor: Average Disk Queue Length, Average Disk Sec/Read, Average Disk Sec/Write, %Disk Time, Average Disk Reads/Sec and Average Disk Writes/Sec
Tip #3: Ensure indexes are properly designed
Indexes are a great way to speed up certain SQL Server operations, but only if they are well designed. Poorly designed indexes have the opposite effect and are a sure way to kill your SQL Server performance.
Properly setting up these four areas can help ensure indexes are properly designed and help rather than hurt SQL Server performance.
Table size
Not every table is a good candidate for indexing. In fact, if a table is too small, it’s way more efficient for SQL Server to search the entire table versus having to search through indexes. Of course, the opposite is true for large tables, so you need to weigh potential overhead when deciding which tables would benefit from indexes.
Index types
Technically, every database table can have one clustered index and an infinite number of non-clustered indexes, but you know what they say about “Just because you can do something”…
Too many non-clustered indexes can significantly slow down Insert and Update operations, so sticking to one clustered index and the minimum number of absolutely essential non-clustered indexes is a far better design choice.
Index storage
During the design phase, selecting the proper storage criteria for indexes is crucial to I/O performance. Partitioned clustered indexes and non-clustered indexes can be stored on the same filegroup as the main table, or they can be stored on a different filegroup. Storing a non-clustered index in a filegroup located on a different disk drive can improve the performance of queries that use it, because it isn’t affected by the concurrent reading of the data and SQL index pages occurring on different disk drives.
FILLFACTOR
FILLFACTOR specifies the percentage of space that will be filled on each data page when creating an index. FILLFACTOR values can range from 0 percent (none of the data page is filled) to 100 percent (the data page is completely filled). When designing your index, select a FILLFACTOR value that will optimize page usage while minimizing the risk of excessive index fragmentation.
Making SQL Server performance tuning a part of your standard routine is an excellent way to ensure your databases run at peak performance. Incorporating these three simple steps into your regular SQL Server maintenance plans will noticeably improve speed and performance for your users.
