SQLH2: The Free Health, History and Performance Monitoring Tool

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kKellenberger/sqlh2thefreehealthhistoryandperformancemonitoringt.asp

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • Kathi, good article. I love this tool and it's price tag especially . The setup though was a bit of a challenge and maintainability is tricky (modifying the XML files, etc). It's a great version 1 product though overall and I'm sure they'll build on it.

  • Thanks, Brian.  I found the the set up was a little tricky at first, too.  My goal was to give a different perspective on installing and configuring so that others would have an easier time than I did.  I also think that this tool is not widely known and many DBA's might hear about it from my article.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • I setup this tool but I was after more frequent collection of performance counters for overall sytem health (System, ASP, etc). I think I set it for hourly collection and the database quickly grew to large.

    Not to knock this tool but for my purposes I have opted to use the round robin database http://www.rrdtool.org. Adrian Wood put together a complete package for system monitoring including an ASP front end called wshRRD which is available at http://members.optusnet.com.au/~picuspickings/

    There is also a java version of this solution called JRobin (http://www.jrobin.org/) but I havn't tried it yet.

    The set up is probably as involved as SQLH2 but the nature of RRD means that the database size is fixed. In my case the the db size for each performance counter (each instance) is 41kb and will never get bigger! It has a built in graphing function. So now I collect performance counters every 10 minutes without having to worrey about it consuming my harddrive.

    Cheers,

    Dave

    Trainmark.com IT Training B2B Marketplace
    (Jobs for IT Instructors)

  • SQL Server Central is all about DBAs and developers helping each other out, so I'm sure that others would appreciate learning about these tools as well.  Thanks for mentioning them!

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • Kathy,

    I think there is an error in batch file. I believe it should look like

    NET STOP SQLH2PerfCollector

    C:\SQLH2\SQLH2.exe /CC:\SQLH2\H2ConfigPerf.xml

    NET START SQLH2PerfCollector

    Otherwise performance file is locked by the service and SQLH2 can not get anything out of it.

     

  • It will still work if you just stop and start the service.  When the service is restarted, it will create a new file with a different name.  The old file will not be locked.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • I experienced the same problem as Nikolay Chernavsky. I had to stop the service, then run SQLH2.exe, then start the service in order to avoid the file lock issue.

    Below is the error I received when the bat file command order was start service, stop service, run SQLH2.exe:

    3/30/2005 7:50:48 AM    INFO:   D:\SQLH2\PerfCollector\Data\pf033005011842.txt: fid = 7, state = 0, retry = 0

    3/30/2005 7:50:48 AM  ERROR:  The process cannot access the file "D:\SQLH2\PerfCollector\Data\pf033005011842.txt" because it is being used by another process.

     

  • That is very odd, the SQLH2 base collector is only supposed to pick up files starting with 'pfc', so it shouldn't have even tried to open that file.  Normally, you don't stop the performance collector at all to run the base collector, so, in theory you shouldn't have a problem.  Running the base collector in between the stop and start evidently works for you, so continue on...

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • Any way to make this work across NON-trusted domains? Other than installing it in every domain. Centralized collection would be nice.

  • When I gave this presentation to the local user group, this question came up.  The only idea someone had was for the performance files.  Use the performance collector in each domain. Then, collect the files into the performance data folder where the repository database is installed.  I haven't tried this, so I don't know if it will work.  I can email the person who wanted to do this and see if he had any luck.  You can also send an email to SQLH2@Microsoft.com with questions.

     

     

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • Kathi,

    Very good article....

    I saw on the microsoft site and it says "This version(2.027) of SQLH2 supports SQL Server 2005 RTM collection "

    Is there a different version of SQLH2 for SQL 2000 ?

    Thanks,

    Ranga

  • Thanks!

    I just found out from Microsoft that this version does support SQL Server 2000.  They said that a previous version worked with SQL 2005 beta and this version was updated for the released version.  So, it is the correct download.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • What does -3 state means on a PerfColector?

    I'm using en-US windows locale spanish sql server & database with english in user instance settings.

    That's the SQLH2 log:

    4/11/2008 1:40:44 PM Status: Starting Perf Loader

    4/11/2008 1:40:44 PM INFO: Perf host_id = 2

    4/11/2008 1:40:44 PM INFO: Lock aquired: pid = 1

    4/11/2008 1:40:44 PM INFO: C:\SQLH2\PerfCollector\Data\pf040108165200.txt:

    fid = 1, state = -3, retry = 0

    4/11/2008 1:40:44 PM INFO: C:\SQLH2\PerfCollector\Data\pfc040108173252.txt:

    fid = 3, state = -3, retry = 0

    4/11/2008 1:40:44 PM INFO: C:\SQLH2\PerfCollector\Data\pfc040308121908.txt:

    fid = 4, state = -3, retry = 0

    4/11/2008 1:40:44 PM INFO: C:\SQLH2\PerfCollector\Data\pfc040908030916.txt:

    fid = 5, state = -3, retry = 0

    4/11/2008 1:40:44 PM INFO: C:\SQLH2\PerfCollector\Data\pfc041008103549.txt:

    fid = 6, state = -3, retry = 0

    4/11/2008 1:40:44 PM INFO: C:\SQLH2\PerfCollector\Data\pfc041008113250.txt:

    fid = 7, state = -3, retry = 0

    4/11/2008 1:40:44 PM INFO: C:\SQLH2\PerfCollector\Data\pfc041008130242.txt:

    fid = 8, state = -3, retry = 0

    4/11/2008 1:40:44 PM INFO: C:\SQLH2\PerfCollector\Data\pfc041008160816.txt:

    fid = 9, state = -3, retry = 0

    4/11/2008 1:40:44 PM INFO: C:\SQLH2\PerfCollector\Data\pfc041008163638.txt:

    fid = 10, state = -3, retry = 0

    4/11/2008 1:40:44 PM INFO: C:\SQLH2\PerfCollector\Data\pfc041008165536.txt:

    fid = 11, state = -3, retry = 0

    4/11/2008 1:40:44 PM INFO: C:\SQLH2\PerfCollector\Data\pfc041008175623.txt:

    fid = 12, state = -3, retry = 0

    4/11/2008 1:40:44 PM INFO: C:\SQLH2\PerfCollector\Data\pfc041008180152.txt:

    fid = 13, state = -3, retry = 0

    4/11/2008 1:40:44 PM INFO: C:\SQLH2\PerfCollector\Data\pfc041008180943.txt:

    fid = 14, state = -3, retry = 0

    4/11/2008 1:40:44 PM INFO: C:\SQLH2\PerfCollector\Data\pfc041008190030.txt:

    fid = 15, state = -3, retry = 0

    4/11/2008 1:40:44 PM INFO: C:\SQLH2\PerfCollector\Data\pfc041008190043.txt:

    fid = 16, state = -3, retry = 0

    4/11/2008 1:40:44 PM INFO: Lock released: pid = 1

    4/11/2008 1:40:44 PM Status: Perf Loader finished

    That's C:\SQLH2\PerfCollector\Data\pf040108165200.txt file:

    e2e15752-d140-4ca8-8d1f-9fbd8dc6c44f,632dfe80-4808-4f2b-aa06-673456abcb02,e25ef2c6-160c-44a5-99c7-9fedd1ef50b3,,

    ,,,,

    1,SRVPRUEBAS,,,

    ,,,,

    ,,,,

    1,PROCESSOR,% PROCESSOR TIME,_TOTAL,

    2,PROCESSOR,% PRIVILEGED TIME,_TOTAL,

    3,MEMORY,AVAILABLE KBYTES,,

    4,MEMORY,PAGES/SEC,,

    5,MEMORY,COMMITTED BYTES,,

    6,MEMORY,COMMIT LIMIT,,

    7,SYSTEM,PROCESSOR QUEUE LENGTH,,

    8,SYSTEM,CONTEXT SWITCHES/SEC,,

    9,PHYSICALDISK,AVG. DISK QUEUE LENGTH,_TOTAL,

    10,PHYSICALDISK,AVG. DISK SEC/READ,_TOTAL,

    11,PHYSICALDISK,AVG. DISK SEC/WRITE,_TOTAL,

    12,PHYSICALDISK,DISK READS/SEC,_TOTAL,

    13,PHYSICALDISK,DISK WRITES/SEC,_TOTAL,

    14,PHYSICALDISK,DISK READ BYTES/SEC,_TOTAL,

    15,PHYSICALDISK,DISK WRITE BYTES/SEC,_TOTAL,

    16,PROCESS,% PROCESSOR TIME,SQLSERVR,

    17,SQLSERVER:BUFFER MANAGER,BUFFER CACHE HIT RATIO,,

    18,SQLSERVER:BUFFER MANAGER,CHECKPOINT PAGES/SEC,,

    19,SQLSERVER:BUFFER MANAGER,PAGE LIFE EXPECTANCY,,

    20,SQLSERVER:MEMORY MANAGER,TOTAL SERVER MEMORY (KB),,

    21,SQLSERVER:MEMORY MANAGER,TARG

  • This may be too late for you now, but I had this problem as well.

    It was due to the fact that the Sybase client was on the server where sqlh2.exe ran & the sybase client was in the PATH enviornment variable before the MS SQLServer.

    You can check this by running bcp /v from the command line. If its sybase, try moving the path to the client to the end of the PATH variable.

    Cheers

    Dan

Viewing 15 posts - 1 through 15 (of 22 total)

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