SQL Server Performance Tuning

What Is SQL Server Performance Tuning?

SQL Server performance tuning is the process of determining the cause of observed or reported SQL Server issues and implementing the necessary changes to optimize the performance of your SQL Server database, application, OS, and hardware. To do so, you will need to make updates to some or all of the following aspects of your SQL Server environment:

Transact-SQL code in stored procedures, functions, queries, and other programmable database objects
Execution plans
Database tables (columns and rows)
Indexes
Joins
SQL Server and OS configuration settings
Hardware, memory, I/O subsystems, and physical configuration details

Performance tuning can be time-consuming, complex, and frustrating. One change or correction can result in repercussions and problems elsewhere. That’s why most data professionals turn to performance tuning tools.

Performance Tuning Terms You Need to Know

Query optimization - Query tuning is a top priority for DBAs. Poorly written queries can cause numerous problems and severely slow performance. Some queries that worked fine when a database was smaller can cause issues as the database grows. Regular analysis and fine-tuning can address such problems.

Execution plans - SQL execution plans tell SQL Server queries how to carry out tasks in a database. As your data environment changes, you should review and revise your execution plans so that queries are operating as efficiently as possible.

Indexing - Indexes affect read performance in SQL Server. They can improve performance but also require maintenance and tuning.

Baseline - Establish a baseline so that you can more easily determine when and by how much performance deteriorates or improves.

The Benefits of Proactively Tuning SQL Server Performance

Performance tuning helps solve the following issues, some of which can have severe repercussions:

I/O bottlenecks
Fragmentation
Slow response times
Instability

The benefits of resolving these issues include:

Reducing time spent resolving tickets
Gaining time to spend on new or more important projects
Improving user satisfaction
Speeding response times
Making more efficient use of storage and server space
Reducing hardware and storage costs
Ensuring accurate query results

Get a grip on execution plans and optimize SQL Server query performance with the free SolarWinds Plan Explorer tool and this free eBook.

Where—and How—to Begin Tuning SQL Server Performance

Ready to start fine-tuning your SQL Server database environment?

Begin with regular and comprehensive performance monitoring. Your goal is to frequently and regularly evaluate the health of each database and the performance of your SQL Server systems, both physical and virtual. By assessing factors such as user activity and complaints, query execution, and response times, you can determine where improvements are needed.

Troubleshooting SQL Server Performance Ebook Cover

In this four-part eBook, Microsoft MVP Kevin Kline takes you through the process of identifying and resolving SQL Server performance problems using native SQL Server tools.

Do:

Ensure that the problem is actually caused by SQL Server, not external factors such as networks, permissions, or security.
Eliminate the possibility of errors causing the performance issue.
Assess and correlate wait stats with related performance information, such as I/O bottlenecks.
Identify problem queries.
Keep an eye on index usage.

After you identify problems, you need to do some detective work to align each problem with the queries, execution plans, tables, indexes, joins, SQL Server and OS settings, and hardware that correlate with the issue. Then, you need to evaluate the code or components to determine why they aren’t working correctly and how to repair or improve the issue without causing unintended consequences elsewhere in the database or server.

Don’t:

Forget to look for possible upstream and downstream repercussions.
Neglect to test changes before implementing them.
Get bogged down with a piecemeal, manual approach.

SQLsentry

SolarWinds SQL Sentry

With SQL Sentry, our installed software solution, you can effectively monitor, diagnose, and optimize your entire database environment.

What to Look for in a Performance Tuning Tool

SQL Server includes some built-in monitoring and performance tuning tools. However, when you manage databases across many systems, these tools might not provide the advanced functionality you need to optimize your database environment.

When evaluating third-party monitoring and performance tuning tools, look for the following capabilities:

A user-friendly GUI and dashboards
A clear view of both historical and real-time data, making it easier to correlate performance issues with potential causes
Insight into your host system and host processes
The ability to set custom baselines, baseline-derived thresholds, and custom alerts to create a complete change management and notification system
Insight into query execution plans
Comprehensive reporting that you can use to gain the necessary buy-in to pursue an issue
High-impact query insights across the entire Microsoft Data Platform, including on-premises, hybrid, and cloud environments
Support for multiple operating systems
Index analysis and defragmentation capabilities
Industry-leading support

Finally, make sure you understand the potential overhead of any tool that you use. Some tools can create performance issues of their own.

Want your next technology purchase request to be approved by your business decision makers? In this free, on-demand webinar, SolarWinds Head Geek Kevin Kline will teach you a time-tested methodology for proposing technology purchases and other ideas that win approval from your business decision makers and improve your life as a data professional.

What Causes SQL Server to Run Slow or Have Performance Issues?

Slow Queries

Slow running queries make up their share of performance issues. Our fully integrated Plan Explorer makes the entire process of query tuning fast and easy.

Virtualized Resource Contention

SQL Server is part of a database system. Not every slow SQL Server performance issue is caused directly by SQL Server. See how your virtualized environment (VMware or Hyper-V) impacts SQL Server performance.

Deadlocks, Bottlenecks, Indexes, Too Many Scheduled Events

Slow SQL Server performance could be caused by a number of factors. Our database performance monitoring solution allows you to see across everything required to monitor, diagnose, and optimize SQL Server and resolve performance issues.

“The more that I use SQL Sentry, the more I fall in love with it. #SQLSentry #LoveStory”

—John Morehouse, Consultant at Denny Cherry & Associates

Try SQL Sentry Today!

Download your free, 14-day trial to see for yourself how SQL Sentry can help you achieve optimal database performance.

SQL Sentry Benefits

Comprehensive Performance Metrics

Resolve SQL Server performance tuning issues quickly and efficiently with side-by-side real-time and historical views in SolarWinds SQL Sentry.

Easily examine historical date ranges to quickly identify potential performance issues. Then, correlate those issues with problem queries, blocks, or deadlocks, and begin performance tuning in SQL Server to resolve those issues as quickly as possible.

Alerts Designed for You

Build alerts specific to the needs of your environment to keep you informed and on top of performance problems so that you can take action right away.

With easy to use point and click Advisory Conditions, you can create performance-based alerts and apply them to any and all servers in your environment.

Powerful and Integrate Baseline Metrics

Pinpoint changing performance metrics and begin performance tuning in SQL Server before issues ever become a problem.

Custom baselines are easily configurable and combined with alerts, they are a complete change management and notification system. You can even create them on demand for any specific time range.

High Impact and Long Running Statement Details

When looking for information to help with performance tuning in SQL Server, Top SQL captures all of your worst performing and high-impact stored procedures, statements, and batches.

The low-overhead trace runs with minimal performance impact to your environment. Quickly identify potential problems by viewing multiple query executions over a historical time range, and drill into individual execution plans.