Troubleshooting - Microsoft SQL Server 2008 R2 Administration - page 358

Implementing systematized monitoring
methods for data platform optimization
Optimization is a key level of achievement for every data platform, and SQL Server is not an
exception. To accomplish performance optimization, it is essential to understand the aspects of
SQL Server tuning and the approach is multi-layered. The process to keep up the optimization
at sustainable levels is quite challenging and a continual activity. It is highly required to adopt
the reliable methods for data platform optimization using SQL Server features.
There are different methods available in SQL Server. Using the tools and commands
technique, we can help evaluate and troubleshoot the query performance. In order to achieve
this, we must address the fragmented indexes and outdated statistics on the table that is
suffering a performance loss. There are various best practices available and involved to
evaluate query performance, which can be used as a checklist. But, these practices provide a
basic level of query performance tuning guidelines.
In this recipe, we will go through a summarized view of major monitoring activities and
methods that can have a demonstrable impact on fine tuning the optimization for the
SQL Server data platform. The demonstration methods involve the usage of Dynamic
Management Views (DMVs), server-side trace, obtaining an estimated query execution plan,
and capturing performance statistics for cached query plans to evaluate query performance.
Getting ready
In order to simulate the performance optimization statistics, let us execute the following TSQL
queries against AdventureWorks2008R2 database:
Use the following TSQL statement to prepare the recipe and enable the monitoring methods:
--Use AdventureWorks2008R2 database and
--generate few long running queries to capture the information for
performance evaluation
USE Adventureworks2008R2
Go
SET SHOWPLAN_TEXT ON
GO
SET STATISTICS IO ON
GO
SELECT CreditCardID,CardType,CardNumber
FROM Sales.CreditCard
WHERE (CardType='Vista' and CreditCardID<>'' and
ModifiedDate>='2008-04-01')
GO