September 24, 2009 at 7:59 am
We have recently started seeing our daily maintenance plan causing performance problems. Specifically, there seems to be an increase in blocking when this job runs. The job usually runs in 4-6 minutes, but when the problem happens it gets hung for hours - until someone kills it or the web servers are rebooted.
The maintenance plan has these steps:
Update Statistics
Reorganize Index
Cleanup History
Are there any "usual suspects" I can start with to diagnose and resolve this problem?
Any help would be greatly appreciated.
Thanks,
webrunnner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
September 24, 2009 at 8:31 am
Web server has nothing to do with the maintenance jobs. Since rebooting the web server “releases” the maintenance jobs, I suspect that you have a blocking chain that the job is caught in the middle of the chain. Did you check if the maintenance job is doing something? Did you check if it is being blocked by another process?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 24, 2009 at 8:38 am
When you are doing Update Stats and doing Index maintenance, it does a schema lock. That cause Blocking. That could be why you see that everything is hanging.
-Roy
September 24, 2009 at 8:51 am
Thanks for your replies.
Based on your information and on what we know so far, it does seem like the rebooting of the web servers may have released locks that were in turn keeping the maintenance plan from completing. In other words, it looks like the causation was [something] blocking [maintenance plan], because if it were the other way around ([maintenance plan] blocking [something]), the maintenance plan would have finished in its normal time. Is that reasoning correct?
It is good to know about the schema lock, too. It seems like we will need to simply make the web site unavailable to users during the maintenance job. Is that what is usually done? Does anyone have advice along those lines to keep other processes from interfering with the maintenance?
Many thanks again for any help.
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
September 24, 2009 at 8:57 am
September 24, 2009 at 9:04 am
I’m not sure that the schema locks cause blocking. As far as I know schema locks won’t let you modify the table’s structure but it doesn’t block DML statements such as select, insert, update and delete.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 24, 2009 at 9:16 am
Adi Cohn-120898 (9/24/2009)
I’m not sure that the schema locks cause blocking. As far as I know schema locks won’t let you modify the table’s structure but it doesn’t block DML statements such as select, insert, update and delete.Adi
Thanks - also, if the maintenance plan is being blocked, its schema locking wouldn't be the culprit, would it? I just want to make sure I understand.
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
September 24, 2009 at 9:24 am
Adi Cohn-120898 (9/24/2009)
I’m not sure that the schema locks cause blocking. As far as I know schema locks won’t let you modify the table’s structure but it doesn’t block DML statements such as select, insert, update and delete.Adi
It depends on the type of Schema lock.
If it is SCH-S, then it will let you access the table data. But if it is SCH-M, it will not.
-Roy
September 24, 2009 at 9:39 am
This is taken from a Microsoft article about SQL Server 2000 http://support.microsoft.com/kb/195565%5B/url%5D. I couldn’t find an official article about SQL Server 2005.
Sch-M-UPD-STATS: Schema Modification Lock
-----------------------------------------
This is a non-blocking lock that is used by the system to ensure that
only one automatic UPDATE STATISTICS process is run against a table at
any given point in time. The sp_lock stored procedure will report this
lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 25, 2009 at 2:15 pm
I think it is much recommended to do the following:
1-Run profiler and check all locks with their wait times and also deadlocks all over the day until you clarify well the reasons of Hung.
2-Also I recommend heavily to generate the correct statistics and indexes using Performance tuning plan to be good for performance while updating them .In other words , if the exist statistics and indexes are suitable for performance of web application transactions , then updating them will raise performance heavily and if not may affect some web transactions.
3-Check scheduling of the job of Reorganize especcially outside working days or least working time as
long or complex select query will cause share lock with this job causing this job especially hung
[font="Arial Black"]Performance Guard[/font]
[font="Courier New"]Just ask me to get 0 sec for all queries[/font]
July 9, 2024 at 1:51 pm
Never do reorganize. On large tables it will take forever and on small tables why reorganize when you can just rebuild. Treat all your server maintenance like a man made lake filling up fast with no overflow. Be as PROACTIVE as possible. Log files every 5 minutes, all databases tuned using DTA wizard. All missing indexes created so the complier asks for none! You will probably need three or four iterations of DTA wizard. Run it and apply indexes until it doesn't ask for any more. The indexes also need to be defragmented daily. Its all been given to you on a platter by the Microsoft software engineers. Use what they created! I've seen whole DBA groups hold organizations hostage for decades to their own incompetence, all because they don't follow through on proper maintenance as described above. Sad since you could almost have a button pushing monkey do it if you could get them to follow through.
July 10, 2024 at 5:53 am
Never do reorganize. On large tables it will take forever and on small tables why reorganize when you can just rebuild. Treat all your server maintenance like a man made lake filling up fast with no overflow. Be as PROACTIVE as possible. Log files every 5 minutes, all databases tuned using DTA wizard. All missing indexes created so the complier asks for none! You will probably need three or four iterations of DTA wizard. Run it and apply indexes until it doesn't ask for any more. The indexes also need to be defragmented daily. Its all been given to you on a platter by the Microsoft software engineers. Use what they created! I've seen whole DBA groups hold organizations hostage for decades to their own incompetence, all because they don't follow through on proper maintenance as described above. Sad since you could almost have a button pushing monkey do it if you could get them to follow through.
Man... be really careful... There's some really bad recommendations in the above...
all databases tuned using DTA wizard. All missing indexes created so the complier asks for none!
Good lord NO!!! You're just creating more sources of page splits, slowing down ALL INSERTs and DELETEs and a good number of updates and not all of the index recommendations are actually going to work correctly. At the very least, they we substantially increase the amount of required storage and backup/restore times and space. DTA can provide some good hints but it generally is ineffective and, like I said, the indexes that "clippy" builds are horrible.
The indexes also need to be defragmented daily.
So... you have the coded demonstrable proof that fragmentation always causes substantial performance issues that you also have the same type of proof that clearly demonstrates that defragmentation fixes it all? If you say "Yes", post it so I can prove why you're wrong. If you say "No", then you need to post a retraction for posting such bad information.
I've seen whole DBA groups hold organizations hostage for decades to their own incompetence, all because they don't follow through on proper maintenance as described above.
Speaking of incompetence, you do realize that MS changed all of that back on 20 Apr 2020, correct? And, you also realize that the guy who first came up with "some recommendations to keep MS happy" basically published a retraction in 2008, correct?
And you DO realize that REBUILDING an index at 0/100% for anything that's fragmenting WILL cause massive page splits on the "morning after" AND you do realize that if you're basing your index maintenance only on logical fragmentation that you're actually causing more damage than you fixing, correct?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 7, 2024 at 11:35 pm
I thought this must be from years ago but only a few months. Maybe just the reply. I've had the same discussions, or more like yelling matches with DBA groups in the past, and they just refuse to be competent. Can I assume you have used the top two options in the SSMS tools menu and had bad results? You do know that most people use a DEV environment to test? You do know there are system DMVs that will show you what is being used and what isn't and there is something referred to as a drop statement? Frankly you just sound like one of the DBAs making life miserable for me as a developer with more excuses like it's the disk sub-system when in reality they tried to use AlwaysOn(TheFritz) when they didn't have the resources to do so. Nothing will change what I have laid out. The developer complains about unusually slow performance, the DBA defragments the database, the developer is happy. Thats how it works as a competent DBA! Frankly I don't care what the DB does in the background as long as it performs. I started out low level programming! I don't need all that complexity now at the end of my career.
September 11, 2024 at 3:20 am
I thought this must be from years ago but only a few months. Maybe just the reply. I've had the same discussions, or more like yelling matches with DBA groups in the past, and they just refuse to be competent. Can I assume you have used the top two options in the SSMS tools menu and had bad results? You do know that most people use a DEV environment to test? You do know there are system DMVs that will show you what is being used and what isn't and there is something referred to as a drop statement? Frankly you just sound like one of the DBAs making life miserable for me as a developer with more excuses like it's the disk sub-system when in reality they tried to use AlwaysOn(TheFritz) when they didn't have the resources to do so. Nothing will change what I have laid out. The developer complains about unusually slow performance, the DBA defragments the database, the developer is happy. Thats how it works as a competent DBA! Frankly I don't care what the DB does in the background as long as it performs. I started out low level programming! I don't need all that complexity now at the end of my career.
I used to be a front-end Developer... that's why I don't rebuild indexes all the time 😉 ... The page splits that came after the rebuilds would slow down the code too much. Reducing the Fill Factor didn't help in most of the cases because INSERTs on clustered indexes with "ever increasing"keys go in at 100% and then the Updates the people did right after that would cause massive page splits. Adding DTA indexes that didn't actually get used by the code or caused faster indexes to not be used also suffered from massive page splits.
Getting back to what you said before...
The indexes also need to be defragmented daily.
I'll ask the same question I asked you before but in a slightly different way than I did before...
Do you have coded proof that REBUILDing indexes causes substantial enough improvements in performance beyond what simply rebuilding the statistics will do to actually make the REBUILDs worthwhile?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply