Post reply

Scripts for monitoring

  • August 9, 2011 at 8:54 am

    #241446

    Experts,

    Need to do some kind of monitoring in one of our client's server.

    We must do it using only scripts as we can't install anything on that server.

    I know that this is a vast topic and there many many things to be checked.

    But atleast for a take off, can someone share with me some basic scripts please.

    Like the scripts to check,

    CPU

    Memory

    IO etc;

    (Ofcourse, I am in the process of doing it systematically and regulerly, which will take time...)

    Thanks in advance.

  • August 9, 2011 at 8:56 am

    #1365980

    This install nothing on the server (but needs a separate server to log data into).

    http://www.red-gate.com/products/dba/sql-monitor

  • August 9, 2011 at 9:23 am

    #1366004

    Thanks a lot for your quick response.

    But is there any plain sql scripts as I mentioned...pls..

    Thanks again

  • August 9, 2011 at 9:37 am

    #1366028

    I think Breant Ozar put something out there, but that's all I can offer.

  • August 10, 2011 at 4:26 am

    #1366459

    Anyone else... any scripts..?

    Thanks.

    Smith

  • August 10, 2011 at 7:18 am

    #1366531

    With only scripts, there's little you can do in terms of monitoring. Monitoring, as a concept, implies gathering data continually in order to respond to the information gathered or to track certain data.

    To see what's happening on a server, I'd suggest using DMVs. There's a great book by Louis Davidson & Tim Ford. It'll get you started. But it won't be enough.

    By the way, SQL Monitor doesn't install anything, but it does need access to WMI, so it might not be a good solution for your situation, unless you can modify the situation. If you do, SQL Monitor is great (disclosure, I work for Red Gate Software).

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • August 10, 2011 at 7:23 am

    #1366536

    I don't work for Red-Gate and with my very extended trials I saw a very good product. It also send alerts when something goes wrong which are 99% configurable (gave a few suggestions).

    You can track baselines and see when you have spikes or anything changes. I'd really check the trial on a test server to see if it can help you out.

    You can also whatch a live demo of the product that is plugged on sqlservercentral's servers : http://monitor.red-gate.com/?utm_source=ssc&utm_medium=peel&utm_content=corner_peel201104&utm_campaign=sqlmonitor

  • August 11, 2011 at 5:04 am

    #1367163

    Greg Larson has written an article with a good base of scripts for performance metrics, they are flexible and gives you the groundwork to add additional metrics as you like. http://www.databasejournal.com/features/mssql/automatically-capturing-sql-server-performance-metrics.html

    Glen Berry has a set of diagnostic scripts, search for "SQL Server 2008 and R2 Diagnostic Information Queries" on http://sqlserverperformance.wordpress.com/

    These need adapting to some extent to use as part of Greg Larsons performance metrics but have additions and other methods including IO and CPU that can be embedded within Greg's metrics script.

  • August 12, 2011 at 4:22 pm

    #1368267

    I used a "cheap" method while I was waiting on Net to get Confio up and running. Do you have Excel 2010 with power pivot that you could query a table that has this data logged to it?

    You can create a job that calls a procedure that stores data to a log table and an excel sheet to graph it. Not much, but I used it when I had nothing else to go on. Need to know a bit of Excel pivot table action to get it looking nice. Otherwise, use perfmon and collect everything straight from the machine.

    Need to create a job, and the tables are in the script below. May want to put on a DB with simple recovery mode. Run the job every minute or four... if you want to go this way.

    create procedure [dbo].[uRecordStats]

    as

    begin

    /*-- Setup

    select top 0

    current_timestamp as dtTimeNow,

    @@total_write as inTotalWrites,

    @@total_read as inTotalReads,

    @@io_busy as inIOBusy,

    @@connections as inConnections,

    @@idle as inIdle,

    @@CPU_BUSY as inCpuBusy

    into

    ServerStats

    create clustered index [TimeNow] on ServerStats (dtTimeNow)

    */

    insert ServerStats

    select

    current_timestamp,

    @@total_write - isnull(sum(inTotalWrites),0),

    @@total_read - isnull(sum(inTotalReads),0),

    @@io_busy - isnull(sum(inIOBusy),0),

    @@connections - isnull(sum(inConnections),0),

    @@idle - isnull(sum(inIdle),0),

    @@CPU_BUSY - isnull(sum(inCpuBusy),0)

    from

    ServerStats

    where (select create_date from sys.databases where name = 'tempdb') < dtTimeNow

    -- there were no records found meaning the server was re-initialized

    -- put in our base measure records!

    if @@rowcount = 0

    begin

    insert ServerStats

    select

    current_timestamp,

    @@total_write,

    @@total_read,

    @@io_busy,

    @@connections,

    @@idle,

    @@CPU_BUSY

    end

    --sp_monitor

    /*-- Setup

    select top 0

    sn.dbid as siDbId,

    sn.fileid as siFileId,

    sn.NumberReads as biNumberReads,

    sn.BytesRead as biBytesRead,

    sn.IoStallReadMS as biIOStallReadMS,

    sn.NumberWrites as biNumberWrites,

    sn.BytesWritten as biBytesWritten,

    sn.IoStallWriteMS as biIOStallWriteMS,

    sn.IoStallMS as biIOStallMS,

    sn.BytesOnDisk as biBytesOnDisk,

    current_timestamp as dtTimeNow

    into

    ServerFileStats

    from

    fn_virtualfilestats(null,null) as sn

    create clustered index [TimeNow] on ServerFileStats (dtTimeNow)

    */

    insert ServerFileStats

    select

    sn.dbid,

    sn.fileid,

    sn.NumberReads - sum(so.biNumberReads),

    sn.BytesRead - sum(so.biBytesRead),

    sn.IoStallReadMS - sum(so.biIoStallReadMS),

    sn.NumberWrites - sum(so.biNumberWrites),

    sn.BytesWritten - sum(so.biBytesWritten),

    sn.IoStallWriteMS - sum(so.biIoStallWriteMS),

    sn.IoStallMS - sum(so.biIoStallMS),

    sn.BytesOnDisk - sum(so.biBytesOnDisk),

    current_timestamp

    from

    ServerFileStats as so

    join fn_virtualfilestats(null,null) as sn on

    so.sidbid = sn.dbid

    and so.sifileid = sn.fileid

    where (select create_date from sys.databases where name = 'tempdb') < so.dtTimeNow

    group by

    sn.dbid,

    sn.fileid,

    sn.NumberReads,

    sn.BytesRead,

    sn.IoStallReadMS,

    sn.NumberWrites,

    sn.BytesWritten,

    sn.IoStallWriteMS ,

    sn.IoStallMS,

    sn.BytesOnDisk

    -- there were no records found meaning the server was re-initialized

    -- put in our base measure records!

    if @@rowcount = 0

    begin

    insert ServerFileStats

    select

    sn.dbid,

    sn.fileid,

    sn.NumberReads,

    sn.BytesRead,

    sn.IoStallReadMS,

    sn.NumberWrites,

    sn.BytesWritten,

    sn.IoStallWriteMS,

    sn.IoStallMS,

    sn.BytesOnDisk,

    current_timestamp

    from

    fn_virtualfilestats(null,null) as sn

    end

    end

  • August 15, 2011 at 3:22 am

    #1368485

    I suggest you to use VB scripts and schedule it using windows scheduler in any of the central server. If you have multiple server to monitor, pass the server list to the script.

    If you are looking for one such script i can pass one.

    Thanks and Regards

    Santhubt.

  • August 15, 2011 at 3:31 am

    #1368487

    If you are looking for one such script i can pass one.

    Yes, Please send it to me.

  • August 16, 2011 at 8:01 am

    #1369092

    1) sp_whoisactive

    2) glenn allan berry has great diagnostic collection script

    3) brent ozar has a good 'blitz' script

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • August 16, 2011 at 11:49 pm

    #1369414

    2) glenn allan berry has great diagnostic collection script

    3) brent ozar has a good 'blitz' script

    Thanks. Where can I find those scripts .?

  • August 17, 2011 at 12:03 am

    #1369415

    Joy Smith San (8/16/2011)

    2) glenn allan berry has great diagnostic collection script

    3) brent ozar has a good 'blitz' script

    Thanks. Where can I find those scripts .?

    From Google.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • September 6, 2011 at 4:32 am

    #1377270

    matt.newman (8/12/2011)

    I used a "cheap" method while I was waiting on Net to get Confio up and running. Do you have Excel 2010 with power pivot that you could query a table that has this data logged to it?

    You can create a job that calls a procedure that stores data to a log table and an excel sheet to graph it. Not much, but I used it when I had nothing else to go on. Need to know a bit of Excel pivot table action to get it looking nice. Otherwise, use perfmon and collect everything straight from the machine.

    Need to create a job, and the tables are in the script below. May want to put on a DB with simple recovery mode. Run the job every minute or four... if you want to go this way.

    create procedure [dbo].[uRecordStats]

    as

    begin

    /*-- Setup

    select top 0

    current_timestamp as dtTimeNow,

    @@total_write as inTotalWrites,

    @@total_read as inTotalReads,

    @@io_busy as inIOBusy,

    @@connections as inConnections,

    @@idle as inIdle,

    @@CPU_BUSY as inCpuBusy

    into

    ServerStats

    create clustered index [TimeNow] on ServerStats (dtTimeNow)

    */

    insert ServerStats

    select

    current_timestamp,

    @@total_write - isnull(sum(inTotalWrites),0),

    @@total_read - isnull(sum(inTotalReads),0),

    @@io_busy - isnull(sum(inIOBusy),0),

    @@connections - isnull(sum(inConnections),0),

    @@idle - isnull(sum(inIdle),0),

    @@CPU_BUSY - isnull(sum(inCpuBusy),0)

    from

    ServerStats

    where (select create_date from sys.databases where name = 'tempdb') < dtTimeNow

    -- there were no records found meaning the server was re-initialized

    -- put in our base measure records!

    if @@rowcount = 0

    begin

    insert ServerStats

    select

    current_timestamp,

    @@total_write,

    @@total_read,

    @@io_busy,

    @@connections,

    @@idle,

    @@CPU_BUSY

    end

    --sp_monitor

    /*-- Setup

    select top 0

    sn.dbid as siDbId,

    sn.fileid as siFileId,

    sn.NumberReads as biNumberReads,

    sn.BytesRead as biBytesRead,

    sn.IoStallReadMS as biIOStallReadMS,

    sn.NumberWrites as biNumberWrites,

    sn.BytesWritten as biBytesWritten,

    sn.IoStallWriteMS as biIOStallWriteMS,

    sn.IoStallMS as biIOStallMS,

    sn.BytesOnDisk as biBytesOnDisk,

    current_timestamp as dtTimeNow

    into

    ServerFileStats

    from

    fn_virtualfilestats(null,null) as sn

    create clustered index [TimeNow] on ServerFileStats (dtTimeNow)

    */

    insert ServerFileStats

    select

    sn.dbid,

    sn.fileid,

    sn.NumberReads - sum(so.biNumberReads),

    sn.BytesRead - sum(so.biBytesRead),

    sn.IoStallReadMS - sum(so.biIoStallReadMS),

    sn.NumberWrites - sum(so.biNumberWrites),

    sn.BytesWritten - sum(so.biBytesWritten),

    sn.IoStallWriteMS - sum(so.biIoStallWriteMS),

    sn.IoStallMS - sum(so.biIoStallMS),

    sn.BytesOnDisk - sum(so.biBytesOnDisk),

    current_timestamp

    from

    ServerFileStats as so

    join fn_virtualfilestats(null,null) as sn on

    so.sidbid = sn.dbid

    and so.sifileid = sn.fileid

    where (select create_date from sys.databases where name = 'tempdb') < so.dtTimeNow

    group by

    sn.dbid,

    sn.fileid,

    sn.NumberReads,

    sn.BytesRead,

    sn.IoStallReadMS,

    sn.NumberWrites,

    sn.BytesWritten,

    sn.IoStallWriteMS ,

    sn.IoStallMS,

    sn.BytesOnDisk

    -- there were no records found meaning the server was re-initialized

    -- put in our base measure records!

    if @@rowcount = 0

    begin

    insert ServerFileStats

    select

    sn.dbid,

    sn.fileid,

    sn.NumberReads,

    sn.BytesRead,

    sn.IoStallReadMS,

    sn.NumberWrites,

    sn.BytesWritten,

    sn.IoStallWriteMS,

    sn.IoStallMS,

    sn.BytesOnDisk,

    current_timestamp

    from

    fn_virtualfilestats(null,null) as sn

    end

    end

    Hi,

    Thanks for the script.

    For a take off this would be fine I believe. If you don't mind can you pls explain it a bit.

    I am somewot familiar with EXCEL.. knows pivot etc; Would appreciate you if you can explain the output a little bit more.

    Thanks again.

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

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