Baseline Logfile

Question

Baseline Logfile

Bobby Glover

SSCarpal Tunnel

Points: 4143
More actions
November 15, 2010 at 7:54 am

#229318

Hi
I'm creating a Performance Baseline for all my SQL servers, 2 questions.
1 How do I change the location of the logfile from C:\Perflogs.... to another drive.
2. Can I create the counters on one server and then replicate it across all server(use the 1st one as a template). Instead of recreatitng one for each server from scratch as I would like to use the same counters on all servers.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 1

2. script this with something like Powershell - http://blogs.msdn.com/b/buckwoody/archive/2010/01/05/performance-counters-there-s-a-script-for-that.aspx

http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/11/30/my-first-useful-powershell-script-capturing-performance-counters.aspx

1. I think if you solve #2, #1 is easy, or you can better control where the data goes.

Bobby Glover SSCarpal Tunnel Points: 4143 More actions · Answer 2

Thanks.

I will look into this, unfortunately i have never used PowerShell before.

But i'll give it a try.

Willem G SSC Eights! Points: 809 More actions · Answer 3

There is another option: using a central management server for perfmon (perfmon can read counters from remote servers), and storing the perfmon values in a SQL database on the management server. I use this setup to monitor a limited number of (consolidated) servers; it works just fine!

A central perfmon setup has several benefits: it will allow you to monitor all monitored servers from a single point and keep one set of perfmon results for all servers combined. Also, it is easy to create and adapt counter logs for (new) servers without having to use VBS, Powershell or the like.

How to set up things?

- Pick a (management) server with SQL installed. On it,

- create an empty monitoring database;

- create an ODBC entry (system DSN) for perfmon to access the monitoring database;

- create a default perfmon Counter Log set:

- General Tab: under 'Add Counters' choose option 'Select counters from computer ..'

- General Tab: choose an 'Interval' (e.g. 10 minutes) and under 'Run As' provide a Windows account with sufficient privileges to query the remote perfmon counters

- Log Files Tab: under 'Log file type' choose 'SQL Database' and use the 'Configure' button to pick the System DSN you created.

Once created, save off the counter log settings to a (.htm) disk file: right-click the Counter Log and choose 'Save Settings As'.

Start and test the newly created Counter Log.

Perfmon values will be stored in two tables in the monitoring database: CounterDetails and Counterdata.

How about adding servers (e.g. Servername2)

- Copy the .htm file and rename to 'Servername2.htm'. Edit with e.g. notepad. If you stick to a default set of counters, you only need to replace 'Servername1' by 'Servername2' with a global search/replace.

- In perfmon, right-click in the Counter Log window, choose 'New Log Settings From', open the .htm file you just edited, and a new Counter Log for Servername2 is in place.

Once you have set things up, it is very easy to query your perfmon repository. I use Excel Pivot Chart Report (nice graphs) to keep track of counters. Once this is set up, you can simply press 'Refresh Data' for the latest counter values. (Setup through Data - Import External Data - New Database Query).

I made an SP to collect values based on server name, date, et cetera to refine data collection (let me know if you want me to post or mail it).

Do not forget to clean up (throw out old counter values) your monitoring database from time to time...

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 4

Willem, nice system, and if you'd like to write this up a little more with code, it would make a good article.

I don't think it scales well, but for a limited number of servers, I think that could work fine.

Bobby Glover SSCarpal Tunnel Points: 4143 More actions · Answer 5

Willem, EXCELLENT!!! EXCELLENT.

The brick wall is now safe as I will no longer be banging my head against it.

This process is not very well documented anywhere on the net. I agree you should post it as an article.

Thanks both, Thanks very much.

Still will be check out the PowerShell option as it would be good to try to get to know PowerShell.

Willem G SSC Eights! Points: 809 More actions · Answer 6

Steve, I followed up on your suggestion. You will find the article (Storing multi-server Perfmon counters in a central SQL database) in the Contribution Center on the site.

Regards, Willem