SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Indexes
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sbrazier
Starting Member

19 Posts

Posted - 06/04/2013 :  11:09:12  Show Profile  Reply with Quote
I know we are behind in SQL. Unfortunately we are still using SQL 2000. I was using sql profile and notice that my number of reads for a simple select statement on the account table is high. The select statements where clause is just pulling from acct_num field. This field is the primary key for the table. So I went to the table to look at any indexes on the table. There on 10 indexes on this table. Here are the listings:
1. Pk_account: acct_num(ASC), Is Unique, Primary key and Not Clustered
2. Pk_account_11d4a34f: acct_num(asc), Is Unique, Index, Clustered
3. acct: acct_num(asc), addr(asc), Not unique, Index, Not Clustered
4. addr: addr(asc), Not Unique, Index, Not Clustered
5. nalnreact: name_line1(asc), acct_num(asc), real_prop_ind(asc), Not Unique, index, Not Clustered
6. ln1: lname1(asc),fname1(asc), Not Unique, Index, Not Clustered
7. ln2: lname2(asc),fname2(asc), Not Unique, Index, Not Clustered
8. ph1: phone(asc), Not unique, Index, Not Clustered
9. ss1: ssn1(asc),ssn2(asc), Not Unique, Index, Not Clustered
10. ss2: ssn2(asc),ssn1(asc), Not Unique, Index, Not Clustered

Don't know why all these indexes where created, I can only assume they were creating queries based on these fields. Can anyone gave me some direction on where to start fixing this. Should I be creating some type of covered index.

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2011 Posts

Posted - 06/06/2013 :  01:54:35  Show Profile  Visit jackv's Homepage  Reply with Quote
In 2005 and beyond there are dmvs where you can see unused indexes - read here for techniques http://www.sqlserver-dba.com/2012/06/sql-unused-indexes.html

In sql server 2000 it's a bit more cumbersome, but still possible to derive the information. You could collect the execution plans of the queries - analyse the plans and check which indexes are used. Disable the unused ones for awhile - and then delete them once you're satisfied not performace degradation

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 06/06/2013 :  10:46:23  Show Profile  Visit russell's Homepage  Reply with Quote
quote:
Originally posted by sbrazier

I know we are behind in SQL. Unfortunately we are still using SQL 2000. I was using sql profile and notice that my number of reads for a simple select statement on the account table is high. The select statements where clause is just pulling from acct_num field. This field is the primary key for the table.


Look at the execution plan. Might rebuild the index (know that rebuilding the clustered index causes all of the indexes to be rebuilt). Also might need to update statistics.

Edited by - russell on 06/06/2013 10:47:09
Go to Top of Page

sbrazier
Starting Member

19 Posts

Posted - 06/06/2013 :  11:02:47  Show Profile  Reply with Quote
Thanks for the replies. I will try the suggestions.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000