Audit table/index access

I inherited a rudimentary data warehouse and would like to gather data on
which tables and which indexes are being used. Queries to the warehouse are
typically issued via ODBC from an ACC2003 front-end.
I have chosen SQL profiler as the tool to identify object usage. I thought
ObjectOpened would provide information on access to tables and indexes,
however, using that (and only that) event produces no results. I have
stripped away all filters.
I initiate a select in query manager, the select returns results. The
profiler session logs no ObjectOpened events. It will log SQL:Statement
started.
Questions:
1) is this the correct tool to use to identify which tables and indexes are
being accessed?
2) am I using the correct event?
3) why no results
Thanks all
======================
JG
Systems/DBA Supervisor
Vermont Agency of Transportation
GIAC Security Certified
MCSE, CCNA
See if this article helps you:
http://www.sql-server-performance.co...on_english.asp
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Vihrea" <Vihrea@.discussions.microsoft.com> wrote in message
news:5562075F-C49C-4D94-9473-3E06EC82E965@.microsoft.com...
> I inherited a rudimentary data warehouse and would like to gather data on
> which tables and which indexes are being used. Queries to the warehouse
are
> typically issued via ODBC from an ACC2003 front-end.
> I have chosen SQL profiler as the tool to identify object usage. I thought
> ObjectOpened would provide information on access to tables and indexes,
> however, using that (and only that) event produces no results. I have
> stripped away all filters.
> I initiate a select in query manager, the select returns results. The
> profiler session logs no ObjectOpened events. It will log SQL:Statement
> started.
> Questions:
> 1) is this the correct tool to use to identify which tables and indexes
are
> being accessed?
> 2) am I using the correct event?
> 3) why no results
> Thanks all
> --
> ======================
> JG
> Systems/DBA Supervisor
> Vermont Agency of Transportation
> GIAC Security Certified
> MCSE, CCNA
>

Audit table/index access

I inherited a rudimentary data warehouse and would like to gather data on
which tables and which indexes are being used. Queries to the warehouse are
typically issued via ODBC from an ACC2003 front-end.
I have chosen SQL profiler as the tool to identify object usage. I thought
ObjectOpened would provide information on access to tables and indexes,
however, using that (and only that) event produces no results. I have
stripped away all filters.
I initiate a select in query manager, the select returns results. The
profiler session logs no ObjectOpened events. It will log SQL:Statement
started.
Questions:
1) is this the correct tool to use to identify which tables and indexes are
being accessed?
2) am I using the correct event?
3) why no results
Thanks all
--
======================
JG
Systems/DBA Supervisor
Vermont Agency of Transportation
GIAC Security Certified
MCSE, CCNASee if this article helps you:
http://www.sql-server-performance.c...ion_english.asp
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Vihrea" <Vihrea@.discussions.microsoft.com> wrote in message
news:5562075F-C49C-4D94-9473-3E06EC82E965@.microsoft.com...
> I inherited a rudimentary data warehouse and would like to gather data on
> which tables and which indexes are being used. Queries to the warehouse
are
> typically issued via ODBC from an ACC2003 front-end.
> I have chosen SQL profiler as the tool to identify object usage. I thought
> ObjectOpened would provide information on access to tables and indexes,
> however, using that (and only that) event produces no results. I have
> stripped away all filters.
> I initiate a select in query manager, the select returns results. The
> profiler session logs no ObjectOpened events. It will log SQL:Statement
> started.
> Questions:
> 1) is this the correct tool to use to identify which tables and indexes
are
> being accessed?
> 2) am I using the correct event?
> 3) why no results
> Thanks all
> --
> ======================
> JG
> Systems/DBA Supervisor
> Vermont Agency of Transportation
> GIAC Security Certified
> MCSE, CCNA
>

Audit All SQL Queries against all or specific databases

Hello,

I have a problem. I suspect a certain user in my company is using the SA account to log into Databases and run queries(query analyzer) to gather information this user is not suppose to view. The problem I have is this is an IT person so they know the SA password. How can I log specific or all queries run against all databases/tables/fields to find out exactly what this person is doing/seeing? Or if there is an alternative to find out this information?

Please advise?

Hi,

well that depends...on your way for data retrieval. If your only way for data retrieval are stored procedures, you could easily plugin another logging procedure in that code. If they can work with Select statement you will have to let the profiler run on some machine, capturing the SQL and Statement events with specifying additional attributes like the user_name and the host_name from where the user is connecting. That might get you a good information about the called statements. With putting a filter on the profiler you can narrow the event down to a database / HostName / Application etc.

HTH, Jens K. Suessmeyer.

http://www.sqlserver20095.de

|||

Hi,

Well they are only running these in Select statements. These are not stored procedures. So I am assuming the SQL profiler would work with capturing the data on the database / hostname /application, etc. If you have a good example, that would be great.

Please advise?

-K

|||Simply run profiler and select the RPC / STMT / SQL Events which the appropiate data you want to capture. Thats all.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Attention all DBAs JOINS VS SUB QUERIES

Fan of Sub's and I feel more comfortable doing them, I've been told they aren't as efficient as JOIN's by another developer. Is this true?

Also, does one or the other perform better on SQL Server vs. Oracle? I'm more seasoned as an Oracle developer and have learned to avoid JOIN's when ever possible. So I guess my real question is have I been led astray?

:o

Thanks in advance, DBA's Rule!Most sub-queries get optimized into joins anyway. I wouldn't worry about it much. Just FYI, SQL Server does not have anywhere close to the number of problems that Oracle has with joins, joins are quite safe and efficient in SQL Server.

-PatP|||I'm just getting my feet wet with Oracle. I had no idea JOINs were not efficient in Oracle. Why is that? (I mean why are they ineffecient, not why didn't I have any idea.)|||If you can do all the logic in the SELECT of the main query, a JOIN is faster.|||I'm just getting my feet wet with Oracle. I had no idea JOINs were not efficient in Oracle. Why is that? (I mean why are they ineffecient, not why didn't I have any idea.)The answer gets really complicated, but it centers around the way that Oracle in general, and PL/SQL in specific has developed over time.

Oracle has evolved over time to address the needs of certain key segments of the market, with the assumption that if those needs are met, then the rest of the market will follow those key segments.

One segment that Oracle has addressed well is the TPC testing community. Oracle as a company has traditionally been willing to go to almost any lengths to cooperate with/accomodate the TPC testing groups. Other vendors might be willing to provide similar access, but as far as I know Oracle is unique in this respect right now.

Oracle has also built a strong relationship with many *nix DBAs over the years. They've expanded their toolset (including PL/SQL) to address the needs of the DBA, even when those added features pose significant problems for query optimization. The net effect is to trade performance/scalability off in order to get DBA loyalty.

These decisions over time have strengthened Oracle's position in the market. Until recently, they had a defacto strangle hold on the *nix market. Oracle still has a practical lock on the TPC market.

The net result is that Oracle has made some shrewd decisions over time that have bought them market share, but makes the database engine a bit unstable if you aren't aware of the potential problems. This is why there is a relatively strong market for people that can rescue a crippled Oracle installation after inexperienced DBAs render it partially or completely non-functional.

-PatP|||some subqueries(correlated) usually mimic a outer join and can be more efficiently written that way
the performance hit you take is usually attributed to the referencing of the outer query in the inner query this creates a co-relation between the two and forces the query engine to retrive one input from the inner query and pass it to the outer query for evaluation.
this happens for each row in the initial input.
you can optimize these by using the EXISTS argument in the where clause of the outer query.
this does become a more dificult process if the subquery is located in the FROM clause as a derived table.

Joins are optimized more efficiently and you have a greater level of flexibility with the joins and the types of joins and ultimately the execution plans due in part to the optimizers ability to decide based on cost.
subqueries appear to be evaluated as independent operations and suffer because of it

i use whatever works at the time and if it appears correlated i will try to rewrite it using a join.
i try to limit my subquery use to utility issues like complex update and delete statements.|||Check this discussion (http://www.dbforums.com/t1003932.html) in respect to SUBs vs. JOINs.

As per Horacle, - it's strength is in the muscles of the HW it runs on. And as Pat mentioned in regards to *nix admins that ironically became Horacle DBA's, - it made an extra effort throughout the releases to adapt to the *nix admin frame of reference, - this is why cursors in Horacle really fly, because they made sure they are faster than even ANSI joins. Why? Because *nix admins (not all, but a few!) have hard time grasping the concept of set-based approach ;)|||Dare I cross-post this to the Oracle forum? :rolleyes:|||Uh-oh, I think I need a body guard...|||No, I think you'd need a leash and a muzzle, and they'd need a shark-cage. ;)|||Looks like my little query got the reponse of two veteran DBA's. Thanks you for the very useful replies. In a some what related thread I found this example useful too:

http://asktom.oracle.com/pls/ask/f?p=4950:8:11521813539481172785::NO::F4950_P8_DISP LAYID,F4950_P8_B:953229842074,Y

I'm an Oracle guy at heart learning the SQL Server way.

Thanks again for the replies. :)|||I have NEVER heard so much drivel in my life...well except for last night when I was talking to a Yankee fan...

An example that FLYs (Now where did I put the complicated ones...)

Set Pagesize 0
Set Linesize 2000
Set Serveroutput on

