December 13, 2010 at 4:07 am
We have SQL Server 2005 SP2 installed on a virtual server (4GB Ram, Dual 3Ghz Intel Xeon processor) with approximately 30 user databases. The majority of these databases are small, in house systems although we do have 3 or 4 business critical databases on the server.
Occasionally throughout the day we experience periods where things slow down significantly, causing user applications, accessing the data via a web browser to time out. On looking at the activity monitor, the main culprits appear to be processes that are listed as dormant (always seems to be sys.sp_reset_connection;1) and there also appear to be a lot of suspended processes (with the hourglass icon). Killing off these processes usually does bring the server back to life but obviously this is not a favorable solution.
I would be grateful for any help or advice to solve this issue.
Thanks in advance
Craig Buck
December 13, 2010 at 12:41 pm
Are you running perfmon or any kind of 3rd party solution to monitor the server? It's kind of hard to say without more info about what you're seeing on the server and how it's configured (you didn't mention storage config at all).
We can guess, but you'll get better results if you can provide more info.
December 14, 2010 at 1:47 pm
What edition of SQL Server are you using ? And 32 bit or 64 bit ?
What Version & edition of Windows are you using ? And 32 bit or 64 bit ?
What is the size of the page file on the server ?
Are you doing Index Rebuilds\Reorgs and Update statistics on a scheduled basis ?
There seems to be some resource bottlenecks on the server and also issues due to missing indexes etc..
But to quickly identify the bottle necks , you need to run a basic perfmon counter log for 24 hours and then find the problematic queries. The following counters are a good list to capture.
PhysicalDisk(_Total)\Avg. Disk Queue Length --(should get individual disks counters)
PhysicalDisk(_Total)\Avg. Disk sec/Read --(should get individual disks counters)
PhysicalDisk(_Total)\Avg. Disk sec/Write --(should get individual disks counters)
PhysicalDisk(_Total)\Current Disk Queue Length --(should get individual disks counters)
MSSQL\Processor(_Total)\% Processor Time
Process(sqlservr)\% Processor Time
SQLServer:Access Methods\Full Scans/sec
SQLServer:Access Methods\Index Searches/sec
SQLServer:Access Methods\Page Splits/sec
SQLServer:Buffer Manager\Buffer cache hit ratio
SQLServer:Buffer Manager\Free pages
SQLServer:Buffer Manager\Lazy writes/sec
SQLServer:Buffer Manager\Page life expectancy
SQLServer:Buffer Manager\Pages/sec
SQLServer:Memory Manager\Memory Grants Pending
SQLServer:Memory Manager\Target Server Memory (KB)
SQLServer:Memory Manager\Total Server Memory (KB)
SQLServer:Plan Cache(_Total)\Cache Hit Ratio
SQLServer:SQL Statistics\Batch Requests/sec
SQLServer:SQL Statistics\SQL Compilations/sec
SQLServer:SQL Statistics\SQL Re-Compilations/sec
Procesor: % PrivilegeTime
System: Processor Queue Length
System: Context Switches/Sec
Memory: Available Mbytes
SQLServer:Locks Average Wait Time (ms)
SQLServer:Locks Lock Waits /sec
SQLServer:Locks Lock Wait Time (ms)
Thank You,
Best Regards,
SQLBuddy
December 15, 2010 at 8:23 am
Have you done a file IO stall and wait stats analysis? That is the first place to start.
Given the open ended nature of the question I highly recommend you hire a performance tuning professional to give your system a review. It is almost certain that a lot of opportunities for improvement will be found in a very short period of time.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 16, 2010 at 2:21 am
Thanks for all of your help guys. I am going to run the perfmon for 24 hours and see what we can glean from that. We have talked about bringing in external help as we have had this problem for a while now.
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply