Category Archives: LearnSQL

Read Before you even think of Installing SQLServer 2014 CTP1

SQL Server 2014 CTP1 is out with lots of cool & exciting features, if you’ve not yet downloaded it, you can get it from below mentioned link: http://www.microsoft.com/en-us/sqlserver/sql-server-2014.aspx

If you are want to try out all\any of these cool features you might want to install this version but before you even think of doing this read the below limitations carefully – chances are your installation will fail if you do not fulfill any of these.

Microsoft SQL Server 2014 Community Technology Preview 1 (CTP1) installation limitations:

  1. · Microsoft SQL Server 2014 CTP1 is pre-release software and should be installed only on a clean machine. It does not support upgrade from or side-by-side installations with any previous version of SQL Server, including SQL Server 2012. In addition it does not work with or support side by side installations with any client redistributables of SQL Server such as feature pack components, Visual Studio 2012 or earlier versions, SQL Server Data Tools, Data Tier Application Framework, etc.
  2. · To administer Microsoft SQL Server 2014 CTP1 use only SQL Server Management Studio that ships as a part of Microsoft SQL Server 2014 CTP1.
  3. · The Microsoft SQL Server 2014 CTP1 release is NOT supported by Microsoft Customer Services and Support (CSS).
  4. · The Microsoft SQL Server 2014 CTP1 release is only available in the X64 architecture.
  5. · The Microsoft SQL Server 2014 CTP1 release is a NON-PRODUCTION release and should not be installed and used in production environments.
  6. · The Microsoft SQL Server 2014 CTP1 release does NOT have an associated Microsoft SQL Server 2014 CTP1 Feature Pack.
  7. · The Microsoft SQL Server 2014 CTP1 release on Windows Azure VM is only intended for functional development and testing, and not for performance testing.
  8. · The Microsoft SQL Server 2014 CTP1 release contains branding and versioning from older Microsoft SQL Server releases.

I actually tried installing this on my laptop which was already having all version of SQL Server starting from 2005. Same as the previous installations the Setup Support Rules screen is there in SQL Server 2014 installation media which detected the previous installation of SQL Server on my laptop & stopped my attempt of installing something which is against the rules.

clip_image002

After clicking on the Hyperlink “Failed” I got the complete error message in a dialog box – same as earlier versions.

clip_image003

So to fulfill these rules I created a new Virtual Machine to install and test out these features – I’ll be posting all those steps in my next blog – till then stay tuned and Happy Learning J

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Blogs in your Inbox or Follow on twitter @SQLDB

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

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