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 |
|
WaterWolf
Starting Member
24 Posts |
 Posted - 2008-11-26 : 11:21:28
|
| Hello,I have a sql server 2005 database with a table and a view in it. Executing a certain sql statement (select Cost, ResultID from vTheView WHERE ResultID = 1245) against the view takes one and a half minutes to get a result. If I rebuild the indexes on the table the statement takes less than a second. If I insert more data into the table the statement takes one and a half minutes to select the new data but less than a second to select the older data. I don't really understand how an index is supposed to react to data being inserted, surely it shouldn't need to be rebuilt everytime ? The issue doesn't seem to occur with any other table or view in the database.To add confusion to the matter, I have a database with an identical schema on a different sql server and it does not suffer from this problem. It has 2 million records in the table and works fine, the other database has only 40,000 records and takes one a half minutes to run the statement. Does anyone know what could be causing this ? |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-11-26 : 11:30:36
|
| Big insert or update statements could fragment your data resulting in slower reads and performance. Reindexing clustered index will reorganize the data and update statistics as well resulting in better performance. Probably, thats what is happening in your case. |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-26 : 11:39:45
|
| High Inserts/Delete/Update causes internal(page is not sufficient to hold data causing page splits)and external (Unorder in pages resulting in more I/O to search data) fragmentation. If table is subject to High DML operation, put your fill-factor low . |
|
|
|
|
|
|
Why do I have to constantly rebuild indexes ?