sqlserver使用存储过程跟踪SQL

为跟踪EF生成的SQL

/1.添加存储过程/

USE [master] GO /****** Object: StoredProcedure [dbo].[sp_perfworkload_trace_start] Script Date: 2017/7/6 19:48:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

create PROC [dbo].[sp_perfworkload_trace_start] @dbid AS INT, @tracefile AS NVARCHAR(254), @traceid AS INT OUTPUT AS -- Create a Queue DECLARE @rc AS INT; DECLARE @maxfilesize AS BIGINT;

SET @maxfilesize = 5;

EXEC @rc = sp_trace_create @traceid OUTPUT, 0, @tracefile, @maxfilesize, NULL IF (@rc != 0) GOTO error;

-- Client side File and Table cannot be scripted

-- Set the events DECLARE @on AS BIT; SET @on = 1; EXEC sp_trace_setevent @traceid, 10, 15, @on; EXEC sp_trace_setevent @traceid, 10, 8, @on; EXEC sp_trace_setevent @traceid, 10, 16, @on; EXEC sp_trace_setevent @traceid, 10, 48, @on; EXEC sp_trace_setevent @traceid, 10, 1, @on; EXEC sp_trace_setevent @traceid, 10, 17, @on; EXEC sp_trace_setevent @traceid, 10, 10, @on; EXEC sp_trace_setevent @traceid, 10, 18, @on; EXEC sp_trace_setevent @traceid, 10, 11, @on; EXEC sp_trace_setevent @traceid, 10, 12, @on; EXEC sp_trace_setevent @traceid, 10, 13, @on; EXEC sp_trace_setevent @traceid, 10, 14, @on; EXEC sp_trace_setevent @traceid, 45, 8, @on; EXEC sp_trace_setevent @traceid, 45, 16, @on; EXEC sp_trace_setevent @traceid, 45, 48, @on; EXEC sp_trace_setevent @traceid, 45, 1, @on; EXEC sp_trace_setevent @traceid, 45, 17, @on; EXEC sp_trace_setevent @traceid, 45, 10, @on; EXEC sp_trace_setevent @traceid, 45, 18, @on; EXEC sp_trace_setevent @traceid, 45, 11, @on; EXEC sp_trace_setevent @traceid, 45, 12, @on; EXEC sp_trace_setevent @traceid, 45, 13, @on; EXEC sp_trace_setevent @traceid, 45, 14, @on; EXEC sp_trace_setevent @traceid, 45, 15, @on; EXEC sp_trace_setevent @traceid, 41, 15, @on; EXEC sp_trace_setevent @traceid, 41, 8, @on; EXEC sp_trace_setevent @traceid, 41, 16, @on; EXEC sp_trace_setevent @traceid, 41, 48, @on; EXEC sp_trace_setevent @traceid, 41, 1, @on; EXEC sp_trace_setevent @traceid, 41, 17, @on; EXEC sp_trace_setevent @traceid, 41, 10, @on; EXEC sp_trace_setevent @traceid, 41, 18, @on; EXEC sp_trace_setevent @traceid, 41, 11, @on; EXEC sp_trace_setevent @traceid, 41, 12, @on; EXEC sp_trace_setevent @traceid, 41, 13, @on; EXEC sp_trace_setevent @traceid, 41, 14, @on;

-- Set the Filters DECLARE @intfilter AS INT; DECLARE @bigintfilter AS BIGINT; -- Application name filter EXEC sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Server Profiler%'; -- Database ID filter EXEC sp_trace_setfilter @traceid, 3, 0, 0, @dbid;

-- Set the trace status to start EXEC sp_trace_setstatus @traceid, 1;

-- Print trace id and file name for future references PRINT 'Trce ID: ' + CAST(@traceid AS VARCHAR(10))

  • ', Trace File: ''' + @tracefile + '''';

GOTO finish;

error: PRINT 'Error Code: ' + CAST(@rc AS VARCHAR(10));

finish:

/2.创建文件跟踪/

declare @dbid int; declare @traceid int; SET @dbid = DB_ID(); EXEC dbo.sp_perfworkload_trace_start @dbid = @dbid, @tracefile = 'D:\sqltrc\20170706_6', @traceid = @traceid OUTPUT;

/3.写入trc文件/

EXEC sp_trace_setstatus 2, 0; EXEC sp_trace_setstatus 2, 2;

/4. 查询日志/

SELECT * FROM sys.fn_trace_gettable( 'D:\sqltrc\20170706_6.trc', NULL) AS t ORDER BY StartTime desc

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏跟着阿笨一起玩NET

treeview 绑定文件夹和文件

611
来自专栏跟着阿笨一起玩NET

ASP.NET常用导出Excel方法汇总

本文转载:http://mattberseth.com/blog/2007/04/export_gridview_to_excel_1.html

2872
来自专栏菩提树下的杨过

遍历文件夹所有文件(示例)

//要引用 using System.Collections.Specialized; public StringCollection GetAllFile...

2389
来自专栏菩提树下的杨过

正则表达式提取指定内容

using System; using System.Text.RegularExpressions; public partial class _De...

1927
来自专栏C#

将文本文件的内容存储在DataSet中的方法总结

    项目中比较多的会对文件进行操作,例如文件的上传下载,文件的压缩和解压等IO操作。在.NET项目中较多的会使用DataSet,DataTable进行...

1938
来自专栏james大数据架构

Excel导入导出数据库01

主要分为两部份 1.Excel操作类 1 引入 2 using System.Data.OleDb; 3 using System.IO; 4 ...

24510
来自专栏跟着阿笨一起玩NET

C#巧用Excel模版变成把Table打印出来

转载:http://blog.csdn.net/gwblue/article/details/38865525

2152
来自专栏.Net移动开发

.Net语言 APP开发平台——Smobiler学习日志:用Gridview控件设计较复杂的表单

新创建MobileForm项,并命名为MessageShow3,并拖入一个Label控件,如图1

1644
来自专栏菩提树下的杨过

MSDN官方的ASP.Net异步页面的经典示例代码

示例1.演示异步获取一个网址的内容,处理后显示在OutPut这一Label上 using System; using System.Web; using S...

2305
来自专栏互联网开发者交流社区

ASP.NET部分代码示例

1142

扫码关注云+社区