Today I wanted to do some POC since it was boring day for me at work, so I tried to play with SQL Server baby.... Topic today is how to take backup faster then normal time.
Now there are two parameters in the backup command called "MaxTransferSize" and "BufferCount"
What is MaxTransferSize?
"MaxTransferSize Specifies the largest unit of transfer in bytes to be used between SQL Server and the backup media. The possible values are multiples of 65536 bytes (64 KB) ranging up to 4194304 bytes (4 MB)" which means amount of bytes consumed by a block transfer from SQL Server to .bak file.
Note:
1) The value that is set for the MaxTransferSize parameter must be a power of 2, and the value must be between 64 KB and 4 MB, specified in bytes.
2) By using the MaxTransferSize parameter, you can increase the buffer size that can be fetched from the memory to the data cache by the read ahead operation. This will help improve the performance of the backup/restore process.
3) Default size of MaxTransferSize is 1MB.
What is BufferCount?
"Specifies the total number of I/O buffers to be used for the backup operation. You can specify any positive integer; however, large numbers of buffers might cause "out of memory" errors because of inadequate virtual address space in the Sqlservr.exe process." which means number of buckets used to transfer water from well(SQLServer) to our home water tank(backup .bak file)
How to set BufferCount Value?
Formula given by microsoft to calculate this parameter is
BufferCount = NumberofBackupDevices*3 + NumberofBackupDevices + NumberofDatabaseFiles
For example, if you have 60 backup devices and 30 database files, you would calculate the value of the BufferCount parameter as follows:
60*3 + 60 + 30 = 270
In this example, the value of the BufferCount parameter is calculated to be 270. However, in order to make sure that you have enough 1MB blocks of contiguous virtual memory to back up the database, we recommend that you round up slightly the value that you calculate for the BufferCount parameter. In this example, we recommend that you round the value of 270 up slightly, to 300.
So the total space used by the buffers is determined by: buffercount * maxtransfersize which means "how many buckets" * "capacity of each bucket"
Thus expression (BufferCount * MaxTransferSize) evaluates to the memory size that is fetched to the data cache.
Please Note that to determine that the value that you have chosen for the MaxTransferSize parameter or for the BufferCount parameter is correct, you may want to test the value.
So we have to test the combination using above tips. Don't worry for issues since if SQLServer is unable to reserve contiguous virtual memory then it will throw error rightaway.
The Reason it throws error is that SQL Server is unable to get specified number of blocks in specified number of size. This happens because "MemToLeave" the virtual address space (VAS) that's left un-used when SQL Server starts so that external components called by SQL Server are saved some address space is very less. In a typical 32-bit servers, the value on this "MemToLeave" will be few 100 MB's because virtual address space on 32-bit is only 2GB.
What is VAS (Virtual Address Space)?
It is the 4 GB region (in 32-bit) where 2 GB is reserved for the kernel mode and the other 2 GB for user mode (unless you specify /3GB). Every memory allocation (including AWE mapping) for an application happens in the user mode portion of the VAS.So it not unusual to run out!
What is MemToLeave:
Consists of a single contiguous region of addresses within the SQL Server process space that is set aside at startup. This area is static (the size cannot change) and is left unallocated by SQL Server for the use by components in the SQL Server process space, such as extended procedures, COM/OLE Automation objects, and linked servers. Memory allocations by SQL Server larger than 8 KB are also made from the MemToLeave area.
For the default values mentioned, the MemToLeave area evaluates to 256 MB on SQL 7.0 and 384 MB on SQL 2000. The -g startup parameter can be used to increase the external needs setting.
You can start SQL Server with -T3654 which allows you to run a command that returns information about the memory allocations SQL Server has done: DBCC MEMOBJLIST
Use below query to get Total Available Memory and Maximum Free Space in SQL2K5
With VASummary(Size,Reserved,Free) AS
(SELECT
Size = VaDump.Size,
Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 0 ELSE 1 END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 1 ELSE 0 END)
FROM
(
SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes))
AS Size, region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
SELECT CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
GROUP BY Size)
SELECT SUM(CONVERT(BIGINT,Size)*Free)/1024 AS [Total avail mem, KB] ,CAST(MAX(Size) AS BIGINT)/1024 AS [Max free size, KB]
FROM VASummary
WHERE Free <> 0
This value will be higher for 64-bit servers.
POC Starts:
Configuration Details:
RAID of Destination Drive: RAID 5
Type of Drive: SAN (CLARiiON Made)
SQL Server Info: SQL 2000 SP4
Server Activity: Moderate
Memory Size: 4 GB
Virtual Memory Size: 4 GB
Now for playing purpose I took a database of size 36.60 GB and played with above two values and able to see that there is change in time taken to backup. Below are the results.
From this what you can understand? Yes the same, we have to test for mutiple values based on our configuration and use it in BACKUP command to make backup process little quicker since we take backup only when the load on the server is very very minimal(night)! When I used the maximum values possible for both parameters, there was considerable amount of time reduction taken to backup. That is it took 11.03 Min to backup database with normal BACKUP command and it just took 6.70 min to backup same database with maximum possible value for both of these parameters which is almost 40% reduction in time.
Main Point of this POC:
1) The 3rd party tools which claim that backup will be taken quicker may be doing this game only... They will choose these values at run-time depending on server configuration and make the backup to happen faster....(ha ha ha) Ofcourse they use VDI and compress in memory
2) Whenever you face memory issues in SQL Server the whole issue will be resolved by restarting SQL Server :) Yes... True...
3) One more thing you can do to reduce time taken to backup is choose the proper RAID and a separate disk mapped drive for taking backup. I was amazed that one of our database took 15 mins when we where doing RESTORE process (for another DB) at same time. But when we backup the same database next day it just took 6 mins. (I know that some transaction would have happened but still this difference is huge). So note this point.
Other ways to make Backup Throughput Faster:
1. Stripe backup to different drives (Normally 4 Stripes)
2. Use Partial Backup option in SQL 2005
3. Take backup of only READ_WRITE filegroups instead of taking backup of all READONLY filegroups.
4. Use WITH NO_LOG command in BACKUP DATABASE to do flat backup of Data file and not Log file.
Links:
1. SQL Server Memory Settings and How it works?
2. Using DBCC MEMORYSTATUS
No comments:
Post a Comment
Please do not spam!