August 19, 2016 at 5:17 pm
I ran a query and it took about 5s to return. Then I ran the same query again and it returned in < 1s. I ran the query again with a different value in the where clause and it the query still returned in < 1s so I'm guessing that SS cached the query plan. Is this the proper terminology to describe what SS did? I was thinking that only sprocs cached the query plan but I guess that an SSMS session will do this as well. Or does this have to do with re-running the query within the same connection that was used to run the query the first time?
I'm assuming that this also means that when the sproc runs that contains this SQL, the first execution will take 5s and subsequent calls will take < 1s. Does this seem like a correct assumption? In this case do you normally disregard the initial exec time of SQL and rely on the exec time after the query plan is cached? I seem to remember that there's some type of SS sproc that can be run to precompile the sproc and cache the query plan. Can you provide the name of that sys sproc? In the scenario I described, should I expect a < 1s return time on initial exec if I use the SQL as written but run the sys sproc I mentioned before exposing the sproc to prod?
August 20, 2016 at 3:14 am
It's far more likely that the time difference is due to data caching than the plan being cached. The QO doesn't take seconds to generate query plans in most cases, but if a lot of data is needed it can take time to fetch it off disk if it's not in cache.
There's no command to 'pre-compile' a query, other than running the query, and even if there was, it wouldn't help if the time difference is due to whether the data is in cache or not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply