Performance Degradation

We have a view in production that have been working fine. Recently, the performance on it has changed signficantly. The view is "union" (not "union all") of select statements on four other views. In the past, it would take a few minutes to return the resultset back, but now, it's taking like 30+ minutes.

The individual select statements only take 1 minutes, 2 minutes, 4 minutes and 9 minutes respectively. But when you run the overall select statement with the unioning of the 4, it takes 30+ minutes. This shows the tempdb resources needed to execute the statement is taxing. When we changed the union to union all, the statement only took 13 minutes to run.

Over the weekend, a decimal field was widened from 9,2 to 11,2. Replication was turned off before the field change and then turned back on after the changes to the field. (I hope I have that replication explained right. I'm not familiar with replication as a process.) There was mention that any custom indexes might have been overwritten/lost due to the replication.

The DBA reindexed all the underlying tables for the views tonight.

My question is if the reindexing doesn't improve the performance. Where else can we check? What else can we do?

Check statistics? Check the transaction log/drive? Does calling a view cause impact on the transaction log? Another thought would be to place indexes on the views. We don't have any in place at the moment.

Unfortunately, I can't post the TSQL due to company rules.

Any ideas to improve the performance would be greatly appreciated.

KenReindexing is a good start. Next you might want to take a look at the query plan. Perhaps, you do not have appropriate index.

Also, if you do not need filtering, consider using 'union all' instead of just 'union'. When you only specify 'union', the system will have to filter out duplicates data. Thus, increase overhead.