Index and table optimizaion question

[Dashley]

I just inherited another DB (SQL 7). I've found a table with more indexes than columns with index sizes larger than the PK.

There are no unique items in the table. It looks like a data dumping ground. This data is updated, deleted and inserted, triggered off of and read consantly.

I'm really not not that confident with indexes etc.. though I am researching them and hope to be real soon. From what I've read so far I might as well get rid of all but the PK and even its retention is questionable.

Any advice would be appreciated.

Thanks

Dan

Table student_data col's:

CurItemID INT
UserID INT
Date smalldatetime 
Quantity decimal (9,2)

10,330,300 rows

PK_student_data                 299MB Non Clustered
IX_USERID_QTY			385MB Non Clustered 
IX_Userid_Qty			332MB Non Clustered 
IX_StudentData_Curitem_ID	282MB Non Clustered 
IX_Student_Data_Userid		297MB Non Clustered   
IX_Student_Data_Date		334MB Non Clustered

The most frequent Sql statment that hits (very frequently)the tabel is :

SELECT S.UserID, Quantity, Date, S.CurItemID, SeqNum 
FROM StudentData S 
INNER JOIN Curriculum C ON S.CurItemID = C.CurItemID 
Where userid = 1234 
and s.date between Thisdate and thatdate
ORDER BY c.SeqNum, s.Date'

 

[vongrunt]

Hm...

IX_USERID_QTY 385MB Non Clustered
IX_Userid_Qty 332MB Non Clustered

Dupe?

Index on Quantity column looks like waste of resources to me... it is probably designed only to speed up one or two particular queries (a la TOP 10 items per quantity or something).

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[tanman1975]

This may be a stupid question , but did you run the index tuning equivelant in SQL 7.0 on the code? I suppose after that you could dump the other indexes.

My guess is you could get away with two composite indexes.

 

[Dashley]

yup. gotta learn how to cut and paste HA


PK_student_data 299MB
IX_USERID_QTY 385MB
IX_Userid_Qty 332MB
IX_StudentData_Curitem_ID 282MB
IX_Student_Data_Userid 297MB
IX_Student_Data_Date 334MB

 

[Dashley]

yes. the tuning wizard didn't recommend anything.

 

[druer]

Dashley,

Ignoring all other factors in the system, if that query is by far the most frequently run query against the table you should definately have an index on UserId.

After that I'd have to know more about the actual data. If there are very few records per UserId, then just having the index on UserId would be fine, because it will need to do a bookmark lookup of the table to resolve the select fields anyway, so it would match the few on UserId and then filter out the Date values. However, if there could be hundreds of different values for the Date field, then I would probably go ahead and have the index on UserId, Date. That way the query could use the index to resolve all of the intelligence by using the index, and would only have to do a bookmark lookup of the rows that past the test, rather than looking them all up.

If the data really isn't constant at all, then I probably wouldn't have a clustered index because it would actually degrade the system more than not having a clustered index on that table. However, if the data is pretty constant from the UserId side, and there are simply updates occuring around the UserId like the quantity or additional dates being added, and that query is the most heavily run query I would likely create the Clustered Index on UserId. That way your most heavily hit query could be resolved quickly, and the data would be right there.

If you kept your data table or your clustered index on one physical drive (or drive array) and the indexes on another then that would offset the performance penalty of having so many indexes. Having an index on each field might seem overkill in terms of disk space, but space is in reality very, very cheap, compared to the performance impact of having to do a complete table scan of 10,000,000 rows if the index isn't there. As Vongrunt indicated it could be that some of the indexes are simply used for a query here and there to use. If you've got the statistics of what queries are triggered and how frequently you can determine how often the where clauses invoke each of the indexes. If quantity is only queried 1 time per day, bag that index. Suffer the 1 table scan instead of the penalty of having to maintain the index.

Just some thoughts,