The DBA’s Essential Index Monitoring and Maintenance Tasks

  • SQLBlimp

    SSCertifiable

    Points: 6111

    Comments posted to this topic are about the item The DBA’s Essential Index Monitoring and Maintenance Tasks

  • Indy Mike

    SSC-Addicted

    Points: 408

    I’ve come to understand that tables below a certain size will not use the indexes assigned to them, because the table is more efficiently joined by loading it entirely into memory. In the scripts I’ve created, I put the cutoff at 1000 pages, a completely arbitrary number based only on the opinions of other posters on the topic.

    Is there any way to determine on my system’s unique configuration of memory and processors what that cutoff should really be?

  • SQLBlimp

    SSCertifiable

    Points: 6111

    Indy Mike (2/15/2016)


    I've come to understand that tables below a certain size will not use the indexes assigned to them, because the table is more efficiently joined by loading it entirely into memory. In the scripts I've created, I put the cutoff at 1000 pages, a completely arbitrary number based only on the opinions of other posters on the topic.

    Is there any way to determine on my system's unique configuration of memory and processors what that cutoff should really be?

    Wow. An interesting question to be sure. Let me see if I can find anything on that.

    Thanks

    John.

  • SQLEnthusiastic

    Mr or Mrs. 500

    Points: 593

    The query given in “Detecting Heaps” will also list tables that have indexes, for example non-clustered PK.

  • SQLBlimp

    SSCertifiable

    Points: 6111

    SQLEnthusiastic (2/15/2016)


    The query given in "Detecting Heaps" will also list tables that have indexes, for example non-clustered PK.

    Hi. That is correct. A heap is a table with no clustered index.

    Thanks

    John.

  • Recce70

    Say Hey Kid

    Points: 676

    Indy Mike (2/15/2016)


    I've come to understand that tables below a certain size will not use the indexes assigned to them, because the table is more efficiently joined by loading it entirely into memory. ..... Is there any way to determine on my system's unique configuration of memory and processors what that cutoff should really be?

    There are a number of different variables that will effect whether or not the optimiser decides to use an index or not, but I really doubt that the size of the RAM comes into play. The optimiser is going to be making a number of assumptions in the interest of producing a plan quickly, and I’m sure the details of the optimiser can and will change between versions.

    A rule of thumb (and rules of thumb are usual wrong) is you’re unlikely to see a non-covering index used unless you are selecting less than 2% of the table, and possibly as little as 0.5%. I have a feeling that one of Itzik Ben-Gan’s “Inside SQL Server” books covers this in good detail.

    EDIT:- I’ve had a look at my old copy of “Inside SQL Server 2005 – T-SQL Querying” and in Chapter 3, page 159, Itzik demonstrates how on his Orders table the selectivity of the query had to be as low as 0.72% before the index was used. He doesn’t demonstrate the calculation and/or the assumptions made by the optimiser in it’s decision, but a trial and error test is quicker and way more reliable. If you really want how the optimiser is making the decision you may be able to find something by Kimberly Tripp about it.

  • AZJim

    Default port

    Points: 1402

    My current employment only uses purchased software. I find it amazing how many vendors do not create their indexes with a fill factor (other than the default). The default is zero (or 100 percent filled). If you have a data warehouse, read only table, or a table whose primary key is in ascending sequence, then that is fine (unless there are a lot of updates to the ascending sequence table). Otherwise, start with a minimum of 90% fill (or 10% free space). You will find that there will be a lot less need to reorg/rebuild indexes. Saying this in a slightly different way, there will be a lot less fragmentation during the day. Monitor your database periodically. If it still gets fragmented too quickly, lower the fill factor.

    Here is what I run to check index fragmentation for the databases on myserver. You can tweak it according to your shop’s environment:

    USE master

    GO

    SELECT DB_NAME(database_id) AS ‘DBName’

    ,OBJECT_SCHEMA_NAME(ips.OBJECT_ID, database_id) AS ‘SchemaNm’

    ,OBJECT_NAME(ips.OBJECT_ID, database_id) AS ‘TabName’

    ,OBJECT_ID AS ‘ObjectID’

    ,ips.index_id AS ‘IndexID’

    ,CAST (avg_fragmentation_in_percent AS decimal(4,2)) AS ‘AvgFrag%’

    ,page_count AS ‘PageCount’

    FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, ‘Limited’) ips

    WHERE page_count > 1000

    AND index_type_desc <> ‘HEAP’

    AND avg_fragmentation_in_percent > 2.5

    ORDER BY DBName

    , SchemaNm

    , OBJECT_NAME(ips.OBJECT_ID, database_id)

    , IndexID, [AvgFrag%] desc;

  • SQLBlimp

    SSCertifiable

    Points: 6111

    AZJim (2/16/2016)


    My current employment only uses purchased software. I find it amazing how many vendors do not create their indexes with a fill factor (other than the default). The default is zero (or 100 percent filled). If you have a data warehouse, read only table, or a table whose primary key is in ascending sequence, then that is fine (unless there are a lot of updates to the ascending sequence table). Otherwise, start with a minimum of 90% fill (or 10% free space). You will find that there will be a lot less need to reorg/rebuild indexes. Saying this in a slightly different way, there will be a lot less fragmentation during the day. Monitor your database periodically. If it still gets fragmented too quickly, lower the fill factor.

    Here is what I run to check index fragmentation for the databases on myserver. You can tweak it according to your shop's environment:

    <snip>

    Cool; Michelle Ufford has a proc named dba_indexdefrag that diagnoses and remediates fragmentation.

    I try to standardize on fillfactor = 90 for most NC indexes.

    Thanks

    John.

  • Robert Eder

    Hall of Fame

    Points: 3106

    The query provided to detect heaps will also return table valued functions. The query below will return only user tables that are heaps.

    SELECT

    OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName

    , OBJECT_NAME(i.object_id) AS TableName

    , i.*

    FROM sys.indexes i

    INNER JOIN sys.objects o ON i.object_id = o.object_id

    WHERE i.index_id = 0

    AND o.type = ‘U’

  • gs_100

    SSC Enthusiast

    Points: 133

    I found this article on Index usage interesting. By Kimberly Tripp. Talks about Tipping Points.

    http://www.sqlskills.com/blogs/kimberly/category/the-tipping-point/

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Good article, thanks.

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    i learned the hard way that if you have a busy replication environment then dropping unused indexes on your subscribers is a huge performance improvement and will get rid of weird replication issues you might have

  • csj

    Old Hand

    Points: 376

    Sorry, but there are mistakes/wrong conclusions in the article. You write “Heaps become fragmented as time passes and new rows are inserted at the end of the heap and not where old ones are deleted. The bad part is that a heap cannot be defragmented like a clustered index can.”  The 2 following scripts shows the mistakes.

    Inserts is not at the end – where this is – but where rows are deleted. So if there is space on a page, this is used before a new page is allocated to the table. See the following script!

    — Heap – Reuse space on a page
    USE master;
    GO
    DROP DATABASE HeapDB;
    GO
    CREATE DATABASE HeapDB;
    GO
    USE HeapDB;
    CREATE TABLE dbo.HeapData
    (
     ID  INT   NOT NULL
          CONSTRAINT PK_HeapData PRIMARY KEY NONCLUSTERED,
     Txt  CHAR(200) NOT NULL
          CONSTRAINT DF_HeapData_Txt DEFAULT (REPLICATE(‘x’, 200))
    );
    GO
    SET NOCOUNT ON;
    WITH
    Id_Data
    AS
    (
    SELECT 1 AS ID
    UNION ALL
    SELECT ID + 1
     FROM Id_Data
     WHERE ID < 500
    )
    SELECT ID,
      NEWID() AS Sortkol
     INTO Data
     FROM ID_Data
     OPTION (MAXRECURSION 0);
    GO
    DECLARE @Sortkol UNIQUEIDENTIFIER = (SELECT MIN(Sortkol)
               FROM Data);
    DECLARE @ID   INT = (SELECT ID
           FROM Data
           WHERE Sortkol = @Sortkol);

    WHILE EXISTS (SELECT * FROM Data WHERE Sortkol > @Sortkol)BEGIN INSERT INTO dbo.HeapData (ID) VALUES (@ID);

    WHILE EXISTS (SELECT * FROM Data WHERE Sortkol > @Sortkol)
    BEGIN
     INSERT INTO dbo.HeapData (ID) VALUES (@ID);

     SELECT @Sortkol = MIN(Sortkol)   FROM Data  WHERE Sortkol > @Sortkol;

     SELECT @Sortkol = MIN(Sortkol)
      FROM Data
      WHERE Sortkol > @Sortkol;

     SELECT @ID = ID   FROM Data   WHERE Sortkol = @Sortkol;END;SET NOCOUNT OFF;GOSELECT SUBSTRING( sys.fn_PhysLocFormatter(%%physloc%%),      2,     LEN(sys.fn_PhysLocFormatter(%%physloc%%)) – 2)  AS PhysicalRID,  * INTO t1 FROM dbo.HeapData ORDER BY 1;GODELETE  FROM dbo.HeapData WHERE ID % 2 = 1;GOSELECT SUBSTRING( sys.fn_PhysLocFormatter(%%physloc%%),      2,     LEN(sys.fn_PhysLocFormatter(%%physloc%%)) – 2)  AS PhysicalRID,  * INTO t2 FROM dbo.HeapData ORDER BY 1;GOINSERT INTO dbo.HeapData (ID, Txt) SELECT ID + 1,   REPLICATE(‘x’, 100)  FROM dbo.HeapData;GOSELECT SUBSTRING( sys.fn_PhysLocFormatter(%%physloc%%),      2,     LEN(sys.fn_PhysLocFormatter(%%physloc%%)) – 2)  AS PhysicalRID,  * INTO t3 FROM dbo.HeapData ORDER BY 1;GOSELECT LEFT(SUBSTRING(PhysicalRID, 3, 20),     CHARINDEX(‘:’, SUBSTRING(PhysicalRID, 3, 20)) – 1) AS Pageno,  RIGHT(PhysicalRID,      CHARINDEX(‘:’, REVERSE(SUBSTRING(PhysicalRID, 3, 20))) – 1) AS Offset,      * INTO AfterPriInsert FROM t1;

     SELECT @ID = ID
      FROM Data
      WHERE Sortkol = @Sortkol;
    END;
    SET NOCOUNT OFF;
    GO
    SELECT SUBSTRING( sys.fn_PhysLocFormatter(%%physloc%%),
         2,
         LEN(sys.fn_PhysLocFormatter(%%physloc%%)) – 2)  AS PhysicalRID,
      *
     INTO t1
     FROM dbo.HeapData
     ORDER BY 1;
    GO
    DELETE
     FROM dbo.HeapData
     WHERE ID % 2 = 1;
    GO
    SELECT SUBSTRING( sys.fn_PhysLocFormatter(%%physloc%%),
         2,
         LEN(sys.fn_PhysLocFormatter(%%physloc%%)) – 2)  AS PhysicalRID,
      *
     INTO t2
     FROM dbo.HeapData
     ORDER BY 1;
    GO
    INSERT INTO dbo.HeapData (ID, Txt)
     SELECT ID + 1,
       REPLICATE(‘x’, 100)
      FROM dbo.HeapData;
    GO
    SELECT SUBSTRING( sys.fn_PhysLocFormatter(%%physloc%%),
         2,
         LEN(sys.fn_PhysLocFormatter(%%physloc%%)) – 2)  AS PhysicalRID,
      *
     INTO t3
     FROM dbo.HeapData
     ORDER BY 1;
    GO
    SELECT LEFT(SUBSTRING(PhysicalRID, 3, 20),
        CHARINDEX(‘:’, SUBSTRING(PhysicalRID, 3, 20)) – 1) AS Pageno,
      RIGHT(PhysicalRID,
         CHARINDEX(‘:’, REVERSE(SUBSTRING(PhysicalRID, 3, 20))) – 1) AS Offset,   
      *
     INTO AfterPriInsert
     FROM t1;

    SELECT LEFT(SUBSTRING(PhysicalRID, 3, 20),     CHARINDEX(‘:’, SUBSTRING(PhysicalRID, 3, 20)) – 1) AS Pageno,  RIGHT(PhysicalRID,      CHARINDEX(‘:’, REVERSE(SUBSTRING(PhysicalRID, 3, 20))) – 1) AS Offset,      * INTO AfterDelete FROM t2;

    SELECT LEFT(SUBSTRING(PhysicalRID, 3, 20),
        CHARINDEX(‘:’, SUBSTRING(PhysicalRID, 3, 20)) – 1) AS Pageno,
      RIGHT(PhysicalRID,
         CHARINDEX(‘:’, REVERSE(SUBSTRING(PhysicalRID, 3, 20))) – 1) AS Offset,   
      *
     INTO AfterDelete
     FROM t2;

    SELECT LEFT(SUBSTRING(PhysicalRID, 3, 20),     CHARINDEX(‘:’, SUBSTRING(PhysicalRID, 3, 20)) – 1) AS Pageno,  RIGHT(PhysicalRID,      CHARINDEX(‘:’, REVERSE(SUBSTRING(PhysicalRID, 3, 20))) – 1) AS Offset,      * INTO AfterSecInsert FROM t3;
    GO
    SELECT Pageno,  COUNT(*) AS RowsOnPage FROM dbo.AfterPriInsert GROUP BY Pageno ORDER BY Pageno;

    SELECT LEFT(SUBSTRING(PhysicalRID, 3, 20),
        CHARINDEX(‘:’, SUBSTRING(PhysicalRID, 3, 20)) – 1) AS Pageno,
      RIGHT(PhysicalRID,
         CHARINDEX(‘:’, REVERSE(SUBSTRING(PhysicalRID, 3, 20))) – 1) AS Offset,   
      *
     INTO AfterSecInsert
     FROM t3;
    GO
    SELECT Pageno,
      COUNT(*) AS RowsOnPage
     FROM dbo.AfterPriInsert
     GROUP BY Pageno
     ORDER BY Pageno;

    SELECT Pageno,  COUNT(*) AS RowsOnPage FROM dbo.AfterDelete GROUP BY Pageno ORDER BY Pageno;

    SELECT Pageno,
      COUNT(*) AS RowsOnPage
     FROM dbo.AfterDelete
     GROUP BY Pageno
     ORDER BY Pageno;

    SELECT Pageno,
      COUNT(*) AS RowsOnPage
     FROM dbo.AfterSecInsert
     GROUP BY Pageno
     ORDER BY Pageno;
    GO

    And since 2008 it has been possible to rebuild a HEAP – see the following script! 

    USE master;
    DROP DATABASE HeapDB;
    GO
    CREATE DATABASE HeapDB;
    GO
    USE HeapDB;
    CREATE TABLE dbo.HeapData
    (
     ID  INT   NOT NULL
          CONSTRAINT PK_HeapData PRIMARY KEY NONCLUSTERED,
     Txt  CHAR(200) NOT NULL
          CONSTRAINT DF_HeapData_Txt DEFAULT (REPLICATE(‘x’, 200))
    );
    GO
    SET NOCOUNT ON;
    WITH
    Id_Data
    AS
    (
    SELECT 1 AS ID
    UNION ALL
    SELECT ID + 1
     FROM Id_Data
     WHERE ID < 500
    )
    SELECT ID,
      NEWID() AS Sortkol
     INTO Data
     FROM ID_Data
     OPTION (MAXRECURSION 0);
    GO
    DECLARE @Sortkol UNIQUEIDENTIFIER = (SELECT MIN(Sortkol)
               FROM Data);
    DECLARE @ID   INT = (SELECT ID
           FROM Data
           WHERE Sortkol = @Sortkol);

    WHILE EXISTS (SELECT * FROM Data WHERE Sortkol > @Sortkol)BEGIN INSERT INTO dbo.HeapData (ID) VALUES (@ID);

    WHILE EXISTS (SELECT * FROM Data WHERE Sortkol > @Sortkol)
    BEGIN
     INSERT INTO dbo.HeapData (ID) VALUES (@ID);

     SELECT @Sortkol = MIN(Sortkol)   FROM Data  WHERE Sortkol > @Sortkol;

     SELECT @Sortkol = MIN(Sortkol)
      FROM Data
      WHERE Sortkol > @Sortkol;

     SELECT @ID = ID   FROM Data   WHERE Sortkol = @Sortkol;END;SET NOCOUNT OFF;GOSELECT SUBSTRING( sys.fn_PhysLocFormatter(%%physloc%%),      2,     LEN(sys.fn_PhysLocFormatter(%%physloc%%)) – 2)  AS PhysicalRID,  * INTO t1 FROM dbo.HeapData ORDER BY 1;GODELETE  FROM dbo.HeapData WHERE ID % 2 = 1;GOSELECT SUBSTRING( sys.fn_PhysLocFormatter(%%physloc%%),      2,     LEN(sys.fn_PhysLocFormatter(%%physloc%%)) – 2)  AS PhysicalRID,  * INTO t2 FROM dbo.HeapData ORDER BY 1;GOALTER TABLE dbo.HeapData REBUILD;GOSELECT SUBSTRING( sys.fn_PhysLocFormatter(%%physloc%%),      2,     LEN(sys.fn_PhysLocFormatter(%%physloc%%)) – 2)  AS PhysicalRID,  * INTO t3 FROM dbo.HeapData ORDER BY 1;GOSELECT LEFT(SUBSTRING(PhysicalRID, 3, 20),     CHARINDEX(‘:’, SUBSTRING(PhysicalRID, 3, 20)) – 1) AS Pageno,  RIGHT(PhysicalRID,      CHARINDEX(‘:’, REVERSE(SUBSTRING(PhysicalRID, 3, 20))) – 1) AS Offset,      * INTO AfterPriInsert FROM t1;

     SELECT @ID = ID
      FROM Data
      WHERE Sortkol = @Sortkol;
    END;
    SET NOCOUNT OFF;
    GO
    SELECT SUBSTRING( sys.fn_PhysLocFormatter(%%physloc%%),
         2,
         LEN(sys.fn_PhysLocFormatter(%%physloc%%)) – 2)  AS PhysicalRID,
      *
     INTO t1
     FROM dbo.HeapData
     ORDER BY 1;
    GO
    DELETE
     FROM dbo.HeapData
     WHERE ID % 2 = 1;
    GO
    SELECT SUBSTRING( sys.fn_PhysLocFormatter(%%physloc%%),
         2,
         LEN(sys.fn_PhysLocFormatter(%%physloc%%)) – 2)  AS PhysicalRID,
      *
     INTO t2
     FROM dbo.HeapData
     ORDER BY 1;
    GO
    ALTER TABLE dbo.HeapData REBUILD;
    GO
    SELECT SUBSTRING( sys.fn_PhysLocFormatter(%%physloc%%),
         2,
         LEN(sys.fn_PhysLocFormatter(%%physloc%%)) – 2)  AS PhysicalRID,
      *
     INTO t3
     FROM dbo.HeapData
     ORDER BY 1;
    GO
    SELECT LEFT(SUBSTRING(PhysicalRID, 3, 20),
        CHARINDEX(‘:’, SUBSTRING(PhysicalRID, 3, 20)) – 1) AS Pageno,
      RIGHT(PhysicalRID,
         CHARINDEX(‘:’, REVERSE(SUBSTRING(PhysicalRID, 3, 20))) – 1) AS Offset,   
      *
     INTO AfterPriInsert
     FROM t1;

    SELECT LEFT(SUBSTRING(PhysicalRID, 3, 20),     CHARINDEX(‘:’, SUBSTRING(PhysicalRID, 3, 20)) – 1) AS Pageno,  RIGHT(PhysicalRID,      CHARINDEX(‘:’, REVERSE(SUBSTRING(PhysicalRID, 3, 20))) – 1) AS Offset,      * INTO AfterDelete FROM t2;

    SELECT LEFT(SUBSTRING(PhysicalRID, 3, 20),
        CHARINDEX(‘:’, SUBSTRING(PhysicalRID, 3, 20)) – 1) AS Pageno,
      RIGHT(PhysicalRID,
         CHARINDEX(‘:’, REVERSE(SUBSTRING(PhysicalRID, 3, 20))) – 1) AS Offset,   
      *
     INTO AfterDelete
     FROM t2;

    SELECT LEFT(SUBSTRING(PhysicalRID, 3, 20),     CHARINDEX(‘:’, SUBSTRING(PhysicalRID, 3, 20)) – 1) AS Pageno,  RIGHT(PhysicalRID,      CHARINDEX(‘:’, REVERSE(SUBSTRING(PhysicalRID, 3, 20))) – 1) AS Offset,      * INTO AfterRebuild FROM t3;GOSELECT Pageno,  COUNT(*) AS RowsOnPage FROM dbo.AfterPriInsert GROUP BY Pageno ORDER BY Pageno;

    SELECT LEFT(SUBSTRING(PhysicalRID, 3, 20),
        CHARINDEX(‘:’, SUBSTRING(PhysicalRID, 3, 20)) – 1) AS Pageno,
      RIGHT(PhysicalRID,
         CHARINDEX(‘:’, REVERSE(SUBSTRING(PhysicalRID, 3, 20))) – 1) AS Offset,   
      *
     INTO AfterRebuild
     FROM t3;
    GO
    SELECT Pageno,
      COUNT(*) AS RowsOnPage
     FROM dbo.AfterPriInsert
     GROUP BY Pageno
     ORDER BY Pageno;

    SELECT Pageno,  COUNT(*) AS RowsOnPage FROM dbo.AfterDelete GROUP BY Pageno ORDER BY Pageno;

    SELECT Pageno,
      COUNT(*) AS RowsOnPage
     FROM dbo.AfterDelete
     GROUP BY Pageno
     ORDER BY Pageno;

    SELECT Pageno,  COUNT(*) AS RowsOnPage FROM dbo.AfterRebuild GROUP BY Pageno ORDER BY Pageno;

    SELECT Pageno,
      COUNT(*) AS RowsOnPage
     FROM dbo.AfterRebuild
     GROUP BY Pageno
     ORDER BY Pageno;

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    If you rebuild a heap, how long does it take for it to become fragmented again?

  • csj

    Old Hand

    Points: 376

    alen teplitsky - Monday, March 12, 2018 12:49 PM

    If you rebuild a heap, how long does it take for it to become fragmented again?

    It is not possible to answer. It depends on how many rows you DELETE and how many UPDATE’s with changing data in variable length columns.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply