SQLServerCentral / Discuss Content Posted by Mike Dillon / Article Discussions / Article Discussions by Author / How SQL Server Chooses the Type of Join / Latest Posts

RE: How SQL Server Chooses the Type of Join

Charles Kincaid — Fri, 21 Nov 2008 08:39:10 GMT

I agree with Lowell, mostly. If jino.jose had said "Enterprise Manager" then I would not even posted a reply. Management Studio does get the whole recordset. Open a big table, sit back, and watch your memory drain away.You are right about pushing the data "down the wire". That could be the culprit. Same thing to Luiz. Even if they are using the same plan they don't use the same libraries to fetch the result set.I remember the same controversy when ADO came out. More capable, takes more memory, works slower.

RE: How SQL Server Chooses the Type of Join

Lowell — Fri, 21 Nov 2008 07:27:34 GMT

here's one possibility:if it's not parameter sniffing as mentioned above, which uses a bad execution plan so it's slow,it might actually be the data and the time it takes to move the data over the wire (or wireless)i believe if i select 10 billion rows from a table with QA or SSMS, it doesn't really fetch all 10 billion rows...it executes the query, but really grabs the top few hundred rows, and as i scroll down, it fetches more and more rows...it doesn't do it all at once.but if i try to move those ten billion rows to a dataset, it's gotta move all ten billion rows over the network instead of the top few hundred, and then the local disk has to allocate some space in memory for the dataset, which might get paged to the harddisk because there;s not enough ram....how big is the recordset you are grabbing.

RE: How SQL Server Chooses the Type of Join

jino.jose — Fri, 21 Nov 2008 06:56:12 GMT

Hi friends, I am new to this and my issue is my stored procedure runs very fast in management studio less than 1-2 seconds. And when i try to get those results in a dataset it takes 8-10 seconds. My Application code is below myConnection.Open(); SqlDataAdapter MyAdapter = new SqlDataAdapter(myCommand); MyAdapter.Fill(ds);any one can suggest any ideas?Jino

RE: How SQL Server Chooses the Type of Join

Luiz-458831 — Tue, 01 Apr 2008 14:02:59 GMT

If I have a situation where Query Analyzer and .NET, both running under the same parameter values, take different time to finish (.NET is slower), can I assume that Query Analyzer and .NET are using different cache plans?If so, are both plans stored in a system table?Thanks,Luiz.

RE: How SQL Server Chooses the Type of Join

Sean Zhang-369248 — Tue, 19 Feb 2008 13:59:52 GMT

Another technique we use to disable the so called "parameter sniffing" instead of WITH RECOMPILE is to declare local variables to replace the parameters immediately inside the stored procedure. That way, the optimizer would not know to use ("sniff") a particular set of parameter values passed in during 1st execution to come up with the query plan, but rather use the typical values. So we would not run into the risk of a plan optimized on "atypical" values. The advantabe of this technique is plan-reuse - the plan may not be the optimal for all scenarios, but probably good enough for all of them.We have tested that technique for some of our stored procedures and it seems to be working fine. We are weighing that against WITH RECOMPILE to decide when and where to use either.

RE: How SQL Server Chooses the Type of Join

KWymore — Wed, 13 Feb 2008 08:43:41 GMT

There have been scripts posted on the site before to go through and recompile all stored procs and refresh all views. These have been particularly helpful for me in the past. Good article topic!Here is one of them:http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31902/

RE: How SQL Server Chooses the Type of Join

sing4you — Wed, 13 Feb 2008 07:05:26 GMT

Wow, thanks for the article. This is the kind of mystery that would have me banging my head against a wall for days. I was unaware of sp_recompile and with recompile. I'm going to research these options immediately.Thanks for saving my head :D

RE: How SQL Server Chooses the Type of Join

Michael Earl-395764 — Wed, 13 Feb 2008 05:31:01 GMT

Just another option...Rather than using RECOMPILE in your procedure, using an OPTOMIZE FOR and specifying date parameters that give you your hash joins may give you better performance overall and avoid recompiling the procedure each time it runs.It would force the optimization engine to avoid the loop joins all of the time, but if the performance of small date ranges os ok this way, your overall time to execute the entire set of batches may go down.

RE: How SQL Server Chooses the Type of Join

Andy Hilliard — Tue, 12 Feb 2008 21:57:07 GMT

Wouldn't it be more effective to either call two different stored procedures (one for wide date ranges, one for narrow) or use the HINT option to force the JOIN type? That way you wouldn't incur the overhead of a RECOMPILE with every call of the stored procedure.There's a good writeup on HINTs here: http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx

RE: How SQL Server Chooses the Type of Join

Shon Thompson — Wed, 23 May 2007 14:13:00 GMT

Does anyone know how to solve this problem with a function? It appears that using hints is the only option. The WITH RECOMPILE option is not supported for user defined functions.

RE: How SQL Server Chooses the Type of Join

BobAtDBS — Wed, 09 May 2007 06:07:00 GMT

If the statistics were "bad", or insufficiently sampled, why would running the query with the recompile option help? You'd be making a new query plan with the same bad or incomplete information, and I would think you'd come up with the same ill-chosen query plan.

I believe RichardReynolds is correct in concept though. If your table is not made up of homogenous data, you need to sample at a much higher rate than default. Because I believe that's true, we rebuild all indexes each week (on Sunday where we have very few users online). That gives us a complete statistics sample every week.

Still, we see the same problem described here. Even with proper statistics, once the query plan has been built and cached for the first set of data it encounters, it seems to be reused even if statistics would show that it should be be reevaluated for another set of parameters for the same query. We use the option recompile for several such Stored Procedures.

My opinion is, that the query plan does not get automatcially recompiled when a quick glance at the spread of data for a given set of parameters would indicate that it should. In my mind, this is in the "bug" category.

RE: How SQL Server Chooses the Type of Join

MVDBA — Wed, 09 May 2007 02:47:00 GMT

rather than looking at recompiles and statistics, how about looking at your joins themselves. would it be possible to have 2 versions of the proc - each with distinct join hintsone usign loop joins and the other using merge or hash joins. this would avoid the constant recompiles.

RE: How SQL Server Chooses the Type of Join

richard.reynolds75013 — Tue, 08 May 2007 15:08:00 GMT

This does happen on occasion with any SQL server installation but the main reason actually is statistics. In fact it is more than likely the sample level of the statistics. What is happening is that the sample rate is probably at the default and on a large table (few million rows or more) this default rate is not adequate for the amount of data you have therefore SQL is not able to accurately determine the amount of rows that the input vars are going to fetch therefore it is using the old plan.

Try something.

1) Take this same procedure and duplicate the issue with OUT issuing a recompile in the proc. Once you can confirm that you know exactly how to duplicate the issue look at the execution plan and the estimated row count for each (the fast query and the slow query). Run the proc without the recompile in both cases and see how close it is. What you will most likely find is that the version that takes longer than it should will have the highest delta between the estimated row count and the actual which is typically directly related to the fact that the statistic sample rate is not high enough for the tables involved.

2) Now update the statistics on the tables involved, (update the stats for each index on every table used as well as the system statistics on the fields in those tables) and force it to use the FULL Scan sample rate. Perform the same test in step 1 again and you should see that the execution plan will change and the estimated row count is more accurate.

DBCC SHOW_STATISTICS ( Table_Name_Here , Index_Name_Here ) --run this and look at the Rows Sampled field and if this is a few million row table you will see that it is not actually sampling very much. In my case I did this on "small" 2 million row table and the rows sampled was 81k which equates to about 4% or so.

Thanks..Richard

RE: How SQL Server Chooses the Type of Join

Mike Dillon — Tue, 08 May 2007 07:03:00 GMT

Thanks for all of the feedback. I think the with recompile option is probably the best choice looking back. The hints thing didn't work because you really didn't know the size of the tables. Just because it was a large date range didn't mean it was a larger data set. I wanted and trusted the server to choose the right plan, which it did when it was recompiled. I also want to clear up that I wasn't blaming .Net. I was just stating what we saw, but the points that have been raised are valid and should be considered when trouble shooting. For me, the big thing here was discovering how and why the engine chose the type of join it did, and what that join was best at. The difference between query analyzer and the .Net application just help to shed some light on it. I am pleased with all of the discussion that has taken place and all of the wonderful ideas that have come from the discussion. Thanks for keeping it positive and adding value to the discussion. -mike

RE: How SQL Server Chooses the Type of Join

Dave F-425609 — Tue, 08 May 2007 04:34:00 GMT

Good Article Mike. Well done!Cheers,Dave

RE: How SQL Server Chooses the Type of Join

Martin Bastable — Tue, 08 May 2007 02:45:00 GMT

I had a similar problem with a query that was being run in a ms access front end vs. me trying it directly on sql server. Access was taking 30 seconds, sql server was taking <1 second.

Turned out when the access called sql was being run, sql server wasnt using an index, which it was when run directly in query analyser (I did some background checking and looking at query plans to find this out).

I added a index hint to the access sql on a field/index in question to force its use, and BANG, <1 second in my access version.

martin

RE: How SQL Server Chooses the Type of Join

garryurban — Tue, 08 May 2007 01:57:00 GMT

very useful as I'm struggling with exactly the same problem:a storeed procedure that joins two tables based on a selection from one table that can return as few as 50 rows or as many as 2 million!I will test this and see how it runs.

RE: How SQL Server Chooses the Type of Join

tymberwyld — Mon, 07 May 2007 22:01:00 GMT

In my experience with running Queries in .NET apps vs. Query Analyzer is that most don't actually call the Procedures or Ad-hac queries in the same way. For example, it IS a best practice to always use a "parameterized command" in .NET, this way you code will hit the same Cached Plan REGARLESS if it's ad-hoc or a Stored Procedure being called.So, imagine I have a .NET app that is using a Parameterized Command. And on the 2nd call it retrieves a much bigger data set than the 1st call. In this case, it will use the same cached plan because the only thing changing is the command parameter values. Howedver, in Query Analyzer, most don't take the time to actually declare "variables" for their ad-hocs there, and instead call the Procs by explicitly setting values on it.Example:SqlCommand command = new SqlCommand;command.CommandText = "spGetData";command.CommandType = StoredProcedure;command.Parameters.Add(...);command.ExecuteNonQuery();// This execution always hits the same plan-- Now for Query AnalyzerExec spGetData '01/01/2006', '01/01/2007-- This will NOT use the same plan if other dates are used-- because the parameters are changingDeclare @Date1 DateTime, @Date2 DateTimeSelect @Date1 = '01/01/2006', @Date2 = '01/01/2007'Exec spGetData @Date1, @Date2Select @Date1 = '01/01/2006', @Date2 = '01/01/2008'Exec spGetData @Date1, @Date2-- This WILL use the same query plan and could potentionally be slower...All I'm saying is that you should be careful how you are testing the speed and query plans. You can't blame a .NET everytime something is slow. I am agreeing with the "WITH RECOMPILE" option, but I also might try to prepend a "Merge, Hash, or Nested" Join clause in my joins if I could find an optimal solution that way.

RE: How SQL Server Chooses the Type of Join

Leo Miller — Mon, 07 May 2007 16:02:00 GMT

I've just finished a(nother) SQL course and this exact senario was discussed with both of these solutions proposed. i.e. using WITH RECOMPILE or using an itermediate SP to decide which final SP to call, based on the data range. We have this problem with one of our apps at the moment.

