Troubleshooting - Microsoft SQL Server 2008 R2 Administration - page 363

This completes the required steps in implementing systematized monitoring methods for data
platform optimization to fine tune the queries.
How it works...
We started using the SSMS tool to obtain the information and TSQL-based monitoring
methods that will help to start optimizing the frequently running queries. The executed DMVs
data is grouped at the instance level and subgrouped from the database level to collate the
information required in performance troubleshooting. The information obtained here gives
performance waits that indicate the actual problem on system resources such as disk,
network, and memory. To talk about the process, we started with the SSMS tool by looking
at the Activity Monitor and the query execution plan ; these tools give high-level
information on system resources usage with an explanation on the steps that are followed
during a query execution. The monitor and query plan also help us to suggest the missing
index for that query execution, which can be tested on the current database by retrying
execution of the same query.
Further, we have used DMV to get information on how many times the query execution plan is
used at the time of the query execution. The DMV started with the user-seeks from the sys.
dm_db_missing_index_group_stats and joined with two other DMVs, sys.dm_db_
missing_index_groups and sys.dm_db_missing_index_details , based on the
handle condition. The DMV sys.dm_db_index_usage_stats gives us the information on
what indexes are used on that instance since the last restart of SQL Server services. The sys.
dm_exec_query_stats DMV is used to obtain metrics about what queries are running and
how often; here, we used it to return aggregate performance statistics for cached query plans.
Initially, this DMV might return inaccurate results if there is a workload currently executing
on the server. Hence, it is executed within a common table expression (CTE). This recursive
CTE is executed repeatedly to return subsets of data until the complete result set is obtained.
SQL Server 2008 introduced a new column called query_hash , which gives the aggregated
statistics on the query execution by using a common table expression function.
Designing a filtered indexes and statistics
feature
Scalability and performance are the common buzzwords whenever we are dealing with
large data sets, and to help further, indexes and statistics play an important role in query
performance. A Database Administrator or Developer needs a good understanding of the
indexing methods, which can result in efficient practices to return the required subset of data
based upon the index key columns and their values.
Inside the database, the data is inserted, deleted, and updated frequently; to return
subsequent results efficiently, indexes are helpful. However, NULL values are present in the
data, and if it is likely that null values are stored in the data on a larger scale, the indexes will
need efficient design for better performance.