sql trace profile笔记

来源：互联网发布：淘宝上架后用不用管了编辑：程序博客网时间：2024/05/21 15:40

for more detailed info, you can refer to the bloghttp://technet.microsoft.com/en-us/library/cc293613.aspx

Note:the duration is micro-second, not milli-second

We can create template via

Forstored procedure investigation, we just check the row, it is enough.

After running, and stop it, then save it to a file.
Export the file.
The file is like this:

/****************************************************/

/*Created by: SQL Server 2012 Profiler */

/*Date: 12/18/2013 02:14:59 AM */

/****************************************************/

--Create a Queue

declare@rc int

declare@TraceID int

declare@maxfilesize bigint

set@maxfilesize = 256

--Please replace the text InsertFileNameHere, with an appropriate

--filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension

--will be appended to the filename automatically. If you are writing from

--remote server to local drive, please use UNC path and make sure server has

--write access to your network share

exec@rc = sp_trace_create @TraceID output,2, N'R:\Backup\100KAssetsProfileTrace\TB8SGLog', @maxfilesize, NULL

if(@rc != 0) goto error

--Client side File and Table cannot be scripted

--Set the events

declare@on bit

set@on = 1

execsp_trace_setevent @TraceID, 10, 1, @on

execsp_trace_setevent @TraceID, 10, 9, @on

execsp_trace_setevent @TraceID, 10, 2, @on

execsp_trace_setevent @TraceID, 10, 10, @on

execsp_trace_setevent @TraceID, 10, 6, @on

execsp_trace_setevent @TraceID, 10, 11, @on

execsp_trace_setevent @TraceID, 10, 12, @on

execsp_trace_setevent @TraceID, 10, 13, @on

execsp_trace_setevent @TraceID, 10, 14, @on

execsp_trace_setevent @TraceID, 10, 15, @on

execsp_trace_setevent @TraceID, 10, 16, @on

execsp_trace_setevent @TraceID, 10, 17, @on

execsp_trace_setevent @TraceID, 10, 18, @on

--Set the Filters

declare@intfilter int

declare@bigintfilter bigint

execsp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler -8d9fb860-75a9-4bf8-9787-e137484adf59'

--Set the trace status to start

execsp_trace_setstatus @TraceID, 1

--display trace id for future references

selectTraceID=@TraceID

gotofinish

error:

selectErrorCode=@rc

finish:

We can add it to a job.

create database[SQLProfilerTraceDB]

use[SQLProfilerTraceDB]

SELECT* INTO baibai

FROM::fn_trace_gettable('E:\1105.trc', default)

insert intoBranchProfile0109_1 select * FROM::fn_trace_gettable('R:\BranchProfileTest_Trace\BranchProfile0109_3.trc',default)

Deadlock found:

BranchDB.baibai5

BDB:S:\TraceFiles\TraceFile2_temp_2.trc

<deadlock-list>

<deadlockvictim="process20fe2acf8">

<process-list>

<frameprocname="BranchDB.dbo.sproc_delete_asset_attributes"line="14" stmtstart="544" stmtend="712"sqlhandle="0x03000700febb614eb6d11900aba1000001000000000000000000000000000000000000000000000000000000">

DELETEcategoryassetmap

WHERE assetpackageid= @assetpackageid

-- removegenres </frame>

</executionStack>

Proc [Database Id =7 Object Id = 1315027966] </inputbuf>

</process>

<frameprocname="BranchDB.dbo.sproc_update_version" line="5"stmtstart="322" stmtend="416"sqlhandle="0x03000700b3e4122750d31900aba1000001000000000000000000000000000000000000000000000000000000">

DELETE FROMVersion WITH (TABLOCKX,HOLDLOCK) </frame>

<frameprocname="BranchDB.dbo.sproc_delete_asset_package_V_2_5"line="188" stmtstart="13298" stmtend="13362"sqlhandle="0x0300070070044a50c3d11900aba1000001000000000000000000000000000000000000000000000000000000">

EXECdbo.sproc_update_version </frame>

<frameprocname="BranchDB.dbo.sproc_delete_asset_package_wrapper_V_2_5"line="40" stmtstart="3234" stmtend="3352"sqlhandle="0x03000700e24c3252cdd11900aba1000001000000000000000000000000000000000000000000000000000000">

EXECdbo.sproc_delete_asset_package_V_2_5 @assetpackageid </frame>

</executionStack>

Proc [Database Id =7 Object Id = 1379028194] </inputbuf>

</process>

</process-list>

<resource-list>

<keylockhobtid="72057598129602560" dbid="7"objectname="BranchDB.dbo.categoryassetmap"indexname="PK_categoryassetmap" id="lock26801a600"mode="X" associatedObjectId="72057598129602560">

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</keylock>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</objectlock>

</resource-list>

</deadlock>

</deadlock-list>

<deadlock-list>

<deadlockvictim="process20fe2acf8">

<process-list>

DELETEcategoryassetmap

WHERE assetpackageid= @assetpackageid

-- removegenres </frame>

</executionStack>

Proc [Database Id =7 Object Id = 1315027966] </inputbuf>

</process>

<frameprocname="BranchDB.dbo.sproc_update_version" line="5"stmtstart="322" stmtend="416"sqlhandle="0x03000700b3e4122750d31900aba1000001000000000000000000000000000000000000000000000000000000">

DELETE FROMVersion WITH (TABLOCKX,HOLDLOCK) </frame>

EXECdbo.sproc_update_version </frame>

EXECdbo.sproc_delete_asset_package_V_2_5 @assetpackageid </frame>

</executionStack>

Proc [Database Id =7 Object Id = 1379028194] </inputbuf>

</process>

</process-list>

<resource-list>

<keylockhobtid="72057598129602560" dbid="7"objectname="BranchDB.dbo.categoryassetmap"indexname="PK_categoryassetmap" id="lock26801a600"mode="X" associatedObjectId="72057598129602560">

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</keylock>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</objectlock>

</resource-list>

</deadlock>

</deadlock-list>

SGDB:

R:\Backup\100KAssetsProfileTrace\tracefile2_temp_3.trc

execsp_trace_setstatus 2, 0 --to stop the trace

execsp_trace_setstatus 2, 2 --to stop the trace and remove the trace definition

0 0