seawalt-优快云博客

原创 When and how to trace who and when alter the table

CREATE TABLE TriggerLog (LogInfo xml,HostName Varchar(255), AppName Varchar(255)) create TRIGGER safety ON DATABASE FOR DROP_TABLE AS DECLARE @EventData AS xml DECLARE @ObjectName AS VARCHAR(100)

2010-07-19 11:35:00 305

转载 Figure out which databases involve most of the I/O

WITH Waits AS( SELECT wait_type, wait_time_ms / 1000. AS wait_time_s, 100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct, ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn FROM sys.dm_os_wait_stats WHERE wait_type NOT LI

2010-06-25 17:10:00 295

转载 get the top-10 queries that are taking the most CPU

SELECT TOP 10 total_worker_time/execution_count AS avg_cpu_cost, plan_handle, execution_count, (SELECT SUBSTRING(text, statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVER

2010-06-25 17:07:00 276

原创 Find out the missing indexes and their usefulness

SELECT t1.object_id, t2.user_seeks, t2.user_scans, t1.equality_columns, t1.inequality_columnsFROM sys.dm_db_missing_index_details AS t1, sys.dm_db_missing_index_group_stats AS t2, sys.dm_db_missing_index_groups AS t3WHERE database_id = DB_ID() AND

2010-06-09 16:44:00 262

原创 find the top-10 queries that do the most I/Os per execution

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, (SELECT SUBSTRING(text, statement_s

2010-06-09 16:35:00 370

转载 provide the I/O performance information at the database file leve

SELECT DBS.database_id, DBS.name, file_id, io_stall_read_ms, io_stall_write_msFROM sys.dm_io_virtual_file_stats(NULL, NULL) IO_FilesJOIN SYS.DATABASES DBSON IO_FILES.database_id=dbs.database_id

2010-06-09 16:31:00 339

转载 identify internal components that are stealing the most pages from buffer pool using the following DMV query

SELECT TOP 10 type, SUM(single_pages_kb) AS stolen_mem_kb FROM sys.dm_os_memory_clerks GROUP BY type ORDER BY SUM(single_pages_kb) DESC

2010-06-09 15:49:00 312

原创 find the total amount of memory consumed (including AWE) by the buffer pool

SELECT SUM(multi_pages_kb + virtual_memory_committed_kb + shared_memory_committed_kb + awe_allocated_kb) AS [Used by BPool, Kb]FROM sys.dm_os_memory_clerksWHERE type = 'MEMORYCLERK_SQLBUFFERPOOL'

2010-06-09 15:41:00 380

原创 find the top-10 query plans that have been recompiled the most.

SELECT TOP 10 plan_generation_num, execution_count, (SELECT SUBSTRING(text, statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),text)) * 2 ELSE statement_end_offset END - s

2010-06-09 15:37:00 290

原创 get the top-10 queries that are taking the most CPU per execution

1.SELECT TOP 10 total_worker_time/execution_count AS avg_cpu_cost, plan_handle, execution_count, (SELECT SUBSTRING(text, statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text))

2010-06-09 15:34:00 353

seawalt的专栏