October 4, 2005 at 4:50 am
All,
I am new to performance monitoring SQL Server and would like to follow the best practice to provide accurate results.
If I wish to performance monitor 2 SQL Servers:
1 Am I best to run perfmon from my own machine or from the SQL Server I wish to monitor?
2 Am I best to write to a file on my local C: or the C: of the SQL Server I wish to monitor?
3 How do I display the time on the graph when I come to view it?
Any help would be appreciated
Carl
October 4, 2005 at 9:54 am
Hi Carl,
This is just my take on things but here goes.
1. I always run performance monitor on the SQL server that I want to monitor (except for when I decide not to for some reason, in that case I still use a server on the same subnet as the SQL box).
2. For historical logging of perfmon data you should save the file to a drive on the SQL Server.
3. It's not that difficult really but may not be immediately apparent, basically when you load a log file you get a time window that the data was collected between, you can either guestimate when things happen or change the time window in the properties, it gives a sliding scale so you should be ok.
Hope this explains things properly for you,
Mike
October 5, 2005 at 12:01 pm
Could be a little premature at this point, but anyway... Make sure that when you restart your machine, you have to restart your counters too!
October 5, 2005 at 4:53 pm
There is an excellent white-paper from Quest on this. Go to http://www.quest.com and download the white-paper. It has all the detailed steps on how to do performance monitoring and tuning for SQL Server.
October 5, 2005 at 5:00 pm
2. For historical logging of perfmon data you should save the file to a drive on the SQL Server.
In addition to that, remember that in XP and Server 2003 it is possible to log directly to SQL Server (or any ODBC data source). Also, using the relog utility it is possible to move logs between different storage formats (csv, blg, odbc source etc).
October 6, 2005 at 3:40 pm
To avoid any possible network traffic issues I always monitor directly from the SQL Server and store the files locally. PerMon generates a substantial amount of data through the wire (even though you only select a few counters at time. Just selecting basic things on a quad CPU server; like CPU % usage, swapfile usage, SQL cahce hit ratio and a few other statistics, you can see about 4 Mb a minute (with the sampling interval at 1 minute) coming down the pipe to your machine. Just imagine if I wanted this information on an 8 CPU box and add in dual network cards and some disk performance information every 15 seconds (or even every second) I could easily 'choke' activity on my network segment. I have done this on s few networks as a test of throughput and resiliency. Be safe, be smart, execute it on the SQL Server. After all that's what RDP is for anyway !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply