Several Questions

SAM has the ability to monitor SQL database instances individually, and has a template that you could customize to monitor a single database within that instance. SQL Server 2005-2008 (SQL) is one such template.

You can also create a custom monitor for specific processes running on a server. when you create a new monitor under Add component monitor you can use "Windows Service Monitor" that monitor will work with WMI or RPC and polls the CPU and memory utilization.

SAM has the option to monitor Windows Event logs outside of that I'm not certain. I think you can use a powershell or vbscript to pull information out of a custom log file, but I haven't had a need to use that yet.

Check out this template. This may help. If not, take a look at all the SAM templates here. You may want to filter by "event." Hope this helps.

Depending on if the log file you want to parse is on Windows (PowerShell) or Linux (Perl) I would recommend you look at the templates below. These are also included with SAM 5.0.1

Log Parser (PowerShell)

Also, you can very easily monitor any process or service running on the remote host. The easiest way might be to click the "Start Monitoring" button in the Real-Time Process Explorer. The next simplest way would be to use the "Find Process, Services, and Performance Counters" Wizard under "SAM Settings".

Gary,

when you refer to the ability to monitor a single DB inside an instance with customization in the SQL template, do you mean by simply altering the initial catalog field, or something else? I'm working on drilling down to specific DB monitoring as well.

The "SQL Server 2005-2008 Performance (SQL)" template has the ability to monitor DB performance by instance. Once you've assigned the template to a server you'll need to go through the assigned template and set the instance you want to monitor under each of the components you want to monitor.

I ended up making a copy of the primary template and making several changes to it so I had the original to refer back to in the event that business requirements changed. So far the only issues I've had with it is when the DBA decided to change my monitoring account's password on the database servers.

Hope this helps.

Gary,

thanks - got that part okay. What I'm trying to do is find out an effective method to poll specific DBs inside the instance, and wasn't sure if that's what your earlier post referred to.

Thanks for any info.

Ah, ok i get what you mean.

The database specific counters you want are stored in the same system database views as the overall counters.

You'll need to add a line to the query to get the database you're looking into.

for example the query for Log Flushes/sec is:

DECLARE @time1 DATETIME;
DECLARE @time2 DATETIME;
DECLARE @value1 BIGINT;
DECLARE @value2 BIGINT;

-- get first sample
SELECT
@value1 = cntr_value,
@time1 = getdate()
FROM
sys.dm_os_performance_counters
WHERE
object_name = 'SQLServer:Databases' AND
counter_name = 'Log Flushes/sec' AND
instance_name = '_Total';

-- wait for 5 seconds
WAITFOR DELAY '00:00:05';

-- get second sample
SELECT
@value2 = cntr_value,
@time2 = getdate()
FROM
sys.dm_os_performance_counters
WHERE
object_name = 'SQLServer:Databases' AND
counter_name = 'Log Flushes/sec' AND
instance_name = '_Total';

-- calculate page reads per second
SELECT
(@value2 - @value1) / datediff(ss,@time1,@time2) [Log Flushes/sec];

The instance_name='_Total' is what you'll change to reflect the database you'll monitor.

Some of the counters are not available as database specific, but you can get a list of the counters if you look at the sys.dm_os_performance_counters view in the master database and filter it down by database (instance_name).

Running this query in the SQL Server Management Studio should get you that list.

SELETCT TOP 1000 [object_name]

, [counter_name]

, [instance_name]

, [cntr_value]

, [cntr_type

FROM [master].[sys].[dm_os_performance_counters]

WHERE instance_Name="<your db name here>'

I haven't gotten much further into it past this. I've gotten most of what I've needed from the templates and a few minor custom queries added through the SQL user experiance monitor component.

Excellent - precisely what I was after! Thanks!