May
7
2013

Overhead of using Policy-Based Management on SQL Server

I

Policy-Based Management is a very useful feature of SQL Server 2008 and later, if you like to audit certain parameters or enforce certain restriction on particular SQL Server/s, but first this to notice is will there be any overhead on current performance of SQL Server or not, basically the answer depends upon the way you are going to use it,

 

Q. What is the overhead of Policy-Based Management on my SQL Server?

A.  Unless the policy is currently being evaluated, there is no overhead on SQL Server. Two exceptions are there, “On Change: Prevent” and “On Change: Log Only” evaluation modes. These modes use DDL Triggers which execute to perform a check every time an object in the policy’s target set is alerted. This results in some overhead.

 

Ref. book: The Real MCTS SQL Server 2008 Exam 70-432 Prep Kit: Database Implementation and Maintenance (Pg. 298)

 

So the extract of what was said above is, if we enable the policies in preventive mode like someone should not go and modify any SQL parameter then only there will be an overhead for e.g. if someone trying to changing the recovery model of a database then the person will get denied error; and in case if policy is scheduled to run on predefined schedules or manually to audit certain settings then it is similar to fetch information from SQL Server which will not put much overhead and governed by the type of policy and parameters being audited.

 

Useful reference

http://sugeshkr.blogspot.ca/2009/01/policy-based-management-in-sql-server.html

Download PDF

About the Author: Nitin G

Indian born, trekker, biker, photographer, lover of monsoons. I've been working full time with SQL Server since year 2005 and blogs to post the content aquired during my research on new topics or fixing issues faced by me as a DBA while working in different kind of projects, hope some of my posts may helps others in SQLDBA community. Everything you read on my blog is my own personal opinion and any code is provided "AS-IS" with no warranties!

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 23 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

November 2017
M T W T F S S
« Oct    
 12345
6789101112
13141516171819
20212223242526
27282930  

View Post by Categories

%d bloggers like this: