Policy for Ad-hoc Workloads

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:

[code lang=”sql” wraplines=”true”]
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[/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.

Share

EPM Framework and SQL 2008 R2

This weekend at SQLSaturday 62 in Tampa, I presented my policy-based management presentation. During my presentation
one of the cool things I cover is how policy-based management can be extended utilizing Reporting Services and PowerShell
through the use of an amazing tool called the Enterprise Policy-Management Framework available on Codeplex.

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.
Share

SQL University: Spring 2011

The new SQL University Logo

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.

Continue reading

Share

SQLSaturday #62: Tampa

We’re 10 short days away from SQLSaturday #62 event and I just wanted to remind everyone about some of the amazing stuff that will be happening that weekend!

First off we have an incredible deal with a pre-con we call Day of Data. We have two all-day training options for you at the incredible price of $99 (after today 1/5, price jumps to $109)! For the DBAs we have Denny Cherry (Blog | Twitter) presenting Storage and Virtualization for the DBA.  For the BI focus We have Stacia Misner (Blog | Twitter) presenting a Day of BI. This price includes coffee, juice and donuts, lunch, and course materials. To register click here and make sure to share this with co-workers and your boss! I guarantee the ROI on this training will be off the charts!

As for the main event we have an AMAZING lineup of speakers for this free (yes, I said FREE) training event. Check out the schedule (time/rooms subject to change):

Cafeteria Room A Room B Room C
8:30
– 9:30
Introduction to SSIS Efficient Datawarehouse Design How SQL saved my Business Intelligence Platform DBA Repository Update 2010 Using SSIS and SSRS
9:45
– 10:45
SSIS Cafeteria DBA 101 Developing Date and Role-Playing Dimensions Implementing auditing in SQL Server
11:00
– 12:00
Cool Tricks to Pull from your SSIS Hat: Why I Use Stored Procedures Introduction to PowerPivot for Excel SQL Server Auditing 101
12:15
– 1:15
Accelerating BI Development with BI xPress
1:30
– 2:30
Do You Know the Data Flow? Zen and the Art of Writing SQL Query Indexing for performance Reporting Services 2008
2:45
– 3:45
SQL Smackdown: SSIS vs. PowerShell Page And Row Compression How, When, and Why SQL Server 2008 R2 Parallel Data Warehouse Revive the code: refactoring for performance
4:00
– 5:00
Iron Chef SQL Server Troubleshooting with the SQL Server 2008 DC & MDW Bad SQL SSIS and SSRS Better Together
More rooms below – keep scrolling
Room D Room E Room F Cantina
8:30
– 9:30
Why Learn PowerShell? Policy-Based Management in a Nutshell To click or to type, that is the question
9:45
– 10:45
SQL Server PowerShell Extensions (SQLPSX) Become a Bilingual DBA! Oracle for the SQL Server Sql Server Service Broker – An Overview
11:00
– 12:00
Windows PowerShell 2.0 Best Practices for DBA’s Introduction to Transactional Replication ITIL V3 for the Database Administrator
12:15
– 1:15
Lunch is served
1:30
– 2:30
You inherited a database Now What? MDX 201 Find Performance Problems by Reading the Waits WIT Discussion
2:45
– 3:45
Where should I be encrypting my data SQL Server Memory Deep Dive Spatial Data in SQL 2008 and Bing
4:00
– 5:00
DR Availability,You’re Wanted in the Recovery Room SSIS Data Flow Buffer Breakdown Creating a Metadata Mart w/ SSIS – Data


And I guess it’s worth mentioning I’ll be there presenting my Policy-Based Management in a Nutshell talk so if you come to the event swing by and say hi (even stay for my session if you’d like!). So grab yo kids, grab yo wife, grab yo coworkers and get to SQLSaturday cuz everyone’s learning up in there!*

*I apologize for the horrendous addition to an internet meme to my post

Share