Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Performance testing and tuning for a beginner Expand / Collapse
Author
Message
Posted Wednesday, December 10, 2008 7:41 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, April 18, 2014 5:57 PM
Points: 535, Visits: 729
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.



Post #617534
Posted Saturday, July 03, 2010 9:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, July 03, 2010 9:24 PM
Points: 1, Visits: 0
Great article dude. very simple and helpful information.
Thanks for all of your efforts.
Post #947286
Posted Friday, September 10, 2010 12:58 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:42 AM
Points: 890, Visits: 1,179
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
Post #983571
Posted Friday, September 10, 2010 5:45 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 12:51 PM
Points: 86, Visits: 88
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.
Post #983674
Posted Friday, September 10, 2010 6:00 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, November 21, 2013 11:33 PM
Points: 1,481, Visits: 1,959
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

Post #983680
Posted Friday, September 10, 2010 6:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:14 AM
Points: 14,803, Visits: 27,283
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #983701
Posted Friday, September 10, 2010 9:09 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 5:07 PM
Points: 4,128, Visits: 5,840
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
Post #983869
Posted Friday, September 10, 2010 9:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 1:29 PM
Points: 219, Visits: 59
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
Post #983887
Posted Friday, September 10, 2010 11:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:26 AM
Points: 115, Visits: 739
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.
Post #983993
Posted Friday, September 10, 2010 12:02 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 18, 2014 3:28 PM
Points: 774, Visits: 1,031
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.
Post #984016
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse