SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using your critical eye to see something positive


Using your critical eye to see something positive

Author
Message
bkubicek
bkubicek
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9725 Visits: 1092
Comments posted to this topic are about the item Using your critical eye to see something positive
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)

Group: General Forum Members
Points: 912439 Visits: 48738
I can't speak for others but I almost always offer a better solution when I say "No". The trouble is... it doesn't help. They say that DBAs have this negativity about them but a lot of people get their feelings hurt simply because their idea, no matter how bad it may actually be, is in jeopardy.

Sometimes I let people experiment even though I think something is a horrible idea just so I can occasionally prove that the idea is horrible. For example, I let some folks use sp_GetAppLock because they insisted that it would do what they wanted it to do. I had suggested that there's a much better way and they simply wouldn't hear it and basically accused me of being an old fuddy-duddy that didn't know squat about how modern front-end code worked against a database. Heh... and their strongest justification was that it was a recommended method that they got from some web site somewhere.

So, knowing full well what was going to happen, I let them see it my way Wink by letting them use sp_GetAppLock. The next couple of days were grand fun. People on the floor were complaining about some slowness and the front-end developers started to have timeouts start appearing in their logs. As each day wore on, more timeouts occurred and faster. I'm sitting there watching the blocking mount up knowing full well what the cause was. I finally explained it to them and the reason why sp_GetAppLock was the cause and the boogers still wouldn't take my word for it. They finally got on the forum were they got the original idea and someone with more than half a brain suggested that the ol' fuddy-duddy DBA they were talking about was right.

Ah... almost forgot. It took the devs about a half hour to implement the suggestion that I originally gave them to replace the sp_GetAppLock code and things have been running hot, straight, and normal since.

Another instance was when we were having problems with some monster sized outages on the floor. 36 CPUs on an SSD box with a quarter TB or RAM would suddenly ramp up to over 80% average usage (many of the individual CPUs would peg to the ceiling). I had identified the symptoms of the problem and was working on isolating the cause but knew it wasn't the hardware and I told them about it the problem. They said they thought it was a hardware problem and that they were going to buy another 12 core and another 128GB of RAM. I almost went into my "No... that'd be a waste of money because it's not a hardware problem" but then I remembered that I'd been preaching that, although hardware certainly did carry some weight, true performance was in the code and no one listed then. So I told them one more time without being adamant about it except that I did mention that I thought adding the extra CPUs would simply raise contention (more stuff failing) and then kow towed to their incorrect urges. They spent the money on the hardware and, sure enough, the problems got worse. It didn't last long, though. It was definitely a front-end related problem and I had fed my front-end savvy boss enough information about the symptoms that he figured out that it was because .NET, contrary to what was documented, had been defaulted all connections to implicitly set M.A.R.S. to enabled. He and the devs ripped through the connections and explicitly set M.A.R.S. to disabled and THAT fixed the problem instantly.

I now have a 48 Core fire breathing monster server with 384 GB of RAM (although that change didn't do squat for performance) and folks have a fair bit more respect for when I say "Performance is in the code and you need to fix it... here's how" instead of them saying "we doubt it". BigGrin

As I tell folks, old DBAs aren't hard of hearing... we're tired of listening. And we're not stubborn... we've just seen a lot more than most and already know if something's going to work or not even it's its the latest shinny new twitch on the market. At least I give a reason and an alternative when I say "No" (although I am getting tired of offering alternatives to the same "new" stupid things over and over). The best I get from people with all the twitch ideas is that "everyone is doing it" or "it's a 'Best Practice'" or some idiot who's somehow managed to develop a following on some forum said so.

And those are just two of the dozens of instances where people haven't listened and were more interested in their own agenda rather than doing it right. I got the same lines of horse-muffins when I was a young DBA and I still get them now that I'm an old DBA. Other people need to start listening to us for a change. We're not being negative... we're trying to save people some time, money and, in many cases, embarrassment.

To be absolutely blunt, I'm tired of articles like this one. Get a grip people. Instead of fighting us, join us because we can make you shine if you'll let us. Wink And, for the love of Pete, remember all the times we said "Yes".

And, no... I won't budge an inch if it puts the data in jeopardy, period.

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

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jason-
Jason-
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2249 Visits: 611
Jeff Moden - Thursday, October 18, 2018 12:34 AM
... and their strongest justification was that it was a recommended method that they got from some web site somewhere. ...
It took the devs about a half hour to implement the suggestion that I originally gave them to replace the sp_GetAppLock code and things have been running hot, straight, and normal since.

So for me, some of my strong justifications in favor of specific approaches are articles and posts from some guy named Jeff Moden. (I mean that as a sincere compliment, your willingness to share your knowledge has helped me immensely, thank you). I recently came across an article that sent me down the path of using sp_getapplock to prevent a set of procedures from being run while another specific procedure is being run. I tested several scenarios and devised a solution (one that handles releasing all the nested locks and enforces a timeout error after x seconds). I've been pleased with the solution (still in dev) until I just read your comment here and now I am concerned that I'm going down the wrong path and building a sleeping bomb.

What is the issue with sp_getapplock?


-
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)

Group: General Forum Members
Points: 912439 Visits: 48738
Jason- - Thursday, October 18, 2018 6:46 AM
Jeff Moden - Thursday, October 18, 2018 12:34 AM
... and their strongest justification was that it was a recommended method that they got from some web site somewhere. ...
It took the devs about a half hour to implement the suggestion that I originally gave them to replace the sp_GetAppLock code and things have been running hot, straight, and normal since.

So for me, some of my strong justifications in favor of specific approaches are articles and posts from some guy named Jeff Moden. (I mean that as a sincere compliment, your willingness to share your knowledge has helped me immensely, thank you). I recently came across an article that sent me down the path of using sp_getapplock to prevent a set of procedures from being run while another specific procedure is being run. I tested several scenarios and devised a solution (one that handles releasing all the nested locks and enforces a timeout error after x seconds). I've been pleased with the solution (still in dev) until I just read your comment here and now I am concerned that I'm going down the wrong path and building a sleeping bomb.

What is the issue with sp_getapplock?

Just so you don't think this is a case of the mouse guarding the cheese while answering your question.... the developer that insisted on the use of sp_getapplock finally asked someone else (on StackOverFlow) about whether or not his DBA (me) was puffing on a crack pipe in wanting to get rid of sp_GetAppLock. Please see the question and answer that someone else gave.
https://stackoverflow.com/questions/50030620/does-sp-getapplock-cause-sql-server-performance-problems

I'd show you the PerfMon Charts that I had collected on the problem except that I just found out by looking that my historical folder in outlook have been wiped clean (it's gonna be a hell of a day!).

As for the source of your strong justifications, thank you for the honor but it's not me... it's the code that I post that demonstrates the problem and one possible solution. I usually attach the code so that other people can not only test the solution in their own environment but can also verify that I'm not actually hitting the crack pipe. BigGrin Without that code, I'd be as bad as some of the people claiming "Best Practices" in their articles with no proof.

There have been articles that I posted with a proposed solution that turned out to be slower than expected compared to someone else's solution. The one to convert adjacent spaces in a string to just one is one of those articles. My testing showed a much faster way than the traditional (at the time) RBAR methods for doing this task. Someone else produced some code that blew the doors off my code (even I can learn new stuff and I still do) and I added a prolog to my article saying so and where to get the code and more information as to why it was so much faster. Even that author was dethroned when someone added a binary collation to the code. That's a part of why I love this community... people willing to help.

Compare that to some of the "holy grail" articles on splitters where people have actually compared the performance of the DelimitedSplit8K and DelimitedSplitN4K functions. There are several articles out there that seem to prove that (for example) the XML-Splitter method is much faster and they even cite my article as the source of the function code even though I have proof in code in my original article that it's not. I've challenged several of them because they either changed my code to fit their tests or their test data had a cardinality of 1, which DOES make the XML code work faster but it doesn't reflect real life data (I've actually got a most complete series of articles on the subject that I really need to finish). As a result of my challenges and suggestions, they simply shut down all replies to the article.

I've been through the same thing with people that write articles that say that using rCTEs to produce numerical sequences as a replacement for a Tally Table or Tally Function is a "Best Practice" especially for small stuff. I provide them with a link to my article that proves with code that their claims are incorrect and the bastards either never post my reply or delete any of my posts that are contrary to their stated position on the subject.

THOSE are the kinds of people/articles/sources of information that I'm talking about.

Like my Dad used to say, "Half of all that is written is incorrect... the other half is written in such a fashion that you can't tell". And that and the opportunity for people to come up with improvements is why I post code along with my opinions and conclusions. BigGrin


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

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Mlakar
Jeff Mlakar
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2489 Visits: 670
I like to look at this phenomenon of the negative DBA like this: the nature of the work for a sysadmin (i.e. production DBA) is strikingly different from the development side (i.e. a development DBA).

A fun way I like to put it is vampires vs werewolves. I remember Jeff Atwood gave an analogy like that on his blog Coding Horror. Developers are like vampires and admins are like werewolves. The vampire stays up all night coding and doesn't care much about procedure outside of that. Werewolves are quite until there is a full moon (the DB goes down) then transform to a terrible up-time monster.

So it is the nature of the beast that determines this behavior.

Anyway - maybe I just like Halloween too much...
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)

Group: General Forum Members
Points: 912439 Visits: 48738
Jeff Mlakar - Thursday, October 18, 2018 8:57 AM
I like to look at this phenomenon of the negative DBA like this: the nature of the work for a sysadmin (i.e. production DBA) is strikingly different from the development side (i.e. a development DBA).

A fun way I like to put it is vampires vs werewolves. I remember Jeff Atwood gave an analogy like that on his blog Coding Horror. Developers are like vampires and admins are like werewolves. The vampire stays up all night coding and doesn't care much about procedure outside of that. Werewolves are quite until there is a full moon (the DB goes down) then transform to a terrible up-time monster.

So it is the nature of the beast that determines this behavior.

Anyway - maybe I just like Halloween too much...


My suggestion is that most DBAs (at least the good ones) aren't anything like that at all. I can't speak for others but I'm kind, courteous, helpful and a couple of other Boy Scout terms and I frequently and enthusiastically say "Yes" to requests. I take the time to mentor folks and help their code really cook giving them full credit because my job isn't about collecting attaboys. So when I say "No" to something, people need to stop and listen.

And, as I'm sure it is with many DBAs, it's the people that are asking DBAs that are actually negative because they don't remember all the times the DBA said "Yes" or saved their hinnies when something went haywire without actually pointing any fingers at anyone but quietly mentored the people or the group of people whose code or actions caused the issue and how to avoid it in the future.

And, no... I disagree with the analogy that developers are like vampires, as well. They're just trying to get their jobs done (although some need to start thinking). If you want to point the finger at who is actually responsible for the rift between Developers and DBAs, that would be the managers and BA's that think Rome can be built in a single day and write their schedules and requirements that way. Even though I don't care for what people consider to be "Agile Methodology", it DOES cause people to slow down (contrary to popular belief) and work on one thing at a time despite the whip of the uniformed task masters. BigGrin

And, sorry but no... You and your application can't have sysadmin or dbo privs. It's just not necessary to take on that risk. Here's how to do it right and how we can continue to pass audits.

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

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Mlakar
Jeff Mlakar
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2489 Visits: 670
Jeff Moden - Thursday, October 18, 2018 9:11 AM
Jeff Mlakar - Thursday, October 18, 2018 8:57 AM
I like to look at this phenomenon of the negative DBA like this: the nature of the work for a sysadmin (i.e. production DBA) is strikingly different from the development side (i.e. a development DBA).

A fun way I like to put it is vampires vs werewolves. I remember Jeff Atwood gave an analogy like that on his blog Coding Horror. Developers are like vampires and admins are like werewolves. The vampire stays up all night coding and doesn't care much about procedure outside of that. Werewolves are quite until there is a full moon (the DB goes down) then transform to a terrible up-time monster.

So it is the nature of the beast that determines this behavior.

Anyway - maybe I just like Halloween too much...


My suggestion is that most DBAs (at least the good ones) aren't anything like that at all. I can't speak for others but I'm kind, courteous, helpful and a couple of other Boy Scout terms and I frequently and enthusiastically say "Yes" to requests. I take the time to mentor folks and help their code really cook giving them full credit because my job isn't about collecting attaboys. So when I say "No" to something, people need to stop and listen.

And, as I'm sure it is with many DBAs, it's the people that are asking DBAs that are actually negative because they don't remember all the times the DBA said "Yes" or saved their hinnies when something went haywire without actually pointing any fingers at anyone but quietly mentored the people or the group of people whose code or actions caused the issue and how to avoid it in the future.

And, no... I disagree with the analogy that developers are like vampires, as well. They're just trying to get their jobs done (although some need to start thinking). If you want to point the finger at who is actually responsible for the rift between Developers and DBAs, that would be the managers and BA's that think Rome can be built in a single day and write their schedules and requirements that way. Even though I don't care for what people consider to be "Agile Methodology", it DOES cause people to slow down (contrary to popular belief) and work on one thing at a time despite the whip of the uniformed task masters. BigGrin

And, sorry but no... You and your application can't have sysadmin or dbo privs. It's just not necessary to take on that risk. Here's how to do it right and how we can continue to pass audits.
I'm sorry to disappoint on my analogy - it was a generalization. I have noticed it, Jeff Atwood has, and many others. Not sure what you are talking about on the last sentence. Every place I've been in has a separation in job role between prod and dev (or admin / dev) in my example.
BTW that reads as quite disagreeable - not the boy scout response I would expect.

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)

Group: General Forum Members
Points: 912439 Visits: 48738
Jeff Mlakar - Thursday, October 18, 2018 9:24 AM
Jeff Moden - Thursday, October 18, 2018 9:11 AM
Jeff Mlakar - Thursday, October 18, 2018 8:57 AM
I like to look at this phenomenon of the negative DBA like this: the nature of the work for a sysadmin (i.e. production DBA) is strikingly different from the development side (i.e. a development DBA).

A fun way I like to put it is vampires vs werewolves. I remember Jeff Atwood gave an analogy like that on his blog Coding Horror. Developers are like vampires and admins are like werewolves. The vampire stays up all night coding and doesn't care much about procedure outside of that. Werewolves are quite until there is a full moon (the DB goes down) then transform to a terrible up-time monster.

So it is the nature of the beast that determines this behavior.

Anyway - maybe I just like Halloween too much...


My suggestion is that most DBAs (at least the good ones) aren't anything like that at all. I can't speak for others but I'm kind, courteous, helpful and a couple of other Boy Scout terms and I frequently and enthusiastically say "Yes" to requests. I take the time to mentor folks and help their code really cook giving them full credit because my job isn't about collecting attaboys. So when I say "No" to something, people need to stop and listen.

And, as I'm sure it is with many DBAs, it's the people that are asking DBAs that are actually negative because they don't remember all the times the DBA said "Yes" or saved their hinnies when something went haywire without actually pointing any fingers at anyone but quietly mentored the people or the group of people whose code or actions caused the issue and how to avoid it in the future.

And, no... I disagree with the analogy that developers are like vampires, as well. They're just trying to get their jobs done (although some need to start thinking). If you want to point the finger at who is actually responsible for the rift between Developers and DBAs, that would be the managers and BA's that think Rome can be built in a single day and write their schedules and requirements that way. Even though I don't care for what people consider to be "Agile Methodology", it DOES cause people to slow down (contrary to popular belief) and work on one thing at a time despite the whip of the uniformed task masters. BigGrin

And, sorry but no... You and your application can't have sysadmin or dbo privs. It's just not necessary to take on that risk. Here's how to do it right and how we can continue to pass audits.
I'm sorry to disappoint on my analogy - it was a generalization. I have noticed it, Jeff Atwood has, and many others. Not sure what you are talking about on the last sentence. Every place I've been in has a separation in job role between prod and dev (or admin / dev) in my example.
BTW that reads as quite disagreeable - not the boy scout response I would expect.


No... it wasn't a disappointment. It's actually kind of clever but such generalizations do tend to deepen the rift a bit and so expressed my thoughts on it, especially since I'm privileged to work with some great Developers that I do have to say "No" to once in a while.

My last comment was just an example of one place where I stand my ground whether I have extra hair on my back that day or not. BigGrin

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

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Mlakar
Jeff Mlakar
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2489 Visits: 670
Jeff Moden - Thursday, October 18, 2018 9:37 AM
Jeff Mlakar - Thursday, October 18, 2018 9:24 AM
Jeff Moden - Thursday, October 18, 2018 9:11 AM
Jeff Mlakar - Thursday, October 18, 2018 8:57 AM
I like to look at this phenomenon of the negative DBA like this: the nature of the work for a sysadmin (i.e. production DBA) is strikingly different from the development side (i.e. a development DBA).

A fun way I like to put it is vampires vs werewolves. I remember Jeff Atwood gave an analogy like that on his blog Coding Horror. Developers are like vampires and admins are like werewolves. The vampire stays up all night coding and doesn't care much about procedure outside of that. Werewolves are quite until there is a full moon (the DB goes down) then transform to a terrible up-time monster.

So it is the nature of the beast that determines this behavior.

Anyway - maybe I just like Halloween too much...


My suggestion is that most DBAs (at least the good ones) aren't anything like that at all. I can't speak for others but I'm kind, courteous, helpful and a couple of other Boy Scout terms and I frequently and enthusiastically say "Yes" to requests. I take the time to mentor folks and help their code really cook giving them full credit because my job isn't about collecting attaboys. So when I say "No" to something, people need to stop and listen.

And, as I'm sure it is with many DBAs, it's the people that are asking DBAs that are actually negative because they don't remember all the times the DBA said "Yes" or saved their hinnies when something went haywire without actually pointing any fingers at anyone but quietly mentored the people or the group of people whose code or actions caused the issue and how to avoid it in the future.

And, no... I disagree with the analogy that developers are like vampires, as well. They're just trying to get their jobs done (although some need to start thinking). If you want to point the finger at who is actually responsible for the rift between Developers and DBAs, that would be the managers and BA's that think Rome can be built in a single day and write their schedules and requirements that way. Even though I don't care for what people consider to be "Agile Methodology", it DOES cause people to slow down (contrary to popular belief) and work on one thing at a time despite the whip of the uniformed task masters. BigGrin

And, sorry but no... You and your application can't have sysadmin or dbo privs. It's just not necessary to take on that risk. Here's how to do it right and how we can continue to pass audits.
I'm sorry to disappoint on my analogy - it was a generalization. I have noticed it, Jeff Atwood has, and many others. Not sure what you are talking about on the last sentence. Every place I've been in has a separation in job role between prod and dev (or admin / dev) in my example.
BTW that reads as quite disagreeable - not the boy scout response I would expect.


No... it wasn't a disappointment. It's actually kind of clever but such generalizations do tend to deepen the rift a bit and so expressed my thoughts on it, especially since I'm privileged to work with some great Developers that I do have to say "No" to once in a while.

My last comment was just an example of one place where I stand my ground whether I have extra hair on my back that day or not. BigGrin

Indeed that is a worthy fight to engage in - keeping sysadmin and dbowner away from those who do not need it!

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)

Group: General Forum Members
Points: 912439 Visits: 48738
Jason- - Thursday, October 18, 2018 6:46 AM
Jeff Moden - Thursday, October 18, 2018 12:34 AM
... and their strongest justification was that it was a recommended method that they got from some web site somewhere. ...
It took the devs about a half hour to implement the suggestion that I originally gave them to replace the sp_GetAppLock code and things have been running hot, straight, and normal since.

So for me, some of my strong justifications in favor of specific approaches are articles and posts from some guy named Jeff Moden. (I mean that as a sincere compliment, your willingness to share your knowledge has helped me immensely, thank you). I recently came across an article that sent me down the path of using sp_getapplock to prevent a set of procedures from being run while another specific procedure is being run. I tested several scenarios and devised a solution (one that handles releasing all the nested locks and enforces a timeout error after x seconds). I've been pleased with the solution (still in dev) until I just read your comment here and now I am concerned that I'm going down the wrong path and building a sleeping bomb.

What is the issue with sp_getapplock?


Ah... finally got my history folders back...

Here's a perfmon of the blocking that sp_GetAppLock was causing. The heavy Red lines are the blocking, which normally never appear (and look at the very long period of time they went off the scale, as well!!!). The skinny Red line is average CPU usage across all 48 CPUs. Normally, it's at about 8% during the time of the day (always good to have a baseline) that I took that "picture". The heavy Dark Blue kinda square looking lines are SQL Server jobs.... they normally don't appear for more than a couple of seconds. You can see that they've been extended, as well. The skinny Green and Blue lines are disk reads and disk writes respectively. With 384GB on the box, they're normally quite a bit less active than on the "picture" below. All of that went away when we stopped using sp_GetAppLock.


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

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. 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