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.
| 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 ClusteredDon'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.htmlIn 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 degradationJack 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. |
|
|
|
|
|
|
|
|
Indexes