SQL SERVER – To Find Events Mapped to Channels in Extended Events

I have outlined a number of blogs here that point to the basics of working with Extended Events. Some of them also detail you some specific scenarios that you can use inside your environments.

Identify Page Splits Using Extended Events in SQL Server

Knowing Deprecated or Discontinued Features Using Extended Events

Filtering CPU Bound Execution Plans with Extended Events

Introduction to Extended Events – Finding Long Running Queries

As I wrote them and used them in a number of external facing conferences, guess what – a number of you asked me during the sessions and via emails to the advantage of using the same. I used to give a standard definition and say it is quite exhaustive when compared to using SQL Server Profiler. Though this was subjective, I needed a way to quantify and this question did come from one DBA during a conversation – how exhaustive is this?

In other words, he wanted to know what are the exhaustive list of events we can collect with extended events and across the channels. Unlike the standalone tool (SQL Server Profiler) in previous releases, starting with SQL Server 2012, the UI to manage and view extended events sessions is built into SQL Server Management Studio. There is a new node in the Management tree in Object Explorer called Extended Events. Use this node to graphically create or modify a session, start or stop session, view data captured by sessions live as well as script the session definition.

What is Channel?

A channel identifies the audience of an event. Events are categorized into these different buckets. The available channel types are:

Admin Admin events are primarily targeted to the end users, administrators, and support. The events that are found in the admin channels indicate a problem with a well-defined solution that an administrator can act on.
Operational Operational events are used for analyzing and diagnosing a problem or occurrence. They can be used to trigger tools or tasks based on the problem or occurrence.
Analytic Analytic events are published in high volume. They describe program operation and are typically used in performance investigations.
Debug Debug events are used solely by developers to diagnose a problem for debugging.

Now that we have put the foundation in each of the channels, Use the following query to get a list of all events and the channel to which they belong:

SELECT p.name AS package, c.event, k.keyword, c.channel, c.description
SELECT event_package=o.package_guid, o.description,
event=c.OBJECT_NAME, channel=v.map_value
FROM sys.dm_xe_objects o
LEFT JOIN sys.dm_xe_object_columns c ON o.name = c.OBJECT_NAME
INNER JOIN sys.dm_xe_map_values v ON c.type_name = v.name
AND c.column_value = CAST(v.map_key AS NVARCHAR)
WHERE object_type='event' AND (c.name = 'channel' OR c.name IS NULL)
SELECT event_package=c.object_package_guid, event=c.OBJECT_NAME,
FROM sys.dm_xe_object_columns c INNER JOIN sys.dm_xe_map_values v
ON c.type_name = v.name AND c.column_value = v.map_key
AND c.type_package_guid = v.object_package_guid
INNER JOIN sys.dm_xe_objects o ON o.name = c.OBJECT_NAME
AND o.package_guid=c.object_package_guid
WHERE object_type='event' AND c.name = 'keyword'
) k
k.event_package = c.event_package AND (k.event = c.event OR k.event IS NULL)
INNER JOIN sys.dm_xe_packages p
ON p.guid=c.event_package
WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
ORDER BY channel, keyword, event

On a SQL Server 2016 instance I can see more than 1050+ events getting listed. I am sure this is more than what someone can chew.

extended channel 01 SQL SERVER   To Find Events Mapped to Channels in Extended Events

At a high level the number of events associated with each of the Channels are as shown above.

Do let me know if you have moved away from Profiler and started adopting Extended Events inside your environments? What are some of the typical usecases in your environments? Drop a line as comments as it would be useful for others too.

Reference: Pinal Dave (http://blog.sqlauthority.com)


errorstop SQL SERVER   Error Fix: Msg 300, VIEW SERVER STATEI travel quite a bit and the month of October and November seem to be filled with half my life is going to be spent hopping from one airport to another. Not to mention the amount of screening that go through every single time in every flight. However strange as it might sound, in one of such airport transits I was waiting for my bag to be screened through the machine and I was watching the screen on the other side where the security officer sits. It was interesting because it was a very skillful task as he was finding a needle in a haystack (in the literal sense). He was able to figure out what items, sharp items and toys which are restricted were getting passed through. I was amused and was transported into a world of wonderland because it was quite a challenging task.

With these memories not subsiding, I landed to my desired destination and started checking my mails. There was one mail that caught my attention because suddenly it was a junior DBA who was trying to give some permissions and was getting an error. I was quick to get interested because it was a topic around security and I wanted to crack it. The message stated like:

Msg 300, Level 14, State 1, Line 1
VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’.
Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.

As I always mention, most of the SQL Server error messages these days are well documented and were self-explanatory. To make sure I got the error message right, I requested the person to send me the query that was being executed that caused the error. The reply was:

SELECT wait_type, wait_time_ms
FROM sys.dm_os_wait_stats


This made complete sense and the solution for this was also simple. From a repro point of view, I created a user Pinal and tried to run the above command. And guess what, as luck might turn out – the same error message as described in the start. To mitigate this error, I was to give the following GRANT and the error message disappeared:


Just curious to know have you as a DBA ever granted this right to users? What was the scenario in your case? Would love to see why you don’t want to give to specific users? Will be a great learning for all of us. Let me know via the comments, please.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #040 – Difference Between Unique Index vs Unique Constraint

Question: What is the difference between unique index and unique constraint?


Add Unique Constraint
<namingconventionconstraint> UNIQUE NONCLUSTERED

Add Unique Index
<namingconventionconstraint> ON dbo.<tablename>

There is no difference between Unique Index and Unique Constraint. Even though the syntax is different the effect is the same. Unique Constraint creates Unique Index to maintain the constraint to prevent duplicate keys. Unique Index or Primary Key Index is a physical structure that maintains uniqueness over some combination of columns across all rows of a table. It is a convenient way to enforce a Unique Constraint in SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Trick – Running SSMS With Different Windows Account

Recently in one of my interaction with a DBA inside a big financial organization, I was surprised to see that the administrator was logging into a desktop / server using a different credential. What baffled me was the fact that he was logging off. I immediately asked, why are you doing this? He said, since he was using windows authentication, which was different from his normal credentials that for DB related activities he used to create a different session. After a bit of research, I found this to be a common practice in a number of organizations.

Many companies provide two different windows account to any Administrator. One “regular” account (example SQLAuthority\Pinal) is used for general work like emails, login to laptop etc. And another “admin” account (example SQLAuthority\adm_pinal) for administrative level tasks to be done on the server. The regular account would have no access to SQL Servers, but the admin – level account would. To make security stronger, the companies use a Windows account to connect to SQL Server.

This makes thing little interesting. If one has to connect to SQL Server from the laptop where the DBA has logged in with regular account, he/she has to do “Right Click” with Shift key pressed and then use “Run as Different user” to use the admin account.

ssms runas 01 SQL SERVER   Trick   Running SSMS With Different Windows Account

Pressing shift key is important otherwise we would not see that option. After choosing that, it would pop-up with user name and password window. If you notice, you would notice ssms.exe path which is used.

ssms runas 02 SQL SERVER   Trick   Running SSMS With Different Windows Account

Another way, which I prefer is using Run as command. The program runas.exe allows to let us tell Windows to run a program using a different user’s current network environment instead of the local environment. The full details and switches of the run as the program can be found in TechNet article.

I would normally create shortcut on desktop. Right Click and choose a new shortcut as shown below

ssms runas 03 SQL SERVER   Trick   Running SSMS With Different Windows Account

and give location/parameter as below

C:\Windows\System32\runas.exe /noprofile /env /user:SQLAuthority\adm_Pinal “C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe”

You need to change two things, user name and the correct path for SSMS.exe

Once it’s saved, you can double click and provide password on the command prompt. I have given a shortcut name as Admin SSMS in below screenshot. As soon as I double click, I see below

One credential is provided, SSMS would open with that windows account.

I must conclude by saying that if you are using SQL Authentication then you do not need to do this because Windows credentials are not passed to SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Database Testing and Waitfor Delay – Notes from the Field #099

[Note from Pinal]: This is a 99th episode of Notes from the Field series. In this episode we are going to learn something very simple but effective about database testing. Database testing is one of the most critical elements for any developer or DBA. If you do not test your code, there are good chances to make mistakes. However, sometime testing requires some advanced tricks.

In this episode of the Notes from the Field series database expert Kevin Hazzard explains the how to use command Waitfor Delay for our advantage when doing database testing. Read the experience of Kevin in his own words.

KevinHazzard SQL SERVER   Database Testing and Waitfor Delay   Notes from the Field #099

Database testing has become something of an obsession for us at Linchpin People. To test ETL processes, I often write mocking procedures that can simulate data flowing in from clients. When I first began doing this years ago, I’d set up SQL Agent jobs to invoke the mocking procedures on a regular frequency. However, that’s fairly cumbersome to set up so I started writing scripts like this instead:

WAITFOR DELAY '00:00:03';
EXEC sp_InsertMockTransaction;
GO 100

This code uses the WAITFOR command which many developers already understand. If you’ve never seen or heard of WAITFOR before, it’s fairly straightforward. In the code shown above, the DELAY option is used to induce a delay of three seconds. Afterwards, the mocking procedure runs.

The last line of the script is not as familiar to developers, I’ve found. We’ve all seen and used the GO phrase but as it turns out, it’s not part of the T-SQL language. GO is a batch separator that tells SSMS or the SQLCMD processor to forward the current batch of commands to an instance of SQL Server. What many developers don’t realize is that the GO batch separator can accept a positive integer parameter that will loop over the batch that many times.

In SSMS, the code shown above will invoke the mocking procedure 100 times, pausing for three seconds in between. This is a simple but effective way to simulate the input I’ll need for my tests. Of course, this pattern comes in handy for all sorts of uses, not just testing. Hopefully you’ll find simple trick as useful as I have.

If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Why Should You Not to Use Old Style JOIN?

If you want to JOIN two tables you can do it in two ways. One using ANSI compliant INNER JOIN and another with old style join

But it is advisable not to use old style join. Here is the reason.

Let us create the following data sets

CREATE TABLE item_master(item_id INT, item_description VARCHAR(100));
CREATE TABLE item_sales(item_id INT, sales_date DATETIME, qty INT);
INSERT INTO item_master
INSERT INTO item_sales
SELECT 1,'2015-04-03 12:10:10',2 UNION ALL
SELECT 2,'2015-06-11 07:22:00',3 UNION ALL
SELECT 2,'2015-06-12 11:00:48',22;

If you want to show each item_item description with total quantity that are sold, you can use this code

Method 1 : INNER JOIN

SELECT item.item_description,SUM(details.qty) AS qty FROM item_master AS item
INNER JOIN item_sales AS details
ON item.item_id=details.item_id
GROUP BY item.item_description;

Method2 : Old style join with WHERE clause

SELECT item.item_description,SUM(details.qty) AS qty FROM item_master AS item
,item_sales AS details
WHERE item.item_id=details.item_id
GROUP BY item.item_description;

Both returns the following result

item_description qty
 ------------------- ----------
 LG 25
 Samsung 2

But what happens if you omit the WHERE condtion in method 2 by mistake

SELECT item.item_description,SUM(details.qty) AS qty FROM item_master AS item
,item_sales AS details
GROUP BY item.item_description;

The result is

item_description qty
 ------------------- ----------
 LG 27
 Samsung 27

Which is totally wrong as it leads to cross join

But method 1 will throw error if no JOIN is specified

SELECT item.item_description,SUM(details.qty) AS qty FROM item_master AS item
INNER JOIN item_sales AS details
GROUP BY item.item_description;

The error is

Incorrect syntax near the keyword 'group'.

In summary, always use an ANSI compliant INNER JOIN.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SSMS Query Command(s) completed successfully without ANY Results – Part 2

What do you do when you encounter something that you didn’t expect? A lot of times such experiences take you to a state of shock or a state of bafflement. The curiosity sometimes pushes you off the cliff to understand why certain behaviors occur. I am fortunate that I get into bizarre situations and the learning it gives is immense. No wonder such things happen because I play around with SQL Server Management options as part of learning. This learning exercise gets me into trouble and below is a classic case.

From my laptop, whatever query I run, I just receive a message like Query Command(s) completed successfully without any result. Here is an example:

no exec 01 SQL SERVER   SSMS Query Command(s) completed successfully without ANY Results   Part 2

Long back I have written below blog post with the same subject.

SQL SERVER – SSMS Query Command(s) completed successfully without ANY Results

The first stop would be to search the blog for possible replies that might help. Later, I realized that there were few comments where readers mentioned that even after following the steps given in the above blog (SET PARSEONLY option), they were still seeing the same behavior. Whenever I get into such stage, I ask folks who might know. So, I was asked I can capture a profiler to reproduce the issue.

Below is what I shared with my friends to walk the same learning:

no exec 02 SQL SERVER   SSMS Query Command(s) completed successfully without ANY Results   Part 2

In the profiler, we have the answer. It’s been due to the fact that NOEXEC option, it SET to ON. The next question was to identify a possible reason and how I landed into this in the first place. There are two places we can set it in Management Studio.

Query Level (for current query window in SSMS):

In SQL Server Management Studio, right click on query window and choose “Query options”

no exec 03 SQL SERVER   SSMS Query Command(s) completed successfully without ANY Results   Part 2

And over there, we can set option for query level.

no exec 04 SQL SERVER   SSMS Query Command(s) completed successfully without ANY Results   Part 2

SSMS Level (For all query windows):

In SSMS, we can go to “Tools” > “Options” and chose Advanced as shown below.

no exec 05 SQL SERVER   SSMS Query Command(s) completed successfully without ANY Results   Part 2

If any the option is checked, we would see the same behavior which I explained at the beginning of the blog. So, if you see such behavior, check PARSEONLY and NOEXEC set options in query level setting and SSMS level setting.

Have you ever landed into this behavior ever? Do let me know. I am sure if you share via comments, it will get us into an exploratory mode to learn and try more options if I missed any.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Steps to Backup to Windows Azure storage

Moving to a cloud based world is inevitable, it is something we need to learn soon. Ever since SQL Server 2014 has been released, the concept of uploading your backups to Blobs with Windows Azure has been around. In reality, performing a backup or restore operations with SQL Server 2012 SP1 CU4 and later requires no additional tools actually, and be done with either T-SQL or SSMS. This blog describes how to perform backup operations with T-SQL. This will be part of a series of blogs to come in the future. Let me walk through the initial steps.

Creating Credentials

To perform the backup and restore procedures on your local SQL Server you will need to create a SQL credential using the Windows Azure Storage Account configuration. The following steps will create the necessary credential:

  1. Connect to SQL Server Management Studio.
  2. On the Standard toolbar, click New Query.
  3. Copy and paste the following example into the query window, modifying as needed.

WITH IDENTITY= 'mystorageaccount' --this is the name of the storage account you specified when creating a storage account
, SECRET = '<storage account access key>' -- this should be either the Primary or Secondary Access Key for the storage account.

Steps to backup to Windows Azure storage

The following steps describe how to perform a backup of a database to the Windows Azure storage service. The database can be an on-premises database, or located in a Windows Azure Virtual Machine. The key requirement for this demo variation is that the database must be accessible from SQL Server Management Studio:

If you don’t have your own database, you plan to use for this tutorial, then install AdventureWorks from: http://msftdbprodsamples.codeplex.com/

  1. Connect to SQL Server Management Studio.
  2. In the Object Explorer, connect to the instance of SQL Server on which the database to be backed up is located.
  3. In Object Explorer, connect to the instance of the Database Engine that has the database you plan to backup.
  4. On the Standard menu bar, select New Query.
  5. Copy and paste the following example into the query window, modify as needed, and click Execute.

BACKUP DATABASE [AdventureWorks2014]
TO URL = 'https://mystorageaccount.blob.core.windows.net/privatecontainers/AdventureWorks2014.bak'
/* URL includes the endpoint for the BLOB service, followed by the container name, and the name of the backup file*/
WITH CREDENTIAL = 'mycredential';
/* name of the credential you created in the previous step */

As you can see, the steps to back up to an URL is simple as described above. The pre-requisite for this is to have a storage account in Azure ready before you do the same.

Do let me know if you have ever taken a backup to Azure till date. Please let me know if you want me to write on this topic in the future.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Creating a Database Administrator Group Using User-Defined Server Roles

When it comes to security related topics, I always read and learn something new from my friend Vinod. He always comes-up with something interesting and when he was at GIDS event, one of the attendees walked and asked a security question. He was interested in knowing how we can have near to DBA privileges and still mask the data from the DBA because it has sensitive information. Without an iota of doubt, I just pointed to Vinod to take the conversation forward. Curious enough, I made it a point to join the discussion immediately. Who would want to miss an opportunity to learn something new anyways?

By the time I reached, the explanation had started and here is what I was hearing. Once that learning – which in my opinion was in air in general. I had to tryout what he said. Let me do a recap of the same:

The sysadmin role has access to everything inside SQL Server. It is not possible to restrict access to logins who are members of the sysadmin role.  In some cases, you may want to have users who are DBAs but you do not want them to access sensitive tables or change certain database schemas.  In SQL Server 2012 you can leverage a new feature called user-defined server roles to provide a solution to this scenario.

In this exercise, you have a SALES database that contains two users, “PinalUser” and “SQLAuthUser”.  SQLAuthUser owns a schema called, “CRM”.  In this schema there is an Employees table which contains sensitive information. PinalUser is a DBA and our task is to create a server scoped user role called, “DBA Role” which PinalLogin is a member.  Note: PinalLogin is mapped to PinalUser.  By the end of this lab you will see how even though PinalLogin is a member of DBA Role and that role has CONTROL SERVER permissions, he can’t SELECT the sensitive information from the CRM schema.

To simulate and showcase the same, below are the 6 steps to be taken:

Create Sales database, logins and users

Open SQL Server Management Studio, In the Query Editor that was created as a result of the previous step, enter the following code and press F5 (or click on the “Execute” button on the toolbar):

CREATE LOGIN PinalLogin WITH PASSWORD='pass@word1'
USE Sales
(Employee_name VARCHAR(50) NOT NULL,
Emplpoyee_SS CHAR(12) NOT NULL,
INSERT INTO CRM.Employees VALUES('Vinod','123-22-3456','12/1/1980'),('Balu','345-55-1234','4/3/1985')

Connect to SQL Server as PinalLogin and see if he has access to the Employees table

In Object Explorer click on the Connect button and select, “Database Engine…”. Connect as PinalLogin. If the connection was successful you will see another node in the object explorer for the connection to the database under PinalLogin context. You can tell the connection context by looking at the last part of the name of the server node. Navigate down PinalLogin’s connection to the Databases node, then to the Sales node then to the Tables node.

Note: The CRM.Employees table does not exist as Pinal doesn’t have access to the CRM schema.

As the sysadmin, create a server level role, DBA Role and add Pinal to it

In a query editor, please type the below commands:


Note: If these commands failed, make sure your query editor window is running under the context of the administrator.  You can tell this by looking at the connection properties dialog (hitting F4).  The Login name will show you the login context of the current query editor window.

Now that PinalLogin is a member of the DBA Role and this role has CONTROL SERVER permission let’s see if PinalLogin can now access the table.

As PinalLogin, query the CRM.Employees table

  1. In Object Explorer, select the server node that is connected as “PinalLogin”
  2. Right click and select, “Refresh” from the context menu
  3. Navigate down to, “Databases” then “Sales” then “Tables”
  4. Click on Tables node and select the “CRM.Employees” table
  5. Right click and select, “Select Top 1000 Rows” from the context menu

A new query editor window should open showing you the contents of the CRM.Employees table. This is because PinalLogin has CONTROL SERVER permission. A permission that is almost equivalent to sysadmin. Close the document window showing you the CRM.Employees result set by clicking on the “X” or by selecting, “Close” from the “File” menu.

As an administrator, deny select to PinalUser

PinalUser is the database user that is mapped to the PinalLogin server principal.  To ensure Pinal can’t see out sensitive employee table, we simply issue a DENY statement on PinalUser. In the query editor window enter the following code:


Note: If these commands failed, make sure your query editor window is running under the context of the administrator.  You can tell this by looking at the connection properties dialog (hitting F4).  The Login name will show you the login context of the current query editor window.

The big difference between sysadmin and someone with CONTROL SERVER permission is SQL Server will respect a DENY on an object even when the user has CONTROL SERVER. In our example, even though PinalLogin has CONTROL SERVER, since database user PinalUser has a DENY on the CRM.Employees table, he can’t see the data.

As  PinalLogin try to query the CRM.Employees table

Follow these steps:

  1. In Object Explorer, select the server node that is connected as “PinalLogin”
  2. Right click and select, “Refresh” from the context menu
  3. Navigate down to, “Databases” then “Sales” then “Tables”
  4. Click on Tables node and select the “CRM.Employees” table
  5. Right click and select, “Select Top 1000 Rows” from the context menu

Expected Outcome: 

Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object ‘Employees’, database ‘Sales’, schema ‘CRM’.

This completes our steps for restricting a DBA user to access sensitive data.  I am curious to know; have you ever done this before? I would love to hear from you and your learnings on this subject.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #039 – What is Included Column Index in SQL Server 2005 and Onwards?

You may find it interesting that not many people still know what is Included Index in SQL Server. I have seen it personally around me and also have seen at

Question: What is Included Column Index?


In SQL Server 2005, the functionality of non-clustered indexes is extended by adding non-key columns to the leaf level of the non-clustered index. Non-key columns, can help to create cover indexes.By including non-key columns, you can create non-clustered indexes that cover more queries. The Database Engine does not consider non-key columns when calculating the number of index key columns or index key size. Non-key columns can be included in non-clustered index to avoid exceeding the current index size limitations of a maximum of 16 key columns and a maximum index key size of 900 bytes. Another advantage is that using non-key column in the index we can have index data types not allowed as index key columns generally.

In following example column FileName is varchar(400), which will increase the size of the index key bigger than it is allowed. If we still want to include in our cover index to gain performance we can do it by using the Keyword INCLUDE.

USE AdventureWorks
CREATE INDEX IX_Document_Title
ON Production.Document (Title, Revision)
INCLUDE (FileName)

Non-key columns can be included only in non-clustered indexes. Columns can’t be defined in both the key column and the INCLUDE list. Column names can’t be repeated in the INCLUDE list. Non-key columns can be dropped from a table only after the non-key index is dropped first. For Included Column Index to exist there must be at least one key column defined with a maximum of 16 key columns and 1023 included columns.

Avoid adding unnecessary columns. Adding too many index columns, key or non-key as they will affect negatively on performance. Fewer index rows will fit on a page. This could create I/O increases and reduced cache efficiency. More disk space will be required to store the index. Index maintenance may increase the time that it takes to perform modifications, inserts, updates, or deletes, to the underlying table or indexed view.

Another example to test:
Create following Index on Database AdventureWorks in SQL SERVER 2005

USE AdventureWorks
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID)

Test the performance of following query before and after creating Index. The performance improvement is significant.

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN '98000'
AND '99999';

Reference: Pinal Dave (http://blog.sqlauthority.com)