Index Tuning for SSIS tasks

Question

I am loading tables in my warehouse using SSIS. Since my SSIS is slow, it seemed like a great idea to build indexes on the tables.

There are no primary keys (and therefore, foreign keys), indexes (clustered or otherwise), constraints, on this warehouse. In other words, it is 100% efficiency free.

We are going to put indexes based on usage - by analyzing new queries and current query performance.

So, instead of doing it our old fashioned sweat and grunt way of actually reading the SQL statements and execution plans, I thought I'd put the shiny new Database Engine Tuning Advisor to use.

I turned SQL logging off in my SSIS package and ran a "Tuning" trace, saved it to a table and analyzed the output in the Tuning Advisor. Most of the lookups are done as:

exec sp_executesql N'SELECT [Active], [CompanyID], [CompanyName], [CompanyShortName], [CompanyTypeID], [HierarchyNodeID] FROM [dbo].[Company] WHERE ([CompanyID]=@P1) AND ([StartDateTime] IS NOT NULL AND [EndDateTime] IS NULL)',N'@P1 int',1

exec sp_executesql N'SELECT [Active], [CompanyID], [CompanyName], [CompanyShortName], [CompanyTypeID], [HierarchyNodeID] FROM [dbo].[Company] WHERE ([CompanyID]=@P1) AND ([StartDateTime] IS NOT NULL AND [EndDateTime] IS NULL)',N'@P1 int',2

exec sp_executesql N'SELECT [Active], [CompanyID], [CompanyName], [CompanyShortName], [CompanyTypeID], [HierarchyNodeID] FROM [dbo].[Company] WHERE ([CompanyID]=@P1) AND ([StartDateTime] IS NOT NULL AND [EndDateTime] IS NULL)',N'@P1 int',3

exec sp_executesql N'SELECT [Active], [CompanyID], [CompanyName], [CompanyShortName], [CompanyTypeID], [HierarchyNodeID] FROM [dbo].[Company] WHERE ([CompanyID]=@P1) AND ([StartDateTime] IS NOT NULL AND [EndDateTime] IS NULL)',N'@P1 int',4

and when analyzed, these statements have the reason "Event does not reference any tables". Huh? Does it not see the FROM dbo.Company??!! What is going on here?

So, I have multiple questions:

1. How do I get it to capture the actual statement executing in my trace, not what was submitted in a batch?
2. Are there any best practices to follow for tuning performance related to SSIS packages running against SQL Server 2008?

Answer 1

Warehouses always have indexes, at least in my universe :-). ETL load is usually simple to index for, because the queries are really basic. (You just index the business key columns to make fetching the warehouse keys faster in a lookup.)

E.G. "WHERE ([CompanyID]=@P1) AND ([StartDateTime] IS NOT NULL AND [EndDateTime] IS NULL)" looks like maybe CompanyID is a lookup key and needs an index.

What you are seeing in your sample is parameterized SQL (which is good). Have you considered using the missing index DMVs instead of the tuning wizard? I personally like that method, as it's simple and direct. Just don't immediately take all the suggested indexes; judgement is required to see which are really wise to implement.

Also, if your load is slow, it might be due to other things. Are foreign keys in play? Are you bulk loading (that's tricky, because you can ask the server for bulk inserts, and it can ignore your request based on the details of clustered and non-clustered indexes in the target tables)