Query to find indexes that are larger than 900 bytes

Question

Query to find indexes that are larger than 900 bytes

Santhoshkumar KB

SSCertifiable

Points: 5066
More actions
November 3, 2016 at 11:19 pm

#331762

In one of our databases, I just observed an index which is over 900 bytes.
Index is on 3 key fields (no included columns)
Col1 INT, Col2 NVARCHAR(255), Col3 NVARCHAR(255)
Total datalength for above is 1024 bytes (since nvarchar takes 2 bytes per character)
This is the query that I have come up with and gives me the results I need.
Am I overlooking something here by using sys.columns.max_length field?
SELECT ss.name AS SchemaName, so.name AS TableName, si.name AS IndexName, SUM(sc.max_length) AS KeyColumnMaxLength
FROM sys.objects so JOIN sys.columns sc ON so.object_id = sc.object_id
JOIN sys.schemas ss ON so.schema_id = ss.schema_id
JOIN sys.indexes si ON sc.object_id = si.object_id
JOIN sys.index_columns sic ON si.object_id = sic.object_id AND si.index_id = sic.index_id AND sic.column_id = sc.column_id
WHERE so.type = 'U' --AND so.name NOT IN('sysdiagrams')
AND sic.is_included_column = 0--only key column length counts for index max length of 900 bytes
GROUP BY ss.name, so.name, si.name
--HAVING SUM(sc.max_length) > 900--filter only the ones with > 900
ORDER BY 4 DESC
Thanks,
Santhosh
Human Knowledge Belongs To The World !!

Viewing 5 posts - 1 through 4 (of 4 total)

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

It seems correct, the only issue would be with xml columns, but I'm sure those would be handled differently.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Luis Cazares (11/4/2016)
It seems correct, the only issue would be with xml columns, but I'm sure those would be handled differently.

Not a concern here, since XML columns, like the MAX datatypes can't be in the index key.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 3

Thank you...

I was wondering how the max_length field was calculated and tried digging the system view sys.columns

and stuck at sys.syscolpars and thought to simply trust the view !!

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

GilaMonster (11/4/2016)
Luis Cazares (11/4/2016)
It seems correct, the only issue would be with xml columns, but I'm sure those would be handled differently.
Not a concern here, since XML columns, like the MAX datatypes can't be in the index key.

I mentioned it, because the query returns XML indexes. Which of course use xml columns.

As mentioned, it's not a concern. Especially when using the HAVING clause.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2