Resource Governor – Quick tips

ByDr. SubraMANI Paramasivam

Resource Governor – Quick tips

Quick and useful tips on using Resource Governor in your SQL Server environment. I have selected randomly on some of the Resource Governor related T-SQL queries that might be useful in your work environment.
–to view the resource governor configuration
SELECT * FROM sys.dm_resource_governor_configuration;

GO

— to view the resource governor workload groups
SELECT * FROM sys.dm_resource_governor_workload_groups

GO

— to view the resource governor resource pools

SELECT * FROM sys.dm_resource_governor_resource_pools

GO

–to reset the accumulated statistics about the resource governor usage

ALTER RESOURCE GOVERNOR RESET STATISTICS;
GO

–if you have more than one resource governor classifier function, you can use below statement to apply another function to take effect

ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.AnotherClassifierFunction);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

–Code to Remove All Classifier Functions from the Resource Governor
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

About the Author

Dr. SubraMANI Paramasivam administrator

Dr.SubraMANI Paramasivam is a Data Platform MVP, Power BI Group Leader, International Speaker, Microsoft Certified Trainer and Principal Solutions Architect at DAGEOP Ltd, having 20+ years of DB, BI & Analytics experience. Dr.SubraMANI Paramasivam is a frequent speaker in SQLBITS, PASS Summit, PASS SQL Saturdays, SQLRelay, Data Platform Summit, MCT Summit, SQLServerGeeks Summit, INSIDESQL UK, Data Awareness Programmes, Data Day Events. He likes to enlighten the education & data and started his own journey with FREE Data Awareness Programme (www.dataap.org), in remote areas who are in need. Mani speaks a lot about Visualization, Data Science, R Analytics, Azure concepts, Performance Tuning, DB & SQL Server Architecture, DBA, Partitioning, Resource Governor, Disaster Recovery, High Availability, Development, T-SQL Programming and Microsoft certifications.

Comments Are Closed!!!