June 18, 2011 at 7:32 am
Sql 2005 Enterprise: Our scanning department uses "BAS" vbscript-like scripts to do thousands of inserts into two tables in our production database. (Teleform/Digital Documents process) These are not parameterized and create about 20,000 single-use execution plans out of a plan cache which might have 23,000 plans. I know having the scripts call a stored procedure would eliminate this, but will need help from the Scanning vendor to modify the scripts.
In the mean time, short of FreeProcCache, is there a way to modify the insert statements to not cache, or perhaps remove these single-use plans without wiping out the the entire cache?
Karen Delaney has an article that seems to speak to this, although I haven't digested it completely yet
The inserts look like:
insert into ScanTable (field, field, field, field, field,) values
("value","value","value","value","value")
June 20, 2011 at 5:15 am
The inserts look like:
insert into ScanTable (field, field, field, field, field,) values
("value","value","value","value","value")
Have you tried the database setting 'Force Paramaterization'? Turning that on for the database should turn those calls into auto-parameterized queries, which would limit the number of cache entries.
-Eddie
Eddie Wuerch
MCM: SQL
June 20, 2011 at 5:45 am
You might want to make a case for upgrading to 2008. Then you can turn on optimize for ad hoc workload which will only store plan stubs for the first call of any one query. It radically reduces stress on cache.
Another thing you could do, it's somewhat radical and maybe even a little stupid, is to put WITH RECOMPILE hints on all the queries. Then the plans won't be cached. But... that makes more than a little nervous. Getting 2008 involved would be better.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 20, 2011 at 9:13 am
Yes we really do need to upgrade to sql 2008. I'll look up forced parameterization and see if we can test it on our DEV boxes. Most sql is coming from .Net via an ORM and is parameterized, but this one application doing inserts is a problem. I suppose the main question would be where forced parameterization would have some down-side for the other sql being generated.
June 21, 2011 at 5:44 am
Forced parameterization is a sledge hammer and you best test it THOROUGHLY on all parts of the application.
By far the best way to go, that should also be relatively easy to implement, is to simply write the data to a file and use some form of BULK insert to load the file in one fell swoop. You are not only getting killed by plan caching. You are also getting hit by excessive network calls, log buffer flushes, etc.
Another sledge hammer, but one which I use at several clients with 'bad' code such as yours (ORMs are AWFUL - even parameterized ones) is to flush all or part of the sql/procedure cache. I have set up jobs at several clients to run those calls as frequently as every 2 hours.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 21, 2011 at 7:20 am
Thanks Kevin, we are going to test forced parameterization on our dev environment. I checked yesterday and in production 20,000 of the 40,000+ cached plans are these single-use inserts, so beyond using 2.3GB of our typical 7GB plan cache, sql has to search through thousands of useless plans to find good ones.
We need to get on sql 2008 so individual plans can be removed. We don't run freeprocCache anywhere as near as often as we used to and it isn't the hit it used to be on slower hardware, but still.....
As to ORM stuff, yah I hear you but that's the trend these days, not likely to change at our shop.
June 22, 2011 at 9:35 am
Indianrock (6/21/2011)
Thanks Kevin, we are going to test forced parameterization on our dev environment. I checked yesterday and in production 20,000 of the 40,000+ cached plans are these single-use inserts, so beyond using 2.3GB of our typical 7GB plan cache, sql has to search through thousands of useless plans to find good ones.We need to get on sql 2008 so individual plans can be removed. We don't run freeprocCache anywhere as near as often as we used to and it isn't the hit it used to be on slower hardware, but still.....
As to ORM stuff, yah I hear you but that's the trend these days, not likely to change at our shop.
1) what version of 2005 are you on? pre-SP2 the plan cache could eat a LOT more buffer pool than SP2+.
2) Why don't you use freeproccache? With CPU ticks to spare it really can make a big difference.
3) If you are doing lots of ORM, please take my business card - you're gonna need it! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 22, 2011 at 11:04 am
We're on Sql 2005 Enterprise 64-bit SP4 with 128GB memory ( 88 for sql ) Active/Passive cluster, Netapp for shared storage
We had a consultant in a few years ago but I don't think anyone implemented his suggestions. :hehe:
And I justed tested forced parameterization on a test server. flushed plan cache and buffer pool, ran 75 of the "dumb" inserts while profiler was running. The resulting parameterized inserts created 3 plans in cache with usage as follows:
Plan 1: 46 uses
Plan 2: 28 uses
Plan 3: 1 use
Big improvement. We only have one view that actually has an index on it, which is one of the main downsides to Forced Parameterization
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply