#0168-SQL Server-Table/Column Aliasing does not affect query performance


It’s interesting to know about and think over some of the old-wives tales surrounding SQL Server and it’s features. One such tale is that the use of table and column aliases affects query performance.


The very purpose of table and column aliases is to improve the read-ability of the query, and nothing else. They help in eliminating ambiguity, but do not in any way impact the internal workings of SQL Server, and today I will be proving just that.


My example will be for using column aliases. You can work out a similar example for table aliases.


The Example


Below are two straight-forward T-SQL queries, intended for nothing else but to demonstrate the theory mentioned above. The first query does not use column aliases, whereas the second query uses them.


IMPORTANT NOTE: In this script, we use 2 DBCC commands – FREEPROCCACHE and DROPCLEANBUFFERS to simulate a cold buffer condition, that is generally achieved when SQL Server restarts. Doing so indiscriminately can have severe performance implications. Please do not execute the scripts shown in this example in your production or quality assurance environments.

USE AdventureWorks2012
GO

/*************************************************************************************************************
IMPORTANT WARNING
————————————————————————————————————–
DBCC FREEPROCCACHE and DROPCLEANBUFFERS are used to simulate a cold buffer condition,
that is generally achieved when SQL Server restarts.

Doing so indiscriminately can have severe performance implications.

Please do not execute the scripts shown in this example in your production or quality assurance environments.

The author, this site or Microsoft are not responsible
for any damage caused by indiscriminate use of these DBCC commands
*************************************************************************************************************/
DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO

SELECT ””””Non-Aliased Query””””,
HRE.BusinessEntityID,
HRE.LoginID,
HRE.NationalIDNumber,
HRE.OrganizationNode,
HRE.OrganizationLevel,
HRE.JobTitle,
HRE.BirthDate,
HRE.MaritalStatus,
HRE.Gender,
HRE.HireDate,
HRE.SalariedFlag,
HRE.VacationHours,
HRE.SickLeaveHours,
HRE.CurrentFlag,
HRE.rowguid,
HRE.ModifiedDate,
Dept.DepartmentID,
Dept.ShiftID,
Dept.StartDate,
Dept.EndDate,
Dept.ModifiedDate,
HumanResources.Department.Name,
HumanResources.Department.GroupName,
HumanResources.Department.ModifiedDate,
HumanResources.Shift.Name,
HumanResources.Shift.ShiftID,
HumanResources.Shift.StartTime,
HumanResources.Shift.EndTime,
HumanResources.Shift.ModifiedDate
FROM HumanResources.Employee AS HRE
INNER JOIN HumanResources.EmployeeDepartmentHistory AS Dept ON HRE.BusinessEntityID = Dept.BusinessEntityID
INNER JOIN HumanResources.Department ON Dept.DepartmentID = HumanResources.Department.DepartmentID
INNER JOIN HumanResources.Shift ON Dept.ShiftID = HumanResources.Shift.ShiftID
GO

