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»»»

How SQL Server Chooses the Type of Join Expand / Collapse
Author
Message
Posted Monday, May 7, 2007 7:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 14, 2014 6:57 AM
Points: 10, Visits: 90

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

 

Post #363633
Posted Monday, May 7, 2007 8:29 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 11:08 AM
Points: 31,371, Visits: 15,839
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







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #363668
Posted Monday, May 7, 2007 9:47 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 12:36 PM
Points: 348, Visits: 24

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.

Post #363717
Posted Monday, May 7, 2007 10:11 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: Today @ 12:33 AM
Points: 824, Visits: 2,038

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.



ATB

Charles Kincaid

Post #363731
Posted Monday, May 7, 2007 10:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 18, 2010 12:27 PM
Points: 22, Visits: 40
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.
Post #363752
Posted Monday, May 7, 2007 11:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 9:22 AM
Points: 386, Visits: 441

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.

 

Post #363768
Posted Monday, May 7, 2007 11:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 14, 2014 6:57 AM
Points: 10, Visits: 90

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

Post #363769
Posted Monday, May 7, 2007 12:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, October 25, 2014 5:47 PM
Points: 9, Visits: 41

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

Post #363771
Posted Monday, May 7, 2007 2:24 PM


SSC Eights!

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

Group: General Forum Members
Last Login: Today @ 12:33 AM
Points: 824, Visits: 2,038

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.



ATB

Charles Kincaid

Post #363809
Posted Monday, May 7, 2007 2:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 14, 2014 6:57 AM
Points: 10, Visits: 90

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.

Post #363811
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse