Performance Goes Down - Help!
-
GOSH!! I feel your pain! What a horrible situation!! Best of luck with the hoster. BTW, be sure to post a "this hosting company sucks-ass" posting all over the web if they don't give you satisfaction.
Personally I would already be working on that given the duration of your troubles! I would have expected some serious escalation of this problem on their end by now.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
I get the impression that your 2x8 machines have been running for some time with no problems until now? If yes the first question would have to be "what has changed?" even better, can you tie a specific change either in your database or to your hosted environment to the sudden change in performance?
Given that you're running in a hosted environment I would assume that even though you have "your" machines, you are sharing other portions of your environment with other customers (e.g. a SAN perhaps?) which could also impact the performance of your database servers. Get the hosting company to fire up performance monitor on those servers and at the same time ask about recent changes to any shared components in your environment.
Joe
-
Hi Harley,
found this link on the internet to DBCC MEMORYSTATUS, it seems to be an undocumented DBCC command for Microsoft tech support. If you have DBCC access through SQA, you might be able to get some memory info from the server using this.
http://support.microsoft.com/kb/271624/ (SQL Server 2000)
http://support.microsoft.com/kb/907877 (SQL Server 2005)
There is nothing in BOL about this command, and I have not used it in anger myself, so can't vouch how useful it is, but perhaps someone else can shed some light on it.
David
If it ain't broke, don't fix it...
-
PS:
My prime suspects for memory/resource eating applications affecting include Virus checkers running out of control and terminal sessions not releasing their resources.
If bouncing SQL Server has no effect on the performance then it is probably not the contents of SQL Server's memory, it would either have to be the physical data or some process external to SQL Server.
Do you have a test system on the same server, does that show a concurrent deterioration in performance? If it does then the problem is likely to be server environment, if it doesn't then the problem is within your database.
David
If it ain't broke, don't fix it...
-
Check the target server memory and total server memory. Might be a memory problem too. Check how much you r sql server is using. Each connection takes few memory for it to process data.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
You may have a back door into the cluster is the SQL Server service and the cluster service are using domain user accounts, they have to be local admins, and you have the password to any one of them your in. Try using xp_logininfo with the service accounts to see what authorities they have. If they have disallowed interactive login then you probably have full access to the OS via xp_cmdshell since most sites do not want to muck with things a whole lot and just make those accounts local admins.
There is quite a bit you can still do - I know since I have had to 'back door' servers in at paranoid client sites in the past.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Most common issue I see that casues this type of issue is that when a person creates an object in VB and ASP they forget to do proper cleanup.
Make sure if you allocate and object you close it an properly destroy if needed. Such as this
Dim x
SET x = Server.CreateObject("ADODB.Recordset")
x.Open ....
If you don't have a corresponding
x.Close
SET x = nothing
you may have locked resources and unfreed memory.
Very common mistake which even with a few connections can cause the very issue you are mentioning.
-
when you say sql performance is running slow, run profiler and only track those t-sql or stored procs that runs more than certain time (4s, etc). analyze to see if your server is hit badly with bad long running queries. check another counter using perfmon under system > context switch. i'm not 100% if context switch is under system, i wrote it from my memory.
if the context switch is high (~10K/s) or your server is in deep trouble >20K/s), then turn of your sql parallelism.
sp_configure 'max degree of parallelism', 1
reconfigure
Simon
Simon Liew
Microsoft Certified Master: SQL Server 2008
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply