May 1, 2012 at 5:24 am
Guys,
I have a table with 48M rows in it which I had to copy from one database to another (same instance). to improve performance of the copy I disabled the indexes on the table (non clustered, one Unique, one not)
I have run the Alter Index ()... Rebuild .... Script and activity monitor is showing the reindex as being blocked by another process (activity shows as DBCC) which my DBA says is running a fragmentation query.
This seems to be seriously degrading the performance of MSSM (2005); The original copy appears to have stopped responding (even though it doesn't say it in the title bar) - the query timer has stopped and I cannot open any tree nodes in the database explorer window.
I have also started a new instance of MSSM and am getting query timeouts when trying to open the Tables node of the Database Explorer tree
I can't find any details on-line about a rebuild causing a fragmentation query. Would I do better kill the fragmentation and rebuild processes and then drop and re-create the index rather than try to rebuild it?
May 1, 2012 at 6:33 am
You disabled indexes at the same time as the index defrag program was running and you started the copy anyway? All at the same time? Yeah, you're blocking on different resources all over the place. I'm not even sure what I'd kill first, but it sounds like you need to kill processes. I think, given the situation, I'd start with the index defrag, but I'm not sure. Your explanation is a little confusing.
"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
May 1, 2012 at 6:41 am
aaron.reese (5/1/2012)
Guys,I have a table with 48M rows in it which I had to copy from one database to another (same instance). to improve performance of the copy I disabled the indexes on the table (non clustered, one Unique, one not)
I have run the Alter Index ()... Rebuild .... Script and activity monitor is showing the reindex as being blocked by another process (activity shows as DBCC) which my DBA says is running a fragmentation query.
This seems to be seriously degrading the performance of MSSM (2005); The original copy appears to have stopped responding (even though it doesn't say it in the title bar) - the query timer has stopped and I cannot open any tree nodes in the database explorer window.
I have also started a new instance of MSSM and am getting query timeouts when trying to open the Tables node of the Database Explorer tree
I can't find any details on-line about a rebuild causing a fragmentation query. Would I do better kill the fragmentation and rebuild processes and then drop and re-create the index rather than try to rebuild it?
If you had Activity Monitor running, it's likely that the low resource popup is hidden. Use ALT-Tab to cycle through your apps to reach SSMS again - the popup will be on top.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 2, 2012 at 5:17 am
aaron.reese (5/1/2012)
I have run the Alter Index ()... Rebuild .... Script and activity monitor is showing the reindex as being blocked by another process (activity shows as DBCC) which my DBA says is running a fragmentation query.
Grant , i have question for you. when the ALTEr was under process at that time defragment process(by other DBA) doesnt work with other copy of index ? as we know that ONLINE option will help on these type of scenario or is it only helpful for DML queries or SELECT ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 2, 2012 at 5:54 am
Bhuvnesh (5/2/2012)
aaron.reese (5/1/2012)
I have run the Alter Index ()... Rebuild .... Script and activity monitor is showing the reindex as being blocked by another process (activity shows as DBCC) which my DBA says is running a fragmentation query.Grant , i have question for you. when the ALTEr was under process at that time defragment process(by other DBA) doesnt work with other copy of index ? as we know that ONLINE option will help on these type of scenario or is it only helpful for DML queries or SELECT ?
No, I'm pretty sure you're only going to get benefits for the ONLINE option for things that are reading the index, not stuff trying to alter the index. I wouldn't run both these things at the same time with any expectation that it's somehow not going to interfere with each other.
"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
May 2, 2012 at 7:01 am
When an index is being rebuilt online, it does not take any1 locks. That means other processes can read the indexes and other processes can run inserts, updates and deletes while the index is being rebuilt and they are not blocked.
1) Well, it takes locks, but they are short-lived ones.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 2, 2012 at 7:36 am
GilaMonster (5/2/2012)
When an index is being rebuilt online, it does not take any1 locks. That means other processes can read the indexes and other processes can run inserts, updates and deletes while the index is being rebuilt and they are not blocked.1) Well, it takes locks, but they are short-lived ones.
But, trying to alter the index itself at the same time, that isn't happening.
"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
May 2, 2012 at 7:39 am
Grant Fritchey (5/2/2012)
GilaMonster (5/2/2012)
When an index is being rebuilt online, it does not take any1 locks. That means other processes can read the indexes and other processes can run inserts, updates and deletes while the index is being rebuilt and they are not blocked.1) Well, it takes locks, but they are short-lived ones.
But, trying to alter the index itself at the same time, that isn't happening.
Oh no, absolutely not. That will block.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 2, 2012 at 7:41 am
Grant Fritchey (5/1/2012)
You disabled indexes at the same time as the index defrag program was running and you started the copy anyway? All at the same time? Yeah, you're blocking on different resources all over the place. I'm not even sure what I'd kill first, but it sounds like you need to kill processes. I think, given the situation, I'd start with the index defrag, but I'm not sure. Your explanation is a little confusing.
This post seems to have gone off-topic slightly.
In response to Grant:
I disabled indexes BEFORE starting the copy then tried to rebuild them after the copy was completed. I think the fragmentation check may have been a red-herring and the process IDs left over from a SSMS instance that had crashed.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply