Having looked at the procedure in detail, I would very much encourage you to read:

http://www.sommarskog.se/dyn-search-2005.html

That should give you some ideas.

Cheers,

Paul

shnex,

Have you done any of the analysis work using the suggestions already posted to see if re-compilation is even an issue on this server?

The use of dynamic SQL will not...

Also take a look at:

http://support.microsoft.com/kb/308737

You can get a general idea of the optimization time for all queries from the dynamic view: sys.dm_exec_query_optimizer_info

If you are experiencing delays due to compilation, you may see a number...

Tracey,

We take snapshots every five minutes as a matter of routine. When collecting data for a SAN upgrade for example, we capture snapshots every 10s for 24 hours on...

It seems that the application is using a prepare/execute model - where a query is prepared once and then executed one or more times with different parameters later on. ...

Hey Nick,

It's really difficult to give more than general advice based on what you have provided, so:

Take a look at http://support.microsoft.com/kb/308737 for an approach you might take.

Plan guides and USE...

A.J. Wilbur (5/22/2009)

---

Yep, mixing DDL with DML in stored procedures is a sure-fire way to get a re-compile. Best practices mentioned grouping all DDL (like your Create table statement...

As Grant says, unless you have SET FORCEPLAN ON, use OPTION(FORCE ORDER), or use a join hint (like INNER HASH JOIN), the order in which you write the joins does...

As an alternative to running Profiler continuously, there are a number of third-party products which would have helped you here. One I am familiar with and can recommend (also...

A great way to measure I/O statistics is to use the dynamic view sys.dm_io_virtual_file_stats which will give you detailed statistics server-wide. (Our SAN people use data collected from this...

Hey Ran,

The short answer is yes. The long answer follows.

You say that you are SELECTing data with the NOLOCK hint, so I am going to limit what I say...

Lynn Pettis (5/24/2009)

---

Well, it is what the rest of the world calls football or futbol. Sorry, but us Americans...

What is this "soccer" everyone keeps referring to? :laugh:

Bruce W Cassidy (5/21/2009)

---

[font="Verdana"]Can't get rid of mental image of a posse of baseball-bat wielding DBA's wanting to chat about kneecap...