Question to execution plans and memory consumption

Question

Question to execution plans and memory consumption

alexander.huberti

SSC-Addicted

Points: 461
More actions
July 12, 2011 at 2:03 am

#244130

Hi all,
I found some problems with our program and database (SQL Server 2008).
The Server Dashboard Report of the instance show me 100% CPU usage of Adhoc queries.
With the view sys.dm_os_memory_clerks I can see a grow up of the CACHESTORE_SQLCP
to 4.5 GB.
I found in the execution plans the same SQL statement thousand times:
UPDATE TABLE SET VALUE=@PARAM_0, VARVALUELONG=NULL WHERE ID=@PARAM_1 AND VARNAME=@PARAM_2
I think one of the problem is the databinding in our program, we use very often different length for
the parameter. Here is the code of the databinding:
System.Data.IDbDataParameter param;
param = cmd.CreateParameter();
param.Direction = System.Data.ParameterDirection.Input;
param.ParameterName = DbClient.Default.CreateParamName(name);
param.Value = val;
cmd.Parameters.Add(param);
return param;
Is there a flag or an option in SQL Server to ignore the different length of the parameter
to avoid creating different execution plans? Or is the only way to change the binding in the code
with fix length?
In our program it's possible to use an own program language to create workflows. All parameters, states of the workflows
and so on are saved within the database. I think that is a problem, too.
The part of the database for the workflows has more writing access (probably 50%),
the rest of the database is more reading access (>90%). My idea for the future is to extract the tables
for our workflowengine in an own database and using of the resource governor. Is this the right way? Or other ideas?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Consider turning on 'optimise for ad-hoc queries'

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

alexander.huberti SSC-Addicted Points: 461 More actions · Answer 2

Thanks,

I've already activated the option 'optimize for ad hoc workloads'. The growing of the memory for the execution plans is a little bit better, but the counts of the execution plans for the same statement is the same.

At the moment we delete sometimes the proccache with this statement: DBCC FREESYSTEMCACHE('SQL Plans').

Alex

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Yes, the count will be the same. You have to fix the mis-matched lengths from the app, not from SQL. If this is nHibernate there is a setting somewhere (I think)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

properly designed code is the way to avoid procedure cache bloat. ORMs are HORRIBLE about this, as are poorly coded middle tier constructs (even ADO.NET).

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service