Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance testing and tuning for a beginner


Performance testing and tuning for a beginner

Author
Message
Misha_SQL
Misha_SQL
SSChasing Mays
SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)

Group: General Forum Members
Points: 607 Visits: 983
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.



venkatbjsubs
venkatbjsubs
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 0
Great article dude. very simple and helpful information.
Thanks for all of your efforts.
Hardy21
Hardy21
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1204 Visits: 1399
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.

Thanks
mlabedz
mlabedz
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 91
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.
tommyh
tommyh
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1560 Visits: 2000
mlabedz (9/10/2010)
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 juniors in my office and I got the deer in the headlights look from them.

Just wanted to provide you some feedback.


There fixed it for you :-D
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17569 Visits: 32254
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.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5941 Visits: 8299
I had intended to write pretty much everything Grant did - thanks for saving me the time Grant!

I will add one thing:

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.


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. ;-)

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
TheCrazyLudwig
TheCrazyLudwig
SSC Veteran
SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)

Group: General Forum Members
Points: 219 Visits: 73
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):
http://weblogs.sqlteam.com/jeffs/archive/2007/04/30/60192.aspx

-TheCrazyLudwig
David Data
David Data
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 810
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.
cdesmarais 49673
cdesmarais 49673
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1163 Visits: 1423
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search