Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rajsin
Starting Member
4 Posts |
 Posted - 2014-03-26 : 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 DWH2. find out performance bottlenecks and address them3. 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.Raj rajsin |
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2014-05-07 : 05:45:49
|
| For index maintenance thing have a look at Ola Hallengren's index maintenance script.It checks the index fragmentation and then performs the required action(rebuild or reorganize)Javeed Ahmed |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2014-05-07 : 13:23:15
|
| Datawarehouses become larger over time - therefore a regular review of how you maintain the datawarehouse is a useful exercise. Even with regular incex maintenance - you might find some sort of scaling techniques may be required - e.g archiving data - http://www.sqlserver-dba.com/2013/01/sql-server-archiving-data-into-a-separate-database.htmlBut key is to first get the basics - backup strategy satisfies RPO , and peformance is satisfactoryJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
involuntary DBA to manageDWH