The SQL UPDATE Statement

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 @object_set_id int
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'Ad-hoc Workload Check_ObjectSet', @facet=N'IServerConfigurationFacet', @object_set_id=@object_set_id OUTPUT
Select @object_set_id

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'http://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

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.

This is just a quick note to remind everyone I’ll be presenting tomorrow, Tuesday October 26th 2010 for the PragmaticWorks Free Monthly Webinar Series. The topic (big surprise here) is Policy-Based Management!

The webinar runs from 11:00 AM – 12:00 PM EDT and you can register for free here. Hope to see you all there!

Just a quick note that today at 12:30 EST (11:30 CST) I’ll be presenting on Policy-Based Management for SQLLunch. If you’re not familiar with SQLLunch it’s an awesome series of FREE webcasts setup by SQL MVP Patrick LeBlanc (Blog | Twitter). Each webcast focuses on a different aspect of SQL Server.

Free training? Can’t beat it, make sure to check it out! To find out latest news on SQLLunch and all upcoming webcasts register on their site for free. You can also join PASS (free) directly from their registration, Win/Win!

Info on today’s webcast:

SQLLunch #29: Policy-Based Management

Topic: #29-Policy-Based Management in a Nutshell
We will be learning an overview of this powerful new feature in SQL Server 2008 and how you can leverage it to help manage your existing SQL environment. This will include plenty of demos, best practices and Q&A so by the end you should be able to walk away ready to take control of your SQL Servers!

Hope to see you there!

After months of deadlines, late night writing sessions, getting my ass handed to me by ADD and editors, and a loving wife pushing me to write some more the book is now done! A HUGE thank you to Ken Simmons for inviting Colin Stasiuk and myself to be a part of this project. Also huge thanks to all the folks at Apress for doing a great job pushing this along as well as Tom LaRock, whose technical editing on this book was a great asset. According to Amazon, the book should be available April 27th so make sure you buy plenty of copies for you and your loved ones. After all, nothing says “I love you” like the gift of policies that stop developers from horrendous naming conventions Policy-Based Management. You can also get the book directly from Apress by going to http://www.policybasedmanagement.com.