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