SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index Breakdown


Index Breakdown

Author
Message
Sean Smith (SSC)
Sean Smith (SSC)
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2181 Visits: 1022
Comments posted to this topic are about the item Index Breakdown
vandana.1103
vandana.1103
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 104
Undoubtedly an excellent post!
However, if one wants to learn about indexes from the scratch, what would you suggest?
robert.bingham
robert.bingham
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 478
@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
Greg Drake
Say Hey Kid
Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)

Group: General Forum Members
Points: 675 Visits: 536
I like the organization you put into this.
Sean Smith (SSC)
Sean Smith (SSC)
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2181 Visits: 1022
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. Smile
ScottPletcher
ScottPletcher
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34775 Visits: 7732
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
hfreeman
hfreeman
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 46
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 (SSC)
Sean Smith (SSC)
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2181 Visits: 1022
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. Smile
Sean Smith (SSC)
Sean Smith (SSC)
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2181 Visits: 1022
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
pdanes
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3130 Visits: 1366
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search