June 28, 2011 at 12:50 pm
select id
from table1 c with(nolock)
where (select max(creation_Date) from table2 cd with(nolock)
where cd.id = c.id ) < dateadd(year, -2, getdate()) ..........
Hello all,
this selection sometimes selects wrong data. Not always but it does it time to time. Its a long running query so i dont want to remove with(nolock).
And the odd part its selecting data where the hasnt changed past 2 days. So its not dirty data.
Any idea what am i doing wrong.
June 28, 2011 at 12:57 pm
Does the table get a lot of updates/inserts/deletes?
If so, page splits can cause exactly what you're talking about.
The best way to avoid that is dump NoLock and switch to using Snapshot Isolation. That can cause tempdb to grow, depending on update and select concurrency, but it won't give you the wrong-data issues that NoLock causes.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 28, 2011 at 1:03 pm
There might be an option to tune your query and or the indexes involved.
Is there any chance you could provide some ready to use table def (including indexes) and sample data as well as the current actual execution plan?
June 28, 2011 at 2:14 pm
Yea i am taking it out, sad part adding indexes right now isnt an option. The table has 50mil records 13gb of data 23gb of current indexes which takes 3 4 hours min to add a index. And the current state of the system cant take it down for that long. I will eventually will have to get around this but i will do multiple indexing and removals at one specific day to reduce down time.
Thanks for clearifying my suspisions.
Also i cant give my codes, sorry
June 28, 2011 at 2:26 pm
Have you tested Snapshot Isolation on it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 28, 2011 at 2:38 pm
No i havent i fear it might take too much space to do so. I will look into it tho.
June 28, 2011 at 2:40 pm
Test, don't assume. Depending on what else is running it could be heavy or virtually none, can't tell until you test (dev/test)
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
June 28, 2011 at 3:20 pm
How can i find out snapshot database size would be before creating it. There should be away to get a aprox amount. I dont want to run out of space. My database is 150gb and trans files gets pretty big which takes a lot of space on my server.
June 28, 2011 at 3:25 pm
None that I know of. It's TempDB, not log, not user DB. It has to store old versions of rows that change during your transaction, so if little changes the tempDB usage will be low. It's not based on your select, it's based on what modifications are running at the same time.
Run some tests in a dev/test environment first. That should give you a good ballpark figure assuming you have prod data and simulated load.
I've not seen it cause a bottleneck with the couple of clients I've seen using it, as long as TempDB is decently configured already.
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
June 29, 2011 at 6:39 am
Essentially, Snapshot Isolation stores row versions in tempdb while they are being updated and selected at the same time.
It doesn't do anything like storing a duplicate copy of your database or anything like that. Just the pages that are being updated at the time. So, if you have a ton of updates that are also needed for queries all the time, it will grow tempdb a lot, but not anything like your whole database size.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply