Archive for the ‘Performance’ Category

SQL Query To Determine The Last Database Backup Date

This simple script will get list of dates of backups. use msdb SELECT database_creation_date,backup_start_date,backup_finish_date, name, user_name, backup_size FROM backupset WHERE (database_name = N’DataBaseName’) ORDER BY backup_finish_date DESC;

More »

SQL SERVER Merge Statement Make INSERT UPDATE DELETE To Integrate Data Between Two Tables

In this post i will not talk long i will put code and examples for how to use Merge Statement. Go MERGE [ INTO ] target_table [ WITH ( ) ] [ [ AS ] table_alias] USING ON [ WHEN MATCHED [ AND ] THEN ] [ WHEN NOT MATCHED [ BY TARGET ] [ […]

More »

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 »

Clean Adhoc plan cache

Adhoc Queries generate lots of execution plans ,these plans take size in the sql buffer .The great number of these plans can cause pressure on the system leads to a RAM bottleneck. Identify the adhoc sql cache size select objtype, count(*)as number_of_plans, sum(cast(size_in_bytes as bigint))/1024/1024 as size_in_MBs, avg(usecounts)as avg_use_count from sys.dm_exec_cached_plans group by objtype clean […]

More »