- Lets say that we created database on a single drive (both data and log file). Insert one Million records into the database table. Now query the table and it takes about 8 to 10 seconds to retrieve the data. It took about 2 seconds to insert the rows.
- Now, lets say that we create database with data and log files on separate drives. Do the same thing, insert rows and run a select query. What do you think happens now? Now, it took longer to insert the million rows. It took about 45 seconds to insert the rows. Why do you think this ran longer? Check the top waits, i see that the top waits are CXPACKET and ASYNC_NETWORK_IO. It shows that the data is being inserted in parallel and ASYNC_NETWORK_IO shows that your app is slow in retrieving the data. Now, when you look at the waits at the first scenario, top wait is WRITELOG. It had to waits for 10 seconds out of 12 sec execution time in order to complete the insert statement. In this scenario, the slowness was caused due to latency on the disk. In a real world scenario, you would have to investigate by talking to system admin or network admin to check if a particular disk is slow (measure the latency on the disk), or check if a network adapter is slow.
-
Follow
Following
Already have a WordPress.com account? Log in now.
Database Blog 