Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chmbrssh
Starting Member
3 Posts |
 Posted - 2012-08-21 : 12:02:12
|
| Hi,I have noticed that a stored proc takes up to 40 seconds when first run, and then less than 1 second on subsequent calls. This as indicated in an excellent article (http://blog.sqlauthority.com/2010/02/18/sql-server-plan-recompilation-and-reduce-recompilation-performance-tuning/), is because each time a new connection is made to the database an entry in dm_exec_cached_plans has to be created. Once created the databasehas an execution plan and thus the query will be much faster.This explains why if you stop and restart sql server this entry in dm_exec_cached_plans will dissapear and thus need to be replaced by running the procedure again.We are about to deploy our solution to the client site, and I want to ensure that this application (which is a silverlight app with SQL Server back end) runs as fast as possibloe first time.From my undertstanding I would need to run the stored proc before hand so that an entry is made to dm_exec_cached_plans. My questions around this are:1) If the data in the underlying tables are the same, will different parameter varaiations need to be called with the stored proc, so that new entries in dm_exec_cached_plans can be inserted, or will the same stored proc use the same dm_exec_cached_plans entry regardless of parameter values.I ask as the article mentions that:"It could be that the data amount in underlying tables changed so much that the previously created plan is not optimized"This refers to the underlyting physical table, which is different to the derived table that my stored proc will return, which depending on parameters passed can return very different amounts of data. 2) In the same vein as above do I need to log in as each user from the silverlight application, in order that an execution plan is stored for each one, or it is sufficient to simply log in as a simple user, which will in turn create an entry in dm_exec_cached_plans, and thus each user will get the page faster when they first log in?Thus you can see what I want to do, and can set things up, whereby on a new deployment or restore we carry out a process of running cerstin stored procs or logging into application as different users, so that the user experience is as good as can be.Any suggestions, to what I am sure is an existing and hopefully already solved issue would be greatly appreciated:-) |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-08-21 : 12:26:26
|
| 40 seconds --> 1 second?Are you sure it's not because the data needed is in memory for the second call rather than compilation time?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
chmbrssh
Starting Member
3 Posts |
Posted - 2012-08-22 : 08:59:53
|
| yeh after experimenting with DBCC DROPCLEANBUFFERS,that is what that points to, was curious if any way round this rather than explaining to client slow first time or pre-running query first |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-08-22 : 11:38:07
|
| When you create the compilations make sure statistics are up to date. This also helps with inefficiencies that may derive from parameter sniffing . Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
chmbrssh
Starting Member
3 Posts |
Posted - 2012-08-28 : 09:47:47
|
| ok thank you |
|
|
|
|
|
|
|
|
Precompilation of stored procedure