February 5, 2011 at 2:43 pm
Database running on sql server 2005 sp3 32bit. Merge replication subscriber. 30gbytes. Simple recovery mode.
Weekly update statistics full scan job used to take 30 min to 1 hour. Two weeks ago it took 5 hours. Last weekend it ran for more than 29 hours before someone killed an unresponsive server and embarked on an unsuccessful 12 hour recovery...
I have disabled the update stats this weekend! Checkdb ran fine in 10 min BEFORE this job as did a complete index rebuild, taking 40min. Two other user jobs that started after the update stats never completed, presumably turning in to long transactions.
What can make update stats take so long? What can I look out for this weekend? Many thanks, Breadon.
February 6, 2011 at 4:51 am
Logged in this morning to find an unresponsive ssms minimised to task bar. Ran task manager. Noticed sqlwb.exe hogging all of one of the two CPUs.
Closed the minimised ssms process from the task bar, by right clicking and choosing close. CPU usage then returned to more usual levels. Was then able to run up ssms with no ill effects.
Not sure where this leaves me. Am having to log on to server to run ssms from home this weekend. Think someone else is too.
February 6, 2011 at 5:38 am
With sql2005 auto update statistics is supposed to be better than with previous versions.
That frequent rate of update statistics should no longer be needed, so your issue might be avoided for a fair bit.
When encountering such an issue you reported, can you check (b)locking going on at your instance ?
(maybe even use the dac session to figure it out.)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 6, 2011 at 6:26 am
May be you can consider updating stats only for the required tables. You don't need to update statistics for the entire database. So, you should know which tables cause the contention!
February 7, 2011 at 3:00 am
Thankyou for help and support, especially at the weekend. It was much appreciated.
We are now around 90% confident that this was due to running SSMS on the server and sqlwb.exe hogging one CPU plus a lot of memory. Issue is described in:
https://connect.microsoft.com/SQLServer/feedback/details/241997/sqlwb-exe-cpu-100
Because of company policy not to permit VPN tunnelling, persons working from home at the weekend routinely log on to the servers and run client tools, such as SSMS, on the server itself. This practise is apparently making our servers vulnerable to known problems with said client tools. Recently reducing the virtual CPUs from 4 to 2 probably makes the issue more apparent. Persons have been routinely rebooting this particular server as a 'preventative' measure...
Yes - I agree our update stats routine too vigorous. However, it would be good to re-enable it as it is next weekend to see how it runs. Weekend server resource is not normally an issue.
I have enabled, and publicised how to use, the DAC. In this case, the server would respond to remote client read only selects and even supported our main client app. The issues came when users wanted to update any data.
Incidentally, the server itself is a virtual server running Windows Server 2003, sp2, under HyperV. It was virtualised as a whole from a physical Dell PowerEdge 180 server. Would have been much better to rebuild the server under hyperV. Hindsight is a wonderful thing.
Thank you once again.
Breadon
February 7, 2011 at 12:20 pm
I pretty much don't allow Remote Desk Top and SSMS to be run on production servers unless I specifically want to work as a dedicated console on the server. I've seen servers brought to their knees supporting multiple Remote Desk Top sessions and SSMS.
I much prefer using tools like GoToMyPC or LogMeIn to grab control of my development machine where I have all the client tools and my dev environment all set up.
Todd Fifield
February 8, 2011 at 1:06 pm
if you make your update stats a sql agent job, no one will have to remote in and even if they did they certainly wouldn't (SHOULDN'T) leave SSMS running!
BTW, I HIGHLY recommend ola.hallengren.com for an amazing FREE suite of maintenance sprocs that will even create the jobs for you.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 14, 2011 at 4:48 am
Hi there
I re-enabled update stats and the two other problematic updates in SQL Agent last weekend. All were processed in their normal timeframes. (The update stats in 50 mins.)
Be aware that my colleagues would typically have logged in via remote desktop at weekends in order to deploy changes out of hours. Update stats was always (and is still) run by SQL Agent. The method used to support out of hours work is under review. It will change to prevent client tool issues such as this taking out servers.
Many thanks for all your help.
Breadon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply