Monitoring on a Budget

  • Comments posted to this topic are about the item Monitoring on a Budget

    Take care,

    Bert

    "Speculations? I know nothing about speculations. I'm resting on certainties. I know that my Redeemer lives, and because He lives, I shall live also." - Michael Faraday

  • This is a nice write-up. I know a few folks who could use this. On a related note, how are you moitoring the performance counters outside of SQL Server, such as CPU usage, pages/sec, etc.? Or are you strictly concentrating on SQL Server?

    K. Brian Kelley
    @kbriankelley

  • To be quite honest, I hadn't thought of monitoring counters outside of SQL Server. You've sparked my interest.

    We did add code to track disk space when we were trying to justify additional SAN space. The overall process is the same; simply capture today's disk utilization and subtract yesterday's from it to get a daily increase/decrease.

    Take care,

    Bert

    "Speculations? I know nothing about speculations. I'm resting on certainties. I know that my Redeemer lives, and because He lives, I shall live also." - Michael Faraday

  • That calls for a part 2, Bert!

    πŸ˜€

  • You're right. I really need to spend some time converting this over to SQL 2005 and expanding it beyond SQL Server.

    Take care,

    Bert

    "Speculations? I know nothing about speculations. I'm resting on certainties. I know that my Redeemer lives, and because He lives, I shall live also." - Michael Faraday

  • That looks very nice BUT where is that Excel spreadsheet then? πŸ˜‰

    It would be really nice if you posted here also so we can have a look at final product... πŸ™‚

    And to be honest I am not very good and Excel and all that graphs stuff so it would be rather nice to see how you're doing the whole "thing"

    Cheers

  • I debated putting the Excel spreadsheet in the article. Ultimately, I decided not to since, once the raw data is in a table, you can present in pretty much any format you choose. I'm not very good with Excel either, but give me a few days, and after the holiday, I can post a "How I did it with Excel" follow up. I've also been working with a SQL 2005 equivalent. The internals are pretty much the same, but the presentation has much more potential with SSRS and SSAS...Perhaps another followup article?

    This was my first stab at technical writing. I've already learned a great deal from your responses, thank you! I'll definitely try to be more detailed in the future.

    Take care,

    Bert

    "Speculations? I know nothing about speculations. I'm resting on certainties. I know that my Redeemer lives, and because He lives, I shall live also." - Michael Faraday

  • I thought you can't query temp table as source in Transform Data task as stated here

    When using temporary tables in the Transform Data task, the Data Driven Query task, or the Execute SQL task in DTS Designer, be aware that you cannot use a Transact-SQL statement or stored procedure that calls a temp table as your source.

  • I mean at source tab in the Transform Data Task for DB Stats monitoring with the TSQL:

    select * from tempdb.dbo.DayDBStat

  • Perhaps that restriction pertains to a #TEMP table? All I know is the DTS works as coded.

    Take care,

    Bert

    "Speculations? I know nothing about speculations. I'm resting on certainties. I know that my Redeemer lives, and because He lives, I shall live also." - Michael Faraday

  • Thanks for the article Bert.

    I don't often use DTS so I went ahead and did the same thing in SSIS. I followed the article just as it is written without much hassle. The only real snag I had was to create a connection to the tempdb in the connection manager (this is interesting as I had never thought of it before and has helped solve another problem I was having). I have only tested it on my development server, but so far it seems to work great.

  • Bert Carles (11/20/2007)


    Perhaps that restriction pertains to a #TEMP table? All I know is the DTS works as coded.

    I still getting error invalid object name 'tempdb.dbo.DayDBStat' at the same Transform Data Task when I click on the transformation tab. I couldn't understand why?

    My SQL2000 version is 8.00.2187.

  • Bert Carles (11/20/2007)


    Perhaps that restriction pertains to a #TEMP table? All I know is the DTS works as coded.

    I finally figure it out.

    Somehow, the DTS Transform task won’t auto detect and populate the column names or types in the transformation tab unless it can find the physical table in the source.

    Here's the tricks, go to QA and connect to the source DB instance and run this:

    CREATE TABLE tempdb.[dbo].[DayDBStat] (

    [SrvName] [varchar] (50) NOT NULL ,

    [DBName] [varchar] (128) NOT NULL ,

    [LogFileName] [varchar] (128) NOT NULL ,

    [SampDate] [datetime] NOT NULL ,

    [ReadQty] [bigint] NOT NULL ,

    [WriteQty] [bigint] NOT NULL ,

    [ByteReadQty] [bigint] NOT NULL ,

    [ByteWriteQty] [bigint] NOT NULL ,

    [IOStallMSQty] [bigint] NOT NULL

    ) ON [PRIMARY]

    GO

    this will create a perm table in tempdb.

    Now go back to the DTS Transform task and type

    select * from tempdb.dbo.DayDBStat

    and click the transformation tab and da da... it works.

  • That makes sense. I've run into that myself before.

    Take care,

    Bert

    "Speculations? I know nothing about speculations. I'm resting on certainties. I know that my Redeemer lives, and because He lives, I shall live also." - Michael Faraday

  • Hi

    Where's that so promised Excel spreasheet that you talked about? πŸ˜‰

    The numbers in the tables don't mean anything without a proper explanation and Excel sheet as you've promised πŸ˜‰

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

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