I like to describe a query plan as a road map to your data. Your query plan shows you the steps the SQL Server engine is taking to fetch your data. Typical things you want to avoid in query plans are table scans, hash matching, bookmark lookups, excessive looping, spooling and excessive compute scalar operators.
To view a query plan you can use several set statements, for example: SET SHOWPLAN_TEXT or XML on, SET STATISTICS IO ON.
You can also see the plan in graphical format by using the SSMS from the query drop down menu. You can see even more information by selecting properties when you have a plan step highlighted.
When you write TSQL you want to get your data in the most effecient way possible. It is good practive to become familar with what the plan looks like and how even small things can have a large impact on performance.
Lets start with compute scalar for todays post. When you see a compute scalar step in your query plan SQL Server is doing an implicit conversion on a variable or column. Imagine if you are selecting 500,000 rows and for each row SQL Server is doing a conversion. How much over head do you think that will add? Just today I was tuning a piece of code. This code populated a table variable from a string of comma seperated values which were seperated by calling a function. The string passed to the function was a varchar, however the function was expecting a nvarchar and thus SQL was performing an implicit conversion on the string variable. I changed the function to expect a varchar and the execution time of my stored procedure was reduced by 15 seconds. The number of rows I was processing was 32000. A simple yet costly mistake. Always make sure your variable declaration have the same data type as the data they will be holding, the same should be applied to parameters for stored procedures and functions.
In our next post we will discuss table scans.
