Search code examples
sql-serversql-viewsqlperformance

MSSQL 2012 view faster after executing "alter"

I have a situation/context where a specific view takes 120sec before returning a result. When doing a simple ALTER (or drop/create) the view takes 1 to 2 seconds. How is this possible and how can we fix this - as we have no dedicated DBA that can help us out. Making indexed views is not an option due to linked server setup we have (MSSQL Server 2012 complains about that).

Below is information on the setup.

TL;DR View_MAIN takes 120 secs after a certain amount of time. When we execute an ALTER view on View_X, View_Y and View_MAIN without changing anything the performance is normal again to 1 to 2 seconds.

View_MAIN

SELECT 
   column1, column2, column3, column4, column5
FROM View_X

UNION ALL

SELECT 
   column1, column2, column3, column4, column5
FROM View_Y

View_X

SELECT
   LTRIM(RTRIM(table1.a)) as column1,
   table2.b COLLATE Latin1_General_CI_AS as column2,
   table1.c as column3,
   CAST(table3.d AS DATETIME) as column4,
   'XXXXXX' as column5
FROM
   [linkedserver01].[DATABASE_IDN].[dbo].[dataForX] table1
   LEFT OUTER JOIN [linkedserver02].[DATABASE_INFM] as table2
   ON table2.id = table1.id
   LEFT OUTER JOIN [linkedserver02].[DATABASE_PIK] as table3
   ON table3.id = table1.id

View_Y

SELECT
   LTRIM(RTRIM(table1.a)) as column1,
   table2.b COLLATE Latin1_General_CI_AS as column2,
   table1.c as column3,
   CAST(table3.d AS DATETIME) as column4,
   'YYYYY' as column5
FROM
   [linkedserver01].[DATABASE_IDN].[dbo].[dataForY] table1
   LEFT OUTER JOIN [linkedserver02].[DATABASE_INFM] as table2
   ON table2.id = table1.id
   LEFT OUTER JOIN [linkedserver02].[DATABASE_PIK] as table3
   ON table3.id = table1.id
Solution

  • By altering, or recreating, the view, you will be clearing the cached execution plan that exists for this, and recreating it based on the current set of data.

    You can achieve the same effect by executing sp_recompile 'View_Main'

    https://technet.microsoft.com/en-us/library/ms181055(v=sql.105).aspx

    Looking at the actual execution plan for the query should be your starting point to determine why it is running slowly (in SSMS, on the Query menu)