Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Strange Behavior with (nolock)


Strange Behavior with (nolock)

Author
Message
josh 92884
josh 92884
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 29
First post here so hope I don't come of as too newb with this.

I have a table on a MSSQL 2000 with about 4700 rows.

My query:

SELECT * FROM Product with (nolock);


only comes back with about 250 rows, sometimes slightly less or more. This query also takes awhile to complete. Longer than these:


SELECT colname FROM Product with (nolock);
SELECT * FROM Product;


which come back with the correct number of records.

I'm a little mystified. What is it about the combination of * and NOLOCK that causes this?

Nothing unusual stands out to me about the table design. It is a clustered index with a few non-clustered indexes on non-key columns. DBCC SHOWCONTIG(Product) looks reasonable.

Anyone have any ideas? or next steps to investigate?

**EDIT - symptoms changed from original description. See my next post.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47198 Visits: 44366
Nolock means 'with potentially incorrect data', not usually this incorrect, but you should expect to not always get correct results when you use nolock. It's why it's not recommended for general usage.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45002 Visits: 39880
josh 92884 (10/21/2013)
First post here so hope I don't come of as too newb with this.

I have a table on a MSSQL 2000 with about 4700 rows.

My query:

SELECT * FROM Product with (nolock);


only comes back with about 250 rows, sometimes slightly less or more. This query also takes awhile to complete. Longer than these:


SELECT colname FROM Product with (nolock);
SELECT * FROM Product;


which come back with the correct number of records.

I'm a little mystified. What is it about the combination of * and NOLOCK that causes this?

Nothing unusual stands out to me about the table design. It is a clustered index with a few non-clustered indexes on non-key columns. DBCC SHOWCONTIG(Product) looks reasonable.

Anyone have any ideas? or next steps to investigate?



I've never seen WITH(NOLOCK) cause such a large disparity in the number of rows like that before. Any clues in the execution plan?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
josh 92884
josh 92884
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 29
Nothing groundbreaking in the plan. It shows a Clustered Index Scan -> Compute Scalar (I think it's resolving *)-> SELECT.

The Clustered Index Scan get 100% of cost. Estimated rows is correct.

One of the symptoms has changed. The following query is also short records now:


SELECT * FROM Product;



So possibly this problem is to do with the * wildcard? or one of the columns in the table?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47198 Visits: 44366

DBCC CheckDB (<database name>Wink WITH NO_INFOMSGS, ALL_ERRORMSGS



I'd still remove the nolocks, they definitely can cause you problems, by design they can result in incorrect data. As I said, not usually this incorrect though.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


josh 92884
josh 92884
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 29
thanks Gila

CHECKDB found 0 errors and repaired 0 errors. Took about 32 minutes on a 77GB db with reasonably modern hardware.

I hear you on NOLOCK. Based on what I'm reading about dirty reads, it's not going to be worth using.

I spread this issue around to some devs on my team. They aren't having any problems at all like I am.... Crazy

Are my tools borked? I'm going to re-install...
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47198 Visits: 44366
josh 92884 (10/22/2013)
CHECKDB found 0 errors and repaired 0 errors.


Sick I didn't say run it with a repair option...

I spread this issue around to some devs on my team. They aren't having any problems at all like I am.... Crazy


Sure you're looking at the correct table in the correct database on the correct server?

Are my tools borked? I'm going to re-install...


No.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Sean Pearce
Sean Pearce
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1170 Visits: 3432
GilaMonster (10/22/2013)
[quote]josh 92884 (10/22/2013)Sure you're looking at the correct table in the correct database on the correct server?

And the correct schema.



The SQL Guy @ blogspot

@SeanPearceSQL

About Me
josh 92884
josh 92884
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 29
GilaMonster (10/22/2013)
josh 92884 (10/22/2013)
CHECKDB found 0 errors and repaired 0 errors.

Sick I didn't say run it with a repair option...


I ran:


DBCC CheckDB (mydbname) WITH NO_INFOMSGS, ALL_ERRORMSGS



The results I saw are what showed up in the event log. Query Analyzer came back with 'The command(s) completed successfully'

I spread this issue around to some devs on my team. They aren't having any problems at all like I am.... Crazy

Sure you're looking at the correct table in the correct database on the correct server?


It's the only DB on that instance. This particular table is only found in the dbo schema. I've re-confirmed everyone is using the same server.

Are my tools borked? I'm going to re-install...

No.


I'll hold off for now. I know this is truly bizarre and I appreciate all the input so far.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45002 Visits: 39880
GilaMonster (10/22/2013)

DBCC CheckDB (<database name>Wink WITH NO_INFOMSGS, ALL_ERRORMSGS



I'd still remove the nolocks, they definitely can cause you problems, by design they can result in incorrect data. As I said, not usually this incorrect though.


Heh... to be sure, I absolutely agree. I wasn't suggesting to leave the nolocks in place. I've just never seen such a large problem with it before.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search