Permon counter questions

toad2898

Hi. I'm trying SQL Monitor out for our company (approx 50 VMs running SQL 2000 through 2012).

As we run vmware and even though they're obviously not SQL counters I would normally monitor various counters under the VM Processor and VM Memory categories.

I see you can create custom metrics but these appear to have to be t-sql based and these counters aren't ever likely to appear in sys.dm_os_performance_counters

Is there a way to include additional machine level perfmon counters without using t-sql?

Thanks

RajK

I am the support engineer that is responding to this support request. The forum post has raised a support ticket in our ticketing system and I am following up in regards to this issue.

I have responded to this via our ticketing system for your reference.

Are you able to let me know how many licenses of SQL Monitor you are planning to use to monitor this environment?

Thanks for your interest in our product.

toad2898

Hi. If the ticketing system response you mention was sent to me via e-mail could you send it again as the address on my profile was out of date.

betweeen 20 and 40 servers.

thanks

toad2898

Hi. Would it possible to get an answer to this soon? It's just my trial runs out in 5 days, 2 of which I won't be working. Thanks.

priyasinha

Hi Rob,

Apologies for the delay. We assumed that you received our reply email. You should be able to do this using xp_cmdshell. I have just quickly put an example together.

IF OBJECT_ID('tempdb..#ProcessorTime') IS NOT NULL
    DROP TABLE #ProcessorTime

CREATE TABLE #ProcessorTime
    (
      RowID INT IDENTITY(1, 1) ,
      Value VARCHAR(255)
    )

INSERT  INTO #ProcessorTime
        EXEC xp_cmdshell 'typeperf "\processor(_Total)\% Processor Time" -sc 1'


IF NOT EXISTS ( SELECT  1
                FROM    #ProcessorTime
                WHERE   Value LIKE '%Error%' )
    BEGIN
        SELECT  CAST(REPLACE(RIGHT(Value, LEN(Value) - CHARINDEX(',', Value)),
                             '"', '') AS FLOAT)  AS ProcessorTime
        FROM    #ProcessorTime
        WHERE   RowID = 3 
    END
ELSE
    BEGIN
        SELECT  -1 AS ProcessorTime
    END

DROP TABLE #ProcessorTime

It is not tested properly so please verify it before you actually use this. Also, if you need to extend your trial then just drop us a line and we would be happy to help you with trial extension.

Thanks,
Priya

toad2898

Thanks Priya.

Here is some feedback.

It does work, I've tested it but it's better for some counters than others, if I can explain:

To add disk level counters, for example PhysicalDisk > Disk Writes/sec. I'd have to set up a separate custom metric for every disk on every server which isn't managable as in my case I'm going to end up with hundreds of custom metrics and you kind of buy these tools so you don't have to do stuff like that.

in addition to that, the show: box under the analysis graph is too small to display the full metric name unless you hover on it which means you always have to think up clever short names for perfmon counters with long ones.

and then there's the use of xp_cmdshell and all the debate that comes along with enabling it.

I guess to summarise it would be:- please can you please put more machine metrics into the core product.

Overall I like the product though and i understand it's not just written for me is it!

Thanks