Monday, August 6, 2012

SQL Server 2012-Execution Plans

One of the most often used and very valuable tool available for SQL Server Developers is the Show Execution Plan feature available in SSMS. The execution plan would be used every developer in order to understand the execution plans generated by the queries and more importantly is used for performing troubleshooting on queries that are not performing well. In SQL Server 2012 there is a new hint which is available in the Execution Plan. In order to demonstrate the concept I created a simple table called National.
Here is the structure/script of the table created.

CREATE TABLE [dbo].[National]
[NationalID]    Varchar(50) NULL,
[EntityID] [int] NULL

Once the table was created I inserted some sample values into the table. Once this was complete I was ready ro run some queries and look at the execution plan. The first query I ran was

SELECT [NationalID],[EntityID] FROM [dbo].[National]
WHERE [NationalID]='1'

When I ran the above query there was nothing abnormal with the execution plan, kind of saw what was expected.

I changed the above query to test the NationalID by passing a integer value, so i modified the query and ran it as

SELECT [NationalID],[EntityID] FROM [dbo].[National]
WHERE [NationalID]=1

When I looked at the execution plan there was  one thing I noticed, there was an "!" point next to the SELECT icon with COST 0%. I looked at the "!" and had a message saying Type Conversion in Expression.

meaning there was an implicit conversion performed since NationalID is defined as VARCHAR and this may affect "Cardinality Estimate in query plan cache. This is very interesting as if this were a bigger table with large number of rows such implicit conversions could cause performance issues.

No comments:

Post a Comment