Extended Events

  • Michael L John

    One Orange Chip

    Points: 25946

    I have set up an extended event to capture the queries users may be running against the databases.  I'm using sqlserver.sp_statement_completed to capture the stored procs, and sqlserver.sql_statement_completed to capture the ad-hoc queries.

    It all works as intended, except it captures the "system" portion of the queries that is virtually impossible to filter out.

    This is an example of the results that I would like to filter out:

    select SERVERPROPERTY(N'servername')
    use [databasename]
    SELECT CAST(@UserOption & 1 AS bit) AS [DisableDefaultConstraintCheck], CAST(@UserOption & 2 AS bit) AS [ImplicitTransactions], CAST(@UserOption & 4 AS bit) AS [CursorCloseOnCommit], CAST(@UserOption & 8 AS bit) AS [AnsiWarnings], CAST(@UserOption & 16 AS bit) AS [AnsiPadding], CAST(@UserOption & 32 AS bit) AS [AnsiNulls], CAST(@UserOption & 64 AS bit) AS [AbortOnArithmeticErrors], CAST(@UserOption & 128 AS bit) AS [IgnoreArithmeticErrors], CAST(@UserOption & 256 AS bit) AS [QuotedIdentifier], CAST(@UserOption & 512 AS bit) AS [NoCount], CAST(@UserOption & 1024 AS bit) AS [AnsiNullDefaultOn], CAST(@UserOption & 2048 AS bit) AS [AnsiNullDefaultOff], CAST(@UserOption & 4096 AS bit) AS [ConcatenateNullYieldsNull], CAST(@UserOption & 8192 AS bit) AS [NumericRoundAbort], CAST(@UserOption & 16384 AS bit) AS [AbortTransactionOnError]
    select @UserOption=convert(int, c.value) from sys.configurations c where c.name='user options'
    SELECT SYSTEM_USER
    SET CONCAT_NULL_YIELDS_NULL ON
    SET ARITHABORT ON
    SET QUERY_GOVERNOR_COST_LIMIT 0
    SET TEXTSIZE 2147483647
    SET ANSI_NULLS ON
    SET ANSI_NULL_DFLT_ON ON
    SET ANSI_PADDING ON
    SET ANSI_WARNINGS ON
    SET CURSOR_CLOSE_ON_COMMIT OFF
    SET IMPLICIT_TRANSACTIONS OFF
    SET NOCOUNT OFF
    SET LOCK_TIMEOUT -1
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    SET ROWCOUNT 0
    SET DEADLOCK_PRIORITY NORMAL
    select @@spid
    select SERVERPROPERTY('ProductLevel')
    if @edition = N'SQL Azure'
    exec ('select CONVERT(nvarchar(40),CONNECTIONPROPERTY(''net_transport'')) as ConnectionProtocol')
    SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname);
    select N'Windows' as host_platform

    There are a few hundred more lines like this before I get to the actual query text that the user ran.

    The vast majority of these are being run using SSMS.

    How can I do this?

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Mike01

    SSChampion

    Points: 11284

    Can you go into the EE and filter out what you don't want?

     

     

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Mr. Brian Gale

    SSC-Insane

    Points: 23165

    Mike01, I think what Michael L John is looking for is what filters to put in place to strip out some of that "junk" data.

    A quick look at what he got, a lot of that would be hard to filter out as (as far as I know) there is no way to filter that useless "junk" out as to SQL, it is all just a query being run by that user.

    One filter you COULD do that SHOULD reduce a lot of that "junk" is to look at the number of rows read.  Running "use [master]" for example will read 0 rows and thus would get filtered out.  Downside is something like:

    select @UserOption=convert(int, c.value) from sys.configurations c where c.name='user options'

    still reads some rows and would not be filtered, and something like:

    SELECT 10+20

    reads 0 rows, but may want to be captured by you.  The last example is not something that is likely to be run against a SQL database, but an end user may grab 2 pieces of data, store them in variables and then add the 2 variables (for example) and that would read 0 rows.

  • Jeffrey Williams

    SSC Guru

    Points: 88652

    Instead of looking at sp_statement_completed and sql_statement_completed you should look at rpc_completed and sql_batch_completed.  The events you are currently looking at identify each statement in a stored procedure (sp_statement_completed) or every SQL statement in the batch (sql_statement_completed).

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Michael L John

    One Orange Chip

    Points: 25946

    Jeffrey Williams wrote:

    Instead of looking at sp_statement_completed and sql_statement_completed you should look at rpc_completed and sql_batch_completed.  The events you are currently looking at identify each statement in a stored procedure (sp_statement_completed) or every SQL statement in the batch (sql_statement_completed).

    Stumbling through this via trial and error, I actually created another one using these events.  It needs fine tuning, but I think it will work better.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Jeffrey Williams

    SSC Guru

    Points: 88652

    I am curious - what fine tuning is required?  Are you looking to limit the results in some way?

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Michael L John

    One Orange Chip

    Points: 25946

    Jeffrey Williams wrote:

    I am curious - what fine tuning is required?  Are you looking to limit the results in some way?

    There are filters for users, and there are still rows that I do not need.  For instance, there are many that are "SELECT @@SPID;", "SELECT SYSTEM_USER", "select @@trancount", and "SET ROWCOUNT 0 SET TEXTSIZE 2147483647 SET NOCOUNT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ARITHABORT ON SET LOCK_TIMEOUT -1 SET QUERY_GOVERNOR_COST_LIMIT 0 SET DEADLOCK_PRIORITY NORMAL SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET ANSI_NULLS ON SET ANSI_NULL_DFLT_ON ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET CURSOR_CLOSE_ON_COMMIT OFF SET IMPLICIT_TRANSACTIONS OFF SET QUOTED_IDENTIFIER ON".

    There are a couple client_app_name's that can be filtered out also.  Intellisense is very busy, as an example!

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

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

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