Index Breakdown

Question

Post reply

Index Breakdown

Sean Smith

SSCertifiable

Points: 6016
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: 6016 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: 101188 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: 6016 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: 6016 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: 8502 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: 6016 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: 6016 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: 164 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: 6016 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

Index Breakdown

Cookies on SQLServerCentral