SQL Server 2k8 Management Data Warehouse

One of the new features in SQL Server 2k8 is the Management Data Warehouse.  This feature is made up of a data collector, storage, and execution that collects data about the particular databases within the server into a reporting warehouse.  This data is stored in the msdb and also in management data warehouse databases that are created when the Data Collection is configured.  The warehouse uses various pieces of SQL Server such as SSIS and other components also.  One of these components is the dcexec.exe file that is responsible for loading and executing SSIS packages that are part of this collection set.

Keep in mind, that there is an API for this warehouse also for extension of the functionality.

The following are the steps for setting up a basic management data warehouse.

Right click on Data Collection and you’ll see the menu below.

Select Configure Management Data Warehouse.

On this screen I had to actually come back and use the New button to create a new database to use to host the warehouse.  My suggestion is to NOT use an existing database and instead create a new database.

Just leave these unchecked unless you know that one of these accounts need access.

Now that this is setup for the warehouse host we’ll setup the data collection.

The cache directory will default if a path is not entered, but I’ve entered one just because I felt the inclination to do so.

The screen shot below, which you might want to click on just to review with a full size shot, shows what is now available in SQL Management Studio.  You’ll see the collection sets, and also many new jobs setup to perform the collection of data for the warehouse.

After setting up the collection click on the Disk Usage option, right click, then select properties.

On this screen select the T-SQL that is in the input parameters text box.  The code is shown below for ease of copy n’ pasting.  🙂

DECLARE @dbsize bigint
DECLARE @logsize bigint
DECLARE @ftsize bigint
DECLARE @reservedpages bigint
DECLARE @pages bigint
DECLARE @usedpages bigint

SELECT @dbsize = SUM(convert(bigint,case when type = 0 then size else 0 end))
      ,@logsize = SUM(convert(bigint,case when type = 1 then size else 0 end))
      ,@ftsize = SUM(convert(bigint,case when type = 4 then size else 0 end))
FROM sys.database_files

SELECT @reservedpages = SUM(a.total_pages)
       ,@usedpages = SUM(a.used_pages)
       ,@pages = SUM(CASE
                        WHEN it.internal_type IN (202,204) THEN 0
                        WHEN a.type != 1 THEN a.used_pages
                        WHEN p.index_id < 2 THEN a.data_pages
                        ELSE 0
                     END)
FROM sys.partitions p
JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT JOIN sys.internal_tables it ON p.object_id = it.object_id

SELECT
        @dbsize as 'dbsize',
        @logsize as 'logsize',
        @ftsize as 'ftsize',
        @reservedpages as 'reservedpages',
        @usedpages as 'usedpages',
        @pages as 'pages'

.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, “Courier New”, courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

When you run this the query should give you the results as shown.

One of the sample reports, which won’t have the trend graphs until data is processed multiple times.

In summary.  This feature of SQL Server is really helpful for development to keep track of growth, performance, and other characteristics.  I strongly suggest checking out this reporting capability of SQL Server 2008.

kick it on DotNetKicks.com

Shout it

Ubuntu, VMWare, Environments

I finally got all my virtual image loads setup and my Ubuntu Install is running smoothly again.  I also got a Windows 2008 install running and hope to use it to try out some WAS, Velocity, and other WCF related features.

In addition to that I’ve got my work in progress up and running at http://mystuff.adronbhall.com if you want to check out my story list application.  This sub domain is literally the work in progress, so don’t expect much of anything here.

So far that’s been my weekend.  I’ve also had an onslaught of allergies kick me in the face.  This is never fun, even when I’m trying not to do anything constructive, and I was working on the exact opposite of that.  Argh!

Next step, Mono, getting Silverlight to run on Mono, and a few other tasks.  Then back to Cockatoo, HighBall, and MyStoryList (which has no link, as I’m not sure what I’m going to do with this project yet).

I’m pondering doing an updated tools, trips, and tricks list of my current environment, but right now I’m still putting the finishing touches on it all.  So maybe that is a Monday entry.

ASP.NET MVC Story Application

NOTE:  This is not forgoing my other project: Cockatoo &** **HighBall****.

The story starts like this.  I’ve been using some, what will go unnamed, Agile Project Management Software that I just wasn’t digging.  It was too restrictive and focused on the tool over the developers.  Several times the tool literally got in the way of development and forward progress.  Whenever this happens one should see a MASSIVE RED FLAG that the core manifesto concepts are broken.  When those are broken in such a brash and obvious way then the project is in threat of serious risks.  So I set out to fix this with simpler, more versatile, easy to use software that could replace the need for this tool.

I started out writing this software with the standard user story concept, to keep track of user stories.  That sounds kind of odd that I’m creating user stories to describe an app that will track user stories as a story.

