Category Archives: SQL Server

SQL Server: Get Fragmentation Level for Indexes

If using an older version of SQL Server,

DBCC SHOWCONTIG

Newer versions should use the dm_db_index_physical_stats

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks');
SET @object_id = OBJECT_ID(N'Production.Customers');
IF @object_id IS NULL 
BEGIN
   PRINT N'Invalid object';
END
ELSE
BEGIN
   SELECT IPS.Index_type_desc, 
      IPS.avg_fragmentation_in_percent, 
      IPS.avg_fragment_size_in_pages, 
      IPS.avg_page_space_used_in_percent, 
      IPS.record_count, 
      IPS.ghost_record_count,
      IPS.fragment_count, 
      IPS.avg_fragment_size_in_pages
   FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'DETAILED') AS IPS;
END
GO

Reference

 

SQL Server – Finding Resource Intesive or Expensive Queries

It’s suggested you use SQL Profiler trace data ove layed against system information found in the performance monitor. However, to get a quick look at what queries may be slowing down performance , run the following –

select ss.sum_execution_count
    , t.text
    , ss.sum_total_elapsed_time
    ,ss.sum_total_worker_time
    , ss.sum_total_logical_reads
    , ss.sum_total_logical_writes
    from 
        (Select s.plan_handle
        , sum(s.execution_count) sum_execution_count
        , sum(s.total_elapsed_time) sum_total_elapsed_time
        , sum(s.total_worker_time) sum_total_worker_time
        , sum(s.total_logical_reads) sum_total_logical_reads
        , sum(s.total_logical_writes) sum_total_logical_writes
        from sys.dm_exec_query_stats s
        group by s.plan_handle
        ) as ss
        cross apply sys.dm_exec_sql_text(ss.plan_handle) t
        order by sum_total_logical_reads DESC