May 28, 2014 at 5:49 pm
Hi,
I'm rewriting a poorly performing view, and I want to be able to cleanly and accurately compare the performance of the original vs my rewrite. (I'm not speaking about index changes here, merely changes to the SQL itself.) I'm fairly familiar with using SET STATISTICS and reading execution plans, and that's part of my strategy. I've pulled the SELECT statement out of the view, and I'm analyzing that by itself.
However, the SELECT query returns 100M rows+, and I get an out of memory error on my client machine before the query is able to complete and return statistics. I tried directing the output to a temp table (SELECT ... INTO #results FROM ...), but that has the unfortunate side effect of completely changing the "weighting" in the execution plans. The final insert operation takes 95% of the work, so other small differences scattered throughout the plans are nearly impossible to find.
Another thing I've tried in cases like this is removing all the SELECTed columns, and changing the query to a SELECT COUNT(*). That keeps the joins and where clauses intact, but can still drastically change an execution plan and statistics.
The challenge, really, is to remove factors like network performance and server or client hardware when comparing two versions of a query. I'm not interested in measuring how long it takes to return 100M rows to a workstation or write them to a table; I'm trying to compare two approaches to having SQL Server effeciently retrieve that data regardless of where it goes. This view is not used unfiltered by users directly; it's referenced as an object in several stored procedures, usually further filtered, joined, etc.
I seem to remember an article on this site a few years back, describing this exact problem and posing an innovative solution. As I recall, the article was about comparing a few different specific SQL techniques, but mentioned this along the way. Does this ring a bell for anyone? Or do you have ideas or suggestions? Thanks in advance!
May 28, 2014 at 6:18 pm
Maybe you could use SQL Server Profiler with the correct filters and use the option "Discard results after execution" (Options->Query Results->SQL Server->Results to Grid).
Maybe someone else can give a better advice. 😉
May 28, 2014 at 6:34 pm
Easy solution: script your SELECT to be into variables. Now your 100M rows never have to leave the server and you don't pay the cost (in many ways) of inserting this stuff into a temp object nor sending them across the network to be rehydrated somewhere else. I have been using this technique to tune huge-row-count queries for a very long time.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 28, 2014 at 8:44 pm
I have seen both of those methods used successfully in performance tuning exercises.
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
May 28, 2014 at 10:14 pm
Luis Cazares (5/28/2014)
Maybe you could use SQL Server Profiler with the correct filters and use the option "Discard results after execution" (Options->Query Results->SQL Server->Results to Grid).Maybe someone else can give a better advice. 😉
+1, no change to the code being compared!
😎
May 28, 2014 at 10:39 pm
Eirikur Eiriksson (5/28/2014)
Luis Cazares (5/28/2014)
Maybe you could use SQL Server Profiler with the correct filters and use the option "Discard results after execution" (Options->Query Results->SQL Server->Results to Grid).Maybe someone else can give a better advice. 😉
+1, no change to the code being compared!
😎
But doesn't Discard Results still shove them over to the client?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 28, 2014 at 10:51 pm
TheSQLGuru (5/28/2014)
Eirikur Eiriksson (5/28/2014)
Luis Cazares (5/28/2014)
Maybe you could use SQL Server Profiler with the correct filters and use the option "Discard results after execution" (Options->Query Results->SQL Server->Results to Grid).Maybe someone else can give a better advice. 😉
+1, no change to the code being compared!
😎
But doesn't Discard Results still shove them over to the client?
According to the Client Statistics it does but this bypasses the physical limits of the client (SSMS), hence one can work with huge sets without the display problems.
😎
May 29, 2014 at 12:13 pm
Excellent suggestions, thank you! I wasn't aware of the Discard Results option - amazing how there's always more to learn no matter how long you work with something. And selecting into variables sounds like an interesting approach as well. I'll try them both. Thanks again!
June 3, 2014 at 1:36 pm
Just curious. Since your table has at least 100M rows, did you review the indexes, particularly the clustered indexes?
That usually makes vastly most difference than how the query is written, assuming the query code is reasonable: SARGable, avoiding unnecessary joins and de facto hidden "loops", etc..
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 3, 2014 at 1:52 pm
Just a caution... if the view code uses scalar or multi-statement table value function, you really shouldn't be using SET STATISTICS as a measuring tool because it skews the results... A LOT! Please see the following article...
http://www.sqlservercentral.com/articles/T-SQL/91724/
--Jeff Moden
Change is inevitable... Change for the better is not.
June 3, 2014 at 2:25 pm
Jeff Moden (6/3/2014)
Just a caution... if the view code uses scalar or multi-statement table value function, you really shouldn't be using SET STATISTICS as a measuring tool because it skews the results... A LOT! Please see the following article...
Don't EVER use a statement-level execution Profiler trace when those are in play either!!! Well, I do it once per each visit to a new client just to show them what their code is doing. Seeing MILLIONS of code executions for a single SELECT statement (and then my refactored code taking 6 orders of magnitude less CPU, IO, Duration, whatever) is a GREAT way to get said client to move away from those bad code mechanisms!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 4, 2014 at 11:52 am
TheSQLGuru (6/3/2014)
Jeff Moden (6/3/2014)
Just a caution... if the view code uses scalar or multi-statement table value function, you really shouldn't be using SET STATISTICS as a measuring tool because it skews the results... A LOT! Please see the following article...Don't EVER use a statement-level execution Profiler trace when those are in play either!!! Well, I do it once per each visit to a new client just to show them what their code is doing. Seeing MILLIONS of code executions for a single SELECT statement (and then my refactored code taking 6 orders of magnitude less CPU, IO, Duration, whatever) is a GREAT way to get said client to move away from those bad code mechanisms!! 😎
That's a fantastic idea.
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
June 4, 2014 at 12:01 pm
SQLRNNR (6/4/2014)
TheSQLGuru (6/3/2014)
Jeff Moden (6/3/2014)
Just a caution... if the view code uses scalar or multi-statement table value function, you really shouldn't be using SET STATISTICS as a measuring tool because it skews the results... A LOT! Please see the following article...Don't EVER use a statement-level execution Profiler trace when those are in play either!!! Well, I do it once per each visit to a new client just to show them what their code is doing. Seeing MILLIONS of code executions for a single SELECT statement (and then my refactored code taking 6 orders of magnitude less CPU, IO, Duration, whatever) is a GREAT way to get said client to move away from those bad code mechanisms!! 😎
That's a fantastic idea.
I have heard some quite memorable expletives over the years with this one!! There are a quite a number of other things that I routinely get big "WOW" moments from too! :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply