Main Page | Report this Page
Computers Forum Index  »  Computer - Databases - MS SQL Server  »  how can i figure out what is causing increased SQL...
Page 1 of 1    

how can i figure out what is causing increased SQL...

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
 
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
 
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
 
 
Page 1 of 1    
All times are GMT
The time now is Thu Nov 26, 2009 9:52 am