MSSQLTips

How to Improve performance on partion View? I have partition view that will fetch data by having union all to 9 database tables. All 9 databases have same table and schema structure on same instance. At the background, this tables keep loading new data for every 1 min to all 9 databases through automated jobs since morning. Means every 1 minute heavy INSERT operaion is going on this tables and at the middle of the day table holds more than 2 milion records. I am calling this view in one of the stored procedure. So due to heavy insert operation my procedure is running long or getting TIME OUT. Is there any other way to resolve the issue or any other solution to bit up the issues? Thanks in advance. Sample Script of View :- Create view VIEW1 AS select * from FROM DB1.dbo.table1 union all select * from FROM DB2.dbo.table1 union all select * from FROM DB3.dbo.table1 union all select * from FROM DB4.dbo.table1 union all select * from FROM DB5.dbo.table1 union all select * from FROM DB6.dbo.table1 union all select * from FROM DB7.dbo.table1 union all select * from FROM DB8.dbo.table1 union all select * from FROM DB9.dbo.table1 Thanks and Regards Sachin Bhaygude

 As you are using stored procedure so query plan itself will be stored and efficient.You should consider disk seperation option for as many these tables as you can. I hope you have fine tuned the indexes on tables and best of all will be to filter the selected columns.