SQL Server 2008 random "go slow" for two minutes

deepsix
deepsix used Ask the Experts™
on
We are running SQL 2008 Enterprise server with ASP Persistent sessions and our main data DB on the same 2008 Enterprise server. We're currently experiencing random freezes where all websites and applications accessing the DB become un-responsive.

Looking at the activity monitor in SQL Server Management Studio there is nothing out of the ordinary whilst the slow-downs occur. However, there are huge spikes randomly. The "Waiting Tasks" leap from between 0-10 as they normally are to 80,000 and the DB I/O leaps from the 0.3-1MB/sec it normally runs at up to 80MB/sec.

I think that the huge spikes may be due to the ASPState table doing a Commit. I managed to catch it once and it had huge IO so I don't think that this is part of the problem.

Are there any tests I can run to find what is causing the slow down? Our websites use a lot of ajax (one request every three seconds per customer) and we have a ~200 users on the websites concurrently so using the profiler is extremely difficult.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011

Commented:
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> The "Waiting Tasks" leap from between 0-10 as they normally are to 80,000 and the DB I/O leaps from the 0.3-1MB/sec it normally runs at up to 80MB/sec.

Seems like you have hardware resource contention in your machine.
Either your CPU is not able to support the load or insufficient memory..

Kindly let me know your SQL Server edition and hardware configuration so that we can check whether it is limited to SQL Server edition or other parameters accordingly..
Top Expert 2012

Commented:
>>We are running SQL 2008 Enterprise server with ASP Persistent sessions and our main data DB on the same 2008 Enterprise server.<<
I trust you are not sharing IIS and SQL Server on the same server, right?

Do you have Auto-Close set for your database?  If so disable it.
Have you allocated enough space for your data and Transaction Log files.  If not this could account for the slowdown.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
Answering all the points:
-The server is running Enterprise edition with 8x2.4GHz Xeon processors in a XenServer VM.
-IIS and SQL are on seperate machine
-We have seperate HDDs for the data and transaction log files, all of which have ample free space (70% free)
-I don't believe the HotFix will work as we are tunning .Net 3.5 and that hotfix was included with .Net 1.1 SP1
-I don't know what Auto-Close is, do you have any further reading?

Thanks

Author

Commented:
I have found the Auto-Close and Auto-Shrink options and they are both disabled on the main DB and the ASPState DB.
Top Expert 2012

Commented:
>>-We have seperate HDDs for the data and transaction log files, all of which have ample free space (70% free)<<
But the question is have you "allocated enough space for your data and Transaction Log files"? and not whether you have enough free disk space (that is a given).  In other words, have you checked how much space is used in each database and how much is free?  When there is no more free space in the database it needs to grow and depending on the configure growth size this can take time.

Author

Commented:
Sorry, I misunderstood.

The main DB file is set to auto-growth 1024MB, unrestricted growth. The log file is set to autogrowth initial 1024MB, restricted growth to 20480MB. It is currently 11GB.
Top Expert 2012
Commented:
You still have not told me very much.  Sometimes a picture is worth a thousand words:
1. Select your database
2. Right-click and select "Reports".
3. Select "Standard Reports".
4. Select "Disk Usage"
5. This will clearly show you the amount of space reserved and the actual space used.

>>It is currently 11GB.<<
This sounds excessive.  When was the last time you did a Transaction Log backup?  If the answer is never, then consider:
A.  Start a Transaction Log Backup plan, to run say every hour.
B.  Change the Recovery Model to Simple (and understand that you lose the possibililty of doing a point-in-time recovery.
Top Expert 2012
Commented:
>>SQL Server 2008 random "go slow" for two minutes<<
This could be the time it takes to allocate 1GB for the Data or Transaction Log file.

Author

Commented:
So effectively I lose the log every hour? is this correct?

Screenshot attached
datause.png
Top Expert 2011
Commented:
no you backup the transaction log every hour...

so the active log is reduce/maintained at a reasonable size...

you archive you backed up log files to another machine/tape for disaster recovery purposes...

you determine how many transaction log  backups together with full and/or differential full Database backups you keep with respect to the element of risk your business is willing to take with regards to data loss and the time required to restore the database.

as acperkins indicated the file growth scenario is a potential cause of response time problems as the dbms expends effort to grow and prepare the bew file areas... Ideally you should always plan your database size increases, and be monitoring database usage.  Ensuring the transaction log is regularly backed up reduces the scope for unplanned file growth processing.
Top Expert 2012

Commented:
It definitely looks like you have a problem with your Transaction Log.  So again when was the last time you backed up the Transaction Log?  See my recommedations here http:#a33139628

Author

Commented:
Ok, I think I've never done that as I'm not sure what you mean.

Can you tell me how to schedule the backups and I'll put that in place?

Thanks.
Top Expert 2012
Commented:
Ask yourself how you are currently doing Full Backups and a Transaction Log Backup is not a lot different.  For example, if you are currently using a Maintenance plan than follow the wizard "Maintenance Plans | Maintenance Plan Wizard" to create a Transaction Log Backup.
Top Expert 2012

Commented:
If all of this sounds over-whelming or you do not have the expertise in your team, consider using option B from here http:#a33139628

Author

Commented:
After checking with others on the team, we are emptying the file every night. Is this not sufficient?

The other guy is concerned that emptying it every hour will be too much of a performance burdan. Is this concern waranted?
Top Expert 2011
Commented:
obviously not, you originally sized the log for 1GB its since had to grow on 5 occasions...

so it requires at least 10GB as a primary at your current processing frequency...

only you know whether this is exceptional for your system.

if you have plenty of freespace then change the t-log allocation to 20GB with a secondary of 0.5GB
, monitor its usage, and see if that reduces the incidence of your go slows.
Top Expert 2012

Commented:
>>After checking with others on the team, we are emptying the file every night.<<
What does this mean?  What file?

>>The other guy is concerned that emptying it every hour will be too much of a performance burdan. <<
I trust "the other guy" will not take it the wrong way if I state he/she has not got a clue.  

>>Is this concern waranted?<<
In short no.  You are already having performance problems potentially from the file increasing frequently and you are concerned about Transaction Log backups running every hour?

You really need to change your Recovery Model to Simple.  You obviously do not have the expertise to handle a database in Full Recovery Model.
Top Expert 2012

Commented:
Top Expert 2011

Commented:
points to acperkins

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial