Posts Tagged ‘Performance’

Disable and Enable Indexes to perform large data update in SQL Server 2008

Indexes are very important they used for making Queries run faster and to reduce execution time . But they are Headache in very large table especially when updating large number of data . The query can run for ever. So it is recommended To disable and run your update and then re enable the indexes […]

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 »

Find Most Active Database in SQL Server 2008

In this post I will talk about “Finding Most Active Database in SQL Server–DMV dm_io_virtual_file_stats”. The following TSQL uses “sys.dm_io_virtual_file_stats” view -one of the sys Views for SQL2008- that shows the amount of reads and writes for each file in the database . This query will help us determining the most busy file and this […]

More »