August 20, 2003 at 1:38 pm
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
August 20, 2003 at 10:55 pm
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.
August 27, 2003 at 2:25 am
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
August 27, 2003 at 6:29 am
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.
August 27, 2003 at 11:16 pm
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply