sudden slow down
-
Recently we've been experiencing sudden slow down in SQL server performance. The strange thing is that it happens in the intervals of 5 min. 5 minutes of real slowness and then it's fine for another 5, and again super slow. Reboot fixed the problem, but I wonder for how long....
Ran the traces, but they don't indicate any bottle necks. System monitor didn't report any high disk activity. I checked index fragmentation, statistics, query plans and they all look good. This is why it is so strange. This is the second time in a week we had this problem. I also checked running services and CPU usage, again this is normal.
As for the hardware this happens on Dell (2950) with SAS drives and Perc5/ controller. Unfortunatelly this is running on RAID 5. This is SQL Server 2005 with SP1.
Anyone experiencing similar symptoms? The strange thing is that it's been running fine since we bought it, and suddenly it decided to slow down.
-
A few more ideas on stuff to check..
Is anything strange happing in the output of sp_who2 during the slow down? Is there anything/body who is always there during a slow down who is not when its quicker?
Have you got antivirus software on the machine which could be causing problems?
How about network IO?
Is the slow down exactly every 10 mins for 5 mins or is it roughly that?
Are there any long running queries which correlate with this?
Whats the systems memory usage like? whats SQL Servers VM size? How much of that is paged?
- James
--
James Moore
Red Gate Software Ltd -
Any scheduled jobs that corresond to the slowdowns? Either on SQL or windows scheduled events?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I've had the same sort of random slowness issues with SQL Server 2000. An insert into a table will sometimes cause it to go out to lunch for like 20 minutes for no apparent reason.
And our DB is on a fast SAN.....So I'm looking into Oracle 10.
-
Sp_who2 does not indicate anything. We do see normal connections from the web server. We did think of the anti-virus so we shut it off for the time being, but it is not cause of the problem. The slowness occurs for 5 minutes every 10 minutes....It could be 5 minutes and 20 sec... or 4 minutes and 50 sec...So it varies a little...basically +/- 30 seconds.
It typically happens when they run a search job. I checked the queries which get executed but I don't see anything wrong. The plan does not inidcate any table scans or other negative performance objects. The strange thing is that it was working fine for so long, and now it just stinks.
It is a big database connected to the web application. The db size is around 200GB.
I checked the memory usage from SQL server, it uses maximum what it can and should. As for the VM.... well I focused so much on queries I forgot about this one. I'll check again.
I found an interesting article here: http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=19594
I wonder if it is the VM problem....
-
Have you considered running profiler to capture an activity trace for 15 minutes or so to see if there is activity that corrolates with the slowdown?
-Dan B
-
Yes, we did run profiler. It shows the usual stuff that we see on a normal day too.
-
If mem usage is high then I would check page file usage during these slow downs - page reads - writes etc and see if this is higher than when its running normally.
- James
--
James Moore
Red Gate Software Ltd -
Just for giggles, monitor the size of the databases, especially TempDB, when this happens. I'd do a DB Size "profile" right after a reboot to use as a baseline.
The other thing I'd check is fans... if a fan failed in the blade (or whatever type of box it is) and the CPUs get too hot, it'll sometime act like this. And, no, I don't trust heat sensors to always report what's going on.
We also had a similar problem caused by a coffee pot... yep, you heard me... a coffee pot. They plugged a coffee pot into the same outlet as the "hub" for the server which bypassed the "power conditioning"... everytime the coffee pot cycled to keep the coffee warm, it made an artificial "data storm" on the network.
Lastly, what makes anyone think Oracle 10G is the answer to this type of problem? Oracle forces RBAR on sterioids... for example, the UPDATE statement has no FROM clause which forces you to use correlated subqueries when joining to another table for the update (yeah, you can use the relatively new MERGE statement to get around that), triggers have no INSERTED/DELETED tables which force RBAR "for each row", and there are all sorts of limits in Oracle not present in SQL Server... for example, object and column names are STILL limited to only 30 characters and triggers are STILL limited to only 32K. Why would you need more than 32K in a trigger? Because there's no equivelent to COLUMNS_UPDATED so you have to check each column individually for Audit triggers.
Upset that SQL Server 2000's VARCHAR datatype only handles 8000 bytes? Then you'll just "love" Oracle... it only goes to 4000! And just wait 'till you find out that all Oracle objects return as all uppercase (forget any chance at camel casing... love those (#$($(#%! underscores!) and that all strings are case sensitive. And, sure, date formatting in Oracle is a breeze... but, date manipulation is a pain in the patooti. And, compare the GUI's to Query Analyzer and Enterprise Manager... SQL Server is so much easier. What about TOAD? Lot's of folks that don't really know how to use the features in QA or EM think it's the berries... I hate it.
Are there some neat tools in Oracle? You bet... but before you go sour on SQL Server, regardless of version, and start making recommendations to go to Oracle, you really, really need to check out what a major paradigm shift that would be and how much the cost of ownership for Oracle would truly be.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
If I were you I would do a update statistics with fullscan on all tables in order to escape of this problem. I would turn off auto update statistics, too.
-
Turn off auto update of statistics? Why? Most folks consider that to be a form of "death by SQL"...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I did update statistics right after it happened. Also checked index fragmentation and defrag what ever was necessary. But it still happens.
The auto update statistics is off.
-
What did you find out about memory? Do you have an app that someone recently made a change to that has memory leaks?
Also, since it occurs so regularly, have you examined all internally and externally scheduled jobs?
The bottom line is something changed... it might even be just the amount of data that a given poorly written proc or view is using and it drove it over the "tipping point". Maybe there's a proc that uses an "illegal" form of UPDATE that is notorious for working fine up to a point and then WHAM!, it starts pegging CPUs.
That "illegal" form of update looks like this (it's insideous, difficult to find, works fine until certain conditions in the data/indexes are met, then BOOM!)...
UPDATE targettable
FROM othertable
WHERE targettable.somecolumn = othertable.somecolumn
Notice that the illegal form of UPDATE has a join but no mention of the targettable in the FROM clause.
The "legal" forms of an UPDATE to do the same thing are (although the second one is NOT listed in Books OnLine and could change with next service pack)...
UPDATE targettable
FROM targettable tt,
othertable ot
WHERE tt.somecolumn = ot.somecolumn
UPDATE tt
FROM targettable tt,
othertable ot
WHERE tt.somecolumn = ot.somecolumn
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
What command did you use to defrag your indexes? Have you considered rebuilding them?
John
-
dbcc dbreindex was used to rebuild the indexes.
Viewing 15 posts - 1 through 15 (of 46 total)
You must be logged in to reply to this topic. Login to reply