Spool c:\Temp\DisabledChild.csv

Select '"Empl Id","Employee Name","Dpnd Id","Dependant Name",'
||'"Medical (10)",'
||'"Dental (11)",'
||'"Vision Care (14)",'
||'"Domestic Partner Medical (15)",'
||'"Domestic Partner Dental (16)",'
||'"Domestic Partner Vision Care (17)",'
||'"LegalCare (1Z)",'
||'"Basic Group Life (20)",'
||'"Basic AD'||Chr(38)||'D (22)",'
||'"Health Care Reimbursement (60)",'
||'"Dependent Care Reimbursement (61)"'
As Unload_String
From Dual
Union All
select '"'||x.emplid||'","'||Empl_Name||'","'||x.dependent_benef||'","'||x.name
||'","'||PT10_Ind||'","'||PT11_Ind||'","'||PT14_Ind
||'","'||PT15_Ind||'","'||PT16_Ind||'","'||PT17_Ind
||'","'||PT1Z_Ind||'","'||PT20_Ind||'","'||PT22_Ind
||'","'||PT60_Ind||'","'||PT61_Ind||'"'
from
(select a.emplid, a.dependent_benef, a.name
from enr_dpnd_benef a
where disabled = 'Y'
and lastupdveh = 'W'
and relationship in ('D','GC','OC','S','SC')
and exists
((select 1
from enr_dpnd_benef_h h
where (disabled = 'N'
or disabled is Null)
and h.lastupdveh = 'C'
and a.emplid = h.emplid
and a.dependent_benef = h.dependent_benef)
union all
(select 1
from enr_dpnd_benef i
where i.new_rcd = 'Y'
and a.emplid = i.emplid
and a.dependent_benef = i.dependent_benef))) x
,(select name as Empl_Name, emplid
from enr_employ_data) y
,(select 'Y' as PT10_Ind, emplid, dependent_benef
from enr_partic_dpnd b
where b.plan_type = '10') pt10
,(select 'Y' as PT11_Ind, emplid, dependent_benef
from enr_partic_dpnd b
where b.plan_type = '11') pt11
,(select 'Y' as PT14_Ind, emplid, dependent_benef
from enr_partic_dpnd b
where b.plan_type = '14') pt14
,(select 'Y' as PT15_Ind, emplid, dependent_benef
from enr_partic_dpnd b
where b.plan_type = '15') pt15
,(select 'Y' as PT16_Ind, emplid, dependent_benef
from enr_partic_dpnd b
where b.plan_type = '16') pt16
,(select 'Y' as PT17_Ind, emplid, dependent_benef
from enr_partic_dpnd b
where b.plan_type = '17') pt17
,(select 'Y' as PT1Z_Ind, emplid, dependent_benef
from enr_partic_dpnd b
where b.plan_type = '1Z') pt1Z
,(select 'Y' as PT20_Ind, emplid, dependent_benef
from enr_partic_dpnd b
where b.plan_type = '20') pt20
,(select 'Y' as PT22_Ind, emplid, dependent_benef
from enr_partic_dpnd b
where b.plan_type = '22') pt22
,(select 'Y' as PT60_Ind, emplid, dependent_benef
from enr_partic_dpnd b
where b.plan_type = '60') pt60
,(select 'Y' as PT61_Ind, emplid, dependent_benef
from enr_partic_dpnd b
where b.plan_type = '61') pt61
where x.emplid = y.emplid(+)
and x.emplid = pt10.emplid(+)
and x.dependent_benef = pt10.dependent_benef(+)
and x.emplid = pt11.emplid(+)
and x.dependent_benef = pt11.dependent_benef(+)
and x.emplid = pt14.emplid(+)
and x.dependent_benef = pt14.dependent_benef(+)
and x.emplid = pt15.emplid(+)
and x.dependent_benef = pt15.dependent_benef(+)
and x.emplid = pt16.emplid(+)
and x.dependent_benef = pt16.dependent_benef(+)
and x.emplid = pt17.emplid(+)
and x.dependent_benef = pt17.dependent_benef(+)
and x.emplid = pt1Z.emplid(+)
and x.dependent_benef = pt1Z.dependent_benef(+)
and x.emplid = pt20.emplid(+)
and x.dependent_benef = pt20.dependent_benef(+)
and x.emplid = pt22.emplid(+)
and x.dependent_benef = pt22.dependent_benef(+)
and x.emplid = pt60.emplid(+)
and x.dependent_benef = pt60.dependent_benef(+)
and x.emplid = pt61.emplid(+)
and x.dependent_benef = pt61.dependent_benef(+)
;

Spool Off;|||I DID quantify my statement, didn't it?!