Are my SQL Indexes Being Used? Utilization Rate?

[fhlee]

Hi,
Thanks for your time. Microsoft SQL Server 2003.

I just took over a fairly large Database (4.5GB) and I notice there are A LOT of (large) indexes being built for certain (large) tables. I think some are redundant and are not used; thus wasting store space.

Is there are a way for me to:
1. Find out how often an index is used?
*Note: All the SQL statements are issued from program as Text Commands; So, I cannot analyze the Views/SP to see which index are being used.

2. Find out the size (as in MB) of each Index.

Again, thanks for your time. Any ideas/pointers will be appreciated.

_____________________


~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

 

[TitleistDBA]

you can run
sp_spaceused [index_name] to find how many rows and the size of you index. as for finding which indexes are being used that is going to be allot more difficult. if you know all the querries you can use query analyzer show plan. If you don't then you will need to run profiler to get them all.

http://www.acushnetcompany.com

http://www.titleist.com

 

[Catadmin]

SQL Server 2003???

What client tools are you using to access your Server?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[fhlee]

CatAdmin,
Enterprise Manager.

Hi TitleIstDBA,
Thanks for the pointer

Anyone who might be interested,
I came up with an SQL Statment that will List Of All Indexes with its Size in a Database.
-----------------------
List Of ALL Indexes Inside A Database With Size.
Reserved & Used Size in Mb.
-----------------------

SELECT TOP 100 PERCENT
dbo.sysindexes.id AS TableID, dbo.sysobjects.name AS TableName,
dbo.sysindexes.indid AS IndexID, dbo.sysindexes.name AS IndexName,
dbo.sysindexes.reserved * 8 / 1024 AS ReservedInMB,
dbo.sysindexes.used * 8 / 1024 AS UsedInMB,
dbo.sysindexes.keycnt AS NumberOfKeys,
dbo.sysindexes.rowcnt AS [RowCount],
dbo.sysindexes.OrigFillFactor
FROM dbo.sysindexes
LEFT OUTER JOIN
dbo.sysobjects
ON dbo.sysindexes.id = dbo.sysobjects.id
WHERE (dbo.sysobjects.type = 'U')
AND (dbo.sysindexes.reserved > 0)
AND (dbo.sysindexes.indid > 0)
ORDER BY dbo.sysobjects.name, dbo.sysindexes.name

-------------------
However, I have not been able to figure out which Index is being used and which are not...

If anyone else have any pointers, will appreciate. Meanwhile, I will explore the Profiler.




~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

 

[timscronin]

If you want to find out what indexes are being used I suggest you run profile and capture the sql and sp's that are used against your data. You could then put that in QA and see how good the statements execution plans are

 

[jester777]

One of the EventClass options you can monitor with SQL Profiler is 'Execution Plan'. What you can look for in each of the plans shown are things like INDEX SEEK, INDEX SCAN, CLUSTERED INDEX SEEK, what you will see further along that line is the INDEX being used. Therefore you can get an idea of which indexes are used regularly.

Capturing a full profiler trace and feeding that into the Index Tuning wizard will show which indexes SQL thinks you don't need and which new ones could be added to improve performance.