August 1, 2014 at 8:00 am
I followed the following link from Brent Ozar's web site to collect PerfMon statistics for a SQL server I inherited: http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/.
I let the perfmon run for an entire week and used Brent's trick to get numbers for Average, Min, Max, etc... on the spreadsheet.
One thing I noticed and am curious about is, this physical machine has 8 processors and looking at the %Process Time for each processor only Processor(0) has data filled in. I was expecting each processor to have data in it. Does this mean that on a 8 processor system only 1 processor is being used? If so, seems like a waste of processors and licensing money.
I checked the MAXDOP and it is set to 0, which it is my understanding it will use all processors.
This database server unfortunately is not a dedicated database server as there are other small applications running on it.
Another thing I find weird is within SSMS, the properties of the server, and under memory; the max memory is set to 2900MB on a system that has 32GB of RAM installed on it. Seems a low to me, I would welcome anyone's thoughts on that as well.
Any and all information is appreciated!!!
August 1, 2014 at 8:04 am
Another thing that is weird...I wanted to get statistics for the drives that host my data and log files (two separate drives) and they both have no data in them as well. Maybe I need to run the perfmon again and pay more attention to what I am doing???
Some other information that may be relevant: I ran the PerfMon from a VM connecting remotely to the SQL box.
August 1, 2014 at 8:41 am
One thing I noticed and am curious about is, this physical machine has 8 processors and looking at the %Process Time for each processor only Processor(0) has data filled in. I was expecting each processor to have data in it. Does this mean that on a 8 processor system only 1 processor is being used? If so, seems like a waste of processors and licensing money.
When you say you expected each processor to have data, did the perfmon show 8 processors, and only one had data, or, did it only show one processor?
If it's one, then you were capturing the VM, not your server.
I checked the MAXDOP and it is set to 0, which it is my understanding it will use all processors.
MAXDOP indicates the number of processors that can be used in a query. What you see in perfmon in unaffected by this setting.
Another thing I find weird is within SSMS, the properties of the server, and under memory; the max memory is set to 2900MB on a system that has 32GB of RAM installed on it. Seems a low to me, I would welcome anyone's thoughts on that as well.
It does seem low. Run this to be certain:
This will display all of the options.
sp_configure 'show advanced options', 1
RECONFIGURE
sp_configure 'max server memory'
Also, have you looked into PAL (Performance Analysis of Logs)?
It's a lot more user friendly, and automates most of the things laid out in Brent's post.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
August 1, 2014 at 8:47 am
Some performance counters are further divided in instances, so the % Processor Time counter is divided into one instance for each processor you have, and the Avg. Disk sec/Read counter is divided into one counter for each physical disk in your system. Did you choose <All instances> or _Total or just one of the instances for those counters?
2900 is very low for SQL Server in this day and age. I notice you've posted in the SQL Server 2005 forum. Are you using 32-bit or 64-bit? How much memory do the other programs on the server use?
MAXDOP 0 doesn't necessarily mean you'll use all processors. If all your queries are below the cost threshold for parallelism, each query will only use one processor. Of course, that doesn't mean that different queries can't use different processors.
John
August 1, 2014 at 11:17 am
Michael,
The perfmon showed all 8 processors, 0-7 and I selected each one of them individually to add them to the perfmon. Also, I will be researching Max Memory settings and what they should be. For some reason I have it in my head that the max memory setting should be set to 75% of the physical RAM. Not sure why that sticks out, but I will be researching this. Do you have a guideline you follow for setting this? Thanks for the codeplex link as well.
John,
When creating the perfmon I selected each processor individually to add it to the perfmon. I did not selecet <All Instances> or the _Total. Also, I am running 64-bit SQL Server.
Thanks again for the replies...
August 1, 2014 at 11:27 am
To be sure I did not gather statistics for the local VM (Windows 7), I re-opened a new Data Collector set and before changing the <Local Computer> to my SQL box, I went down to the Processor%Process Time category and only one processor is available (processor 0). Then connecting it to my SQL box and looking at the same measurment, I see all 8 processors. There is a column for all 8 processors in my corresponding csv file, only processor 0 recorded any data.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply