Would a columnstore index improve this insert

RichardBrianSmith 2016-01-14 11:13:31

I have been trying for ages to get this insert under 3 hours. It is the worst performing part of our nightly DW (sql2012) import from Prod (sql2008r2). It suddenly occured to me that perhaps a column-store-index is the way to go (I havent used them yet)?

PS: running the select gives error – An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

/storage/attachments/1450-Plan.queryanalysis
SQLkiwi 2016-01-15 03:24:39
Please edit your question to include a plan for the actual insert, and more details: There are a wide variety of potential factors in play here, not least the indexing on the destination table, and whether a linked server is in use.
RichardBrianSmith 2016-01-18 12:09:41
Sorry Gents, I'll filter out the whole insert statement into its own job-step and manually run it to capture the plan (I guess I'll need to post a-new into "SQLPerformance.com"?) . In the meantime … "INSERT tblWorkflowItemData (ItemID, DataObject, FieldID, Value)". btw there's no linked-server.
SQLkiwi 2016-01-19 04:08:29
You should be able to edit your existing question. By all means post a new one if that's easier.
RichardBrianSmith 2016-01-20 09:07:03
Stayed up and ran the insert part manually. Posted new as "Three hour insert". thanks.
Aaron Bertrand 2016-01-14 14:18:00
The plan is just a select. Have you considered the index that the engine recommends?

CREATE INDEX [tDOD_V1]
ON [dbo].[tblDataObjectDetail] ([Value])
INCLUDE ([DataObjectID],[FieldID]);

Or perhaps a filtered index would work as well (depending on how many rows actually have NULL in the Value column):

CREATE INDEX [tVOD_V2
ON [dbo].[tblDataObjectDetail] ([Value])
INCLUDE ([DataObjectID],[FieldID])
WHERE [Value] IS NOT NULL;

(However, since it seems like this is a large portion of the table, I don't know that the filter will add any substantial benefit, at least for this query. If you have other queries where you filter on only the NULL values, a filtered index with the opposite clause might be useful.)

The exception you're getting is from Management Studio, not SQL Server, and it sounds like you're running the query on a machine that doesn't have enough memory capacity to render the results. You are returning 47MM rows to a client grid – surely it is not useful to try to render that many rows.

January 14, 2016
No Comments