November 1, 2007 at 4:48 pm
Hi,
I am reviewing my databases indexes and would like to know if there is a way of finding out what indexes have never been used?
Also, is there such a thing as an Index Tuning Wizard similiar to what was in 2000 in 2005?
Thanks,
November 1, 2007 at 4:59 pm
1) Assuming you have reporting services installed and are on SP2, Right click on the DB, Go to Reports | Standard Reports | Index Usage Stats
also query:
select
si.name,
db_name(ps.database_id),
ps.*
from
sys.dm_db_index_usage_stats ps
INNER JOIN
sys.indexes si
on
si.[object_id] = ps.[object_id]
and
si.index_id = ps.index_id
where
si.index_id > 0
--and user_seeks = 0
--and database_id not in (1,2, 4, 7, 8)
--optional tweaks
2) Database Engine Tuning Advisor
Edit:
Also check out sys.dm_db_missing_index_details for this
November 1, 2007 at 5:10 pm
Hi,
Reporting Services not installed.
The query returns lots or rows, (695). I assume these aren't all unused? Which of the columns do you suppose says categorically that the index is unused?
November 1, 2007 at 5:18 pm
definitely user seeks = 0, debatably user scans = 0
The best scenario is an index seek, second-best is a scan. So, if there are no seeks and no scans, the index isn't being used. If there are scans but not seeks, the index's usefulness is debatable.
Keep in mind that these are statistics gathered from the last restart of SQL Server.
If you don't have a compelling reason to not avoid installing reporting services, I'd highly recommend it. Once it's installed you can get the performance dashboard for much richer performance analysis.
November 1, 2007 at 6:15 pm
You should check this out, too:
http://www.sqlskills.com/blogs/paul/CategoryView,category,Indexes%2BFrom%2BEvery%2BAngle.aspx
In particular, this one:
(Better this time Paul? :hehe: )
November 1, 2007 at 6:28 pm
Hi,
Thanks for assitance.
Unfortunatley I am not responsible for what software is installed on the server, so Reporting Services is a no-go.
Incidently, can reporting services be run a clustered 64 bit server?
For future reference the query I ended up with is:
select
object_name(ps.object_id) AS TABLE_NAME,
si.name AS INDEX_NAME,
db_name(ps.database_id) AS DATABASE_NAME,
ps.*
from
sys.dm_db_index_usage_stats ps
INNER JOIN
sys.indexes si
on
si.[object_id] = ps.[object_id]
and
si.index_id = ps.index_id
where
si.index_id > 0
and user_seeks = 0
and user_scans = 0
--and database_id = 5
order by
object_name(ps.object_id),
si.name
November 1, 2007 at 6:37 pm
Although I've not worked with a clustered environment before, I don't see why not.
Also, if you have a development machine with access to the server, you can have reporting services on the dev machine and look at the reports for the server there. That's how I do it. Developer editions of SQL Server 2K5 are cheap. I got mine for $40.
November 2, 2007 at 1:34 am
Pam Brisjar (11/1/2007)
If you don't have a compelling reason to not avoid installing reporting services, I'd highly recommend it. Once it's installed you can get the performance dashboard for much richer performance analysis.
Reporting services isn't required to use the performance dashboard. Reporting services is the app that handles management, security, the automatic running and delivery of reports, etc, etc.
The performance dashboard, and the other reports in management studio use the reporting services report format, but they run within management studio.
You do have to download th dashboard seperatly, as it is not included in SP2
See http://blogs.msdn.com/psssql/archive/2007/03/30/sql-server-2005-performance-dashboard-reports.aspx
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
November 2, 2007 at 2:14 am
Back on the topic of unused indexes.
The index usage DMV t4racks only since SQL last started. If an indx has not been used at all (no seeks, no scans, no lookups, no updates) when it will not be mentioned in the DMV. Indexes are only entered into that DMV when they are used in some way
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
November 2, 2007 at 3:49 am
It's worse than that. Certain events, such as restoring a database or changing it to or from read-only status, will cause the counters for that database to be removed. If your database has auto-close set, then every time it "closes" the counters will also disappear.
John
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply