User Tools

Site Tools


database:mssql:tricks

SQL Server Tricks

Clear SQL Server cache/memory

In development or testing environment its very common that during performance tuning we do clear cache to get correct picture.

Checkpoint -- Write dirty pages to disk
DBCC FreeProcCache -- Clear entire proc cache
DBCC DropCleanBuffers -- Clear entire data cache

Find queries using the most performance on the server

Useful report to identify top queries on SQL Server.

Make sure you clear the cache after optimizations and running the query in order to get updated statistics.
SELECT TOP 10 
	capture_time = GETDATE(),
	total_elapsed_time,
        qs.execution_count,
        DatabaseName = DB_NAME(qp.dbid),
        ObjectName = OBJECT_NAME(qp.objectid,qp.dbid),
        StatementDefinition =
                SUBSTRING (
                        st.text,
                        (
                                qs.statement_start_offset / 2
                        ) + 1,
                 (
                                       (
                                               CASE qs.statement_end_offset
                         WHEN -1 THEN DATALENGTH(st.text)
                         ELSE qs.statement_end_offset
                                               END - qs.statement_start_offset
                                       ) / 2
                                ) + 1
                ),
         query_plan
 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) qp
 WHERE
     st.encrypted = 0
 ORDER BY total_elapsed_time DESC
/srv/wiki.niwos.com/data/pages/database/mssql/tricks.txt · Last modified: 2009/08/15 12:14 (external edit)