10 Tips to Optimize Data Warehouse Reporting

Unless your marketing data warehouse is stored in a massively parallel appliance such as Netezza, your data most likely resides on a Symmetric Multi-Processing (SMP) computer in a relational database engine, such as Microsoft SQL Server.

SQL Server is a highly scalable, versatile database product that can serve the needs of a wide range of applications, from the tiniest 100MB database all the way up to multi-terabyte data warehouses.  Along with its versatility comes the responsibility of the data architects to optimize the configuration of the database for the specific application.  The configuration of a transactional database, such as an order processing system, will look much different than a heavy hitting operational data warehouse.

For large-scale data warehouses, such as marketing automation databases, optimization often means providing the end users with the fastest reporting times possible.  Slow response times will quickly frustrate them, and inevitably, result in your phone ringing off the desk. 

If you are running a large data warehouse, and don’t like having the red phone light up all day long, there are a few simple rules that must be followed in order to keep your customers happy:

  1. Spread your data across as many physical disk drives as possible and as evenly as possible.  If you are building a large data warehouse, don’t put all of your data in a single file group that contains a single file.  In order to do this you will need to:
    • Create multiple file groups.  File GROUPS are logical database containers that are used to hold database files.
    • Create multiple database files for each of the file GROUPS.  These are the physical database files.  Ideally they will be spread across multiple physical drives. 
    • Rule #1: A database RECORD is always contained in a single database file.  It is NEVER spread across multiple database files, even if those database files are in the same file GROUP.
    • Rule #2: A database TABLE is always contained in a single database file GROUP.
    • Rule #3: SQL Server uses a proportional fill algorithm to spread writes across the multiple database files (within a file group).  If all database files in the FILE GROUP are the same size the same number of records will be written to each individual file (desired affect!!!).  Hence, always create database files within the same file group to have the same size.
  2. Initialize your database files to the max size that will be needed to hold your data.  This is way easier said than done.  It’s very easy to grossly under estimate the amount of data that will be flowing into your data warehouse.  When this happens the data files will auto grow, which is a SLOW process.  Even more important in the context of reporting, the new disk space added to the file group will probably not be contiguous with the original file, which will lead to slower reporting capabilities.
  3. Use the fewest number of threads possible when loading data.  If you set the MAXDOP (max degree of parallelism) to 1 the incoming data will be written to contiguous chunks of disk space.  Data warehouse reporting is typically grabbing lots of related records, usually over a time range.  This results in pulling records that are all loaded at the same time.  This is known as a RANGE SCAN.  Range scans work best when the data is stored on contiguous blocks of disk.  It’s really that simple…  By using a single thread to lay the data down on the disk, we are sacrificing load performance for improved reporting performance.  That’s OK, you only load the data once and hopefully no people are involved in the load process.  The data will be read many times over by people waiting for reports to come out of the system.  A slower load time is a fair trade for a faster user experience any day of the week.
  4. Maintain the highest quality statistics on your data as possible.  If your tables tend to grow incrementally over time, the auto statistics feature of SQL Server will work fine, for a while.  At some point the low sample rate of the auto stats will result in inaccurate statistics.  For that reason it is absolutely imperative that a rock solid statistics maintenance plan be put in place.  And don’t be fooled into thinking that the stock “maintenance plan” wizard in SQL Server will suffice.  What happens if a statistic becomes severely out of date?  The query optimizer will generate a bad plan for the SQL query and the query will appear “hung”.  The consumer of the data will think the server is busy or that you have a bad design, when in fact you just have outdated statistics.
  5. Give SQL Server as much memory as you can afford.  Whatever you give it, it will use.  If your server is a dedicated database  server, turn on Lock pages in memory.  This will prevent SQL Server from ever giving up memory after it has allocated it.  If your server is used for other applications however, locking memory pages may end up starving those applications of memory, which may end up making the database look slow.
  6. On a related note, separate services when ever possible.  SQL Server Integration Services (SSIS), like the SQL Server engine, will grab as much memory as is available.  If you just gave all of the system memory to SQL Server (minus a few Gig so you don’t starve the OS) what will be left for your ETL/loads?  Put SSIS on a separate server.
  7. If you have tables that are loaded once a month and then used for reporting purposes the rest of the month, consider placing these tables in READ ONLY file groups.  The SQL engine will reward you with a drastic reduction in locks locks on your data, and your data consumers will think you are a genius.
  8. For very large tables, such as more than 100M records, consider a horizontal partitioning strategy.  SQL Server does not have a nice wizard to setup partitioning.  It is very technical to implement and deploy.  But if you have a table with 200M records, but usually don’t care about any of the data except for the most recent month, horizontal partitioning will allow correctly designed queries to ignore all but that most recent month’s data.  This is called partition elimination, and can/will make a huge difference in query performance.
  9. Do your housecleaning…  Get rid of indexes that are no longer needed.  Add new indexes sparingly and with great caution.  But do so when it makes sense.  I’ve never seen a perfectly indexed database in the initial design.  Drop “temp”/”test” tables.  Archive old data.  Better yet, deploy an automated archiving process.  There’s no point in keeping 10 years’ worth of data when your end users only need the current year plus last year.
  10. And last but not least, my personal favorite, NEVER SHRINK a database or database file.  If you do, you just fragmented your data and indexes, probably FOREVER.  The only way to get rid of that fragmentation will be to move the data to a new file group that isn’t fragmented.  The catch 22… if you just did a shrink on your database it’s probably because you were running low on disk space.  If you don’t have disk space to auto-grow your existing data files, you probably don’t have the disk space to move the data to a new file group.  The data will remain fragmented, FOREVER.

Your reporting customers want their data as simply and as quickly as possible.  Making that happen only requires following some basic design and maintenance strategies.

What strategies have you employed to avoid a data scare-house?


Leave a Reply

You must be logged in to post a comment.

Using Gravatars in the comments - get your own and be recognized!

XHTML: These are some of the tags you can use: <a href=""> <b> <blockquote> <code> <em> <i> <strike> <strong>

kurtz final words cheap bibliography editing sites for phd analysis essay editing for hire online write popular critical essay on trump esl dissertation hypothesis ghostwriting for hire for university professional argumentative essay editor sites for college custom home work writer services for masters best article review proofreading for hire uk top course work writers websites au help with anthropology article custom case study writers site ca custom admission paper ghostwriter for hire uk abortion in the media top letter ghostwriting service for university cheap descriptive essay proofreading service usa top personal statement ghostwriting website best content writing websites best biography editing site online popular dissertation results ghostwriters service for college esl scholarship essay ghostwriters sites ca best curriculum vitae editing services online write me marketing admission essay scholarship writing sites esl research paper writers websites online esl thesis statement ghostwriting websites us do my top application letter online top personal statement ghostwriter sites online best essay ghostwriters services online cheap creative essay editor site for university best definition essay writers website ca professional thesis proposal ghostwriters site for masters help me write professional expository essay on brexit top dissertation conclusion writing for hire custom essays ghostwriter for hire for mba esl blog writers services gb best creative writing writer site for university write my cheap descriptive essay on trump cheap papers ghostwriter service for mba cheap proofreading for hire for mba write cheap argumentative essay on pokemon go best paper writers sites usa professional problem solving ghostwriting sites us order literature dissertation chapter cheap homework proofreading services au popular letter proofreading services au esl application letter writer services for college esl article editor sites ca popular speech writing site pay to get top school essay on trump write my popular definition essay on usa popular resume ghostwriter website for school top mba dissertation conclusion top personal essay editing for hire for university order descriptive essay on trump creative writing ghostwriter site gb best essay on civil war cheap dissertation results ghostwriting website for masters professional mba presentation topics cheap definition essay ghostwriters services ca professional problem solving proofreading site for phd buy poetry home work persuasive essay proofreading sites au best book review proofreading service advice for book review write me u.s. history and government dissertation results cheap problem solving proofreading site gb top academic essay ghostwriting sites online order esl analysis essay on hillary esl application letter ghostwriters for hire ca esl phd essay ghostwriter websites usa popular case study proofreading websites for school cheap admission essay ghostwriting website for school help with esl research paper online outer party 1984 top blog writer site for masters custom report proofreading service usa order speech bibliography custom blog post editor for hire top thesis editing sites online write popular college essay on civil war custom essays ghostwriters services au esl bibliography ghostwriter websites us essay on immigration in the us cheap dissertation conclusion writing for hire for university esl business plan writer sites gb best assignment ghostwriting sites for masters professional argumentative essay writers for hire for phd best speech editor website us cheap critical essay ghostwriter sites au custom thesis statement editor for hire gb popular business plan writers site for college custom editor for hire us esl blog proofreading for hire for university popular rhetorical analysis essay ghostwriters sites gb cheap curriculum vitae proofreading websites uk esl speech writing site best research proposal writers site for college best critical thinking writer for hire gb pay to do theater studies letter best thesis statement ghostwriter service for school professional application letter writing sites uk buy professional admission essay on hillary professional writers service for mba popular dissertation introduction writers service ca professional speech ghostwriting services for masters professional personal statement writers websites gb do my popular application letter online popular annotated bibliography ghostwriters websites for school top research proposal writers service usa buy admission essay on usa pay for my anthropology dissertation introduction stereotype in media top university analysis essay ideas help with my professional creative essay on hillary top definition essay writing service professional ghostwriting websites popular content editing site for university college essay editing service au custom masters business plan examples pay to write geometry creative writing best admission essay writers services for phd custom cv editing websites esl critical essay writing service usa professional course work proofreading websites gb order cheap critical essay on hillary clinton dissertation writer service au top cheap essay editor websites gb popular application letter ghostwriters websites uk letter ghostwriter website uk why did moctezuma reorganize the aztec government? descriptive essay about nature custom book review proofreading site for school custom homework writer website online analysis ghostwriter service us movie review ghostwriter services gb popular business plan ghostwriters for hire for masters why volunteer at a hospital essay esl cv ghostwriters service for masters academic writer for hire us help with my film studies admission essay top dissertation results writers website for university write custom university essay on usa top letter writers websites for university write math dissertation methodology cheap analysis essay proofreading for hire ca custom bibliography ghostwriters service au pay to write best essay on lincoln write me custom phd essay on lincoln custom critical thinking ghostwriting for hire uk custom biography writer website usa best papers writers site online creative essay writer site ca great expectations irony esl reflective essay ghostwriting services best blog post editor websites usa professional article writers website for phd top dissertation results ghostwriter service ca top rhetorical analysis essay ghostwriter website au best essay ghostwriters services esl creative essay editing site for mba popular admission essay editor site for school custom cv proofreading site for university popular book review editing site for college professional mba college essay examples best academic essay proofreading sites for college esl best essay writer for hire online best thesis proposal editor service online cheap problem solving writing sites for masters crime and punishment essay cheap case study ghostwriters service online popular home work writers websites for mba custom assignment ghostwriting service us esl content writer website pay to get art & architecture dissertation methodology esl critical thinking writers services gb help me write popular rhetorical analysis essay esl dissertation introduction writing websites au custom homework ghostwriting sites for university help with criminal law cover letter order professional descriptive essay on founding fathers popular blog ghostwriters services for masters do my geometry admission essay best personal essay writing sites for phd top article editing sites us custom analysis essay proofreading site us dissertation methodology writers services gb bibliography editor sites cheap expository essay editor website top expository essay ghostwriters website online popular application letter writer websites for phd pay for my u.s. history and government dissertation esl school book review assistance write my custom persuasive essay popular blog post writer service for phd sport essay custom dissertation results writing websites for school custom assignment proofreading for hire gb custom letter writing site us professional papers writing for hire for masters help with government thesis proposal curriculum vitae proofreading website online custom letter ghostwriters for hire usa help writing world literature dissertation introduction professional papers writing services for masters write my environmental studies literature review professional article review writer website usa best personal statement editor site for school do my management research paper article review editor sites gb cheap academic essay editor website for mba popular creative essay editor sites for masters professional biography ghostwriter for hire gb top literature review editor website usa japanese and european feudalism best cheap essay proofreading services for phd cheap phd essay editor websites us best report editor website gb popular cover letter ghostwriters sites popular college research paper assistance custom problem solving editing websites usa popular dissertation methodology ghostwriter service top definition essay on civil war university essay editor websites top masters essay editing site us esl essay editor services for phd professional argumentative essay writing for hire for phd write me anthropology dissertation hypothesis custom business plan ghostwriting sites for school do my art & architecture article top cv ghostwriters services au esl cover letter writing website for university thesis proofreading sites thesis statement writing for hire us top scholarship essay ghostwriter service uk cover letter writing website uk popular definition essay ghostwriters for hire for masters professional article ghostwriting for hire for mba thesis writing service gb my wood by em forster custom dissertation abstract writers for hire ca professional mba dissertation results topic best article writer websites us custom bibliography proofreading service pay for communication dissertation hypothesis professional letter writers service usa popular phd essay ghostwriter website au top book review editor services gb top dissertation editor service ca professional blog editing services for mba scholarship essay writing service uk old regime in france hills like white elephants point of view popular literature review writer for hire online pay to write university essay on trump professional phd essay writing services popular reflective essay proofreading services gb college proofreading sites ca order drama admission paper pay to get logic book review ruth c engs popular admission essay writers sites for college professional creative writing writer services for masters top letter writing service for school professional annotated bibliography writer website uk custom dissertation results proofreading for hire ca esl cheap essay proofreading site for school professional analysis essay writing site for mba rhetorical analysis essay editor for hire us popular essays writing services usa top academic essay proofreading site online custom book review proofreading websites uk dissertation proposal editor service uk popular custom essay writers website us tyger and the lamb custom dissertation results editing services for college popular masters admission essay sample dissertation proposal ghostwriter services au custom research proposal writer for hire for university culture ghostwriters website popular dissertation introduction ghostwriting for hire for university top business plan ghostwriter service au popular admission essay editor site for mba best creative writing writing for hire au best case study writers website for masters creative writing editing site ca pay to write health critical thinking was hamlet insane cheap university creative essay topic help with professional phd essay on donald trump professional college cv examples cheap definition essay ghostwriting website uk popular university dissertation introduction assistance best resume ghostwriting websites gb write me top dissertation results esl expository essay writers services for mba paper ghostwriting for hire order popular creative essay on usa esl dissertation chapter proofreading websites gb top cheap essay editing sites for university help me write custom curriculum vitae online cheap admission essay ghostwriter websites for masters popular paper editor services for school pay to do government dissertation methodology admission writers sites online help with cheap admission essay on hacking cheap mba dissertation abstract esl book review writers service for phd pay for my critical essay on trump popular dissertation proposal writer services custom masters essay proofreading services for mba best school essay on hillary esl critical essay proofreading site gb best essay editor services usa best thesis ghostwriters site gb popular school essay ghostwriter sites for school custom research proposal writing site online pay for my best definition essay on pokemon go best dissertation introduction ghostwriter site gb cheap scholarship essay ghostwriters website for school esl school essay writing service usa best dissertation methodology editor sites gb life science essay ghostwriting services top phd essay writer services online top annotated bibliography ghostwriter website ca write my university essay on shakespeare popular critical analysis essay editing for hire usa best descriptive essay writing sites for college top personal statement proofreading sites gb top personal essay ghostwriting sites for mba custom definition essay writer site for school dissertation results proofreading for hire online cheap university essay writers site for college presentation writer site online best content ghostwriters site uk popular assignment writing services for university shmoop king lear marketing essay editing websites esl school essay ghostwriting website for mba george herbert mead stages cheap school content help type my art & architecture letter best dissertation abstract ghostwriting service usa type my management bibliography esl creative writing writing website ca esl critical essay editor for hire uk esl essays editor websites for masters esl assignment writers site au dissertation hypothesis ghostwriters for hire gb definition ghostwriting site usa william blake and william wordsworth professional dissertation introduction writers sites for phd custom dissertation results ghostwriter services for mba esl thesis ghostwriter for hire for mba best presentation ghostwriting website professional cv writers service au write me geology book review best personal statement editor sites best dissertation chapter ghostwriting for hire online characters in oliver twist buy esl academic essay on hillary clinton write my cheap blog online best school essay writer site usa rhetorical analysis writers site custom analysis essay editing for hire cheap university essay ghostwriter site for masters do my professional rhetorical analysis essay on donald trump esl masters admission essay help top phd paper example help with my custom essay on hacking dissertation proposal proofreading type my custom school essay on donald trump custom creative essay ghostwriter services uk esl blog proofreading website for masters bell hooks selling hot pussy top school essay advice help writing geometry cv custom custom essay proofreading sites content writing website us cheap course work writers service for phd custom cover letter writer websites online best blog post writers websites for mba the juggler poem epic of gilgamesh importance custom speech writing sites for college custom thesis proposal editing websites usa custom dissertation proposal writing websites ca esl masters rhetorical analysis essay advice letter writer services uk esl curriculum vitae ghostwriter site uk professional presentation writer site for university popular college essay ghostwriting website au best custom essay proofreading site us top assignment writing websites au cheap annotated bibliography editing sites us professional home work ghostwriter site gb write me top phd essay on donald trump popular dissertation introduction writer service au custom dissertation proposal editor site usa esl definition essay writing for hire for college argumentative essay writers services uk esl personal essay editor website for masters friendship quotes in of mice and men custom dissertation abstract ghostwriters service uk best reflective essay writing websites online top business plan proofreading sites ca best annotated bibliography editing for hire best literature review proofreading website au top college essay proofreading websites au best academic essay editor service online best research paper editor websites pay to write professional persuasive essay custom definition essay writing for hire custom cv editing service us esl college essays samples top descriptive essay ghostwriter sites for mba cheap dissertation proposal writers services ca write me cheap critical essay on brexit best thesis ghostwriters websites usa popular blog editor site for school custom rhetorical analysis essay proofreading for hire us esl critical analysis essay proofreading websites for phd esl mba essay writer service online top cv editor website for university help with my medicine critical thinking top dissertation proposal writer website online esl essays editing for hire for masters top masters essay editing service for phd custom letter writing service for college cheap content ghostwriting websites for masters pay to get theater studies blog post pay to write esl personal essay on brexit custom term paper ghostwriting website uk best university essay ghostwriter services for college cheap blog writing for hire gb pay for top academic essay on founding fathers do my esl analysis essay on usa dissertation results editor services top course work writing site gb custom thesis proposal editing services us critical analysis writing service ca popular term paper editing site gb cheap problem solving editing for hire for mba pay to get descriptive essay on hillary clinton best university personal essay assistance custom rhetorical analysis essay writers website popular course work writer website usa professional movie review ghostwriter sites for masters custom descriptive essay ghostwriting services au cheap cheap essay writer website for mba custom dissertation introduction writers site for phd top creative essay writers service top scholarship essay writing website for school do my esl argumentative essay on civil war professional proofreading for hire cheap university essay proofreading website ca cheap rhetorical analysis essay writing services for phd cheap admission paper proofreading website au popular research proposal writing service gb top thesis ghostwriting for hire au esl application letter ghostwriters sites for university help with my science case study professional dissertation editing services online professional personal statement proofreading sites for university cheap letter writing sites au critical analysis essay writers sites au custom critical essay writer services gb popular article ghostwriter websites uk help with custom masters essay on shakespeare popular speech writers services uk pay to do botany course work esl college essay editor websites for school cheap university essay editor website cheap literature review writing for hire au help with journalism research proposal pay for best phd essay on lincoln pay to do family and consumer science thesis proposal pay to write popular definition essay on hillary clinton letter from birmingham jail analysis professional content editing site uk custom cheap essay ghostwriter for hire for mba remembering my father critical analysis writer services au top cheap essay writer sites for phd college ghostwriters site au animal testing should be banned popular thesis proposal writer for hire uk custom research paper writing website for phd write phd essay on donald trump custom resume ghostwriter websites for college popular descriptive essay writing for hire for school alone poe analysis popular dissertation results writer site us archetypes in macbeth cheap report writing sites for university type my investments movie review professional paper writer for hire online best speech ghostwriting sites gb personal statement ghostwriting sites ca cheap papers writing website best literature review writers sites for masters