posted 6/9/2011 12:36:14 AM by Amit Bansal - Views: [1427]
Hi Friends,
Resource Governor is a good feature giving tremendous power and flexibility to DBAs to control resource usage, primarily CPU & Memory. As of this writing, in SQL Server 2008 & R2, you can only configure CPU & Memory resources. The concept of Pools and workload groups needs to be understood & tested well before you implement Resource Governor in your Production environment. Failing to do so, you can wreak havoc !! The purpose of this post is not to go in to the details of Pools and Workload groups as that might require a long explanation with examples – and if you are really interested in knowing more, just drop a comment on this blog. In this post, I want to talk about enabling & disabling Resource Governor and trace flag 8040.
Resource Governor has to be enabled before it can be used. You can enable it using the GUI or using T-SQL.
GUI:
TSQL:
ALTER RESOURCE GOVERNOR RECONFIGURE
Similarly, you can disable Resource Governor using GUI and T-SQL:
ALTER RESOURCE GOVERNOR DISABLE
Now the interesting part is disabling it permanently (somewhat). As mentioned earlier that your junior DBA can wreak havoc if he enables Resource Governor without configuring it correctly. Why not use the trace flag 8040 and disable it permanently – which means that if you start your SQL Server instance with trace flag 8040; Resource Governor is always disabled :) – which means if this trace flag is used Resource Governor is always OFF and any attempt to enable/RECONFIGURE it fails.
If you liked this post, do like us on FaceBook at http://www.FaceBook.com/SQLServerGeeks
Regards, Amit Bansal
http://www.twitter.com/A_Bansal http://www.twitter.com/SQLServerGeeks http://www.amitbansal.net/ Visit my FaceBook page at http://www.facebook.com/AmitRSBansal Contribute on SQLServerGeeks.com: visit http://www.sqlservergeeks.com/default-category/write-for-us
Amit Bansal (Member since: 3/12/2011 4:59:54 PM) Follow Amit at Twitter @A_Bansal : Amit Bansal is the CTO of eDominer Systems & Peopleware India. He is a consultant, trainer, writer, speaker & evangelist on SQL Server & Business Intelligence. A seasoned speaker; he speaks at major summits and conferences of Microsoft worldwide including TechED, MCT Summits, MSDN & TechNet conferences. Over the last 8 years, he has consulted, trained & mentored more than 6000 IT professionals on SQL Server & Business Intelligence and worked with top notch blue chip companies worldwide including Microsoft, Infosys, Wipro, RBS, HCL, HP, Siemens, IBM, Accenture, etc. He has delivered more than 400 workshops on SQL Server & Business Intelligence. Apart from holding many Microsoft credentials, he is also a Microsoft Most Valuable Professional (MVP) awardee on SQL Server. He is also one of the first Microsoft Certified Trainer in India. Amit has worked with Microsoft in India and US as a Subject Matter Expert in various capacities participating in OD sessions, technical reviewing, etc. He is also a very active community lead in Asia Pacific. Visit www.amitbansal.net to know more. Subscribe to Amit’s blog and be a member of his technical forum at www.SQLServerGeeks.com - an exclusive SQL Server portal. Visit www.peoplewareindia.com for Corporate Training solutions. FaceBook – http://www.facebook.com/people/Amit-Bansal/525339346 LinkedIn – http://www.linkedin.com/pub/amit-bansal/7/121/755
View Amit Bansal 's profile
Great Tip Amit.
Thanks Sarab, you liked it !
Leave a comment