Getting List of Fragmented Indexes in a Database

Index fragmentation is naturally happens as an action of insert, deletes and updates.  It is important to manage this fragmentation, if not you can have performance issue with range type queries.

  SELECT   OBJECT_NAME(IPS.OBJECT_ID)       AS TableName
         , CASE WHEN i.name IS NULL THEN
              'HEAP TABLE, NO INDEXES'
           ELSE
              i.name
           END                              AS IndexName
         , index_type_desc                  AS IndexType
         , index_depth
         , avg_fragmentation_in_percent
         , page_count
    FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') IPS
    JOIN sys.indexes I ON IPS.OBJECT_ID = I.OBJECT_ID AND IPS.index_id = I.index_id
ORDER BY avg_fragmentation_in_percent

Finding All Plans/SQL Statements that executed in Parallel

       WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
     SELECT   query_plan                                            AS CompleteQueryPlan
            , n.value('(@StatementText)[1]', 'VARCHAR(4000)')       AS StatementText
            , n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)')    AS StatementOptimizationLevel
            , n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost
            , n.query('.')                                          AS ParallelSubTreeXML
       FROM sys.dm_exec_cached_plans AS ecp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')     AS qn(n)
      WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1

References: #21

Statement copied from The Rambling DBA: Jonathan Kehayias Blog

Get Top 50 SQL Statements/Query Plans

Finding out what statements causing grief on SQL Server has become significantly easier with SQL Server 2005+.  I am using some of the DMV/DMF to pull this information together.  Please note on a 64-bit system with lots of memory this can take a bit of time to run because of Sort & pulling execution plan information.  If that is an issue, I recommend getting the full information without sort by and dumping it to a temp table.  From there do your analysis, here are few options for running this statement:

Following query returns everything it is very expensive and takes a while to complete, even if you dump it to tempdb it can take 10+ mintutes on systems with lots of memory.

-- Get Top 50 Statements that have been recorded in SQL Server since last restart.
--

DECLARE @Top50By INT

-- 1 = Duration, 2 = CPU, 3 = Reads
SET @Top50By = 1

     SELECT   TOP 50
              SUBSTRING(st.text,
                        (qs.statement_start_offset/2)+1,
                        ((CASE qs.statement_end_offset WHEN -1 THEN
                             DATALENGTH(st.text)
                          ELSE
                             qs.statement_end_offset
                          END - qs.statement_start_offset)/2) + 1)  AS SQLStatement
            , qp.query_plan                                         AS QueryPlan
            , execution_count                                       AS NumOfExecutions
            , (total_elapsed_time/execution_count)/1000             AS AVGDuration_ms
            , (total_worker_time/execution_count)/1000              AS AVGCPU_ms
            , (total_logical_reads/execution_count)                 AS AVGReads
            , (total_logical_writes/execution_count)                AS AVGWrites
       FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle) qp
   ORDER BY CASE
            WHEN @Top50By = 1 THEN
               (total_elapsed_time/execution_count)/1000
            WHEN @Top50By = 2 THEN
               (total_worker_time/execution_count)/1000
            WHEN @Top50By = 3 THEN
               (total_logical_reads/execution_count)
            END DESC

Same code, but I have taken out the execution plan, so it should execute faster. The idea is once you know which queries you want to address those are the only plans you will bring up; making it faster. However this method if you save the data, there is no grantee the plan will be there in the future.

-- Get Top 50 Statements that have been recorded in SQL Server since last restart.
--

DECLARE @Top50By INT

-- 1 = Duration, 2 = CPU, 3 = Reads
SET @Top50By = 1

     SELECT   TOP 50
              SUBSTRING(st.text,
                        (qs.statement_start_offset/2)+1,
                        ((CASE qs.statement_end_offset WHEN -1 THEN
                             DATALENGTH(st.text)
                          ELSE
                             qs.statement_end_offset
                          END - qs.statement_start_offset)/2) + 1)  AS SQLStatement
            , qs.plan_handle										AS PlanHandle
            , execution_count                                       AS NumOfExecutions
            , (total_elapsed_time/execution_count)/1000             AS AVGDuration_ms
            , (total_worker_time/execution_count)/1000              AS AVGCPU_ms
            , (total_logical_reads/execution_count)                 AS AVGReads
            , (total_logical_writes/execution_count)                AS AVGWrites
       FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st
   ORDER BY CASE
            WHEN @Top50By = 1 THEN
               (total_elapsed_time/execution_count)/1000
            WHEN @Top50By = 2 THEN
               (total_worker_time/execution_count)/1000
            WHEN @Top50By = 3 THEN
               (total_logical_reads/execution_count)
            END DESC

Getting file usage stats

SELECT db_name(database_id) AS DBName,
       FILE_ID, -- File ID 2 = Log, File Id = 1 Data
       CASE WHEN num_of_reads = 0 THEN
          0
       ELSE
          io_stall_read_ms/num_of_reads
       END AS AvgIOStallRead_ms,
       CASE WHEN num_of_writes = 0 THEN
          0
       ELSE
          io_stall_write_ms/num_of_writes
       END AS AvgIOStallWrites_ms
  FROM sys.dm_io_virtual_file_stats(null,null)

You can use this DMV to see what SQL Server thinks about the I/O stall times. I don’t rely on it 100% but it helpful to see which file is generating the most I/O or is performing the worst. However if you have Avg IO Stall time for read or write over 100 ms, I think it requires investigation. Correlate these with the Disk KPIs such as Avg Disk Sec/Read and Avg. Disk Sec/Write.

References: #9, #10, #11

DMV/DMF Information

All my posts from old SQL Learnings DMF/DMV will be a bit delayed in coming online on this blog. I need to clean them up and only keep the statements I like, I borrowed many statements from various sites (with reference) that did similar work.  So I am going to try to clean up and pull statements that I use instead of whats there.  Going to try to put more information around how statements can help troubleshoot or what information they are giving.  Lets see how use full can I make it heh.

DMV/DMF

I have used many different queries of DMV/DMF over the past years to get various information for SQL Server OS. So I am going to start keeping a record of all the queries and try to explain their meaning and usage as I understand them.

There are many DMV/DMF and it is hard to remember them all, in SQL Server 2005 there were 12 categories (link), totaling 78 DMV/DMFs and in SQL Server 2008 there are 17 categories (link), totaling 117 DMV/DMFs. I will make sure to point out any differences between two as I run into them. These help make a DBA life easier by providing alot of the low level system information we didn’t have access to before.

This post will be catch all for all the pages I use for my queries references. I think everyone’s work should be referenced fully; so if I have forgotten to reference your work please let me know. Thanks.

List of References are in order I run into them …

1. The Code Project. Dynamic Management Views. Hari Prasand K. (2006, Dec 21). Accessed from http://www.codeproject.com/KB/database/Dynamic_Management_Views.aspx.
2. Microsoft. SQL Server 2008 Books On-Line: sys.dm_exec_query_stats (Transact-SQL). (2009, Feb). Accessed from http://msdn.microsoft.com/en-us/library/ms189741.aspx.
3. Microsoft. MSDN Magazine. SQL Server: Uncover Hidden Data to Optimize Application Performance. Ian Stirk. (2008, Jan). Accessed from http://msdn.microsoft.com/en-us/magazine/cc135978.aspx.
4. SQL Solutions. SQL Server Cache Hits Ratio and SQL Server Performance. (2007, July 30). Accessed from http://www.sqlsolutions.com/articles/articles/SQL_Server_Cache_Hits_Ratio_and_SQL_Server_Performance.htm.
5. Microsoft. SQL Server 2005 for Administrator Workshop.
6. SQLCast. How to Check Fragmentation in SQL 2005. (2009, December 9). Accessed From http://blogs.msdn.com/jorgepc/archive/2007/12/09/how-to-check-fragmentation-on-sql-server-2005.aspx.
7. MSSQLTips. Index Fragmentation Report in SQL Server 2005 and 2008. (2009, March 16). Accessed from http://www.mssqltips.com/tip.asp?tip=1708.
8. SQLSkills.com. Inside sys.dm_db_index_physical_stats (2010, March 9). Accessed from http://sqlskills.com/BLOGS/PAUL/post/Inside-sysdm_db_index_physical_stats.aspx.
9. SQLBlogs.com. sys.dm_io_virtual_file_stats. (2007, July 27). Accessed from http://sqlblog.com/blogs/louis_davidson/archive/2007/07/27/sys-dm-io-virtual-file-stats.aspx.
10. Database Journal. SQL Server Database File I/O Report. (2009, January 20). Accessed from http://www.databasejournal.com/features/mssql/article.php/3796551/SQL-Server-Database-File-IO-Report.htm.
11. SQL Workshops. Webcast #1, Part 1, 2 and 3. Accessed from http://www.sqlworkshops.com/.
12. SQL Workshops. Webcast #2, Part 1, 2, and 3. Accessed from http://www.sqlworkshops.com/.
13. CPU Pressure. SQL Workshops. Webcast #3, Part 1, 2, and 3.  Accessed from http://www.sqlworkshops.com/cpupressure.htm.
14. Bart Duncan’s SQL Weblog. Are you Using SQL’s Missing Index DMVs?. Access from http://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx.
15. Kalen Delaney. Inside Microsoft SQL Server 2005: The Storage Engine. Link.
16. Simple-Talk. Troubleshooting with Dynamic Management Views. Link.
17. SQL Server Magazine. Troubleshooting Parallel Queries on SQL Server 2005. Link.
18. TechNet WebCast: SQL Server 2005 Performace Troubleshooting and Tuning (Part 1 of 2) (Level 200).
19. TechNet WebCast: SQL Server 2005 Performace Troubleshooting and Tuning (Part 2 of 2) (Level 200).
20. Slava Oks’s Weblog. SQLOSDMV’s Continue. Link.
21. The Rambling DBA: Jonathan Kehayias, Tuning ‘cost threshold for parallelism’ from the Plan Cache. Link.