|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Select on a table can be audited?Hi,
I have requirement to audit all the select that had happened to particular set of tables. But I am not clear which apporach should I follow. Currently I am trying through profiler and tracing stmt:completed event in the profiler and then try to parse that query to find whether it was a select query and find out the tables in that query. Is there any neat or cleaner approach to audit Select statements? Thanks Pushkar You can catch the execution plan. This way, you don't miss if the table is access through a view or
stored procedure. Also, some data can be returned and then the operations cancelled, meaning no statement completed. Just make sure you filter wisely. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Pushkar" <pushkartiw***@gmail.com> wrote in message news:uXjAUAAlFHA.2444@tk2msftngp13.phx.gbl... I have requirement to audit all the select that had happened to particular set of tables. But I am Hi, not clear which apporach should I follow. Currently I am trying through profiler and tracing stmt:completed event in the profiler and then try to parse that query to find whether it was a select query and find out the tables in that query. Is there any neat or cleaner approach to audit Select statements? Thanks Pushkar Thanks, I think this will solve my problem.
Pushkar Show quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:ehhxJMAlFHA.1372@TK2MSFTNGP10.phx.gbl... > You can catch the execution plan. This way, you don't miss if the table is > access through a view or stored procedure. Also, some data can be returned > and then the operations cancelled, meaning no statement completed. Just > make sure you filter wisely. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > Blog: http://solidqualitylearning.com/blogs/tibor/ > > > "Pushkar" <pushkartiw***@gmail.com> wrote in message > news:uXjAUAAlFHA.2444@tk2msftngp13.phx.gbl... > Hi, > > I have requirement to audit all the select that had happened to particular > set of tables. But I am not clear which apporach should I follow. > Currently I am trying through profiler and tracing stmt:completed event in > the profiler and then try to parse that query to find whether it was a > select query and find out the tables in that query. > > Is there any neat or cleaner approach to audit Select statements? > > Thanks > Pushkar > > Hi,
I have one concern in collecting trace of Execution Plan that whether this will hit performance of my SQL Server. Please let me know which approach is better, collecting Events of Stmt:Starting event and then parse the query to find out if there is any select query or collect the Execution Plan event. Thanks Pushkar Show quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:ehhxJMAlFHA.1372@TK2MSFTNGP10.phx.gbl... > You can catch the execution plan. This way, you don't miss if the table is > access through a view or stored procedure. Also, some data can be returned > and then the operations cancelled, meaning no statement completed. Just > make sure you filter wisely. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > Blog: http://solidqualitylearning.com/blogs/tibor/ > > > "Pushkar" <pushkartiw***@gmail.com> wrote in message > news:uXjAUAAlFHA.2444@tk2msftngp13.phx.gbl... > Hi, > > I have requirement to audit all the select that had happened to particular > set of tables. But I am not clear which apporach should I follow. > Currently I am trying through profiler and tracing stmt:completed event in > the profiler and then try to parse that query to find whether it was a > select query and find out the tables in that query. > > Is there any neat or cleaner approach to audit Select statements? > > Thanks > Pushkar > > I haven't measured these. Should be easy to do, though, if you have some load you can put on the
system, -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Pushkar" <pushkartiw***@gmail.com> wrote in message news:uOUgo4ElFHA.2156@TK2MSFTNGP14.phx.gbl... > Hi, > I have one concern in collecting trace of Execution Plan that whether this will hit performance of > my SQL Server. > > Please let me know which approach is better, collecting Events of Stmt:Starting event and then > parse the query to find out if there is any select query or collect the Execution Plan event. > > > Thanks > Pushkar > > > > "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message > news:ehhxJMAlFHA.1372@TK2MSFTNGP10.phx.gbl... >> You can catch the execution plan. This way, you don't miss if the table is access through a view >> or stored procedure. Also, some data can be returned and then the operations cancelled, meaning >> no statement completed. Just make sure you filter wisely. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> Blog: http://solidqualitylearning.com/blogs/tibor/ >> >> >> "Pushkar" <pushkartiw***@gmail.com> wrote in message >> news:uXjAUAAlFHA.2444@tk2msftngp13.phx.gbl... >> Hi, >> >> I have requirement to audit all the select that had happened to particular set of tables. But I >> am not clear which apporach should I follow. >> Currently I am trying through profiler and tracing stmt:completed event in the profiler and then >> try to parse that query to find whether it was a select query and find out the tables in that >> query. >> >> Is there any neat or cleaner approach to audit Select statements? >> >> Thanks >> Pushkar >> >> > > Pushkar wrote:
> Hi, If you have a specific set of tables, have you considered driving all > I have one concern in collecting trace of Execution Plan that whether > this will hit performance of my SQL Server. > > Please let me know which approach is better, collecting Events of > Stmt:Starting event and then parse the query to find out if there is > any select query or collect the Execution Plan event. queries to these tables through a limited set of stored procedures? If so, you can perform the auditing there and avoid the tedious task of parsing the trace output. If you use a trace, you may want to consider putting filter conditions on the TextData for the table names in question. If you use the execution plan event, you can wrap the filter conditions in brackets since all execution plan objects are generated fully qualified. > If you use a trace, you may want to consider putting filter conditions on the TextData for the Just be careful with the square brackets. Profiler handles LIKE in the same way as the execution > table names in question. If you use the execution plan event, you can wrap the filter conditions > in brackets since all execution plan objects are generated fully qualified. engine does regarding square brackets. This means that you need to do something like below to not catch every event containing any of the letters in the string inside the square brackets you specify: EXEC sp_trace_setfilter @traceid = @TraceID ,@columnid = 1 --TextData ,@logical_operator = 1 --OR ,@comparison_operator = 6 --LIKE ,@value = N'%[[]authors]%' -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "David Gugick" <david.gugick-nospam@quest.com> wrote in message news:OzUA5OHlFHA.4028@TK2MSFTNGP10.phx.gbl... > Pushkar wrote: >> Hi, >> I have one concern in collecting trace of Execution Plan that whether >> this will hit performance of my SQL Server. >> >> Please let me know which approach is better, collecting Events of >> Stmt:Starting event and then parse the query to find out if there is >> any select query or collect the Execution Plan event. > > If you have a specific set of tables, have you considered driving all queries to these tables > through a limited set of stored procedures? If so, you can perform the auditing there and avoid > the tedious task of parsing the trace output. > > If you use a trace, you may want to consider putting filter conditions on the TextData for the > table names in question. If you use the execution plan event, you can wrap the filter conditions > in brackets since all execution plan objects are generated fully qualified. > > > -- > David Gugick > Quest Software > www.imceda.com > www.quest.com Hi,
I am tracing Show plan all event of profiler. Can some one let me know the list of logical operations performed in case of select of table. Currently I am able to figure out that whenever there is a select there will a logical operation corresponding to one of three operations : Table Scan, Index Scan, Clustered Index Scan. Is there any other operation that corresponds for select on table? Is this list is complete? I also want to know about Index Seek operation? Thanks Pushkar Show quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:e6YZk6TlFHA.3552@TK2MSFTNGP10.phx.gbl... >> If you use a trace, you may want to consider putting filter conditions on >> the TextData for the table names in question. If you use the execution >> plan event, you can wrap the filter conditions in brackets since all >> execution plan objects are generated fully qualified. > > Just be careful with the square brackets. Profiler handles LIKE in the > same way as the execution engine does regarding square brackets. This > means that you need to do something like below to not catch every event > containing any of the letters in the string inside the square brackets you > specify: > > EXEC sp_trace_setfilter > @traceid = @TraceID > ,@columnid = 1 --TextData > ,@logical_operator = 1 --OR > ,@comparison_operator = 6 --LIKE > ,@value = N'%[[]authors]%' > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > Blog: http://solidqualitylearning.com/blogs/tibor/ > > > "David Gugick" <david.gugick-nospam@quest.com> wrote in message > news:OzUA5OHlFHA.4028@TK2MSFTNGP10.phx.gbl... >> Pushkar wrote: >>> Hi, >>> I have one concern in collecting trace of Execution Plan that whether >>> this will hit performance of my SQL Server. >>> >>> Please let me know which approach is better, collecting Events of >>> Stmt:Starting event and then parse the query to find out if there is >>> any select query or collect the Execution Plan event. >> >> If you have a specific set of tables, have you considered driving all >> queries to these tables through a limited set of stored procedures? If >> so, you can perform the auditing there and avoid the tedious task of >> parsing the trace output. >> >> If you use a trace, you may want to consider putting filter conditions on >> the TextData for the table names in question. If you use the execution >> plan event, you can wrap the filter conditions in brackets since all >> execution plan objects are generated fully qualified. >> >> >> -- >> David Gugick >> Quest Software >> www.imceda.com >> www.quest.com >  |
|||||||||||||||||||||||
Other interesting topics