Archive for May, 2013

How to view the SQL Server Execution Plans?

Similar to how you plan to drive to the meeting place that your buddy, Billy Bob, has told you about before staring your engine, SQL Server also has plans for every query executions. If the plan is not in the cache, one will be created before executing the query being requested. You may be a man with a plan. SQL Server is a mighty database engine with many plans and they are cached in memory for reuse instead of drawing up new ones unprepared.  Even better, you can remove an existing execution plans, optimize the query, and then re-execute for SQL Server to create a new execution plan and reiterate the process until you are happy with the execution plan.

Now, just how do you go about viewing the execution plans so that you can remove them if needed? In this post, we will perform the following walk-through step by step:

  1. Create a new test database.
  2. Create a new table by importing data from the AdventureWorks2008R2.Production.TransactionHistory table.
  3. Query the newly created table.
  4. Query the DMVs to view the execution plan created.
  5. Save the execution plan with a .sqlplan extension to view it in a GUI window if needed.
  6. You can remove the execution plan by running the DBCC FREEPROCCACHE() function by specifying the plan handle.
  7. You can then optimize the query by adding indexes or query hints if needed and then re-execute when ready to create a new execution plan.

Implementation steps:

  1. Create a new test database.
  2. -- create a test database
    -- !!!Do not do this in a production server!!!
    create database TestDB
    
  3. Create a new table by importing data from the AdventureWorks2008R2.Production.TransactionHistory table.
  4. -- import data from the AdventureWorks2008R2.Production.TransactionHistory table
    USE TestDB
    GO
    SELECT *
    INTO dbo.TransactionHistory
    FROM AdventureWorks2008R2.Production.TransactionHistory
    
  5. Query the newly created table.
  6. -- create an index on the TransactionID column
    ALTER TABLE dbo.TransactionHistory
    ADD CONSTRAINT [PK_TransactionID] PRIMARY KEY (TransactionID)
    
    -- create an index on the ProductID column
    CREATE NONCLUSTERED INDEX IX_TransactionHistory_ProductID
    ON dbo.TransactionHistory (ProductID)
    
    -- query the newly created table
    SELECT [TransactionID]
          ,[ProductID]
          ,[ReferenceOrderID]
          ,[ReferenceOrderLineID]
          ,[TransactionDate]
          ,[TransactionType]
          ,[Quantity]
          ,[ActualCost]
          ,[ModifiedDate]
      FROM [TestDB].[dbo].[TransactionHistory]
    WHERE ProductID = 784  
    
  7. Query the DMVs to view the execution plan created.
  8. -- query the DMVs to view the query plan created
    SELECT st.text, qs.execution_count, qp.query_plan, qs.plan_handle
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    WHERE st.text like '%ProductID = 784%' -- specify the code to filter 
    
  9. Save the execution plan with a .sqlplan extension to view it in a GUI window if needed.
  10. You can click on the XML link in the query_plan column. If a new window with XML code shows up, you can save it as a .sqlplan file and then open it in a GUI format to view it. If it opens in a GUI format window for the execution plan, you can save it for review in a later time or share with your colleagues for further analysis.

  11. You can remove the execution plan by running the DBCC FREEPROCCACHE() function by specifying the plan handle.
  12. -- remove the execution plan specifying its plan handle
    DBCC FREEPROCCACHE (0x06000E001C47E20540C17A84000000000000000000000000) -- plan handle in the ()
    
  13. You can then optimize the query by adding indexes or query hints if needed and then re-execute when ready to create a new execution plan.