Hi,
We're seeing a large number of not-reused execution plans filling up our procedure cache in SQL Server 2005 from essentialy the same NHibernate-generated SQL statement.
It seems this issue is that SQL Server is not reusing plans when the parameter sizes passed to sp_executesql vary.
For example, in the SQL trace below, the first time the query is run we see "@p0 nvarchar(4)" but the second time it's "@p0 nvarchar(38)".
I tried various combinations of values in the <column> element (initially I had no <column> element) but I could not change the behaviour where NHib uses the string lengths of the actual parameter values to decide what formal parameter sizes to pass.
It seems we are not the only ones seeing an issue here. See for example:
http://scarydba.wordpress.com/2008/04/29/nhibernate-recompiles-and-execution-plans/
Have you seen this issue before, and is there a known workaround?
Thanks,
Roger
Hibernate version: 1.2.0.4000
Mapping documents:
Code:
<hibernate-mapping default-cascade="none" xmlns="urn:nhibernate-mapping-2.2">
<class name="TestDb.PlayerSnapshot, TestDb" table="ETC_player_snapshot">
<id name="Snapshotix" type="System.Int32" column="snapshotix" unsaved-value="0">
<generator class="native" />
</id>
<property name="Address_city" type="System.String" not-null="false" >
<column name="address_city" length="50" sql-type="nvarchar"/>
</property>
<property name="Address_post_code" type="System.String" column="address_post_code" not-null="false" />
<property name="Address_state" type="System.String" column="address_state" not-null="false" />
<property name="Address_street1" type="System.String" column="address_street1" not-null="false" />
<property name="Address_street2" type="System.String" column="address_street2" not-null="false" />
<property name="Email" type="System.String" column="email" not-null="false" />
<property name="Geo_stateix" type="System.Int32" column="geo_stateix" not-null="false" />
<property name="Iso_countryix" type="System.Int32" column="iso_countryix" not-null="false" />
<property name="Name_first" type="System.String" column="name_first" not-null="false" />
<property name="Name_last" type="System.String" column="name_last" not-null="false" />
<property name="Phone_number" type="System.String" column="phone_number" not-null="false" />
<property name="Playerid" type="System.String" column="playerid" not-null="false" />
<property name="Playerix" type="System.Int32" column="playerix" not-null="true" />
<property name="Ts_create" type="System.DateTime" column="ts_create" not-null="true" />
</class>
</hibernate-mapping>
Code:
using (ITransaction txn = session.BeginTransaction())
{
PlayerSnapshot playerSnapshot = new PlayerSnapshot();
playerSnapshot.Playerid = "aaa";
playerSnapshot.Address_city = "Cork";
session.Save(playerSnapshot);
PlayerSnapshot playerSnapshot2 = new PlayerSnapshot();
playerSnapshot2.Playerid = "bbb";
playerSnapshot2.Address_city = "Cork adfkjafk dl;a adlkfjakdj fakldf j";
session.Save(playerSnapshot2);
txn.Commit();
}
Name and version of the database you are using:
SQL Server 2005
The generated SQL (from profiler):
exec sp_executesql N'INSERT INTO ETC_player_snapshot (address_city, address_post_code, address_state, address_street1, address_street2, email, geo_stateix, iso_countryix, name_first, name_last, phone_number, playerid, playerix,
ts_create) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13); select SCOPE_IDENTITY()',N'
@p0 nvarchar(4),@p1 nvarchar(4000),@p2 nvarchar(4000),@p3 nvarchar(4000),@p4 nvarchar(4000),@p5
nvarchar(4000),@p6 int,@p7 int,@p8 nvarchar(4000),@p9 nvarchar(4000),@p10 nvarchar(4000),@p11 nvarchar(11),@p12 int,@p13
datetime',@p0=N'Cork',@p1=NULL,@p2=NULL,@p3=NULL,@p4=NULL,@p5=NULL,@p6=NULL,@p7=NULL,@p8=NULL,@p9=NULL,@p10=NULL,@p11=N'one hundred',@p12=100,@p13=''2008-05-12 20:27:28:000''
exec sp_executesql N'INSERT INTO ETC_player_snapshot (address_city, address_post_code, address_state, address_street1, address_street2, email, geo_stateix, iso_countryix, name_first, name_last, phone_number, playerid, playerix,
ts_create) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13); select SCOPE_IDENTITY()',N'
@p0 nvarchar(38),@p1 nvarchar(4000),@p2 nvarchar(4000),@p3 nvarchar(4000),@p4 nvarchar(4000),@p5
nvarchar(4000),@p6 int,@p7 int,@p8 nvarchar(4000),@p9 nvarchar(4000),@p10 nvarchar(4000),@p11 nvarchar(11),@p12 int,@p13 datetime',@p0=N'Cork adfkjafk dl;a adlkfjakdj fakldf
j',@p1=NULL,@p2=NULL,@p3=NULL,@p4=NULL,@p5=NULL,@p6=NULL,@p7=NULL,@p8=NULL,@p9=NULL,@p10=NULL,@p11=N'two hundred',@p12=200,@p13=''2008-05-12 20:27:28:000''
Login
FAQ
Search