Clustered Index Analyser

  • Comments posted to this topic are about the item Clustered Index Analyser

  • 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/[/url]

    http://uk.linkedin.com/pub/richard-doering/5/268/37

    --------------------------------------------------

  • 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.

  • 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

  • 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... 😉 )

  • 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

  • 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[/url]

    # Part 2 - The Clustered Index[/url]

    # 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

  • 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.

  • 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 + ''')

  • 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.

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

  • 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.

    G. Milner

  • Everything worked well until the script encountered a partitioned table.

  • Hello Cameron,

    I am always trying to learn something.

    I ran your script and got:

    Msg 468, Level 16, State 9, Line 152

    Cannot 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.

    Dewes van Lohuizen,
    DBA at CSC Netherlands
    Private interest: www.mikondo.nl

  • 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 C

    JOIN 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

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply