Indexes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Indexes

Author

Topic

sbrazier
Starting Member

19 Posts

Posted - 2013-06-04 : 11:09:12

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
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-06-06 : 01:54:35

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

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-06-06 : 10:46:23

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.

sbrazier
Starting Member

19 Posts

Posted - 2013-06-06 : 11:02:47

Thanks for the replies. I will try the suggestions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources