SQL Server 2008 Profiler pads the datetime datatype with 0s when tracing for a ADO.Net application
We have seen that when we use a sql server 2008 profiler trace to trace RPC calls coming from an ADO.Net application and if the ADO.Net applications calls a stored procedure which accepts the parameters of datatype “datetime” which has a precision of 3.333ms, the same will be shown in the sql server 2008 profiler trace with precision of 3.3333333.
Consider the following stored procedure defined in the database
CREATE PROCEDURE [dbo].[MyProc] @var datetime
AS
Begin
declare @a datetime
set @a = @var
select @a
end
Consider a following console application for running the demo
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
namespace ConsoleApp1
{
class MyData
{
public void GetByDbType()
{
SqlConnection cn = new SqlConnection("Data Source=.;Initial Catalog=tempdb;Integrated Security=SSPI");
cn.Open();
SqlCommand cmd = new SqlCommand("dbo.myproc", cn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter p1 = new SqlParameter("@var",DbType.DateTime);
DateTime d = DateTime.Now;
p1.Value = d; Console.WriteLine(d.ToUniversalTime().ToString());
cmd.Parameters.Add(p1);
cmd.ExecuteNonQuery();
cn.Close();
Console.ReadLine();
}
}
}
When you run a profiler trace on sql server 2008 instance while running the above console application the RPC Completed events are traced as
exec dbo.myproc @var='2009-07-18 04:09:28.1670000'
However when the same application is run against sql server 2005 instance and the sql server 2005 profiler show the following results
exec dbo.myproc @var=’’2009-07-18 04:08:50:037’’
So we observe that in sql server 2008 profiler, zeros are appended to the datetime values which should otherwise show a precision of 3.333ms as in case of sql 2005 profiler. The above behavior of the profiler appears to be caused due to the changes in the code of the sql server 2008 to accommodate the new datetime2 datatype in sql server 2008 which has higher precision.
However due to the above behavior of the profiler, when we are using profiler to replay the workload in DTA , the execution of the stored procedure can fail since the profiler sends the zero padded parameter to the Parser however the parser checks the datatype of the parameter as “datetime” and does not accept additional 0s.Hence due to the above behavior of the profiler the Replay of the workload from profiler in DTA cannot be used successfully.
Reference
=======
Parikshit
SE, Microsoft Sql Server
Reviewed By
Sourabh Agrawal
Technical Lead, Microsoft Sql Server