Has anyone used the DBCC FREEPROCCACHE call? Say using it on a nightly or weekly basis from a job? This would force a recompile of all procs

Leo

RE: How SQL Server Chooses the Type of Join

sqldba-294117 — Mon, 07 May 2007 15:20:00 GMT

I am curious to know in reference to this article

a>whether any dynamice sql statement were created within store procedure

b> is it possible to splitting it into multiple small stored procedures and calling them from a single stored procedure

c> are you generating any Errors from Stored Procedures(raise error) or try-catch in case sql serer 2005 to return to application

d> Does the enduser as that permission to call this store procedure & is the owner of this store procedure & how it is calling, is it calling with full qualify name

e> if store procedure is not called by qualifying the owner name, SQL Server enters the compile code and acquires a COMPILE lock on the procedure & determines that a new plan is not required assuming no other reasons apply, so it does NOT recompile the plan at this point due to the lack of qualification. However, the extra step of getting a COMPILE lock on the procedure can cause blocking contention in severe situations

f> are you using parameterization in your store prodecure for sql statement & which are executed using sp_executesql.

g> KEEP PLAN option is helpfull if you are creating any temp table, as no plan is generated untill they are created. Microsoft says that to ensure that the optimizer uses the best plan in all cases concerning temporary tables, a special algorithm was developed to be more aggressive with recompilations.The algorithm states that if a temporary table created with a stored procedure has changed more than six times, the procedure will be recompiled when the next statement references the temporary table. KEEP PLAN does not prevent recompilations altogether, it simply prevents those caused by more than six changes to temporary tables referenced in the procedure.

h> The following five SET options are set to ON by default: ANSI_DEFAULTS ANSI_NULLS ANSI_PADDING ANSI_WARNINGS CONCAT_NULL_YIELDS_NULL If you execute the SET statement to set any of these options to OFF, the stored procedure will be recompiled every time it runs. The reason for this is that changing these options may affect the query result that triggered the recompilation. using the option of KEEP PLAN here won't help to avoid the recompilation because the cause of the recompilation is from the SET statement.

i> The following table lists some common SET statements and whether or not changing the SET statement in a stored procedure causes a recompile: Set Statement Recompile Set quoted_identifier No Set arithabort Yes Set ansi_null_dflt_on Yes Set ansi_defaults Yes Set ansi_warnings Yes Set ansi_padding Yes Set concat_null_yields_null Yes Set numeric_roundabort No Set nocount No Set rowcount No Set xact_abort No Set implicit_transactions No Set arithignore No Set lock_timeout No Set fmtonly No

The reason i focused on recompile is that as you menioned that your store procedure is using WITH RECOMPILE clause,while above specified steps is implictily recompling procedure cache which i thing is double overhead. as i hope the above information might have already been considered while troubleshooting store procedure. i just try to add some more additional tips if that helps.

Thanks

Jay

RE: How SQL Server Chooses the Type of Join

Frank Albanello — Mon, 07 May 2007 14:38:00 GMT

Charles Kincaid

Thanks for your response

That is what I thought. I have run Drop/Creates but is does not seem to make a difference I must be doing something wrong. I don't mind brute force as long as it works. All this stuff is a learning experience so I wouldn't be surprised if I did it wrong. I'll try again.

RE: How SQL Server Chooses the Type of Join

Charles Kincaid — Mon, 07 May 2007 14:24:00 GMT

Frank:

I used to have problems like this too. I kept scripts for all my views and stored procedures. The script has a drop statement at the top followed by the create. Any time anything changed (add an index, add a column, etc) I would merge all my little scripts into one big one and run it. All new execution plans in the cache.

Brute force, I know, but you have to do what you have to do.

RE: How SQL Server Chooses the Type of Join

J Christin — Mon, 07 May 2007 12:06:00 GMT

What really helped me from the article was the info about hash joins vs nested loop joins. I wound up doing some more research and I was able to put some hints on a few of our key joins and it sped up the procedure from 7 seconds to 3. During testing I found that the hash joins were slower than the nested loop joins so I tried merge joins and voila!

Here's a good link on joins

http://blogs.msdn.com/craigfr/archive/2006/08/16/702828.aspx

RE: How SQL Server Chooses the Type of Join

Frank Albanello — Mon, 07 May 2007 11:53:00 GMT

Steve Jones

Thanks for your response

You said "They didn't have a good way to fix it other than flush or restart the server" That is part of my problem once the SP slows down I can not do what you suggest because my site is hosted so I do not have authorization to flush or restart. This is actually the second time this has happened. The first time the hosting service restarted the server fixing the problem. I reworked all my SP to try and improve them. Everything worked fine for about 5 months then the SP's slowed down again.

I've tried:- Dropping Tables, Stored Procedures, Indexes- Updating statistics- Reworking SP's- Forcing SP recompile with (sp_recompile and WITH RECOMPILE)

The Only thing that worked once was for the Hosting service to restart the server and I don't think they will do that again. How can I force all of my cache to clean out so I can start the cache process from scratch ?

Thanks in advance

RE: How SQL Server Chooses the Type of Join

Mark Shvarts 1 — Mon, 07 May 2007 11:52:00 GMT

If sp_recompile is placed inside of stored procedure it will actually recompile the next time it runs. From here, for the current execution it uses the plan compiled at previous run. So it's still unclear how it solved the problem. As it was mentioned by other members above, to use WITH RECOMPILE clause would be more beneficial.

Anoter benefit of using WITH RECOMPILE is that it instructs engine not to place execution plan in procedure cache, thereby saving on overhead of placing it there.

RE: How SQL Server Chooses the Type of Join

Marty Benson — Mon, 07 May 2007 10:55:00 GMT

If you can guarantee that a result set with a large date range will have a LOT of data, I would probably choose to evaluate the date range in the initial stored procedure and have it call one of two procedures.If the date range is large, then call Procedure A without a recompile.If the date range is small, then call Procedure B with a recompile (unless you can also guarantee that a small date range will always have a LITTLE data).This way you save the time of the recompile because Procedure A should always have the same optimum plan.

RE: How SQL Server Chooses the Type of Join

Charles Kincaid — Mon, 07 May 2007 10:11:00 GMT

Actually the fact that an SP uses the cached plan is one of the oft touted reasons to use SP's rather than dynamic SQL. It save all that time compiling an execution plan.

I've seen cases where compiling the plan was much longer than the actual execution. If your SP breaks a task down to where it runs a whole lot of simple querries of dynamic nature the the recompile option might be helpful.

RE: How SQL Server Chooses the Type of Join

TJ-183067 — Mon, 07 May 2007 09:47:00 GMT

Can someone post short example of how to use sp_recompile or with recompile option? I looked at MS reference. But not good enough.

Thanks.

RE: How SQL Server Chooses the Type of Join

Steve Jones - SSC Editor — Mon, 07 May 2007 08:29:00 GMT

Both QA and app use cache, but the way the cache gets used is that exactly the same command needs to run, down to the whitespace. This means that it's possible when you run from QA, you might force a recompile or a different plan.Frank,We actually saw this at Andy's old company during TechEd 05. We queried Christian Kleinerman of the storage engine and a few other MS guys at the time. They said that at times they've seen a plan mistakenly flushed and then a bad one chosen under load, especially memory pressure. They didn't have a good way to fix it other than flush or restart the server. That seemed to work for Andy and so they did that.If you do that, one suggestion they had was to set a startup script that runs some important queries before the app to try and build a cache up. As mentioned, be sure that you use the same query that's expected.More reading: http://blogs.msdn.com/sqlprogrammability/archive/2007/01/23/4-0-useful-queries-on-dmv-s-to-understand-plan-cache-behavior.aspx

