SQL Server: Cumulative IO By Database since Last Restart

Here's a TSQL script to get cumulative IO by database since last restart. WITH Agg_IO_Stats AS ( SELECT DB_NAME(database_id) AS database_name, CAST(SUM(num_of_bytes_read + num_of_bytes_written) / 1048576 / 1024. AS DECIMAL(12, 2)) AS io_in_gb FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS DM_IO_Stats GROUP BY database_id ), Rank_IO_Stats AS ( SELECT ROW_NUMBER() OVER(ORDER BY io_in_gb DESC) AS row_num, database_name, io_in_gb, CAST(io_in_gb / SUM(io_in_gb) Continue reading →

#iops

SQL Database IO Consumption

Here's a query I found that helps show database IO consumption. SELECT DB_NAME(DB_ID('DATABASENAME')) AS [Database Name] , mf.physical_name , io_stall_read_ms , num_of_reads , CAST(io_stall_read_ms / (1.0 + num_of_reads) AS NUMERIC(10, 1)) AS [avg_read_stall_ms] , io_stall_write_ms , num_of_writes , CAST(io_stall_write_ms / (1.0 + num_of_writes) AS NUMERIC(10, 1)) AS [avg_write_stall_ms] , io_stall_read_ms + io_stall_write_ms AS [io_stalls] Continue reading →

SQL Server Optimization Rules of Thumb

This helped me today in troubleshooting and understanding some issues. The performance counter SQL Server Access Methods: Full Scans/sec is very helpful in determining if you have a query doing full scans however, remember that it is for the entire database server not just a particular database. Optimization Rules of Thumb Always look at the query plan first. It will show you the optimal current execution plan from the query engine's point of view. Find the most expensive part of the execution Continue reading →

#performance

Defrag SQL MDF, LDF Files on Disk using Contig

Wrote this batch script to search an entire system (all disks) and find MDF/LDFs and then prompt whether or not to defrag. You need to have 'contig' from SysInternals. Also, set MINFRAGMENTS variable to something suitable for you. Again, I wrote this quickly and YMMV certainly. Code @echo off : Rich Kreider : : You need contig.exe from sysinternals: http://live.sysinternals.com/contig.exe SETLOCAL set MINFRAGMENTS=5 set CONTIGBIN=c:tempcontig.exe if not exist %CONTIGBIN% goto missingcontig : Continue reading →

#sysinternals

SQL 2008 Backups: Compression Comparison and Notes

Here's a transact-SQL to show you a comparison of a compressed and uncomopressed backup from your SQL server instance.
Backup compression is a new feature in SQL Server 2008 that can help provide smaller sized backups and reduce backup time.
(Reference)
SELECT
b.database_name 'Database Name',
CONVERT (BIGINT, b.backup_size / 1048576 ) 'UnCompressed Backup Size (MB)',
CONVERT (BIGINT, b.compressed_backup_size / 1048576 ) 'Compressed Backup Size (MB)',
CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, Continue reading →

#performance