Do Synonyms impact performance in SQL Server?

I tried an experiment recently to test whether synonyms impacted performance in SQL Server.

Here’s our setup for the experiment:

-- Experiment table
CREATE TABLE test 
(one INT NOT NULL, 
two INT NOT NULL, 
three INT NOT NULL, 
four INT NOT NULL);

-- Get records
INSERT INTO test
VALUES (1, 1, 1) 
GO 10000

-- Make synonym
CREATE SYNONYM test2
FOR test;

-- Clear cache
DBCC FREEPROCCACHE;

-- Measure
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Test
SELECT * from test2;
SELECT * FROM test;

When experimenting, I ran each SELECT independently and ran DBCC FREEPROCCACHE (DON’T DO THAT IN PRODUCTION) in between run times.

My first experiment, I created the table as a HEAP. I saw no change in plan or performance.

-- TEST 1.1 Synonym used in HEAP
DBCC FREEPROCCACHE;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Test synonym
SELECT * from test2;

Test 1.1: Synonym used in HEAP plan

(10000 row(s) affected) Table ‘test’. Scan count 1, logical reads 33, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 117 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

-- TEST 1.2 Synonym unused in HEAP
-- Test base table
SELECT * FROM test;

Test 1.2: No synonym used in HEAP plan

(10000 row(s) affected) Table ‘test’. Scan count 1, logical reads 33, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 131 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

The second experiment I added a primary key and clustered index, I also saw no change in plan or performance. Here’s the setup and first test.

DROP TABLE test;

CREATE TABLE test 
(one INT NOT NULL identity(1, 1) PRIMARY KEY, 
two INT NOT NULL, 
three INT NOT NULL, 
four INT NOT NULL);

INSERT INTO test(1,1,1)
GO 10000

DBCC FREEPROCCACHE;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- TEST 2.1 Synonym used in B-Tree
-- Test synonym
SELECT * from test2;

Test 2.1: Synonym used in clustered table plan

(10000 row(s) affected) Table ‘test’. Scan count 1, logical reads 33, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times: CPU time = 16 ms, elapsed time = 102 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

-- TEST 2.2 Synonym unused in B-Tree
DBCC FREEPROCCACHE;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Test base table
SELECT * FROM test;

Test 2.2: No synonym used in clustered table plan

(10000 row(s) affected) Table ‘test’. Scan count 1, logical reads 33, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 83 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

For the last experiment I created a non-clustered index on the two column to include columns three and four as well and forced the use of an index.

CREATE NONCLUSTERED INDEX testnonc ON test (two);

DBCC FREEPROCCACHE;

-- Measure
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- TEST 3.1 Synonym used with B-Tree and Index
SELECT * FROM test2 WITH (INDEX(testnonc));

Test 3.1: Synonym used in clustered table with non-clustered index plan

(10000 row(s) affected) Table ‘test’. Scan count 1, logical reads 20654, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 104 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

-- TEST 3.2 Synonym unused in B-Tree with index
DBCC FREEPROCCACHE;

-- Measure
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT * FROM test WITH(INDEX(testnonc))

Test 3.2: No synonym used in clustered table with non-clustered index plan

(10000 row(s) affected) Table ‘test’. Scan count 1, logical reads 20654, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times: CPU time = 31 ms, elapsed time = 126 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

Ok, so there was actually no difference. So what’s going on? The short answer is that there is a phase that happens before SQL creates a Query Plan, the bind phase. So this means we won’t see the impact Synonyms have on the query. This also means that any if at all negative performance associated to the Synonym, is so negligible that it is not relevant to tuning or worth worrying about.

Scott Hodgin on Stack Exchange has compiled a few sources talking about the bind phase and how it has little to no impact.