Posts Tagged ‘Stored Procedure’

T-SQL Script To Drop all Stored Procedures

Early today i faced issue of deleting all stored procedure in Backup database . The normal action is to open SQL Mangement Studio and try to delete them the Problem in this solution That you can’t select all stored procedure and delete them on one click You must selecte the stored procedures one by one […]

More »

SP That Consumes The Most CPU Resources And Most I/O Requests

This post will be about stored procedures and performance indicators . If you want to show the SP that consumes the most CPU resources ? you can run the following TSQL command: SELECT DB_NAME(st.dbid) DBName ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName ,OBJECT_NAME(st.objectid,dbid) StoredProcedure ,MAX(cp.usecounts) Execution_count ,SUM(qs.total_worker_time) total_cpu_time ,SUM(qs.total_worker_time) / (MAX(cp.usecounts) * 1.0)  avg_cpu_time FROM sys.dm_exec_cached_plans cp JOIN sys.dm_exec_query_stats qs […]

More »

T-SQL How To Get the Execution Count Of a Stored Procedure

How to Get the Execution Count of a Stored Procedure ? This Query will get the top 100 stored procedure by max execution time. This is good indicator for developer to determine heavily used procedure. This can help developer to know if his code needs to replaced or Maintained, and for SQL admin to but […]

More »