Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from serverfault.com Mar 6 '12 at 3:30

This question came from our site for professional system and network administrators.

2 Answers 2

up vote 0 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.