Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 involuntary DBA to manageDWH
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

United Kingdom
4 Posts

Posted - 03/26/2014 :  09:02:05  Show Profile  Reply with Quote
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.



Aged Yak Warrior

737 Posts

Posted - 05/07/2014 :  05:45:49  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Flowing Fount of Yak Knowledge

United Kingdom
2179 Posts

Posted - 05/07/2014 :  13:23:15  Show Profile  Visit jackv's Homepage  Reply with Quote
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 -
But key is to first get the basics - backup strategy satisfies RPO , and peformance is satisfactory

Jack Vamvas
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000