Index Breakdown

Question

Post reply

Index Breakdown

Sean Smith

SSCertifiable

Points: 5977
More actions
December 21, 2013 at 11:51 pm

#396021

Comments posted to this topic are about the item Index Breakdown

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

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

vandana.1103 SSC Veteran Points: 204 More actions · Answer 1

Undoubtedly an excellent post!

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

robert.bingham SSC Enthusiast Points: 121 More actions · Answer 2

@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.

Greg Drake Ten Centuries Points: 1071 More actions · Answer 3

Greg Drake

Ten Centuries

Points: 1071

December 23, 2013 at 10:51 am

#1675661

I like the organization you put into this.

Sean Smith SSCertifiable Points: 5977 More actions · Answer 4

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. 🙂

ScottPletcher SSC Guru Points: 101095 More actions · Answer 5

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".

hfreeman Mr or Mrs. 500 Points: 517 More actions · Answer 6

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)

Sean Smith SSCertifiable Points: 5977 More actions · Answer 7

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. 🙂

Sean Smith SSCertifiable Points: 5977 More actions · Answer 8

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.

pdanes SSCrazy Eights Points: 8495 More actions · Answer 9

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

gclausen SSC Enthusiast Points: 112 More actions · Answer 10

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!!

Sean Smith SSCertifiable Points: 5977 More actions · Answer 11

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. 😉

Sean Smith SSCertifiable Points: 5977 More actions · Answer 12

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. 🙂

MiketheDBA SSC Enthusiast Points: 154 More actions · Answer 13

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.

Sean Smith SSCertifiable Points: 5977 More actions · Answer 14

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