 |
|
| Computers Forum Index » Computer - Databases - MS SQL Server » how can i figure out what is causing increased SQL... |
|
Page 1 of 1 |
|
| Author |
Message |
| Tim... |
Posted: Thu Oct 08, 2009 4:14 pm |
|
|
|
Guest
|
hi,
recently we installed some software updates and noticed that on the
average the values of the performance counter
PerfCounter = [\SQLServer:General Statistics()\User Connections]
increased roughly 30%. i'm trying to figure out how to measure what
might have changed (and whether i need to worry). nothing else we
measure with performance counters and graph with nagios seems to have
changed significantly (cpu, memory, full scans, batch requests, ...).
to get a handle on CPU, reads, or writes, for example, i take brief
profiler snapshots (sometimes restricted in some way, e.g. to queries
running longer than half a second), then store these to a table,
calculate a "factor" i can group by from the "TextData" of the
operations and then look at statistics such as
select top 25
[Factor], sum([CPU]) sum_cpu
FROM <table stored with profiler>
group by [Factor]
order by sum_cpu DESC
but how can i get a handle on user connections?
i'd appreciate any useful suggestions.
cheers,
Tim |
|
|
| Back to top |
|
|
|
| Erland Sommarskog... |
Posted: Thu Oct 08, 2009 9:56 pm |
|
|
|
Guest
|
Tim (t.hanson at (no spam) faz.de) writes:
Quote: recently we installed some software updates and noticed that on the
average the values of the performance counter
PerfCounter = [\SQLServer:General Statistics()\User Connections]
increased roughly 30%. i'm trying to figure out how to measure what
might have changed (and whether i need to worry). nothing else we
measure with performance counters and graph with nagios seems to have
changed significantly (cpu, memory, full scans, batch requests, ...).
In that case, I would not worry that much. It could just be a change
in application behaviour.
Quote: to get a handle on CPU, reads, or writes, for example, i take brief
profiler snapshots (sometimes restricted in some way, e.g. to queries
running longer than half a second), then store these to a table,
calculate a "factor" i can group by from the "TextData" of the
operations and then look at statistics such as
select top 25
[Factor], sum([CPU]) sum_cpu
FROM <table stored with profiler
group by [Factor]
order by sum_cpu DESC
but how can i get a handle on user connections?
There are Audit events for login and logout, and the logout event
has Duration, but I believe also CPU, Reads and Writes statistics.
--
Erland Sommarskog, SQL Server MVP, esquel at (no spam) sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |
|
|
| Back to top |
|
|
|
| Tim... |
Posted: Fri Oct 09, 2009 5:31 am |
|
|
|
Guest
|
Quote: There are Audit events for login and logout, and the logout event
has Duration, but I believe also CPU, Reads and Writes statistics.
thanks, Erland! you're right: i should have thought of those: i
usually remove them first thing when i start a profile because they
don't contain SQL and always seemed like noise to me. i guess for this
investigation i should include them. i'll try that and see how things
go.
--Tim |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Thu Nov 26, 2009 9:52 am
|
|