RE: How SQL Server Chooses the Type of Join

Frank Albanello — Mon, 07 May 2007 07:08:00 GMT

Why did Query Analyzer always run faster? Doesn't QA use the Cache?

I have a similar problem except my stored procedure will run for months in the application then suddenly slow down to the point of timing out.

albanello

RE: How SQL Server Chooses the Type of Join

Brian Kesselman — Mon, 07 May 2007 07:05:00 GMT

A little off topic. I have seen similar results (slow .NET access but fast in Query Analyzer) that seemed to be solved with "SET ARITHABORT ON". Maybe by doing that I am forcing a recompile and it is the same problem. Of course you need to be careful that the actual procedure doesn't have the potential for an actual error that ARITHABORT will falsely handle.

Hope that helps someone else. I would welcome any other feedback on a relationship between how SQL Server and .NET are actually handling these differently at a code level. Maybe there is some other solution that is more appropriate.

Enjoy,

Brian

RE: How SQL Server Chooses the Type of Join

Igor Kutsyy — Mon, 07 May 2007 06:43:00 GMT

Nice. I agree with others, you should use "with recompile" statement . And more, if you are using MS SQL 2k5, you may use "with recompile" on a statement rather on the batch.

And some comments about the network level in .NET. If you would be having problem with that layer, I guess you would see a waiting status for your spid with something like 'NETWORKIO'.

Best Regards,

Igor.

RE: How SQL Server Chooses the Type of Join

Andrew Stanton — Mon, 07 May 2007 06:33:00 GMT

This is good info, but I don't see how it matches the article title. Would anyone mind "join"-ing the dots for me?

RE: How SQL Server Chooses the Type of Join

Lowell — Mon, 07 May 2007 05:50:00 GMT

valuable article; whenever I found a situation similar to this, I always blamed out of date statistics, and updated statistics and recompiled procedures; nice detective work, I have a better understanding now.

RE: How SQL Server Chooses the Type of Join

Saurabh Kulkarni — Mon, 07 May 2007 04:23:00 GMT

I am a novice and don't know much about the SQL server engine but Microsoft documentation claims that "Microsoft® SQL Server™ automatically recompiles stored procedures and triggers when it is advantageous to do so."

I found this claim at http://msdn2.microsoft.com/en-us/library/aa238892(SQL.80).aspx

Not sure what is it. Could some expert explain?

RE: How SQL Server Chooses the Type of Join

VincentRainardi — Mon, 07 May 2007 03:55:00 GMT

Thank you for posting this useful article, Mike. I also had the same experience in the past with the same cause (sp_recompile).

Regards, Vincent

RE: How SQL Server Chooses the Type of Join

Chirag-436975 — Mon, 07 May 2007 00:01:00 GMT

Great!! Recently one of the stored procedure was behaving some what in the similar way as mentioned, I did use sp_recompile which made it much faster, but didnt knew the reason behind..!!

The Great article for explaning the behaviour of sp..!!

RE: How SQL Server Chooses the Type of Join

Lawrence Smith-270662 — Sun, 06 May 2007 23:03:00 GMT

You're on the money with your detective work.

However, I agree with Alex that you should have considered the WITH RECOMPILE option with your create procedure statement. That's probably in line with your intent.

I'm curious, did you call the sp_recompile on your stored procedure from within the stored procedure itself?

-Larry

RE: How SQL Server Chooses the Type of Join

Alex Afo — Sun, 06 May 2007 22:35:00 GMT

Thanks for the article, Mike!

Just curious: Why have not you used WITH RECOMPILE option? I believe one has more flexibility with this option if specified in a procedure call statement. Alternatively a procedure can be created with this option.

And the join hints can tell SQL to use a particular join every time, just in case

How SQL Server Chooses the Type of Join

Mike Dillon — Wed, 07 Feb 2007 20:38:00 GMT

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/mdillon/2863.asp