:o  wowzerz!

So here we go.  Here are the initial user stories for minimally functional and working software.

  1. A user can write a story.
  2. A user can read the stories that have been written.
  3. A user can change a story.
  4. A user can throw away a story.

Those simple stories make sense to me.  With that I?ve started and ensuing entries will be forth coming.

Another Day o' Code… …Maybe

I’m writing this blog entry at various intersections during the day to try out keeping a real ‘log’ of the things I accomplish.  I’ve always had trouble keeping up with the past, and when it comes time for reviews and such I usually draw a blank of what has been done.  It appears this is the crux of an Agile mindset always being geared toward the future and the next stories coming off of the back log.  So we’ll see how this goes.

9:20am After a morning coffee, reading e-mail, catching up on communications, and all that regular morning activity I’m diving into the first code bits of the day.  I had done a search earlier about ASP.NET MVC task list applications and pulled up a stackoverflow entry.  This caused me to log in and actually see if there was anything I could throw an answer down for.  So far, nothing in particular, so onward with the code bits.

9:28am Had to drop code to step up to bat for a deployment.  One of the cool things about Agile projects is that at any moment ‘ WHAM ‘ off to production the code goes.  We don’t have all the bits in exactly the right place so we have to manually manipulate a few bits before it moves to production.  (bits being a metaphorical descriptor)

9:49am Back to code?

9:50am Needed some things from the MSDN subscription.  Well lo and behold it is HeldAwaitingReactivation which is a bummer.  I’ll have to get with IT and get that renewed or maybe I can do it?  Then I clicked on what appears to be another MSDN subscription and it is active.  Why even default to an inactive subscription?  I’m not really following the logic (if it exists) there.  Hmpf, as I started to download stuff, I realized MS has broken Chrome compatibility or something because it isn’t launching right.  I suppose I’ll open IE.  🙁

MS please fix the fact that there is still active X stuff on your site(s).  K THX.

10:08am Finally got stuff I needed downloading after installing all the active X crap.  Now to try and get some focus on this code’  arrgh.

10:30am Stand up’

10:52am Stand up done, getting back to code in a few minutes.

11:08am Team took a break to watch a video & a bit o’ camaraderie.  Hey, one can’t code nonstop.  🙂  Got about 15 minutes of code review finally done.

11:28am Going for lunch!

12:26pm Returned from lunch, diving back into code and returning e-mails.

1:27pm Actually got to start working on code again.  Hopefully will be able to maintain this for a while without interruptions.

2:10pm Got to a point and realized I didn’t have the latest ‘manually copied’ assemblies and had to move them back into the project.  Wasted 20+ minutes.  Beginning the process of getting the files and fixing the problem.

2:38pm Got the files, project building and executing again properly.  Finally got to adding the feature I’ve been trying to work on since this morning.

3:43pm Actually taking a short break after making good progress.  Almost done with adding attribute(not the kind you might think, proprietary in nature) support for some reporting.  Things are looking better after a morning of not getting anything done.

4:18pm Finally completed the feature addition, now submitting info for tracking the feature addition and then will pack for dinner time.  Will then code some more tonight, I hope.

5:02pm Out of office for the day.

Now that I’ve written this, I realize the day has been a slow one, which is by no means the average.  I’m hoping to be in the code and at task (or in the story) a much more significant part of the day tomorrow.

Maybe I’ll be able to keep up with my daily summaries, or maybe not.  It was a little bit of effort beyond the normal to keep this one going.  Overall I did get the feature request added and managed to help out a number of individuals with their efforts throughout the day.  I’d say, even with it being a slow to get going day, it has been a good day.  However, even with the feature request, no code to post or nothing particularly new learned.  I’ll have to make a point tonight to learn something new.

Schedules, Tickets, and Analysis? HighBall Thoughts

OLAP?

I got to thinking about my HighBall Project and thought, wouldn?t it be awesome to use some of my know how to wire up some serious cubes and analyze the results from site usage all the way to scheduling analysis?  It?s an idea, but I?m not really sure how I would do it yet.  I do however have some ideas in regards to scheduling and how a nice cube could be used to analyze effective scheduling usage.  Sounds like something TriMet could even use ? if they don?t already do these types of analysis.  😉

Here?s my thought.

Take a schedule as the time dimension.  That?s simple enough.  Now take X number of routes as opposing dimensions and use ridership counts, peak load, etc as the fact table sums and such.  The data should align accordingly to the apex of routes and concentration of route needs by riders.  This type of data could be used to find out where ridership peaks and drops and how to fill gaps or even where to increase service.

?not really sure, got a ways to go before I try it, but it is an interesting thought for analysis.  Eventually I?ll give it a shot.