Monday, March 19, 2012

BACKUP DATABASE successfully processed pages - MB/sec formula?

I am trying to find out how SQL is generating this result below
of 14.122 MB/sec? What is the formula being used?
If I do the math myself, I am doing the following:
2247732 x 8KB (since a page in SQL is 8KB) = 17981856KB
To make 17981856KB into a MB / 1024 = 17560.40625MB
17560.40625MB in 1303.799 seconds means divide these two numbers
to get the value of 13.47 MB/sec!
So what am I doing wrong here or what am I missing?
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 2247728 pages for database 'TestDB', file 'TestDB_Data' on file 1.
100 percent processed.
Processed 4 pages for database 'TestDB', file 'TestDB_Log' on file 1.
BACKUP DATABASE successfully processed 2247732 pages in 1303.799 seconds
(14.122 MB/sec).
Is the formula I am using wrong?
Thank you>I am trying to find out how SQL is generating this result below
> of 14.122 MB/sec? What is the formula being used?
pages * page_size / seconds:
SELECT 2247732 * 8192.0 / 1303.799
> snip <
> BACKUP DATABASE successfully processed 2247732 pages in 1303.799 seconds
> (14.122 MB/sec).
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"serge" <sergea@.nospam.ehmail.com> wrote in message
news:6204ABB3-D51F-4E1E-BB1E-E56547DBD75E@.microsoft.com...
>I am trying to find out how SQL is generating this result below
> of 14.122 MB/sec? What is the formula being used?
> If I do the math myself, I am doing the following:
> 2247732 x 8KB (since a page in SQL is 8KB) = 17981856KB
> To make 17981856KB into a MB / 1024 = 17560.40625MB
> 17560.40625MB in 1303.799 seconds means divide these two numbers
> to get the value of 13.47 MB/sec!
> So what am I doing wrong here or what am I missing?
> 10 percent processed.
> 20 percent processed.
> 30 percent processed.
> 40 percent processed.
> 50 percent processed.
> 60 percent processed.
> 70 percent processed.
> 80 percent processed.
> 90 percent processed.
> Processed 2247728 pages for database 'TestDB', file 'TestDB_Data' on file
> 1.
> 100 percent processed.
> Processed 4 pages for database 'TestDB', file 'TestDB_Log' on file 1.
> BACKUP DATABASE successfully processed 2247732 pages in 1303.799 seconds
> (14.122 MB/sec).
> Is the formula I am using wrong?
> Thank you
>|||Thanks Dan, however I'm having difficulty figuring out if
you made a mistake or I still don't understand the formula?
How do you reach the number of 14.122 MB/sec?
> >I am trying to find out how SQL is generating this result below
>> of 14.122 MB/sec? What is the formula being used?
> pages * page_size / seconds:
> SELECT 2247732 * 8192.0 / 1303.799
>> snip <
>> BACKUP DATABASE successfully processed 2247732 pages in 1303.799 seconds
>> (14.122 MB/sec).|||> Thanks Dan, however I'm having difficulty figuring out if
> you made a mistake or I still don't understand the formula?
> How do you reach the number of 14.122 MB/sec?
I used the decimal value of 1 million rather than 2^20:
SELECT CAST(2247732 * 8192.0 / 1303.799 / 1000000 AS decimal(10,3))
SELECT CAST(2247732 * 8192.0 / 1303.799 / 1048576 AS decimal(10,3))
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"serge" <sergea@.nospam.ehmail.com> wrote in message
news:D9047C22-1AF9-4493-8419-79DFAF0A157A@.microsoft.com...
> Thanks Dan, however I'm having difficulty figuring out if
> you made a mistake or I still don't understand the formula?
> How do you reach the number of 14.122 MB/sec?
>
>> >I am trying to find out how SQL is generating this result below
>> of 14.122 MB/sec? What is the formula being used?
>> pages * page_size / seconds:
>> SELECT 2247732 * 8192.0 / 1303.799
>> snip <
>> BACKUP DATABASE successfully processed 2247732 pages in 1303.799 seconds
>> (14.122 MB/sec).
>|||>> How do you reach the number of 14.122 MB/sec?
> I used the decimal value of 1 million rather than 2^20:
> SELECT CAST(2247732 * 8192.0 / 1303.799 / 1000000 AS decimal(10,3))
> SELECT CAST(2247732 * 8192.0 / 1303.799 / 1048576 AS decimal(10,3))
I can see now the first giving a result of 14.123 and the second giving a
result
of 13.469.
How can there be a 1000 x 1000 mixed in numbers that should be bytes, i.e.
1024?
If we use 1,024 in one part of the calculation, shouldn't the other part be
also 1,024?
We're dealing with bytes here everywhere, no?
Then wouldn't this be a MS SQL bug then?
Thanks again!|||> How can there be a 1000 x 1000 mixed in numbers that should be bytes, i.e.
> 1024?
Disk storage MB are usually measured in decimal rather than binary so one
can make the argument that throughput should also be expressed in decimal
MB.
> If we use 1,024 in one part of the calculation, shouldn't the other part
> be also 1,024?
> We're dealing with bytes here everywhere, no?
There is no place in the actual calculation where 1,024 is used. The value
of 8,192 bytes is the constant page size in bytes. The number of bytes
transferred is the same in both calculations (18,413,420,544); it is only
the denominator in the conversion of bytes to MB that differs:
SELECT 18413420544 / 1303.799 / 1000000
SELECT 18413420544 / 1303.799 / 1048576
> Then wouldn't this be a MS SQL bug then?
Not in my opinion.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"serge" <sergea@.nospam.ehmail.com> wrote in message
news:EAB6BC87-E7D8-45BA-A3EB-5BE230095FBE@.microsoft.com...
>> How do you reach the number of 14.122 MB/sec?
>> I used the decimal value of 1 million rather than 2^20:
>> SELECT CAST(2247732 * 8192.0 / 1303.799 / 1000000 AS decimal(10,3))
>> SELECT CAST(2247732 * 8192.0 / 1303.799 / 1048576 AS decimal(10,3))
> I can see now the first giving a result of 14.123 and the second giving a
> result
> of 13.469.
> How can there be a 1000 x 1000 mixed in numbers that should be bytes, i.e.
> 1024?
> If we use 1,024 in one part of the calculation, shouldn't the other part
> be also 1,024?
> We're dealing with bytes here everywhere, no?
> Then wouldn't this be a MS SQL bug then?
> Thanks again!
>|||> Disk storage MB are usually measured in decimal rather than binary so one
> can make the argument that throughput should also be expressed in decimal
> MB.
> it is only the denominator in the conversion of bytes to MB that differs:
Thanks Dan for the explanation, now I understand.

No comments:

Post a Comment