BLOGS

ByDr. SubraMANI Paramasivam

Resource Governor for all users in a group

It was a hard part initially to make use of balancing the resources for all users with different groups. We can make use of IS_MEMBER() function, but the Function Classifier need to be derived accordingly. Also this is not an 100% workout to get the ideal result. I have recently come out with good work around to sort out this issue.


To give a scenario, I have 3 sets of SQL users. Moreover I also have replication stuff going on.
1. Business Team with developers knowledge
2. Business team with basic knowledge + pivot table manipulators in excel
3. IT team

I need to balance the users login on one side & the replication on the other.

I have used 3 Workload groups & Resouce pools on top of the server’s default & internal pools.

–**CREATE RESOURCE POOL **–
CREATE RESOURCE POOL SSUsers
WITH (MAX_CPU_PERCENT = 2, MAX_MEMORY_PERCENT = 2);
go

CREATE RESOURCE POOL DevUsers
WITH (MAX_CPU_PERCENT = 4, MAX_MEMORY_PERCENT = 4);
go

CREATE RESOURCE POOL Distribution
WITH (MIN_CPU_PERCENT = 15, MAX_CPU_PERCENT = 40, MIN_MEMORY_PERCENT = 40, MAX_MEMORY_PERCENT = 60);
go

–to reconfigure the created resource pools, i used the following
ALTER RESOURCE GOVERNOR RECONFIGURE;
go

–the below query displays the newly created resource pools with its ID.
SELECT * FROM sys.dm_resource_governor_resource_pools;
go

–**CREATE WORKLOAD GROUP **–
–create workload group to assign to the resource pools

CREATE WORKLOAD GROUP WG_SSUsers
USING SSUsers;
go

CREATE WORKLOAD GROUP WG_DevUsers
USING DevUsers;
go

CREATE WORKLOAD GROUP WG_Distribution
USING distribution;
GO

–to reconfigure the created resource pools, i used the following
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

–the below query displays the newly created workload groups with its ID.
SELECT * FROM sys.dm_resource_governor_workload_groups
I am going to write a classifier function that will distinguish the users and the replication database and allocated the workload groups accordingly.

Before creating the classifier function I need to have all the list of users underneath their group level. In our case, I had issue in using IS_MEMBER() function where it was able to allocate resource for the first set of rules defined in the classifier function. Then I came out with adding the list of user to a physical table with their permissions level in the master database. You can execute the following against your master database.

Use master
go

CREATE TABLE RG_Users_Group
(
RG_Name NVARCHAR(15) NULL,
Username NVARCHAR(25) NULL

)

INSERT INTO rg_users_group VALUES (‘SS_Users’, ‘AB01\user1′)
INSERT INTO rg_users_group VALUES (‘SS_Users’, ‘AB01\user2′)
INSERT INTO rg_users_group VALUES (‘SS_Users’, ‘AB01\user3′)
INSERT INTO rg_users_group VALUES (‘SS_Users’, ‘AB01\user4′)
INSERT INTO rg_users_group VALUES (‘Dev_Users’, ‘AB01\user5′)
INSERT INTO rg_users_group VALUES (‘Dev_Users’, ‘AB01\user6′)
This creates a list of all users with proper Resource Group name. The output of the table looks like this.
RG_Name Username
——————————
SS_Users AB01\user1
SS_Users AB01\user2
SS_Users AB01\user3
SS_Users AB01\user4
Dev_Users AB01\user5
Dev_Users AB01\user6
Once the table is created then I write my classifier function here.

–create the classifier function to assign the workload groups
IF OBJECT_ID ( ‘dbo.RGov_Classifier’) IS NOT NULL
DROP FUNCTION dbo.RGov_Classifier;
go
CREATE FUNCTION dbo.RGov_Classifier ()
RETURNS sysname WITH SCHEMABINDING
AS
BEGIN
RETURN
(
SELECT CASE WHEN SUSER_NAME() IN (SELECT username FROM dbo.rg_users_group WHERE RG_name = ‘SS_Users’)
THEN N’WG_SSUsers’
WHEN SUSER_NAME() IN (SELECT username FROM dbo.rg_users_group WHERE RG_name = ‘Dev_Users’)
THEN N’WG_DevUsers’
WHEN ORIGINAL_DB_NAME () = ‘distribution’
THEN N’WG_Distribution’
ELSE N’default’
END
);
END;
go
–register the classifier function to the RG.

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

