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 |
|
creane
Starting Member
11 Posts |
 Posted - 2003-09-11 : 12:38:12
|
| Hi, Im a developer using sql server 2000 as our database.. We run a query regularly select * from aTable where item < 8 and noitems=0there is an index on the item column, the table is over 1 million records. The query analyser disregards the index when running the query - it performs a clustered index scan, Ive used an index hint but this was even more detramental performance wise, ive looked at the index fragmentation...There was quite a lot of index defragmentation on the table, scan density 13% which went up to 97% when I defragmented the indexes.. I presume which will be an improvement, yet didnt use the index still, was better over an index hint. My question is will the indexdefrag help with the clustered index scan, or is it better to rethink the indexes on these columns? Any help greatly appreciated, as Im relatively inexperienced in this area.ThxEric |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-11 : 12:41:46
|
| Defragging the indexes needs to occur on a regular basis. This can be done with DBCC INDEXDEFRAG or DBCC DBREINDEX. Statistics also need to be updated regularly: UPDATE STATISTICS. These are part of normal database maintenance. Do you have a DBA there that has these scheduled?What do the indexes look like on your table? Why are you performing SELECT *? You should always use an explicit column list: SELECT Column1, Column2, etc...Tara |
 |
|
|
creane
Starting Member
11 Posts |
Posted - 2003-09-15 : 07:11:59
|
| Thanks for the reply, Ive changed the query to just include the columns required..I think the indexing will have to be reconsidered as the data distribution etc indicates that the index is not being used....thx againEric |
|
|
|
|
|
|
|
|
indexdefrag an index scans