Basic SQL User Stats

Is there a tool, either MS or third party, that gives me a breakdown of user
statistics, such as how much of the CPU cycles and disk activity is
attributed to each user on SQL? We periodically experience massive
slowdowns on our system, and when I pull up performance monitor, the CPU
cycles and disk usages are spiking for minutes at a time.
I have configured the profiler tool to capture some information to a table,
and I have some queries that aggregate the information on a per user basis,
but I'm not sure how much to trust this info.
We evaluated a tool called Spotlight on SQL by Quest software, but I'm not
sure it showed a breakdown of CPU cycles per user. If it does, let me
now. - Thanks, DaveI've found SQL Profiler to be very helpful and accurate for doing analysis
like this...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Dave Slinn" <dslinn@.accesscomm.ca> wrote in message
news:%23S9ZH5TfDHA.3228@.tk2msftngp13.phx.gbl...
> Is there a tool, either MS or third party, that gives me a breakdown of
user
> statistics, such as how much of the CPU cycles and disk activity is
> attributed to each user on SQL? We periodically experience massive
> slowdowns on our system, and when I pull up performance monitor, the CPU
> cycles and disk usages are spiking for minutes at a time.
> I have configured the profiler tool to capture some information to a
table,
> and I have some queries that aggregate the information on a per user
basis,
> but I'm not sure how much to trust this info.
> We evaluated a tool called Spotlight on SQL by Quest software, but I'm not
> sure it showed a breakdown of CPU cycles per user. If it does, let me
> now. - Thanks, Dave
>|||Yes, Profiler does have the information I'm looking for, but is there a tool
out there that summarizes these numbers and then breaks it down by user
connection? Also, having a graph for each user displaying the CPU cycles
would be ideal.
Basically, I want to know if most of the work that SQL is doing is isolated
to a few specific users so we can track those users who put more stress on
the system than others (for example, administrative people vs customer
service people.)
If SQL Profiler is the best method, perhaps some advice on how to set it up
to collect this information would be beneficial.
- Dave
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:%23dePBKUfDHA.1648@.TK2MSFTNGP09.phx.gbl...
> I've found SQL Profiler to be very helpful and accurate for doing analysis
> like this...
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Dave Slinn" <dslinn@.accesscomm.ca> wrote in message
> news:%23S9ZH5TfDHA.3228@.tk2msftngp13.phx.gbl...
> > Is there a tool, either MS or third party, that gives me a breakdown of
> user
> > statistics, such as how much of the CPU cycles and disk activity is
> > attributed to each user on SQL? We periodically experience massive
> > slowdowns on our system, and when I pull up performance monitor, the CPU
> > cycles and disk usages are spiking for minutes at a time.
> >
> > I have configured the profiler tool to capture some information to a
> table,
> > and I have some queries that aggregate the information on a per user
> basis,
> > but I'm not sure how much to trust this info.
> >
> > We evaluated a tool called Spotlight on SQL by Quest software, but I'm
not
> > sure it showed a breakdown of CPU cycles per user. If it does, let me
> > now. - Thanks, Dave
> >
> >
>|||What I'm doing at the moment is using Analysis Services to do this analysis
on trace data gathered by rolling daily server side traces. If you keep the
number of events to a minimum the overhead is generally bearable on today's
hardware. Loading the trace into a staging database and cleaning it and then
populating fact and dimension tables allows you to easily create cubes that
allow you to very quickly spot the offenders :-) As an example the
dimensions I tend to use are login,database,event type,application and
drillthrough to TSQL statement level with CPU,Reads,Writes and duration as
measures. This makes it simple to slice + dice to get almost any view I'd
want on the data plus it lets me keep a much larger history available for
instant querying than if I left it in the default trace tables.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dave Slinn" <dslinn@.accesscomm.ca> wrote in message
news:ubTxwTVfDHA.1832@.TK2MSFTNGP09.phx.gbl...
Yes, Profiler does have the information I'm looking for, but is there a tool
out there that summarizes these numbers and then breaks it down by user
connection? Also, having a graph for each user displaying the CPU cycles
would be ideal.
Basically, I want to know if most of the work that SQL is doing is isolated
to a few specific users so we can track those users who put more stress on
the system than others (for example, administrative people vs customer
service people.)
If SQL Profiler is the best method, perhaps some advice on how to set it up
to collect this information would be beneficial.
- Dave
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:%23dePBKUfDHA.1648@.TK2MSFTNGP09.phx.gbl...
> I've found SQL Profiler to be very helpful and accurate for doing analysis
> like this...
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Dave Slinn" <dslinn@.accesscomm.ca> wrote in message
> news:%23S9ZH5TfDHA.3228@.tk2msftngp13.phx.gbl...
> > Is there a tool, either MS or third party, that gives me a breakdown of
> user
> > statistics, such as how much of the CPU cycles and disk activity is
> > attributed to each user on SQL? We periodically experience massive
> > slowdowns on our system, and when I pull up performance monitor, the CPU
> > cycles and disk usages are spiking for minutes at a time.
> >
> > I have configured the profiler tool to capture some information to a
> table,
> > and I have some queries that aggregate the information on a per user
> basis,
> > but I'm not sure how much to trust this info.
> >
> > We evaluated a tool called Spotlight on SQL by Quest software, but I'm
not
> > sure it showed a breakdown of CPU cycles per user. If it does, let me
> > now. - Thanks, Dave
> >
> >
>|||I would really love to know more about what youve done.. and not too sure
how you would like to communicate that..Would really love to see the design
of your fact and dimension tables , the events captured in profiler and
maybe the population of the fact and dimension tables...
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%239$ueBWfDHA.128@.tk2msftngp13.phx.gbl...
> What I'm doing at the moment is using Analysis Services to do this
analysis
> on trace data gathered by rolling daily server side traces. If you keep
the
> number of events to a minimum the overhead is generally bearable on
today's
> hardware. Loading the trace into a staging database and cleaning it and
then
> populating fact and dimension tables allows you to easily create cubes
that
> allow you to very quickly spot the offenders :-) As an example the
> dimensions I tend to use are login,database,event type,application and
> drillthrough to TSQL statement level with CPU,Reads,Writes and duration as
> measures. This makes it simple to slice + dice to get almost any view I'd
> want on the data plus it lets me keep a much larger history available for
> instant querying than if I left it in the default trace tables.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Dave Slinn" <dslinn@.accesscomm.ca> wrote in message
> news:ubTxwTVfDHA.1832@.TK2MSFTNGP09.phx.gbl...
> Yes, Profiler does have the information I'm looking for, but is there a
tool
> out there that summarizes these numbers and then breaks it down by user
> connection? Also, having a graph for each user displaying the CPU cycles
> would be ideal.
> Basically, I want to know if most of the work that SQL is doing is
isolated
> to a few specific users so we can track those users who put more stress on
> the system than others (for example, administrative people vs customer
> service people.)
> If SQL Profiler is the best method, perhaps some advice on how to set it
up
> to collect this information would be beneficial.
> - Dave
> "Brian Moran" <brian@.solidqualitylearning.com> wrote in message
> news:%23dePBKUfDHA.1648@.TK2MSFTNGP09.phx.gbl...
> > I've found SQL Profiler to be very helpful and accurate for doing
analysis
> > like this...
> >
> > --
> >
> > Brian Moran
> > Principal Mentor
> > Solid Quality Learning
> > SQL Server MVP
> > http://www.solidqualitylearning.com
> >
> >
> > "Dave Slinn" <dslinn@.accesscomm.ca> wrote in message
> > news:%23S9ZH5TfDHA.3228@.tk2msftngp13.phx.gbl...
> > > Is there a tool, either MS or third party, that gives me a breakdown
of
> > user
> > > statistics, such as how much of the CPU cycles and disk activity is
> > > attributed to each user on SQL? We periodically experience massive
> > > slowdowns on our system, and when I pull up performance monitor, the
CPU
> > > cycles and disk usages are spiking for minutes at a time.
> > >
> > > I have configured the profiler tool to capture some information to a
> > table,
> > > and I have some queries that aggregate the information on a per user
> > basis,
> > > but I'm not sure how much to trust this info.
> > >
> > > We evaluated a tool called Spotlight on SQL by Quest software, but I'm
> not
> > > sure it showed a breakdown of CPU cycles per user. If it does, let me
> > > now. - Thanks, Dave
> > >
> > >
> >
> >
>
>|||Jasper;
That's interesting. Precise (bought by Veritas a few months ago) seems to be
doing something quite similar to what you are doing. I understand that they
are not operating on the SQL Trace data, but on the data primarily sampled
from sysprocesses and fn_get_sql().
The advantage of sampling sysprocesses is that the performance impact is
small, and therefore you can afford to have it running all the time. This is
good because it gives you that comfort level, knowing that you can always go
back in time to review your performance problems. The disadvantage is that
it's sampling, thus not comprehensive. But I guess if you sample frequently
enough, you should be able to capture the serious offenders, and whatever
splip through are probably not that important.
Do you set up SQL Trace to feed data into your 'warehouse' all the time?
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e$CXePJgDHA.128@.tk2msftngp13.phx.gbl...
> I would really love to know more about what youve done.. and not too sure
> how you would like to communicate that..Would really love to see the
design
> of your fact and dimension tables , the events captured in profiler and
> maybe the population of the fact and dimension tables...
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:%239$ueBWfDHA.128@.tk2msftngp13.phx.gbl...
> > What I'm doing at the moment is using Analysis Services to do this
> analysis
> > on trace data gathered by rolling daily server side traces. If you keep
> the
> > number of events to a minimum the overhead is generally bearable on
> today's
> > hardware. Loading the trace into a staging database and cleaning it and
> then
> > populating fact and dimension tables allows you to easily create cubes
> that
> > allow you to very quickly spot the offenders :-) As an example the
> > dimensions I tend to use are login,database,event type,application and
> > drillthrough to TSQL statement level with CPU,Reads,Writes and duration
as
> > measures. This makes it simple to slice + dice to get almost any view
I'd
> > want on the data plus it lets me keep a much larger history available
for
> > instant querying than if I left it in the default trace tables.
> >
> > --
> > HTH
> >
> > Jasper Smith (SQL Server MVP)
> >
> > I support PASS - the definitive, global
> > community for SQL Server professionals -
> > http://www.sqlpass.org
> >
> > "Dave Slinn" <dslinn@.accesscomm.ca> wrote in message
> > news:ubTxwTVfDHA.1832@.TK2MSFTNGP09.phx.gbl...
> > Yes, Profiler does have the information I'm looking for, but is there a
> tool
> > out there that summarizes these numbers and then breaks it down by user
> > connection? Also, having a graph for each user displaying the CPU
cycles
> > would be ideal.
> >
> > Basically, I want to know if most of the work that SQL is doing is
> isolated
> > to a few specific users so we can track those users who put more stress
on
> > the system than others (for example, administrative people vs customer
> > service people.)
> >
> > If SQL Profiler is the best method, perhaps some advice on how to set it
> up
> > to collect this information would be beneficial.
> >
> > - Dave
> >
> > "Brian Moran" <brian@.solidqualitylearning.com> wrote in message
> > news:%23dePBKUfDHA.1648@.TK2MSFTNGP09.phx.gbl...
> > > I've found SQL Profiler to be very helpful and accurate for doing
> analysis
> > > like this...
> > >
> > > --
> > >
> > > Brian Moran
> > > Principal Mentor
> > > Solid Quality Learning
> > > SQL Server MVP
> > > http://www.solidqualitylearning.com
> > >
> > >
> > > "Dave Slinn" <dslinn@.accesscomm.ca> wrote in message
> > > news:%23S9ZH5TfDHA.3228@.tk2msftngp13.phx.gbl...
> > > > Is there a tool, either MS or third party, that gives me a breakdown
> of
> > > user
> > > > statistics, such as how much of the CPU cycles and disk activity is
> > > > attributed to each user on SQL? We periodically experience massive
> > > > slowdowns on our system, and when I pull up performance monitor, the
> CPU
> > > > cycles and disk usages are spiking for minutes at a time.
> > > >
> > > > I have configured the profiler tool to capture some information to a
> > > table,
> > > > and I have some queries that aggregate the information on a per user
> > > basis,
> > > > but I'm not sure how much to trust this info.
> > > >
> > > > We evaluated a tool called Spotlight on SQL by Quest software, but
I'm
> > not
> > > > sure it showed a breakdown of CPU cycles per user. If it does, let
me
> > > > now. - Thanks, Dave
> > > >
> > > >
> > >
> > >
> >
> >
> >
>