Now the creation of all resource pools, workload groups, table & classifier function is done. It is time to check how the resource group works. So I have the following query to check each resource pools & their average response in milliseconds.
USE MASTER;
GO
SELECT
RP.name as PoolName,
COALESCE(SUM(RG.total_request_count), 0)as Total_Request,
COALESCE(SUM(RG.total_cpu_usage_ms), 0)as Total_CPU_in_MS,
CASE
WHEN SUM(RG.total_request_count) > 0 THEN
SUM(RG.total_cpu_usage_ms) /SUM(RG.total_request_count)
ELSE
0
END as Avg_CPU_in_MS
FROM
sys.dm_resource_governor_resource_pools AS RP
LEFT OUTER JOIN
sys.dm_resource_governor_workload_groups AS RG
ON
RP.pool_id = RG.pool_id
GROUP BY
RP.name;

In the above query, I have used the DMV (Dynamic Management Views) to help in identifying the resources used for each pool.

PoolName Total_Request Total_CPU_in_MS Avg_CPU_in_MS
DevUsers 3644 13412519 3680
SSUsers 50 243846 4876
distribution 1340523 16428325 12
default 621057 822676774 1324
internal 0 3182271 0

In the above scenario, I made sure all the resource pool now works fine.

This is not an 100% solution, and still have some work around to better up the results. But still a good work around compared to the other scenario.

Let me know if you need any clarifications on this.

ByDr. SubraMANI Paramasivam

OLAP Intro

Business intelligence (BI) is a broad category of application programs and technologies for gathering, storing, analyzing, and providing access to data to help enterprise users make better business decisions. BI applications include the activities of decision support, query and reporting, online analytical processing (OLAP), statistical analysis, forecasting, and data mining. In particular to Microsoft, it supports 3 types of OLAP technology, they are

1. MOLAP: Multidimensional OLAP

2. ROLAP : Relational OLAP

3. HOLAP: Hybrid OLAP

I shall post these definitions in my further posts.

ByDr. SubraMANI Paramasivam

MOLAP, ROLAP, HOLAP

As promised in my previous post please find the definitions, PLUS some more information on the characteristic nature of the post title.

MOLAP

This is the traditional mode in OLAP analysis. In MOLAP data is stored in form of multidimensional cubes and not in relational databases. The advantages of this mode is that it provides excellent query performance and the cubes are built for fast data retrieval. All calculations are pre-generated when the cube is created and can be easily applied while querying data. The disadvantages of this model are that it can handle only a limited amount of data. Since all calculations have been pre-built when the cube was created, the cube cannot be derived from a large volume of data. This deficiency can be bypassed by including only summary level calculations while constructing the cube. This model also requires huge additional investment as cube technology is proprietary and the knowledge base may not exist in the organization.

ROLAP

The underlying data in this model is stored in relational databases. Since the data is stored in relational databases this model gives the appearance of traditional OLAP’s slicing and dicing functionality. The advantages of this model is it can handle a large amount of data and can leverage all the functionalities of the relational database. The disadvantages are that the performance is slow and each ROLAP report is an SQL query with all the limitations of the genre. It is also limited by SQL functionalities. ROLAP vendors have tried to mitigate this problem by building into the tool out-of-the-box complex functions as well as providing the users with an ability to define their own functions.

HOLAP

HOLAP technology tries to combine the strengths of the above two models. For summary type information HOLAP leverages cube technology and for drilling down into details it uses the ROLAP model.

Comparison

Comparing the use of MOLAP, HOLAP and ROLAP

The type of storage medium impacts on cube processing time, cube storage and cube browsing speed. Some of the factors that affect MOLAP storage are:

Cube browsing is the fastest when using MOLAP. This is so even in cases where no aggregations have been done. The data is stored in a compressed multidimensional format and can be accessed quickly than in the relational database. Browsing is very slow in ROLAP about the same in HOLAP. Processing time is slower in ROLAP, especially at higher levels of aggregation.

MOLAP storage takes up more space than HOLAP as data is copied and at very low levels of aggregation it takes up more room than ROLAP. ROLAP takes almost no storage space as data is not duplicated. However ROALP aggregations take up more space than MOLAP or HOLAP aggregations.

All data is stored in the cube in MOLAP and data can be viewed even when the original data source is not available. In ROLAP data cannot be viewed unless connected to the data source.

MOLAP can handle very limited data only as all data is stored in the cube.

