Tuning their systems for optimal performance is one of the main responsibilities of database administrators. It can often be a difficult task to find the right ways to satisfy the user demands put on their SQL Servers. Numerous moving parts can be tweaked in an attempt to attain faster response time. The challenge lies in finding the right place to start.
Pinal Dave is a well-respected authority on all things related to SQL Server. He is the guest presenter for an IDERA Geek Sync Webcast that goes into detail concerning the most relevant SQL Server performance tips that will make a real difference to your systems. We are going to take a brief look at what Pinal discusses in this video as an appetizer that should get you motivated to watch the complete presentation. It’s an hour well-spent that will point you in the right direction when searching for SQL Server performance gains.
Where to Start?
Faced with a poorly performing SQL Server database, a DBA is often challenged to select the right optimization techniques. Once the problem queries have been identified, the correct methods need to be used to fix them. Some areas deserve immediate attention as they may substantially increase database performance.
- Incorrect index usage is one of the leading causes of inefficient query execution. While indexes certainly have their place in SQL Server databases, it is important to use them wisely. Having too many indexes on tables that experience a lot of inserts or updates will result in excessive execution time and slower response. Queries that take an extended time to complete also lead to resource locking. Identifying unused or little-used indexes and eliminating them can lead to a nice bump in performance.
- An often-overlooked parameter change that can have a great impact on query execution is the COMPATIBILITY_LEVEL setting. This parameter controls the feature set a query uses based on the SQL Server version specified. When upgrading to new versions of SQL Server, this parameter is not changed. This is to ensure that nothing gets broken if you are using features that are not carried over to the newer version of the platform. You may well have outdated settings for this parameter that need to be updated to reflect your current SQL Server environment. You can dynamically change this parameter without the need to restart the database.
- In the world of SQL Server parallelism, more is not always a good thing. While your first impulse may be to allow parallel queries to use the maximum number of processors available, this is often not the most effective approach. The MAXDOP parameter that designates the maximum parallel processes that a query can execute can also be changed dynamically which makes it very useful for testing different configurations. Testing your ten most executed queries with various MAXDOP settings will usually indicate that a number between two and four provides the best results and shortest wait times. Simply throwing all of your processors at SQL queries may prove to be counterproductive.
Finding Out Where the Problems Originate
Having potential solutions to SQL Server problems is all fine and good, but you need to know where should you direct your attention. Randomly selecting queries to tune will usually not lead to the desired results. What you need is the insight into the inner workings of your SQL Servers that can be provided by a comprehensive monitoring tool.
IDERA’s SQL Diagnostic Manager for SQL Server monitors, alerts, diagnoses, and reports on SQL Server performance and availability. It can help a database team identify the problem queries that are locking system resources and causing excessive wait times. The tool monitors standard SQL Server metrics as well as hardware and session information. Long-running and high-impact queries can easily be identified for closer investigation.
DBAs can drill down on reported wait states to find out which sessions and statements are causing a workload spike. Historical data enables forensic investigation to help eliminate recurring problems that may be plaguing your systems. Informative reports analyze server performance and provide prioritized recommendations for improvements.
Using the information available from SQL Diagnostic Manager for SQL Server and the techniques that Pinal Dave illustrates during his demonstration, you can identify and address performance issues that are slowing down your system and leading to unsatisfied users. There’s no excuse to keep your users waiting for query results. It’s time to tune the SQL Servers!