编写Profiler查询

Question

我正在编写一个模拟Server事件探查器的查询，我的版本是Server 2008 --我没有Server，我需要它，此时我得到了这个查询：

Use master
Go

Alter procedure dbo.NS_sp_profilerQuery
    @dbName sysname='',
    @userName sysname=Null,
    @hostName sysname=Null
As
Begin
    --Step 1. Declaration of variables.
    Declare @idDb Int=DB_ID(@dbName);
    Declare @Query Nvarchar(Max);

    --Step 2. SETTING THE query.
    Select @Query = N'Select '+
    N'idProceso=syp.spid,basedatos=DB_NAME(syp.dbid),usuario=syp.loginame,cpu,hostname,espera=lastwaittype,[bloqueadoPor]=blocked,Query=(Select text from ::fn_get_sql(syp.sql_handle)) '+
    N'From master.dbo.sysprocesses syp '+
    N'Where 1=1 '+
    (case when @idDb is null then '' else 'and syp.dbid='+Ltrim(Rtrim(Str(@idDb))) end)+
    (case when @userName is null then '' else ' and Ltrim(Rtrim(syp.loginame))='+Quotename(Ltrim(Rtrim(@userName)),Char(39)) end)+
    (case when @hostName is null then '' else ' and Ltrim(Rtrim(syp.hostname))='+Quotename(Ltrim(Rtrim(@hostName)),Char(39)) end);

    --Step 3. Query and return data.
    Exec(@Query);
End
Go

这个查询在一个点上捕获了所有的东西，我的问题是:什么是捕捉在很长时间内发生的所有事情的最好方法？，做一个循环？

你有什么建议？

Answer 1

这个查询解决了我的需求，它工作了99%，我拍了快照到dbo.sysprocesses。

Use master
Go

Alter procedure dbo.NS_sp_profilerQuery
    @dbName sysname='',
    @userName sysname=Null,
    @hostName sysname=Null,
    @Seconds int
As
Begin

    Declare @SnapShots Table(
    idProceso int,
    idThread int,
    basedatos sysname, 
    usuario sysname, 
    cpu int, 
    hostname sysname, 
    espera sysname, 
    bloqueadoPor int, 
    Query Nvarchar(Max)
);

Declare @Profiler Table(
    idProceso int,
    idThread int,
    basedatos sysname, 
    usuario sysname, 
    cpu int, 
    hostname sysname, 
    espera sysname, 
    bloqueadoPor int, 
    Query Nvarchar(Max)
);

--Paso 1. Declaración de variables.
Declare @idDb Int=DB_ID(@dbName);
Declare @Query Nvarchar(Max);
Declare @HoraFinal Datetime=DateAdd(Second,@Seconds,Getdate());

While Getdate()<=@HoraFinal
Begin
    --Paso 2. Seteo de consulta.
    Select @Query = N'Select '+
    N'idProceso=syp.spid,idThread=syp.kpid,basedatos=DB_NAME(syp.dbid),usuario=syp.loginame,cpu,hostname,espera=lastwaittype,[bloqueadoPor]=blocked,Query=(Select text from ::fn_get_sql(syp.sql_handle)) '+
    N'From master.dbo.sysprocesses syp '+
    N'Where 1=1 '+
    (case when @idDb is null then '' else 'and syp.dbid='+Ltrim(Rtrim(Str(@idDb))) end)+
    (case when @userName is null then '' else ' and Ltrim(Rtrim(syp.loginame))='+Quotename(Ltrim(Rtrim(@userName)),Char(39)) end)+
    (case when @hostName is null then '' else ' and Ltrim(Rtrim(syp.hostname))='+Quotename(Ltrim(Rtrim(@hostName)),Char(39)) end);

    --Paso 3. Consulta y retorno de datos.
    Insert Into @SnapShots(idProceso,idThread,basedatos,usuario,cpu,hostname,espera,bloqueadoPor,Query)
    Exec(@Query);

    Insert Into @Profiler(idProceso,idThread,basedatos,usuario,cpu,hostname,espera,bloqueadoPor,Query)
    Select 
        sna.idProceso,sna.idThread,sna.basedatos,sna.usuario,sna.cpu,sna.hostname,sna.espera,sna.bloqueadoPor,sna.Query 
    From @SnapShots sna
    Left Join @Profiler pro
        On (sna.idProceso=pro.idProceso and sna.idThread=pro.idThread)
    Where pro.idProceso is null;

    Delete From @SnapShots;
End

Select 
    idProceso,idThread,basedatos,usuario,cpu,hostname,espera,bloqueadoPor,Query
From @Profiler;

End
Go

Answer 2

用这样的一个查询来模拟分析器是不可能的。您将只获得当前正在运行的查询的快照。如果您希望看到需要很长时间才能运行的查询，也许这对您来说已经足够好了，但在这种情况下，我建议您查看一下dmv sys.dm_exec_query_stats。这样，您就可以检查哪些查询占用了大量的资源或时间。

示例，包括查询计划：

-- List top 100 queries that has the highest average logical reads
select top (100) *
from sys.dm_exec_query_stats qs
  cross apply sys.dm_exec_query_plan(qs.plan_handle) p
order by cast(qs.total_logical_reads as float) / qs.execution_count desc;

有许多有趣的列要排序。

如果您真的想捕获所有已执行的查询，则应该查看扩展事件。您可以记录您想要的查询，例如稍后可以检查的文件或环形缓冲区。在设置它时需要做一些工作，但是正式的sql server文档中有关于如何设置它的好信息。