Can Recreating a View Improve the Performance?

SQL Server Administration
1

We have a 2014 view that was taking a very long time to complete. (It retrieves data from 2 large tables).
Today the developer dropped and recreated the view. The view now completes in less than a minute--much much faster. (This is NOT an indexed view)
Is this feasible? And if it is, should I recreate all my views on a regular basis?
Thanks.

2

The poor performance may have been because of a cached query plan that was cleared when the view was recreated. Next time the query starts performing poorly, you might want to look at the query plan that is being used, clear it, and then run the query again and see the difference in query plans.

Here is how to clear query plans. Clearing the entire query plan cache can slow down the system, so clear just what you need to.

1 Like
3

Also, the underlying table schemas might have changed during that time. You should be able to get the same effect without dropping the view (rather risky, as permissions and extended attributes are lost) or clearing the entire query plan cache, which can be a lot of overhead on an instance:

EXEC sp_refreshview 'view_name'

2 Likes
4

Thanks ScottPletcher. A developer noticed very significant improvements after he dropped and recreated a sp that was running very slowly before. But today I ran exec sp_refreshview on all other procs. Some of these procs are 10 years old (that's the create date) even thought they were migrated from 2005 to a 2014 instance.

5

sp_refreshview doesn't work on procs.

What does your statistics maintenance plan look like?

6

That was a mistype, Jeff. I ran sp_refreshview on all views. Thanks I use one of Ola's scripts for maint. I'll have to dig into it to see what it does for stats.