SELECT ””””Aliased Query””””,
EmployeeID = HRE.BusinessEntityID,
LoginID = HRE.LoginID,
NationalIDNumber = HRE.NationalIDNumber,
OrganizationNode = HRE.OrganizationNode,
OrganizationLevel = HRE.OrganizationLevel,
JobTitle = HRE.JobTitle,
BirthDate = HRE.BirthDate,
MaritalStatus = HRE.MaritalStatus,
Gender = HRE.Gender,
HireDate = HRE.HireDate,
SalariedFlag = HRE.SalariedFlag,
VacationHours = HRE.VacationHours,
SickLeaveHours = HRE.SickLeaveHours,
CurrentFlag = HRE.CurrentFlag,
rowguid = HRE.rowguid,
EmployeeModifiedDate = HRE.ModifiedDate,
DepartmentID = Dept.DepartmentID,
ShiftID = Dept.ShiftID,
StartDate = Dept.StartDate,
EndDate = Dept.EndDate,
DepartmentModifiedDate = Dept.ModifiedDate,
DepartmentName = HumanResources.Department.Name,
GroupName = HumanResources.Department.GroupName,
DepartmentModifiedDate = HumanResources.Department.ModifiedDate,
ShiftName = HumanResources.Shift.Name,
ShiftID = HumanResources.Shift.ShiftID,
StartTime = HumanResources.Shift.StartTime,
EndTime = HumanResources.Shift.EndTime,
ShiftModifiedDate = HumanResources.Shift.ModifiedDate
FROM HumanResources.Employee AS HRE
INNER JOIN HumanResources.EmployeeDepartmentHistory AS Dept ON HRE.BusinessEntityID = Dept.BusinessEntityID
INNER JOIN HumanResources.Department ON Dept.DepartmentID = HumanResources.Department.DepartmentID
INNER JOIN HumanResources.Shift ON Dept.ShiftID = HumanResources.Shift.ShiftID
GO


Impact on Query Performance


A quick way to compare the performance of two queries is to use the Actual Execution Plan. I therefore set the Actual Execution Plan to ON (press Ctrl + M) and then ran the entire batch (i.e. both queries) together against the AdventureWorks2012 database. The actual execution plan showed an equal – 50/50 split between the two queries.


The Actual Execution plan shows us an equal - 50/50 split between the two test queries - one with column aliasing, the one without


Conclusion 01: Using column aliases does not affect query performance.


Impact on Query Plan Cache


Now that we have derived the conclusion that column aliases do not affect query performance, do they impact the plan cache? We know that query plans are ultimately text, and therefore a query using aliases should have a bigger plan, bloating the plan cache. The Dynamic Management Views provide a powerful way of checking this one out.


I wrote the following query in an attempt to find the query plan size in bytes for a given ad-hoc query, which is:

SELECT cp.size_in_bytes,
st.text,
qs.sql_handle,
qs.plan_handle,
cp.usecounts,
cp.refcounts,
cp.cacheobjtype,
cp.objtype
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
INNER JOIN sys.dm_exec_cached_plans as cp ON qs.plan_handle = cp.plan_handle
WHERE cp.objtype = ””””Adhoc””””
AND st.text LIKE ””””%HumanResources.EmployeeDepartmentHistory%””””

Which gives us:


Screenshot showing the output of the DMV based query, demonstrating that aliasing does not impact the plan cache


As you can see, there is no difference in the cached plan size for both the queries.


Conclusion 02: Column aliasing does not impact the plan cache.


Conclusion 03: Last year, I wrote a piece on how writing your queries using different cases will cause SQL Server to create multiple query plans. Hence, it is advised to have a single, consistent standard towards using aliases in your product.  You can read the post at: Query Plan Re-use: Write your T-SQL queries with the proper case for plan re-use and better query performance


Summarizing


Based on the observations shared above, I made the following conclusions:















  Is affected by aliasing?
Query Performance No
Plan cache size No
Plan reuse Possible

What have been your observations with respect to aliasing? Do you use aliasing as part of your coding standards? Do leave a note as you go.


Until we meet next time,


Be courteous. Drive responsibly.

Advertisements

4 thoughts on “#0168-SQL Server-Table/Column Aliasing does not affect query performance

  1. ErikEckhardt

    I agree with your results, but am not sure you’ve proven them adequately. Execution plans can be quite inaccurate. Some IO and CPU statistics seem to be in order to truly put this one to rest.

    Like

    Reply
  2. Nakul Vachhrajani

    @Erik: Sure. I will be writing a follow-up on this one with the I/O and CPU statistics. Thank-you for your feedback, I really appreciate it.

    Like

    Reply
  3. Nakul Vachhrajani

    @Dave: Absolutely correct, readability is perhaps the #1 reason why I use aliasing. Thank-you for sharing your reading the post & sharing views!

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

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