The above query will give result with query listing all the index on the table.

There is a small puzzle for all of you here. The puzzle is to write a query that will return the size for each index that is listed in above query. We need a query that will return an additional column in the above listed query and it should contain the size of the index. In our case, we will have three different sizes, which should add up to a total of 40 KB as shown in earlier query, where the total size is displayed.

Hi Pinal, there is an undocumented stored proc called sp_MSIndexSpace that will list size of indexes. However this wil not include XML indexes. To include all there is a very long query that Microsoft supplies on their list of useful DMVs, the main DMV they use is sys.dm_db_partition_stats. I will cut and paste the query tomorrow, am not taking credit for it as it is pre written!

Permission to use, copy, modify, distribute but not sell this software and its documentation for any purpose is hereby granted without fee, provided that this copyright notice appears in all copies and that both the copyright notice and this permission notice appear in supporting documentation.

No representations are made about the suitability of this software for any purpose. It is provided “as is” without express or implied warranty. */ — =================================================================== BEGIN DECLARE @IncomingBytes DECIMAL(24,2); DECLARE @KB DECIMAL(24,4) DECLARE @MB DECIMAL(24,4) DECLARE @GB DECIMAL(24,4)

Hank Freeman in Atlanta, GA used Adam Hutson version and likes it, I validated it against Sp_spaceused and the numbers match. So for me this is really good. Yes, I have modified it some, but the core is all Adam’s work.

Pinal Dave is a technology enthusiast and an independent consultant. He has authored 11 SQL Server database books, 21 Pluralsight courses and have written over 3800 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 14+ years of hands on experience he holds a Masters of Science degree and a number of database certifications. For any SQL Server Performance Tuning Issue send email at pinal @ sqlauthority.com .

Nupur Dave is a social media enthusiast and and an independent consultant.