Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CanadaDBA
583 Posts |
 Posted - 2004-09-09 : 10:08:09
|
The following data comes from my server for a period of 24 hours on Wed Sep 8.Counter Name Avr. Min. Max.---------------------------------------- --------- ------ ---------Memory: Pages/sec 58.954 0 2549.611Memory: Available Bytes Physical Disk: % Disk time 5.507 0.124 997.87Physical Disk: Avg. Disk Queue Length 0.165 0.004 29.936Processor: % Processor Time 11.547 0.001 99.948System: Processor Queue Length 0.074 0 65SQL Server Buffer: Buffer Cache Hit Ratio 99.831 97.046 99.897SQL Server General: User Connections 140.755 3 451 Based on what Sayed Geneidy has written in "SQL Server Hardware Tuning and Performance Monitoring"(http://www.sql-server-performance.com/sg_sql_server_performance_article.asp), I may have some problem in "Memory: Pages/sec" part. It should be 1 to 20 but it is 58. Questions: 1. How it's possible to have data in Performance Monitor based on hourly time axes? In other words, I want to know when I had 2549 page/sec in Memory: Pages/sec? Or when I had 451 user connections? 2. “Maximum Worker Threads” is set to 255 in my server. Should I change it to 500 because of 451 in Max column for User Connections? What is disadvantages if I do that?Thanks,Canada DBA |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2004-09-09 : 13:07:34
|
| 1. The perfmon trace usually includes a graph if you open it up. (At least I have always seen a graph when I use perfmon)2. I wouldn't. Don't have a specific reaso, I just like to keep as many of the default/ let SQL Manage settings as possible, and only change them if I have a specific reason, and I know exactly what the change is intended to do.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
 |
|
|
CanadaDBA
583 Posts |
Posted - 2004-09-09 : 14:16:36
|
Thanks Chad; I have seen the graph but it hasn't a time axis. I don't know when I reached the pick. Was it 10AM or 2PM?Regarding Max worker threads, I checked the server and found 331 connections at 10am. I used the following code:SELECT GetDate() [Time], *FROM master..sysperfinfoWHERE (counter_name = N'User Connections') Do you mean if I leave the number to 255 is ok? Having connections more than 255 in working hours doesn't hurt the SQL server performance? quote:
Originally posted by chadmat
1. The perfmon trace usually includes a graph if you open it up. (At least I have always seen a graph when I use perfmon)2. I wouldn't. Don't have a specific reaso, I just like to keep as many of the default/ let SQL Manage settings as possible, and only change them if I have a specific reason, and I know exactly what the change is intended to do.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime.
Canada DBA |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2004-09-09 : 16:02:54
|
| From BOL:Having all worker threads allocated does not mean that the performance of SQL Server will degrade. Typically, a new batch has only a short wait for a free thread. Allocating more threads may degrade performance because of the increased work required to coordinate resources among the threads. Many SQL Server systems running in production reach this state and run with very high performance levels.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2004-09-09 : 16:07:40
|
| Oh, on perfmon, I could be wrong, but I thought there was a way to add the time to the graph. Look around in the properties. I haven't worked with perfmon in several years so I don't remember exactly what it can do, but I thought you could add the time.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
|
|
|
|
|
Performance Monitoring