SQL Tact

Actual Emails: Is "NOLOCK is the epitome of evil?"

2012-01-21T09:55:00.001-06:00

Sent: Friday, January 20, 2012 10:30 AM
To: William Assaf
Subject: dba has a question

William,

Can you send me references to support my contention that NOLOCK is the epitome of evil?

That is, that it can return invalid and/or unrelated data.

He is still suggesting that folks use NOLOCK (rather than READPAST).

We have to work around other poorly written queries, so some of these are inevitable.

Thanks,

[my colleague's name redacted to protect the innocent]

--------------------------------------------------------------------------------------------

The basic danger is that with NOLOCK, the data can change out from underneath the query while it is in progress. This doesn't require the query to run for a long time, it could happen even on simple queries.

He should know that SQL doesn't read data out of a database like an excel spreadsheet, it reads a series of addresses all across a hard disk. Without even a shared read lock, the data in those addresses can change out from underneath a query using NOLOCK or READ UNCOMMITTED (same thing, different keywords.)

The more appropriate and sometimes-used term for this isolation level is CHAOS. You could (and there are labs out there to prove this) end up with blank data, malformed bytes, or worst - wrong data. The likelyhood is low admittedly, but the potential for data corruption is misreporting is not something that should be invited.

I would be strongly against using this isolation level on any query that could be attached or provide source data from an update/insert/delete statement, which would commit the bad data. Most select statements are the basis for some update, unfortunately.

Perhaps on a query that is used frequently on a constantly-refreshing dashboard would be a situation in which NOLOCK would be appropriate. But then, there are better, safer alternatives like SNAPSHOT isolation mode, or better architectural decisions to make data more available for read-heavy queries.

Good examples and info here:

http://blogs.msdn.com/b/craigfr/archive/2007/06/12/query-failure-with-read-uncommitted.aspx

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx

http://summit2009.sqlpass.org/Agenda/PrePostConferenceSessions/BenGanQA.aspx

http://blogs.msdn.com/b/sqlcat/archive/2007/02/01/previously-committed-rows-might-be-missed-if-nolock-hint-is-used.aspx

http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

-William

View execution plan for a sproc in the wild

2011-12-16T16:19:00.001-06:00

Got stored procedure problems? I feel sorry for you son.

Here's a quick query to pull the cached execution plan out of memory.

I italicized cached because it's not going to stay there forever.

I used this query to find the execution plan of a stored proc that had been running in a SQL job. Instead of pulling down the sproc and trying to estimate the query plan, I chose to see what the cached plan looked like.

select eqp.query_plan
from sys.objects o
inner join sys.dm_exec_procedure_stats eps on eps.object_id = o.object_id
cross apply sys.dm_exec_query_plan (eps.plan_handle) eqp
where o.name = 'PoorPerformingProcedure'

Run this in SQL Management Studio and click on the "hyperlinked" XML in the results window, and you'll find the execution plan displaying just like it would if you'd hit Ctrl-L.

What else can you do with this?

How about finding the top 10 longest running execution plans of stored procs in cache?

select top 10 eqp.query_plan

from sys.objects o

inner join sys.dm_exec_procedure_stats eps on eps.object_id = o.object_id

cross apply sys.dm_exec_query_plan (eps.plan_handle) eqp

order by eps.total_worker_time desc

How about the top 10 most-frequently executed stored procedure plans in cache, in the current database?

select top 10 eqp.query_plan

from sys.objects o

inner join sys.dm_exec_procedure_stats eps on eps.object_id = o.object_id

cross apply sys.dm_exec_query_plan (eps.plan_handle) eqp

where eqp.DBID = DB_ID()

order by eps.execution_count desc

Open_transaction_count in sys.dm_exec_sessions

2011-12-16T16:08:00.001-06:00

This news may be a few months old, but it is worth noting that there is now a column called open_transaction_count in the sys.dm_exec_sessions in SQL Server 2012.

This should put away any need for you to use sys.sysprocesses for session information.

Beware, it can cause an ambiguity in existing queries, as this column already exists in sys.dm_exec_requests for the number of open transactions in a request. It is pretty handy to join these DMV's together on session_id.

Here's an example of a query I use regularly (a shortened form of a previous post) which needed me to add the source alias for sys.dm_exec_sessions:

select

r.session_id

, s.host_name

, s.program_name

, r.status

, r.blocking_session_id

, DBName = db_name(r.database_id)

, r.command

, r.wait_type

, r.wait_time

, s.open_transaction_count

, r.cpu_time

, r.total_elapsed_time

, r.reads

, r.writes

, r.logical_reads

, est.[text]

, offsettext = SUBSTRING (est.[text], r.statement_start_offset/2 + 1,

CASE WHEN r.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), est.[text]))

ELSE r.statement_end_offset/2 - r.statement_start_offset/2 + 1

END)

, r.statement_start_offset

, r.statement_end_offset

from sys.dm_exec_requests r

inner join sys.dm_exec_sessions s on r.session_id = s.session_id

cross apply sys.dm_exec_sql_text (r.sql_handle) est

where s.session_id > 5

and s.session_id <> @@SPID

Using sys.dm_exec_requests to find a blocking chain

2011-10-19T05:00:00.002-05:00

A query that calls sys.dm_exec_requests and pulls the statement text out of the request is a handy script for any DBA's toolbox. A simple version of the query performs a cross apply to dm_exec_sql_text and pulls out the request's TSQL statement-in-progress using the unicode character offsets.

This is simple enough and does a good job as a queryable sp_who2 and DBCC INPUTBUFFER replacement to an extent.

In an environment that suffers from frequent spells of query blocking, the ability to identify which query was leading the way becomes important in finding the query or queries to troubleshoot.

Whether the end result of his query was a simple KILL command, and/or a walk down the hall to investigate the "business case" would be a situational decision.

I wanted a blocking chain - a list of all other spids that a session was blocking.

Here's a query that dumps the output of dm_exec_requests into a temporary table, then uses the XML PATH to conveniently transform the blocked_by list into a comma-delimited list in the row of the blocker. The sort order is the length of this comma-delimited list, putting the cause of a pileup right at the top.

Doing this in a temp table was necessary because the blocking_session_id is a highly volatile column. As a result, the output of this query is several ms behind "live". After a few executions, the spid hanging out at the top is easy enough to spot.

create table #ExecRequests (

id int IDENTITY(1,1) PRIMARY KEY

,session_id smallint not null

,request_id int

,start_time datetime

,status nvarchar(60)

,command nvarchar(32)

,sql_handle varbinary(64)

,statement_start_offset int

,statement_end_offset int

,plan_handle varbinary (64)

,database_id smallint

,user_id int

,blocking_session_id smallint

,wait_type nvarchar (120)

,wait_time int

,cpu_time int

,tot_time int

,reads bigint

,writes bigint

,logical_reads bigint

,[host_name] nvarchar(256)

,[program_name] nvarchar(256)

,blocking_these varchar(1000) null

)

insert into #ExecRequests (session_id,request_id, start_time,status,command,sql_handle,statement_start_offset,statement_end_offset,plan_handle,database_id,user_id,blocking_session_id,wait_type,wait_time,cpu_time,tot_time,reads,writes,logical_reads,host_name, program_name)

select r.session_id,request_id, start_time,r.status,command,sql_handle,statement_start_offset,statement_end_offset,plan_handle,r.database_id,user_id,blocking_session_id,wait_type,wait_time,r.cpu_time,r.total_elapsed_time,r.reads,r.writes,r.logical_reads,s.host_name, s.program_name

from sys.dm_exec_requests r

left outer join sys.dm_exec_sessions s on r.session_id = s.session_id

where 1=1

and r.session_id > 35 --retrieve only user spids

and r.session_id <> @@SPID --ignore myself

update #ExecRequests set blocking_these = (select isnull(convert(varchar(5), er.session_id),'') + ', '

from #ExecRequests er

where er.blocking_session_id = isnull(#ExecRequests.session_id ,0)

and er.blocking_session_id <> 0

FOR XML PATH('')

)

select

r.session_id, r.host_name, r.program_name, r.status

, r.blocking_these

, 'LEN(Blocking)' = LEN(r.blocking_these)

, blocked_by = r.blocking_session_id

, r.tot_time

, DBName = db_name(r.database_id), r.command, r.wait_type, r.tot_time, r.wait_time, r.cpu_time, r.reads, r.writes, r.logical_reads

, [text] = est.[text]

, offsettext = CASE WHEN r.statement_start_offset = 0 and r.statement_end_offset= 0 THEN null

ELSE

SUBSTRING (est.[text], r.statement_start_offset/2 + 1,

CASE WHEN r.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), est.[text]))

ELSE r.statement_end_offset/2 - r.statement_start_offset/2 + 1

END)

END

, r.statement_start_offset, r.statement_end_offset

from #ExecRequests r

outer apply sys.dm_exec_sql_text (r.sql_handle) est

order by LEN(r.blocking_these) desc, r.session_id asc

drop table #ExecRequests

Slides and Sample code: SQL Server Admin Best Practices with DMV's from the Houston TechFest 2011

2011-10-15T18:19:00.000-05:00

Here's my official blog page for my presentation on SQL Server Admin Best Practices with DMV's from the Houston TechFest 2011 on October 15. Thanks for attending my session in the of a great event put on by Michael and the entire Houston TechFest team. The University of Houston was a great host for the event!

Thanks also to all the sponsors who attendees. In the speaker room I heard some discussion of HTF's decision to have back-to-back Keynotes in the morning, not starting the actual sessions until 11AM and then breaking for lunch at noon. I have to agree with what I heard - the vendors paid the money to put this free event on, why not give them the freshest audience? As a veteran of three SQL Saturdays in Baton Rouge, I have to admit it was an uncommon but inventive scheduling decision. If it increases vendor satisfaction and retention for next year's event, nobody should mind, especially the attendees.

Download slides and sample code here.

You can give me a rating on SpeakerRate here.