SQL Clone
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
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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 Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: General Forum Members
Points: 224933 Visits: 46321
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 Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215478 Visits: 41979
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

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

Group: General Forum Members
Points: 12 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 Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: General Forum Members
Points: 224933 Visits: 46321

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
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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 Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: General Forum Members
Points: 224933 Visits: 46321
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
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4060 Visits: 3436
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
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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 Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215478 Visits: 41979
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

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