At WARDY IT Solutions, we’ve lived and breathed SQL Server for the past 14 years. With more than 100 years of SQL Server experience between them, we asked our Data Platform consultants to share their opinions on the most common causes of performance problems and of course, how to avoid them. Happy performance tuning!
Start by checking your wait stats
Now this isn’t a cause but if you hit a performance problem, checking your wait stats is the place to start. SQL Server continuously tracks why execution threads have to wait. This information is vital when you’re trying to pinpoint the root cause of your performance problems. The good news is finding the cause of your performance problem is easy, the bad news is interpreting the information and fixing the issue is often complicated and time consuming but this will point you in the right direction. If your environment is anything like most, the chances are wait stats will point you in one of these five directions:
1. Ensure your TempDB database is configured optimally.
TempDB configuration is a repeat offender when it comes to performance bottlenecks. Look out for multiple data files and check that trace flags 1117 and 1118 are enabled.
Note: If you’re running SQL Server 2016 or above, trace flags 1117 and 1118 are enabled as standard.
Tip:
- PAGELATCH_XX wait types are an indicator of tempdb contention, further troubleshooting is required
- For machines with up to 8 CPUs, the general rule of thumb is that you should have the same number of tempdb data files as you have logical processors https://support.microsoft.com/en-us/help/2154845/recommendations-to-reduce-allocation-contention-in-sql-server-tempdb-d
- Place your tempdb files on dedicated high-speed disks
- Grow your tempdb data files to a desired size
- Set your files to grow equally
- Enable IFI
2. Make sure you’re running index maintenance frequently.
It’s difficult to know how often you should be running index maintenance as it’s dependant on your workload and how quickly your data changes. If you’re unsure what ‘frequently’ means for you, start by checking for index fragmentation on a daily basis. If it takes a week for fragmentation to reach 30% or more, schedule in weekly index maintenance. If they fragment sooner, you should try to schedule a couple of index maintenance sessions a week, if not daily – this should include updating statistics.
Tip: If you don’t have an index maintenance plan, try using a tool like SQL Server Maintenance Solution by Ola Hallengren. This free script dynamically determines the indexes that require maintenance. We run this tool in every SQL Server we manage – it’s a great place to start.
3. Implement indexes that provide a benefit to your queries.
This involved creating missing indexes and removing poor performing or unutilized indexes. Having a large number of indexes can be detrimental to DML transactions. Appropriate indexing means only creating indexes which have a positive impact to your read performance that outweigh the negative impact on write performance which requires a good understanding of your workload.
This script will help you identify missing indexes:
And this script will find poor performing indexes:
4. Check your most expensive queries and stored procedures.
Identify which queries and stored procs have the most IO, CPU usage and are taking the longest to run and see if there’s room for improvement. Start by working on those that will give you the best performance improvement for the least amount of effort. There aren’t many of us who enjoy this and it’s not always possible but it’s certainly worth a check.
5. Monitor your performance counters
Even if you manage to solve your immediate issue by following the steps above, we recommend monitoring your performance counters to isolate the deeper issues. Doing this on a regular basis will ensure you have a targeted performance tuning approach and will help you spot issues before they escalate. Monitoring your counters when your environment is healthy will give you a good baseline.
If you’re not a Virtual DBA customer, you could try using a free tool like the Performance Analysis Log (PAL). PAL enables you to produce a template for collecting your performance counters, collect 24 hours worth of data and produce a report. It uses generic thresholds to give you an indication of why you might have performance issues. The report includes stats on every counter making it time consuming to work through. It helps if you know your environment so you can focus on the most critical areas. Learn more about using the PAL tool and or download PAL from GitHub.
SQL Server Healthcheck
If these tips don’t help you or if you don’t have the time to investigate the underlying issue, it may be worth considering a SQL Server Healthcheck. During a healthcheck, our Data Platform consultants carry out a comprehensive examination of your SQL Server environment. We’ll highlight areas of concern and provide actionable recommendations to improve the performance and availability of your SQL Server infrastructure.
Start your SQL Server Safety Check