Earlier this week, I wrote a post on whether table and column aliasing affects query performance or not (http://beyondrelational.com/modules/2/blogs/77/Posts/14948/0168-sql-server-tablecolumn-aliasing-does-not-affect-query-performance.aspx) . We were able to determine that overall time-wise, both queries were performing equally well. However, one of the comments received as part of the feedback was to also include results of the following in addition to the use of simply the execution plan.
- Statistics IO
- Statistics Profile
- Actual CPU times
I therefore repeated the tests after modifying the query a little bit to include the necessary SET statements and setting the buffer to a clean state.
IMPORTANT NOTE: In this script, we use 2 DBCC commands – FREEPROCCACHE and DROPCLEANBUFFERS to simulate a cold buffer condition, which 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 WITH NO_INFOMSGS
GO
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
GO
SET STATISTICS IO ON
SET STATISTICS PROFILE ON
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
DBCC FREEPROCCACHE WITH NO_INFOMSGS
GO
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
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
SET STATISTICS IO OFF
SET STATISTICS PROFILE OFF
Running the query gives us the following results:
- Query output
- After each query’s output, we receive a result set containing the performance statistics for the given query
- In the “Messages” tab, the IO statistics for the query execution are available
Let us evaluate each one-by-one:
Statistics I/O
We started with clean buffers for both queries to avoid any impact to the I/O statistics arising out of data already being available in the buffer (i.e. a “logical” read). The statistics I/O for both queries yields the same output, which is:
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Employee’. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table ‘EmployeeDepartmentHistory’. Scan count 1, logical reads 4, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Shift’. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Department’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Here’s the screenshot:
Statistics Profile
The statistics Profile is nothing but a textual representation of the query profile information that can be obtained from the execution plan also. Besides many others, the output contains the following values of interest:
- The number of rows processed by each operation
- The number of times a particular operation was executed
- Operator used
- Estimated Rows
- Estimated CPU
- Average Row Size
- Total Subtree cost
To compare the result sets, I simply used Excel. You can use any file comparison tool of your choice.
Comparing the output reveals that the two result sets are identical except for:
- The obvious difference in the query text
- A slight increase in the Average Row Size is observed for the aliased query (which can be attributed to the increased number of characters used for aliasing)
- The Estimated CPU time and the Total Subtree Cost remains the same
Summarizing
Combining the results of the experiments above and the observations from my previous post, we can summarize that:
| Affected By Aliasing? | |
| Query Performance – IO | No |
| Query Performance – CPU | No |
| Query Performance – Avg. Row Size | Possible |
| Query Performance – Time | No |
| Plan Cache Size | No |
| Plan reuse | Possible |
What have been your observations related to aliasing? Do leave a note as you go.
Until we meet next time,
SQLTwins by Nakul Vachhrajani 