July 8, 2016 at 7:28 am
Hello,
I have an issue and I don't even know how to tackle.
We are running SQL2008R2 SP3. Have the Ola Hallengren reindexing jobs, and it's getting stucked on rebuilding a index... it got stuck for 4 days once! Since it was the weekend it wasn't noted, but Tuesday it was causing blocking, dead locks etc.
The last entry on the error log is
Date and time: 2016-07-08 00:04:13
Command: ALTER INDEX [IND_SENDSS_I2] ON [database_name].[dbo].[BirthGeneral] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)
Comment: ObjectType: Table, IndexType: NonClustered, ImageText: N/A, NewLOB: No, FileStream: N/A, AllowPageLocks: Yes, PageCount: 30207, Fragmentation: 37.7859
but no errors, since it never errors out.
What I did.
After hours, I tried to Drop & Recreate the index, well.. that didn't work,
So I put the database in single_user mode and then rebuild the index using the same command above, and it did it fairly quick
However, two days later, the fragmentation levels are again at 37% and we are having the same issue.
What can I do to narrow down the issue?
BTW... I'm not the Developer or Apps support... so any suggestions I guess I'll have to go and ask them.
The index is
/****** Object: Index [IND_SENDSS_I2] Script Date: 07/08/2016 09:19:46 ******/
CREATE NONCLUSTERED INDEX [IND_SENDSS_I2] ON [dbo].[BirthGeneral]
(
[bSENDSS] ASC
)
INCLUDE ( [nChildsID],
[nRecordVersion]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
The database is being replicated. This is the publisher, push replication.
The database is 700 GB database.
The table is 10 million rows, 22 GB in size.
Thanks in advanced
Miguel
July 8, 2016 at 7:37 am
Miguel
Get sp_whoisactive and run it when you see the blocking. That'll tell you who or what is doing it, and you can ask or force them to disconnect.
John
July 8, 2016 at 7:42 am
John Mitchell-245523 (7/8/2016)
MiguelGet sp_whoisactive and run it when you see the blocking. That'll tell you who or what is doing it, and you can ask or force them to disconnect.
John
+1.
Since the index can be rebuilt without any issue when single user, that means the blocking might be the reason. Especially since online index rebuild requires schema lock towards the end some long running transactions on the table might be the culprit. Either you might need to find and fix those queries or find a different maintenance window.
July 8, 2016 at 10:40 am
you might want to explore Minion Reindex. It has a bunch of mechanisms for dealing with blocking that might make it work better for you than Ola's scripts.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 8, 2016 at 5:51 pm
MiguelSQL (7/8/2016)
However, two days later, the fragmentation levels are again at 37% and we are having the same issue.
What can I do to narrow down the issue?
The index is
CREATE NONCLUSTERED INDEX [IND_SENDSS_I2] ON [dbo].[BirthGeneral]
[...]
WITH (PAD_INDEX = OFF, [...], ONLINE = OFF, [...])
The database is being replicated. This is the publisher, push replication.
The table is 10 million rows, 22 GB in size.
1. If your index hits 37% fragmentation in 2 days, then fix your fillfactor to prevent the splits (ALTER INDEX ... REBUILD WITH(FILLFACTOR=n), where n is between 0 and 100 specifying the percentage of space on a page to fill during a rebuild or defrag - smaller nonzero numbers leave more room on the page, and reduce splitting). Page splits are expensive I/O, defragmenting them is also expensive I/O. Your attempts to improve performance through defragmentation could easily be hurting more than helping.
Aggressive defragmentation in this case means that you're deleting and re-writing large portions of your database every day. This results is larger-than-necessary Diff and Log backups (and even more I/O writes); applying the extra work during a database restore adds time to restoring. At 700GB, this database is approaching the size where limiting transaction log writes is necessary for uptime, as the active portion of the log must be scanned and processed when a database restarts (failover, crash, reboot, whatever).
You could also tune the queries a bit to reduce scans, greatly limiting the impact of fragmentation, then just let them frag up for a while.
2. How much is this index even used? If you're going to invest into storing and maintaining it, make sure you need it (use sys.dm_db_index_usage_stats to compare index usage).
3. 22GB ÷ 10 million rows = 2.2KB/row. Lots of indexes or wide rows?
4. Even though the rebuild runs with ONLINE = ON, it must still briefly grab an IX-lock on the table when it starts, and then requires an X-lock on the table to swap the old and new indexes, plus an Sch-S lock during the run.
5. Manually updating statistics (UPDATE STATISTICS, sp_updatestats, maintenance plan) on an index and defragmenting that index at the same time on different threads will block one of the calls and can bump the other up to an X-lock on the table. Note: rebuilding an index resamples statistics at 100%, so don't resample stuff you rebuild. Not the case with a defrag, though.
Bottom line: don't fix the rebuild. Fix the index.
-Eddie
Eddie Wuerch
MCM: SQL
July 10, 2016 at 8:27 am
Thank so much Eddie. That's the kind of guidance I was looking for. I'll take a look at the index this week.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply