Tuesday, September 21, 2010

How to check query performance without profiler.

If you don’t have profiler installed and you just want to find out execution statistics of procedures, you can take help of DMVs. You can write T-SQL queries using DMV to gather execution statistics of SQL procedures.

You can extract following information using DMV regarding Procedure.

· How many times your stored procedure executed.

· Total number of cached plans.

· Identify Missing Indexes Using.

· Buffer Cache hit ratio example.

· SQL CLR Memory Usage

· And many more information we can find from below references.

Here is the sample query using DMV which if found from some good sites.

SELECT OBJECT_NAME(st.objectid,dbid) StoredProcedure
      ,max(cp.usecounts) Execution_count
 FROM sys.dm_exec_cached_plans cp
         CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'
   group by cp.plan_handle, DB_NAME(st.dbid),
            OBJECT_SCHEMA_NAME(objectid,st.dbid), 
   OBJECT_NAME(objectid,st.dbid) 
 order by Execution_count desc.

SELECT TOP ( 100 )
        p.name AS [SP Name] ,
        deps.total_logical_reads AS [TotalLogicalReads] ,
        deps.total_logical_reads / deps.execution_count AS [AvgLogicalReads],
        deps.execution_count,
        ISNULL(deps.execution_count / DATEDIFF(Second, deps.cached_time,
                                           GETDATE()), 0) AS [Calls/Second],
        deps.total_elapsed_time,
        deps.total_elapsed_time / deps.execution_count AS [avg_elapsed_time],
        deps.cached_time
FROM    sys.procedures AS p
        INNER JOIN sys.dm_exec_procedure_stats
                       AS deps ON p.[object_id] = deps.[object_id]
WHERE   deps.database_id = DB_ID()
ORDER BY  deps.execution_count DESC

References:

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!828.entry

http://technet.microsoft.com/en-us/library/cc966540.aspx

http://www.databasejournal.com/features/mssql/article.php/3687186/Monitoring-Stored-Procedure-Usage.htm

http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/23/4-0-useful-queries-on-dmv-s-to-understand-plan-cache-behavior.aspx

http://www.simple-talk.com/sql/t-sql-programming/dmvs-for-query-plan-metadata/

Though DMV are very powerful and we can find out any information about query execution. we can’t spend time to change them as our requirement. The thing we can do is keep DMV for some test cases ready and just run them when it comes to performance testing.

Profiler:

SQL profiler provides GUI to gather same data, only good thing about using SQL profiler will give you all the information separated by events, let’s say If you want to know that which statement in procedure force recompilation of plan?.

Well, if you prefer DMV you have to write DMV to find out how many time plans have re-used, and if you are running SQL profiler it will just log all the events and will show all the data at all point of execution. Also SQL profiler has many pre-defined templates which come handy when we are running profiler for collection different types of data.

You can see http://support.microsoft.com/kb/243586 for more details about Troubleshooting stored procedure recompilation

But the point when I would like to Use DMV over Profiler is, when your application is in development mode you can use SQL profiler wisely running and seeing data. But once application is running on production server it is very time cumbersome to run profiler and then find which procedure have taken maximum time to execute.

In this case you can just run one Query and result will be in front of you….NICE….

Hope this will help you.

Any input always invited.