WIDBA

WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Thursday, October 4, 2012

Database Table Sizes in Powershell


Just wanted to share a very simple script that I have used far more than I ever thought I would.  The initial purpose was to get the average cost of a row of data, but this has been nice to find tables that are filling up quick, have a high row to space ratio, etc.



[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") "MySQLServerName"

$sqlServer.Databases | Where-Object{$_.Name -eq "BeerDB"} | 
    Select-Object Tables -ExpandProperty Tables | Sort-Object -Property RowCount -Descending  |
    Select Name, DataSpaceUsed , IndexSpaceUsed, RowCount,
         @{Label="TableSizeInMB";Expression={($_.DataSpaceUsed + $_.IndexSpaceUsed) / 1024}},
         @{Label="AvgSpacePerRowKB";Expression={($_.DataSpaceUsed + $_.IndexSpaceUsed) / $_.RowCount}}  | out-gridview -Title "SpaceUsed"

Wednesday, October 3, 2012

SQL Saturday 149 - Minnesota

A quick followup to SQL Saturday 149 at the University of MN.

First, my demos (the whole presentation is demos) are posted on the SQL Saturday site for anyone to play with.  The feedback was positive, my only non 4/5 scores made comments to the fact they didn't know Powershell, which is totally fine, given the session is labeled intermediate.

I had a few fascinating comments (ignoring someone taking a shot at my beard - Bob).

  1. Need more slides
  2. Refreshing!
  3. You are a natural speaker, its like you are having a conversation with me

Need more slides
1. I need to think about how I can put more "pictures" into my sessions I guess.  Currently, every picture is of beer bottles.  I don't like sessions where they just Powerpoint pictures the entire time when the session is about using a technology, like mine.  So I don't use Powerpoint at all, and just have lots of dialogue around scripts I have created or borrowed and their practicality.

Refreshing
2. This is what I am going for all the time - something useful, different and not too boring.

You are a natural speaker, its like you are having a conversation with me
3. Funny how I always feel nervous and think it is interesting that I seem to be watching myself speak while I am up there.  I get a little better each time, but comments like that throw you for a loop when you don't see yourself that way.  Very flattering, even when it comes from someone that has to work with you :)

I was able to learn some good information on Powerview, Index Stats and Dimensional Modeling and visit with several friends and some familiar faces, including coworkers past and future.  I helped with the Powershell lunch on a stick and we had some nice discussions.  I need to do a better job of facilitating discussions and talking to more people.  I only got a chance to say hi to some of the real luminaries in our industry and that is a bummer.

All in all, Mr @MNDBA, my opposite state counterpart in Twitter names, did a great job organizing and asked me specifically not to correlate the seedy motel I stayed at with SQL Saturday 149.  It was a Super 8 in Roseville that has a very colorful review - all I can say is I was happy no one stole my new tires :)  Note to self, if you need a hotel, don't wait till the day before to book it, you may end up in a place that actively advertises the fact your vehicle is not safe and there might be solicitation inside for services.