It is very important to optimize the SQL Server stored procedure query to improve the performance. Optimizing the query is not a one time task as it needs to be checked frequently based on the developers activity on database related changes. Adding one single column to an existing select query would leads to performance degradation. There are many ways to monitor and identify the query performance. One of which is DMV (Dynamic Management Views).
DMV is nothing but queries returning information about server state that is current at the time the query was run. The information which are returned are actually returned from the cache. Every time a query executes, its information will be stored in the cache.
Below is the DMV query to identify the stored procedure which uses most resources which taking more time to execute. It is also returning the Query Plan in the last column which is very useful to identify the query section and optimize it.
You have to mention your database name in the where condition to return stored procedure information from that. As I said earlier, the last column shows the Query plan link. Clicking it would open the query plan of the corresponding stored procedure similar to the below diagram.
DMV is nothing but queries returning information about server state that is current at the time the query was run. The information which are returned are actually returned from the cache. Every time a query executes, its information will be stored in the cache.
Below is the DMV query to identify the stored procedure which uses most resources which taking more time to execute. It is also returning the Query Plan in the last column which is very useful to identify the query section and optimize it.
SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id)END AS DBName
,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME]
,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
,cached_time
,last_execution_time
,execution_count
,total_worker_time / execution_count AS AVG_CPU
,total_elapsed_time / execution_count AS AVG_ELAPSED
,total_logical_reads / execution_count AS AVG_LOGICAL_READS
,total_logical_writes / execution_count AS AVG_LOGICAL_WRITES
,total_physical_reads / execution_count AS AVG_PHYSICAL_READS,
queryplan.query_plan
FROM sys.dm_exec_procedure_stats querystatus
CROSS APPLY sys.dm_exec_query_plan(querystatus.plan_handle) queryplan
where DB_NAME(database_id) = 'DBNAME'
ORDER BY AVG_LOGICAL_READS DESC
You have to mention your database name in the where condition to return stored procedure information from that. As I said earlier, the last column shows the Query plan link. Clicking it would open the query plan of the corresponding stored procedure similar to the below diagram.
