May 30, 2011 at 6:02 am
We have a database hosted in SQL Server 2000 in F drive of the server.
We want to move the data base to G drive. Before doing so the performance of the disk needs to be tested and should be comparable with that of F drive.
For performance testing we have created two dummy databases in F drive and G drive and created 1 table in each of the databases.
Then we have inserted 2872029 rows containing 716664 KB data from a table in F drive.
Please find the results below:
F Drive:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 14 ms.
Table 'Test'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
Table ‘Test_Target’. Scan count 1, logical reads 89696, physical reads 0, read-ahead reads 89717.
SQL Server Execution Times:
CPU time = 22532 ms, elapsed time = 63101 ms.
(2872029 row(s) affected)
________________________________________________________________________________
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'Test'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
Table ‘Test_Target’. Scan count 1, logical reads 89696, physical reads 0, read-ahead reads 44369.
SQL Server Execution Times:
CPU time = 21547 ms, elapsed time = 36929 ms.
(2872029 row(s) affected)
__________________________________________________________________________________
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'Test'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
Table ‘Test_Target’. Scan count 1, logical reads 89696, physical reads 2, read-ahead reads 24256.
SQL Server Execution Times:
CPU time = 21375 ms, elapsed time = 60275 ms.
(2872029 row(s) affected)
____________________________________________________________________________________
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'Test'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
Table ‘Test_Target’. Scan count 1, logical reads 89696, physical reads 6, read-ahead reads 42115.
SQL Server Execution Times:
CPU time = 21797 ms, elapsed time = 33818 ms.
(2872029 row(s) affected)
_____________________________________________________________________________________
G Drive:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 3 ms.
Table 'Test'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
Table ‘Test_Target’. Scan count 1, logical reads 89696, physical reads 0, read-ahead reads 43500.
SQL Server Execution Times:
CPU time = 24281 ms, elapsed time = 132024 ms.
(2872029 row(s) affected)
_____________________________________________________________________________________
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'Test'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
Table ‘Test_Target’. Scan count 1, logical reads 89696, physical reads 0, read-ahead reads 34094.
SQL Server Execution Times:
CPU time = 22297 ms, elapsed time = 120116 ms.
(2872029 row(s) affected)
_____________________________________________________________________________________
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'Test'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
Table ‘Test_Target’. Scan count 1, logical reads 89696, physical reads 2, read-ahead reads 20536.
SQL Server Execution Times:
CPU time = 22109 ms, elapsed time = 40198 ms.
(2872029 row(s) affected)
_____________________________________________________________________________________
Is there any better way to test the performance of the drive?
May 30, 2011 at 9:36 pm
Can anybody help me on this?
May 31, 2011 at 12:39 am
What do you need help on? The G drive, per your metrics, is significantly slower than the F drive, right?
Note that SQLIO is a much better tool to test SQL Server IO pattern response times and throughput.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 31, 2011 at 9:49 pm
Thanks a lot for your help.
There is another database of around 300 GB in F drive which also a reporting database.
If I move the current database to G drive which is about 350 GB, will it improve the overall performance due to IO improvement.
Please note that G drive is much slower as per the below statistics:
F Drive:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 14 ms.
Table 'Test'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
Table ‘Test_Target’. Scan count 1, logical reads 89696, physical reads 0, read-ahead reads 89717.
SQL Server Execution Times:
CPU time = 22532 ms, elapsed time = 63101 ms.
(2872029 row(s) affected)
________________________________________________________________________________
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'Test'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
Table ‘Test_Target’. Scan count 1, logical reads 89696, physical reads 0, read-ahead reads 44369.
SQL Server Execution Times:
CPU time = 21547 ms, elapsed time = 36929 ms.
(2872029 row(s) affected)
__________________________________________________________________________________
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'Test'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
Table ‘Test_Target’. Scan count 1, logical reads 89696, physical reads 2, read-ahead reads 24256.
SQL Server Execution Times:
CPU time = 21375 ms, elapsed time = 60275 ms.
(2872029 row(s) affected)
____________________________________________________________________________________
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'Test'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
Table ‘Test_Target’. Scan count 1, logical reads 89696, physical reads 6, read-ahead reads 42115.
SQL Server Execution Times:
CPU time = 21797 ms, elapsed time = 33818 ms.
(2872029 row(s) affected)
_____________________________________________________________________________________
G Drive:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 3 ms.
Table 'Test'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
Table ‘Test_Target’. Scan count 1, logical reads 89696, physical reads 0, read-ahead reads 43500.
SQL Server Execution Times:
CPU time = 24281 ms, elapsed time = 132024 ms.
(2872029 row(s) affected)
_____________________________________________________________________________________
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'Test'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
Table ‘Test_Target’. Scan count 1, logical reads 89696, physical reads 0, read-ahead reads 34094.
SQL Server Execution Times:
CPU time = 22297 ms, elapsed time = 120116 ms.
(2872029 row(s) affected)
_____________________________________________________________________________________
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'Test'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
Table ‘Test_Target’. Scan count 1, logical reads 89696, physical reads 2, read-ahead reads 20536.
SQL Server Execution Times:
CPU time = 22109 ms, elapsed time = 40198 ms.
(2872029 row(s) affected)
_____________________________________________________________________________________
June 1, 2011 at 12:47 pm
Sorry, but I still don't see anything you need help on.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply