October 27, 2011 at 11:37 am
Just started a new job. From my understanding of the data warehouse, they truncate all the tables and repopulate everything nightly using 'select into' statements. The db is over 170gb and takes over 4 hours to process the population. This is not a discussion on how that is done. At least not yet. My problem is that most of these tables are not indexed and statistics are non-existent. So I created an non-clustered index on one of the tables I am using for a report.
My question is, When the truncate and repopulating of the data occurs at night, how does that effect the non-clustered index? Is it rebuilt automatically?
Thanks
Adam
October 27, 2011 at 11:46 am
Indexes are always kept up to date with data modification, so when the table is truncated, so are the indexes. When the table is repopulated, the indexes are updated with the new rows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2011 at 2:01 pm
... and since 100% of the data has been changed in that table, the stats will be updated when the first select runs on that table. So those will be up to date as well.
October 27, 2011 at 4:28 pm
It might be faster if you drop an index before population and re-create it after. It will also reduce the fragmentation and page splits. And in this case statistics for that particular index will be updated with FULL SCAN otherwise it will be updated as well, but with some sample rate which may not be good enough.
October 28, 2011 at 6:24 am
A couple of guys that I talked with here mentioned dropping the index as well and then add it back. Maybe one day one of us can take ownership of it all. The reason I brought this up is because I created a report. I ran the query in optimizer and it recommended an index. Then someone dropped a table. Just happened to be a table I was reporting off of. So I had to rebuild the report using the new tables. It is slower than before. I ran the optimizer again and it told me to create an index and a statics. At the time I though I had already created an index, which led me down the road of 'what happens to indexed with truncated tables'. Now I think the events were that I created the index and then the table was dropped, which left me working with a non-indexed table.
Can't say I am enjoying this new job lol.
November 1, 2011 at 12:57 pm
adams.squared (10/28/2011)
A couple of guys that I talked with here mentioned dropping the index as well and then add it back. Maybe one day one of us can take ownership of it all. The reason I brought this up is because I created a report. I ran the query in optimizer and it recommended an index. Then someone dropped a table. Just happened to be a table I was reporting off of. So I had to rebuild the report using the new tables. It is slower than before. I ran the optimizer again and it told me to create an index and a statics. At the time I though I had already created an index, which led me down the road of 'what happens to indexed with truncated tables'. Now I think the events were that I created the index and then the table was dropped, which left me working with a non-indexed table.Can't say I am enjoying this new job lol.
They absolutely need to index these tables. My previous employer worked with a dev team that created an application that truncated and loaded data. They had no indexes on these tables at first. Our database was only around 2gb, and still extremely slow when querying against it. Identify which queries are run against this data and have them incorporate the proper indexes into their program.
Note: Creating indexes on tables that get truncated and reloaded may make the load job run even slower.
Thanks,
Jared
Jared
CE - Microsoft
November 1, 2011 at 1:59 pm
There is no absolute answer to what is "the best." Dropping indexes may speed an INSERT-based load; however, if the load is done by MERGE (for example, to handle duplicates by WHEN MATCHED), indexes might speed the processing up.
November 1, 2011 at 2:05 pm
Revenant (11/1/2011)
There is no absolute answer to what is "the best." Dropping indexes may speed an INSERT-based load; however, if the load is done by MERGE (for example, to handle duplicates by WHEN MATCHED), indexes might speed the processing up.
Absolutely! However, I am operating under the assumption that it is insert based being that there is no data after a truncate 🙂
IF it is an insert based load, drop the index, insert data, recreate index.
IF it is a MERGE, look at your typical amount of inserts, updates, and deletes compared to your total data to determine which is best.
Jared
Jared
CE - Microsoft
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply