Troubleshooting Methodology and Practices - SQL Server 2012 Internals and Troubleshooting - page 256

➤

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.

COUNTING THE COST OF PERFORMANCE PROBLEMS

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 (dura-
tion, 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.

VALIDATING AND IMPLEMENTING RESOLUTION

Once the solution has been identii ed, 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 coni rm,
or validate, that the i x has the desired impact. It is also important to carry out coni dence 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 under-
standing of the consequences of any change, including rollback options.