June 19, 2017 at 12:27 pm
Jeff Moden - Saturday, June 17, 2017 9:30 PMDavid.Poole - Friday, June 16, 2017 2:02 AMI think many job descriptions aren't worth the paper they are printed on. My role varies so much you could write it as "Do stuff with data for money. Mentor others wishing to do stuff with data. Do longer hours than were contracted or anticipated by people committing to deadlines. Show ingenuity on a daily basis".I also think HR muddy the waters. Candidates must have 5 years experience in a 2 year old technology. Salaries up to £x for the right candidate. As per the post the right candidate doesn't exist.
My job title is "Senior Data Engineer".
One of the best jobs I ever had came with a very simple, short, and to the point job description. It was quite literally "Basically, we need someone that can look at SQL, figure out what's wrong, and fix it".
I used to describe my job as "getting things done" with SQL Server.
June 19, 2017 at 1:06 pm
Jeff Moden - Saturday, June 17, 2017 9:30 PMOne of the best jobs I ever had came with a very simple, short, and to the point job description. It was quite literally "Basically, we need someone that can look at SQL, figure out what's wrong, and fix it".
That sounds more like a job description for a contractor, consultant, ... or intern. However, every medium to large IT organization could use someone on staff who primary job fits that description. It's a specialized and high ROI skill.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 19, 2017 at 2:40 pm
Eric M Russell - Monday, June 19, 2017 1:06 PMJeff Moden - Saturday, June 17, 2017 9:30 PMOne of the best jobs I ever had came with a very simple, short, and to the point job description. It was quite literally "Basically, we need someone that can look at SQL, figure out what's wrong, and fix it".That sounds more like a job description for a contractor, consultant, ... or intern. However, every medium to large IT organization could use someone on staff who primary job fits that description. It's a specialized and high ROI skill.
That was one of the problems they had. They had a whole lot of poor performing code with lots of cursors for batch processes, etc, etc. They HAD hired several consultants. One recommended that they replace ALL the cursors with <insert single bugle "Taps" play here> Temp Tables and While Loops, which they did at great expense and to no avail. As you might guess, some of the routines actually got worse because of Temp DB/Memory contention. Another "genius" suggested that they use <insert snare drum roll here> WITH (NOLOCK) everywhere... even on tables that were being UPDATEd <badaboom tish!>, which they did at great expense. Still others suggested the use of functions (scalar) and VIEWs to encapsulate all common code and so they <head-desk, head-desk, head-desk> could code more effectively and they made functions of functions and views of views at great expense. Of course, through this all, performance kept getting worse and worse and when the next "genius" came in and suggested getting rid of all stored procedures and writing all functionality in the front end, they decided to try a different ploy... I was very fortunate to see the ad and know exactly what they meant and wanted.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 20, 2017 at 7:20 am
Jeff Moden - Monday, June 19, 2017 2:40 PM...That was one of the problems they had. They had a whole lot of poor performing code with lots of cursors for batch processes, etc, etc. They HAD hired several consultants. One recommended that they replace ALL the cursors with <insert single bugle "Taps" play here> Temp Tables and While Loops, which they did at great expense and to no avail. As you might guess, some of the routines actually got worse because of Temp DB/Memory contention. Another "genius" suggested that they use <insert snare drum roll here> WITH (NOLOCK) everywhere... even on tables that were being UPDATEd <badaboom tish!>, which they did at great expense. Still others suggested the use of functions (scalar) and VIEWs to encapsulate all common code and so they <head-desk, head-desk, head-desk> could code more effectively and they made functions of functions and views of views at great expense. Of course, through this all, performance kept getting worse and worse and when the next "genius" came in and suggested getting rid of all stored procedures and writing all functionality in the front end, they decided to try a different ploy... I was very fortunate to see the ad and know exactly what they meant and wanted.
Unfortunately a true prophet must often follow in the chaotic wake of the many false prophets who came before.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 20, 2017 at 9:22 am
Jeff, I just got caught up and saw your reply. I've got a question for you and anyone else who wants to answer. This question isn't related to the topic, sorry.
Its about the use of WITH (NOLOCK). I've never used it. I've looked it up and know the BOL description, but what I want to know is how does it impact performance?
Kindest Regards, Rod Connect with me on LinkedIn.
June 20, 2017 at 9:35 am
Rod at work - Tuesday, June 20, 2017 9:22 AMJeff, I just got caught up and saw your reply. I've got a question for you and anyone else who wants to answer. This question isn't related to the topic, sorry.Its about the use of WITH (NOLOCK). I've never used it. I've looked it up and know the BOL description, but what I want to know is how does it impact performance?
It must be great to have spent your SQL Server career in an organization that never resorted to using NOLOCK hints. I feel as if you're pulling our leg, but for the benefit of those who actually don't know, NOLOCK hints apply READ UNCOMMITTED isolation level at the statement level. A reader using NOLOCK hint will not hold shared locks on pages it reads, meaning that it doesn't block writers, and it is not blocked by writers. So, it can potentially reduce the run-time of queries by preventing wait states ordinarily caused by blocking at the cost of potentially reading inconsistent data (partially committed batches of updates and inserts or in some cases double reading). In other words, it doesn't make the query "run faster"; it simply prevents the query from pausing to a take a smoke breaks while waiting for UPDATE locks to free up. If there are no active writers, NOLOCK will have no impact on run-time performance of the query or the consistency of it's reads.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 20, 2017 at 9:41 am
Thank Eric, that's what I needed to know.
BTW, I don't mean to imply that no one uses NOLOCK. In fact its used often here. I'm just saying that I haven't used it. Looks like I really should, unless consistency in reading the data is very important.
Kindest Regards, Rod Connect with me on LinkedIn.
June 20, 2017 at 9:46 am
Rod at work - Tuesday, June 20, 2017 9:41 AMThank Eric, that's what I needed to know.BTW, I don't mean to imply that no one uses NOLOCK. In fact its used often here. I'm just saying that I haven't used it. Looks like I really should, unless consistency in reading the data is very important.
If what you're struggling with is extended query run-time due to blocking, then NOLOCK help mitigate that. However, other options are: re-scheduling your daily workflow so that bulk inserts do not occur during periods when users are running reporting style queries, implementing a read-only replica for reporting purposes, or using READ COMMITTED SNAPSHOT isolation level as an alternative to READ UNCOMMITTED.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 20, 2017 at 9:52 am
Eric M Russell - Tuesday, June 20, 2017 9:35 AMRod at work - Tuesday, June 20, 2017 9:22 AMJeff, I just got caught up and saw your reply. I've got a question for you and anyone else who wants to answer. This question isn't related to the topic, sorry.Its about the use of WITH (NOLOCK). I've never used it. I've looked it up and know the BOL description, but what I want to know is how does it impact performance?
It must be great to have spent your SQL Server career in an organization that never resorted to using NOLOCK hints. I feel as if you're pulling our leg, but for the benefit of those who actually don't know, NOLOCK hints apply READ UNCOMMITTED isolation level at the statement level. A reader using NOLOCK hint will not hold shared locks on pages it reads, meaning that it doesn't block writers, and it is not blocked by writers. So, it can potentially reduce the run-time of queries by preventing wait states ordinarily caused by blocking at the cost of potentially reading inconsistent data (partially committed batches of updates and inserts or in some cases double reading).
I hate to be "that guy", but there are some very rare cases where I have used NOLOCK. Mind you, nothing that ever hit production (that I recall).
If you are working with service broker and you want to get data out of one of the queues, it is a good idea to use NOLOCK as you don't want to cause blocking on service broker if you don't need to plus I've found that most of the time, service broker causes blocking on the queues anyways so when you try to select it can take FOREVER to get a result if you don't use NOLOCK.
The other cases were when I wanted to see if the query I was running would return results and as long as a row or 2 looked correct, then I could remove the NOLOCK stuff and send it off to end users. But when I am developing (especially against a high use test system), I like to use NOLOCK so I can get my results back quicker even if they are potentially incorrect.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 20, 2017 at 10:04 am
If what you're doing is running a report or ad-hoc query where the results are returned to an external application, then perhaps NOLOCK and less trustworthy data is not a serious issue. However, I would not want to see NOLOCK hints or READ UNCOMMITTED isolation being used in stored procedures where DML operations are writing back to the database.
For example, this is particularly dangerous because it is persisting untrusted data back to the database:
INSERT INTO TableA ( a, b, c )
SELECT ( a, b, c ) FROM TableB (NOLOCK);
There is a big difference between an untrusted query versus an untrusted database.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply