Page tree

Versions Compared


  • This line was added.
  • This line was removed.
  • Formatting was changed.

Excerpt Include

SQL Database Health

Query NameDescriptionQuery
Index Fragmentation AuditThis query provides you with the index fragmentation % for every table in the DB./**************************************************************
   You can use the following script to determine index 
   fragmentation by table. This can help to determine 
   that the database is causing performace problems.

SELECT OBJECT_NAME(object_id), index_id, index_type_desc, index_level, avg_fragmentation_in_percent, avg_Page_space_used_in_percent, page_count 
FROM sys.dm_db_index_physical_stats(DB_ID(N'AirWatch'), null, null, null, 'SAMPLED')
ORDER BY avg_fragmentation_in_percent DESC
Table Size AuditThis query will provide physical sizing information of all the tables in the database./**************************************************************
    This query can help you determine what table is taking
    up the most disk space and potentially what tables have
    too many rows.

--If the temp table exisits drop temp table
IF EXISTS ( SELECT * FROM sys.objects 
     WHERE object_id = OBJECT_ID(N'#Sizes') 
     AND type in (N'U') ) 
     DROP TABLE #Sizes; 

--Create temp table, #Sizes 
--Table name 
     table_name nvarchar(255), 
--Number of rows 
     table_rows char(11),
--Physical space table is using
     table_reserved varchar(18),
--Phyiscal space table data is using
     table_data varchar(18),
--Physical space idexes are using
     table_index_size varchar(18),
--Physical space reserved
     table_unused varchar(18)

EXEC sp_MSforeachtable @command1='INSERT #Sizes (table_name, table_rows, table_reserved,
table_data, table_index_size, table_unused) EXEC sp_spaceused ''?'''
--This query orders the results by actual phyisical table size
SELECT * FROM #Sizes ORDER BY CAST ( SUBSTRING (table_data, 0, LEN(table_data) - 2) AS InT) DESC
--This query orders the results by row count
--SELECT * FROM #Sizes ORDER BY CAST (table_rows AS int) DESC
Table Row Count Only A more efficient script to get table row counts./**************************************************************
    This is a more efficient way to get Row Counts
    but will not include any Physical Sizing data

SELECT +'.'+ TableName
     , SUM(pa.rows) RowCnt
     sys.tables ta
     INNER JOIN sys.partitions pa
          INNER JOIN sys.schemas sc
          ON ta.schema_id = sc.schema_id
     ta.is_ms_shipped = 0 
      AND pa.index_id IN (1,0)
     SUM(pa.rows) DESC;
Database IO Stalls You can use this query to see which queries are causing IO stalls./**************************************************************
   The table valued dynamic management function, 
   sys.dm_io_virtual_file_stats provides a breakdown of SQL 
   Server reads, writes, and io_stalls for a particular
   database or transaction log file. IO_stalls is the total 
   cumulative time, in milliseconds, that users waited for
   I/O to be completed on the file since the last restart    of SQL Server. 

     DB_NAME(fs.database_id) AS [DB Name]
     , fs.file_id
     , 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
     , num_of_reads + num_of_writes AS total_io
     , CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads
          + num_of_writes ) AS NUMERIC(10 ,
          1)) AS 'avg_io_stall_ms'
     sys.dm_io_virtual_file_stats(NULL , NULL) AS fs
INNER JOIN sys.master_files AS mf
          ON fs.database_id = mf.database_id
          AND fs.[file_id] = mf.[file_id]
     avg_io_stall_ms DESC ;
Identify Expensive Operations
   This query provides you with operations that are expensive
   from a database standpoint. This query is useful in
   determining what is causing performance problems on a server

     DB_NAME(qp.[dbid]) AS dbname
     , qp.[dbid]
     , qp.objectid
     , qp.number 
     --, qp.query_plan 
     --the query plan can be *very* useful; enable if desired 
     , qt.[text]
     , SUBSTRING(qt.[text] , 
          ( qs.statement_start_offset / 2 ) + 1 ,
          ( ( CASE statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
          ELSE qs.statement_end_offset
     END - qs.statement_start_offset ) / 2 ) + 1) 
         AS statement_text
     , qs.creation_time
     , qs.last_execution_time
     , qs.execution_count
     , qs.total_worker_time / qs.execution_count 
         AS avg_worker_time
     , qs.total_physical_reads / qs.execution_count 
         AS avg_physical_reads
     , qs.total_logical_reads / qs.execution_count 
         AS avg_logical_reads
     , qs.total_logical_writes / qs.execution_count 
         AS avg_logical_writes
     , qs.total_elapsed_time / qs.execution_count 
         AS avg_elapsed_time
     , qs.total_clr_time / qs.execution_count
     nbsp;   AS avg_clr_time
     --, qs.total_worker_time 
     --, qs.last_worker_time 
     --, qs.min_worker_time 
     --, qs.max_worker_time 
     , qs.total_physical_reads
     , qs.last_physical_reads
     , qs.min_physical_reads
     , qs.max_physical_reads 
     --, qs.total_logical_reads 
     --, qs.last_logical_reads 
     --, qs.min_logical_reads 
     --, qs.max_logical_reads 
     --, qs.total_logical_writes 
     --, qs.last_logical_writes 
     --, qs.min_logical_writes 
     --, qs.max_logical_writes 
     --, qs.total_elapsed_time 
     --, qs.last_elapsed_time 
     --, qs.min_elapsed_time 
     --, qs.max_elapsed_time
     --, qs.total_clr_time 
     --, qs.last_clr_time 
     --, qs.min_clr_time 
     --, qs.max_clr_time 
     --, qs.[sql_handle] 
     --, qs.statement_start_offset 
     --, qs.statement_end_offset 
     --, qs.plan_generation_num 
     --, qp.encrypted 
     sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
     --sample WHERE 
     last_execution_time > '20120912 12:15'
     AND creation_time > '20130101'
     AND execution_count > 10
     --AND SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1,
     --((CASE statement_end_offset 
     --WHEN -1 THEN DATALENGTH(qt.text)
     --ELSE qs.statement_end_offset END 
     -- - qs.statement_start_offset)/2) 
     -- + 1)
     -- LIKE '%MyText%'

     --sample ORDER BY
     --ORDER BY qs.execution_count DESC --Frequency
     --ORDER BY qs.total_worker_time DESC --CPU
     --ORDER BY avg_worker_time DESC --CPU
     --ORDER BY qs.total_elapsed_time DESC --Durn
     --ORDER BY qs.total_logical_reads DESC --Reads 
     --ORDER BY qs.total_logical_writes DESC --Writes
     --ORDER BY qs.total_physical_reads DESC --PhysicalReads 
     --ORDER BY avg_worker_time DESC --AvgCPU
     --ORDER BY avg_elapsed_time DESC --AvgDurn 
     --ORDER BY avg_logical_reads DESC --AvgReads
     --ORDER BY avg_logical_writes DESC --AvgWrites
     avg_physical_reads DESC --AvgPhysicalReads