Performance Goes Down - Help!
-
Hi,
We have a strange problem occuring in out SQL server for the past few days. the performance goes down rapidly when the user connection exceeds 200+ wherein our server is very much capable of handling more than 500 conections. All the applications referring the server throws timeout. The counters seem to be normal and like all the other days when we had no issues and also the user connections were relatively high.
While contacted microsoft, they suggested to run a Pss Diag and send them the output. Still am curiuos to know what could be the problem. Kindly throw some light in this regard.
-
do your connections use isolation level repeatable read (default for .net) in stead of read committed ?
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
-
- launch sql profiler and examine your most consuming queries.
- do you have "auto update statistics" activated ?
if not, did someone run sp_updatestats ?
changes in stats will result in changes in access plans !
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
-
Hi,
you said 'for the past few days'. Has your server been performing satisfactorily up until now? Have you changed anything on the server that might have triggered this problem?
Worth checking the windows event logs to see if anything is amiss, such as a problem with memory or disk which might have occurred.
On a different tack, what memory settings do you have enabled (connection pooling etc. AWE) ?
David
If it ain't broke, don't fix it...
-
1) When things go to hell, quickly run sp_who2 active to get a list of all connections. Check the BlkBy column for blocking. DBCC inputbuffer(spid) will tell you who is running what. You can find a 'head blocker' script online if necessary.
2) You may need to increase your max worker threads, although this is not to be done lightly.
3) Check for lots of paging on the server. RAM usage could be inappropriate and sql could be thrashing is't page file due to being starved for memory.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Many times I have seen applications and databases 'go down the tubes' performance-wise when adding or reaching a certain threshold of users. This is generally due to some limitation (bad design of something) within the database itself. Below is my short list for tuning. I'd do round I & Round II first. Then I would skip to a 'short list' provided by Jeff Moden before going to Round III.
Here's my 'short list' for tuning:
Round I
DBCC UPDATEUSAGE
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round II
DBCC DBREINDEX
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round III
Profiler
Query Hints
Hint: Write scalable code to start with !
"gots" list courtesy of Jeff Moden.
- Got cursors?
- Got WHILE loops?
- Got views with aggragate functions?
- Got views on views?
- Got non-setbased triggers?
- Got user defined functions (not all are bad but a lot of folks just don't know
- how to write good ones).
- Processing anything one row at a time?
- Got mixed datatypes?
- Got tables without primary keys?
- Got clustered primary keys on big transactional tables?
- Got dates stored as VARCHAR?
- Got temp DB set to autogrow at 10%?
- Etc, etc...
Those are ALL different things to look at that can cause performance problems!
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Ganesh,
About 4 weeks ago, we started to experience the same type of slowdown - after rebooting the server, a complex test query takes 90 seconds......over the next 7 days, the query slows down about 15% each day..when it reaches 4 minutes, performance on on the entire system becomes unpredictable - then the query run time jumps to 8 minutes and most user queries time out.... no schema changes have taken place and we have run the test query after hours with no users on the system with the same degradation present. After a reboot, performance immediately returns to optimal levels.
We are running 2000SP3 on 2003SP1, 8 processors, 16GIG RAM with AWE enabled. We also have AV (data, log & backup directories excluded), Veritas (running replication to our DR site), Lumigent (collecting audit data) and Quest (Performance Analysis) on the box.
Reading through the fixes in SP4, I did find the following:
FIX: Intermittent query slowdowns and corresponding high CPU utilization
http://support.microsoft.com/kb/835864/EN-US/
SYMPTOMS
<script type=text/javascript>loadTOCNode(1, 'symptoms');</script>You have a workload that is running on a multiprocessor computer that forces SQL Server to perform many memory allocation operations and memory free operations each second (several hundred small batches that require compilation each second, for example). You may notice that a query that typically runs in a certain time takes longer to complete, although the query processes the same amount of data, uses the same query plan, and performs the same amount of I/O. The only noticeable difference is an increase in the CPU time and the elapsed time that it takes to run the query. In extreme cases, you may find that simple queries (such as SET ROWCOUNT 0) might take several hundred milliseconds to run and several hundred milliseconds of CPU time.
We have been reluctant to move to SP4 (+AWE hotfix) due to a number fo comments relating to poor performance and increased blocking after the SP is installed. Any thoughts?
-
Harley, are you actually OBSERVING high CPU utilization during these periods of poor performance?
Since you have win2k3 sp1, are you by chance copying large files to slow drives? One of my clients got pounded by that OS bug. It would essentially flush the RAM, leading to extensive paging and horrible performance.
Check for excessive paging too. Unchecked sql server (or other app/service) memory growth can lead to disk thrashing.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
SQLGuru,
The CPU usage is typically hovering around 13-18%....we do see spikes during our heavy load periods up to 50% but very rarely higher.....on Sat, the CPU is running at 1-2% and the slowdowns are still present......most of the data for the test qeuery is cached - particularly by the 3rd run since we always run 3 consecutive trials......cache hits always run 98.7+ - no large file copies.......
Unfortunately, we don't run on our own servers so I have no access to the OS and only limited access to SQL through QA as a typical user - Quest Performance Analysis is my only window into what is going on.......
The odd thing is that the performance degrades steadily over time - even on a Sat, when no users are on the system , the performance stays at the worst levels after 5-6 days of uptime until a reboot, then performance immediately returns to normal..........
It seems that the server is steadily running out a resource that is not released....
-
Hate to say it, but if you can't get on the box and check out task manager, perf mon and run various sql server system stuff like dbcc sqlperf, etc, you are pretty much hosed because you won't have any way (well, perhaps Quest PA can help but I don't know this tool's capabilities) to see what is choking the system.
REAL good reason here for not going to a hosted model . . .
As a workaround - do you have any regular maintenance window whereby you can simply shutdown/restart sql server?
Oh, btw, does fixing the problem require a REBOOT of the entire server, or just a bounce of the SQL Server service?? This could be meaningful.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Here is one all important question, that depending on the answer, may go a long way towards explaining things. Since you are in a 'hosted' environment, is your SQL Server a 'physical' server or a 'virtual' server ? If it is not a 'virtual' server you are still in the same boat. However if it is a 'virtual' server then your hosting company has some work and possibly explaining to do.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
SQLGuru - I couldn't agree with you more - this is the first time I have ever had responsibility for a system that I can't touch - something like trying to be a doctor and diagnosing a patient over the phone without ever actually seeing them.....
Bouncing the SQL services has no affect - the performance still stays at the worst levels - but a reboot immediately resets it back to optimal. I assume that bouncing SQL would release any memory, as well as other resources, used by SQL Server but the article I referenced in an earlier post
Rudy - the server is a physical server - actually 2 8-ways clustered dedicated to my specific application.
Sorry Ganesh - I don't mean to take over the thread - just noting a similar experience in hopes that someone has discovered a solution.
Harley
-
I'm gonna go out on a limb here and say that if bouncing sql server service doesn't improve sql's query performance then the problem is NOT sql server. Something else is eating all of the server's RAM, disk capacity, CPU or network resources.
Hmm, since it is a cluster it could be something funky with that too I suppose. Those damn things can be SOOO cantankerous!! 🙂
Can you remote into the box? I am betting not. Good luck resolving the problem if not! I suppose you could get one of the hosting company's staff on the phone and have them repeat back what they see for various perf mon counters, etc.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
SQLGuru,
I agree with your thought - bouncing SQL seemed to remove it from the list of suspects...and no, I have no access to the box at all - except through the app and SQL QA as a standard user - every diagnostic, change script, etc has to be run by the hosting company after a long list of approvals....somewhat of a nightmare scenario......
I have opened several tickets with hosting company - they are "looking into the issue"
Harley
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply