SQL Server Performance Tuning

Tuning your SQL Server databases and systems is essential to ensuring optimal data performance.

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

Plan Explorer Query Optimization Ebook Cover

Get a grip on execution plans and optimize SQL Server query performance with the free SentryOne Plan Explorer tool and this free eBook from Aaron Bertrand, Paul White, Greg Gonzalez, and Rob Farley. 

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.

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.

 

 

Building A Business Case For SQL Server Monitoring Solutions Webinar

Want your next technology purchase request to be approved by your business decision makers? In this free, on-demand webinar, SentryOne Principal Program Manager 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 SentryOne SQL Sentry, the more I fall in love with it. #SQLSentry #LoveStory”

—John Morehouse, Consultant at Denny Cherry & Associates

Why Should You Give SentryOne a Try?

Improve Database Performance

When it comes to tuning your SQL Server environment, SentryOne makes it easy to determine the root cause of  database performance problems.

Regular Updates and New Features

With the continual innovation of our products, featuring more than five times as many new releases as our nearest competitor, you can rest assured that features and functionality will continue to match the needs of the SQL Server community.

Low Overhead

SentryOne software is built with performance in mind. Our architecture is agent-less, thus creating as minimal an impact as possible on your environment. Other tools might create more of a performance load than the issue you are trying to resolve. 

Lower Cost and Higher Return on Investment

With SentryOne software, you will save time by reducing the amount of work required to get to the root of performance problems and be able to spend more time proactively managing your database environment. Our white paper proves it.

World-Class Customer Support and Credibility

SentryOne provides world-class support from our development centers in the USA and Europe. Our highly skilled support engineers have real-world SQL Server experience and are not constrained by tight time-limits when giving you the help you need. We have been a Microsoft Gold Certified Partner for years and take great pride in our customer survey results that rank us consistently in the highest percentiles of the entire software industry. View our survey results here.

 

 

 

Join Lori Edwards, SentryOne training manager, for a walk-through of SentryOne—the all-in-one platform that allows data professionals to monitor their SQL Server environment easily, quickly diagnose issues, and get to the optimal performance. 

 

Try SentryOne SQL Sentry Today!

Download your free, 15-day trial of the SentryOne Platform, which includes SQL Sentry and other premier SQL performance monitoring and tuning tools for the Microsoft Data Platform.

Download Trial

 

SentryOne Benefits

Comprehensive Performance Metrics

Resolve SQL Server performance tuning issues quickly and efficiently with side-by-side real-time and historical views in SentryOne 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 Conditionsyou 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.