This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.
Today, I worked on a service request that our customer reported that a query that they are using is taking more time if they execute the same in SQL Server Management Studio.
Besides too many things that could impact in the execution of the query, I would like to share another thing that could impact also in the execution.
I saw that in many applications, that customer code tries to parametrize the values that it is a good idea, but, you need to review that the parameter type that your application is using.
For example, if your application is running the following query:
The results as you could see would be:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1633 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Completion time: 2021-02-19T19:39:58.8536169+01:00
But, if I run the same execution running the following query:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Completion time: 2021-02-19T19:42:27.2848682+01:00
Why? Well, the reason, is that application is using unicode parameter that SQL Server needs to perform an implicit conversion to unicode if the column is not Unicode. In this situation, the column TextToSearch is a varchar and the parameter datatype is unicode. Sometimes, the time is slighty different between executions with/without unicode but if your application executes multiple times having this difference or reading a lot of rows that might cause parallelism in the execution impacting too much in your environment.
So, please, remember to use the same parameter datatype that your database has in your application.
Enjoy!