SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Cameron Wells / Clustered Index Analyser / Latest Posts

RE: Clustered Index Analyser

Dewes van Lohuizen — Wed, 20 Jul 2011 01:30:05 GMT

Hi Cameron,thanks for your explanation.

RE: Clustered Index Analyser

cameron.wells — Tue, 19 Jul 2011 19:12:55 GMT

Hi Dewes,I'll try and explain this in layman's terms, as these are the terms I understand. This issue is not uncommon I believe. Collation's describe the ordering of data within columns. Mainly collation's are used to separate different languages alphabet ordering, as these differ between languages.Specifically for your issue, Some columns on the database you are running the script on are using collation Latin1_General_CI_AS, whereas other columns are using SQL_Latin1_General_CP1_CI_AS. - SQL_Latin1_General_CP1_CI_AS (legacy SQL Specific collation, which doesn't support unicode, and and will be removed from SQL Server one day) - Latin1_General_CI_AS (windows collation which supports unicode) Run this script to identify the columns on your database and their different collations:SELECT C.Name, C.Collation_Name FROM SYS.COLUMNS CJOIN SYS.OBJECTS O ON O.Object_id = C.Object_id WHERE C.OBJECT_ID > 100 --AND C.Collation_Name <> 'Latin1_General_CI_AS' AND O.Type = 'U'I believe that because these collations aren't consistent, the error is occurring when running my script. Other scripts may also have a similar issue with the inconsistent collations.You can overcome this by casting collations at every join, but i wont be updating my script to account for this as it will be time consuming. I'd advise that your column collations be consistent.Regards,Cameron

RE: Clustered Index Analyser

Dewes van Lohuizen — Tue, 19 Jul 2011 08:25:41 GMT

Hello Cameron,I am always trying to learn something.I ran your script and got:Msg 468, Level 16, State 9, Line 152Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.I do not know enough about the scripts to make it running but possibly you can handle that.Thanks,Dewes.

RE: Clustered Index Analyser

EdSwiedler — Thu, 14 Jul 2011 04:52:14 GMT

Everything worked well until the script encountered a partitioned table.

RE: Clustered Index Analyser

Greg Milner — Fri, 18 Feb 2011 15:01:22 GMT

Wonderful script, Cameron. Thanks! I have to come up with an Auditing solution for data changes and you script helped me happily find out that most of my tables have only one column for the clustered index which is almost always the primary key. This will make my Auditing solution easier. Very useful, thank you so much.

RE: Clustered Index Analyser

jaberjung-531432 — Thu, 01 Jul 2010 05:38:42 GMT

Thanks for sharing this script. i just tried it on 2008, working fine. But getting errors while running this script on 2000.

RE: Clustered Index Analyser

jumpin — Mon, 03 May 2010 08:01:27 GMT

Msg 515, Level 16, State 2, Line 113Cannot 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.

RE: Clustered Index Analyser

cjones-956169 — Fri, 12 Mar 2010 16:11:54 GMT

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 top3. ReplaceOBJECT_ID(N''' + @TableName + ''')withOBJECT_ID(N''' + @SchemaName + '.' + @TableName + ''')

RE: Clustered Index Analyser

tamie.mcdonald — Fri, 12 Mar 2010 07:10:06 GMT

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.

RE: Clustered Index Analyser

cameron.wells — Thu, 11 Mar 2010 21:04:58 GMT

[quote][b]thisisfutile (3/11/2010)[/b][hr][quote][b]tamie.mcdonald (3/11/2010)[/b][hr]Why would you cluster an index for an Identity column key? ...so wouldn't you want to cluster it around a logical key?[/quote]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... ;-) )[/quote]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:# [url=http://www.sqlservercentral.com/articles/Indexing/68439/]Part 1 - The basics of indexes[/url]# [url=http://www.sqlservercentral.com/articles/Indexing/68563/]Part 2 - The Clustered Index[/url]# [url=http://www.sqlservercentral.com/articles/Indexing/68636/]Part 3 - The Non-clustered index[/url]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

RE: Clustered Index Analyser

cameron.wells — Thu, 11 Mar 2010 20:27:08 GMT

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

RE: Clustered Index Analyser

thisisfutile — Thu, 11 Mar 2010 10:33:21 GMT

[quote][b]tamie.mcdonald (3/11/2010)[/b][hr]Why would you cluster an index for an Identity column key? ...so wouldn't you want to cluster it around a logical key?[/quote]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... ;-) )

RE: Clustered Index Analyser

Ligtorn — Thu, 11 Mar 2010 06:26:08 GMT

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

RE: Clustered Index Analyser

tamie.mcdonald — Thu, 11 Mar 2010 06:01:37 GMT

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.

RE: Clustered Index Analyser

r5d4 — Thu, 11 Mar 2010 02:17:57 GMT

running sql 2008 enterprise and this script fails :(it repeats the message >Msg 208, Level 16, State 1, Line 1Invalid object name 'tablename'.for each one of my tables....I believe you've omitted the schemaname from the codeThe 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.rowsfrom sys.partitions P Join sys.tables T On P.object_Id = T.object_id Join sys.schemas S On T.schema_id = S.schema_idWhere 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 DoeringMCITP SQL 2008 Administrator + DeveloperMCDBA SQL 2000 | MCSE Windows 2000[url=http://sqlsolace.blogspot.com/]http://sqlsolace.blogspot.com/[/url][url=http://uk.linkedin.com/pub/richard-doering/5/268/37]http://uk.linkedin.com/pub/richard-doering/5/268/37[/url]--------------------------------------------------

Clustered Index Analyser

cameron.wells — Wed, 24 Feb 2010 18:23:06 GMT

Comments posted to this topic are about the item [B]Clustered Index Analyser[/B]