Share via

SQL Server Performance Survival Guide

  • Article

Troubleshooting performance problems on a database system can be an overwhelming task. The reasons for poor performance can range from a poorly designed database to a system that is improperly configured for the workload. As an administrator, you want to proactively prevent or minimize problems, and when they occur, you want to diagnose the cause and take corrective actions to fix the problem whenever possible - there is a vast array of deep technical discussions about SQL Server performance, and content about how to troubleshoot performance issues. We seek to point to the best content on the Web to enable you to find the information you need to learn how to improve and troubleshoot performance. If it's related to SQL Server performance, we'll try to point to it!

Note that throughout this guide, we point to performance content for SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012. Most of the guidance written for SQL Server 2005 applies to later versions of SQL Server – but if we point to SQL Server 2005 content that doesn’t apply to later versions, (and vice versa) we’ll point it out.

This Performance Survival Guide is a continual work in progress, so please add your own performance tips and troubleshooting advice. Just click the "Edit" button above this article to add or improve the existing content.

Note:
Throughout this guide, the opinions and views expressed in links to non-Microsoft articles, marked with an icon (&), are those of the author and do not necessarily state or reflect those of Microsoft.

Learning about Troubleshooting SQL Server Performance

Follow the links in this guide to learn about how to diagnose and troubleshoot SQL Server performance issues. We point to SQL Server performance related articles, tips & tricks, whitepapers, useful tools, social networking sites, and more!

Note that this guide focuses on SQL Server core engine performance and not the Business Intelligence technologies. For detailed information about Analysis Services performance, see the SQL Server 2008 R2 Analysis Services Operations Guide and the Analysis Services 2008 R2 Performance Guide. Additionally, the following links contain some excellent articles written by the SQLCAT team to help you troubleshoot your Integration and Analysis Services performance issues.

Transaction Processing (OLTP)

Online transaction processing (OLTP), workloads are characterized by high volumes of similar small transactions. You need to keep this in mind when you are trying to troubleshoot resource utilization and system performance. The following resources provide performance information for OLTP applications:

Data Warehouse Performance

Data warehouses are characterized by queries that scan larger numbers of rows, large ranges of data and may return relatively large results for the purposes of analysis and reporting. Most relational technologies are not tuned to deal with the large-query and data-loading workloads of data warehousing. The following links provide some general information and best practices about how to make the database engine work more efficiently.

Troubleshooting Hardware Issues

Following are some links to help you get the most out of SQL Server and your hardware:

 
& Denotes Non-Microsoft articles.

 

Monitoring and Performance Tools

This section lists the publicly available tools you can use to diagnose and troubleshoot SQL Server 2012 performance problems in common customer scenarios.

  • Activity Monitor. Explains the benefits of activity monitoring, and how to set the refresh interval to configure how often Activity Monitor obtains new information about instance activity.
  • Data Collector. Provides information about how to use Data Collector to use data collection containers enabling you to determine the scope and frequency of data collection on a computer that is running SQL Server.
  • Event Notifications. Provides the information that is required to understand, design, and implement event notifications.
  • Log File Viewer. Provides guidance around using Log File Viewer to access information about errors and events that are captured in the following logs: Data Collection, Database Mail, SQL Server, SQL Server Agent, and Job History.
  • Resource Governor. Introduces Resource Governor and explains how you use it to specify limits on the amount of CPU and memory that incoming application requests can use.
    Note:
    Resource Governor is available in SQL Server versions 2008 and later.
  • SQL Server Profiler. Explains how to capture and save data about each event to a file or table that you can use to analyze at a later time.
    Note:
    We are announcing the deprecation of SQL Server Profiler for Database Engine Trace Capture and Trace Replay. These features will be supported in the next version of SQL Server but will be removed in a later version.
  • SQL Server Extended Events. Describes how to use Extended Events to easily and quickly create, modify, display, and analyze your session data.
    Note:
    SQL Server Extended Events is available for performance monitoring in SQL Server versions 2008 and later.
  • SQL Trace. Explains how to use Transact-SQL system stored procedures to create and run traces.
  • Trace and Replay Objects: A New API for SQL Server Tracing and Replay. Introduces Trace and Replay objects, which is a managed API for tracing, trace manipulation, and trace replay.
  • System Monitor. Explains how to use System Monitor to measure SQL Server performance, allowing you to view SQL Server objects, performance counters, and the behavior of other objects, such as processors, memory, cache, threads, and processes.
  • Tuning Advisor. Provides features that enable both novice and experienced database administrators to tune databases for better query performance.
  • CodePlex. CodePlex is an open source project community that is hosted by Microsoft. Microsoft does not control, review, revise, endorse or distribute the third party projects on this site. Microsoft is hosting the CodePlex site solely as a web storage site as a service to the developer community.
  • Windows Sysinternals. Whether you’re an IT Pro or a developer, you’ll find Sysinternals utilities to help you manage, troubleshoot and diagnose your Windows systems and applications
  • Performance Monitor. Explains how to use Performance Monitor to help identify and monitor key performance counters.
  • sys.dm_exec_cached_plans. Explains how to use the sys.dm_exec_cached_plans dynamic management views (DMVs) to monitor the performance of your stored procedures.
  • SQL Server Performance Coding Standards. List the SQL Server performance coding standards.

Performance Tips and Tricks

This section contains some useful links to articles that contain tips and tricks that you can use to help improve your SQL Server performance. Everyone likes tips and tricks, so please add your own by clicking the "Edit" button above this article!

Note that the following are not Microsoft articles and the opinions and views expressed are those of the author and do not necessarily state or reflect those of Microsoft.

Performance Topics in SQL Server Books Online

For a more general set of SQL Server documentation that includes information about monitoring and managing database engine performance, see the following topics in SQL Server Books Online.

Getting Advice and Guidance 

The Web contains a rich set of advice and guidance for troubleshooting performance - from learning the basics, to the hard to find corners most people don't go to. Here are what the community thinks are the best:

SQL Server Customer Advisory Team

The SQL Server Customer Advisory Team (SQLCAT) team manages all customer activity out of the SQL Server engineering organization and engages with the most interesting SQL Server customer projects across the world.

Community Resources

SQL Server Performance Blogs

There are a number of blogs that contain a log of great information about troubleshooting SQL Server performance. Following are some blogs you might want to check out.

Note that the opinions and views expressed in non-Microsoft blogs are those of the author and do not necessarily state or reflect those of Microsoft.

Microsoft Corporation Blogs

MVP Blogs

  • Aaron Bertrand. Aaron is a senior consultant for SQL Sentry, Inc., makers of performance monitoring and event management software for SQL Server, Analysis Services, and Windows. He has been blogging at sqlblog.com since 2006, focusing on manageability, performance, and new features. He has been a Microsoft MVP since 1997, and speaks frequently at user group meetings and SQ: Saturday events.
  • Ashish Kumar Mehta.
  • Glenn Berry’s SQL Server Performance. Glenn is a database architect at Avalara in Denver, Colorado, and has been a SQL Server MVP since 2007.
  • Greg Larson. Greg has been working with SQL Server since 1999, and has been working as a DBA on one platform or another since 1985. He currently holds a MCTS: SQL Server 2005 certification and is one test away from getting his MCITP: Database Administrator certificate. Greg is a PASS chapter leader for the Olympia Area SQL Server User Group.
  • Kimberly Tripp. Kimberly is a SQL Server MVP, and a Microsoft Regional Director. Currently, Kimberly is focused on SQL Server 2008 content for conferences and workshops around the world. She has worked as a co-author/editor helping to put together the Index Internals chapter for SQL Server 2008 Internals and SQL Server MVP Deep Dives, which is a collection of best practices from MVPs around the world.
  • Maciej PileckiMaciej Pilecki. Maciej is an Associate Mentor with Solid Quality Mentors, a global organization that specializes in training, mentoring, and consulting. He is a Microsoft Certified Trainer (MCT) and Microsoft MVP and frequently delivers courses and conference speeches on many aspects of SQL Server and application development.
  • Paul Randal. Paul is the managing director of SQLskills.com, a Microsoft regional director and a SQL Server MVP. He worked on the SQL Server Storage Engine team at Microsoft from 1999 to 2007. He was responsible for the Core Storage Engine during SQL Server 2008 development and is an expert on disaster recovery, high availability and database maintenance, and is a regular presenter at conferences worldwide.
  • Zach Nichter. Zach is a SQL Server professional with more than 10 years of experience. He has held a number of SQL Server support roles including DBA, team lead, manager, and consultant. Currently, Zach is employed by Levi Strauss & Co. as the DBA Architect, focusing on SQL Server performance, monitoring, and architecture as well as other strategic initiatives.
    In German
    • Frank Kalis. Den Teilnehmern der Microsoft SQL Server Foren ist Falk bestens bekannt durch seine fachkundigen und fundierten Antworten auf viele Fragen.

Community Blogs

  • AmruthaVarshiniJ.
  • THE SQL Server Blog Spot on the Web. Blogs about SQL Server, T-SQL, CLR, Service Broker, Integration Services, Reporting, Analysis Services, Business Intelligence, XML, SQL Scripts, best practices, database development, database administration, and programming.

MSDN Blogs

  • Bart Duncan. Bart is a senior software design engineer and focuses on blogging about what he finds most interesting -- SQL Server performance.
  • Buck Woody. Buck Woody is a Senior Technical Specialist for Microsoft, working with enterprise-level clients to develop computing platform architecture solutions within their organizations. With over twenty-five years professional and practical experience in computer technology, he is also a popular speaker at TechEd, PASS and many other conferences; the author of over 500 articles and five books on databases; and teaches a Database Design course at the University of Washington.
  • Conor Cunningham. Conor blogs on SQL Server, data-driven applications, and pretty much whatever other random stuff he decides to post.
  • Mike Wachal. Mike is a program manager in the SQL Server group. He has spent a lot of time working on desktop database systems including Access and SQL Express. He’s working on diagnostic systems now, primarily SQL Server Extended Events.
  • Tony Voellm. Tony is the Principle Development Lead in the Windows Virtual Machine Team.

SQL Server Performance Forums

Do you have a SQL Server question that needs answering? Forums are an excellent place to ask questions and find answers to your SQL Server performance issues.

SQL Server 2008 Forums

SQL Server 2005 Forums

SQL Server Resource Centers

The SQL Server Resource Centers connect users to the help content, how-to information, downloads, installation information, community, social media, and other online resources that help you deploy, maintain, and support SQL Server.

SQL Server Performance Workshops

To learn useful, practical techniques to help you improve your SQL server performance skills, you can register for the following performance workshops, or download copies of the workshop videos.

SQL Server Social Networking

 

Twitter

You can tweet with some of the best minds in the SQL Server community. Following are some of the active Microsoft SQL Server employees on Twitter. For a more comprehensive list of tweeters in the Microsoft SQL Server community, see SQLServerPedia, a community knowledge base.

SQL Server Performance on Facebook

SQL Server Performance Books

See Also