Possible performance issue with 6.5.2.9

ChrisAVWood

Hi,

On one of our 32_bit SQL2005 SP4 Enterprise Edition servers running on W2K3R2 SP2 Enterprise Edition we went from 16Gb to 32Gb of memory, changing the min/max for SQL from 11Gb to 26Gb. Sql has not been restarted since. We have locked pages in memory/AWE enabled etc.

We do have a number of databases on this test server that have many more times the amount of data than there is memory allocated to SQL. We know that this extra memory can only be used for the data buffer. We had hoped that the time taken to backup the databases would have decreased and we do know that the extra memory is being used by SQL.

We also know that SQLBackup uses VAS memory. So would we be wrong to hope that SQLBackup could also use this extra memory to make the backups run faster?

Thanks

Chris

petey

Most of the default settings pertaining to memory utilization in SQL Backup are already running at their maximum values. The only variable that affects memory utilization is the number of threads used to perform the backup.

With a larger thread count, SQL Server needs to allocate more memory to store the backup data before passing it to SQL Backup. However, it also imposes a higher requirement on your disks. If your current bottleneck is your disks, and is usually the case, increasing the thread count wouldn't help in making your backups faster.

In the SQL Backup help file, there is a topic titled 'Optimizing backup speed', which provides some hints on how to identify the bottleneck and to determine the optimum number of backup threads to use.

ChrisAVWood

Thanks Petey. I will look at your topics.

I just thought that as we had more memory and that would only be for the data buffer, that the backups might have taken less time than before. I will examine the toal time taken and the verify portion since the memory upgrade happened. The server gets rebooted tonight and I am interested to see if that makes any difference.

Chris

ChrisAVWood

Thinking this thru I should not expect any thruput improvement as I am still having to read the database that will populate the data buffer cache and as I have much more data than I have buffer cache, even with the extra memory, I should not expect faster backups.

I'll close this off then.

Chris