Post reply

Are "SELECT Statements" cached?

  • April 14, 2010 at 8:16 pm

    #146419

    I know that a stored proc is cached after it is executed and one can neutralize the impact of that first run by using DBCC FreeProcCache. But what about t-sql run from Query Editor? After being executed is it cached? (And if so, how can one clean out the cache?)

    TIA,

    Barkingdog

  • April 14, 2010 at 9:25 pm

    #1151858

    yes, the plan could be cached; it's not guaranteed.

    I have an older book that explains it in some detail...the answer is if SQL thinks there is a high cost for a SELECT query, it will cache the plan; every use of the plan increments it's cost factor, which makes it more likely to stay in cache longer.

    one of the things i found interesting, is it states this (for sql 2000):

    Another issue that can affect whether a query plan can be reused is whether the table or view names in a query are fully qualified with a username. If the owner name must be resolved implicitly, then a plan cannot be reused.

    it gives the example of select * from titles vs dbo.titles, and how the fully qualified object name would be a candidate for caching...

    I assume that 2005/08 still use that rule, so that seems to make it a good practice to fully qualify your object names in order to increase the likelihood of it's plan being cached and improve performance.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 14, 2010 at 10:34 pm

    #1151871

    Lowell,

    That is a very interesting idea which I had not heard of before.

    Thanks,

    Barkingdog

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply