[SQL Server Data Warehousing] Recovery model for DWH databases

Using Recovery Model (Simple) and relying on a mixture of say differential backups during the week and full backups once a week can very significantly reduce maintainance overhead especially when the data warehouse reaches many gigbytes in size. If as Mike says the data warehouse is not populated with new data outside the nightly ETL. So I find in practice if you perform a backup immediately after your perform your ETL you have the most optimised solution in most scenarios.

http://msdn.microsoft.com/en-us/library/ms175987(v=sql.105).aspx

Kind Regards,

Kieran.

---

If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood

.

social.technet.microsoft.com/Forums

[SQL Server Data Warehousing] SQL

Hi all!

I configure OLAP cube.

I have a few projects using cube via c # (AdomdCommand) and some reports (ReportingServises) working directly via MDX.

Where can I save the same requests to avoid duplicating code?
Need something like SP in SQL.

.

social.technet.microsoft.com/Forums

[SQL Server] Efficiently Reuse Gaps in an Identity Column

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

.

sqlteam.com

[SQL Server Data Warehousing] Where to find best practices for tuning data warehouse ETL queries?

Hi Everybody,

Where can I find some good educational material on tuning ETL procedures for a data warehouse environment?  Everything I've found on the web regarding query tuning seems to be geared only toward OLTP systems.  (For example, most of our ETL queries don't use a WHERE statement, so the vast majority of searches are table scans and index scans, whereas most index tuning sites are striving for index seeks.)

I have read Microsoft's "Best Practices for Data Warehousing with SQL Server 2008R2," but I was only able to glean a few helpful hints that don't also apply to OLTP systems:

• often better to recompile stored procedure query plans in order to eliminate variances introduced by parameter sniffing (i.e., better to use the right plan than to save a few seconds and use a cached plan SOMETIMES);


• partition tables that are larger than 50 GB;


• use minimal logging to load data precisely where you want it as fast as possible;


• often better to disable non-clustered indexes before inserting a large number of rows and then rebuild them immdiately afterward (sometimes even for clustered indexes, but test first);


• rebuild statistics after every load of a table.

But I still feel like I'm missing some very crucial concepts for performant ETL development.

BTW, our office uses SSIS, but only as a glorified stored procedure execution manager, so I'm not looking for SSIS ETL best practices.  Except for a few packages that pull from source systems, the majority of our SSIS packages consist of numerous "Execute SQL" tasks.

Thanks, and any best practices you could include here would be greatly appreciated.

-Eric

.

social.technet.microsoft.com/Forums

[SQL Server] Using REPLACE in an UPDATE statement

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

.

sqlteam.com

[SQL Server] Handling SQL Server Errors

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

.

sqlteam.com

[SQL Server] Advanced SQL Server 2008 Extended Events with Examples

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

.

sqlteam.com