October 7, 2009 at 2:12 pm
I ran a number of index rebuilds, and reorganizes (mostly online, a couple offline) last weekend, suddenly after this, we are getting timeouts for many of our standard stored procedures that ran before.
My question is if anyone can point me to documentation showing that the rebuilds could cause this for any reason. Perhaps docs that show the affect on the data and/or proc cache due to these operations?
Also, At one point I ran a dbcc indexdefrag, hoping it would complete quicker for a very large table. Would this behave differently in terms of cache?
Has anyone ever had this sort of experience? Where many indexes were rebuilt and queries ended up taking longer?
Any help or links are appreciated
October 7, 2009 at 2:49 pm
We need to know what the current status of the indexes are?
Maybe the rebuilds hung during the process therefore you could be worse off then when you started. post the results and we can help from there
SELECT
OBJECT_NAME(i.object_id) AS TableName,
i.name AS TableIndexName,
phystat.avg_fragmentation_in_percent,
avg_page_space_used_in_percent,
page_count,
*
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') phystat inner JOIN sys.indexes i
ON i.object_id = phystat.object_id
AND i.index_id = phystat.index_id
order by phystat.page_count desc
October 7, 2009 at 6:01 pm
All operations were end with success ? did you check standard counters like: page life expectency or buffer cache ratio? did you update statistics ? what about queries ? any thoughts?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply