"Best Practices" Questions
-
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.
Alex Suprun -
syquil (1/7/2015)
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.
I would have to test this more but based on what I saw in the profiler it just issues blanket update statements when you change a record based solely on data happened to be in your query window when you pulled up the edit records. IE while it looks like it's actually a direct connection to the database it's not which can be very very misleading.
That said you should never be using to edit any large frequently used table in a production setting anyways, while it might be acceptable for small tables or tables in a dev/test system it's not a robust production level data editing tool.
-
syquil (1/7/2015)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.
That is simply wrong conclusion. Do you see that data changes in edit window? No. Then it's not "aware".
It will only check if data has changed when it actually tries to run an UPDATE statement. This kind of behavior called Optimistic concurrency control.
Alex Suprun -
ZZartin (1/7/2015)
That said you should never be using to edit any large frequently used table in a production setting anyways, while it might be acceptable for small tables or tables in a dev/test system it's not a robust production level data editing tool.That's how I typically see it used by others, which seems acceptable. I'd personally just use a begin/rollback block, but I'm not allowed.
-
Alexander Suprun (1/7/2015)
That is simply wrong conclusion. Do you see that data changes in edit window? No. Then it's not "aware".It will only check if data has changed when it actually tries to run an UPDATE statement. This kind of behavior called Optimistic concurrency control.
Right - I know what you mean and that's what I was driving towards, I neglected to say that the modal shows up when you attempt to commit. "Aware" wasn't meant to mean "actively aware", just that it responds in a meaningful way in this particular scenario.
-
Some of these do look like rules we had with SQL 6.5 and I think Sybase. Is it possible they are just really old?
-
The only reason to agree with not using "edit top 200" is because it is potentially dangerous to have people manually editing data, but it's not inherently bad - it doesn't lock or block anything, however it is a rule that is not particularly "stupid" or "dangerous" to adhere to, just a bit blugh!
The rule about NOLOCK can sometimes be grudgingly acceptable - there are applications out there (looking at you, Sage) that do not use Optimistic Concurrency, but instead will attempt to lock data when it is read and hold the locks until it is finished with the data (Pessimistic), and believe it or not, that can sometimes be for as long as the user keeps a screen of data open <- yes, all day has been known. In situations like this, it is common for there to be a general rule that all SELECTS should use NOLOCK to prevent every report or enquiry from being blocked by the person on their tea break. As long as it is understood that there are risks to this, it can be a sensible option for general enquiry queries, but remember the problems it can bring...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);- Forum Etiquette: How to post Reporting Services problems
[/url]- Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]- How to Post Performance Problems - by Gail Shaw
[/url] -
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
-
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
-
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.
Alex Suprun -
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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 36 total)
You must be logged in to reply to this topic. Login to reply