Database Reference
In-Depth Information
Implementing, Monitoring, and Tuning for
performance
SQL Server performance tuning and monitoring requires a multi-layered approach. The proper
configuration of the resources (hardware) is the primary area of focus. This ensures that SQL
Server will retain adequate resources that are tuned for performance and fault tolerance as a
foundation for an optimized data platform.
The important ingredient in performance tuning is planning an appropriate baseline and
benchmarking the data platform. Such planning requires an understanding of storage
architecture and indexing of data tables. The process of baseline and benchmarking will
enable better monitoring aspects.
In this recipe, we will cover the best usage of the Dynamic Management Views (DMV),
which is important when fine tuning the database performance and implementing
monitoring methods.
Getting ready
Whenever you are seeking help to fine tune the performance, the response will always be
'it depends', as it is purely dependent upon the individual environment and configuration
settings on hardware and software. To keep up the performance, the statistics on the table
must be updated regularly as this will decide how best to execute the query. SQL Server may
choose a less-than-optimal plan, if it is basing its execution decisions on stale statistics.
In order to fine tune the performance it is important to capture and evaluate the currently
executed statements on the SQL Server. The internal query architecture will save a plan for
every statement that is executed on SQL Server.
To monitor a query process, the Profiler tool is used. It is a resource-intensive application that
might cause additional problems, if the current environment is already hampered with the
slow performance of queries. Hence, it's best to use TSQL based on capture using Dynamic
Management View (DMVs), which returns the snapshot of the current system state without
causing any overhead to the SQL Server.
How to do it...
To get started, you need to complete the following steps:
1.
Connect to the SQL Server instance and open the query editor by clicking on the
New Query button on SSMS.
2.
To obtain a snapshot of the current running queries run the following TSQL:
SELECT r.session_id, r.status, r.start_time, r.command,
s.textFROMsys.dm_exec_requests r
 
Search WWH ::




Custom Search