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
Resource Governor – Default settings
How does the resource governor default settings look like? Below is a simple screenshots taken from SSMS and also from the resource governor properties.
How does the resource governor would look like after configuring a successful resource governor? This can be seen from the below screenshot, which shows four resource pools and workload groups configuration, which has dedicated lanes for Applications, Reporting systems, Developers and Business users.
Tiny bits about Resource Governor and of course my favorite
Resource Governor helps sorry saves DBA’s life in terms of performance improvement by deriving proper lanes on what applications or which service accounts should have high horse power when it comes to CPU & I/O usage. This feature was introduced in SQL Server 2008 and made available in Enterprise, Developer and Evaluation editions only. This has got the ability to classify connections and route the workload to specific already derived lanes by DBA as per the business requirements. This is limited to SQL Server Engine only and self contained and instant specific only. This has got 3 main below concepts.
- Resource Pool
- Represents physical resource
- Workload Group
- Categorizes the sessions and routes to relevant resource pool
- Classification
- A function to decide the sessions to allocate the relevant group
Below picture explains the available system views, DMVs, Performance counters that are specific to Resource Governor only.
SQL Server Issues and the available tools
Below is the cheat chart, which will give you a quick glimpse on most of the possible issues, that you face in your day to day SQL Server life. This chart also provides you with available SQL Server Admin tools from left to right in the top, to tackle the issues that are relevant to your problems. This also explains a good understanding on available SQL Server admin tools that can be used to tackle problems that you may be facing in your work environment. At least now you know where to start if you hit with any performance or other issues.
System Resources
How to enable/disable Resource Governor
A simple screenshot below shows you how you can easily enable/disable resource governor. By using SQL Server Management studio, connect to the instance where you need to enable Resource Governor. Then navigate down to Management and expand to see resource governor. There you can simply right click and choose Enable from the pop up menu. You can also enable by using T-SQL by simply passing below command.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
To disable via GUI, using your SSMS navigate to the management again and then right click on Resource Governor and choose Disable from the pop-up box. Alternatively, you can pass below T-SQL command to disable.
ALTER RESOURCE GOVERNOR DISABLE;
GO
SQL Server Memory Issue
Every one running SQL Server has always had the same issue of memory. Whatever the extra memory is given to windows, it is all eaten by the SQL Server alone. No matter what the process is going on, the memory takes much resources for all related to SQL Server. How can we control the memory issue. Well there are some possibilities from SQL Server that we can restrict the server to occupy the server memory.
–To check for the existing memory usage
use master
EXEC sp_configure ‘max server memory (MB)’
By default the server won’t display the advance options to enable this service. So follow the steps below to view the memory usage.
–To display advance option
USE master
EXEC sp_configure ‘show advanced options’, 1 RECONFIGURE WITH OVERRIDE
After this you can run the below query to see the usage.
use master
EXEC sp_configure ‘max server memory (MB)’
Then use this to change the server memory.
–To change the maximum server memory
USE master
EXEC sp_configure ‘max server memory (MB)’, 6144 RECONFIGURE WITH OVERRIDE
Then again you can check the usage using the query which we have see earlier.
Microsoft BI – New Features
Top New Features Create high-performance Analysis Services solutions with optimized cube designers, subspace computation, and MOLAP-enabled writeback capabilities Implement enterprise-scale Reporting Services solutions through new on-demand processing and instance-based rendering Build flexible and effective reports with the new Tablix data structure and rich formatting capabilities Expand reach, and empower more users through optimized integration with the 2007 Microsoft Office system.
Boost data warehouse performance
- Integrate large volumes of data into your data warehouse faster by using SQL Server 2008 Integration Services, and consolidate real-time data by capturing data changes
- Increase the manageability and performance of large tables with partitioning, which enables you to manage growing volumes of data and users of your SQL Server 2008 data warehouse more efficiently
Consolidate all data for optimized reporting and analysis
- Work with all of your data and interact the way you want through support for both relational and non-relational data, including new data types such as FILESTREAM & Spatial
- Incorporate data from multiple sources directly into a single report
- Use the data source view to integrate data from across the enterprise and create a Unified Dimension Model that consolidates data from heterogeneous, enterprise-wide data stores, such as SQL Server, Oracle, DB2, SAP and Teradata, to create a holistic view of your business, helping end users gain enterprise-wide insight