Possible performance issue with 6.5.2.9

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

Possible performance issue with 6.5.2.9

Postby ChrisAVWood » Thu Jan 26, 2012 10:58 pm

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
English DBA living in CANADA
ChrisAVWood
 
Posts: 308
Joined: Tue Dec 18, 2007 6:18 pm
Location: Edmonton, Alberta, CANADA

Postby petey » Mon Jan 30, 2012 5:26 am

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.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2230
Joined: Sun Apr 24, 2005 12:34 pm

Postby ChrisAVWood » Mon Jan 30, 2012 5:02 pm

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
English DBA living in CANADA
ChrisAVWood
 
Posts: 308
Joined: Tue Dec 18, 2007 6:18 pm
Location: Edmonton, Alberta, CANADA

Postby ChrisAVWood » Mon Jan 30, 2012 11:54 pm

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
English DBA living in CANADA
ChrisAVWood
 
Posts: 308
Joined: Tue Dec 18, 2007 6:18 pm
Location: Edmonton, Alberta, CANADA


Return to SQL Backup 6

Who is online

Users browsing this forum: No registered users and 0 guests

cron