Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Clustered Index Analyser Expand / Collapse
Author
Message
Posted Wednesday, February 24, 2010 6:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 5:19 PM
Points: 20, Visits: 161
Comments posted to this topic are about the item Clustered Index Analyser
Post #872406
Posted Thursday, March 11, 2010 2:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 4:12 AM
Points: 267, Visits: 688
running sql 2008 enterprise and this script fails

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
--------------------------------------------------
Post #880815
Posted Thursday, March 11, 2010 6:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 9:29 AM
Points: 14, Visits: 193
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.
Post #880919
Posted Thursday, March 11, 2010 6:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 14, 2014 4:35 PM
Points: 233, Visits: 460
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
Post #880932
Posted Thursday, March 11, 2010 10:33 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:19 PM
Points: 80, Visits: 722
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... )
Post #881177
Posted Thursday, March 11, 2010 8:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 5:19 PM
Points: 20, Visits: 161
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
Post #881460
Posted Thursday, March 11, 2010 9:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 5:19 PM
Points: 20, Visits: 161
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
Post #881468
Posted Friday, March 12, 2010 7:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 9:29 AM
Points: 14, Visits: 193
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.
Post #881749
Posted Friday, March 12, 2010 4:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 10:44 AM
Points: 7, 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 + ''')
Post #882193
Posted Monday, May 3, 2010 8:01 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 7:59 AM
Points: 54, Visits: 322
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.

I've tried this script on 2 different SQL Server 2005, SP3 boxes and getting the same errors.
Post #914691
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse