Monitoring on a Budget

Question

Monitoring on a Budget

Bert Carles

SSCrazy

Points: 2634
More actions
November 18, 2007 at 12:02 am

#377083

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

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

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

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

Bert Carles SSCrazy Points: 2634 More actions · Answer 2

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

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 3

Steve Jones - SSC Editor

SSC Guru

Points: 736172

November 19, 2007 at 9:26 am

#752359

That calls for a part 2, Bert!

😀

Bert Carles SSCrazy Points: 2634 More actions · Answer 4

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

giorgio-459258 SSC Enthusiast Points: 155 More actions · Answer 5

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

Bert Carles SSCrazy Points: 2634 More actions · Answer 6

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

Garrick-249518 Ten Centuries Points: 1346 More actions · Answer 7

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.

Garrick-249518 Ten Centuries Points: 1346 More actions · Answer 8

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

select * from tempdb.dbo.DayDBStat

Bert Carles SSCrazy Points: 2634 More actions · Answer 9

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

Nolan Schoonraad SSC-Addicted Points: 410 More actions · Answer 10

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.

Garrick-249518 Ten Centuries Points: 1346 More actions · Answer 11

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.

Garrick-249518 Ten Centuries Points: 1346 More actions · Answer 12

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.

Bert Carles SSCrazy Points: 2634 More actions · Answer 13

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

giorgio-459258 SSC Enthusiast Points: 155 More actions · Answer 14

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 😉