Sql-server – What’s the least risky way to Select on production environment

performancequery-performancesql serversql-server-2008

My goal is to use selects on production without affecting anything else.

When I was new to SQL, I just wrote simple selects, but later I learned this can cause locks.

If I don't care about dirty reads, I can use nolocks on every select or set transaction isolation level to read uncommited.

But I do care about dirty reads, so I went with isolation level snapshot.
Isolation level snapshot writes into tempdb and I don't know if that's a problem or not (extra writing to database means slower DB and less space?)

So what is the least intrusive way to write a select on DB, where I cannot afford slowing it down or creating shared locks? Should I go back to isolation level read uncommited on every query?

This is asked from developer point of view. I don't know details about DB settings, but want to use selects in a way that will cause the least trouble.

Best Answer

The correct answer will depend on a number of things:

The size of your database.
The size of your buffer pool compared to your database.
The volume and nature of the background (ie: not you) query activity.
The nature of the queries you want to run.
What will you bottleneck on first (CPU, I/O, tempdb throughput).
How much extra latency you are willing to tolerate in your normal workload.

You should always get an estimated query plan and look for intensive operations, such as scans of large tables/indexes, or sorts of a large number of rows. One of the most important considerations when working with a live, heavily used database is to avoid driving pages out of the buffer pool by running a query that needs to bring a lot of data into cache.

If you are concerned about your select queries going parallel and consuming excessive CPU, you can limit your query to a single core by adding OPTION (MAXDOP 1) to the end of your query.

If you don't have Read Committed Snapshot enabled on your database, see if you can get that turned on. That will give you snapshot-like behaviour on your selects, so that you will not block writers. However if you are running long ad-hoc queries and there is a lot of write activity, you'll get a lot of data written to tempdb.

You could also investigate using Resource Governor to limit yourself.

Related Solutions

Sql-server – Shared Lock issued on IsolationLevel.ReadUncommitted

What is HOBT lock?

A lock protecting a B-tree (index) or the heap data pages in a table that does not have a clustered index.

Why would I still get a S lock?

This happens on heaps. Example

SET NOCOUNT ON;

DECLARE @Query nvarchar(max) = 
   N'DECLARE @C INT; 
     SELECT @C = COUNT(*) FROM master.dbo.MSreplication_options';

/*Run once so compilation out of the way*/
EXEC(@Query);

DBCC TRACEON(-1,3604,1200) WITH NO_INFOMSGS;

PRINT 'READ UNCOMMITTED';
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
EXEC(@Query);

PRINT 'READ COMMITTED';
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
EXEC(@Query);

DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;

Output READ UNCOMMITTED

Process 56 acquiring Sch-S lock on OBJECT: 1:1163151189:0  (class bit0 ref1) result: OK

Process 56 acquiring S lock on HOBT: 1:72057594038910976 [BULK_OPERATION] (class bit0 ref1) result: OK

Process 56 releasing lock on OBJECT: 1:1163151189:0

Output READ COMMITTED

Process 56 acquiring IS lock on OBJECT: 1:1163151189:0  (class bit0 ref1) result: OK

Process 56 acquiring IS lock on PAGE: 1:1:169 (class bit0 ref1) result: OK

Process 56 releasing lock on PAGE: 1:1:169

Process 56 releasing lock on OBJECT: 1:1163151189:0

According to this article referencing Paul Randal the reason for taking this BULK_OPERATION shared HOBT lock is to prevent reading of unformatted pages.

Sql-server – SQL Server 2008 R2 Dirty reads – how non-atomic

EDITED after reading the MSDN forum link from the comment, very interesting.

Regardless of isolation level, two users cannot update a single page simultaneously, nor can any user read a partially updated page. Just imagine how SQL Server would deal with a page where the header says Col3 starts at byte 17. But it really starts at byte 25, because that part of the row hasn't been updated yet. There's no way a database could handle that.

But for rows larger than 8k, multiple pages are used, and that makes a half-updated column possible. Copied from the MSDN link (in case the link breaks), start this query in one window:

if object_id('TestTable') is not null
    drop table TestTable
create table TestTable (txt nvarchar(max) not null)
go
insert into TestTable select replicate(convert(varchar(max),
    char(65+abs(checksum(newid()))%26)),100000)
go 10
update TestTable set txt=replicate(convert(varchar(max),
    char(65+abs(checksum(newid()))%26)),100000)
go 100000

This creates a table and then updates it with a string of 100.000x the same character. While the first query is running, start this query in another window:

while 1=1 begin
 if exists (select * from TestTable (nolock) where left(Txt,1) <> right(Txt,1))
    break
end

The second query stops when it reads a column that is half updated. That is, when the first character is different from the last. It will finish quickly, proving that it is possible to read half-updated columns. If you remove the nolock hint, the second query will never finish.

Surprising result! A half-updated XML column might break a (nolock) report, because the XML would be malformed.

Best Answer

Related Solutions

Sql-server – Shared Lock issued on IsolationLevel.ReadUncommitted

Sql-server – SQL Server 2008 R2 Dirty reads – how non-atomic

Related Question