April 23, 2008 at 2:44 pm
we have a server that is slowing way down after running for a few hours. a query that would normally take 1 second is taking over a minute to complete. we have checked all the logs and found nothing. when we restart the sql service, it comes back up and performs fine for a while, but then slows again. It seems to affect one user database more than others. we have also tried clearing the procedure cache and that didn't help (thought it may have been reusing a bad plan). Any ideas?
I'm also wondering what about cycling the services is casing the problem to go away temporarily. we have cleared all the connections manually and cleared the cache manually. what else does sql do when it shuts down and starts back up?
April 23, 2008 at 3:23 pm
My first suggestion would be to see how your tempdb is sized? If the user is doing something that uses a lot of tempdb space you could be waiting on IO as tempdb is autogrowing.
If you tempdb is properly sized for your environment I'd then check to see if any of my other databases are autogrowing.
If not, post back with what the user is doing.
David
April 24, 2008 at 1:39 am
Use Windows Performance to set counters on diskspace/access and memory usage. Also set counters to monitor SQL buffer cache and re-compilations. These counters can show you the direction to look for if values change over time.
April 24, 2008 at 6:25 am
I'd check to see if you've got open connections and possibly uncommitted transactions. For it to just degrade linearly over time means that resources are being consumed and not released. Also look for blocking processes. You could have a single badly behaving query shutting down the rest of the system. The quick & easy way to check is just to open the Activity Monitor. You can see blocked processes and waiting processes from right there.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 24, 2008 at 7:28 am
there is no long running transactions or blocking going on. TempDB is failry small (under 1 GB). I am unable to find the counters for sql server in perfmon. Not sure if it's because it is a cluster, but the only sql counters in perfmon is ssis. paging is normal. CPU does go up a bit while this occurs ( from around 10% normally, to about 50% when the problem occurs) this is the only performance metric that seems to change.
April 24, 2008 at 8:42 am
< 1 gb is a small tempdb unless this is small non-oltp system. Since it is a cluster I doubt that is the case. You can read about sizing tempdb in BOL but at a minimum I would increase my tempdb to 4096 Mb on the data file and 1024 on the log. If you have the disk space go 8192 and 2048.
I like to cap my tempdb to prevent runaway processes from filing the disk and set the growth increment to be a specific megabtye size. In my environment we use 32mb.
Growing the tempdb will strain your disk system for a few minutes so do it during a low usage period.
David
April 24, 2008 at 8:50 am
Hmmm, no performance counters is an issue, although probably not a symptom of the performance problem. That makes it more difficult to know what's going on. Have you run Profiler? Maybe you've just got a large scale, ever increasing load?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 24, 2008 at 8:56 am
yeah, we have run profiler, and didn't find an increase in load. We even have a case open w/ microsoft since friday and they have not come up with anything after collecting a pssdiag.exe output. we seem to be running out of stuff to look at, so any more suggestions are greatly appreciated, as well as the suggestions that have already been made.
Thanks all
April 24, 2008 at 9:00 am
It sounds like you've covered everything from a DBA standpoint. It's starting to sound like some type of hardware issue, drivers, the OS, something along those lines. Sorry this hasn't been more helpful.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 24, 2008 at 9:09 am
well, I think we have eliminated hardware as the issue. Over the weekend, we rebuilt the cluster on new hardware and even a new location on the SAN for the disks. It seems to be something internal with SQL, but looking in the the normal places isn't finding anything. We are all highly frustrated with the situation as it impacts out business users a few times a day. I really appreciate everyones suggestions. BTW - we also increased the size of tempDB as suggested earlier. the problem hasn't occured since, but it could just be a matter of time before it shows up agian.
April 24, 2008 at 9:33 am
Stupid question, I realize, but being the stupid guy in the stack, it's my job.
What service pack are you currently running?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 24, 2008 at 9:37 am
well, the problem first happened last thursday, and we were on sp1. we rebuilt the cluster over the weekend and upgraded to sp2, and that didn't fix the issue. microsoft just recomended we go to cu7, but I think they are just trying to buy some more time, as they cannot point us to what in cu7 will fix our problem.
April 24, 2008 at 9:40 am
To see the counters you have to be admin on the server I'm pretty sure so that might be the issue.
Are tracing the Sql? Since you can isolate the problem to a particular user that should give you a lot of info.
April 24, 2008 at 9:41 am
That's a pretty common PSS tactic. Until you're running the set of patches that they want to test against, they generally won't support you. Oh well, I've exhausted my feeble set of guesses. When you find out what it is, please post the solution.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 24, 2008 at 10:05 am
the problem is not isolated to a certian user. The performance counters are truely missing (microsoft confirmed and they are working on a way to get them installed). I'll definitely post the solution when we come up with one.
we may be narrowing the problem to a table that gets about 6000 rows inserted into it just before the problem occurs. Since it's a 7mil+ row table, I wouldn't expect a 6000 row insert to affect it like this. we added a sp_recompile to be run on the table after we insert it, and the problem hasn't occured yet today, but again, may just be a matter of time. we have tried reindexing and updating stats as well, with no luck.
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply