SQL Server Performance Forum – Threads Archive

Performance Monitoring

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.95402549.611
Memory: Available Bytes
Physical Disk: % Disk time 5.5070.124997.87
Physical Disk: Avg. Disk Queue Length 0.1650.00429.936
Processor: % Processor Time 11.5470.00199.948
System: Processor Queue Length 0.074065
SQL Server Buffer: Buffer Cache Hit Ratio 99.83197.04699.897
SQL Server General: User Connections 140.7553451
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, CanadaDBA

1) Check the time of event where Memory<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />ages/sec spiked to 2549 and also look which process is taking this resource, if you’re capturing PROCESS counter also in PERFMON. 2) To find out how many worker threads your SQL Server is using, check the number of connections that are currently made to your server using Enterprise Manager or QA, <pre>SELECT *, counter_name AS Expr1 FROM master..sysperfinfo WHERE (counter_name = N’User Connections’). </pre> If the maximum number of the user connections to your SQL Server box is equal to 500, you can set the ‘max worker threads’ options to 500, this can improve SQL Server performance because thread pooling will not be used. Keep in mind the server must have enough RAM in order to gain best performance by increasing this value and thereare some limitations to this general recommendation. If the memory is not available for extra values then it will degrade SQL performance drastically and it may crash. But, on the downside, thread pooling can introduce problems of resource contention between connections. As usual you must test this on the development environment for all aspects and then deploy in the production. <hr noshade size="1">Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum</a> <center>This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.</center>

Actually, this is my question. How can I check the time? <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by satya 1) Check the time of event where Memory<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />ages/sec spiked to 2549… <hr height="1" noshade id="quote"></blockquote id="quote"> CanadaDBA

One way (may be there is other easy) is to trace performance monitor and profiler with starttime column.
With both you can find whats going on when pages reach 2549.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Try exporting the profile to TAB delimited.
I cant remember but it might contain the time

Here you go: If you’re using Windows NT OS then on the Performance Monitor after loading the trace file press Ctrl+E for the time slot. If you’re using Windows 2000 Server then after opening SYSMON (PERFMON) on System Monitor click on ‘Hand on note’ icon which is properties and obtain the source log file where the stats are gathered. On the Source tab itself you can find TimeRange command button to get the information.[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FarhadR Actually, this is my question. How can I check the time? <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by satya 1) Check the time of event where Memory<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />ages/sec spiked to 2549… <hr height="1" noshade id="quote"></blockquote id="quote"> CanadaDBA <hr height="1" noshade id="quote"></blockquote id="quote"> <hr noshade size="1">Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum</a> <center>This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.</center>

If you trace performance monitor and store as CSV, first column is date,hour,minuts and seconds.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>