SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Arup Chakraborty / Performance testing and tuning for a beginner / Latest Posts

RE: Performance testing and tuning for a beginner

Grant Fritchey — Mon, 04 Oct 2010 11:25:59 GMT

[quote][b]sql_er (10/4/2010)[/b][hr]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.[/quote]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.[quote]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[/quote]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.[quote]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].[/quote]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. [quote]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[/quote]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.[quote]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![/quote]

RE: Performance testing and tuning for a beginner

sql_er — Mon, 04 Oct 2010 10:07:09 GMT

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 thing3. 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 case5. 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!

RE: Performance testing and tuning for a beginner

Nadrek — Thu, 16 Sep 2010 08:22:33 GMT

[quote][b]RBarryYoung (9/15/2010)[/b][hr][quote][b]Nadrek (9/13/2010)[/b][hr][quote][b]Jeff Moden (9/12/2010)[/b][hr][quote]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.[/quote] 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.[/quote]+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 [url=http://www.jonelo.de/java/jacksum/]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 [url=http://www.chuchusoft.com/par2_tbb/index.html]par2 with Intel Threaded Building Blocks[/url].[/quote]This is easy enough to do right in SQL server, without ever having to go to an external tool:[code="sql"]SELECT 'removed' as [issue], * FROM (Select * From #OldResults EXCEPT Select * From #NewResults) as LostUNION ALLSELECT 'added' as [issue], * FROM (Select * From #NewResults EXCEPT Select * From #OldResults) as Found[/code][/quote]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):[code]SELECT * INTO #OldResultsFROM generic.dbo.Tally32kORDER BY N ASCSELECT * INTO #NewResultsFROM generic.dbo.Tally32kORDER BY N DESCSELECT * FROM #OldResultsSELECT * FROM #NewResultsDELETE FROM #NewResults WHERE N = 5INSERT INTO #NewResults VALUES('-5')SELECT 'removed' as [issue], * FROM (Select * From #OldResults EXCEPT Select * From #NewResults) as LostUNION ALLSELECT 'added' as [issue], * FROM (Select * From #NewResults EXCEPT Select * From #OldResults) as Found DROP TABLE #OldResultsDROP TABLE #NewResults[/code]

RE: Performance testing and tuning for a beginner

RBarryYoung — Wed, 15 Sep 2010 16:20:20 GMT

[quote][b]Nadrek (9/13/2010)[/b][hr][quote][b]Jeff Moden (9/12/2010)[/b][hr][quote]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.[/quote] 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.[/quote]+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 [url=http://www.jonelo.de/java/jacksum/]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 [url=http://www.chuchusoft.com/par2_tbb/index.html]par2 with Intel Threaded Building Blocks[/url].[/quote]This is easy enough to do right in SQL server, without ever having to go to an external tool:[code="sql"]SELECT 'removed' as [issue], * FROM (Select * From #OldResults EXCEPT Select * From #NewResults) as LostUNION ALLSELECT 'added' as [issue], * FROM (Select * From #NewResults EXCEPT Select * From #OldResults) as Found[/code]

RE: Performance testing and tuning for a beginner

Nadrek — Mon, 13 Sep 2010 08:53:42 GMT

[quote][b]Jeff Moden (9/12/2010)[/b][hr][quote]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.[/quote] 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.[/quote]+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 [url=http://www.jonelo.de/java/jacksum/]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 [url=http://www.chuchusoft.com/par2_tbb/index.html]par2 with Intel Threaded Building Blocks[/url].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.

RE: Performance testing and tuning for a beginner

Hardy21 — Sun, 12 Sep 2010 22:29:09 GMT

[quote]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). [/quote]That's correct. If you want to clear cache for perticular statement or procedure then [code="sql"]DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle } ][/code]For example:[code="sql"]select * from table1GOSELECT plan_handle, st.textFROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS stWHERE text LIKE N'select * from table1%';GODBCC FREEPROCCACHE (0x06000800E6057F1EB840340B000000000000000000000000);GO[/code]

RE: Performance testing and tuning for a beginner

Jeff Moden — Sun, 12 Sep 2010 08:48:48 GMT

[quote]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. [/quote]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".[quote]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.[/quote] 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.[quote]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).[/quote] 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.[quote]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.[/quote]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. ;-) [quote]As an conclusion, this is my advice to all of you. Do everything you want, there are no specific rules to tune SQL statements.[/quote]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."[quote]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.[/quote]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. ;-)

RE: Performance testing and tuning for a beginner

Nadrek — Sat, 11 Sep 2010 21:52:18 GMT

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 waysC) 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 timesD) 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 ProfilerUse 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).

RE: Performance testing and tuning for a beginner

SQLRNNR — Fri, 10 Sep 2010 18:51:58 GMT

Thanks for the article.

RE: Performance testing and tuning for a beginner

cdesmarais 49673 — Fri, 10 Sep 2010 12:02:16 GMT

The very first things I look at in a proc are cursors, scalar UDF's, and IN abuse. Table/clustered index scans and temp tables may or may not be appropriate, I think blanket warnings may take people in the wrong direction.

RE: Performance testing and tuning for a beginner

David Data — Fri, 10 Sep 2010 11:25:13 GMT

I'd like to endorse what others have said about temp tables.I use CTEs a lot, and often they make the query both more legible and faster. But sometimes they seem to overload the optimiser and result in a very slow plan. I have speeded up some UPDATEs from minutes to seconds by splitting them into a SELECT INTO a temp table then an UPDATE FROM a join to that table, compared to my original code that used WITH cte ... UPDATE.

RE: Performance testing and tuning for a beginner

TheCrazyLudwig — Fri, 10 Sep 2010 09:34:04 GMT

First time posting, been reading for a while.First off, nice article. Performance is something that I have always loved tweaking. I figured I would share one of my favorite articles (goes over set-based logic):[url=]http://weblogs.sqlteam.com/jeffs/archive/2007/04/30/60192.aspx[/url] -TheCrazyLudwig

RE: Performance testing and tuning for a beginner

TheSQLGuru — Fri, 10 Sep 2010 09:09:14 GMT

I had intended to write pretty much everything Grant did - thanks for saving me the time Grant!I will add one thing: [quote]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.[/quote] Actually those are a gold mine for me for the OPPOSITE reason - I often get huge performance GAINs from adding in the first two of those things. Temp tables can be used to split up massive join queries to get the optimizer better metrics to have more efficient plans in subsequent processing. And dynamic SQL can provide exact values for optimal statistics retrievals which can also lead to optimal plans (and certainly the dreaded nested-loop-with-a-kajillion-row disasters). I do note that this is an ADVANCED usage from someone who has been doing SQL Server relational engine design and tuning for going on 15 years now. ;-)

RE: Performance testing and tuning for a beginner

Grant Fritchey — Fri, 10 Sep 2010 06:30:56 GMT

First, the positives:Way to go on pointing out that you need to validate data during the tuning process. This is something that it's far too easy to assume and doesn't get pointed out often enough.Keeping track of the changes made over time and the results is also a great idea while tuning. If nothing else it provides an excellent path to learning what works and what doesn't while tuning. Again, something that a lot of people miss.Also, finally, well done on taking a consistent approach to your tuning. Always running the procedures one way from SSMS is great advice when doing the tuning work.Unfortunately, I've also got a few negatives: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). Especially because this is intended as an introductory level article, that information is vital to people who don't yet know what they're doing.Speaking of people needing information who don't know what they're doing, you talked about Profiler through the whole article. Again, in a production environment, using the Profiler GUI is dangerous. Instead it's very highly recommended that you use the server side trace through trace events started from TSQL and output to file.Too much focus on reads, just as too much focus on duration, can be misleading. You need to work on both. You can have only a few reads on the system and still have a badly tuned query.I'm sure it's just sentence structure, but the way you wrote it, you're advocating for the elimination of joins in queries. I'd suggest that's not necessarily a good approach.Index scans can be just as costly, maybe even more so, than table scans. Plus, remember, the clustered index is the table, so getting a clustered index scan is effectively identical to getting a table scan. Going from a table scan to a clustered index scan, in most circumstances, won't improve performance at all.I'd suggest trying to put more cautions into your articles, especially when writing for beginners, so they understand where the weaknesses in a given approach may lie. Especially when advocating for things like cleaning out the cache or using the Profiler GUI.

RE: Performance testing and tuning for a beginner

tommyh — Fri, 10 Sep 2010 06:00:16 GMT

[quote][b]mlabedz (9/10/2010)[/b][hr]First off, I think the article was well intended. However, being a seasoned vet with SQL and SQL performance, I think that your article was more of an intermediate guide rather than a beginners guide. I have shared it with some of the [b]juniors[/b] in my office and I got the deer in the headlights look from them.Just wanted to provide you some feedback.[/quote]There fixed it for you :-D

RE: Performance testing and tuning for a beginner

mlabedz — Fri, 10 Sep 2010 05:45:22 GMT

First off, I think the article was well intended. However, being a seasoned vet with SQL and SQL performance, I think that your article was more of an intermediate guide rather than a beginners guide. I have shared it with some of the junior DBA's in my office and I got the deer in the headlights look from them.Just wanted to provide you some feedback.

RE: Performance testing and tuning for a beginner

Hardy21 — Fri, 10 Sep 2010 00:58:54 GMT

Good Introductory article.Performance tuning is varying on case to case basis but you need to first identify where the problem or bottleneck is.Some times, query is perfect, it is following all best practices (like indexes, joins are correct) but hardware is not capable enough to provide the support.Some times, just adding CTE in the select query and use that CTE table in join - solve the purpose.

RE: Performance testing and tuning for a beginner

venkatbjsubs — Sat, 03 Jul 2010 21:25:33 GMT

Great article dude. very simple and helpful information.Thanks for all of your efforts.

RE: Performance testing and tuning for a beginner

mishaluba — Wed, 10 Dec 2008 19:41:21 GMT

Good introductory article. Have to somewhat disagree with you on the temp tables though. Breaking huge complex query into more manageable smaller queries, which utilize temp tables may sometimes boost the performance. I have seen it change dramatically. I don't think there any universal rule on this. You just have to test both approaches to see which one performs better.Thank you.

RE: Performance testing and tuning for a beginner

Ninja's_RGR'us — Wed, 19 Nov 2008 04:29:48 GMT

I think he is talking about a case to save let's say the application's user's parameter or stuff like that, where the usage of the db is not strickly required. This is more a case of correct analysis and planning. If the apps needs the db, then use it, otherwise it might be worth it to use the users' local machine to save some workload on the server.

RE: Performance testing and tuning for a beginner

Alberto dbLearner — Tue, 18 Nov 2008 15:17:25 GMT

[quote][b]crainlee2 (11/17/2008)[/b][hr]This seems so fundamental that I almost think I shouldn't mention it but I will anyway.When designing a database, its indexes, and the queries that access it, do everything you can to:1. not use the database at all (I've known programmers who used it for logging when a flat file would have been much more efficient.),2. avoid physical disk writes (they are significantly slower than disk reads),3. avoid physical disk reads.Your hard drives are the slowest components on your system by several orders of magnitude. Do everything you can to design for the use of the system and database caches. This will speed up everything. This means lots of memory dedicated to SQL Server and correct indexing of all search fields.And no correlated sub-queries - period.LC [/quote]Interest Thing you’re proposing: Not to use the database at all, just cache. But how? And what if you don’t have enough memory? I encourage you to write an article about that.

RE: Performance testing and tuning for a beginner

Alberto dbLearner — Tue, 18 Nov 2008 15:14:43 GMT

Interest Thing you’re proposing: Not to use the database at all, just cache. But how? I encourage you to write an article about that.

RE: Performance testing and tuning for a beginner

cliffm — Tue, 18 Nov 2008 04:43:52 GMT

This was an excellent beginner’s article. Performance tuning is an art in itself.It’s particularly confusing when your first starting out, as there is such a wide variety of issues to consider, not only in identifying which measurements are important but also identifying the correct measures to take to improve performance.The article has also encouraged other users to post their experiences and knowledge of performance tuning which will also be useful for the beginner.I look forward to reading the next article.

RE: Performance testing and tuning for a beginner

sqlservercentral-943251 — Tue, 18 Nov 2008 03:24:56 GMT

Not interresting article at all. It tells nothing... I'm so sad

RE: Performance testing and tuning for a beginner

Kari Suresh — Tue, 18 Nov 2008 00:53:00 GMT

Nice article. Really good one.

RE: Performance testing and tuning for a beginner

changbluesky — Mon, 17 Nov 2008 22:51:55 GMT

so glad to join this article and saw many splendid ideas about the performance tunning tips..But we should think about the fragmentation about the database table.It will reduce the performance more if more fragmentation exists in the table.You can check the fragmentation of the table through the view sys.dm_db_index_physical_stats. The view returns size and fragmentation information for the data and indexes of specified or view.Syntax:sys.dm_db_index_physical_stats ( { database_id | NULL | 0 | DEFAULT } , { object_id | NULL | 0 | DEFAULT } , { index_id | NULL | 0 | -1 | DEFAULT } , { partition_number | NULL | 0 | DEFAULT } , { mode | NULL | DEFAULT })The fragmentation level of an index or heap is shown in the avg_fragmentation_in_percent column. So, you can decide whether you need rebuild or reorganize the index of the table to reduce the framentation.----==----:hehe:

RE: Performance testing and tuning for a beginner

Gail Wanabee — Mon, 17 Nov 2008 15:10:20 GMT

This seems so fundamental that I almost think I shouldn't mention it but I will anyway.When designing a database, its indexes, and the queries that access it, do everything you can to:1. not use the database at all (I've known programmers who used it for logging when a flat file would have been much more efficient.),2. avoid physical disk writes (they are significantly slower than disk reads),3. avoid physical disk reads.Your hard drives are the slowest components on your system by several orders of magnitude. Do everything you can to design for the use of the system and database caches. This will speed up everything. This means lots of memory dedicated to SQL Server and correct indexing of all search fields.And no correlated sub-queries - period.LC

RE: Performance testing and tuning for a beginner

Dcarlson — Mon, 17 Nov 2008 14:14:39 GMT

Good Job! Thanks for the input.

RE: Performance testing and tuning for a beginner

rtreff — Mon, 17 Nov 2008 13:04:05 GMT

Great easy to understand article!

RE: Performance testing and tuning for a beginner

Mohit K. Gupta — Mon, 17 Nov 2008 11:11:54 GMT

Nice article :).I spend a lot of time on performance tuning; it is my favorite activity. I find many people don't like to pay attention to it. I find biggest problems in vendor applications and access developers applications. Microsoft Access developers don't think about all the affects when they are working on the dataset, at least not the concurrent data access. So I often find developers selecting the entire table. So doesn't matter how many indexes, covering indexes, or include indexes you built it will be slow. And I usually get a reply when Microsoft Access could do it ... Sure it can when you only have 10k records with only few users hitting it. I usually prove to them the point using READS, and lots of diagrams on Indexes and how SQL Server stores information.As you said you focused on READS, I look at those too. In addition to I look at the number of rows returned. On recent application I tuned the application was generating 150K reads, and I was like great another full table select. When looking at the rows returned it was less then 100 rows; so that didn't turn out to be the issue. In this case developer was at least being nice, but if you average the rows to read that’s 1500 Logical reads per row. Best case would be 2-3 reads/row because of how the data is accessed. In my case it turned out the function call was a problem; RBAR issue. Again good article, just my two cents on tuning I seen ;). As you said there is no set way for tuning, I usually tell people at work. I can't teach you tuning, it is experience and art; you learn it. And I usually hand them the "Dissecting SQL Server Execution Plans" by Grant Fritchey. Really good book to learn more basics :).Thanks.

RE: Performance testing and tuning for a beginner

Andrew Peterson-472853 — Mon, 17 Nov 2008 10:12:41 GMT

Points for writing and posting the article. And a good start. For smallist, in-house corp applications tuning seems to be ignored or overlooked. And even with externally facing internet applications, I've found performance and tuning only become important when problems arise. Keep tuning.

RE: Performance testing and tuning for a beginner

Raj Gujar — Mon, 17 Nov 2008 09:27:18 GMT

This was a great artical. Here is my Experience. In My Previous Job we had a Stored Procedure that used to Take around 6 Hours to Run (Batch JOB) use to do a Lot of Things on a 35 GB Database. Many a times it used to fill up the Temp DB and Crash. Here are few things I did to fix it, and there was a great Improvement (1 Hour 40 Mts.)There were a few Very Complex Query and used to Join with 9 Big Table. I broke that query and created a Temp Table, Used a While Loop on the Temp Table. Removed the Dynamic SSQL. Removed the Function like AVG , SUM on a Large Query and added it to Sub Query, Remember those function are lot of Overheads. Of course I had to create few Index.But Yes !! There are quite a few ways of tuning a Stored Procedure. My Favorate is breaking up joins into various small queries and Pumping the Info in Temp Table and then Join them.I am not sure if this is the best way but has always worked for me ;)R A J

RE: Performance testing and tuning for a beginner

Ric Sierra — Mon, 17 Nov 2008 09:26:32 GMT

It's a nice topic.The "tuning" is more than just apply the "best practices" to the T-SQL, but is the first step to try to improve the performance of your system.Many of ours peers avoid the T-SQL tuning because have a very good HW supporting behind the scenes, and crashes when the database become a millions of records and the qrys give them bad time response, then is when they start thinking in improve HW; and the solution could be tuning code.Saludos a todos!

RE: Performance testing and tuning for a beginner

Mike Byrd — Mon, 17 Nov 2008 07:25:27 GMT

Well I agree with your statement about being careful with temporary tables, don't ignore them. In many cases I have taken a complex multi-table query and broken it up into simpler queries that have significant less overall query cost. This is especially true when the original complex query contains a PK table scan on a very large table and then joining it to a derived result set in lieu of performing an index seek on the large table. This is especially obvious when looking at the query plan and observing large intermediate result sets (thick lines) where there should not be such. Usually when I am given a stored proc to "tune", after cleaning it up (converting to ANSI-92 standard for readibility, SET NOCOUNT ON, moving DDL statements to top of stored proc, and fully qualifing object names) I analyze the query plan to see if it makes sense. As M$ frequently states, the query optimizer doesn't pick the best plan, just one that it thinks is OK. My job then is to "help" the optimizer pick a better plan. Usually I use profiler to determine which stored procs need attention, then the query plan to identify statements within the stored proc that need attention.Also, don't forget to consider revising/adding indexes to help performance. The INCLUDE clause in SQL Server 2005 is a "God send" for covering indexes. Cheers,Mike Byrd

RE: Performance testing and tuning for a beginner

karunnkumar — Mon, 17 Nov 2008 05:54:38 GMT

nice article man... :) done a great job and research... gr8 going .. and waiting for the next article which u have said to do so..

RE: Performance testing and tuning for a beginner

arup chakraborty — Mon, 17 Nov 2008 05:39:56 GMT

HI Addai and Anirban,Many thanks for your valued review.

RE: Performance testing and tuning for a beginner

Anipaul — Mon, 17 Nov 2008 05:24:12 GMT

Nice article ...:)

RE: Performance testing and tuning for a beginner

Addai Mununkum — Mon, 17 Nov 2008 05:19:25 GMT

That is a resourceful post. As you stated, there is no one single approach to tuning a proc. It is purely a case by case issue, so a variety of testings have to be made after baseline numbers have been taken, and thereafter, examining and comparing new readings of various performance indicators. with each modification. The fact is that you have to start from somewhere, and eliminate those indicators that do not improve with changes until eventually, the real cause(s) of the performance is identified and then tweaked to achieve improvement. As stated, it could be problems with indexes, memory & I/O, temp tables, network, how the T_SQL itself had been written, contention of server resources etc... I think one of the principal problems is identifying the real cause of the issue, and that has to do with using the right tools and understanding the tools in use.

Performance testing and tuning for a beginner

arup chakraborty — Sun, 16 Nov 2008 21:36:53 GMT

Comments posted to this topic are about the item [B]Performance testing and tuning for a beginner[/B]