Printable View
MS SQl server 2008(Microsoft SQL Server Express Edition with Advanced Services (64-bit) , version - 10.0.2531.0) suddenly becomes slow and all the programes using the server gets stuck. After few mintues server becomes absolutely all right.
Operating System Used is "Windows server 2003 R2 stndrd x64 Edition SP2"(Microsoft Windows NT 5.2 (3790) and Platform - NT x64)
Can anyone help on this?!
Leave the Task Manager running on the Processes tab with CPU sorted descending. That may tell you what is slowing you down.
That is no where near enough information to help you out. What else is running at the time. Is the machine dedicated to SQL Server (I assume not since it is running Express). What is running on the SQL Server when the slow down occurs? Are you doing backups and the disk is becoming an issue? Are you doing a back up with compression (CPU becoming the issue). Are you running a query on the system that is causing the issue (memory pressure, cpu pressure, disk I/O pressure). We would need a lot more information the the Server is running slow then speeds up.
In addition to what Gary asks, what is the frequency of this problem?
More details had been asked for question asked in the thread located at http://www.vbforums.com/showthread.p...1&goto=newpost
Details are as below:
CPU usage:When the sql sever is down the cpu usage for process 'sqlservr.exe' was 90 and above but after installing Sql server 2008 service pack 1 the cpu usage is reduced to 36 at the maximum
Frequency for this problem in not stable.Doesn't occur everday, if gets stucks then once or more than once a day.
Daily Backup takes place from 9.00Am to around 12.00 PM, along with that hourly backup also takes place. During these backup files of the server is copied to different drive of the same PC.
All the programs accessing sql connections gets stuck and also connecting the server through Sql Management studio takes long time
I couldn't get the query asked by GaryMazzone "Are you doing a back up with compression (CPU becoming the issue)."?
You say you are doning backups from 9AM to 12 PM that means that you are taking 3 hours to backup a database that can't exceed 10 Gig (the max limit of SQL Express 2008R2). There is something wrong with that statement. I can backup a 200 Gig DB in 20 min on my desktop machine. That is including setting the back to be compressed. This is an option in SQL Server 2008 to allow for smaller backup files. If you use this feature the backup becomes more CPU intensive (the CPU will be performing the compression alogrthym). If you can't pin this to a specific time of the day then maybe you need to be running a server side SQL trace (this is cause some performance issues if not done properly) to see what is actually occuring on the SQL Server at the time of the slow downs.