Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

While, variable and table with nolock Expand / Collapse
Author
Message
Posted Tuesday, February 19, 2013 4:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 2:17 AM
Points: 56, Visits: 518
Hello!

When I looping a variable from a table with nolock hint in the while, then the execution time is bigger.
Why?
Here is the script:


use tempdb
go

set nocount on;
set xact_abort on;
go

if OBJECT_ID('tempdb..tmp_zs_456') is not null drop table tmp_zs_456
go
create table tmp_zs_456 (id int primary key identity(1,1), szam int default 0)
go


declare
@id int = 0,
@from_id int,
@to_id int = 10000,
@end_time datetime,
@start_time datetime

while @id < 1000000 begin
set @id += 1
if @@TRANCOUNT = 0 begin tran
insert into tmp_zs_456 default values
if @id % 5000 = 0 AND @@TRANCOUNT <> 0 commit
end

if @@TRANCOUNT <> 0 commit

-- Loop 1: the execution time is ok.
select @start_time = GETDATE(), @from_id = 0
while @from_id < @to_id begin
select top 1 @from_id += 1 from tmp_zs_456
--select top 1 @from_id += 1 from sys.databases
end

set @end_time = GETDATE()

select DATEDIFF(millisecond, @start_time, @end_time) as execution_time_without_nolock

-- Loop 2: the execution time is bigger. Why?
select @start_time = GETDATE(), @from_id = 0
while @from_id < @to_id begin
select top 1 @from_id += 1 from tmp_zs_456 with (nolock) -- Here is the execution time is bigger.
--select top 1 @from_id += 1 from sys.databases with (nolock) -- This is ok.
end

set @end_time = GETDATE()

select DATEDIFF(millisecond, @start_time, @end_time) as execution_time_with_nolock


Thanks
Post #1421549
Posted Tuesday, February 19, 2013 4:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:30 AM
Points: 2,372, Visits: 7,559
Because nolock is not "go faster stripes" ? More seriously, probably because the nolock hint is performing a scan which needs to read more pages due to reading in allocation order.

Check out these links for more reasons not to use nolock:
Missing rows with nolock
Allocation order scans with nolock
Consistency issues with nolock
Transient Corruption Errors in SQL Server error log caused by nolock
Dirty reads, read errors, reading rows twice and missing rows with nolock



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1421558
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse