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
=======


http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/6d455ce9-d699-46b0-9b1f-04cc8a3279ea


 


 


Parikshit
SE, Microsoft Sql Server


 


 


Reviewed By


 


Sourabh Agrawal
Technical Lead, Microsoft Sql Server

Comments (0)