Daily maintenance plan causing performance problems

Question

Daily maintenance plan causing performance problems

webrunner

SSC-Dedicated

Points: 31817
More actions
September 24, 2009 at 7:59 am

#211432

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

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

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/

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 2

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

webrunner SSC-Dedicated Points: 31817 More actions · Answer 3

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 4

You could try Isolation level (Row level version[/url]). I am not sure if this will help or not because I have never tried running it with maintenance plan of Statistics Update

-Roy

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 5

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/

webrunner SSC-Dedicated Points: 31817 More actions · Answer 6

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 7

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 8

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/

Performance Guard (Shehap) Ten Centuries Points: 1297 More actions · Answer 9

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]

Vernon Jimmerson-306463 Mr or Mrs. 500 Points: 547 More actions · Answer 10

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.

Jeff Moden SSC Guru Points: 1004151 More actions · Answer 11