How to get Read/Write percentages for a SQL Server database

The 2015 fishing opener started out with some frustration, but ended up being quite successful for me. I got up at 4:40 AM, which was just a bit later than I had wanted. I also took a bit too much time showering and eating breakfast, so I got to the boat launch on Okauchee lake at 6:10 AM, 25 minutes later than I had intended. Now this was my first fishing opener with a boat, and I clearly underestimated how many other people were as pumped up about it as I was, because the boat launch was full! I was disappointed because I had really wanted to fish Okauchee, but the public launch on Pewaukee at Naga-Waukee is only a ten minute drive from there, so I decided to head over to Pewaukee. Upon arrival, I was shocked to see that this launch, which is much larger than Okauchee with 71 trailer parking places, was also full. I ended up going over to Smokey’s Bait Shop on the far east end of the lake and launching there. I’d never fished the east end of Pewaukee before but I knew it was shallow and weedy all over. As I motored out from the launch, I discovered good weeds almost immediately, and decided this was as good a spot as any to search out some muskies. The wind was coming from the southwest, and I drifted toward the north shore of the lake. I saw a muskie pretty quickly, although it didn’t seem to be following my bait, it was still a good sign that their were fish in the area. The weeds were pretty high in this area, and I was cleaning seaweed off the hooks of my perch colored shallow raider after almost every cast. I began to get close to shore and was about to stop fishing so I could motor back toward the middle of the lake when I saw a hard weedline stretching parallel to the shore. The weeds seemed to completely stop when they got about 20 – 30 feet from shore. This looked like an excellent ambush location, so I fired off a cast west along the weedline. As the lure got to the boat I looked down to see a beautiful muskie following the bait closely. She bit the bait and gave a head shake, and I quickly pulled the rod tip up and drove the hook into her mouth. The fight wasn’t too intense, although she did walk me completely around the boat once. I netted and unhooked her, got a couple of pictures, and then released her back into the water.
20150502 Muskie Enhanced[2]
The rest of the outting was good, but I didn’t land any more muskies. I saw three others in the water, saw four other boats land muskies, and also landed a pretty solid largemouth bass. It was a fun day, and great to be back out on the water after a long winter.

We are going to be replacing our product SQL Server in the next couple months. We have already picked out the new hardware, and I have begun the process of installing SQL Server and running some tests. One important test that should always be run when replacing a server is disk I/O, especially if you are not using a SAN. There is a great tool that can be used to measure disk I/O, but I will demonstrate that in a later post. First we need some preliminary information, the most important one being the read/write ratio of your databases. Fortunately, SQL Server tallies each and every read and write in the form of index stats in the sys.dm_db_index_usage_stats DMV. Using this view, we can get a great idea of the percentage of reads and writes in each database.

I wrote this query, which takes the sum of all reads, both system and user, and divides it by the sum of all reads and writes to get the total read percentage. Similarly, it takes the sum of all writes and divides it by the sum of all reads and writes to get the write percentage. Lastly, I group by database so I can see the difference in each database on the instance. This wouldn’t be required if you wanted an overall picture of all databases on the instance, but I know what my most important and largest databases are, and I want to really know the read/write percentage on those databases.

SELECT  DB_NAME(s.database_id) AS Database_Name ,
        SUM(s.user_seeks +0.000
           + s.user_scans 
           + s.user_lookups 
           + s.system_seeks
            + s.system_scans 
           + s.system_lookups) / SUM(s.user_seeks
                                       + s.user_scans
                                       + s.user_lookups
                                       + s.user_updates
                                       + s.system_seeks
                                       + s.system_scans
                                       + s.system_lookups
                                       + s.system_updates) AS Read_Percentage ,
        SUM(s.user_updates + 0.000
           + s.system_updates) / SUM(s.user_seeks
                                        + s.user_scans
                                        + s.user_lookups
                                        + s.user_updates
                                        + s.system_seeks
                                        + s.system_scans
                                        + s.system_lookups
                                        + s.system_updates) AS Write_Percentage
FROM    sys.dm_db_index_usage_stats AS s
GROUP BY s.database_id
ORDER BY DB_NAME(s.database_id);

20150506 Read Write Percentages
A couple notes on this query:

  • The stats are cumulative since the last SQL service restart.  The longer you wait to run this since the last server reboot or service restart, the more meaningful the stats will be.
  • The DMV used in this query does not exist on versions prior to SQL Server 2008.

Stay tuned for an upcoming post where I will use the results of this query to measure disk I/O for a new server compared to the current server.

Advertisements

2 thoughts on “How to get Read/Write percentages for a SQL Server database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s