It’s been very long time since i updated my blog. i am really sorry for that. Well coming to the topic. I was working for one of our entrprise customer. We have web analytics data close to 90GB. we are building another application to displaying the data, so i need to migrate the data to new database.
Intially when we started our migration it takes almost 6 days for one month data. I was wondering what to da. Then i followed the below mention steps and it come down to 3 Hours for one month.
1 check for each query execution time and also the subtree cost. to get subtree cost include execution steps from the top bar in management studio. When sub tree cost is more sql server uses full 100% cpu time and memory.
2 created the index for each table. while creating index make sure you create index for all where condition combination.
3 use temp table instead of union all query. Because i combine almost 60 table in single query. So instead of union all i used temp table to get the data
4 remove unnessary column in the select statement
Once you follow this points. It will increase the website and sql performance
If any questions just mail me or add a comment