Whole buncha stuff
So it has been a while. In the mean time, WE SHPPED!!!! It is such a great feeling. I have been on this team for almost 7 years now and this release is defnitely the best (one would of course hope that every release is better than the previous but you never know :) :). I am super happy to have this out there and hope you are all going to be really happy with it and we are already working on really cool stuff for the next release. There is rarely a break from inovating. :) :) However, I did manage to take two weeks recently and go to New Zealand which is an absolutely amazing country. You Kiwis really know what is up. Even if a town has only 5 buildings (one of which will always sell fish and chips and the other will have postcards of sheep), you can always find an information bldg which will tell you everything to do, book it for you and send you on your way. It is the best country for tourists. I went surfing, caving, kayaking, glacier hiking, tramping, river surfing, bungy jumping and lots more. It was awesome. I am also apparently now a Hurricanes fan cause the Crusaders win too much and the Blues, well, they are the blues. :) :) I think I should spend more time with customers in New Zealand ... we'll see.
My role on the team has changed a bit and now I have a team to help work on all the great features. Sumitra S has been working with me for a while now and I am going to work on getting her to post to this blog as appropriately. She rocks and along with a bunch of work on the messagebox core functionality she wrote the (internal) OM which exposes all of the data and operations in the Admin MMC Group Hub Page. Also joining us now is Adrian Hamza after working on the Share Point adapter most recently He has his own blog which you should all checkout at https://blogs.msdn.com/ahamza. He is great and we are really looking forward to his work on the core engine. Craig C has also joined the team and will be helping define some of the long term direction we are going towards. Should make an awesome team. :) :) :)
So now maybe I will give you some good technicall details so that you are not just reading fluff. :) One thing I have really wanting to post are some learnings I have had with using Yukon and 2 tips that could really help some people avoid the gotchas that we hit when porting code directly that must run on both Yukon and Shiloh (I'd post more but it is 1 am).
1) After a number of conversations with the optimizer team, we discovered a hidden gem in the use of WITH SCHEMABINDING on UDFs. Our UDF simply took in three datetime variables and did a somewhat complex comparison of them and returned 1 or 0. It did not touch any tables and so did not affect any data. In Yukon there is a new feature / attribute associated with a UDF called SystemDataAccess. This property can be seen via:
SELECT OBJECTPROPERTYEX(OBJECT_id(‘<MyFunction>’), 'SYSTEMDATAACCESS')
Without the WITH SCHEMABINDING option, this property is always set to 1 so the optimizer assumes that you might access the data and will add sort / spool to your plan to protect itself from data changes. Adding the schemabinding hint, causes the function to be parsed when it is created and then you can see this property set to 0 and the sort / spool disappears. It is really quite nice and can be a very large perf improvement which I believe will be KB’d at some point. In our case, this is a major gain in the performance of dequeue for large queue sizes. Thanks to the SysRepublic guys for discovering the perf issue with large queues and triggering this discovery. You will find that our peformance for large queues is better on Yukon in some scenarios because of this .
2) Always owner qualify tables and sprocs / udfs which you execute. SQL plan cache access actually uses this as part of its lookup in the cache and missing this can cause a certain amount of extra cache misses and plan (re)compiles resulting in higher CPU utilization and potentially lower performance. What I mean is instead of "SELECT * FROM Foo" you would write "SELECT * FROM dbo.Foo" assuming the owner of Foo is the dbo. Unfortunately this is something we found too late and it actually didn't make it into our RTM bits as it was not a functional bug and was such an extensive change that we could not risk our RTM date since we had reached our perf goals. We are pushing to QFE this, though, and I will let you know if / when this happens. All it means is that in certain scenarios, on Yukon, we would use more CPU than on Shiloh.
These two things are just so wierd that I figured I would post about them as case one could be a big gain for those who do this (which might not be a lot of you) and case two is probably all of us and these are fixes that can do which have no effect on Shiloh but can have large gains on Yukon. Hope this helps some of you and I expect the Yukon guys will KB this stuff at some point (as well as investigating why #2 is currently worse than Shiloh).
Next time, I will post something cool about BizTalk, but now I have a coulpe more things to catch up on before I go to sleep. :)
Thx
Lee