Synonym

• Do Synonyms impact performance in SQL Server?

  Jun 19, 2018

  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.