SQL Server – Overhead from Using Synonyms to Access Another Database

sql serverssrs

If one database is accessing tables on another database on the same SQL server through synonyms, does that incur any significant overhead to either of the databases?

Setup: We have one database (A) that contains all the data for our application. This database runs some time consuming calculations for a point in time, and then stores the output in another database (A_Reports).

Through the client application, a user can load reports hosted on SSRS. These reports connect to database A which uses synonyms to access the data stored on database A_Reports. Some reports might need some of the data from A, but that is rarely needed and only occurs under certain conditions that the report will always be aware of.

We want to keep the utilization of A low since it has all the data for the app servers and A_Reports is only used to store the calculated reports data.

I would assume that since they are on the same SQL server it doesn't matter which database is connected to. So the real question is, if we change the reports in SSRS to connect to A_Reports instead of A, will this allow reports to access the data quicker or enable database A to run its queries more efficiently?

Best Answer

SQL Server resolves Synonyms before it generates the query plan and it should have little to no impact.

So the real question is, if we change the reports in SSRS to connect to A_Reports instead of A, will this allow reports to access the data quicker or enable database A to run its queries more efficiently?

If your report runs faster on A_Reports instead of A and your synonym references A_Reports, it will run faster. If it references A, it will run slower.

I know your example is asking about cross referencing databases with synonyms and my examples below are regarding synonyms in the same DB. It should be handled identically though, as seen at this answer by Scott Hodgin. To illustrate further it would be handled the same. You will notice the use of the synonym has no impact and furthermore, you can't even notice the impact because it does not appear in the query plan or stats. (Because it happens in the bind phase and has negligible to no cost.)

Consider 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;

I ran each select independently and ran DBCC FREEPROCCACHE (DON'T DO THAT IN PRODUCTION) in between.

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

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

-- Test synonym
SELECT * from test2;

(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: Synonym used in HEAP plan

-- Test base table
SELECT * FROM test;

(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.

Test 1: No synonym used in HEAP plan

The second experiment I added a primary key and clustered index, I also saw no change in plan or performance. 

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 synonym
SELECT * from test2;

(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: Synonym used in clustered table plan

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

-- Test base table
SELECT * FROM test;

(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.

Test 2: No synonym used in clustered table plan

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;

SELECT * FROM test2 WITH (INDEX(testnonc));

(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: Synonym used in clustered table with non-clustered index plan

DBCC FREEPROCCACHE;

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

SELECT * FROM test WITH(INDEX(testnonc))

(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.

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

Related Question