ByDr. SubraMANI Paramasivam

MICROSOFT SQL SERVER

  • Business Intelligence
  • Database Development
  • Database Administration
  • SQL Server Administration & Management
  • SSIS / SSAS / SSRS
  • Reporting & Dashboards
  • Performance Tuning
  • High Availability
  • Custom solutions
ByDr. SubraMANI Paramasivam

Microsoft Business Intelligence – available in BETA!

Finally, what we’ve all been waiting for, today the entire next wave of Microsoft Business Intelligence is available in Beta. Grab yourself a copy of Microsoft Office 2010 beta, Microsoft SharePoint Server 2010 beta, and SQL Server 2008 R2 CTP3 and try out the exciting new BI capabilities from Microsoft.

  • The new Business Intelligence capabilities in Excel 2010. Connect to an Analysis Services cube and try the new Slicers, Sparklines, Custom Sets, Write-back and more…
  • Explore the power of PowerPivot for Excel and PowerPivot for SharePoint add-ins and pull together data from across your organization or from outside the firewall, build analytic reports and share them with colleagues through SharePoint. If you’re an IT Pro don’t forget to check out the PowerPivot dashboards that give you visibility and insight into what PowerPivot applications are being built across organization.
  • Explore what SharePoint Server 2010 has to offer with PerformancePoint Services, Visio Services, and Excel Services and explore how the SharePoint platform makes the BI experience much more collaborative through tags, notes, wiki’s, etc…

Take a look at the new BI capabilities available with SQL Server in Microsoft website with Master Data Services, the new Report Builder, and more…

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
ByDr. SubraMANI Paramasivam

SQL Server 2008 New Features

With SQL Server 2008 version there are quite a number of new features are added in. Those are listed below. (I know this is pretty old update, but new when SQL 2008 was released).

  1. Installation of SQL Server 2008 with more options
  2. Compressed Backups
  3. Enhanced configuration
  4. Management of Audits with CDC (Change Data Capture)
  5. New Table Value Parameter
  6. Filestream Data types
  7. Sparse Columns
  8. Encryption Enhancements
  9. High Availability
  10. Performance
    1. Performance Data Management
    2. Resource Governor
    3. Freeze Plan
  11. Declarative Management Framework (DMF)
  12. Development Improvements
    1. LINQ Supports
    2. MERGE Statement
  13. Spatial Data type
  14. Analysis Services Improvements
  15. ETL Enhancements
  16. Few on Reporting Services
    1. No longer use of IIS
    2. Better Graphing
    3. Export to Word Support
  17. Some of the Deprecated & Discontinued Features
ByDr. SubraMANI Paramasivam

Memory upgrade can lead to performance issues

I had a live scenario where my servers were upgraded with the memory and all my ETL & Replication were totally down. Couldn’t figure out the actual issue and need to reset the whole process and run manually. I have about 6TB of database and 4Bn records. My initial workout with the admin guy is to find out the server problem itself and then came out with the server is kind of parallelized with the faulty motherboard. I have 2 processor and equal amount of RAM supplied to both of them. When the memory was upgraded equally one part of the processor couldn’t recognize the memory and found the faulty motherboard. One side it was good to find out the actual memory in place helped to identify and replace the faulty one. But on the other side the production system was totally disastrous. Then need to configure with the resource governor to distinguish the workload to help more for replication and also the default one. After the resource governor was setup properly, the following query produced the results to show average time take per millisecond.

SELECT
rpool.name as PoolName,
COALESCE(SUM(rgroup.total_request_count), 0)as TotalRequest,
COALESCE(SUM(rgroup.total_cpu_usage_ms), 0)as TotalCPUinMS,
CASE
WHEN SUM(rgroup.total_request_count) > 0 THEN
SUM(rgroup.total_cpu_usage_ms) /SUM(rgroup.total_request_count)
ELSE
0
END as AvgCPUinMS
FROM sys.dm_resource_governor_resource_pools AS rpool LEFT OUTER JOIN
sys.dm_resource_governor_workload_groups AS rgroup
ON rpool.pool_id = rgroup.pool_id
GROUP BY rpool.name
GO

Result:

PoolName TotalRequest TotalCPUinMS AvgCPUinMS
——————————————————————————–
PP_BusUsers 0 0 0
PP_SSUsers 0 0 0
distribution 2613809 30510900 11
default 5730860 179191854 31
internal 0 4966873 0