Re: Perfmon logging to SQL database - slow retrieval

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Andrew J. Kelly (sqlmvpnoooospam_at_shadhawk.com)
Date: 05/19/04 

Date: Wed, 19 May 2004 10:48:35 -0400

What is the purpose of loading the data into a table only to use the GUI to
view it? As you see for your self the flat file (binary) is the most
efficient way if your going to use the Perfmon Gui to look at the data. The
advantage of putting it into a table is to run queries against the data and
not look at it with Perfmon. There is a tool called RELOG.exe (If you have
Xp just type relog /? at the dos prompt) that can import the data into a set
of SQL Server tables in a normalized manor. This will allow you to run
queries against the data. If all you want to do is use the GUI I suggest
you keep them as flat files. 

-- 
Andrew J. Kelly  SQL MVP
"RBurton" <rburton@rm.com> wrote in message
news:%23pHrUnaPEHA.272@TK2MSFTNGP12.phx.gbl...
> I am trialling the Windows2003 perfmon functionality to log counters to a
> SQL database (we currently log to csv files)
>
> I have successfully set up logging and now have some 1/2 million rows in
the
> CounterData table. On full implementation we would expect to log over 3
> million rows per day (32 servers, 18 counters, 15 sec interval)
>
> Now I am using perfmon to view the logged data, and is very slow - many
> times slower than viewing the same data from a csv file. To find out why I
> used SQL Profiler to monitor the query used to retrieve logged data, and
> discovered that a separate SELECT statement is processed for each row of
> data in the CounterData table - many thousands of queries all like this:
>
> select FirstValueA, FirstValueB, SecondValueA, SecondValueB, MultiCount,
> CounterDateTime, CounterValue from CounterData where GUID =
> '24255946-a0c5-4902-ab07-f8595fb4bccd' and CounterID = 26 and RecordIndex
=
> 1167
>
> Has anyone else experienced this? Can anyone tell me is this behaviour
> inherent in the implementation of SQL logging in Perfmon, or can it be
tuned
> or configured for improved performance?
>
> Thanks, Richard
>
>