Post reply

Logging Perfmon counter data in database

  • March 30, 2007 at 6:22 am

    #370106

    Hi,

    I have setup Performance Monitor on my SQL Production box. The monitor is capturing data in .csv files. Going forward, I am looking at logging this data to the SQL Server database tables, so that they can be stored permanently and data retrieval also becomes easy.

    Do you have any ideas / ways of logging the Perf mon data directly to sql tables. It has the option of logging data to "SQL Database", but that kind of is a weird option, because it just logs in the time monitor is started, does not log the actual data. this stores the data in the binary format(.blg)

    Any suggestions would help.

    Thanks,

    Kishore

     

     

  • April 14, 2007 at 8:09 pm

    #700574

    cosider relog.exe for converting from one perfmon format to another such as database.

    I am not sure if it is going to make data retrival easy ;-)`


    -- Amit


    "There is no 'patch' for stupidity."


    Download the Updated SQL Server 2005 Books Online.

  • April 15, 2007 at 9:15 pm

    #700614

    A couple things:

    1.  Always store your PerfMon logs in binary (.blg) format instead of .csv format.  It's more efficient, and loads into Profiler just as csv files do.

    2.  As Amit points out, relog.exe (type relog.exe /? at a command prompt) is the way to go.  It extracts some or all of the data from PerfMon files into other formats, including into SQL Server.  There's a bug with relog loading directly into SQL Server using the SQL Native Client, though.  MS has a patch for it, but you have to call support and wade your way through through a couple layers of support operators to get it.  I don't have the KBase article handy; if you're that interested then try using relog to copy some perf data into a SQL Server, and paste the error message into a search box at microsoft.com.  I just extract the parts I need with relog into .csv files, and import those.

    -Eddie

    Eddie Wuerch
    MCM: SQL

  • April 15, 2008 at 9:24 am

    #802649

    Just completed a report services interface with the Relog Utility. Unfortunately the fix from MS did not work so i am still needing to store the data in 2000 which is ok at this point but does need to be fixed later. The database tables are pretty easy to wade through to see the linking. I strongly suggest that you look into such a ssrs interface if you find yourself working with perflogs often as we do here.

  • May 8, 2009 at 1:08 pm

    #991410

    If you need a jump start on working with Relog.exe to get .blg files into SQL Server, check out those two links.

    Relog SQL database schema (nasty datetime handling)

    Relog Syntax Examples (for SQL Server)

  • January 26, 2010 at 2:57 pm

    #1108972

    Do you have any ideas / ways of logging the Perf mon data directly to sql tables.

    Here's how I've had to do it in the past:

    • Set up the db on whatever server. I called mine "PerfMon" The user that is running the "Performance Logs and Alerts" service on the originating system needs to have rights to the db. I set up db_owner, so don't know exactly what is needed.
    • Create a 'System DSN' on the originating system, with the same name as the db, and accessing the db using NT Authentication.
    • Determine what performance counters you want to use, and put those in a text file. (I use "typeperf -qx > possible_counters.txt", then copy the ones I want into another file, like "to Monitor.txt")
    • Run the following commands on the originating system:

      logman create counter systemLog -f SQL -cf "C:\toMontor.txt" -o PerfMon!systemLog -si 05

      logman start systemLog

    • (you can change certain parameters, of course. Like "systemLog" and the interval after "-si")

  • January 28, 2010 at 10:38 am

    #1110216

    Or simply use logparser from microsoft. I think version 2.2 is the latest that was released. Anyways, once you have logparser you can query your csv file in SQL like language (yes, you can do aggregation) and the output can be sent to databases, csv, tsv, xml or even excel charts.

    Sample queries to help you on the way:

    1. Get the header information from the csv

    logparser -h -i:CSV D:\PerfmonTrace.csv -headerRow:ON

    2. Get the details you need to a datagrid

    logparser -i:CSV -iTsFormat: "mm/dd/yyyy hh:mm:ss.sss" -o:Datagrid "select to_date(datetimefield), avg(to_real(sec/read)) , avg(to_real(sec/write)), avg(to_real(sqlbatches/sec)) from D:\PerfmonTrace.csv group by to_date(datetimefield)"

    Points to note:

    - you column names may vary.

    -iTsFormat -> tells logparser the format of the timestamp field in your csv. if not specified getting the data you need is a pain in the ,oh! well you know 🙂

    -o:Datagrid -> send the processed data to a datagrid.

    Hope this helps

    Regards

    -Hope is a heuristic search :smooooth: ~Hemanth

  • February 3, 2010 at 7:52 pm

    #1113391

    One of the simplest way to load data is to store in CSV format and then use openrowset to read it

    Though you need to create a schema file for the columns like below and delimiter should be good...

    File c:\temp\test.txt

    10248;"Vins et alcools Chevalier";7/4/1996 0:00:00

    10249;"Toms Spezialitäten";7/5/1996 0:00:00

    10250;"Hanari Carnes";7/8/1996 0:00:00

    File c:\temp\schema.ini

    [test.txt]

    Format=CSVDelimited

    CharacterSet=OEM

    ColNameHeader=False

    Col1=CustomerNumber Long

    Col2=CustomerName Text Width 40

    Col3=EntryDate Datetime

    SELECT *

    FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

    'Data Source=c:\scripts;Extended properties=Text')...test#txt

    Prakash Heda
    Lead DBA Team - www.sqlfeatures.com
    Video sessions on Performance Tuning and SQL 2012 HA

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

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