The release of SQL 2014 CTP1 has brought IO to the list of resources it can govern! (yes, previously it was memory and cpu only.) Lets get down to business and run through a quick demo so we can get a sneak peek of SQL server 2014 metering IO!
For the sake of simplicity in this demo, we will do 3 things:
1) Create two resource pools, each with their associated workloadgroup, and we will configure the max_iops value only. No throttling of memory or cpu will take place.
2) The users will each will have a login created, and under the context of those logins, we will generate some IO against the system volume (C:)
3) We will review some DMO’s and analyze the results using perfmon.
First create two new resource pools and set the max_iops_per_volume value. Note, there is no GUI for this in SQL 2014 CTP, so you will have to use TSQL. I have shown the code below. Just repeat this code twice for your resource pools and change out the max iops value.
I have randomly chosen to assign a low number so that we can quickly see the throttling take place. As you can see my pool name is ‘users’ and i have set max_iops to 100. My second resource pool is named ‘developers’ and i have given it a max_iops_per_volume value of 50.
Next, we need to create 2 workload groups which will be assigned to these resource pools. These can be done through GUI or scripts.
Then we create and assign a classifier function, and the logins. (I wont show this here as nothing has changed since the last version of SQL. Please email me if you would like a copy of the scripts that were used.)
Now we need to open two new query windows and log into each using the ‘users’ and ‘developers’ credentials. In each window we will run a DBCC CHECKDB on two copies of the Adventureworks2012 database in order to generate some IO.
Open Performance monitor to see how the IO has been throttled. The results look like this. (Note the performance counters that we are using)
The first thing that i notice is that throttling is clearly taking place. The second thing that i notice however is that the max_iops values that i have set are not being reached consistently. I suspect that a lot of CHECKDB is happening in memory after the initial process begins as AdventureWorks is quite small, a more IO specific workload and tuning of the max_iops could see a more consistent result set here.
Lets have a look at a couple of DMO’s. Microsoft has kindly added several new fields to the sys.dm_resource_governor_resource_pools. You can see here the reads_total versus the reads_throttled. (Note that this System view is not populated until you restart your instance, and that this is only a subset of the newly available columns.)
Lets also checkout sys.dm_resource_governor_resource_pool_volumes!
Here you can see the volumes that are in use by our resource pools, and also a taste of some of the other new columns added. Notice that the ‘developers’ login experienced much more throttling. (This DMV is populated once a user connection is made through Resource Governor.)
So there we have it, a first look at resource governors new IO capability. I have done a very basic example here and if you would like to test further you could scale out by increasing the workload, reducing concurrency, and playing with different values for min and max iops.
Just when we thought we were getting used to SQL server 2012, Microsoft unveils 2014 CTP1!
I still have a lot of reading to do on SQL 2012… So my initial feelings are denial! Then I realize that I cannot alter this fact, and I get over it. (Isn’t IT good for helping us accept change in life!) A little investigation shows that SQL server 2014 is destined to adopt, amongst other things, a new high volume, in-memory OLTP technology. Codename “Hekaton”…
Sounds cool right!
You can read more about Hekaton here: http://research.microsoft.com/en-us/news/features/hekaton-122012.aspx
And you can read more on SQL 2014 here: http://www.brentozar.com/archive/2013/06/almost-everything-you-need-to-know-about-the-next-version-of-sql-server/
To checkout Dave Ballantyne tearing Hekaton apart, go here:
Meanwhile… I am busy trying to get covered off with my SQL server 2012 skills! Let alone a version that is not even out yet… But a new version of SQL is too exciting to refuse!
To get started, download SQL server 2014 CTP1 here:
Take note that it can only be installed on a 64 bit platform and cannot coexist with any other SQL installation.
I have chosen to use Oracle’s VirtualBox to create a fresh VM using a trial version of windows 2012. But you can use whatever platform you like, as long as it meets the minimum requirements.
As always with a CTP version, the finished product may or may not contain the exact features that we are looking at… But let’s not wait to get started!
Finalize your install like any other by reviewing the setup log, and verifying that the data files and binaries exist where you wanted them. Make sure the services start and are running under the correct context. Then Open SSMS to set your preferred feature, instance and DB options, and then backup the sys DB’s!
Ready to go!