The set-up is Dell PowerEdge 2950 with two dual-core Xeon CPU, 4GB RAM and plenty of SAS RAID-5 disk storage. The system is running ESX Server v3.5 (for disaster recovery). A single virtual operating system is installed running Windows 2003 R2 standard 64 bit with the latest service packs. The core software installed is Exchange 2007, SQL 2005 standard 64 bit and Backup Exec 11d.

Normally, the system runs like a treat - no performance issues at all. However, now and then SQL server performance is awful. Twice now we've had to reboot the OS to get performance back - only because we don't know the cause - wouldn't normally do anything that drastic. For example, executing a very simply t-sql "Select * From People Where Agent=1" takes 30 seconds. Normally it's almost instant.

Whilst VM is at 5.9GB (on a 4GB) system, it doesn't appear to be memory related. Watching performance monitor shows that disk, memory and CPU are all running at low levels, i.e. nothing is particularly max'd out. Logging on to the console remotely and the system "feels" responsive. Tools open fine, connection to SQL is as fast as usual, just very slow running a query.

I was watching the server remotely in SQL Server Profiler and noticed that Reporting Services was doing something relatively frequently. We don't actually use Reporting Services so I stopped the service. Performance is now back to normal but I don't know whether that is a coincidence or not.

Does this effect/problem sound familiar to anyone? We're at a bit of a loss.

I know that both Exchange and SQL do try and grab as much memory as possible. Should they two systems be tweaked, memory-wise, when they are on the same system?

Thanks, Rob

 Welcome to the forum Rob...

 There is no straight forward answer to this you need to look at many things few of them are below.

Rob Nicholson :

Select * From People Where Agent=1" takes 30 seconds. Normally it's almost instant.

Check the execution plan of the query, whether its using proper index or not.

Rob Nicholson :

The system is running ESX Server v3.5 (for disaster recovery). A single virtual operating system is installed running Windows 2003 R2 standard 64 bit with the latest service packs.

 

What is the configuration of the  virtual machine???

Rob Nicholson :

The core software installed is Exchange 2007, SQL 2005 standard 64 bit and Backup Exec 11d.

This can also be a problem you need to monitor CPU & memory utilization for all these application and then see if this might be a problem.

Rob Nicholson :

Whilst VM is at 5.9GB (on a 4GB) system, it doesn't appear to be memory related.

Use performance monitor to see the buffer cache hit ration counter.

Rob Nicholson :

Logging on to the console remotely and the system "feels" responsive. Tools open fine, connection to SQL is as fast as usual, just very slow running a query.

 

You didn't specified the database size and the number of database you are managing in this instance. Apart from this are you checking the indexes i.e. the fragmentation etc. Also check for long running and queries which are taking most of the CPU. Also tell us about the tempdb uses also are you optimizing it?

There a lots of thing to be checked and these are some of the basic thing I thought of looking you post

Hope this gives you fair amount of idea as where to start from.

Rob,
Several things running through my mind.

- Is it only slow if you perfom query on client -> Network overload ?
- Does it help speed up perfomance if you stop Exchange ? -> Memory &| Disk I/O
- Where are the data/logfiles saved? same disk? same disk as exchange file? are all disk connected over same controler?  -> Disk I/O
- what about database mainenance? -> index reorg? statistic update?
- is sql server paging?? (set security option "lock pages in memory" for sql service account. Needs to be set exclusiv for service account. Adding service account to local admin does NOT include this setting!)

In general I would never mix exchange and sql server on same box if performace is an issue. Especially not on a virtual box!
I would look at performace counters for avg. disk queue length on phisical disk, paging, buffer cache hit ratio...

HTH
acki4711

acki4711:

- Does it help speed up perfomance if you stop Exchange ? -> Memory &| Disk I/O

Yes, it does indeed help if Exchange is stopped. The cause of this was the battle between Exchange and SQL Server for memory. Both of these systems is designed to grab as much memory as it can but it should equally release it if something else asks for memory.

The fix was to configure the "Minimum memory" for SQL Server which was at it's default of zero. Setting this to 512MB and it's been fine since. It isn't a heavily used SQL server but I think what was happening was that Exchange was been a big bully and grabbing almost every byte of memory for itself.

Cheers, Rob.