Performance Tuning

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

ByDr. SubraMANI Paramasivam

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.

RG_Default_01 RG_Default_02

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.

RG_Default_03

 

 

ByDr. SubraMANI Paramasivam

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.

ResourceGovernor

ByDr. SubraMANI Paramasivam

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.

IssuesVsTools

ByDr. SubraMANI Paramasivam

System Resources

Below are the main system resources and are the key elements for better performing server.
CPU: We definitely require more than one CPU to perform multiprocessing and helps any program to be healthy enough with all I/O operations.
RAM: Even if you are not a DBA, you should be aware of the memory that is allocated for your SQL Server instances. Memory Sizing can be done based on the number of transactions that is going to hit the server. Any environment having high transaction should have highly allocated memory to process multiple operations in parallel.
Disk: Disk is the storage area where every single transaction stores and retrieves data from as part of the request from applications, users. Having good amount of understanding and knowledge on database & log files and their location, on these sort of storages is a must, even for non DBA’s. Monitoring and maintaining the data storage, RAID levels, etc., are some of the key activities for every single DBAs.
I/O:  Any Input and output to and from applications/users is calculated with the I/O level. As part of the user or application’s request the I/O part plays a major role and any performance issues will affect the time taken to retrieve the data.
SystemResources
ByDr. SubraMANI Paramasivam

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

EnableDisableRG

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

 

ByDr. SubraMANI Paramasivam

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.

ByDr. SubraMANI Paramasivam

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
12