SQLH2: The Free Health, History and Performance Monitoring Tool

Question

SQLH2: The Free Health, History and Performance Monitoring Tool

Kathi Kellenberger

SSChampion

Points: 11811
More actions
March 17, 2005 at 4:16 pm

#163980

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

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

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

Brian Knight One Orange Chip Points: 27249 More actions · Answer 1

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.

Brian Knight
Free SQL Server Training Webinars

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 2

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

chisholmd SSCrazy Points: 2490 More actions · Answer 3

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)

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 4

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

Nikolay Chernavsky Mr or Mrs. 500 Points: 573 More actions · Answer 5

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.

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 6

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

gregfaulk Old Hand Points: 369 More actions · Answer 7

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.

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 8

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

Norman Kelm SSCommitted Points: 1922 More actions · Answer 9

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

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 10

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

Ranga N SSChasing Mays Points: 642 More actions · Answer 11

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

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 12

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

jroselln SSC Rookie Points: 27 More actions · Answer 13

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

dgray1 Mr or Mrs. 500 Points: 587 More actions · Answer 14

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