Index Breakdown

  • Comments posted to this topic are about the item Index Breakdown

  • Undoubtedly an excellent post!

    However, if one wants to learn about indexes from the scratch, what would you suggest?

  • @vandana - there is an excellent "staircase" series by Dave Durant at http://www.sqlservercentral.com/stairway/72399/. Also, anything by Gila Monster (Gail Shaw) is well worth reading.

  • I like the organization you put into this.

  • Thank you all very much! And I have to agree, anything by Gail Shaw is always superb. Also, BOL is a great resource, and personally I just create different scenarios on test tables and see what happens (representation in the DMVs, how the optimizer utilizes the indexes, etc.). Again, I really don't consider myself an indexing expert, but there are many great whitepapers online which you can start with. 🙂

  • Excellent! The other thing you need to include in your analysis is the "missing index" info from SQL (DMVs sys.dm_db_missing_index*).

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • The code faulters with this error

    Lookup Error - SQL Server Database Error: Divide by zero error encountered on line 154

    the start of the select query

    -----------------------------------------------------------------------------------------------------------------------------

    --Main Query: Final Display / Output

    -----------------------------------------------------------------------------------------------------------------------------

    SELECT

    (CASE

    WHEN sqBAQ.row_filter = 1 THEN sqBAQ.[type]

    ELSE ''

    END) AS object_type

    Hank Freeman
    Senior SQL Server DBA / Data & Solutions Architect
    hfreeman@msn.com
    678-414-0090 (Personal Cell)

  • ScottPletcher (4/11/2014)


    Excellent! The other thing you need to include in your analysis is the "missing index" info from SQL (DMVs sys.dm_db_missing_index*).

    I agree, but I found it a bit "heavy" to run / slowed down the query. But I do have this available in my SQL Server System Report on this site if needed. 🙂

  • hfreeman (4/11/2014)


    The code faulters with this error

    Lookup Error - SQL Server Database Error: Divide by zero error encountered on line 154

    the start of the select query

    -----------------------------------------------------------------------------------------------------------------------------

    --Main Query: Final Display / Output

    -----------------------------------------------------------------------------------------------------------------------------

    SELECT

    (CASE

    WHEN sqBAQ.row_filter = 1 THEN sqBAQ.[type]

    ELSE ''

    END) AS object_type

    Are you able to provide more details? The field you listed (as is) shouldn't give that type of error as it is only a CASE Expression.

  • Excellent script - many thanks. Already found and fixed some indexing screw-ups in my databases, just on the first run-through. This is something I will be studying in detail, and using regularly in the future. If you ever get to Prague, I have a cold one waiting for you.

    Pete

  • Very good post! I like the breakdown and information the script provides. I'm finding that indexing can be very frustrating and time consuming. Your post will definitely help!!

  • pdanes (10/30/2014)


    Excellent script - many thanks. Already found and fixed some indexing screw-ups in my databases, just on the first run-through. This is something I will be studying in detail, and using regularly in the future. If you ever get to Prague, I have a cold one waiting for you.

    Pete

    Fantastic! It's always great to find issues and fix them right away. I'm going to hold you to that beer offer BTW. 😉

  • gclausen (10/30/2014)


    Very good post! I like the breakdown and information the script provides. I'm finding that indexing can be very frustrating and time consuming. Your post will definitely help!!

    I hear what you're saying. It was a driving force for me to build this script actually. 🙂

  • Getting errors executing the script as well.. Maybe is related to the fact that I have multiple schemas in the database?

    i.e. Report.work_order,

    dbo.items, work.operations

    (0 row(s) affected)

    (536 row(s) affected)

    Warning: Null value is eliminated by an aggregate or other SET operation.

    (536 row(s) affected)

    Msg 8134, Level 16, State 1, Line 221

    Divide by zero error encountered.

    Warning: Null value is eliminated by an aggregate or other SET operation.

  • Did you run the complete script? The first part should eliminate the divide by zero errors:

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    SET NOCOUNT ON

    SET ANSI_WARNINGS OFF

    SET ARITHABORT OFF

    SET ARITHIGNORE ON

    SET TEXTSIZE 2147483647

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

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