November 18, 2008 at 2:15 am
Hi
FIrstly I am sorry if I have placed this question in the wrong area, I have a slow database so thought this might be under this performace area?
I am not sure what has happened but since this weekend our companies main SQL DB has become massivley slow. On average the same stored P's that used to take no longer then a second to come but with data are now taking up to 46000 MS to bring back data.
I have run the SP's directly in QA and they return very quickly, also I have been using Perfmon to look at memory problems or disc IO and cant seem to see anything there. I am still quite fresh to the DBA roll so forgive me if I dont have missed out some vital data.
Also the same SP called from a remote QA on my distrubution server (we use replication:transactional) does bring back the data very quickly as well.
I rebooted the main SQL box last night and when i logged back in a encountered a pop for windows DEP tring to block SNMP, I hav never seen this beofre could this have an effect.
Looking at the SELECT * FROM ::fn_virtualfilestats (dbid,-1)
The main DB IoStallMS = 5134827
Is this high or is that a silly question? I am not sure how best to find the problem??
Any help would be a problem!
Also if you are a admin on this site I am sorry if I have put this in the wrong place.
Regards
November 19, 2008 at 11:36 am
1) there are a kajillion configuration changes that could have happend to cause this slowness. Good luck finding out if one or more occurred and which one may be causing the problem
2) I would update all statistics and perhaps do index maintenance if not done regularly. You can hit certain data sizes (or load lots of data you weren't aware of) and this can cause the optimizer to be using suboptimal 'older' query plans.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply