SQL Server Performance Forum – Threads Archive

Analysis Services or PERFMON

I’ve been asked to provide the following statistics:
– CPU utilization on the server (average and max per hour)
– Memory utilization on the server (average and max per hour)
– Disk utilization (free space per volume)
– CPU utilization per database (average and max per hour for each of databases)
– Number of concurrent users per database (average and max per hour for each of databases)
– Number of total users per database (average and max per hour for each of databases)
– Database report showing the top 50 queries by frequency for each database per day
– Database report showing the top 50 queries by CPU time for each database per day Can I answer ALL the requests with PERFMON utility? What help can I get from Analysis Services? Thanks, CanadaDBA

1. use perfmon
2. use perfmon
3. use perfmon
4. permon may help, you may have to do some work yourself
5. perfmon
6. write your own script to query sysprocesses or use sp_who, schedule a job to run it as frequently as you need, capture to a table, analyze afterwards
7. Profiler
8. Profiler for # 7 and 8 I suggest use a profile to capture to an SQL Server table, with appropriate filters and columns, for easier analysis and calculation on your results. Analysis Services has nothing to do with what you’re after. I can provide more fulsome explanation of what I’m talking about if required. Tom Pullen
DBA, Oxfam GB

Thank you very much Tom. It’s complete as I was expecting. I’ll work on your suggestion on #6 but if you have example or anything else, let me know. I am a poor user of profiler! I have to start working with that ASAP.[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Can you hint me more, please. I can provide more fulsome explanation of what I’m talking about if required. Yes sure, please do it. <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by thomas 1. use perfmon 2. use perfmon 3. use perfmon 4. permon may help, you may have to do some work yourself 5. perfmon 6. write your own script to query sysprocesses or use sp_who, schedule a job to run it as frequently as you need, capture to a table, analyze afterwards 7. Profiler 8. Profiler for # 7 and 8 I suggest use a profile to capture to an SQL Server table, with appropriate filters and columns, for easier analysis and calculation on your results. Analysis Services has nothing to do with what you’re after. I can provide more fulsome explanation of what I’m talking about if required. Tom Pullen DBA, Oxfam GB <hr height="1" noshade id="quote"></blockquote id="quote"> CanadaDBA

For your perfmon counters I recommend logging to *.csv and using Excel to generate your MAX, AVERAGE etc values. It’s also good for graphing counters over time and comparing performance under load that varies over time. (For example, check out what your perf stats look like during overnight maintenance, it can be interesting). CPU, Memory and disk utilization are relatively straightforward counters to monitor and understand. I do have an example for #6 but it’s at work, and I’m at home. Really you could do this yourself. Look in BOL at the output of sp_who, i.e. which columns it generates and what datatypes each column has (it’s normally in a grid if you scroll down a bit). Now in a database (preferably not master!), create a table with this exact structure. Now, write a stored procedure that simply does INSERT INTO yoursp_whotable
EXECUTE sp_who Then, add this procedure as a scheduled task as frequently as you want. The lowest level of granularity in SQL Server is every minute, I believe, so if you want to do it more often than that you need to use some kind of programme you write yourself. Then.. you collect the sp_who output over a period then you can query this table to get the stats you want. use approprite WHERE clauses to filter by database or by STATUS, e.g. WHERE STATUS = ‘runnable’ to get concurrent users. Now, as far as profiler is concerned, there is plenty of top-quality material on this site and frequently in Brad’s weekly emails, and I’m not going to repeat it all. Have a look through the articles, tips, etc and do a search maybe. Use of Profiler is not difficult and it’s a very flexible tool, but it takes a bit of practice to get used to. Why don’t you give it a go and then ask any specific problems you have with it? Tom Pullen
DBA, Oxfam GB

Thank you Tom for wondeful tips. I created a stored procedure and scheduled it to get samples every 5 minutes. Is 5 minutes too short? What’s your idea about the SP?
CREATE PROCEDURE sp_GetSampleUsers AS declare @spidlowint,
@spidhighint [email protected] = 0,
@spidhigh = 32767 INSERT [MyDatabase].[dbo].[SQLwho]
(SampleTime, spid, ecid, status, loginname, hostname, blk, dbname, cmd )
selectGetDate(),
spid,
ecid,
status,
loginame=rtrim(loginame),
hostname,
blk=convert(char(5),blocked),
dbname = case
when dbid = 0 then null
when dbid <> 0 then db_name(dbid)
end
,cmd
from master.dbo.sysprocesses
where spid >= @spidlow and spid <= @spidhigh GO
CanadaDBA

5 mins sounds ok – though your picture of concurrent users will only be a snapshot. sp looks fine. Tom Pullen
DBA, Oxfam GB

Tom, Would you answer my question about the PerfMon? Thanks, CanadaDBA

Sorry, which question do you mean? Tom Pullen
DBA, Oxfam GB

It’s under seperate topic called: "Read a .blg PerfMon file" CanadaDBA

Referring to the SP, the SQLwho has the data now. Does the following query provid the right statistics?
SELECT Count(*) FROM SQLWho WHERE dbname = ‘MYDB’ AND Status = ‘runnable’ AND Day(SampleTime) = 18
What are "sleeping" statuses? Should I include them? What can I get from a report based on GROUP BY spid? What about the report based on GROUP BY hostname?

quote:Originally posted by thomas 5 mins sounds ok – though your picture of concurrent users will only be a snapshot. sp looks fine. Tom Pullen
DBA, Oxfam GB

CanadaDBA

If you’re looking for only concurrent, active processes, uses WHERE status =’runnable’ to eliminate sleeping (i.e. inactive) processes. If you want simply total number of processes, active or inactive, leave out this where clause. You may want also to exclude spids < 50, as these are always system spids.
Tom Pullen
DBA, Oxfam GB

Do you think: can I get something out of these data for my manager? I mean something that gives him some idea. His concern is that two more tools are going to be deployed on the server and he wants to know if the server has enough capacity.

quote:Originally posted by thomas If you’re looking for only concurrent, active processes, uses WHERE status =’runnable’ to eliminate sleeping (i.e. inactive) processes. If you want simply total number of processes, active or inactive, leave out this where clause. You may want also to exclude spids < 50, as these are always system spids.
Tom Pullen
DBA, Oxfam GB

CanadaDBA

If he’s worried about server capacity you need to look at how your current app(s) perform, what your perfmon stats look like, and assess if you consider there to be room for expansion on that basis.
Tom Pullen
DBA, Oxfam GB

What Columns and filters do you suggest to be set in Profiler to provide answers to the follwoings with less impact to production? – Number of total users per database (average and max per hour for each of databases)
– Database report showing the top 50 queries by frequency for each database per day
– Database report showing the top 50 queries by CPU time for each database per day
CanadaDBA

]]>