title: Index Tuning for SSIS tasks
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>We are going to put indexes based on usage - by analyzing new queries and current query performance.</p>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <p><code>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</code></p>
  
  <p><code>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</code></p>
  
  <p><code>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</code></p>
  
  <p><code>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</code></p>
  
  <p>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?</p>
  
  <p>So, I have multiple questions:</p>
  
  <ol>
  <li>How do I get it to capture the actual statement executing in my trace, not what was submitted in a batch?</li>
  <li>Are there any best practices to follow for tuning performance related to SSIS packages running against SQL Server 2008?</li>
  </ol>