Understanding, Analysing and Troubleshooting Deadlocks – Part3

In First Part Understanding, Analysing and Troubleshooting Deadlocks – Part1 we discussed what is Deadlock, How SQL Server engine behaves when a deadlock is encountered, how a victim is identified and why deadlocks occurs etc. In Second Part Understanding, Analysing and Troubleshooting Deadlocks – Part2 we discussed how to get Deadlock Graph from Error Log using Trace Flags.

In this Part we’ll see how to get the same Deadlock graph XML data along with a GUI Representation using Profiler.

Create a New Trace, connect to the relevant server and change the default trace name as desired, you can use any template – for this particular example to keep it simple I used Blank Template which doesn’t pre-select any Event.

1

In the Events Selection tab, expand the locks and you’ll find the first sub-event as Deadlock Graph, select that – you can select other events also if required.2

Now whenever a deadlock will occur you’ll get something similar to below – Profiler by default represent the complete scenario in a GUI flow chart. You can Hover your mouse to any part to get more info on the events & Processes.

3

The Oval shape is used for different processes and the Rectangular share is used for different objects\resources where the deadlock occurred. The Victim process is the one which is crossed by two diagonal lines (x).

We’ve got the GUI Representation but let’s say you still want to get the XML raw information – you can extract the event details from Profiler itself, just Right-click on the event in the upper Tab of trace and select Extract Event Data from the context menu.

clip_image007

A SaveAs Dialog will appear which will help you to save the information in a XDL File. Once saved you can open the file in the SSMS to get same GUI representation.

clip_image009

clip_image011

In case you need XML Data you can open the same XDL file in a Notepad.

clip_image012

clip_image014

In the next Part we’ll discuss how to get this information using Extended Events and how to read the Deadlock Graph XML Data.

Happy Learning 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox

Understanding, Analysing and Troubleshooting Deadlocks – Part2

In First Part Understanding, Analysing and Troubleshooting Deadlocks – Part1 we discussed what is Deadlock, How SQL Server engine behaves when a deadlock is encountered, how a victim is identified and why deadlocks occurs etc.

After reading the error message people often think that either this is the only information SQL Server has for the deadlock or SQL Engine doesn’t share more information since that is internal. But that’s not true, you can get plenty of information that you may need in order to resolve\avoid Deadlocks in Future (yes this is quite possible, well- in most of the cases) This information about Deadlock is also known as Deadlock Graph.

There are many ways to get Deadlock Graph, let’s discuss a few in detail:

1. Trace Flags

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. Prior to SQL Server 2005 using trace flag was the only option to get Deadlock information. In SQL Server 2000 you can use Trace Flag 1204 & 1205 in order to get the information about the Types of locks held, commands being executed and more detailed information about the deadlock.

Starting from SQL Server 2005 Microsoft Introduced Trace Flag 1222 and Profiler trace Event that can be used to get Deadlock Graph. In Trace Flag 1222 you get a detailed information about all the processes running and participating in the deadlock, which query they were running, what all resources were locked, what was the isolation level and what type of lock request was being blocked and why. You can use all this information to troubleshoot the problem.

You can switch-on Trace Flags either by using DBCC Commands or by Startup Parameters.

 

a. Enable Trace Flag using DBCC Commands

DBCC TraceON(1222)

Once the trace flag is enabled you can check the status by using DBCC TraceStatus command, it will provide an output similar to below screenshot.

clip_image001

In the above screenshot you can see that the trace flag 1222 has been enabled but on Session level. Means the trace flag will work for only the current session. If you want to enable this for the full instance you need to give one more parameter i.e., -1. This will enable the trace flag on global level. To enable the trace flag on Global level let’s first disable\switch off the trace flag on session level and then we’ll enable it on global level.

DBCC TraceOFF(1222)

DBCC TraceON(1222,-1)

Now let’s check the status one more time.

clip_image002

So the trace flag has been enabled on Global\Instance level.

 

b. Enable Trace Flag using –T Startup Option

SQLServer uses Startup Parameters during startup to find Master DB Data\Log & Error Log Files, along with this one can also specify some server wide Conditions. Most users do not need to use anything apart from Data\Log & Error log File paths, until and unless you want to change the default startup behavior of DB Engine.

Trace Flags are used to start the server with nonstandard behavior. When using this functionality use an Uppercase T to pass trace flags, a lower case t enables other internal flags that are required by only SQL Server support engineers. The -T startup option enables a trace flag globally. You cannot enable a session-level trace flag by using a startup option.

You can do this in SQL Server Configuration Manager, Choose the right SQL Server Service – right click and open properties. Follow the screenshot below to specify the trace flag and press Add. (the below screenshot is from SQLServer 2012)

Note: Changing any Startup Parameter\option needs a SQL Service Restart.

clip_image003

clip_image004

 

The Deadlock Graph

Once the Trace Flag is enabled (no matter using which way you’ve enabled the Trace Flag) if any deadlock gets captured by the Deadlock monitor the relevant information is captured and logged into the error log. If you read Relevant Error log, you’ll find the Deadlock graph (the deadlock information about all participating processes in XML Format) information gets captured in the error log in clear text.

Refer the screenshot below to see how it looks:

Errorlog-deadlock-graph

In next part we’ll see how to get same Deadlock Graph (text Information as well as a GUI Representation) using Profiler and how to interpret it.

 

Happy Learning 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox

SQL Server Day | Gurgaon | 16-Feb-2013

SQLServerGeeks.com cordially invites you to SQL Server Day, the monthly dose of SQL Server knowledge & learning. And this time we are coming again to Gurgaon. We bring you rock star speakers and panelists from the MVP community & Microsoft. This is yet another opportunity to learn maximum from SQLServerGeeks.com across India. There will be focussed sessions & panel discussion so that you are free to ask any question on SQL Server Database Engine or Business Intelligence platform. And this time, we will have Tanmay Kapoor from Microsoft India MVP program who will talk about “being an MVP”. So there is something for everyone to come and learn. Event will be hosted in Microsoft premises in Gurgaon so that your experience is fabulous.

Register Fast and block your seat. It is absolutely free. Also forward this email to your friends and colleagues to spread awareness. Register Now

Here’s the agenda:

SQL Server Day, Gurgaon, 16 February – Register Now
Session & Speaker details Venue & Contact details Date & Time

Welcome Note & Ice-Breaker (15 mins) 
by 
Amit Bansal (President & Founder,SQLServerGeeks.com, MVP)

Creating SQL Server Performance Baseline by Sarabpreet Singh Anand, (Vice President SQLServerGeeks.com, MVP)

Panel Discussion (open ended) on SQL Server & Microsoft Data Platform (MVP Amit Bansal, MVP Sarabpreet, MVP Dhananjay)

Introduction – Microsoft Most Valuable Professional (MVP) Award Program
by Tanmay Kapoor, Microsoft)

Closing Note : (15 mins) 
by Sarabpreet Singh Anand, (Vice President
SQLServerGeeks.com)

Microsoft Corporation (I) Pvt. Ltd.
DLF Cyber Greens
9th Floor, Tower A,
DLF Cyber City, Sector 25A
Gurgaon – 122002
(Near Shankar Chowk)

Note: Refreshments will be served

Feb 16, 2013 (Saturday) 
1.30 pm to 5.00 pm

Note: The event will start sharp at 2.00 pm. Please come by 1.30 pm for registration. (Due to security policies, registration takes time. Please come with a valid photo ID)

register now

16-feb-sqlserverday-gurgaon

Understanding, Analysing and Troubleshooting Deadlocks – Part1

A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock.

In simple language, Deadlock is a situation when two or more processes are waiting to acquire lock on resource(s) which are already blocked by opposite participant process, hence results in a never ending locking situation. A deadlock will never resolve itself and the processes will keep on waiting forever.

That’s the reason SQLServer has Deadlock monitor\detector, It keep on checking such situation and if a deadlock is found it identifies one process as victim and kill that process so that the lock(s) can be released and the other process can acquire the required locks & complete the job. Deadlock monitor is an internal process which runs every 5 second by default and if your system is experiencing frequent deadlocks the time interval between different checks can be changed internally by SQL Engine and it can go as low as 100 MS.

Now you must be wondering which process will be identified as victim and on what basis?Deadlock Detector does not select a process randomly, there are three rules\conditions:

  1. A process cannot be identified as victim and killed if the deadlock priority is set to High.
  2. Log Generation: the process with least amount of log generation during the transaction will be identified as victim.
  3. It also checks whether the process is a user process or a system process.

Generally a deadlock occurs when different transactions try to access same set of objects\resources in different order. For Example:

  • User A acquires a share lock on Table1.
  • UserB acquires a share lock on Table2.
  • UserA now requests an exclusive lock on Table2, and is blocked until UserB finishes and releases the share lock it has on Table2.
  • UserB now requests an exclusive lock on Table1, and is blocked until UserA finishes and releases the share lock it has on Table1.
  • UserA cannot complete until UserB completes, but UserB is blocked by UserA.
  • UserA has a dependency on UserB, and UserB closes the circle by having a dependency on UserA.

 

Don’t get confused between Deadlock with Blocking – in blocking the resources wait in a queue and the queue gets cleared once the task completes hence the first transaction releases the held locks so that second transaction can acquire lock but in deadlock it becomes a cyclic lock.

Once a deadlock occurred on a transaction\Application SQL Server throws an ugly 1205 Error with the following message: Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

clip_image002

If you are new to SQLServer or you don’t have any prior experience with Deadlocks you may go crazy after reading the error, since the message tells that an error has occurred but still suggests\prompts you to rerun your transaction without asking you to fix something.

The reason is that once the deadlock victim was killed the other transaction got a chance to acquire the required locks and complete the transaction, so if you rerun the transaction after a small delay probably you won’t get another deadlock and this time your transaction can complete.

In next Parts we’ll see how to get more info about Deadlock that just occurred in your environment (yes you can actually get more info), how to get same Deadlock Graph (XML Information as well as a GUI Representation) using Profiler  and how to interpret it.

Happy Learning 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox

Learn SQL Server User Impersonation

As discussed in earlier Login Impersonation Blog Technically SQL Server and windows both can be configured to authenticate to another SQL instance\Windows Server against the current login account, this behavior is known as Impersonation. When one login\user acts on behalf of another login\user this behavior is also known as impersonation.

Database Level

We can easily impersonate a session by explicitly mentioning a standalone EXECUTE AS statement but for doing so you should have Impersonate permissions on the other User else you’ll not be allowed to impersonate and you’ll face the similar error mentioned below:

Impersonate error:

user impersonation error

Msg 15517, Level 16, State 1, Line 1
Cannot execute as the database principal because the principal “Sarab_WriterRole” does not exist,
this type of principal cannot be impersonated, or you do not have permission.

 

Check the User Permission to Impersonate

Let’s check if the User(Sarabpreet) has the Impersonate Permission to impersonate as Sarab_WriterRole, for doing so we’ll run the below mentioned query:

select * from sys.Database_principals

no impersonation user right

If the current logged in user has impersonate permission to impersonate the principal name will appear in the result set. Since we got the error & now the result set also doesn’t show the principal Sarab_WriterRole, it is now confirmed that Sarabpreet login doesn’t have appropriate impersonate permissions.

 

Grant Impersonate Permission on User

To grant the impersonate permission you can execute the below mentioned query, in this query we are granting Impersonate permission to Sarabpreet so that the user Sarabpreet can impersonate as Sarab_WriterRole.

GRANT IMPERSONATE ON User::Sarab_WriterRole TO Sarabpreet

Since we’ve granted the permission let’s cross verify this once:

got user impersonate permissions

You can see that now the principal Sarab_WriterRole is visible to Sarabpreet login, which means now the User Sarabpreet can execute queries by impersonating as Sarab_WriterRole.

To Revoke Impersonate Permission from User

REVOKE IMPERSONATE ON User::Sarab_WriterRole TO sarabpreet

 

Hope you enjoyed the post, feel free to leave a comment. 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox

Learn SQL Server Login Impersonation

Technically SQL Server and windows both can be configured to authenticate to another SQL instance\Windows Server against the current login account, this behavior is known as Impersonation. When one login\user acts on behalf of another login\user this behavior is also known as impersonation.

Server Level

We can easily impersonate a session by explicitly mentioning a standalone EXECUTE AS statement but for doing so you should have Impersonate permissions on the other login else you’ll not be allowed to impersonate and you’ll face the similar error mentioned below:

Impersonate error:

clip_image002

Error: Msg 15406, Level 16, State 1, Line 1

Cannot execute as the server principal because the principal “sarab_SecurityAdmin” does not exist, this type of principal cannot be impersonated, or you do not have permission.

 

Check the Permission to Impersonate

Let’s check if the login (Sarabpreet) has the Impersonate Permission to impersonate as Sarab_SecurityAdmin, for doing so we’ll run the below mentioned query:

select * from sys.server_principals

If the current logged in login has impersonate permission to impersonate the principal name will appear in the result set. Since we got the error & now the result set also doesn’t show the principal Sarab_SecurityAdmin, it is now confirmed that Sarabpreet login doesn’t have appropriate impersonate permissions.

clip_image003

 

Grant Impersonate Permission on Login

To grant the impersonate permission you can execute the below mentioned query, in this query we are granting Impersonate permission to Sarabpreet so that the login Sarabpreet can impersonate as Sarab_SecurityAdmin.

GRANT IMPERSONATE ON Login::Sarab_SecurityAdmin TO Sarabpreet

Since we’ve granted the permission let’s cross verify this once:

clip_image004

You can see that now the principal Sarab_SecurityAdmin is visible to Sarabpreet login, which means now the login Sarabpreet can execute queries by impersonating as Sarab_SecurityAdmin.

 

To Revoke Impersonate Permission

REVOKE IMPERSONATE ON Login::Sarab_SecurityAdmin TO sarabpreet

For User Impersonation read: Learn SQL Server User Impersonation

 

Hope you enjoyed the post, feel free to leave a comment. 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox

Blessed with a Baby Girl

Hi Friends,

If you are following me or my activities you might have missed me for last couple of weeks, I was actually busy with my family and now since everything is normal I want to share a good news. We have been blessed with a beautiful baby girl on 4th of Dec-2012. Party smile

My Sweet Little Baby

My Sweet Little Baby

It was really a tough time for both (my wife & kid), since she’s a premature baby there were some complications but with God’s grace now everything is back to normal (sort of). And there is another good newsWinking smile, you’ll again see me in action very soon. Smile

We are planning our schedule for the next calendar Year (2013) & we’ll bring some new enhancements to our website (SQLServerGeeks.com).

Till Then Happy Learning!

Happy Diwali – Say no to Crackers

Hi Friends,

A Very Happy Diwali To You & Your Family May God Give You All That You Want & Need In Your Life.

Enjoy The Festival With Lots Of Light. And Say NO to Crackers.

With the evolution of the lifestyle, there has been certain change in the way people celebrate Diwali, as more and more technology has been included, but the zeal and the spirit of celebration remains the same. Earthen lamps may have been replaced with stunning electric illuminations, dress code may have changed, but the custom and tradition of performing puja has been carried very well through generations.

 

While enjoying Diwali join the noble cause to save the environment & Say No to Crackers

 

To Know more about Diwali history

The history of Diwali is replete with legends and these legends are moored to the stories of Hindu religious scriptures, mostly the Puranas. Though the central theme of all legends point out to the classic truth of the victory of the good over the evils, the mode of their presentation and the characters differ. Diwali, being the festival of lights, lighting the lamp of knowledge within us means to understand and reflect upon the significant purpose of each of the five days of festivities and to bring those thoughts in to the day to day lives.
The five day of Diwali
The first day of Diwali is called Dhanvantari Triodasi or Dhanwantari Triodasi also called Dhan Theras. The second day of Diwali is called Narak Chaturdasi. It is the fourteenth lunar day (thithi) of the dark forthnight of the month of Kartik and the eve of Diwali. On this day Lord Krishna destroyed the demon Narakasur and made the world free from fear. The third day of Diwali is the actual Diwali. This is the day when worship for Mother Lakshmi is performed. On the fourth day of Diwali, Goverdhan Pooja is performed. The fifth day of the diwali is called Bhratri Dooj. It is a day dedicated to sisters.

Hindu Mythology
The Story of Rama and Sita: Lord Rama was a great warrior King who was exiled by his father Dashratha, the King of Ayodhya, along with his wife Sita and his younger brother Lakshman, on his wife’s insistence. Lord Rama returned to his Kingdom Ayodhya after 14 years of exile, in which he put an end to the demon Ravana of Lanka, who was a great Pundit, highly learned but still evil dominated his mind. After this victory of Good over Evil, Rama returned to Ayodhya. In Ayodhya, the people welcomed them by lighting rows of clay lamps. So, it is an occasion in honor of Rama’s victory over Ravana; of Truth’s victory over Evil.
The Story of King Bali and Vamana Avatar(the Dwarf): The other story concerns King Bali, who was a generous ruler. But he was also very ambitious. Some of the Gods pleaded Vishnu to check King Bali’s power. Vishnu came to earth in the form of a Vamana(dwarf) dressed as priest. The dwarf approached King Bali and said “You are the ruler of the three worlds: the Earth, the world above the skies and the underworld. Would you give me the space that I could cover with three strides?” King Bali laughed. Surely a dwarf could not cover much ground, thought the King, who agreed to dwarf’s request. At this point, the dwarf changed into Vishnu and his three strides covered the Earth, the Skies and the whole Universe! King Bali was send to the underworld. As part of Diwali celebrations, some Hindus remember King Bali.
The Defeat of Narkasur by Lord Krishna: Lord Vishnu in his 8th incarnation as Krishna destroyed the demon Narkasura, who was causing great unhappiness amongst the people of the world. Narkasura was believed to be a demon of filth, covered in dirt. He used to kidnap beautiful young women and force them to live with him. Eventually, their cries for rescue were heard by Vishnu, who came in the form of Krishna. First, Krishna had to fight with a five-headed monster who guarded the demon’s home. Narkasura hoped that his death might bring joy to others. Krishna granted his request and the women were freed. For Hindus, this story is a reminder that good can still come out of evil.
Krishna and The Mountain: In the village of Gokula, many years ago, the people prayed to the God Indra. They believed that Indra sent the rains, which made their crops, grow. But Krishna came along and persuaded the people to worship the mountain Govardhan, because the mountain and the land around it were fertile. This did not please Indra. He sent thunder and torrential rain down on the village. The people cried to Krishna to help. Krishna saved the villagers by lifting the top of the mountain with his finger. The offering of food to God on this day of Diwali is a reminder to Hindus of the importance of food and it is a time for being thankful to God for the bounty of nature. 

Sikh Festival Diwali
In Sikh perspective, Diwali is celebrated as the return of the sixth Guru, Guru Hargobind Ji from the captivity of the city, Gwalior. To commemorate his undying love for Sikhism, the towns people lit the way to, Harmandhir Sahib (referred to as the Golden Temple), in his honour.

Jain Festival Diwali
Among the Jain festivals, Diwali is one of the most important one. For on this occasion we celebrate the Nirvana of Lord Mahavira who established the dharma as we follow it. Lord Mahavira was born as Vardhamana on Chaitra Shukla 13th in the Nata clan at Khattiya-kundapura, near Vaishali. He obtained Kevala Gyana on Vishakha Shukla 10 at the Jambhraka village on the banks of Rijukula river at the age of 42.

http://www.diwalifestival.org/diwali-in-history.html

Enjoy the Festival.