From Sunil's blog to identify the top 10 queries generating most IO. SELECT TOP 10 (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_phys_reads, execution_count, statement_start_offset as…
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.…
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)…
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…
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…
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…
I don't remember the link I found this little tidbit at, but it's useful. This will display the scheduled jobs on your Microsoft SQL Server instance that will…
I came across something that I found useful today when troubleshooting some query performance reports. I won't re-post the entire article, but here's what I found useful and…
Find all the data and log file paths for all databases on Microsoft SQL Server instance. SELECT name, physical_name AS current_file_location FROM sys.master_files
This is how I calculate the number of days between two dates. Suppose I have a small table: Customer Name LastLogon select current year to day - extend(customer.lastlogon,…
Misys Vision Encounter (Superbill) Templates SQL Query: select tmpname,tmpdesc from formtmphdr where allow_edit = 'N' and tmptype = 7 tmpname tmpdesc CODESCAN1S MICROSCAN ENCOUNTER 1 (8 LPI -…
A quick method to find all the data and log file paths for all databases on a database server instance. SELECT name, physical_name AS current_file_location FROM sys.master_files Example…
This is my preferred method. onstat -g ses <sesid> Where <sesid> is the session id of the connected user querying the database. To get the session id onstat…
This will show jobs that will run. I lost the link I found this at. Eventually, I need to add a few columns to report scheduling information, etc.…
tsql so I can remember in the future. declare @db_id int declare @object_id int set @db_id = DB_ID('EMR') set @object_id = 0 select db_name(@db_id) as [database], object_id, object_name(object_id,…