[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Server 2005 index size

Posted on 2010-01-06
4
Medium Priority
?
269 Views
Last Modified: 2013-11-24
How do i find the size of an index in a table. with userscans,, userlookups, userseeks, userupdates etc?
0
Comment
Question by:espanolanthony
4 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 26193905
0
 
LVL 43

Accepted Solution

by:
pcelba earned 1500 total points
ID: 26193909
You may use info from sys.dm_db_index_physical_stats. Following procedure was published at http://blogs.msdn.com/sferg/archive/2008/03/28/calculating-sql-server-index-size.aspx
CREATE PROCEDURE [dbo].[IndexSize]

      @TableName NVARCHAR(256),

      @IndexName VARCHAR(256)

AS 

BEGIN

      DECLARE @index_id INT

      DECLARE @index_size BIGINT SET @index_size = 0

 

      SELECT @index_id = index_id FROM sys.indexes WHERE object_id = OBJECT_ID(@TableName) AND name = @IndexName

 

      SELECT

            @index_size = @index_size + (avg_record_size_in_bytes * record_count)

      FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(@TableName), @index_id , NULL, 'DETAILED')

 

      SELECT @index_size as IndexSizeBytes

END

Open in new window

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26193911

SELECT * FROM sys.dm_db_index_usage_stats
0
 
LVL 1

Author Closing Comment

by:espanolanthony
ID: 31673680
thanks
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question