SQLServer Performance Troubleshooting

Performance tuning

• Dynamic Management Views (DMVs) and System Catalog Views
• Profiler and Server Side Traces
• Windows Performance Monitor
• Built in performance reports in SSMS
• Query Plans
• Database Tuning Advisor

DMV Views

The sys.dm_exec_query_stats DMV is a server-scoped DMV that returns aggregate

performance statistics for cached query plans.

The following code block returns information about the top five queries run against

the master database by the maximum number of physical reads:

USE master;

GO

SELECT TOP 5 sql_handle, last_execution_time, max_physical_reads

AS [max_physical_reads]

FROM sys.dm_exec_query_stats

ORDER BY creation_time DESC;

GO

The sys.dm_os_wait_stats DMV returns information about waits encountered by

threads that are in execution

The sys.dm_tran_locks DMV returns information about currently active lock manager

resources.

Failure Diagnosing

Log File Viewer

The Log File Viewer allows you to view log files from a variety of sources

Using query

SELECT message_id, language_id, severity, is_event_logged, text

FROM sys.messages

WHERE language_id = 1033;

Transaction Log Is Full

SQL Server database engine issues a 9002 error

Options for responding to a full transaction log include the following tasks:

■ Backing up the log

■ Increasing disk space or moving the log to another volume

■ Increasing log file size

■ Terminating long-running transactions

Specific Database Is Out of Space

ALTER DATABASE command to add a file group on

a separate volume to the database

Recycling the SQL Server Log

Start a new SQL Server log by using the sp_cycle_errorlog stored

procedure.