Posted - 03/26/2014 : 09:02:05
| dear forum members,
i am a sap business objects administrator (BI reporting tool), lately i have been asked to maintain the SQL Server 2008 R2 (standard edition) database which holds a datawarehouse DB.
So far there were no such activities done on the DWH and we never hada DBA here so you can assume how bad things are for me at the moment.
To be honest my skills in SQL server are just average, but i have fair knowledge on Oracle administration so this makes me understand/grasp a couple of concepts easily.
my tasks for the next few weeks is to plan a maintenance activities on the DWH to address performance issues.
Anyway, to explain the current situation. we have 2 databases within a single SQL server standalone instance.
one is a staging DB and other is the DWH. We don't do any kind of database / logs backup on a daily basis because its a virtual machine and we do a virtual backups daily.
Also there are no clusters, no log shipping or no mirroring so i am assuming my tasks would be a bit straight forward.
getting a little more technical, i found that each DB has 8 data files and each datafile is above 200GB in size. Also none of the DB has undergone a statistics and index rebuild or update in the last 3 years.
What is expected from me is to come up with,
1. a maintenance plan for the DWH
2. find out performance bottlenecks and address them
3. improve the performance of SSIS packages on the server (yes we have SSIS packages running on the same server)
Please can help me how and where do i begin with, any suggestions and ideas from experts would be really helpful.
Though I am reading threads and posts online to learn things but I am really searching for a right direction to proceed with.
Thanks for reading and looking forward to hear from you.