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


Clustered Index Analyser


Clustered Index Analyser

Author
Message
cameron.wells
cameron.wells
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 185
Comments posted to this topic are about the item Clustered Index Analyser
r5d4
r5d4
Old Hand
Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)

Group: General Forum Members
Points: 355 Visits: 819
running sql 2008 enterprise and this script fails Sad

it repeats the message >

Msg 208, Level 16, State 1, Line 1
Invalid object name 'tablename'.

for each one of my tables....

I believe you've omitted the schemaname from the code
The script will work fine if you have one schema in the database and run it as a user who has that schema as their default.

e.g. at line 295 where it loops tables to get>

SET @Tmp = ' UPDATE #ClusteredIndexes
SET TableRowCount = (SELECT COUNT(*)
FROM '+@TableName+'
WHERE ClusteredIndexID = ' + CONVERT(VARCHAR,@ClusteredIndexID) + ')
WHERE ClusteredIndexID = ' + CONVERT(VARCHAR,@ClusteredIndexID) + ' '


You need to add the schemaname to the code to pick up the row counts correctly. Otherwise the script could also fail if 2 or more tables are named the same but belong to different schemas (e.g. Import.MyData and Processing.MyData)

I would also suggest an alternative (faster) way of getting your row counts >

select
S.name as schemaname,
T.name as tablename,
P.rows
from
sys.partitions P Join
sys.tables T On
P.object_Id = T.object_id Join
sys.schemas S On
T.schema_id = S.schema_id
Where
P.index_id in (0,1)

Looks really good though when these are ironed out and i'd certainly use.
Inspired..., thank you.

r
--------------------------------------------------
Richard Doering

MCITP SQL 2008 Administrator + Developer
MCDBA SQL 2000 | MCSE Windows 2000
http://sqlsolace.blogspot.com/
http://uk.linkedin.com/pub/richard-doering/5/268/37
--------------------------------------------------
tamie.mcdonald
tamie.mcdonald
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 227
Why would you cluster an index for an Identity column key? I understand a unique index on an identity key, but clustering is for organizing your data on the disk, so wouldn't you want to cluster it around a logical key? ex. If it is a table of data on states, around the logical state value ie. Texas, Louisiana, etc.
Ligtorn
Ligtorn
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

Group: General Forum Members
Points: 285 Visits: 489
Another limitation is the small varchar sizes for @TableName, @ClusterKeyName and ClusteredIndexNames. You should use sysname instead of varchar(50) and varchar(75). This will also make the script usable international.

Also tablename, indexname, columnname, should be escaped by using QUOTENAME, I have inherited multiple databases with spaces, dots and other special characters in object names. I betting others have to.

Otherwise a nice script, which can come in handy
thisisfutile
thisisfutile
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 Visits: 990
tamie.mcdonald (3/11/2010)
Why would you cluster an index for an Identity column key?

...so wouldn't you want to cluster it around a logical key?


I too have the same curiosity. It's purely academic, mainly because I'm only now getting into the fine-tuning of an existing database and while I could research other articles, these are exactly the questions I have. Hopefully a good discussion will stem from this (or some links to some of those articles I need to read... ;-) )
cameron.wells
cameron.wells
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 185
Richard,

Thanks for your constructive input; not including the schema was a definite flaw. Your suggestion for Table Row count has also improved performance on the database I'm using to test this script from 2 minutes 45 seconds to just 35 seconds.

Ligtorn,

I've also made changes regarding your input. Thanks also for making the time to look at the script and provide feedback.

I've resubmitted the script and it should be updated on the SQL SERVER CENTRAL site within the next day or so.

Enjoy!

Cameron
cameron.wells
cameron.wells
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 185
thisisfutile (3/11/2010)
tamie.mcdonald (3/11/2010)
Why would you cluster an index for an Identity column key?

...so wouldn't you want to cluster it around a logical key?


I too have the same curiosity. It's purely academic, mainly because I'm only now getting into the fine-tuning of an existing database and while I could research other articles, these are exactly the questions I have. Hopefully a good discussion will stem from this (or some links to some of those articles I need to read... ;-) )


I have read many articles about indexes, but there is one main group of articles that has made my opinion firm about using an identity column as the clustered index, unless there is a specific reason not to. The collection of articles i'm referring to have been written by Gail Shaw and I read them on the SQL Server Central web site. These are:

# Part 1 - The basics of indexes
# Part 2 - The Clustered Index
# Part 3 - The Non-clustered index

This is where I learnt about the importance of having a stream lined and simple clustered index, because its flow on effect regarding performance and disk space is exponential which is the crux of this Clustered Index Analyser script.

Thanks,

Cameron
tamie.mcdonald
tamie.mcdonald
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 227
I do agree with Gail Shaw's articles about the indexes, but I did not read where he suggested to make the clustered unique index on the unique identity column of a table. Making the clustered index on the logical, unique, as narrow as possible columns stored at the beginning of the table, where these columns are ever increasing in value with new inserts, and not changing in key values for the existing rows... in my humble opinion is the right way to choose the clustered index. I do have unique non-clustered indexes on identity columns when I need to use those values to retrieve row(s).

By organizing the logical data together, it makes it efficient for windows and reports retrieving the data by the logical order to be more efficient in data access instead of jumping all over the disk to get a couple thousand rows, they are neatly stored by their buddies.

In my understanding this is the concept of clustering indexes, not only for MS SQL Server, but many other relational and non-relational databases.
cjones-956169
cjones-956169
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 282
1. The @Iteration set statement needs 'AND C.index_id = I.index_id' added to 'JOIN SYS.INDEX_COLUMNS C'

SET @Iteration = (SELECT MAX(C.KEY_ORDINAL)
FROM SYS.INDEXES I

JOIN SYS.OBJECTS S -- Joining on SYS.OBJECTS to get the TableName
ON S.OBJECT_ID = I.Object_ID -- to ensure the

JOIN SYS.INDEX_COLUMNS C
ON C.OBJECT_ID = I.Object_ID
AND C.index_id = I.index_id -- ** 03/12/2010 CAJ **

WHERE I.Type = @ClusteredIndexType
AND I.OBJECT_ID NOT IN (SELECT OBJECT_ID
FROM SYS.ALL_OBJECTS
WHERE TYPE = 'S') -- Not system tables
AND S.Type = 'U'
AND S.is_ms_shipped = 0)

2. Add 'DECLARE @SchemaName SYSNAME;' at the top

3. Replace
OBJECT_ID(N''' + @TableName + ''')
with
OBJECT_ID(N''' + @SchemaName + '.' + @TableName + ''')
jumpin
jumpin
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 381
Msg 515, Level 16, State 2, Line 113
Cannot insert the value NULL into column 'NonClusteredIndexCount', table 'tempdb.dbo.#ClusteredIndexes___________________________________________________________________________________________________000000009C07'; column does not allow nulls. INSERT fails.

Angry I've tried this script on 2 different SQL Server 2005, SP3 boxes and getting the same errors.
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