September 20, 2012 at 9:47 am
Lynn Pettis (9/20/2012)
You are missing my point. You can't say, for example, that using correlated subqueries is bad all the time. Different ways of accomplishing the same task should be explored and tested, and the best solution selected.
Even the post above that basically says don't use scalar or multistatement tvf is not always correct. The different solutions need to be tested and the best one selected. This testing should include scalability testing, or as Jeff Moden would do, the million row test (sometimes you may need more!).
That's why I'm planning on a topic to test the sql code analyze performance...
I'm already reading Gail's topics and blog to get some ideas on what to write there...
Thanks,
Pedro
September 20, 2012 at 9:58 am
Lynn Pettis (9/20/2012)
Even the post above that basically says don't use scalar or multistatement tvf is not always correct.
Well, I didn't say never to use it, I said Try using iTVF over scalar and msTVF where it can be done.
.
I said out developers have never used it since. That doesn't mean that others won't need it.
September 20, 2012 at 10:07 am
Nils Gustav Stråbø (9/20/2012)
Lynn Pettis (9/20/2012)
Even the post above that basically says don't use scalar or multistatement tvf is not always correct.
Well, I didn't say never to use it, I said Try using iTVF over scalar and msTVF where it can be done.
.
I said out developers have never used it since. That doesn't mean that others won't need it.
I do stand corrected regarding what you said, but the addition of the fact that your developers aren't using scalar or msTVF's could be read to say don't use them. All I was trying to convey is that alternative solutions should be tested.
September 20, 2012 at 10:10 am
Nils Gustav Stråbø (9/20/2012)
I would also add a bullet point regarding scalar functions vs inline table valued functions vs multi-statement tables valued functions. Try to explain how a scalar function and msTVF is used in an execution plan vs a iTVF. Try using iTVF over scalar and msTVF where it can be done.I showed our developers the performance and execution impact between these types of functions, and they have never used anything but iTVF since 🙂
Can you give me an example of those or a link to check them?
Thanks,
Pedro
September 20, 2012 at 10:29 am
To start with...
http://sqlinthewild.co.za/index.php/2009/04/29/functions-io-statistics-and-the-execution-plan/
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
September 20, 2012 at 10:54 am
Lynn Pettis (9/20/2012)but the addition of the fact that your developers aren't using scalar or msTVF's could be read to say don't use them.
I can absolutely agree that my last sentence can be misinterpreted, but it was not my intention to say "never" 🙂
@OP, please note that I didn't mean that scalar or msTVF should never be used. They can usually be replaced my iTVF, but there are certainly cases where I have used msTVF that could not be replaced by iTVF.
September 20, 2012 at 1:25 pm
I'm going to nitpick a bit about a few items here the others didn't mention (yet), as they concentrated on the huge issues.
PiMané (9/20/2012)
* Always create FK for referenced columns to check referential integrity instead of triggers or IF (SELECT .. FROM ..)
I don't understand your concern with IF SELECT... FROM... here. Bear with me. In the case of an update/insert statement, where you'll be exclusive locking a row (or rows), pre-checks for known common 'typos' are not always a bad thing to do before opening exclusive locks. You know which records are bad using a shared lock before processing. I'm not saying go overboard and do this for every blessed thing, but I wouldn't make this a 'never'. Besides, controlling error reports to user or front end interpretable errors for data-level errors (this isn't a schema error, it's usually a typo) allows for more user-friendly reporting of the issue.
Again, something I'd only do for highly common errors, but it's something I *do* do often enough pre-processing.
* Avoid CURSORs, try to replace with WHILE loop (this may be a bit slower but when in a multiprocessing environment it better)
A cursor IS a while loop, and constructed correctly doesn't even hold locks on the source table. Both are loops. A better statement would be to 'avoid loops when possible'.
* Declare all variables inside a SP on top and use SET after (query optimizer reuses query plans better this way)
What are you trying to accomplish here? This is news to me that there's any other way to do it. The only thing I can infer is that you're doing a parameter transfer to local variables to completely avoid parameter sniffing, which is not something you want to do except in extreme circumstances.
* Use (SELECT TOP 1 1 FROM ...) if wants to check if a record exists and don't need the resultset
This is an old concern from previous versions of SQL Server (around 7.0). SELECT * and SELECT 1 for IF EXISTS checks function equivalently. I don't disagree with breaking the habit of SELECT * from developers, but just as a side note.
* If necessary to store binary data use FILESTREAM
Be careful of this. LOB data through filestream keeps data out of the database directly, yes, but it has its own cons. I assume you've reviewed this thoroughly and decided it's right for your system. Anytime LOB data gets discussed, each column should be reviewed explicitly. I fear anyone making a bulk decision for any LOB data, it means it's too danged common.
* Use SET TRANSACTION LEVEL READ UNCOMMITTED if the SP has no need to lock records or the NOLOCK
I'm going to comment here as well, simply for emphasis of the others. This is a bad idea. RCSI and other alternatives are now available to help with heavy OLTP system write lockouts during heavy reads. Do not do this. It's error prone.
I've made a few of these documents and honestly I don't get directly into coding styles with them because as mentioned before, It Depends. You're trying to do training via a standards document, I'm not sure this is really the best approach. They need to understand why the rules are there and when it makes sense to break them, not follow a blind list of rules. At worst, you need to be peer reviewing their work as it comes through and discussing with them, one on one, why an alternative method makes more sense.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 21, 2012 at 2:43 am
Nils Gustav Stråbø (9/20/2012)
Lynn Pettis (9/20/2012)but the addition of the fact that your developers aren't using scalar or msTVF's could be read to say don't use them.
I can absolutely agree that my last sentence can be misinterpreted, but it was not my intention to say "never" 🙂
@OP, please note that I didn't mean that scalar or msTVF should never be used. They can usually be replaced my iTVF, but there are certainly cases where I have used msTVF that could not be replaced by iTVF.
This post about ITVF and MTVF says it all... but can you give me an example where iTVF can't replace a msTVF?
Thanks,
Pedro
September 21, 2012 at 3:35 am
Evil Kraig F (9/20/2012)
I don't understand your concern with IF SELECT... FROM... here. Bear with me. In the case of an update/insert statement, where you'll be exclusive locking a row (or rows), pre-checks for known common 'typos' are not always a bad thing to do before opening exclusive locks. You know which records are bad using a shared lock before processing. I'm not saying go overboard and do this for every blessed thing, but I wouldn't make this a 'never'. Besides, controlling error reports to user or front end interpretable errors for data-level errors (this isn't a schema error, it's usually a typo) allows for more user-friendly reporting of the issue.Again, something I'd only do for highly common errors, but it's something I *do* do often enough pre-processing.
Aren't FKs faster to check than the IF SELECT .. ? If we name our FKs we can catch the specific error on code and display a more user-friendly error message..
A cursor IS a while loop, and constructed correctly doesn't even hold locks on the source table. Both are loops. A better statement would be to 'avoid loops when possible'.
Yep, that the main goal.. but if they can't be avoided is it better to insert the "cursor" data on a cursor LOCAL FORWARD_ONLY FAST_FORWARD or on a table variable and loop it?!
What are you trying to accomplish here? This is news to me that there's any other way to do it. The only thing I can infer is that you're doing a parameter transfer to local variables to completely avoid parameter sniffing, which is not something you want to do except in extreme circumstances.
This is something I read on some blog. It says that if you have IF statements and declare the variables as you need (inside the IF) the query plans change according to the IF condition and doesn't reuse them as much... don't know if its true but...
This is an old concern from previous versions of SQL Server (around 7.0). SELECT * and SELECT 1 for IF EXISTS checks function equivalently. I don't disagree with breaking the habit of SELECT * from developers, but just as a side note.
See your point... made a test with
IF EXISTS (SELECT * FROM ProdutStorages WHERE Stock > 3)
SELECT 'OK'
IF EXISTS (SELECT TOP 1 1 FROM ProdutStorages WHERE Stock > 3)
SELECT 'OK'
on a 1.000.000 rows table and the IO is the same and execution plan also the same... The SQL engine optimizes the queries..
Be careful of this. LOB data through filestream keeps data out of the database directly, yes, but it has its own cons. I assume you've reviewed this thoroughly and decided it's right for your system. Anytime LOB data gets discussed, each column should be reviewed explicitly. I fear anyone making a bulk decision for any LOB data, it means it's too danged common.
If I have LOB data on the tables (NVARBINARY, IMAGE, ...) the SQL Server caches it to memory.... FILESTREAM is cached to the NT file system. This makes more memory available for "normal" data...
I'm going to comment here as well, simply for emphasis of the others. This is a bad idea. RCSI and other alternatives are now available to help with heavy OLTP system write lockouts during heavy reads. Do not do this. It's error prone.
RCSI uses tempdb... unfortunately most our customers have the SQL Server on a regular machine with no RAID and SATA disks... But thanks for the tip, I'll make some testing and see how it behaves.
Our most heavy OLTP operation is inserting a sales document... It has triggers with cursors that call SPs to update the customers accounting, change the products stocks, ... It has many tables operations, and take a while to process. RCSI OR READ UNCOMMITTED can be a solution for reading data but I think I'll have a problem when 10 people try to save a sales document at the same time... Does RCSI make lock on the rows or table? With our current system (I'm not sure what it is cause I haven't analysed it deeply, but think is the default lock) when I insert a sales document with product A , B and C to customer C1 and someone else inserts a sales document with product X,Y and Z to customer C2 it was to wait, the locks are made on the entire table... Is there any system to make a row lock only?
I've made a few of these documents and honestly I don't get directly into coding styles with them because as mentioned before, It Depends. You're trying to do training via a standards document, I'm not sure this is really the best approach. They need to understand why the rules are there and when it makes sense to break them, not follow a blind list of rules. At worst, you need to be peer reviewing their work as it comes through and discussing with them, one on one, why an alternative method makes more sense.
My main goal is to write a document for them to have some basic guidelines, not mandatory, but to avoid some common mistakes. Their SQL scripts have to be inserted on a system that manages the "reference" database and executes them. I have an alert system to send me an email with the script so I can look at it, analyse it and notify the developer if necessary of some mistake he made... After a while I'll know which developers I don't need to monitor and my life will be simpler but in the beginning I have to monitor them all... :w00t:
Thanks,
Pedro
September 21, 2012 at 5:48 am
PiMané (9/21/2012)
Yep, that the main goal.. but if they can't be avoided is it better to insert the "cursor" data on a cursor LOCAL FORWARD_ONLY FAST_FORWARD or on a table variable and loop it?!
Are you asking a question or making a statement here? (!?!)
If a question, the only answer is test and see for that specific situation.
This is something I read on some blog. It says that if you have IF statements and declare the variables as you need (inside the IF) the query plans change according to the IF condition and doesn't reuse them as much... don't know if its true but...
Maybe you were thinking about this, which has nothing to do with declaring variables.
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
See your point... made a test with
IF EXISTS (SELECT * FROM ProdutStorages WHERE Stock > 3)
SELECT 'OK'
IF EXISTS (SELECT TOP 1 1 FROM ProdutStorages WHERE Stock > 3)
SELECT 'OK'
on a 1.000.000 rows table and the IO is the same and execution plan also the same... The SQL engine optimizes the queries..
The TOP 1 in the exists is unnecessary and just a waste of typing.
If I have LOB data on the tables (NVARBINARY, IMAGE, ...) the SQL Server caches it to memory.... FILESTREAM is cached to the NT file system. This makes more memory available for "normal" data...
Errrr... no.
If you access the filestream via T-SQL (SELECT ..., FileStreamData FROM SomeTable), then that filestream data goes through the SQL buffer pool. Only if you access if via the direct APIs does it not.
Filestream isn't automatically better. There's a point at which it's faster to store binary data in the DB and a point that it's faster in the file system (and I'll leave it to you to do the research on what that point generally is)
Our most heavy OLTP operation is inserting a sales document... It has triggers with cursors that call SPs to update the customers accounting, change the products stocks, ... It has many tables operations, and take a while to process.
Sounds like your queries need optimising.
RCSI OR READ UNCOMMITTED can be a solution for reading data but I think I'll have a problem when 10 people try to save a sales document at the same time... Does RCSI make lock on the rows or table? With our current system (I'm not sure what it is cause I haven't analysed it deeply, but think is the default lock) when I insert a sales document with product A , B and C to customer C1 and someone else inserts a sales document with product X,Y and Z to customer C2 it was to wait, the locks are made on the entire table... Is there any system to make a row lock only?
SQL does not lock at the table level by default. The locks it takes are based on the amount of data the operation needs, the amount of other locks, the indexes involved, etc. If a single insert is locking the table, you have poor indexing, poor code or both. To solution here is to tune the queries, not try and play smart with the locking system.
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
September 21, 2012 at 7:34 am
Thanks to all for your wise advises and help...
I'll make a new version of the document's topics and if I can "nag" you again post it here for a "review".
I'll be analyzing all the links and suggestions and recompile them in the new version.
Thanks,
Pedro
September 21, 2012 at 9:20 am
GilaMonster (9/21/2012)
SQL does not lock at the table level by default. The locks it takes are based on the amount of data the operation needs, the amount of other locks, the indexes involved, etc. If a single insert is locking the table, you have poor indexing, poor code or both. To solution here is to tune the queries, not try and play smart with the locking system.
I have a database and executed the ALTER DATABASE RCSIDB SET READ_COMMITTED_SNAPSHOT ON;
If I open a windows and write
BEGIN TRANSACTION
UPDATE Products SET TotalStock = TotalStock + 1 WHERE Product = 10
and open another window and write
UPDATE Products SET TotalStock = TotalStock + 20 WHERE Product = 1100
The 2nd window is blocked until I COMMIT the transaction... It's making a table lock.
How can I make a row lock, or is that not possible?
Thanks,
Pedro
September 21, 2012 at 9:26 am
PiMané (9/21/2012)
GilaMonster (9/21/2012)
SQL does not lock at the table level by default. The locks it takes are based on the amount of data the operation needs, the amount of other locks, the indexes involved, etc. If a single insert is locking the table, you have poor indexing, poor code or both. To solution here is to tune the queries, not try and play smart with the locking system.I have a database and executed the ALTER DATABASE RCSIDB SET READ_COMMITTED_SNAPSHOT ON;
If I open a windows and write
BEGIN TRANSACTION
UPDATE Products SET TotalStock = TotalStock + 1 WHERE Product = 10
and open another window and write
UPDATE Products SET TotalStock = TotalStock + 20 WHERE Product = 1100
The 2nd window is blocked until I COMMIT the transaction... It's making a table lock.
How can I make a row lock, or is that not possible?
Thanks,
Pedro
First question I have, how do you know it is a table lock?
September 21, 2012 at 9:34 am
Lynn Pettis (9/21/2012)
First question I have, how do you know it is a table lock?
Entire table and indexes locked?!
Isn't there a way to lock only a row or data page so records on other rows or data pages can be updated?
Thanks,
Pedro
September 21, 2012 at 9:37 am
If it is a table lock, it's probably because you don't have an index on the Product column.
Tune indexes, optimise code, then and only then try to outsmart the lock manager if you know exactly what you're doing and why.
And no, there is no way to force a row lock. You can ask for locks to start at row (which for queries that affect small amounts of data they probably will anyway), but that does not prevent SQL escalating the locks if if feels the need to do so.
Entire table and indexes locked?!
Is that a question or a statement?
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
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply