November 23, 2007 at 2:28 am
Hi Experts
I hv run into a peculiar problem. I have stored procedure in which there is normal query where 4 tables are inner joined. 2 of these tables contain around 200,000 rows (nothing much i believe)
Since the indexes were fragmented i reindexed (DBCC DREINDEX) them. After this query started working properly but only for a short period of time. After this it became very slow. I reindexed again and query started working properly but only for around 2 mins. After that it becam slow again. I reindexed a couple of more times but again query would perform properly for some time and then become slow.
Any ideas ?
"Keep Trying"
November 23, 2007 at 3:01 am
It is probably worth comparing the query plans. This will give you an idea why the slow one is so slow (it may not use a particular index, ...)
Once you have a good idea why it is slower, you could use hints to use a particular index, ...
If this does not help, as a very last resort you may want to save the query plan that is fast, and force SQL Server to use this plan for this query.
How dynamic is your data? You mention that after reindexing the performance is good again. Do your index become very fragmented? Is the data updated frequently, new items added to it? ...
Regards,
Andras
November 23, 2007 at 3:25 am
Thanks Andras
will look into what you have said. data is not dynamic.
I have restored the production db and in this db data is not modified.
"Keep Trying"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply