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

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. 

SQL Server Advanced Options

Part -II - Configurations - Management Studio and sp_configure

In my previous article you should have been familiar with the query sp_configure to see the default values of all the list

of 16 options. Let us see the Advanced options here.

By default the show advanced options will have 0 in its value. To see and set the advanced options this has to be changed

to 1 and it can be changed back to 0 once done. The query for changing the option is

sp_configure 'show advanced options', 1
go
reconfigure

This query will change the config_value option to 1 and reconfigure will send the command to reload all of the values. Once

the value is changed to 1 and by running sp_configure again will display an extended list of 68 in SQL Server 2008 and 70

in SQL Server 2008 R2.

I am going to take you through some of the most commonly used options.

AWE

Instances in SQL Server

 

Instance is nothing but SQL Server Installation. In SQL Server 7.0 only one installation was allowed and this changed from 2000 Version onwards. Default instance is nothing but connecting to SQL Server using the computer name. Where as the named instance such as Mani will have computername\mani. For example if the computer name is MyServer then the named instance would be MyServer\Mani. Now I can install many number of named instances and the maximum is based on the edition that you use. The list is provided below for each edition.

The port used by default instance is always 1433 and named instances use dynamic allocation.

The total number of instances including the default instance of SQL Server supported by all editions differ. The following gives a clear idea of the supported instances for each editions.

Enterprise - 50

Developer - 50

Standard - 50

Workgroup - 16

Express - 16

Note: Windows Failover Cluster supports only 25 instances.

Configurations - Management Studio and sp_configure

 

Comparing these two there are far more configuration options available using sp_configure. sp_configure is nothing but a query run in a query window in a Management studio. But the server properties can be changed using this query and here are some of the examples.

Once sp_configure query is executed the following 16 options are displayed with its default values. By changing these values the advanced configurations can be set up here.

I will show the detailed options in my next post for most of them. Till then good luck on your search on my blog.

SQL Server 2008 Tools & Components

 

For every installation of SQL Server we have to customize according to what has to be installed and for what purpose. The following are the list of components available in SQL Server.

1. Database Engine:
This is the core component which stores, manipulates & manage data. Other features include relational, XML, full text search and replication.

2. Integration Services:
Integration services is responsible for ETL Layer. ETL stands for Extraction, Transformation & Loading. This component is used for extracting the data from the source system, transform accordingly and load it into the database in the 1st component.

3. Analysis Services:
Once the data is available in 1st component via the ETL layer in the second component, not all the shcema is in ready to us and hence those are processed and managed by creating Online Analytical Processing (OLAP) analysis and data mining model for Business Intelligence purpose.

4. Reporting Services:
Whatever transformation is done in 2nd component & 3rd component we need an end user component to see the result and this is achieved as interactive, tables, graphical representations, charts through Reporting services.

The following are the available tools in SQL Server
1. SQL Server Management Studio (SSMS)
This is the management tool to manage all the core components which we saw above.

2. Business Intelligence Development Studio (BIDS)
This tool allows a developer to develop cubes (Analysis Services), integration service packages, reports.

3. SQL Server Configuration Manager
Managing the connectivity components such as Server Network Utility, Client Network Utility, Service Manager is done using this tool. All the connectivity components was separate features in 2005 version and backwards.

4. SQL Server Profiler
All the events occured in SQL Server can be traced and saved for future use using this tool.

5. Database Engine Tuning Advisor
Shortly called DTA is used too analyse the load on the engine. The recommendations provided by DTA can be used to improve the query performance for the same query.

6. Command Prompt Utilities
This is another powerful utility that can be accessed via DOS prompt. You can go to cmd.exe and execute the following commands that have its unique magic work done without the knowledge of T-SQL.

A. bcp - bulk copy of data

B. dta - Database Engine Tuning Advisor

C. dtexec - configure and execute SSIS packages

D. dtutil - Manage SSIS package

E. sqlcmd - command line to execute T-SQL statements

F. rs - Manage Reporting services server

G. rsconfig - manage & configure report server connection

H. rskeymgmt - manage encryption keys on report server

I. sqlagent90 - to start SQL Server Agent

J. SQLdiag - Diagnostic collection by Microsoft

K. sqllogship - Operational tasks like copy, restore, backup operations

L. sqlservr - start and stop the database engine for an instance

M. Ssms - to open SSMS

N. sqlps - to run PowerShell commands

O. tablediff - capable of comparing data from 2 tables in replication topology.

CDC - Change Data Capture

Quite useful for Auditing in finding the insert, update & delete in a database and this can be placed in another new table.

--TO activate the CDC
EXEC sys.sp_cdc_enable_db_change_data_capture

--To enable CDC on table
EXEC sys.sp_cdc_enable_table_change_data_capture @source_schema = 'dbo',
@source_name = 'myTable', @role_name = 'cdc'

This enables tracking down the changes and auditing much easier the way it was in the past. I really have to spend a lot of time on this in past, but this new feature is really cool.

Compressing Backup

Compressing the backup is first provided by 3rd party vendors such as redgate, Quest, etc., But this option is wide open in SQL Server 2008 onwards which has become more popular. I have personally used this and found the result is 60% of the compression is possible and leaving more empty space. Another good part is it takes very less time to compress the big backup files.

Once you have created a backup plan and compressing technique you will be able to view the T-SQL code that reflects the backup and compression process you have created. When you click on the "View T-SQL" Button that will produce the actual T-SQL code that the server executes. But there is always a warning message on this T-SQL Script screen "The T-SQL shown here may not necessarily be the exact T-SQL executed at the server due to any conditional logic you configured for this task."

BACKUP DATABASE [db] TO DISK = N'C:\Backup\db.bak' WITH
NOFORMAT, NOINIT, NAME = N'db-Full Database Backup', SKIP,
NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10

Knowing the GUI and T-SQL is always better for a day to day DBA job.

So, good to go and why don't you give a try.

Sparse Columns

This is a new feature available to support the columns with the NULL values. Any column having NULL values having sparse column enabled will decrease the file size but enabling this feature other than NULL value columns will increase the file size. So be cautious while enabling this option.

This option can be enabled using GUI or T-SQL code. By editing an existing table and select the Is Sparse option in the Column Properties which can be seen in the below screen.

The same can be achieved using the following T-SQL.

CREATE TABLE dbo.Table
(
Column 1 int NULL,
Column2 varchar(30) SPARSE NULL
) ON [PRIMARY]
GO

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.

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