Sometimes it happened with a developer or even with a DBA, a query seems to be a very perfect over time and time, having appropriate index, but taking long time to show result. As per my understanding in multiple scenario there may be various reasons. But one very common and widely happened error is data type conversion issue. As we know Data types can be converted either implicitly or explicitly. Implicit conversions are not visible to the user. SQL Server automatically converts the data from one data type to another while Explicit conversions use the CAST or CONVERT functions. The CAST and CONVERT functions convert a value (a local variable, a column, or another expression) from one data type to another.

Here, I am pointing about Implicit conversions, it’s a silent killer and it has been seen this literally bring a system to its knees by causing deadlocks during high load, CPU at maximum utilization, performance issues and people started blaming on server, but reason is something different. Let see below example,

Create Procedure usp_VirendraTest @ID VarChar(10)
As
Select ID,Name,Fname,BloodGroup,Dept,Desig From tblVirendraTest Where ID = @ID

The code looks very adequate and works good at initial stage but it starts show their own color when table’s rows count become multi millions. The reason is ID column belongs to INT data type and
Developers and even DBAs get confused and write a procedure with the wrong data type in this scenario. Now problem started, every time SQL Server has to look for a ID values and it has to convert @ID from a VarChar to an Int. This is an implicit conversion of data type. Internally, SQL Server uses a convert function perform this operation. When this happens, SQL Server cannot use an index effectively. it has to convert the value for each and every row and as a resultant SQL Server scans the entire table for the value. This takes time and, under default locking modes, places a share lock on the entire table preventing other processes from updating/Inserting/Deleting records while the scan is taking place.

The solution for this type of problem is use of CAST or CONVERT.

Here is the Data type conversions chart from Microsoft


Thanks for happy reading, Please comment and suggest the better ways. JJ

Advertisements
Comments
  1. Nagarjuna says:

    Thank you for sharing key information…

    Like

  2. Thanks for your information.Could you tell that How to find what are the procedures involving implicit conversion issue in the particular database.That is very useful .

    Like

  3. saurabh says:

    Thanks Sir its very useful information for us

    Like

  4. Russ says:

    V you are exactly right. Implicit data type conversions are a silent killer. Often times when performance tuning we overlook the obvious. Implicit conversations can also cause recompilations and cause good query plans to be dropped from plan cache.

    Thanks for bringing this information to my attention.

    Happy hunting,
    Russ

    Like

  5. mandaroke says:

    Good article Virendra.. Excellent Information..

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s