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 12»»

Strange Behavior with (nolock) Expand / Collapse
Author
Message
Posted Monday, October 21, 2013 2:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, July 26, 2014 1:10 PM
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.
Post #1506875
Posted Monday, October 21, 2013 2:49 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 40,175, Visits: 36,573
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 2008, MVP
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

Post #1506878
Posted Monday, October 21, 2013 4:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 35,348, Visits: 31,887
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1506906
Posted Monday, October 21, 2013 6:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, July 26, 2014 1:10 PM
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?

Post #1506931
Posted Tuesday, October 22, 2013 12:33 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 40,175, Visits: 36,573
DBCC CheckDB (<database name>) 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 2008, MVP
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

Post #1506972
Posted Tuesday, October 22, 2013 8:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, July 26, 2014 1:10 PM
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....

Are my tools borked? I'm going to re-install...
Post #1507166
Posted Tuesday, October 22, 2013 8:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 40,175, Visits: 36,573
josh 92884 (10/22/2013)
CHECKDB found 0 errors and repaired 0 errors.


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....


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 2008, MVP
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

Post #1507173
Posted Tuesday, October 22, 2013 8:28 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 941, Visits: 2,936
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
Post #1507179
Posted Tuesday, October 22, 2013 9:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, July 26, 2014 1:10 PM
Points: 4, Visits: 29
GilaMonster (10/22/2013)
josh 92884 (10/22/2013)
CHECKDB found 0 errors and repaired 0 errors.

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....

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.

Post #1507218
Posted Tuesday, October 22, 2013 3:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 35,348, Visits: 31,887
GilaMonster (10/22/2013)
DBCC CheckDB (<database name>) 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1507387
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse