SQL Server 2012 : Troubleshooting Methodology and Practices - Data Analysis, Validating and Implementing Resolution

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
1/1/2015 8:33:26 PM


After data collection, data analysis is the second iterative activity required to identify the problem’s root cause. It may be necessary to perform data analysis multiple times for a single problem, including data from multiple sources and formats. The typical starting point is to review PerfMon output to identify bottlenecks or contention with I/O, CPU, memory, or the network resources. Often, once the main bottleneck is resolved, another will appear. At this point it is important to understand the success criteria and SLAs to have a clear goal and know when to stop troubleshooting.

The following list describes several tools and utilities you can use to analyze the data collected:

  • SQL Nexus — This tool, available from, imports data into a SQL Server database and generates reports showing the most common performance issues based on that data. It takes Profiler, PerfMon, and PerfStats as input. For example, it can show all statements captured in the trace that were blocked for more than 30 seconds. SQL Nexus is commonly used to show the stored procedures or queries that had the highest duration, were executed most frequently, or used the highest cumulative CPU. With stored procedures, it is possible to drill down to get more specific information on duration and other statistics about individual queries within the stored procedure. SQL Nexus is a great tool for taking a large amount of data and quickly locating pain points that require more thorough examination.
  • Profiler — Profiler can be used to replay statements if the right events were captured. A Replay template built into Profiler can be used to capture those events. This is useful to test the same queries repeatedly against a database to which changes have been made, such as modifying indexes and altering file layout. The impact/benefits of these changes can be measured relative to the overall workload using this consistent set of replay workload. Profiler can also be useful for troubleshooting security and connectivity problems.
  • PerfMon — Performance Monitor can be used to isolate CPU, memory, I/O, or network bottlenecks. Another use is to help determine whether SQL Server is the victim of another process (such as anti-virus apps or device drivers), consuming resources such that SQL Server performance is affected.
  • Database Tuning Advisor (DTA) — The DTA can take as input either an individual query or an entire trace workload. It makes recommendations for possible index or partitioning changes that can be tested. Never implement suggestions from DTA without thorough review in the context of the total workload. Analysis with DTA is most effective when a complete workload can be captured in a trace and processed.
  • SQL Server Data Tools — Provides an integrated environment for developers to create, edit and deploy database schemas. A full discussion of the product is well beyond the scope of this section.
  • Debugger — It is possible to debug stored procedures from SQL Server Management Studio beginning with SQL Server 2008.

Performance problems are rarely caused by a single large query executing on a server. More often, the query with the highest cumulative cost is a relatively short and fast query, but one that might be executed thousands of times per minute. A stored procedure that takes 200 ms to execute and is called thousands of times per minute will have a greater impact on server performance than a single query that takes 1.5 seconds to complete. As such, focus your attention on queries with the highest cumulative cost.
When analyzing data, use aggregates to consider the total cumulative time (duration, CPU, read/writes, etc.), rather than identifying the single longest-running query. You can use the Performance Dashboard reports or SQL Nexus to identify these queries.


Once the solution has been identified, it should be validated through testing and implemented in production. This process should be as controlled and disciplined as the iterations of collecting and analyzing data. A production problem does not justify a cavalier attitude toward production changes, and professionalism must be maintained even under pressure.

Validating Changes

Changes should always be made in a test environment prior to production. In an ideal scenario, the problem can be reproduced in the test environment, which provides an opportunity to confirm, or validate, that the fix has the desired impact. It is also important to carry out confidence tests to ensure that the change has no undesired impact.

Testing Changes in Isolation

If possible, test each change in isolation. Changing several settings at once can make it harder to identify which change resolved the problem or caused other issues. In addition, it can be harder to roll back multiple changes than single, individual changes. Ensure that you have a thorough understanding of the consequences of any change, including rollback options.

Implementing Resolution

The final step is to implement the resolution in production. Ensure that the change is documented and any impact (such as service restarts) communicated. Note the behavior of the database or application before and after the change, as well as exactly what change was made. Ensure that the success criteria are met once the resolution is implemented, and share your results with the stakeholders.

Once the resolution is implemented and the solution is stabilized, carry out post-mortem analysis and ensure that the root causes are communicated to relevant parties. Identify any other vulnerable systems within the organization and communicate any lessons learned that may help you avoid a recurrence of similar problems in the future.

Top 10
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
- Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
- Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
programming4us programming4us