Capture deadlock with Extended Events

SQL Script:

ADD EVENT sqlserver.xml_deadlock_report(
WHERE ([sqlserver].[database_name]=N’BizTalkDTADb’))
ADD TARGET package0.ring_buffer


SQL Audit logs shows wrong time on the Events

I have implemented SQL audit for  SQL Server 2012 one one cluster instance.

When i view audits logs, wondering why does it display wrong time.i goggled and found this is a known bug.

SQL server audits are logged with UTC times.

Workaround for this issues is to use the below queries to get the correct result.Auditlog_incorrect time stamp

as corrected_time,
action_id ,
server_instance_name ,
database_name ,
schema_name ,
object_name ,
statement ,
file_name FROM sys.fn_get_audit_file( ‘F:\Audit\*.sqlaudit’ , DEFAULT , DEFAULT)

Additional info :

Continue reading

Installation mistake : SQL Server on mount points:

The other day my colleague told that he is facing permission error while doing the installation. On checking found below error. It is a common mistake that we do make assuming the root of the mount point as a folder 🙂 .


This issue occurs when you are trying to install SQL Server to the root of the mount point. Avoid that, instead create and install into a folder under the mount point to allow the correct propagation of permissions. The permissions that are applied to the root drive file system will not be propagating to the mount point file systems, they are controlled separately.

Another option is to add sql service account which we are using for installation as owner of the ‘’ (under system volume information/advanced security settings) of the mentioned mount point in the error. But, it’s a time consuming process and changing system configurations is bit risky at a later stage. It’s always better to create a folder under the mount point for smooth installation.

SQL Server Index

The most important factor in Sql Server to achieve good query performance is indexing.

The index provides a fast way to look up data based on the values within those columns.For example, if you create an index on the primary key and then search for a row of data based on one of the primary key values, SQL Server first finds that value in the index, and then uses the index to quickly locate the entire row of data. Without the index, a table scan would have to be performed in order to locate the row, which can have a significant effect on performance.

Note: PRIMARY KEY constraints create clustered indexes automatically if no clustered index already exists on the table and a non-clustered index is not specified when you create the PRIMARY KEY constraint.


An index is made up of set of pages (index nodes) that are organized in a B-tree structure.

When a query fires against an index column, the query engine starts at the root node and navigates down through the intermediate nodes. It continues down through the index nodes until it reaches the leaf node.

For example if you are searching for the value 123 in an index column, query engine would look for the root node to determine which page to reference in the top intermediate level. In this example the first page points the value 1-100 and second point to value 101-200, so the query engine would go to the second page on that level. From there, the query engine would determine to go the next intermediate level. From there query engine would navigate to the leaf node to for value 123.

The leaf node will contain either the entire row of data or a pointer to that row, depending on whether the index is clustered or non-clustered.

Clustered Index:

Cluster index

A cluster index store the actual row of data on it its leaf node. According to the above example, that would mean that entire row of data associated with the key value 123 is found on its leaf node.

  • When a cluster index defined on a table, it tells the Sql Server how to order the table data pages. It organizes the data pages into a B-tree structure where root pages at the highest level and leaf pages at the lowest level.
  • Index pages in the root and intermediate level contain the cluster key value and a page pointer down into the next level of the B-tree. This pattern continues till it reaches the leaf page. Leaf level of the cluster index is where the actual data stored.
  • In cluster index, index values are sorted either on ascending or descending order.

Non-clustered Index:

It’s impossible to physically arrange same data in 2 different ways without having the separate structure that store   information. This is where non clustered index comes into picture.

Non-clustered index tree structure is same as the cluster index where instead of having the base data at the bottom or leaf level of tree, we have set up pointer or references back to the base table data. With this type of structure we can use any index key order we want because orders are independent of the base table data. This entire structure called non-cluster index.

  • Index pages in the root and intermediate level contain the key value and a page pointer down into the next level of the B-tree. This pattern continues till it reaches the leaf page.
  • Non-cluster index leaf pages store both the index key value AND a pointer to locate the actual data rows in table where actually data stored.
  • Whereas in the clustered index the leaf level contains the actual data rows, in a non-clustered index, the leaf level contains the clustered key or Row ID (RID), which SQL Server uses to find the rest of the data.

Let’s says we have defined a non-clustered index on the EmployeeID column in Employee table, if we are running a query to retrieve data (empname, empaddress, etc) for an EmployeeID 18, it uses the cluster index to retrieve other column data since actual data stored in the leaf level of cluster index.

Non-Cluster IndexSummary of clustered and non-clustered index:

  1. A table can have multiple non-clustered indexes where as a table can have only one clustered index.
  2. Non clustered index store both a value and a pointer to the actual row that holds that value. Cluster index actually stores the row- level data in its leaf nodes.
  3. Cluster index speeds up the data retrieval since physical data stored on the disk is sorted in the same order as the index. A non-clustered has no effect on which order of the rows will be stored.
  4. Frequent updates on cluster index column cause performance issues.
  5. Non-clustered index is the optimal choice for exact match queries by using the cluster key value or row ID (RID) depending upon the underlying table order.
  6. Non-clustered index is considered for queries that do not return large result sets.
  7. Use of covering index eliminates accessing the table or cluster index together.
  8. It is important to define the cluster index key with as few columns as possible because the non-cluster index entries contain the clustering key.
  9. Cluster index considered for queries that return a range of values using operator such as BETWEEN,>,>=, < and <=.
  10. Considered for column that are accessed sequentially and queries that return large result set.
  11. Cluster index eliminates the need for Sql Server to sort the data when there is GROUP BY and ORDER BY clause in a query. Since data is already sorted in a cluster index hence improve query performance.

Install SQL Server 2012 Cluster on a Windows Server 2012 -Part 1

Prerequisite for SQL Server Cluster Installation

 SQL Server 2012 – Run Failover Clustering Validation tool.
 Two instances of SQL Server or 1 and if 2 then both should have different cluster group.
 .NET Framework 3.5 with Service Pack 1 & Windows Installer 4.5.
 The nodes for the cluster and their ip addresses and access to both nodes.
 OS version & Edition – (Windows Server 2008 R2)
 SQL Server version & edition – (Enterprise or Standard)
 Verify Storage SAN/iSCSI
 Configure the quorum disk
 Create a Local Disk for Page File
 Verify that anti-virus software is not installed on your MSCS cluster
 Verify that you have disabled NetBIOS for all private network cards before beginning SQL Server Setup
 Remote Administration must be enabled
 The SQL Server installation product key.
 The SQL Server components to be installed.
 The SQL Server virtual server name.
 Default or named instance?
 The shared disks to select.
 The SQL Server virtual IP address.
 The subnet mask.
 The dedicated domain group for the SQL Server services and dedicated domain account to be added in admin group for running SQL server Engine and SQL Server Agent services.
 The dedicated domain accounts for each of the services if needed, to be installed (SSAS, SSIS, and/or SSRS).
 The SQL Server Collation to select mostly default.
 Windows or Mixed authentication mode
 The list of users to be the SQL Server administrator for the instance.
 The default locations for the data and log files for the user databases and tempdb and also the location for the backup directory.
 Create Domain Group with SQL Admin permissions and use that group for cluster security and management.
 MSDTC is to be installed prior to install sql server clustering with dedicated MSDTC drive and IP address.
 Cluster nodes should not be a part of domain controller.
Infrastructure Details:
 Ethernet LAN –
 Primary iSCSI LAN –
 Secondary iSCSI LAN –
 Heartbeat LAN –

 Node 1 LAN –
 Node 1 iSCSI1 –
 Node 1 iSCSI2 –
 Node 1 Heartbeat –

 SQL Node 2 LAN –
 SQL Node 2 iSCSI1 –
 SQL Node 2 iSCSI2 –
 SQL Node 2 Heartbeat –

 SQL Server:
 Authentication Mode – Mixed
 Database mode – SQL 2012 Compatible
 Management Cluster Name: SQLCLSTADMIN
 SQL Management Cluster IP –

 Virtual Cluster Name –
 Virtual Cluster IP –
 SQLCLST Cluster IP –

SQL Server Accounts Info
 Use sql Domain group
 SQL Database Engine domain group ID – Domain\sqladmin
 SQL server Agent domain group ID – Domain\sqlagent
Drive Usage :-
Data – L-Drive

