Much like the USA Network here in the States welcomes characters, I’d like to formally let the world know that BIxPress also welcomes folks, and this time it’s looking at you DBAs out there!
You may be thinking, “But Jorge, the product is called BIxpress, why as a DBA would I give a flip about it?!?” Glad you asked! I’ve recently made the transition from a DBA to a BI consultant and as part of my learning process for learning the BI stack I decided to take a crack at creating an SSIS package that would take a bunch of video files from a conference, compare the file names to the actual session titles (files came down named with their session codes, not names) and rename the files according to their formal session titles. If you’re interested in that, I’ll be posting another blog post soon detailing how I did it as well as you’ll be able to download the package yourself and try it out!
Well the new year is here and SQL University is back and better than ever! I just wanted to take a minute to bring everyone up to speed on what’s going on with SQLU.
First off the last semester we had (Spring 2010) started rather late, which pushed the rest of the schedule quite a bit. One of the unique facets of SQL University is having our coach Tom LaRock (Blog | Twitter) posting on EVERY topic, EVERY week which is quite the impressive feat! That being said, since the last semester ran a few weeks late it not only made a lot of work for him so I wanted to give him ample time off as that is a TON of writing he’s doing, which I think we can all agree, is pure awesomesauce. Also Tom has undergone a job transition, as well as I have, so it’s been a little hectic on that front as well. Due to the schedule shifts, job changes, moves and generally hectic life we decided to skip the Fall semester for 2010, hence you’ll find it missing from the overall SQLU main page.
Another reason we went quiet for awhile was we were busy putting together another major project: SQL University – The Book! No, sorry, no movie deals in the works but I think you guys will like this even better. What we’re doing is compiling all of this awesome material our professors have put together for you guys into an organized e-book companion! My hope is that we can it formatted properly for distribution via Amazon’s Kindle store but if that doesn’t work out we’ll probably just PDF it and let you guys go to town! As with the rest of this wonderful project, this is absolutely free to everyone and will be released as SQL University: Volume I, Freshman Year which includes the first two semester’s worth of blog content! This is taking a lot of time to put together so bear with us as we get that worked on.
Another big change you may have noticed, and one of the most exciting parts about this new year, is our re-branding! Our new logo comes courtesy of the wonderful folks at Revealed Design Inc. (Facebook| Twitter) and a big thanks to Aaron Nelson (Blog | Twitter) for hooking me up with them. This is a much cleaner design and look than my atrocious attempt at designing a blogger badge from before. When you visit each professor’s sites this time around you should see the new badges displayed.
Finally the other huge addition this year is our partnership with SQLLunch.com to bring you the live lecture series. We had our first one featuring Josef Richberg during SSIS week. We’ll be bringing you more this semester with some big names so stay tuned! The best way to keep up to date on all the latest news and additions to SQL University is to join our newsletter.
One More Thing…
If you’re enjoying SQL University and learning from all of these great folks in the SQL community you’ll get a chance to experience all of this in person! This Spring at SQLRally we’ll be hosting a Lightning Talk session featuring the professors of SQLU as well as some other special surprise guests. SQLRally runs from May 11-13 in Orlando, Florida and only costs $299 and $199 for pre-conference sessions (optional). Hope to see you there!
During my presentation at SQLSaturday 62 in Tampa I was asked by an attendee about having a policy to check the setting for ad-hoc optimization settings. At the time since I was in a bit of a time crunch (and I couldn’t remember the exact facet to look under) I couldn’t properly demo how to check for it. In this post I’ll show you how to check for that specific setting. In a future post I’ll show you how to check on many more settings.
Before we begin, I highly recommend you familiarize yourself with what exactly this setting changes and how it affects your SQL Server environment. Remember this setting affects the entire instance so all databases installed here will be affected by this change. Read this great post by Bob Pusateri (Blog | Twitter) to get an understanding of what Optimizing for Ad Hoc Workloads really does.
Creating the Policy/Condition
In SQL Server Management Studio browse down to and expand your management node, expand the Policy-Based Management node, right-click the Policies folder and select New Policy.
Name your new policy and then from the Check Conditions drop down menu select New Condition.
Give your new condition a name and from the Facet drop down menu select the Server Configuration facet.
In the Expression editor, click the area below the column title of field and you will be presented with a drop-down of all the properties available for this facet. Select @OptimizeAdhocWorkloads.
Creating our new condition
Under the heading of Value, you will have two options: True or False. When you create a policy you want to establish a condition you want so for the purposes of this demonstration we want our servers to have this setting set to off (which is default setting) so we’ll select the option for FALSE. Click OK to create your condition and return to the new policy window.
Next we’ll select our Evaluation Mode. This policy, based on the facets and properties we’ve selected offer us three options: On demand, on schedule and On Change: log only. The last option, if enabled, will allow this policy to be active and log any changes made to this particular setting. One cool thing you can do with this is you can create alerts to automatically email you if this particular condition is violated. Check out Ken Simmons (Blog | Twitter) article on Configuring Alerts for Policy-Based Management to learn more. Leave the Evaluation Mode to On Demand and click OK.
Now that we have our policy created simply right-click on it (located under your Policies folder) and select Evaluate to try it out!
GUI? We Don’t Need No Stinking GUI!
In this post I walked you through how to create this policy using the GUI but if you prefer to script this out, you can do that too! Here is the T-SQL script that you can run in lieu of walking through the SSMS screens, to create this particular policy:
Declare @target_set_id int
EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N’Ad-hoc Workload Check_ObjectSet’, @type_skeleton=N’Server’, @type=N’SERVER’, @enabled=True, @target_set_id=@target_set_id OUTPUT
Select @target_set_id
GO
Declare @policy_id int
EXEC msdb.dbo.sp_syspolicy_add_policy @name=N’Ad-hoc Workload Check’, @condition_name=N’adhoc optimization check’, @policy_category=N”, @description=N’This policy checks the server setting to see if Optimize for Ad-Hoc Workload is enabled. The default setting is disabled.’, @help_text=N’To learn more about this policy check out Jorge Segarra”s blog post on this’, @help_link=N’https://sqlchicken.com/2011/01/policy-for-ad-hoc-workloads/’, @schedule_uid=N’00000000-0000-0000-0000-000000000000′, @execution_mode=0, @is_enabled=False, @policy_id=@policy_id OUTPUT, @root_condition_name=N”, @object_set=N’Ad-hoc Workload Check_ObjectSet’
Select @policy_id
GO[/code]
Conclusion
Again, I can’t iterate enough NOT to blindly go changing settings on your servers without understanding the effects of your actions! Policy-Based Management is a very powerful and easy-to-use tool but be sure to use it wisely! In a later post I will show you how to modify even more server-level settings and let you customize policies to check exactly the settings you want audit.
Enterprise Policy Management Framework, or EPMF, is completely free and was developed by the folks at Microsoft who created
policy-based management. I absolutely love telling folks about this project because it really helps sell the idea of
policy-based management’s application within an organization. What’s cool about this project is the built in reports make it easy to see the health state of your environment at a glance as well as let you drill down further in to each report piece to find more granular information on policy states.
One caveat of EPMF is that in order to run on SQL Server 2008 it requires SP1 Cumulative Update 3 or higher installed on your Central Management server in order to function properly. This requirement is in place in order for EPMF to be able to properly handle policy evaluation on down level systems (e.g. SQL Server 2000, 2005). An interesting question was asked during the presentation: “Does EPMF support SQL Server 2008 R2 RTM (10.50.1600)?” The answer is YES, it does!
I tested this on my local install of SQL Server 2008 R2 at RTM level and it works. Even though it works at RTM, I highly recommend you update your SQL Server 2008 R2 instance to at least Cumulative Update 3 or higher. I know, you’re thinking “but you just told me it works at RTM!” Yes, it does, however the RTM edition of R2 came with quite a nasty little bug that wasn’t fixed until the CU3 patch. This bug is outlined in this Connect issue by Aaron Bertrand (Blog | Twitter). The bug is that SSMS will not allow you to edit or create a job step after you’ve created an initial one. How does this affect you? Well when you setup EPMF you need to create a new scheduled job that executes the PowerShell script that evaluates the policies against your environment. This particular bug will stop you from editing or creating new job steps which could severely affect you trying to fix things. There is a workaround wherein you can close/reopen SSMS to make the error disappear but this can become quite cumbersome very quickly.
Policy-based management is an extremely powerful and easy to use feature in SQL Server 2008 and EPM Framework extends its awesomeness even further. If you’d like to learn more about Policy-based management you can check out some webinars I’ve done over at Pragmatic Works (webinar link) or at SQLLunch (webinar link) on the topic.
Welcome back students! We’re very excited to start up a new semester and SQLU is back and better than ever! This semester we’re lucky to have 7 MVP’s, 7 Women in Tech (most ever!) and a Microsoft Certified Master (MCM) in SQL Server presenting topics. In addition to our regular lessons we have the SQL Rockstar himself, Tom LaRock (Blog | Twitter), hosting weekly DBA Coaching lessons on his blog as well. Our staff is also hard at work putting together an e-book compilation of the first two semesters-worth of content. We’re calling this compilation SQL University Vol 1: Freshman Year. As soon as we finish putting it together we’ll announce it via the mailing list along with communications on Twitter (Follow us @sqluniversity). What mailing list you ask? Well if you want to make sure you get all the latest news and updates for SQLU please sign up for our mailing list here.
So if you know me via Twitter or my blog you probably know that as of about a month or two ago I joined the fantastic team at Pragmatic Works. This transition was rather significant for me as I would be moving from the world of administration to the development side of SQL Server in being a BI consultant. This series will be a kind of chronicle of my personal journey through the transition from a DBA (with a social media complex) to a BI developer in one of the top BI shops around.
So we started this show with the Pledge, got wowed with the Turn and finally we’ve come to the last step: the Prestige! During the last stage of our act we saw a glimpse of the magic of the Summit and we also saw a certain object near and dear to my heart disappear. So how did this all end? Let’s get to it.
As I’ve mentioned before this was my first Summit so I really had no idea what to expect going there. I’ve known from many others that have gone before me that there is ALWAYS something going on but you have no idea how true that is until you’re there! From the moment I touched down in Seattle there was something going on be it Brent’s Freecon session, the new attendee mixer, the Quiz Bowl, [insert any number of vendor’s name here] party/dinner/event, the various lunches provided, the WIT luncheon, the Microsoft appreciation party, the volunteer appreciation party, the infamous karaoke sessions, or just the random awesome networking opportunities you create for yourself by just meeting up with an expert at breakfast/lunch/dinner/coffee house. Now think about this for just a second. I just rattled off all of those events and that DOESN’T include pre or post con events OR the actual conference sessions itself!
So looks like we have another blog party on our hands, this time courtesy of Jen McCown (Blog | Twitter), one half of the MidnightDBA brand! This is such a great topic especially coming from a couple who have built up quite a successful brand for themselves in the community and continue to grow it. Check out both their entries (Jen | Sean) on this topic as they both have really interesting views on this stuff.
Branding is a funny thing. It can go one of two ways: you can create a brand that resonates with your target audience, makes you memorable, and any continued associations with your brand and your audience are positive and meaningful. Or you can create a brand very much slanted the other way and any interactions or associations with your brand can bring negative results and damage you personally or professionally.
I’m going to make a quick confession here and tell you that I not only have watched the show “What Not To Wear” on TLC but I thoroughly enjoyed said show. Why confess that? Well on the show the hosts Clinton Kelly and Stacy London often tell their fashion-challenged targets that they need to realize that they’re trying to sell themselves. It’s all about image. This may sound a bit shallow but hear me out folks. You really are the image you project and this includes how you dress, how you act and in the world of blogging and presenting this directly relates to the brand you’re trying to sell: you!
I mentioned in my last post that the Turn is the point in the act where the magician executes his illusion, leaving his audience captivated and in wonderment. Sometimes the magician will even make something disappear in to thin air. Funnily enough, this past week I had a little bit of everything I just mentioned!
Well, it’s the final day of PASS Summit and the week’s final keynote! Today’s keynote will be delivered by Dr. David Dewitt! His speaking is the result of a user survey sent out by PASS earlier this year.
You voted, and the results are in: Dr. David DeWitt will be speaking on Query Optimization 101 during the most anticipated keynote of PASS Summit 2010!
“In two short years, giving a keynote at the PASS Summit has become a highlight of my year,” noted Dr. DeWitt, Technical Fellow with Microsoft’s Jim Gray Systems Lab. “This year, I am excited to have the opportunity to talk about SQL query optimization, why it is hard to always produce good plans, and new technologies that offer the promise of better plans in future releases of SQL Server.”
Query optimization is the process of compiling a SQL query into an executable plan that is as efficient as possible. While the basic fundamentals for cost-based query optimization were first described in a seminal paper by Pat Selinger of IBM Research in 1979, optimizing complex queries continues to be very challenging 30 years later. In this talk, David DeWitt will describe the fundamentals of query optimization, why it remains a very hard problem today, and present several recent research results that promise to improve the quality of plans produced in the years to come.
David J. DeWitt is a Technical Fellow in the Microsoft Data and Storage Platform Division. DeWitt’s role is creating and leading the Microsoft Jim Gray Systems Lab, a new advanced development center in Madison, WI, for Microsoft, in association with the University of Wisconsin-Madison Computer Sciences Department. DeWitt came to Microsoft from the Computer Sciences Department at the University of Wisconsin, which he joined in September 1976 after receiving his Ph.D. from the University of Michigan. He served as department chair from July 1999 to July 2004 and held the title of John P. Morgridge Professor of Computer Sciences when he retired from the University of Wisconsin.
Much like the USA Network here in the States welcomes characters, I’d like to formally let the world know that BIxPress also welcomes folks, and this time it’s looking at you DBAs out there!
