January 22, 2014 at 8:29 am
Hi All, i hope i have posted this in the right place.
I am looking for some advice on what better knowledge and help i can offer the Datawarehouse/Systems Architecture manager. Working as a security dba for many years in a very compartmentalized Housing/Regeneration IT dept, I have had limited opportunities to delve in to more interesting areas such as this.
I have started to look more at the DWhouse nightly/weekly builds. Currently looking into plan cach behavior, memory use per database /object, Paremeters for queries to ensure efficient compilation/optimization and execution for adhoc workloads alongwith compiled and prepared
Basically i want to be able to offer a better opinion on whether the current methods are best practice or not. If not then i'd like to pin point certain areas, hence my research into the plan cache behavior. I am thinking (secretly hoping) this would give me an insight into whether their current programming practices are consistent and efficient enough.
Thanks in advance!
January 22, 2014 at 9:05 am
From the sounds of things, you're not so much looking at data warehousing as you are the performance of the queries being used against the data warehouse. If so, I'd suggest grabbing a copy of my book on query tuning. It's every bit as applicable to a data warehouse as it is to OLTP systems (although I'll admit the book has a clear OLTP bias). If not, someone else can surely offer some better advice.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 23, 2014 at 5:14 am
Thanks for your reply and recomendation to read your book, i shall most defintely look it up.
I think you're right, i have never had to get so heavily involved with the workings of a stored procedure and/or the plan cache and how it can effect performance if designed badly. I have to tred carefully.
thanks
š
January 23, 2014 at 5:21 am
In some aspects an OLTP database and a data warehouse are much alike: indexes and statistics need to be maintained, queries must be fast et cetera.
There are important differences though: a data warehouse is optimized for read access. This can be done by denormalizing the data for example.
It's also current that there are no foreign keys, as integrity can be handled by the ETL system. It's also possible that backups are less current due to the size of the data warehouse (with the important condition that the data warehouse can be rebuild from scratch while still preserving history). Typically the data warehouse has the simple recovery model.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 28, 2014 at 4:16 am
Hi Koen thanks for your thoughts. All of what you have said rings true here on our Data Warehouse. I am still trying to get to grips with the architecture and its interconnections etc. I think Iām trying to run before I can walk by reading a varied amount of information. There is so much out there is just trying to digest it correctly.:crazy:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply