May 11, 2015 at 6:58 am
I have used olahalegrean scripts for database maintenance, we have large table of size ~170GB it used to take 4 hours on average to complete over the weekend.
options used
online indexing
SORT_IN_TEMPDB = ON
fragmentation 1.2%
Tempdb configuration is 1 single data file of 40GB I know its bad
when I looked at the session details it is waiting on PAGEIOLATCH_SH, yesterday it ran 8 hours and completed only 45% I have to cancel it because nightly process will slow down.
I am thinking the problem would be due to tempdb but why it happened all of sudden?
as per my knowledge tempdb would need 170gb for index operation is it right?
May 11, 2015 at 2:07 pm
any idea from anyone pls
May 13, 2015 at 6:19 am
Its all covered here in msdn:
https://msdn.microsoft.com/en-us/library/ms188281.aspx
MCITP SQL 2005, MCSA SQL 2012
May 17, 2015 at 10:24 pm
It's not possible to say why it suddenly started taking longer. It could be that someone did a major dataload or update. It could be that someone else was running a job at that time. It could be that the job finally started running just long enough to "bleed over" into another job's space and caused a bit of contention. It could be someone had some blocking going on or ran a nasty ad hoc query. Other than giving you such suggestions as to what to look for, we can't help much from here.
If you can't find it in the Event or SQL Server logs, it might continue to be a mystery. Is it still occurring?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 18, 2015 at 6:37 am
there is no data load or updates, index fragmentation is only 1.2%. I have setup blocking alerts every 5 minutes did not receive any I myself logged in few times to see what is the wait type the session is waiting on PAGEIOLATCH_SH.
yesterday it completed in 14hours which is also not good either since user activity of this application is low no one complaints. But I am trying to figure out where could be contention.
May 18, 2015 at 10:02 am
What parameters are you passing to the scripts?
At only 1.2% reported fragmentation, the index shouldn't even be getting touched with default settings.
Also, just to clarify, is all the time being spent on one index (since you only gave one fragmentation number)? If so, is it rebuilding or reorganizing the index?
Actually, while I'm clarifying things, are the times you've given just for reindexing, not for reindexing and CHECKDB?
Cheers!
May 18, 2015 at 10:16 am
Daily maintenance rebuild if the fragmentation level > 30%, between 20 and 30% reorg
updates stats with default algorithm.
weekend schedule is if the fragmentation level between 1 and 2% reorg(in this case it reorg)
>2% rebuild
updates stats with full scan
weekend doesn't have any threshold's I want to do index maintenance
Yes the pk index is taking that long that's why I am trying to figure out what going on. the index in question is excluded from daily maintenance. Job will do perform only index optimization no DBCC checks, job is separate only for this table.
client don't have maintenance until I set up. first week it ran fine, slowness noticed on second and third runs(yesterday - it take 22 hours to complete all indexes and stats)
June 1, 2015 at 6:49 am
Rebuild process is better when compared to reorg
below are the runtimes of a non clustered index of row count 1114462
rebuild 7 mins with maxdop 2 and sort in tempdb on
reorg 15 mins with maxdop 2 and sort in tempdb on
right now I am checking for fragmentation, if it is between 1 to 2% reorg else rebuild.
I am going to change process to Rebuild indexes and update stats that are not related to Index without checking fragmentation, this way we save time on checking fragmentation which may be hour or more to verify all indexes
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply