SQL Server : Dedicated Administrator Connections

Think about a scenario : People are complaining about your production database server.Many users are getting continuous time out error. Rest of the users are not able to connect database server.When you tried to  run your diagnostic query , either you are not able to connect or it is not returning the result for your diagnostic queries. At this moment most of us will tend to restart the SQL server for the quicker resolution. The dedicated administrator connection (DAC) will help us in this kind of situation.

How DAC helps in this situation ?

Sql server will not not respond to queries if there is scheduler dead lock or all the resource are utilized by another connections. There will not be enough free resource to process your request and requests will be keep waiting for resources. As explained in the earlier post, in this scenario all scheduler will have longer queue,but in sql server we have dedicated scheduler only to process the request comes through the dedicated admin connection.SQL server provides DAC as special diagnostic connection for administrator when standered connection to the server are not possible.This should be considered as the last resort like 'Fire exit'. Do not tempted to misuse this privilege. This is just one thread and there is no parallelism for queries running under the DAC connection. DAC is not designed for high performance, so do not try to schedule any job or to run your day to day queries.

How to enable DAC ?

By default DAC is enabled to connect through local connection. That means you can connect to the local instance of SQL server using DAC with out making any changes. To connect the SQL server using DAC from remote machine using TCP/IP, we have to enable  the 'remote admin connections'  using sp_configure. We can enable the remote DAC connection using the below query.

EXEC sp_CONFIGURE 'remote admin connections',1
GO
RECONFIGURE



By default SQL server listen to DAC connection on port number 1434. If the port number 1434 is not available ,SQL server dynamically assign a port number during the start up and this can be found in the SQL server error log as given below.









if you have firewall between remote machine and SQL server , we might need to open the DAC port in the firewall to establish the DAC connection from the remote machine.

Limitation of of using DAC connection

• Only one DAC connection is allowed per instance.If a DAC connection is already open, new connection request will be denied with error 17810.
• You can't  connect SSMS object explorer using the DAC connection, but you can connect a query window.
• SQL server prohibits running parallel queries or commands on DAC connection.Error 3637 is generated if you try to perform a backup or restore operation.
• Only login with sysadmin rights can establish the DAC connections.

How to establish  DAC ?

You can use either SQL server management studio or SQLCMD to establish a DAC connection.If SQL browser is running, you can use 

ADMIN:SERVERNAME\INSTANCENAME 

in the server name of SSMS. The prefix ADMIN prompt the SQL server browser service to find out the DAC port of that instance. if your are aware about the port number used by the DAC you can use SERVERNAME\INSTANCENAME,1435 

where 1435 is the port number where SQL server listen to the DAC connection. This number might change from instance to instance. You can find out the port number that listen to DAC from the SQL server error log as mentioned earlier.

In the same way to connect using the SQLCMD

SQLCMD -S SERVERNAME\INSTANCENAME -U sa -P Password12 -A 

Where -A prompt the SQLCMD to connect using the DAC port. If you know the DAC port of the instance, you can use 

SQLCMD -S SERVERNAME\INSTANCENAME,1435 -U sa -P Password12 

If you liked this post, do like my page on FaceBook 

Windows Cluster: Moving Quorum Disk to Another Node

In my earlier post I have explained about various quorum settings available in Microsoft windows 2008 cluster environment. While we plan to perform the maintenance in a cluster environment ,as per the design, it is not mandatory to move  the resources and witness disk manually  to another node. While cluster services is stopping on a node, resources owned by that node will automatically be arbitrated to another node in the cluster. 

I personally do not prefer this approach.I prefer to move all the resource manually to another node while we planning to perform maintenance in cluster environment. SQL instances, MSDTC and any other application/services can be moved using the UI, but to move the witness disk we have to rely on the command prompt.

Open a command prompt and type cluster group ,it will list all the group available in the windows cluster as shown in the below picture.

Fig 1

From the Fig-1 you can see SQL server groups,MSDTC group and other two groups.These two groups(available storage and cluster group) are created by default in windows 2008 cluster. The quorum disk resides in the group 'Cluster Group'. In the Fig-1 the 'cluster group' is owned by the node5. To move it other node we have run below command.

cluster group "Cluster Group" /move 

In you have more nodes in your cluster and to move the 'cluster group' to a specific group use the below command.

cluster group "Cluster Group" /moveto:node5

If you liked this post, do like my page on FaceBook

Windows Cluster : Understanding the Quorum settings

In my earlier post , I have explained about the windows cluster and how Sql server works on cluster environment. In this post let us try to understand the quorum settings of windows cluster environment. When I say quorum, do not interpret as quorum disk. Quorum has literal meaning in the cluster environment. In this post I will use the word witness disk to refer the quorum disk.Let us see what are all the possible quorum settings and how it will affect the windows cluster.

What is a quorum ?

As per Wikipedia, quorum is the minimum number of members of a deliberative assembly necessary to conduct the business of that group. In short quorum is minimum number of votes required for majority.As I explained in my earlier post, the nodes participating in the windows cluster are connected through a private network and communicate through User Datagram Protocol (UDP) port 3343.The quorum configuration in a failover cluster determines the number of failures (failure of nodes) that the cluster can sustain while still remain online. If additional failure happened beyond this threshold, the cluster will stop running.Quorum is designed to handle the Split Brain scenario. When nodes are unable to communicate each other, each node assume that, resource groups owned by other nodes have to brought online. When same resource brought online on multiple nodes at the same time,data corruption can occur. This scenario is called Split Brain.

Let us assume that we have four node cluster and one instance of sql server is running on each node. Node1 and Node2 lost the communication with Node3 and Node4. Node1 and Node2 can communicate each other and  Node3 and Node4 can communicate each other. In this scenario each group does not know what happened to other  two nodes. Are they offline or just a communication failure ?. In this scenario, Node1 and Node2 try to bring online the Sql instance(resource) owned by Node3 and Node4. In the same way Node3 and Node4 will try to bring online the Sql instance (resource) owned by the Node1 and Node2, which will lead to disk corruption and many other issues.The windows cluster quorum setting is designed to prevent this kind of scenario.By having the concept of quorum, the cluster will force the cluster service to stop in one of the subsets of nodes to ensure that there is only one true owner for the particular resource group.

Voting

Having quorum (majority) is based on the voting algorithm where more than half of the voters must be online and able to communicate each other. The cluster knows how many node are used to form the the cluster and will know how many votes constitutes a quorum. If the number of votes drop below the majority, the cluster service will stop on the nodes of that group.Cluster requires more than half of the total votes to achieve the quorum.This is to avoid the tie in the number of votes. In a 8 node cluster , 5 voters must be online and able to communicate each other to have quorum. Because of this logic, it is recommended to always have an odd number of total voters in the cluster and the quorum setting define the the voters in a cluster.This does not necessarily mean an odd number of nodes is needed to form the cluster since both a witness disk (quorum disk) and a file share can contribute a vote, depending on the quorum settings.

Quorum Settings

Windows 2008 cluster supports four quorum models.

1 Node Majority 

2 Node and Disk Majority 

3 Node and File Share Majority

4 No Majority (disk only)

Node Majority: Node majority option is recommended for cluster with odd number of nodes.This configuration can handle a loss of half of the number of cluster nodes rounded off downwards. For example , a five node cluster can handle failure of two nodes. In this scenario three of the nodes (N1,N2,N3) can communicate each other but other two(N4 and N5) are not able to communicate. The group constituted by three node have the quorum (majority) and cluster will remain active and cluster service will be stopped on the other two nodes (N4 and N5). The resource group (sql server instance) hosted on that two nodes goes offline and come online on one of the three nodes based on possible owner settings.

Node and Disk Majority: This option is recommended for cluster with even number of nodes.In this configuration every node gets one vote and witness disk (quorum disk) gets one vote which makes total votes a odd number. The witness disk is a small ( approx 1 GB ) clustered disk.This disk is highly available and can failover between nodes. It is considered as part of the cluster core resource group.In a four node cluster, if there is a partition between two subsets of nodes, one of the subset will have witness disk and that subset will have quorum and cluster will remain online. This means that the cluster can lose any two voters,whether they are two nodes or one node and the witness disk.

Node and File Share Majority: This configuration is similar to the the Node and Disk Majority, but in this case the witness disk is replaced with a file share which is also known as File Share Witness Resource (FSW). This quorum configuration usually used in multi-site clusters (nodes are in different physical location) or where there is no common storage. The File Share Witness resource is a file share in any server in the same active directory which all the cluster nodes have access to. One of the node in the cluster will place a lock on the the file share to consider that node as owner of the file share.When this node goes offline or lost the connectivity another node grabs the lock and own the file share.On a standalone sever, the file share is not highly available , however the file share can also put on a clustered file share on an independent cluster,making the FSW clustered and giving it the ability to fail over between node. It is important that, this file share should not put in a node of the same cluster, because losing that node would cause for loosing two votes. A FSW does not store cluster configuration data like witness disk. It contain information about which version of the cluster configuration database is most recent.

No Majority (Disk only) : This configuration was available in windows server 2003 and has been maintained for compatibility reason and it is highly recommended not to use this configuration. In this configuration,only witness disk has a vote and there are no other voters in the cluster. That means if all nodes are online and able to communicate , but when witness disk failed or corrupted, the entire cluster will go offline.This is considered as single point of failure.

Hope you got a fair idea about various quorum settings available in windows 2008 cluster.

If you liked this post, do like my page on FaceBook

SQL Server: Instant Deadlock Alert Using WMI in Your Mailbox

In my last post I have explained,how to setup alert for blocking using WMI. In this post let us see how to set up an alert for dead lock, which will help us to trouble shoot the dead lock scenarios.

As I explained in the last post, we need to do a configuration change in SQL server agent. Right click on the agent node , select properties. On the left pane select Alert System. On bottom of that page, tick the check box of Replace tokens for all job responses to alert. Restart the SQL server agent service.Read more about the this on MSDN.

The next step is create a simple table in one database.In all our environment we have a database to implement the administrative tasks. This table contains only two fields .A date column to store date and time of deadlock and a Xml column to store the deadlock graph. The script to create this table is available here (The first part). Create Procedure DBA_Deadlock_graph using the script available in the second part of the above mentioned script.

The next step is to create a new job.The specialty of this job is, it does not have a schedule.Let us create a job namely DBA_DeadLock_Graph. In the step add the last part of the script mentioned above.

The final step is to create an alert . Refer below screenshot to create the alert. Mention an appropriate name for the alert. Select alert type as WMI event alert.In the name space add  

\\.\root\Microsoft\SqlServer\ServerEvents\INSTANCENAME . 

Replace the instancename with your instance name. 

In the query section add 

SELECT * FROM  DEADLOCK_GRAPH 

In the response page tick the Execute job check box and select the job that we have created earlier.

Now everything is set. Create a deadlock and wait to get the alert in your mail box. In case if your are not receiving the alert follow below point to troubleshoot.

• Check alert history and see number of occurrences. If it is still zero either deadlock is not occurred or there is some problem with WMI. Check your WMI service status. if it is running , restart the WMI service. 
• If the number of occurrence is greater than zero, check the table DeadLockEvents. If there is no entries, the token replacement may not be happening. Check the Replace tokens for all job responses to alert of SQL server agent properties. Make sure that you have restarted the SQL Server agent service after making this change. 
• If there is an entry in the DeadLockEvents table, there is something wrong with your database mail configuration /mailbox. Check the database mail is working and you have mentioned the correct profile name in the procedure DBA_Deadlock_Graph. 

Hope this will help you to implement custom deadlock alert in your environment. 

If you liked this post, do like my page on FaceBook

SQL Server: Instant Blocking Alert Using WMI in Your Mailbox

As a DBA, it is important for all of us to get real time alert on various issues in our inbox to be more proactive and to manage the database servers in better way.There are many third party tools which you can easily configure to monitor the servers and to get the alerts. I feel more happy when we implement the required alert by our self to get the alert. we have implemented many custom alerts in our environment. I will share the important ones in this blog.

There are many method that you can adopt to implement custom alert. I have done this using WMI (Windows Management Instrumentation) .WMI is very powerful one to monitor the performance of the servers and application.

Blocking will happen when one process need  to wait for a resource(example lock) which currently using by another process. The first process has to wait till  the second process completes its action and release the resources.

Let us see how we can set up the alert for blocking. The first step to create blocking alert is configure the blocked process threshold values.This is server wide configuration which can be set using sp_configure.The value of blocked process threshold specify the threshold, in seconds,at which blocked process reports are generated.By default no blocked process reports are produces.In simple words, if the value configured as 30 second, we will get alert when a process is blocked for more than 30 seconds. This can be done using the below code.

EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'blocked process threshold (s)',30
GO
RECONFIGURE WITH OVERRIDE
Another configuration change that we need to do in the SQL server agent. Right click on the agent node , select properties. On the left pane select Alert System. On bottom of that page tick the check box of Replace tokens for all job responses to alert. Read more about the this on MSDN.Restart the SQL server agent service.

The next step is create a simple table in one database.In all our environment we have a database to implement the administrative tasks. This table contains only four fields .One identity column, a date column to store date and time of blocking,a Xml column to store the blocked processed report and a integer column to store the blocking process spid. The script to create this table is available here

The next step is to create a new job.The specialty of this job is, it does not have a schedule.Let us create a job namely DBA_BlockedAlert. In the step add the second part of the script mentioned in here

The final step is to create an alert . Refer below screenshot to create the alert. Mention an appropriate name for the alert. Select alert type as WMI event alert.In the name space add

\\.\root\Microsoft\SqlServer\ServerEvents\INSTANCENAME . 
Replace the instance name with your instance name. 
In the query section add SELECT * FROM BLOCKED_PROCESS_REPORT

In the response page tick the Execute job check box and select the job that we have created earlier.

Now everything is set. Create a blocking and wait for 30+ seconds to get the alert in your mail box. In case if your are not receiving the alert follow below point to troubleshoot.

• Check alert history and see number of occurrences. If it is still zero either blocking is not passed 30 seconds or there is some problem with WMI. Check your WMI service status. if it is running , restart the WMI service. 
• If the number of occurrence is greater than zero, check the table BlockedEvents. If there is no entries, the token replacement may not be happening. Check the   Replace tokens for all job responses to alert of SQL server agent properties. Make sure that you have restarted the SQL Server agent service after making this change.
• If there is an entry in the BlockedEvents table, there is something wrong with your database mail configuration /mailbox. Check the database mail is working and you have mentioned the correct profile name in the job step.

Hope this will help you to implement custom blocking alert in your environment.

If you liked this post, do like my page on FaceBook 

SQL SERVER : SQL Server Scheduler

While troubleshooting the performance issues of SQL server, you might have seen the sessions are in different state like SUSPENDED,RUNNING,RUNNABLE etc. In this post let us try to explain this by going through the threading model of SQL server.

SQL server is like a operating system like windows.It has its own mechanism  to handle the memory management, IO and thread scheduling. SQL server might be using the windows API to do these task but still SQL server control how it works for it. 

Thread

 Thread is the lightweight process or it is part of process.For example running simple select statement can be considered as a process and it might  run in the processor as a single thread. Where as a complicated select statement (process) which returns thousands of rows might split into multiple threads which will run on different processor to complete the process much faster.Each thread will get a small amount of time in the processor,then it move out of the processor to give processor time to other thread and it has to wait for the processor time to complete it pending task. The process of moving threads to the processor and moving out of the processor is called as scheduling. This will give an impression that many process are happening but in reality only only one thread can be executed in the processor at any point of time.

SQL Server uses operating system thread to perform its action and it is called as worker thread.There are dedicated thread and pool of threads. There are dedicated thread for check point , dead lock monitoring,etc. The pool of thread is used to process the user request.

Thread Scheduling 

As mentioned earlier, SQL server controls its thread scheduling and it use non-preemptive scheduling  and windows can not interrupt these threads.When SQL server managing the scheduling of its thread instead operating system, it  has more control and it make  its own priority.The thread scheduling inside the SQL server done by the SQLOS which is an interface between the SQL server and operating system.Each processor core (logical or physical) which allowed to use by SQL server has a scheduler.For example in a machine with two physical core, with hyper threading enabled, will have four scheduler.There are scheduler for user threads and for internal operation.For some processor can have two scheduler one for user request and other one for internal for database engine. The list of schedulers can be seen by querying the DMV sys.dm_os_schedulers. The offline schedulers are mapped to the processors that are offline in the affinity mask and not being used to process any request. Visible schedules are used to process the user request where as hidden schedulers are used to process the internal request. There is one dedicated scheduler to process the DAC requests.When one of thread needs something from OS(calling a windows API) , that thread has to switch to preemptive mode which enable OS to control when that thread goes to sleep or any other state of the thread.

Scheduler

A schedule has three important component, Processor,Waiter List and Runnable queue. A Processor is the actual processor which process the thread one at a time.Waiter List is the list of threads waiting for resources.Runnable Queue is a queue of threads which has all the resource it need to process but waiting for its turn to get into the processor.Scheduler put the thread in to Runnable Queue then move the thread to Processor and migrate to Waiter List. Each thread keeps going through these three component until the thread works completes. 


The waiter list is a list of threads which are suspended and waiting for a resource. This is not a queue as there is no order in which the thread will get the resource.There is no parameter which define the maximum time a thread can be in the waiter list. Theoretically there is no limit but the timeout specified in the query execution session may take effect. While waiting in the Water List , the thread might get canceled due to the execution time out.The Waiter List can be examined by querying the DMV sys.dm_os_waiting_tasks.


The Runnable queue is a pure First-In-First-Out (FIFO) queue.When a thread moves from Waiter List it joins at the bottom of the Runnable queue. We can see the size of runnable queue by looking into the column of runnable_tasks_count column in sys.dm_os_schedules.There is special case when resource governor enabled and relative priorities assigned to multiple workload group for a resource pool.Possible values for priorities are High,Medium and Low which equate to 9,3 and 1. It means that 9 high priority thread and 3 Medium priority thread can override a low priority thread in the Runnable queue.

State of threads

A thread can have three states , Running ,Suspended and Runnable. Running is the state where the thread is currently in the Processor and utilizing the CPU. Only one thread per scheduler can have this state as the Processor can process only one thread at a time.When a thread need a  resource to process further it has to wait for the resource.The thread will be move to Waiter List and thread changes the state from Running to Suspended.When required resources are available after a period of time, nothing is stopping the thread from running.But eventually it has to wait for its turn in the Processor. The process that tells the thread that resources are available is called signaling. When resources are available for a thread (thread is signaled) which was in suspended mode it will move to the Runnable queue and wait for its turn with a state called Runnable.When the thread moves to processor it change the state from Runnable  to Running.The transition between these states keep happening till the thread completes its work.
There is case where thread by pass the Suspended state and directly move to the Runnable from Running and this is called quantum exhaustion.If a thread does not need to wait for any resources, it will continue to run till its quantum is exhausted. The quantum is fixed to 4ms and not configurable. The last column of the DMV sys.dm_os_schedulers define this value. Even if the thread does not need to wait for any resources,after the completion of its quantum time, it will move out of the processor and its state change from Running to Runnable. The thread move directly from Processor to bottom of Runnable queue bypassing the waiter list as it does not need to wait for a resource.

Below script shows the relationship between various DMV

SELECT
   [dot].[scheduler_id],
   [task_state],
   COUNT (*) AS [task_count]FROMsys.dm_os_schedulers dos INNER JOIN  sys.dm_os_workers  dow ON dos.scheduler_address=dow.scheduler_addressINNER JOIN  sys.dm_os_tasks AS [dot] ON dot.task_address=dow.task_addressINNER JOIN  sys.dm_exec_requests AS [der]    ON [dot].[session_id] = [der].[session_id]INNER JOIN  sys.dm_exec_sessions AS [des]    ON [der].[session_id] = [des].[session_id]WHERE [des].[is_user_process] = 1 GROUP BY
   [dot].[scheduler_id],
   [task_state]ORDER BY [task_state],[dot].[scheduler_id]





If you liked this post, do like my page on FaceBook 




  
  
  
  

SQL SERVER:How Can I Get Notification Alert When Fail Over Happened ?

It was long time I was searching for simple solution to get notification alert in my inbox when ever fail over happened in the cluster environment. SCOM will help us to do that , but I do not have direct control over it and the mail first goes to the Infrastructure support team and they forward the alert to the respective team.

Let us see how  DBA's can schedule an email alert with out depending on the SCOM or any other third party tool. When  fail over happen, both SQL server and Agent service will get restarted.In sql server job scheduler, there is an option to schedule a job when SQL server agent starts. For that we have to select the schedule type as "Start Automatically when sql server agent start".

To get an alert when a fail over happened, create a job with following code in the job step

DECLARE @importance AS VARCHAR(6)
DECLARE @body AS NVARCHAR(1000)
DECLARE @Subject  AS NVARCHAR(1000)
DECLARE @InstanceName AS VARCHAR(100)DECLARE @NodeName AS NVARCHAR(100)
DECLARE @recipientsList VARCHAR(100)SELECT  @recipientsList ='abc@yahoo.com,xyz@gmail.com'
SELECT  @InstanceName =@@SERVERNAME
SELECT  @NodeName = CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NVARCHAR(200))
SELECT  @Subject  = 'Fail over Happened for Instance '+@InstanceName
SELECT  @body = 'Fail over Happened for Instance '+@InstanceName + '. This instance is currently running on the node '+@NodeName
SELECT  @importance ='High' 
EXEC msdb.dbo.sp_send_dbmail
     @profile_name ='MyMailProfile',
     @recipients=@recipientsList,
     @subject = @subject ,
     @body = @body,
     @body_format = 'HTML' ,
     @importance=@importance


In the schedule pane select schedule Type as "Start Automatically when sql server agent start".

Do this on all instances of the cluster and it is ready . Wait for next fail over. Mail will be there in your inbox.Please be careful that you will get a mail even if the instance got restarted in the same instance or you just stopped and started the agent service. But that can be easily ignored or can be avoid by tweaking the above code little bit.

If you liked this post, do like my page on FaceBook at http://www.facebook.com/practicalSqlDba

SQL SERVER : The Scene Behind NOLOCK

I have heard many time people talking about the NOLOCK hint. Some of them says it improves the performance and some of them says it reduce the blocking and deadlock as it will not acquire any locks. Let us see what is happening when we use NOCLOCK table hint in queries.

Let us see a query with out NOLOCK table hint and analyse the lock acquired by that query.

SELECT *FROM Sales.SalesOrderHeader a CROSS JOIN Sales.SalesOrderHeader b

The lock acquired by this session can be easily found using the below query

SELECT ResourceName = CASE resource_type
            WHEN 'database' THEN DB_NAME(resource_database_id)
            WHEN 'object' THEN OBJECT_NAME(resource_associated_entity_id, resource_database_id)
            ELSE ''
            END,request_mode,request_status, * FROM sys.dm_tran_locksWHERE request_session_id = 53




While analyzing the result, we can see a shared lock on database level. That is nothing to do with the NOLOCK  hint. While opening a connection to the database, it will always take shared connection on database used by that connection to make sure that other sessions will not drop the database while in use.

Next we can see an Intent Shared(IS) lock on the table level.An intent lock indicates that SQL Server wants to acquire a shared (S) lock or exclusive (X) lock on some of the resources lower down in the hierarchy. For example, a shared intent lock placed at the table level means that a transaction intends on placing shared (S) locks on pages or rows within that table. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive (X) lock on the table containing that page.This IS lock also make sure that this table will not be modified till the select statement complete the operation.Next you an see a shared lock on page. This is to make sure that the data in the page are not getting modified while reading the data.

Let us see how it will work along with an update statement.

BEGIN TRAN

UPDATE Sales.SalesOrderHeader SET status=5 WHERE SalesOrderID=43659

Now  run the select statement and lock analyzing script. We can see that the request for Share Lock on page  is in Wait status as it is conflicting with the Intent exclusive lock acquired by the update session on the page. This helps sql server to avoid the dirty read but it cause for blocking. You can see that the select statement is  blocked by the update statement.

Let us see the same with NOLOCK hint

SELECT *

FROM Sales.SalesOrderHeader a WITH (NOLOCK)
CROSS JOIN Sales.SalesOrderHeader b WITH (NOLOCK)  
In this case we can see only shared schema lock on the table. It is not taking a shared lock on the page and this lead to a dirty read.The shared schema lock om table level is important to make to sure that the schema of the table is not getting changed while reading the data. Let us try this select statement after the update statement and still the select statement will run without blocking as it is not trying to acquire the shared lock on pages and it also cause for the dirty read.

The READ UNCOMMITTED isolation level also works in the same way. Instead of specifying the table hint for each table we can set the isolation level to READ UNCOMMITTED. Theoretically NOLOCK hint improve the performance slightly as it  need to acquires less lock compared with statement does not have a NOLOCK hint.Be careful with the usage of NOLOCK hint as it perform dirty read and may give undesired result.

If you liked this post, do like my page on FaceBook at http://www.facebook.com/practicalSqlDba