Transaction Log:
Log file: P Drive
Data – N-Drive
Backup Drive:
Backup: G:\
Windows Server 2012 Validate Cluster Configuration
Word of caution: do not skip any error message that this wizard generates in the final report. Doing so would simply mean that our configuration going forward will be unsupported. We only need to run this wizard on either of the nodes.
To run the Validate Cluster Configuration Wizard:
1. Open the Failover Cluster Management console
2. Under the Management section, click the Validate a Configuration link. This will run the Validate a Configuration Wizard
Cluster Validation1
3. In the Select Servers or a Cluster dialog box, enter the host names of the nodes that you want to add as members of your cluster and click Next.

Cluster Validation2

4. In the Testing Options dialog box, click Next to run all the necessary tests to validate whether or not the nodes are OK for clustering. If this is the first time to run the wizard, we must run all the tests for validation. For succeeding runs, especially when adding hardware like disk subsystems ot network cards on your cluster nodes, we can selectively choose which tests to run as long as we have initially validated our hardware by running all tests.

Cluster Validation3

5. In the Confirmation dialog box, click next. This will run all the necessary validation tests.
In the Summary dialog box, verify that all the report returns successful.

Cluster Validation 4

Cluster Validation 5

Fail over Cluster Resource dependencies in sql server
When you install Microsoft SQL Server on a cluster as a SQL Server fail over cluster instance, a specific set of SQL Server resources that have dependencies on other resources in the cluster group are created.
Dependency Report:
Step 1: Right click on sql server  more actions Show Dependency Report


Depedency report

SQL Server Upgrade

Prerequisite for upgrading SQL Server from one version to another for a smooth upgrade process.

Step 1: We should be aware of the Microsoft supported upgraded paths.

Example: SQL server 2005 X64 Enterprise Edition can’t be upgraded to SQL Server 2008 R2 standard edition. Only option to upgrade to SQL server 2008 Enterprise edition or Datacentre Edition.

Step 2: Run the upgrade advisor
It’s a free utility tool that analyses existing SQL Server deployments and highlighted the issues that need to be addressed prior to performing an upgrade.

Step 3: Verify the hardware and software requirements

It’s very important to verify that existing SQL Server meet the entire hardware requirement for running SQL Server.

Step 4: Perform a Full Backup

Take full backup prior to perform a SQL Server upgrade. Upgrade process usually go smoothly however sometime may go wrong.
Hence it’s important to have a backup so that we can revert to its previous state if the upgrade does not go as planned.

Step 5: Two main options for an upgrade in SQL Server.

 In place Upgrade

An in-place upgrade is the fastest and easiest upgrade method because it upgrades all system and user databases and settings for you. In addition, you do not have to update client applications to connect them to a new instance of the relational Database Engine. However, an in-place upgrade is an all-or-nothing approach. In the unlikely event that an in-place upgrade of the relational Database Engine fails, you cannot quickly roll back to SQL Server 2000 or SQL Server 2005.

 Side by side upgrade

With a side-by-side upgrade, the SQL Server 2008 R2 relational Database Engine is installed as a second instance and the original SQL Server 2000 or SQL Server 2005 relational Database Engine remains installed. Then move or copy one or more SQL Server 2000 or SQL Server 2005 user databases to the SQL Server 2008 R2 instance (each moved database is automatically upgraded).
This approach help maximize ability to quickly roll back to the prior instance whenever difficulties arise. A side-by-side upgrade might also result in simpler testing scenarios because both versions are available at the same time.

Step 6: Things to consider while upgrading the database engine.

If you are running analysis service, make sure you upgrade analysis service before database engine. The analysis service must be upgraded first.

Before upgrade temporarily disable stored procedure .During the course of upgrade, various SQL- related services will be stopped and restarted. If there are procedures configured to run when services start, there may be chance those stored procedure will interfere with upgrade.

Check the master, model, msdb and tempdb databases and verify that they are set to auto grow.

Disable database replication prior to performing an upgrade.

Even though Microsoft SQL Server 2008R2 is designed to preserve max worker thread, Microsoft recommend settings the MAX worked thread value to 0.


Post upgrade run DBCC UPDATEUSAGE on all databases to ensure database integrity.

Attached one Microsoft upgrade document for complete detail.

