Alexander Suprun (1/7/2015)
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?
Which is why I said, "If I remember correctly." I didn't. Oops.
"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
Alexander Suprun (1/7/2015)
Grant Fritchey (1/7/2015)
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.When you have to do a lot of updates/modifications on a live production database during business hours and none of these scripts have been tested in QA\UAT the only thing you can do to protect yourself is to explicitly open a transaction, run the query, check the result and then commit transaction if everything looks good or rollback otherwise. If someone forget to close transaction or if update takes too long then you will get a lot of blocking issues.
The whole approach is wrong in the first place. It's not about open transaction or not, but about the deployment process and security policies which definitely have to be changed. Only few people should have access to modify production databases and they should perform the deployments during scheduled hours.
If updates take too long it doesn't matter if it's part of an explicit transaction or not. It's part of the implicit transaction anyway, so the blocks get taken out in order to ensure the ACID properties of the database are maintained.
"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
You mention a contractor gets involved when things go awry...........
Sounds like that person has a LOT of job security/work for the long future with these rules in place !!!!
Alexander Suprun (1/7/2015)
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?
Thats correct in my experience, it uses top, and in my experience if it can identify the row to change it works, otherwise it bails, so if you have a small table you'd like to edit this way the little window does what it advertises in my experience, and if the table is bigger than 200 rows its sort of a moot point (although in my experience it still works even in this case, just might not nab the row you want to edit, thats all).
Like Alexander already mentioned, thats the "optomistic" concurrency control at work.
Grant Fritchey (1/7/2015)
Alexander Suprun (1/7/2015)
Grant Fritchey (1/7/2015)
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.When you have to do a lot of updates/modifications on a live production database during business hours and none of these scripts have been tested in QA\UAT the only thing you can do to protect yourself is to explicitly open a transaction, run the query, check the result and then commit transaction if everything looks good or rollback otherwise. If someone forget to close transaction or if update takes too long then you will get a lot of blocking issues.
The whole approach is wrong in the first place. It's not about open transaction or not, but about the deployment process and security policies which definitely have to be changed. Only few people should have access to modify production databases and they should perform the deployments during scheduled hours.
If updates take too long it doesn't matter if it's part of an explicit transaction or not. It's part of the implicit transaction anyway, so the blocks get taken out in order to ensure the ACID properties of the database are maintained.
If you have 50 update statements then it does matter.
We can take these 6 suggestions and pretty much use them as a basis for an E-book called 6 complete and utter myths about SQL server. Some of this made me laugh pretty good (especially the ones about stored procedures and transaction).
A lot of
syquil (1/7/2015)
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.
Absolute bull.
RDP is far more resource-intensive than connecting to SQL, plus it means that you need access to Windows, which you should never have on a production server
2 - Always use the NOLOCK hint on every select query.
Translation: "Always ensure that any data returned from the DB has the option of being completely wrong."
3 - Never use transactions. "Transactions can destroy the server"
Never take data consistency and the atomicity of data modifications into account. We don't care if operations are half-done and result in bad data
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.
The reason is wrong, but I agree with the rule. The edit can be a little buggy, I've seen it hang SSMS. Far better to write proper update statements
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
I think this list is missing one extremely important "Best Practices" point:
#7 Never connect to SQL SERVER by any means and never try to run any query against it as it can bring a server down!
*I do like idea about not using JOINs, but all of us should admit that using WHERE clauses is pretty dangerous as well. So, ban it too!
Stop here! I got it!
You are working for Reynholm Industries!
Do your managers keep SQL Server in the same box with Internet?
You better tell them that it is outdated practice, it should be removed from there ASAP and placed into separate box with at least two buttons, modern boxes for SQL Server keeping also have LED screens and in-built heating element for hot-fixes...
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply