July 30, 2015 at 8:29 am
And now I'm finding out our databases aren't set up to allow snapshot or read committed isolation. So do I take the overhead hit or not?
How do people deal with isolation levels if the databases aren't set up to use them? I guess they use NOLOCK.
July 30, 2015 at 8:31 am
Brandie Tarvin (7/30/2015)
Why oh why oh why do people insist on using WITH (NOLOCK) instead of doing things like setting transaction isolation levels or fixing indexes to resolve performance issues?Work thing. I've been trying to stamp out WITH (NOLOCK) use and now because things "aren't working" one group is trying to throw it back in.
ARGH!
Well, this is my battle for the week.
EDIT: Seriously, can anyone give me a real reason why WITH (NOLOCK) might be functionally necessary for production code?
Functionally necessary? No.
If they use table variables, it is used by default behind the scenes. Not that that will make that much difference with the nature of table variables.
DW environments love NOLOCK. SSIS works well with nolock.
But, if all they care about is "eventually good enough" then ok there is the prod requirement.:crazy:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 30, 2015 at 8:33 am
Brandie Tarvin (7/30/2015)
ALZDBA (7/30/2015)
Brandie Tarvin (7/30/2015)
...EDIT: Seriously, can anyone give me a real reason why WITH (NOLOCK) might be functionally necessary for production code?To keep the answer brief and firm: No.
The risk encountering nolock downsides is just to big. They will bit you in the back.
Would they use float for all their calculations ? ... strike that last question ... they just might
@=)
Not ALL their calculations. But yes, float is used in this office.
Well, float is a little bit better than the sink data type. At least they are still above water. 😀
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 30, 2015 at 8:38 am
GilaMonster (7/30/2015)
Luis Cazares (7/30/2015)
NOLOCK hints can be useful to get estimates of certain data.Yup. In fact I told some people recently to put nolock onto a bunch of queries. They were populating a dashboard showing number of documents captured, number of claims processed, number of errors encountered etc. The queries were causing blocking and it doesn't matter if they're not 100% accurate.
That is the route I will normally go with. If it is for a report/dashboard type of thing where it can be off a bit but *eventually* good enough, go ahead and use it. Then try to refactor the code to see if it can perform better.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 30, 2015 at 8:39 am
Grant Fritchey (7/30/2015)
Brandie Tarvin (7/30/2015)
As I dig into this issue (Execution Plans HO!), I come across a thread in which one respondent posts a link to Gail's blog and then Grant shows up to promote his execution plan book (by posting a link to the free PDF).Hee. You two get around.
Just remember, read the second edition. The guy who wrote the first edition was an idiot. Soon, after the third edition comes out, the guy who wrote the second edition will become an idiot.
And not long before the guy who wrote the third edition becomes that same idiot.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 30, 2015 at 8:39 am
I am semi-bored so I have written an article on Resource Governor, I was wondering if anyone fancies reading it to confirm if it is worth submitting?
I will send the individual an "e-coffee" as a token of my appreciation?
July 30, 2015 at 8:40 am
GilaMonster (7/30/2015)
Grant Fritchey (7/30/2015)
Brandie Tarvin (7/30/2015)
As I dig into this issue (Execution Plans HO!), I come across a thread in which one respondent posts a link to Gail's blog and then Grant shows up to promote his execution plan book (by posting a link to the free PDF).Hee. You two get around.
Just remember, read the second edition. The guy who wrote the first edition was an idiot. Soon, after the third edition comes out, the guy who wrote the second edition will become an idiot.
Oh don't get me started. Whoever wrote the older posts on that 'SQLIntheWild' blog was clearly clueless and a terrible writer.
Agreed!
I find my writing is getting better with more practice. It also gets a little easier to crank out an article. But I find myself cringing at some of the old stuff and then I go into update/fix mode.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 30, 2015 at 8:44 am
Brandie Tarvin (7/30/2015)
And now I'm finding out our databases aren't set up to allow snapshot or read committed isolation. So do I take the overhead hit or not?How do people deal with isolation levels if the databases aren't set up to use them? I guess they use NOLOCK.
Consider this.
If it is a handful of queries causing the grief, snapshot is probably overkill.
If they have any code using the nolock directive, or connection strings that force a specific isolation level for the connection, then the SNAPSHOT isolation will be overridden - and you are right back at the same problem.
I would do a little more digging and find the top 10 grief causing queries and see if they are constantly related to blocking or deadlocking. I would also see if they are things that can get away with using nolock. But in the end, it will take a little investigation and some blood and tears on your part.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 30, 2015 at 8:51 am
SQLRNNR (7/30/2015)
Grant Fritchey (7/30/2015)
Brandie Tarvin (7/30/2015)
As I dig into this issue (Execution Plans HO!), I come across a thread in which one respondent posts a link to Gail's blog and then Grant shows up to promote his execution plan book (by posting a link to the free PDF).Hee. You two get around.
Just remember, read the second edition. The guy who wrote the first edition was an idiot. Soon, after the third edition comes out, the guy who wrote the second edition will become an idiot.
And not long before the guy who wrote the third edition becomes that same idiot.
Nah, that guy will be smart for a little while. Rewriting these damn books takes a lot of time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 30, 2015 at 9:15 am
Grant Fritchey (7/30/2015)
SQLRNNR (7/30/2015)
Grant Fritchey (7/30/2015)
Brandie Tarvin (7/30/2015)
As I dig into this issue (Execution Plans HO!), I come across a thread in which one respondent posts a link to Gail's blog and then Grant shows up to promote his execution plan book (by posting a link to the free PDF).Hee. You two get around.
Just remember, read the second edition. The guy who wrote the first edition was an idiot. Soon, after the third edition comes out, the guy who wrote the second edition will become an idiot.
And not long before the guy who wrote the third edition becomes that same idiot.
Nah, that guy will be smart for a little while. Rewriting these damn books takes a lot of time.
If that guy stopped writing books, would he stop becoming an idiot and stay smart? 😛
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 30, 2015 at 9:21 am
Brandie Tarvin (7/30/2015)
And now I'm finding out our databases aren't set up to allow snapshot or read committed isolation. So do I take the overhead hit or not?
Up to you.
Do you have blocking problems you can't fix by tuning indexes/queries?
Is TempDB under serious load already?
Does the application work correctly under optimistic concurrency?
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
July 30, 2015 at 9:48 am
Alvin Ramard (7/30/2015)
Grant Fritchey (7/30/2015)
SQLRNNR (7/30/2015)
Grant Fritchey (7/30/2015)
Brandie Tarvin (7/30/2015)
As I dig into this issue (Execution Plans HO!), I come across a thread in which one respondent posts a link to Gail's blog and then Grant shows up to promote his execution plan book (by posting a link to the free PDF).Hee. You two get around.
Just remember, read the second edition. The guy who wrote the first edition was an idiot. Soon, after the third edition comes out, the guy who wrote the second edition will become an idiot.
And not long before the guy who wrote the third edition becomes that same idiot.
Nah, that guy will be smart for a little while. Rewriting these damn books takes a lot of time.
If that guy stopped writing books, would he stop becoming an idiot and stay smart? 😛
Oh, now that is an interesting spin.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 30, 2015 at 10:12 am
And on another subject...
I HAVE GUMMY BEARS! (fear me)
July 30, 2015 at 10:17 am
Brandie Tarvin (7/30/2015)
And on another subject...I HAVE GUMMY BEARS! (fear me)
That can be taken multiple ways.
Let's guess that means this instead of the surgical process.
https://www.youtube.com/watch?v=mLpNGp-IFIk
edit: better link
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 30, 2015 at 12:19 pm
SQLRNNR (7/30/2015)
Brandie Tarvin (7/30/2015)
And on another subject...I HAVE GUMMY BEARS! (fear me)
That can be taken multiple ways.
Let's guess that means this instead of the surgical process.
https://www.youtube.com/watch?v=mLpNGp-IFIk
edit: better link
That is officially scary. @=)
Viewing 15 posts - 49,756 through 49,770 (of 66,738 total)
You must be logged in to reply to this topic. Login to reply