SQL Server 2005 performance issue

Question

Hi !

I have a dev and a UAT environments. Dev is in our place, UAT is in client's place.

Our DEV machine is a XEON 4 core @2,33GHz, 4Go RAM with Windows server 2003 The UAT physical machine is quite the same but a virtual machine is used (under VMWare). I don't know the exact parameters used for this VM.

The problem is that the SQL Server on the dev machine runs very well and the one on the UAT is very very slow.

Opening SQL Server Management Studio takes 2 minutes on the UAT machine. Runing even a simple select request is also very slow. The database is quite small (6 GB). Opening any other application on that server works well.

So we think there is a problem with the sql server instance and I must investigate to find the reason.

Here is what I checked :

• server configuration is similar to the one we have on DEV.
• there is enough space on disk
• processors are not overloaded (10% used is the max reached)
• memory seems also to be OK.
• data and log files are set to grow automatically
• SQL Server Recovery model : FULL

It seems in the database log that this error occured at least once (I only have access to a small part) :

2008-10-14 19:16:54.84 spid55
Autogrow of file 'xxxxx_log' in database 'xxxxxx' was cancelled by user or timed out after 6766 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

As there is enough space on the hard drive, what could be the cause ? Could it be related to my perfs problem ? What should I check to find the cause of the problem ?

I'm not a SqlServer expert so if someone has any suggestion, I'd love to hear it. Thanks !

---

Update 1 :
SQL Server Recovery model : FULL
The database is new so so far we didn't performed any backup.
I don't know the log file size, I'll check that.

Update 2 :
The Management Studio problem is solved.

It's caused by the fact that there is not Internet access on the server and that Management Studio seems to try to connect when starting : http://weblogs.sqlteam.com/tarad/archive/2006/10/05/13676.aspx

But it seems that the perf problem is not linked to that problem. Still searching.

Answer 1

If it's not a processor issue, I wonder if the network isn't configured appropriately. If it's trying several things and waiting for connections to time out etc, that could make things really painful.

Answer 2

Sounds like a VM problem. When "Opening SQL Server Management Studio takes 2 minutes on the UAT machine" - if you are not connecting to a SQL Server in that time, then the SQL Server instance has little part to play in that slowness.

If you mean it takes 2 minutes to connect to the local SQL Server, then I'd look at memory and the VM settings.

Answer 3

Are the virtualization extensions of the processor enabled? Disabling these could cause quite a performance loss.

Answer 4

1/ Check how much RAM is being consumed by the SQL Server. For a small database on a 4GB RAM server, I'd set SQL Server to only grow to a maximum of 1GB (1024MB) of RAM.

2/ I'm check the autogrow size for the DB files and the LOG file. We avoid using percentage growths and use fixed MB growth sizes (like grow in 1MB chunks).

3/ Make sure your backing up your transaction logs regularly, otherwise they will just grow and grow. (Also back up the full database while your at it!)

4/ What disk setup do you have? Avoid RAID 5 for high IO systems (use mirrored data drives), put your LOGS and DATA files on different disks, check your RAID array is configured correctly. Are there other systems using the disks?

5/ Use SQL Profiler to examine what the server is actually doing. You can be surprised at how badly some SQL code can run on a busy machine (and how well it can on a dedicated machine).

Good Luck.