Diagnosing and Troubleshooting SQL Server Performance Problems

Microsoft Professional Advisory Services is a support option that provides short-term, proactive, consultative support beyond break-fix product maintenance needs. This includes working with the same technician for assistance with issues like product migration, code review, or new program development and is a remote, phone-based support option. This service is typically used for shorter engagements, and is designed for developers and IT professionals who do not require the traditional onsite consulting or sustained account management services that are available from other Microsoft support options.

For additional information on Microsoft Advisory Services, including on how to engage, refer to this Microsoft web page:

http://support.microsoft.com/gp/AdvisoryService (http://support.microsoft.com/gp/AdvisoryService)

This Advisory Services scenario is designed to assist customers who help diagnosing and troubleshooting general performance issues and are asking for recommendations on how to resolve these issues.

Microsoft SQL Server Support Engineers will work with you using tools to diagnose and troubleshoot Microsoft SQL Server Performance issues. Tools that the engineer may use to collect the necessary information to identify the performance issue can include SQL Server Profiler, Performance Monitor (Perfmon), PSSDiag and others. These tools may be used to capture data that will give the engineer insight to the performance bottleneck. In this advisory service, the Support Engineer will provide recommendations to improve performance based on their findings and analysis.

Questions and information that Support Engineers may ask or need for this service.

General Information Needed:

• SQL Version (2000, 2005, 2008), Edition (Std, Ent, Exp)
• Service Pack Level with Build
• CPU (x86, x64, IA64)
• Windows Version (2000, 2003, 2008) and Edition (Std, Ent)
• Clustered (yes/no)
  • What type of clustering? (MS Windows, Veritas)
• Virtualization (yes/no)
  • What type of virtualization? (MS HyperV, VMWare)

How is SQL implemented?

(Client Application, Web application, no front-end app)

What is slow?

• A particular query, stored procedure, SQL job?
  • Is performance the same regardless of the parameters you pass to the query/stored proc?
• Replication?
• Administrative processes (Backups, Re-Indexing)?
• If application-related, is it a specific window/form, button, web page, link, etc?
• Have you narrowed it down to one process/object causing the "slowness"? If so, have you identified the T-SQL that is executing in conjunction with that process?
  • One specific query?
  • One job/batch of queries?
  • Slow hard disk / slow IO subsystem
  • CPU is high/pegged to 100%
  • Low memory (RAM is exhausted)
• If data is on a SAN, are other applications/servers connected to the same SAN having performance issues as well (may immediately point to SAN issues).
• Is it one particular task or process in the application? (Please include name of process or task so we can refer to it by a name)
  • Have you isolated the T-SQL code that is executing in conjunction with the application process?
  • Do you see the same performance if you execute directly from SQL Server Management Studio?
• Or is it general/overall application response time?

How do you determine that things are slow?

• " Has it always been slow since initial implementation?
  • If not, can you point to a particular time when the performance problem first occurred?
    • Were there any changes made to the environment (code changes, schema changes, added users, etc) at the time?
  • If yes, why do you expect it to perform faster than it does?
• What does the end user see or how do they know it is slow? (application timeouts, slow webpage load time)
• It is slower compared to…?
  • Previous performance?
  • Performance on another system?
• Are you judging performance based on (web) application performance, or by directly observing SQL's performance? What tools and data did you use to determine the slowness?
  • Execution times or query duration?
  • Did you use Performance Monitor - what objects and counters?
  • Did you use a Profiler trace to see query duration and CPU usage? What events did you collect and look at?
  • Did you use any Dynamic management views (DMVs) to discover the problem? Which DMVs did you use and what columns did you look at?
  • Did you use any third-party monitoring tools to establish that? How does the tool gather data - what data?
• Do you have benchmark for "acceptable"/expected performance?

Is the performance issue easily reproducible or intermittent in nature? If it is intermittent, does it happen…

• At a specific time of day? Day of the week? Time of the month?
• Does it happen during a time you know the server is under heavier than normal load?
• Only for specific users?

Have you found any way to resolve the performance issue (even if temporarily)?

• Is the performance issue resolved by:
  • A restart of SQL Server service?
  • Rebooting the server?
  • DBCC FREEPROCCACHE?
  • Updating statistics and/or rebuilding indexes?

Other questions

What is your current maintenance plan for indexes and statistics?

Are there multiple instances of SQL installed on this server?

Is this server a dedicated SQL Server box?

If necessary, could you supply a copy of the database to MS Support for in-house reproduction of your issue?

Below is a list of self-help resources or this scenario. These resources may also be used by Microsoft Support Engineers during an Advisory Services engagement.

Whitepapers and Microsoft Knowledge base articles

Troubleshooting Performance Problems in SQL Server 2005

This paper provides step-by-step guidelines for diagnosing and troubleshooting common performance problems by using publicly available tools such as SQL Server Profiler, System Monitor, and the new Dynamic Management Views in SQL Server 2005.
http://technet.microsoft.com/en-us/library/cc966540.aspx (http://technet.microsoft.com/en-us/library/cc966540.aspx)
Troubleshooting Performance Problems in SQL Server 2008

This paper provides step-by-step guidelines for diagnosing and troubleshooting common performance problems by using publicly available tools such as SQL Server Profiler, System Monitor, and the new Dynamic Management Views in SQL Server 2005.
http://download.microsoft.com/download/1/3/4/134644fd-05ad-4ee8-8b5a-0aed1c18a31e/TShootPerfProbs.doc (http://download.microsoft.com/download/1/3/4/134644fd-05ad-4ee8-8b5a-0aed1c18a31e/TShootPerfProbs.doc)
Statistics Used by the Query Optimizer in Microsoft SQL Server 2008

This paper describes what data is collected, where it is stored, and which commands create, update, and delete statistics.
http://msdn.microsoft.com/en-us/library/dd535534.aspx (http://msdn.microsoft.com/en-us/library/dd535534.aspx)
Scaling Up Your Data Warehouse with SQL Server 2008

This paper discusses how to use SQL Server 2008 to get great performance as your data warehouse scales up.
http://msdn.microsoft.com/en-us/library/cc719182.aspx (http://msdn.microsoft.com/en-us/library/cc719182.aspx)
Improving Performance with SQL Server 2008 Indexed Views

This document describes the indexed views capability of SQL Server 2005 and SQL Server 2008, including the new support for partition-aligned views added to SQL Server 2008.
http://msdn.microsoft.com/en-us/library/dd171921.aspx (http://msdn.microsoft.com/en-us/library/dd171921.aspx)
Working with tempdb in SQL Server 2005

This white paper describes how SQL Server 2005 uses tempdb. Many improvements in SQL Server 2005 optimize tempdb usage and make it easier to manage and to troubleshoot.
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/WorkingWithTempDB.doc (http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/WorkingWithTempDB.doc)
SQL Server 2000 IO basics

Learn the I/O requirements for Microsoft SQL Server database file operations. This will help you increase system performance and avoid I/O environment errors. Applicable to SQL 2005 and 2008.
http://technet.microsoft.com/en-us/library/cc966500.aspx (http://technet.microsoft.com/en-us/library/cc966500.aspx)
Disk Partition Alignment Best Practices for SQL Server

This paper documents performance for aligned and nonaligned storage and why nonaligned partitions can negatively impact I/O performance; it explains disk partition alignment for storage configured on Windows Server 2003, including analysis, diagnosis, and remediation; and it describes how Windows Server 2008 attempts to remedy challenges related to partition alignment for new partitions yet does not correct the configuration of preexisting partitions.
http://technet.microsoft.com/en-us/library/dd758814.aspx (http://technet.microsoft.com/en-us/library/dd758814.aspx)
INF: Understanding and Resolving SQL Server Blocking Problems

This article discusses how to troubleshoot and resolve blocking problems.
http://support.microsoft.com/kb/224453 (http://support.microsoft.com/kb/224453)