Possible performance issue with 6.5.2.9
ChrisAVWood
Posts: 361
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
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
English DBA living in CANADA
Comments
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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
I'll close this off then.
Chris