A significant part of SQL Server process memory has been paged out

A significant part of SQL Server process memory has been paged out. This may result in performance degradation.

A significant part of sql server process memory has been paged out.

This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 48864, committed (KB): 124224, memory utilization: 39%.

Cannot use Large Page Extensions: lock memory privilege was not granted.

What is working set: Memory allocated by the process which is currently in RAM.

Committed: Total memory that is allocated by process (allocated bytes can be in RAM or Page file)

Working Set trimming:  Windows is moving the allocated bytes of the process from physical RAM to page file because of memory pressure. Memory pressure is most commonly caused by applications or windows components that are requesting more memory causing OS to start trimming working set of other processes to satisfy these new requests.

Before we step in to troubleshooting working set trimming warnings, here are few basics about how SQL Server memory management is designed to dynamically adjust the committed memory based on the amount of available memory on the system.

SQL Server uses CreateMemoryResourceNotification to create a memory resource notification object  and SQL Server Resource monitor threads calls QueryMemoryResourceNotification every time it runs to identify if there is any notification. If a low memory notification comes from Windows, SQL Server scales down its memory usage and when Windows sends the high memory notification, SQL Server Server can grow its memory usage target. Low memory notification is signaled by windows when the available physical memory drops approximately below 32 MB per 4 GB, to a maximum of 64 MB. The default level that signals a high-memory-resource notification event is three times the default low-memory value. As soon as the SQL Server resource monitor threads finds low-memory-resource notification it scales down SQL Server memory usage.

Why do I see “A significant part of sql server process memory has been paged out. This may result in performance degradation.” By SQL Server In spite of having above mechanism to detect the system level memory pressure and scale SQL Server memory?

There are couple of situations where SQL Server Process working set might be paged out by Windows despite these memory resource notification mechanism.

1.If windows is not sending the correct notifications to all listening processes at the right moment and thresholds

2.If SQL Server is not responding fast enough to the low memory resource notification from Windows

3.Conditions in Windows where working sets of all processes are trimmed

4.Windows might decide to trim a certain percentage of working set of various or specific processes

We can also increase the LowMemoryThreshold value so the OS will notify applications such as SQL on low memory conditions much earlier and SQL Server can respond to memory pressure much early before the system is starving for memory.

How to set the LowMemoryThreshold value (in MB)?

In Regedit -> go to


Right click on the right pane,

Select New -> select click DWORD Value -> enter LowMemoryThreshold

Double Click LowMemoryThreshold -> value (choose decimal) -> 1000

System Reboot is required to take effect.

Default values as per MSDN:

“The default level of available memory that signals a low-memory-resource notification event is approximately 32 MB per 4 GB, to a maximum of 64 MB. The default level that signals a high-memory-resource notification event is three times the default low-memory value.”

We can use the below query to extract information about the condition of OS memory and SQL memory using a query like the following. Looking at this query, you will be able to easily determine the various indicators that would have triggered the Windows to page various processes including SQL Server. Use the following query to obtain the memory notification-related information from the XML data of the ring buffer.

Common Side Effects of Working set Trimming

1.  When OS starts trimming the working set of SQL Server we normally see non-yielding Resource Monitor dumps.

2.  IS-alive check failures resulting in SQL Server resource failure.

3. Resource monitor thread can start  Garbage collector  if SQLCLR is enabled on this instance of SQL. When Garbage collector  is kicked off during memory pressure all other threads in the process are suspended. So if Garbage collector is taking a long time reosurce monitor thread appears stuck and hence the non-yielding errors and dumps are generated. (Refer

How to troubleshoot?

1.  Capture perfmon counters (Process: Private bytes and Working set ) to determine which applications / windows component are requesting memory and causing OS to start trimming the working set of processes including SQL Server.

2.  Use this exe which will print the memory information of all the processes and system wide memory information (Global memory status) when the operating system signals  low memory notification.

3.  Cap the SQL Server MAX Server Memory after considering the memory required by other applications, Operating system, Drivers , SQL Server Non- Bpool allocations etc. Make sure you have adequate available physical memory even when the system is under heavy load.

4.  We can consider using the Lock pages in memory privilege. Remember it protects only the BPool from paging and Non-Bpool allocations can still be paged out.