Understanding Parameters in SQL Server Query Plan

jdbcsql server

I'm looking at query plan in SQL Server Mgmt Studio and I see something like this:

(@P0 nvarchar(4000), @P1 nvarchar(4000)) Update...

What does the type mean beside the parameters? For this example lets assume that the column for those parameters are actually varchar(64). Is this how the parameters are being bound at runtime?

EDIT

This is a Java EE application using JDBC 3.0 or 4.0 providers. Basically we have a properties file full of SQL statements like this:

mySqlStatementFoo = UPDATE Schema.Table.Column set column = 1 WHERE objectID = ?

It looks like in the past if there was a binding mismatch we would cast the param like so:

Where objectID = CAST(? AS VARCHAR(36))

I suppose they chose to do it this way since we support multiple databases. I.E. DB2 and Oracle. I haven't seen the actual Java, but I suspect they are just selecting the statement from the file and sending it down the wire. I'm doing load testing and seeing that we are doing table scans and suspect that the optimizer isn't using the correct index because the parameters are being bound incorrectly.

Best Answer

Yes, you are likely seeing implicit conversions due to the guesses SQL Server has to make. It is creating a plan that will work not just for the parameter value you're passing now, but other potential values, too. With strings it (or perhaps JDBC? not sure) chooses an arbitrary default of 4000 and Unicode. I believe there are ways in JDBC to not send Unicode (Unicode vs. non-Unicode implicit conversions are among the most tedious), but you're still going to deal with the length issue (which may or may not be an issue in your specific case; again, not sure).

To avoid the implicit conversions and guesses, and ultimately the scans and high CPU conditions, you need to make SQL Server understand the data type that is being used in the parameter through explicit / strong typing.

Here are my suggestions, in order of preference:

  1. Switch to stored procedures.
  2. Switch to stored procedures.

  3. Declare your variables explicitly, e.g.:

    DECLARE @p1 VARCHAR(36) = ?; UPDATE ... WHERE col = @p1;

  4. Keep using CAST/CONVERT.

Related Question