|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Determine Index Usage...Hello...
I am looking for a way in SQL Server 2000 to determine which indexes are used and which indexes are NOT used. I have several databases that have gone through several iterations and is in need of index cleaning. Please advise... Cheers! Brett Brett Davis (sqlsquir***@msn.com) writes:
> I am looking for a way in SQL Server 2000 to determine which indexes are You can run Profiler and catch execution plans, and then tick of which> used and which indexes are NOT used. I have several databases that have > gone through several iterations and is in need of index cleaning. indexes that are actually in use. You can also present a workload to the Index Tuning Wizard to see what it says. But before you drop an index, you will need to ask around. Some indexes may be good for a report that is only run at the end of the month. Then again, you can just be aggressive and drop some indexes you don't think are needed, and then see how long time it takes before someone screams... -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >But before you drop an index, you will need to ask around. Some indexes may It's often better to remove these indexes & only put them back at the end of >be good for a report that is only run at the end of the month.< the month anyway. Providing EOM reporting users with a backup copy of the database, taken precisely at whenever EOM falls is also not a bad idea as it's effectively a snapshot & can also have its indexes changed for specific reporting requirements. I often wonder why people bother running reports against live OLTP systems as they're either working with moving targets or they have to stop all OLTP until they can get their reporting completed... Regards, Greg Linwood SQL Server MVP Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns983269FA3CF1Yazorman@127.0.0.1... > Brett Davis (sqlsquir***@msn.com) writes: >> I am looking for a way in SQL Server 2000 to determine which indexes are >> used and which indexes are NOT used. I have several databases that have >> gone through several iterations and is in need of index cleaning. > > You can run Profiler and catch execution plans, and then tick of which > indexes that are actually in use. You can also present a workload to the > Index Tuning Wizard to see what it says. > > But before you drop an index, you will need to ask around. Some indexes > may be good for a report that is only run at the end of the month. > > Then again, you can just be aggressive and drop some indexes you don't > think are needed, and then see how long time it takes before someone > screams... > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Greg Linwood (g_linw***@hotmail.com) writes:
> It's often better to remove these indexes & only put them back at the Good points. I guess the answer is "ease of manageability". If you forget> end of the month anyway. Providing EOM reporting users with a backup > copy of the database, taken precisely at whenever EOM falls is also not > a bad idea as it's effectively a snapshot & can also have its indexes > changed for specific reporting requirements. to add back those indexes in time, you may some managers screaming at you. And you never know if someone finds reason to run that report in the middle of the month - and want to re-run report because some data from last months has been corrected, so that copy you mentioned won't do. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics