Resource Governor:

As the name implies, Resource governor is a very useful and powerful feature which is used to allocate and control available resources on the Server of your current workload and server capacity.
Beware, before you implement this feature in your environment. It needs lots of baselines, analysis.
Most of us, might have/definitely lead to a situation where there are either resource unavailability for the current work load on our SQL DB boxes.
From SQL Server 2008 onwards, new feature has been introduced named as RESOURCE GOVERNOR.
Using, this feature as we DBAs control /allow/manage available resources for a spefic time/type of workload.
For Ex:
Normally, we cannot instruct our SQL Server to use only specific amount of memory/CPU for a particular task right. So what is the solution to make our SQL Server to allow us to customize available resources based on our workload at various points?
When our CEO, runs a sales report which almost tweaks to 100% of CPU/Memory some time due to some other junk queries ran by a developer at a back end and report was not generated. It would cause lots and lots of issues for business right.
Using Resource Governor, You can specify SQL Server to use specific amount  of CPU/Memory. When there is a request from CEO User id which allocates 75% of resources to CEO request and restrict available resources to 25% for all other requests comes to this SQL Server which in turn process CEOs request on high Priority.

Note:

1. Resource Governor is only to control/manage resources efficiently for DB Engine and it cannot be used to manage any other SQL services.

2. It is not advisable to use Resource Governor if you have more than one SQL instance installed on that machine. Since it will allocate resources based on the availability of the resources to that particular SQL instance.

For Ex: In a Server, physical memory RAM is 10GB and we have two SQL instances named as INST1, INST2 installed on it. Each instance have been allocated 4 GB of RAM each. So if you allocate Memory 50% of memory for a workload, through Resource Governor for INST 1. Then allocated memory would be 2GB. Since it will take the max available memory from the instance level.

50% memory allocated by Resource Governor  =  50% memory / Max available memory

50% of 4 GB Memory = 2 GB

So, We need to be very careful. When using this resource governor in our environment.

Deadlock Query

Posted: June 26, 2014 in General

SELECT  L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction as IsUserTransaction,
AT.name as TransactionName,
CN.auth_scheme as AuthenticationMethod
FROM    sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
–WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id

High Level Differences between SQL High availability solutions

 

Description Log-Shipping Replication Database Mirroring
Latency >1min(Based on the transactional log backup size) Potentially as low as a few seconds Potentially < 1 min
Causes schema alterations to be made at the publisher No Plain Transactional – no; Updating subscribers – yes No
Causes schema alterations to be made at the subscriber No Possibly (see text) No
Requires schema properties No Primary keys are  required for transactional table articles(Only tables which have primary keys can be used for replication) No
Selection of individual objects possible(For ex. Tables, stored procedures, views) No Yes No
Subscriber database “protected”*Subscriber = DR database Yes No Yes
System data transferred Mostly No Yes
Can the subscriber server be used as reporting server? Can be used Yes *PossiblyIt can be achieved through creating a snapshot of the DR database
Supports automatic failover No No Yes
Ease of implementation Simple Moderate Moderate

Query to check the existence of the user on a current login domain.

Query:

EXEC xp_cmdshell ‘net user /domain username’

For 2000:

SELECT d.name,
ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
FROM sysaltfiles mf
INNER JOIN sysdatabases d ON d.dbid = mf.dbid
WHERE d.dbid > 4 — Skip system databases
GROUP BY d.name
ORDER BY d.name

For 2005 & above:

SELECT d.name,
ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
FROM sysaltfiles mf
INNER JOIN sys.databases d ON d.database_id= mf.database_id
WHERE d.database_id > 4 — Skip system databases
GROUP BY d.name
ORDER BY d.name

SQL Server stores history of all the database backup history forever. History of all the backup is stored in msdb database system tables. Following Stored Procedure can be executed with parameter which takes days of history to keep.

USE msdb;
GO
EXEC sp_delete_backuphistory @oldest_date = ’01/01/2014′;

How to find the Last Windows Server Boot Time

Posted: December 11, 2013 in Uncategorized

Windows 2003:

C:\>systeminfo | findstr /C:”System Up Time”

Windows 2008:

C:\>systeminfo | findstr /C:”System Boot Time”

Before you begin the renaming process, review the items below.

  • SQL Server does not support renaming servers involved in replication, except in the case of using log shipping with replication. The secondary server in log shipping can be renamed if the primary server is permanently lost.
  • When renaming a virtual server that is configured to use database mirroring, you must turn off database mirroring before the renaming operation, and then re-establish database mirroring with the new virtual server name. Metadata for database mirroring will not be updated automatically to reflect the new virtual server name.

SQL Server 2005, 2008, 2008 R2

  1. Using Cluster Administrator, change the SQL Network Name to the new name.
  2. Take the network name resource offline. This takes the SQL Server resource and other dependent resources offline as well.
  3. Bring the SQL Server resource back online.

After a virtual server has been renamed, any connections that used the old name must now connect using the new name.

To verify that the renaming operation has completed, select information from either @@servername or sys.servers. The @@servername function will return the new virtual server name, and the sys.servers table will show the new virtual server name. To verify that the failover process is working correctly with the new name, the user should also try to fail the SQL Server resource over to the other nodes.

To minimize network propagation delay of a virtual server renaming operation, use the following steps:

  • Issue the following commands from a command prompt on the server node:
    ipconfig /flushdns
    ipconfig /registerdns
    nbtstat –RR

For SQL 2012, Additionally registry settings also should be changed.

Sometimes, SQL Server may start consuming more than allocated MAX Memory setting. Reason for this is as follows:

The max server memory sets the size of the buffer pool (data cache, plan cache and a bunch of others). SQL also takes memory outside of the buffer pool (back on 32-bit that was called the MemToLeave)
That’s for backup buffers, CLR memory, linked server drivers, extended stored procs, thread stacks and a few other things. It’s not usually more than a few hundred MB but, especially if lots of CLR is used, it can become noticeably large.

How to Identify this memory pressure issue?

You can monitor this using “Windows Sysinternals tool – “VMMAP”

VMMap is a process virtual and physical memory analysis utility. It shows a breakdown of a process’s committed virtual memory types as well as the amount of physical memory (working set) assigned by the operating system to those types. Besides graphical representations of memory usage, VMMap also shows summary information and a detailed process memory map. Powerful filtering and refresh capabilities allow you to identify the sources of process memory usage and the memory cost of application features.

Besides flexible views for analyzing live processes, VMMap supports the export of data in multiple forms, including a native format that preserves all the information so that you can load back in. It also includes command-line options that enable scripting scenarios.

VMMap is the ideal tool for developers wanting to understand and optimize their application’s memory resource usage.

Steps to change the service account to new service account for SQL Services as follows:

  1. Create new login for this new service account on SQL Server instance with SYSADMIN Privileges for which service account is going to be changed.
  2. In Group policy (gpedit.msc). Please enable privilege to “Lock pages in memory” for this new service account.
  3. Go To

    Local Computer policy –> Windows settings –> security settings –> local policies –> user rights assignment –> “Lock pages in memory” –> add new service account to this. (Without adding this account SQL agent will not start)

  4. After enabling group policy. Please check the sp_configure settings as follows:
    sp_configure ‘show advanced options’, 1;
    GO
    RECONFIGURE;
    GO
    sp_configure ‘Agent XPs’, 1;
    GO
    RECONFIGURE
    GO

  5. After performing the above three steps in sequence.

Change the service account name and password for the SQL Services on services.msc.

If above three steps are not performed correctly. SQL Services and SQL Agent services will not start/restart.