September 10, 2010 at 6:51 pm
Thanks for the article.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 11, 2010 at 9:52 pm
I believe that the true beginner's tuning guide boils down to:
A) Pop open Profiler with CPU, Reads, Writes, and Duration on the SQL:BatchCompleted event filtered for the SPID of your session (shown at the bottom of your SSMS/Query Analyzer session, or by SELECT @@SPID)
B) Write your query in several different ways
C) Against your full production-sized dataset, execute each way about three times; note that the first time is likely to show different stats than immediately subsequent times
D) Learn; get a feel for your particular environment.
#Temp tables (with and without indexes, and those indexes created/rebuilt at different places in the code) vs. @table variables vs. CTE's vs. derived tables vs. simple joins.
Personally, I find that on 2000, at least, derived tables hold an advantage for simple queries that need them, and #temp tables hold an advantage for complex queries that need them (even if only used once). Indexing those temp tables; sometimes a big win (especially if it's used more than once), sometimes a loser, sometimes it simply breaks even.
But the basic things remain the same:
Use Profiler
Use a full-size dataset (or bigger, if you're planning for significant growth)
Try different things.
If you're able to, pull up Performance Monitor (perfmon) or Resource Monitor (resmon) as well, and watch how CPU, Disk and Network traffic is flowing, too.
Every environment is different, and must be learned in detail. Maybe CPU is more important, maybe Reads+Writes are more important; it depends (until 2008's compression, I've almost never seen CPU be a bottleneck; with 2008, index rebuilds with page compression I've seen to be CPU constrained even with 16 CPU cores available).
September 12, 2010 at 8:48 am
Specially I learned in tuning a few things:
We should be careful about creating temporary tables, dynamic SQL and joining. Those things are the gold mine for the tuners as eliminating them can make your procedure much faster.
Some of the others brought it up (I especially agree with Grant's and Kevin's posts) and I'll stress, again, that correct usage of Temp Tables and certain types of Dynamic SQL are, in fact, a gold mine for tuners... but only because they work so effectively when done correctly. I agree somewhat on the joins... you have to be careful that you don't build accidental "cross joins" or "triangular joins" and you have to make sure the predicates are sargeable to make effective use of indexes. In fact, you can eliminate many huge join problems by, ironically, creating the right kind of Temp Tables for the right reasons. 😉 It's known as "Divide'n'Conquer".
Be very careful about the functionality of the procedure as your procedure should return the same results after tuning as before. It is not enough that SSMS is returning the "Command(s) completed successfully." message. We should check all the tables used in the procedures. Whether they have the same number of rows and whether the rows are contain the same data. These are the kinds of checks we should perform after tuning.
I'm really happy to see someone say this, especially a "beginner". One of the best things you can do is to set up and execute a series of tests using known data BEFORE you start test and save the results. After tuning, run the exact same tests on the exact same data and compare the results to ensure that NOTHING has changed in the results.
We always want to see the execution plan for each DML statement. The execution plan is the road layout, based on which the optimizer will decide which path will take less time and resources.
I learned to try and avoid table scans. If the query optimizer chooses a table scan, it will scan the whole table, which is far more time consuming than index scan. If possible, create indexes where table scan is used (though based on the size of the table, optimizer sometimes decide to go for a table scan though there are indexes present for the column, but in this case table should contains a small number of records).
Yep... that's definitely a good idea. It'll also let you know which sections of code you might want to check for predicate sargeablility so an index can be more effectively used.
Always try to write all the SQL statements in ANSI format. Generally, ANSI syntax not reduces the logical Reads, but it is more helpful to understand.
You'll find that statements like that have caused SQL "holy wars". Generally speaking, relegating your code to using only ANSI functionality is like relegating yourself to using only the 4 basic math functions of a scientific calculator and, IMHO, is some of the worst advice anyone could give. No matter what you do, SQL is currently NOT 100% portable nor do I believe it ever should be. It would be like telling people they shouldn't use certain add-ons for C because others may not buy them or may not understand them. Look at all the performance problems posted on this and dozens of other SQL forums and then decide to use ALL the power of SQL that is available no matter which dialect it may be. 😉
As an conclusion, this is my advice to all of you. Do everything you want, there are no specific rules to tune SQL statements.
Very mixed message here, IMHO... You CAN'T do EVERYTHING you want because a lot of people want to write RBAR Cursors, RBAR While Loops, RBAR Recursion, and a wealth of other non-set-based sins into production code because they think it's "good enough" for a limited set of requirements. Do I agree that you should try those things to make sure that you shouldn't use them? Absolutely... and you should never just take someone's word for something. Always set up a test and prove a claim. "A Developer must NOT guess... a Developer must KNOW." Like they said on an NFL/GMC commercial, "Amateurs practice until they get it right... professionals practice until they can't get it wrong."
The ultimate goal is to reduce time and system resources. It is better to do it in some logical way and document what impat your changes have. This allows you to explain it your senior or testing team what you have done and why you have done it.
That's SOOOOOO very true. Better than that, YOU have to understand it so YOU become a better Developer and know enough to not make the same mistake the first time. You also need to be able to prove to yourself that there's no reason to ever write code that is just "good enough" because you'll know the right way to do it the first time and without it ever taking any extra time. If you learn to do your job right, there's no such thing as "premature optimization"... it'll just happen. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 12, 2010 at 10:29 pm
You didn't say, or at least I didn't see, in the article where you were doing this tuning, which environment. Because you didn't say, people might not know, that running DROPCLEANBUFFERS and FREEPROCCACHE are extremely dangerous operations to do to your production system. You've just flushed the cache of all the data and query plans and every application running on the system takes a hit as that data is read from disk instead of memory and the queries all have to recompile (taking longer, as you noted).
That's correct. If you want to clear cache for perticular statement or procedure then
DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle } ]
For example:
select * from table1
GO
SELECT plan_handle, st.text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'select * from table1%';
GO
DBCC FREEPROCCACHE (0x06000800E6057F1EB840340B000000000000000000000000);
GO
Thanks
September 13, 2010 at 8:53 am
Jeff Moden (9/12/2010)
Be very careful about the functionality of the procedure as your procedure should return the same results after tuning as before. It is not enough that SSMS is returning the "Command(s) completed successfully." message. We should check all the tables used in the procedures. Whether they have the same number of rows and whether the rows are contain the same data. These are the kinds of checks we should perform after tuning.
I'm really happy to see someone say this, especially a "beginner". One of the best things you can do is to set up and execute a series of tests using known data BEFORE you start test and save the results. After tuning, run the exact same tests on the exact same data and compare the results to ensure that NOTHING has changed in the results.
+1; returning different results is not the goal of initial tuning.
My favorite method to check and see if results are identical, particularly on nontrivial result sets, is to send each output (preferably over a good set of test cases) to a text file, and use the Windows commands "comp" or "fc" at the command line to do a byte by byte compare. Alternately, do hash checking with the Java Jacksum[/url] (I currently favor options "-a md5+sha1+sha512 -A -m") or full ECC checking with the faster, more useful, and more limited multithreaded par2 with Intel Threaded Building Blocks.
Note that this method also easily exposes problems like TOP 1 or other "we're only using the first row" without an ORDER by returning essentially arbitrary rows.
September 15, 2010 at 4:20 pm
Nadrek (9/13/2010)
Jeff Moden (9/12/2010)
Be very careful about the functionality of the procedure as your procedure should return the same results after tuning as before. It is not enough that SSMS is returning the "Command(s) completed successfully." message. We should check all the tables used in the procedures. Whether they have the same number of rows and whether the rows are contain the same data. These are the kinds of checks we should perform after tuning.
I'm really happy to see someone say this, especially a "beginner". One of the best things you can do is to set up and execute a series of tests using known data BEFORE you start test and save the results. After tuning, run the exact same tests on the exact same data and compare the results to ensure that NOTHING has changed in the results.
+1; returning different results is not the goal of initial tuning.
My favorite method to check and see if results are identical, particularly on nontrivial result sets, is to send each output (preferably over a good set of test cases) to a text file, and use the Windows commands "comp" or "fc" at the command line to do a byte by byte compare. Alternately, do hash checking with the Java Jacksum[/url] (I currently favor options "-a md5+sha1+sha512 -A -m") or full ECC checking with the faster, more useful, and more limited multithreaded par2 with Intel Threaded Building Blocks.
This is easy enough to do right in SQL server, without ever having to go to an external tool:
SELECT 'removed' as [issue], * FROM
(Select * From #OldResults EXCEPT Select * From #NewResults) as Lost
UNION ALL
SELECT 'added' as [issue], * FROM
(Select * From #NewResults EXCEPT Select * From #OldResults) as Found
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 16, 2010 at 8:22 am
RBarryYoung (9/15/2010)
Nadrek (9/13/2010)
Jeff Moden (9/12/2010)
Be very careful about the functionality of the procedure as your procedure should return the same results after tuning as before. It is not enough that SSMS is returning the "Command(s) completed successfully." message. We should check all the tables used in the procedures. Whether they have the same number of rows and whether the rows are contain the same data. These are the kinds of checks we should perform after tuning.
I'm really happy to see someone say this, especially a "beginner". One of the best things you can do is to set up and execute a series of tests using known data BEFORE you start test and save the results. After tuning, run the exact same tests on the exact same data and compare the results to ensure that NOTHING has changed in the results.
+1; returning different results is not the goal of initial tuning.
My favorite method to check and see if results are identical, particularly on nontrivial result sets, is to send each output (preferably over a good set of test cases) to a text file, and use the Windows commands "comp" or "fc" at the command line to do a byte by byte compare. Alternately, do hash checking with the Java Jacksum[/url] (I currently favor options "-a md5+sha1+sha512 -A -m") or full ECC checking with the faster, more useful, and more limited multithreaded par2 with Intel Threaded Building Blocks.
This is easy enough to do right in SQL server, without ever having to go to an external tool:
SELECT 'removed' as [issue], * FROM
(Select * From #OldResults EXCEPT Select * From #NewResults) as Lost
UNION ALL
SELECT 'added' as [issue], * FROM
(Select * From #NewResults EXCEPT Select * From #OldResults) as Found
An useful SQL, good for finding any rows which are different, but it doesn't notice ordering issues when those are important (for example, many direct to output report applications):
SELECT *
INTO #OldResults
FROM generic.dbo.Tally32k
ORDER BY N ASC
SELECT *
INTO #NewResults
FROM generic.dbo.Tally32k
ORDER BY N DESC
SELECT * FROM #OldResults
SELECT * FROM #NewResults
DELETE FROM #NewResults WHERE N = 5
INSERT INTO #NewResults VALUES('-5')
SELECT 'removed' as [issue], * FROM
(Select * From #OldResults EXCEPT Select * From #NewResults) as Lost
UNION ALL
SELECT 'added' as [issue], * FROM
(Select * From #NewResults EXCEPT Select * From #OldResults) as Found
DROP TABLE #OldResults
DROP TABLE #NewResults
October 4, 2010 at 10:07 am
I think some of this was mentioned to some extent in the replies, but here are my two cents:
1. Subtree cost: Not sure how often people use this, but I have been taught to look at this as a measuring stick when tuning a stored procedure. It is supposed to combine all the costs (io/cpu ...) into one, and provide an overall cost figure. Maybe it is not often used because there isn't a good documentation on it [or any at all?] into what the numbers actually mean. I always try to reduce the number, and in my experience an ideal stored procedure would have this number in the range of 0.1 to 0.2 and once it crosses 1.0 it indicates trouble. NOTE:This is for SELECT only stored procedures which are expected to return results in milliseconds.
2. Memory tables: In my experience I try to avoid #temp tables and use @temp tables [i.e. variable tables in memory]. I do this only in cases where the the resulting data to be stored in the temp table is known before hand and is not too big [i.e. 100 rows and just a few small columns]. I found that using #temp tables in sps which are called thousands of times over a period of 1 hour is not a very good thing
3. sp_recompile: I use this command when tuning a stored procedure to only take off the execution plan of sp I am tuning from procedure cache. But of course, still better not to do it in production, although good to do in a production like server to have real tuning results [i.e. not to tune for the data size in test environment and then get in trouble once it is pushed to production].
4. If a stored procedure has IF/ELSE, where with IF you touch one set of tables and with ELSE another set of tables, I found that breaking it into 3 sps, one 1 main one, which based on IF/ELSE condition calls the appropriate one of the other two would speed things up. I think it is because it fixes the problem of the original sp where it would create one execution plan which would not work in the other case
5. Index Rebuild: In a production server where data gets deleted/inserted a lot (like ours), indexes would need to be rebuild periodically [ideally, daily]. Otherwise, you might be tuning an sp which takes 10 seconds, which would be taking milliseconds if you simply rebuilt your indexes.
Thanks!
October 4, 2010 at 11:25 am
sql_er (10/4/2010)
I think some of this was mentioned to some extent in the replies, but here are my two cents:1. Subtree cost: Not sure how often people use this, but I have been taught to look at this as a measuring stick when tuning a stored procedure. It is supposed to combine all the costs (io/cpu ...) into one, and provide an overall cost figure. Maybe it is not often used because there isn't a good documentation on it [or any at all?] into what the numbers actually mean. I always try to reduce the number, and in my experience an ideal stored procedure would have this number in the range of 0.1 to 0.2 and once it crosses 1.0 it indicates trouble. NOTE:This is for SELECT only stored procedures which are expected to return results in milliseconds.
While you can use these numbers as a measure, because they are just estimates based on the operators chosen by the optimizer and the statistics available to the optimizer, you have to remember that they're just estimates. The values were based, so the story goes, on a developers machine in Microsoft back in 1997-1998, not on any real measure of performance or cost on any modern system. Keep that in mind when you use these numbers. Also remember, these things can tell lies. For example, a multi-statement table valued function has no statistics. Because of this, it's cost, regardless of what it does, is extremely low in the estimates. But in fact it can be a very costly operation.
2. Memory tables: In my experience I try to avoid #temp tables and use @temp tables [i.e. variable tables in memory]. I do this only in cases where the the resulting data to be stored in the temp table is known before hand and is not too big [i.e. 100 rows and just a few small columns]. I found that using #temp tables in sps which are called thousands of times over a period of 1 hour is not a very good thing
Just remember that #temp tables are in memory, exactly the same as @temp tables, and @temp tables can be written out to disk exactly the same as #temp tables. The one difference between the two that is substantial is the lack of statistics in @temp tables. When dealing with very small data sets, say less than 100 rows, like you say, the missing stats are not a problem. As the data set grows, it becomes more and more of an issue.
3. sp_recompile: I use this command when tuning a stored procedure to only take off the execution plan of sp I am tuning from procedure cache. But of course, still better not to do it in production, although good to do in a production like server to have real tuning results [i.e. not to tune for the data size in test environment and then get in trouble once it is pushed to production].
Just so you know, if you alter a stored procedure, the next time you run it, it generates a new plan. You don't need to use sp_recompile to get it to compile a new plan.
4. If a stored procedure has IF/ELSE, where with IF you touch one set of tables and with ELSE another set of tables, I found that breaking it into 3 sps, one 1 main one, which based on IF/ELSE condition calls the appropriate one of the other two would speed things up. I think it is because it fixes the problem of the original sp where it would create one execution plan which would not work in the other case
I used to think this too, but in fact what happens is, you'll get multiple plans, regardless of how you work it. You'll get a plan for the wrapper, and then a plan for each individual SELECT statement. You can validate this using the DMOs for execution plans.
5. Index Rebuild: In a production server where data gets deleted/inserted a lot (like ours), indexes would need to be rebuild periodically [ideally, daily]. Otherwise, you might be tuning an sp which takes 10 seconds, which would be taking milliseconds if you simply rebuilt your indexes.
Thanks!
"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
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply