Extended Events
-
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_platformThere 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/ -
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/ -
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - 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/ -
I am curious - what fine tuning is required? Are you looking to limit the results in some way?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - 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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply