use perf

CREATE TABLE [dbo].[plancache](

      [sqlstmt] [nvarchar](max) NULL,

      [avg_physical_reads] [bigint] NULL,

      [avg_logical_reads] [bigint] NULL,

      [avg_logical_writes] [bigint] NULL,

      [avg_cpu_time_ms] [bigint] NULL,

      [avg_duration_ms] [bigint] NULL,

      [query_plan] [xml] NULL,

      [dbid] [smallint] NULL,

      [objectid] [int] NULL,

      [db] [nvarchar](128) NULL,

      [objname] [nvarchar](128) NULL,

      [execution_count] [bigint] NOT NULL,

      [total_physical_reads] [bigint] NOT NULL,

      [total_logical_reads] [bigint] NOT NULL,

      [total_elapsed_time_ms] [bigint] NULL,

      [total_logical_writes] [bigint] NOT NULL,

      [total_clr_time_ms] [bigint] NULL,

      [id] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY

)

insert into perf..plancache(sqlstmt,avg_physical_reads,avg_logical_reads,avg_logical_writes,avg_cpu_time_ms,avg_duration_ms,query_plan,dbid,

objectid,db,objname,execution_count,total_physical_reads,total_logical_reads,total_elapsed_time_ms,total_logical_writes,total_clr_time_ms)

select top 200

      substring(text, (statement_start_offset/2) + 1,

      (case when statement_end_offset = -1 then len(convert(nvarchar(max), text)) * 2 else statement_end_offset end

      - statement_start_offset)/2) as sqlstmt,

      total_physical_reads/execution_count as avg_physical_reads,

      total_logical_reads/execution_count as avg_logical_reads,

      total_logical_writes/execution_count as avg_logical_writes,

      total_worker_time/execution_count/1024 as avg_cpu_time_ms,

      total_elapsed_time/execution_count/1024 as avg_duration_ms,

      query_plan, 

      st.dbid, st.objectid, db_name(st.dbid) as db, object_name(st.objectid) as objname,

      execution_count, total_physical_reads, total_logical_reads, total_elapsed_time/1024 as total_elapsed_time_ms,

      total_logical_writes, total_clr_time/1024 as total_clr_time_ms

from sys.dm_exec_query_stats qs

cross apply

      sys.dm_exec_sql_text(sql_handle) st

cross apply

      sys.dm_exec_query_plan(plan_handle) qp

order by total_elapsed_time desc

select * from perf..plancache

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using System.Diagnostics;

namespace QueryPlan

{

    class Program

    {

        static void Main(string[] args)

        {

            //Wrap the code in one big try to catch all errors.

            try

            {

                //Display the help message.

                if (args.Length != 5)

                {

                    System.Console.WriteLine("Purpose: Display a graphical query plan from showplan xml data." + Environment.NewLine + "Usage:  QueryPlan.exe Server Database Table IdColumn RowIdValue" + Environment.NewLine + "Example:  QueryPlan.exe (local) perf plancache id 2");

                    return;

                }

                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = @"Server=" + args[0] + "; Database=" + args[1] + "; integrated security=SSPI";

                conn.Open();

                SqlCommand command = conn.CreateCommand();

                //Create a sql select statement using an XQuery to get the xml showplan data for the query plan you want.

                command.CommandText = @"select TOP 1 query_plan.query(

                'declare namespace ns=""http://schemas.microsoft.com/sqlserver/2004/07/showplan"";/')

                from " + args[2] +

                " where " + args[3] + "=" + args[4];

                SqlDataReader datareader = command.ExecuteReader();

                System.Text.StringBuilder builder = new System.Text.StringBuilder();

                //Read the xml data and put it in the stringbuilder.

                while (datareader.Read())

                {

                    SqlXml sqlxml = datareader.GetSqlXml(0);

                    builder.Append(sqlxml.Value);

                }

                string fileName = "queryplan.sqlplan";

                //Delete the file if it exists.

                if (System.IO.File.Exists(fileName))

                {

                    System.IO.File.Delete(fileName);

                }

                //Create the file.

                using (System.IO.FileStream fs = System.IO.File.Create(fileName, 1024))

                {

                    //Add the xml showplan housed in the stringbuilder to the file.

                    byte[] info = new System.Text.UTF8Encoding(true).GetBytes(builder.ToString());

                    fs.Write(info, 0, info.Length);

                    fs.Close();

                }

                //Create a new process which launches SSMS with the sqlplan file.

                Process process = new Process();

                process.StartInfo.FileName = "sqlwb.exe";

                process.StartInfo.Arguments = "-nosplash -d master -E queryplan.sqlplan";

                process.Start();

            } //end try

            catch (Exception e)

            {

                Console.WriteLine("{0} Caught exception.", e);

            }

            finally

            {

            }

        }

    }

}