4

We have a data warehouse built in MSSQL 2012, Staging area, EDW, cubes and reports.

In the staging area, the tables are a 1:1 copy of those supporting the systems used to fill the DWH with. The staging area tables are TRUNC'ed and refilled daily (nightly). Then views on those tables provides input for the ETL process towards the EDW.

Besides providing a copy for the EDW to work with, these staging tables are also queried using MSSQL Management Studio by some of our analists, as not to put pressure on the source systems with their queries.

For both the ETL towards EDW as for the analist's queries, I get the idea that having an index on (some of) these tables might aid performance (greatly). Is it wise to have indexes on (large) tables that get TRUNC'ed daily. If not, why? And what kind of index (Clustered vs Non-clusterd vs someting else entirely)?

Improve this question
5
  • What pain is being avoided by not creating the indexes you say will help a lot? Storage space or throughput? Memory? Time? – Jon Seigel May 28 '14 at 15:41
  • Truth be told, that's what I'm trying to find out. I can imagine a drop in performance when copying 3.000.000 records into an indexed table, or an increase in logging or disk space usage. I'm trying to see if these concernsare only imagined or if this is warranted. If so, what index type would be most suitable for this scenario? – steenbergh May 28 '14 at 16:31
  • We can tell you that it will affect certain things, but we can't tell you if it's a concern for your system without an extensive investigation. Only you can answer those questions. I don't use the same storage as you. I don't know what the queries being run look like. I don't know what the table schema looks like. Etc. – Jon Seigel May 28 '14 at 19:34
  • So basically what you're telling me: it's not odd or especially inconvenient / unconventional to have indexes on tables in a daily cycle of truncing and refillig, right? – steenbergh May 28 '14 at 22:11
  • 1
    For the most part, it's not a big deal. Depending on the type of index(es) needed, it may involve a bit of change in the ETL in terms of dropping/recreating, but really that's a small tradeoff for the benefits of having the tables indexed well to satisfy the queries being thrown at them. Barring other factors, as I mentioned, of course. – Jon Seigel May 29 '14 at 3:32
1

We have a similar situation as the one you described, except that these copied tables are only used for ETL (by select queries). I'll describe how we approached it, but you have to evaluate if it works for your environment (see Jon Seigel's comment at your question).

The concept is straightforward:

  • We index these "copy" tables in the staging area to support queries as much as possible. Most of indexes (especially on the large tables which get hit by scans) are columnstore indexes (we're on 2012 as well), some are covering nonclustered indexes. As I said, these tables are (for the time being) only used for select queries in the ETL, so we can make sure every query is supported either by a columnstore index or a covering nonclustered index.
  • Before the data pump (copy) from source systems into the staging area, we drop the indexes, to make bulk loads as fast as possible and to ensure we're touching source systems for the minimal amount of time. After the data pump is complete, we recreate them - the logic is set up so that the recreation is done "dynamically" (as in, indexes are not hardcoded - every index that gets dropped is then recreated exactly as it was), which allows us to change indexing without having to maintain the drop/create code.

This approach works for us, drop/recreate is faster than loading into indexed tables; well in the case of a columnstore index you don't have many other options anyway apart from partitioning & using partition switch, but we're not doing that for these tables (EDW fact tables are a different story).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.