We're experiencing about 300 SQL Compilations per second, and we use very little dynamic SQL. However, I noticed that in SQL Profiler our calls look like:
exec cid.dbo.uspCIDLog '9425','19155852234',2 -- null
And I think they should look like this if properly parameterized:
exec cid.dbo.uspCIDLog @sFrom='9425', @sTo='19155852234', @userId=2
Will this make a difference?
I'm not a SQL expert by any means so this is just my understanding of reading several Microsoft articles:
I am not sure which version of SQL server you are using, but I believe your trace IS showing correctly, and the values after the stored proc name ARE the "parameters" for the stored proc even if SQL is not "parameterizing" the stored procedures. If you break open your stored proc, you should see the various variables and parameters being declared within it which correspond to the "parameters" after the exec cid.dbo.uspCIDLog. You may or may not have Forced SQL Parameterization enabled, which essentially parameterizes T-SQL statements (SELECT, INSERT, UPDATE or DELETE), but that is unrelated to this particular trace since it is a stored procedure that is already pre-compiled. This setting has no effect on stored procedures.
If you want to investigate your Compilations/sec value further, you should read one of the "Troubleshooting Performance Problems in SQL Server 200x" articles written by Microsoft depending on your version of SQL server. This MS KB has links to all of them at the bottom.
It shouldn't really make a difference if the parameters are specified or if parameter location is assumed. The compiling happens to the statements within the stored procedure.
That said, it's best practice to pass in the values properly parametrized so that if a parameter is added in the middle of the list nothing will break.
