store showplan_all output

  • Does anyone know a good way to store the data that showplan_all generates into a database table? I would like to create some way to automate the generation of plans for all my stored procedures.

    Thanks,

    kevkaz

  • You can do so by running the queries from the front end e.g. vb 6.0.

    How I did was :

    1)Connected to the database of my choice.

    2)Executed the statement set showplan_on on the database.

    3)Then executed the stored procedure whose execution plan I need to store.

    4) This statement would return two recordsets.

    5)I set the pointer to the 2nd recordset and loop through it's records

    6)Here I get the execution plan.

    Tell me if this has helped u.

    I would be happy to submit you the code 🙂

    Pay Respect to People on your way up. For you will meet the same People on your way down.


    He who knows others is learned but the wise one is one who knows himself.

  • would be great to see this code... please post...

    Cheers..

    Prakash Heda

    Sr Consultant DBA

    Bangalore, India

    Chat: hedafriends@yahoo.com

    Prakash Heda
    Lead DBA Team - www.sqlfeatures.com
    Video sessions on Performance Tuning and SQL 2012 HA

  • Copy Paste his Code

    You Will need to have a Listbox1 and a text box on the form.Please do the required changes.

    Start

    Option Explicit

    Private Sub Command1_Click()

    Dim oCon As New Connection

    Dim oCon1 As New Connection

    Dim oRsStoredProcedure As New Recordset

    Dim oRsParameters As New Recordset

    Dim oRsPlan As New Recordset

    Dim oRsPlan1 As New Recordset

    Dim intParametersCnt As Integer

    Dim oCom As Command

    oCon.Open "server=Ind-mhp1d2k0201;database=ldap;uid=sa;pwd=;provider=sqloledb.1;"

    oCon.Execute "SET DATEFORMAT DMY"

    oCon.CursorLocation = adUseClient

    oRsStoredProcedure.Open "Select name from sysobjects where xtype='p' and category=0", oCon

    Do Until oRsStoredProcedure.EOF

    List1.AddItem oRsStoredProcedure.Fields("name").Value

    oCon.Execute "SET SHOWPLAN_ALL OFF"

    oRsParameters.Open "select specific_name,parameter_name,parameter_mode,data_type from information_schema.parameters where specific_catalog ='pubs' and specific_name='" & oRsStoredProcedure.Fields("name").Value & "'", oCon

    Set oCom = New Command

    Set oCom.ActiveConnection = oCon

    oCom.CommandType = adCmdStoredProc

    oCom.CommandText = oRsStoredProcedure.Fields("name").Value

    If oRsParameters.RecordCount > 0 Then

    oRsParameters.MoveFirst

    'Do Until oRsParameters.EOF

    For intParametersCnt = 0 To oRsParameters.RecordCount - 1

    oCom.Parameters.Append oCom.CreateParameter(oRsParameters("parameter_name"), GetDatatype(oRsParameters("data_type")), GetParameterMode(oRsParameters("parameter_mode")))

    If GetDatatype(oRsParameters("data_type")) = adVarChar Or GetDatatype(oRsParameters("data_type")) = adChar Then

    oCom.Parameters(intParametersCnt).Value = "A"

    ElseIf GetDatatype(oRsParameters("data_type").Value) = adDate Then

    oCom.Parameters(intParametersCnt).Value = Now()

    ElseIf GetDatatype(oRsParameters("data_type")) = adInteger Or _

    GetDatatype(oRsParameters("data_type")) = adBigInt Or _

    GetDatatype(oRsParameters("data_type")) = adSmallInt Or _

    GetDatatype(oRsParameters("data_type")) = adTinyInt Or _

    GetDatatype(oRsParameters("data_type")) = adDecimal Or _

    GetDatatype(oRsParameters("data_type")) = adNumeric Or _

    GetDatatype(oRsParameters("data_type")) = adDouble Then

    oCom.Parameters(intParametersCnt).Value = 1

    End If

    Next

    Text1.Text = Text1.Text & "Plan For " & oRsStoredProcedure.Fields("name") & vbCrLf

    oCon.Execute "SET SHOWPLAN_ALL ON"

    oCom.ActiveConnection = oCon

    Set oRsPlan = oCom.Execute()

    oRsPlan.Move 1

    Do Until oRsPlan.EOF

    Text1.Text = Text1.Text & oRsPlan.Fields(0).Value & vbCrLf

    oRsPlan.MoveNext

    Loop

    Text1.Text = Text1.Text & vbCrLf

    oCon.Execute "SET SHOWPLAN_ALL OFF"

    Else

    Text1.Text = Text1.Text & "Plan For " & oRsStoredProcedure.Fields("name") & vbCrLf

    oCon.Execute "SET SHOWPLAN_ALL ON"

    Set oCom.ActiveConnection = oCon

    Set oRsPlan = oCom.Execute

    oRsPlan.Move 1

    If Not oRsPlan Is Nothing Then

    Do Until oRsPlan.EOF

    Text1.Text = Text1.Text & oRsPlan.Fields(0).Value & vbCrLf

    oRsPlan.MoveNext

    Loop

    End If

    Text1.Text = Text1.Text & vbCrLf

    oCon.Execute "SET SHOWPLAN_ALL ON"

    End If

    oRsParameters.Close

    oRsStoredProcedure.MoveNext

    Loop

    End Sub

    Private Function GetDatatype(strInputType As String) As ADODB.DataTypeEnum

    Select Case UCase(strInputType)

    Case "INT"

    GetDatatype = adInteger

    Case "BIGINT"

    GetDatatype = adBigInt

    Case "SMALLINT"

    GetDatatype = adSmallInt

    Case "TINYINT"

    GetDatatype = adTinyInt

    Case "DECIMAL"

    GetDatatype = adDecimal

    Case "NUMERIC"

    GetDatatype = adNumeric

    Case "MONEY"

    GetDatatype = adDouble

    Case "SMALLMONEY"

    GetDatatype = adDouble

    Case "DATETIME"

    GetDatatype = adDate

    Case "SMALLDATETIME"

    GetDatatype = adDate

    Case "CHAR"

    GetDatatype = adChar

    Case "VARCHAR"

    GetDatatype = adVarChar

    End Select

    End Function

    Private Function GetParameterMode(strParameter As String) As ADODB.ParameterDirectionEnum

    Select Case UCase(strParameter)

    Case "IN"

    GetParameterMode = adParamInput

    Case "OUT"

    GetParameterMode = adParamOutput

    End Select

    End Function

    END

    Pay Respect to People on your way up. For you will meet the same People on your way down.


    He who knows others is learned but the wise one is one who knows himself.

  • Addendum

    The code will not function properly if the stored procedures refers any temporary tables (#).

    Pay Respect to People on your way up. For you will meet the same People on your way down.


    He who knows others is learned but the wise one is one who knows himself.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply