| Author |
Topic |
|
CanadaDBA
583 Posts |
 Posted - 2005-02-16 : 14:55:19
|
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,Canada DBA |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-02-16 : 15:53:35
|
| Analysis Services is not a tool for this. You would need SQL Profiler for the last two. PerfMon can be used to get the rest.Tara |
 |
|
|
CanadaDBA
583 Posts |
Posted - 2005-02-16 : 16:10:35
|
Thank you Tara; I haven't used Profiler. Do you think I can find the answer for those two easily? Any recommendation?quote:
Originally posted by tduggan
Analysis Services is not a tool for this. You would need SQL Profiler for the last two. PerfMon can be used to get the rest.Tara
Canada DBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-02-16 : 16:13:36
|
| Is there another DBA at your organization that can help you out with this tool? If not, then start playing with it. You need to get familiar with it if you are a DBA anyway as it is an essential tool for DBAs.Tara |
|
|
|
CanadaDBA
583 Posts |
Posted - 2005-02-17 : 12:40:52
|
Is there any good resources for Profiler except BOL?quote:
Originally posted by tduggan
Is there another DBA at your organization that can help you out with this tool? If not, then start playing with it. You need to get familiar with it if you are a DBA anyway as it is an essential tool for DBAs.Tara
Canada DBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-02-17 : 12:43:23
|
| I haven't used any other resources except other DBAs and just playing around with it. There are plenty of books though that discuss it.Tara |
|
|
|
CanadaDBA
583 Posts |
Posted - 2005-02-22 : 21:14:00
|
| I saved the results of sp_Who in a table every 5 minutes. Does the following query provid the right statistics?SELECT Count(*) FROM SQLWho WHERE dbname = 'MYDB' AND Status = 'runnable' AND Day(SampleTime) = 18 If I get the report based on "sleeping" statuses, what can I achive? Should I include it in the above report? What can I get from a report based on GROUP BY spid? What about the report based on GROUP BY hostname?Canada DBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-02-23 : 12:35:21
|
| What do you mean provide the right statistics? What are you trying to capture? Creating a report based on sleeping statuses isn't going to show you much as every connection is sleeping in between queries being run. sp_who isn't going to provide much for the report that you need.Tara |
|
|
|
CanadaDBA
583 Posts |
Posted - 2005-02-23 : 14:55:07
|
| So, I need to start working with Profiler. Do you think if I run it against production, it might impact performance?Canada DBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-02-23 : 14:58:42
|
| Yes it can impact performance. To minimize it, you would run the client on a separate machine, meaning not the database server. You also would filter out the data that you don't need to see and also include only those events that you are interested in.Tara |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-02-23 : 16:30:48
|
| >> Can I answer ALL the requests with PERFMON utility? What help can I get from Analysis Services?After You catch the data, Analysis Server might help You analyze it.But You would need to be familiar with both perfmon/tracing and analysis server+OLAP.What is the reason for this requirement,are there performance problems somewhere, are you distributing costs among application owners ???In short - What problem/requirement will these statistics solve ?rockmoose |
|
|
|
CanadaDBA
583 Posts |
Posted - 2005-02-24 : 15:18:48
|
The client? Do you mean I run the Profiler on my local computer which is connected to the server. Right? quote:
Originally posted by tduggan
Yes it can impact performance. To minimize it, you would run the client on a separate machine, meaning not the database server. You also would filter out the data that you don't need to see and also include only those events that you are interested in.Tara
Canada DBA |
|
|
|
CanadaDBA
583 Posts |
Posted - 2005-02-24 : 15:22:32
|
I have two tools with their databases with about 110 users. The company is going to add another tool/tools and I have been asked to find out if our server has enough capacity for that expansion. An architect asked me the above questions and I am trying to find out his requests. quote:
Originally posted by rockmoose
>> Can I answer ALL the requests with PERFMON utility? What help can I get from Analysis Services?After You catch the data, Analysis Server might help You analyze it.But You would need to be familiar with both perfmon/tracing and analysis server+OLAP.What is the reason for this requirement,are there performance problems somewhere, are you distributing costs among application owners ???In short - What problem/requirement will these statistics solve ?rockmoose
Canada DBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-02-24 : 15:26:14
|
quote:
Originally posted by farhadr
The client? Do you mean I run the Profiler on my local computer which is connected to the server. Right? quote:
Originally posted by tduggan
Yes it can impact performance. To minimize it, you would run the client on a separate machine, meaning not the database server. You also would filter out the data that you don't need to see and also include only those events that you are interested in.Tara
Any client, so yes your local computer would be fine.Canada DBA
Tara |
|
|
|
CanadaDBA
583 Posts |
Posted - 2005-02-26 : 12:29:34
|
| 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 dayCanada DBA |
|
|
|
|
Analysis Services or PerfMon