does sql server collect stats on full table scans?

Hi all. Anyone know if sql server collects stats on how many full
table scans a table gets and if so how do I get at those stats? Trying
to track down poorly indexed tables / processes and I am guessing that
sql server does have this data secreted away somewhere much like my
db2 and informix databases do.On 20 Oct 2004 06:41:51 -0700, sumGirl wrote:

> Hi all. Anyone know if sql server collects stats on how many full
> table scans a table gets and if so how do I get at those stats? Trying
> to track down poorly indexed tables / processes and I am guessing that
> sql server does have this data secreted away somewhere much like my
> db2 and informix databases do.

There is a performance counter under the "SQL Server:Access Methods"
category called "Full Scans/sec". Unfortunately nothing cumulative as far
as I can see. You could do a trace against your database, set an alert for
Full Scans/sec > 0 and see what was happening at that time.

Does MS SQLServer support auditing in the way that you can for Sybase?

I know that there are tools like Lumigent, but an wondering about the
internal facilities to track events such as table creation, security
operations (add login, add role), and such.

Under Sybase, there is a set of procedures that permit you to set
theses events and to record the results for later extraction and
analysis.

The Profiler seems to have a lot of the same functionality but this
appears to be more along the lines of running a monitor.

Can the events be tracked without Profiler running?

Can the events being tracked be recorded in the system of SQLServer
logs?byrocat (bdealhoy@.sympatico.ca) writes:
> I know that there are tools like Lumigent, but an wondering about the
> internal facilities to track events such as table creation, security
> operations (add login, add role), and such.
> Under Sybase, there is a set of procedures that permit you to set
> theses events and to record the results for later extraction and
> analysis.
> The Profiler seems to have a lot of the same functionality but this
> appears to be more along the lines of running a monitor.
> Can the events be tracked without Profiler running?

Yes. But I am not going to say that it is particular appealing.

The good news is that in SQL Server 2005, currently in beta, you can
define triggers on database and server level, which makes this sort of
auditing a lot more reasonable to do.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland, would you haoppen to have an URL to a Technote or soemthing
similar that lays out the procedures for setting auditing of events
other than logins?|||byrocat (bdealhoy@.sympatico.ca) writes:
> Erland, would you haoppen to have an URL to a Technote or soemthing
> similar that lays out the procedures for setting auditing of events
> other than logins?

You mean with Profiler? No, I don't have any reference any such texts.

You can look up C2 auditing in Books Online, but you will not find much.
There is a reference to a "C2 Administrator's and User's Security Guide",
but there is no link.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"byrocat" <bdealhoy@.sympatico.ca> wrote in message
news:1123616848.996208.232970@.o13g2000cwo.googlegr oups.com...
> Erland, would you haoppen to have an URL to a Technote or soemthing
> similar that lays out the procedures for setting auditing of events
> other than logins?

See sp_trace_create (and related procs) in Books Online. I agree with Erland
that it's not always perfect, but you can trace anything that Profiler can
trace, which obviously extends beyond just login events.

Simon