performance

Performance statistics for stored procedures

By looking at the results returned by dm_exec_procedure_stats DMV, we can gather info about how a stored procedure is performing:

SELECT
        DB_NAME(database_id), p.name as SPName, execution_count
        , total_physical_reads, total_physical_reads / execution_count as avg_physical_reads
        , total_logical_reads, total_logical_reads / execution_count as avg_logical_reads
        , total_elapsed_time, total_elapsed_time / execution_count as avg_elapsed_time
        , QP.query_plan
FROM
        sys.procedures P
        JOIN sys.dm_exec_procedure_stats S ON S.object_id = P.object_id
        CROSS APPLY [sys].[dm_exec_query_plan] (S.[plan_handle]) QP
WHERE
        S.database_id = DB_ID()
        AND P.is_ms_shipped = 0

How to delete or invalidate a query plan

Reboot / Restart
When SQL Server is rebooted, or the service is stopped and restarted, any DMV data is lost.

Flushed Cache
The hard way, delete all query plans from cache:

-- Never execute this on your production server
DBCC FREEPROCCACHE

or you can target specific plans:
DBCC FREEPROCCACHE(plan_handle)

Memory pressures
Keeping a large number of cached plans can quickly fill the available memory, especially in systems that create lots of SQL queries that differ subtly (for example, having different environment settings), resulting in separate cached plans for each one.
SQL Server manages the size of the procedure cache by maintaining the age of the execution plans in the cache. If a stored procedure is not re-executed for a long time, the age field of the execution plan can come down to 0, and the plan can be removed from the cache when SQL Server observes a pressure on its memory.

Recompiling
Recompile all queries related to a specific table or view:

EXEC sp_recompile <tablename>

Delete all query plans associated with a stored procedure.

-- Manually mark a stored procedure for recompilation, so it will be recompiled next time it is executed.
EXEC sp_recompile <procname>

Or you can enforce a query recompilation each time, then add a query hint to the end of the query:

OPTION (RECOMPILE)

Add the WITH RECOMPILE option when a stored proc is created:

CREATE PROCEDURE <ProcedureName>
WITH RECOMPILE
AS
...

When statistics are updated
More details about when statistics change.

Simple parametrization
Setting configuration option optimize for ad hoc workloads to 1, will store only query_hash into the cache and and on second execution (if it happens), the plan itself will be placed in cache.

Forced parametrization
Parametrize all queries like in case of simple parametrization

ALTER DATABASE <dbname> SET PARAMETRIZATION FORCED;

When statistics really change?

Stats change:

  • When a table with no rows gets a row
  • When a table has fewer than 500 rows and is increased by 500 or more rows
  • Through SQL Server's auto-updating mechanism based on database option AUTO_UPDATE_STATISTICS (which by default is enabled and it performs synchronously), statistics get automatically changed when 500 + 20% of the table rows change. E.g. for a table with 100k rows, stats will get invalidated when 20500 rows are updated. Read more about this.
  • In addition to statistics on indexes, SQL Server can build statistics on columns with no indexes. See AUTO_CREATE_STATISTICS option.
  • If trace flag 2371 is turned on, then updates are dynamic Read more about this.
  • When you rebuild an index (equivalent to FULLSCAN).
  • Or you can decide to manually update the statistics.

Performance statistics for cached query plans

SELECT
          DB_NAME(QP.dbid)
        , ST.[text]
        , QS.[execution_count]        
        , QS.query_hash
        , QS.query_plan_hash
        , QP.query_plan
        , QS.*
FROM
        [sys].[dm_exec_query_stats] as QS
        CROSS APPLY [sys].[dm_exec_sql_text] (QS.[sql_handle]) AS ST
        CROSS APPLY [sys].[dm_exec_query_plan] (QS.[plan_handle]) AS QP
WHERE
        ST.[text] LIKE '%<part of your query here>%'
ORDER BY
        QS.[execution_count] DESC;
GO

Detecting fragmentation for all indexes in SQL Server

The first step in deciding which defragmentation method to use is to analyze the index to determine the degree of fragmentation. By using sys.dm_db_index_physical_stats DMV, you can detect fragmentation in a specific index, all indexes on a table or indexed view, all indexes in a database, or all indexes in all databases. For partitioned indexes, sys.dm_db_index_physical_stats also provides fragmentation information for each partition.

The result set returned by the sys.dm_db_index_physical_stats dynamic management function includes the following columns:
avg_fragmentation_in_percent: the percent of logical fragmentation (out-of-order pages in the index)
fragment_count: the number of fragments (physically consecutive leaf pages) in the index
avg_fragment_size_in_pages: average number of pages in one fragment in an index
avg_record_size_in_bytes: This number simply represents a useful measure for the amount of data stored within the record (index or heap).

SELECT
     OBJECT_NAME(S.OBJECT_ID) as TableName,
     I.name as IndexName,
     S.avg_fragmentation_in_percent
FROM
     sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) as S
     INNER JOIN sys.indexes AS I ON S.OBJECT_ID = I.OBJECT_ID AND S.index_id = I.index_id
WHERE
     S.database_id = DB_ID()
ORDER BY
     TableName, IndexName

After the degree of fragmentation is known, check how you can "resolve" the fragmentation.

Index usability - tune your index strategy

The view sys.dm_db_index_usage_stats returns statistical counts of the various index operations that have occurred to an index over time.

SELECT
     object_name(i.object_id) as TableName
     , i.name as [Index]
     , user_seeks + user_scans + user_lookups + user_updates as usability
     , user_seeks, user_scans, user_lookups, user_updates
     , i.index_id, i.type_desc, i.is_unique, i.is_primary_key, i.is_unique
FROM
     sys.indexes i    
     INNER JOIN sys.dm_db_index_usage_stats s ON s.index_id = i.index_id AND s.object_id = i.object_id
WHERE 1=1
     AND i.name is not NULL
     AND s.database_id = DB_ID() -- current DB
     --AND object_name(i.object_id) = '<TableName>'
ORDER BY
     usability, [Index], TableName;

List of long running queries in SQL Server

T-SQL script that returns the queries that take the longest time to run:

SELECT TOP 20
        R.session_id, R.status, R.start_time, R.command, Q.text
FROM
        sys.dm_exec_requests R
        CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) Q
--WHERE R.status in ('runnable')
ORDER BY R.start_time

There’s also an interesting script, completely free on http://www.brentozar.com.

Pages

Subscribe to RSS - performance