January 7, 2015 at 1:00 pm
Let me start off by saying that SQL isn't my area of expertise, nor am I striving to be a DBA. I've worked at several companies, most of which have some app that runs on some year/flavor of SQL Server. None of the DBAs seemed to be obsessed with the issues that my current company has defined.
This company didn't have a DBA initially, and only now has a contractor who only really helps when stuff goes awry. The managers have defined some best practices around how we should leverage SQL that I'm not convinced about. Any insight on how correct they are would be greatly appreciated.
1 - Only execute queries against production on production. "Connecting through SSMS from your dev box and running queries and schema updates can bring down the server"
We have to RDP into our production server in order to write ad-hoc queries for troubleshooting or updating schemas. The problem is that we're such a small team with no QA team or unit tests (both of which are frowned upon as time-wasters) and we spend a lot of time debugging production code (and therefore production data). This means we're constantly bumping eachother off in the middle of working on things.
2 - Always use the NOLOCK hint on every select query.
I'm inclined to be OK with this one when we can accept eventual consistency, but sometimes we need the data used by the app to be correct at the expense of a lock.
3 - Never use transactions. "Transactions can destroy the server"
We're not allowed to use them when updating or deleting from tables to see if we'd be impacting the correct rows, or in code when we genuinely have a transaction. Instead, deleting or editing data manually requires that we fully backup the table using SELECT INTO, perhaps using a ROWCOUNT, and then hoping we don't have to restore from the backup.
4 - Never use any JOIN syntax. "Joins kill performance".
We're expected to never use them. Needless to say sometimes we could write a simple JOIN and get what we need, or write a bunch of code that manually shoves data into a DTO. Our tables are indexed pretty well and performance is reviewed often.
5 - NEVER EVER EVER use SSMS's 'Edit Top 200 Rows' feature. "Using this locks the table when the window is left open".
I've all but proven this one untrue, but I figured I'd ask anyway. When the window opens and is populating, I'm sure it does a one-time SELECT and doesn't use a NOLOCK, so we'd expect a short lock. Managers say that the table remains locked as long as the window is open and you're a horrible person if you do this.
6 - Never use stored procedures and don't use prepared statements.
I never got a real explanation on this, which doesn't help my confusion.
If it seems like I'm fishing to confirm my suspicions, it's because... well, I am. I've done enough research on each of them that has lead me to my current point of view, and when I present evidence to the best practice owners that is contrary to their statements, they ignore it or just tell me "don't do it". I'm going to stop pressing these issues regardless, but I'd love to have someone show me the light one way or the other so I don't hold on to misconceptions.
January 7, 2015 at 1:16 pm
All six of those recommendations are absolute cannon fodder written by people who obviously have no clue about anything having to do with SQLServer. Some of them are, in fact, worst practices rather than anything that comes close to a best practice. Some are actually deemed to be a worst practice and are considered to actually be dangerous. The edict they've produced for practice #3 will cost them dearly some unknown day in the near future. And, if they're not going to allow any joins between tables, then they might must as well turn the SQL Server off and do everything in ISAM files.
#5 is the closest they've come to doing something right but their understanding is grossly lacking.
I have to admit that these are probably the worst and most ridiculous set of Draconian edicts written by management about the computing environment that I've ever seen and that I'm demonstrating incredible restraint in my comments about these edicts. To be very generous, I can't believe that this total stupidity was spawned by the minds of managers alone. They had some serious help producing something this absurd and dangerous.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 7, 2015 at 1:27 pm
That was a good chuckle. Thank you for that.
Can you expand on any 'gotchas' or 'no-nos' with the edit window for my own benefit? I typically only use it when I'm seeding brand new tables, and I'm inclined to not leave any window open when I'm done with it out of pure OCD, but given that we have to all share the same server, we can see when edit windows are being used on some larger tables.
January 7, 2015 at 2:03 pm
Sorry, but it looks like a joke to me. Like someone created the worst practices list just for fun.
I have only one suggestion to you - run. Run as fast as you can from this company.
January 7, 2015 at 2:11 pm
syquil (1/7/2015)
That was a good chuckle. Thank you for that.Can you expand on any 'gotchas' or 'no-nos' with the edit window for my own benefit? I typically only use it when I'm seeding brand new tables, and I'm inclined to not leave any window open when I'm done with it out of pure OCD, but given that we have to all share the same server, we can see when edit windows are being used on some larger tables.
Open edit window and then run the following query to check what is locked. I bet you will see nothing.
SELECT s.session_id, s.login_name, s.host_name, s.program_name, L.*
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_tran_locks L on s.session_id = l.request_session_id
WHERE s.login_name = 'put your login name here'
January 7, 2015 at 2:35 pm
January 7, 2015 at 2:43 pm
Yeah, I wish it was, too.
January 7, 2015 at 2:57 pm
This is scary. Watch for a toxic person in this environment--it only takes one to catapult the entire workplace into crazy-land. If someone is being "thrown under the bus", then it could be anyone next, even if it looks like that person deserved it. I've worked at too many of these places and have learned (the hard way) to see what's really going on rather than what I want to be going on, and leave.
Run is the correct advice here.
January 7, 2015 at 3:18 pm
When ever you say "never" it always bits you in the end. There is a time a place for most rules. These seem like the worst of the worst.
January 7, 2015 at 3:36 pm
I had to check if the original post was from Apr 1st.
Nope. :w00t:
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]
January 7, 2015 at 3:42 pm
Jeez, you'd be better off working for a company with no standards at all. At least then you'd have a chance to get things right, these pretty much guarantee every solution will be bad and will break in production.
January 7, 2015 at 4:20 pm
Hello and welcome to the forums.
I absolutely agree with #5. It's actually good advice.
Unfortunately, the rest, in fact, I'd say they got basically 180 degrees out of alignment.
You should prefer either prepared statements or stored procedures (I'm OK with either, but I prefer stored procedures because I don't have to touch your code to help out with tuning). This is because it promotes reuse of the execution plans for these queries which radically reduces CPU use on machines. Also, being able to reuse execution plans means fewer copies of execution plans stored in memory, so you get more efficient memory management.
Sounds like you already know what NO_LOCK could be an issue. Just remember, it's not just that you'll get 'Dog' when someone is updating it to 'Cat'. Because of page splits during inserts and updates, you could actually have missing rows or extra rows of data. Most businesses I know are fine with the Dog/Cat thing, but kind of freak out when they see duplicate or missing data.
The transaction thing I just don't understand. That's because, actually, every query within the system is already within a transaction all on it's own. So not explicitly using the BEGIN TRAN/COMMIT TRAN syntax doesn't really hurt or help you except that you're taking away a few tools that could come in handy, especially around error trapping.
The JOIN statement... that's... I'm trying to find something nice to say and I'm coming up short. Let's just say that data normalization with enforced referential integrity is a performance enhancer. Here's just one example[/url]. There are millions of others.
I've got a checklist and set of suggestions for good T-SQL in my book on performance tuning (link below). I also have a presentation where I walk through this under development. It's not done, and won't have all the supporting documentation, but I've posted it to SlideShare so you can take a look if you like. Unfortunately, I don't think that's going to help much.
"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
January 7, 2015 at 4:39 pm
syquil (1/7/2015)
That was a good chuckle. Thank you for that.Can you expand on any 'gotchas' or 'no-nos' with the edit window for my own benefit? I typically only use it when I'm seeding brand new tables, and I'm inclined to not leave any window open when I'm done with it out of pure OCD, but given that we have to all share the same server, we can see when edit windows are being used on some larger tables.
The main issue is that the top 200 list can be pretty horrific performance wise. If I remember correctly, it doesn't actually issue a TOP, it just grabs the first 200 rows. That means it processes all of them, but you only see 200. That's probably why they don't want you to use it. You should be writing T-SQL for all that kind of thing anyway.
"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
January 7, 2015 at 5:13 pm
Grant Fritchey (1/7/2015)
syquil (1/7/2015)
That was a good chuckle. Thank you for that.Can you expand on any 'gotchas' or 'no-nos' with the edit window for my own benefit? I typically only use it when I'm seeding brand new tables, and I'm inclined to not leave any window open when I'm done with it out of pure OCD, but given that we have to all share the same server, we can see when edit windows are being used on some larger tables.
The main issue is that the top 200 list can be pretty horrific performance wise. If I remember correctly, it doesn't actually issue a TOP, it just grabs the first 200 rows.
That's not true. It runs "SELECT TOP (200)..." query. So I don't see how TOP 200 query can be bad for performance.
Grant Fritchey (1/7/2015)
That means it processes all of them, but you only see 200.
Not all Microsoft products are perfect, but do you really think they would deliberately make something so stupid?
January 7, 2015 at 5:30 pm
Grant Fritchey (1/7/2015)
syquil (1/7/2015)
The main issue is that the top 200 list can be pretty horrific performance wise. If I remember correctly, it doesn't actually issue a TOP, it just grabs the first 200 rows. That means it processes all of them, but you only see 200. That's probably why they don't want you to use it. You should be writing T-SQL for all that kind of thing anyway.
Yikes, that would be no good. I have heard others say the edit window can be buggy, but 2008/2012 both seem to have a TOP 200 in it, with the '200' being a configurable option. I believe there used to be an 'open table' feature in past versions that did grab all of the rows, though.
That being said, the manager who is rabidly against the edit window isn't against using it, he's against leaving it open because he claims it locks the table and database.
During my testing I found that the edit window (in 2012, at least) is aware that underlying data has changed. If you begin editing a record in the edit window and perform an update statement, it will prompt you that the data has changed.
After feeling that my personal testing was justified, I found this SO post:
I'm wondering if the locked-when-viewing issue is an artifact of older versions, and was taken care of when the 'open table' feature was removed.
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply