You make some good points Scott. I think my approach had been that NOLOCK, like CURSORS, should be avoided except where absolutely necessary. It was what I had read in many places online (both on this forum and elsewhere), and had heard in multiple webinars/conferences, so I thought if the experts said to avoid it and I am by no means an "expert" when I see how much others know of SQL Server, they must be right. On a side note - I remember the first time I heard about windowing functions and it blew my mind! Brought that back to the team and at that time (1 DBA (me), 4 developers, 1 BA), nobody had heard of windowing functions. It made all of our code so much more efficient. Then I heard about CTE's and recursive CTE's and brought that back to the team and our BA loved them. Nobody else really used them for our stuff, but was good to know they existed.
Learning of uses for NOLOCK is actually good for me! I like learning new things and after reading for years about how NOLOCK is evil, I just took it to be truth. But the more I think about it, the more I realize that if it was to be avoided 100%, then Microsoft would have removed it from SQL Server (or at least disabled it because if they disabled that hint, old code would still run, just the hint would not do anything).
Where I work, we have the system busy, but not THAT busy, so NOLOCK is rarely the right solution to our problems when we hit performance issues. USUALLY if we hit a performance issue, an index or rewriting the query is a better solution than NOLOCK. NOLOCK is our "band-aid" fix so people stop complaining while we work on the real issue.
I also gave the example with RECOMPILE above and while I prefer to avoid it. There are cases where it fixes the problem you are having, but again, at my workplace, this is more of a "band-aid" fix than a proper fix. OPTIMIZE FOR UNKNOWN is a better option in most cases and optimizing for a specific value is in general a better solution than UNKNOWN. Alternately, adding an index can help with this too.
You gave me something to think about with this. I am going to revisit my coding standards. I know I changed some stuff when the previous primary DBA left. One big one I changed was they had "don't use CHAR, use VARCHAR". I changed that to "use appropriate data types and try to use the smallest required data type (ie INT vs BIGINT)".
I am now running through my head of other things I advise to avoid and am thinking about which ones are "should be avoided" and which ones are "use only where appropriate". Should be avoided ones I am still thinking Cursors are in that bucket. They have a purpose and a use, but overall I find them to be abused and used when not needed (looping for no good reason). NOLOCK, for all of the use cases I have at my workplace, seems like more of a "magic button" fix than a proper fix, same thing with RECOMPILE. Our blocking is generally short and I generally recommend it for the tables we see the blocking happening on. But I am going to change our coding documentation (for SQL) so that NOLOCK is allowed without needing to comment things and get approvals, but that the developer needs to be aware of the risks of using it and that the code reviewer may question that decision.
Another one I usually recommend against using is sp_msforeachdb or sp_msforeachtable, but that is because I've read stories where it skips some db's or tables. Thankfully, writing your own version of that is fairly easy to do and from my experience is reliable. And is one of the use cases for a cursor where I don't complain if I see a cursor.
On a completely unrelated note - one of the BIGGEST pet peeves of mine is single character variables and aliases. That was another big thing I added to our coding standards. Variables and aliases must be descriptive enough that I don't need to scroll through the code to determine what the alias or variable is to be used for. Poorly named objects are also a no-go in my system with the exception of objects named from 3rd party tools. If part of the ETL process is to copy a table from a 3rd party tool to the data mart, the table name should be prefixed with the system/tool it came from followed by the original table name. But I am starting to get really far off topic, so I will stop this post.
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.