Journey to the PASS Summit 2011 – Part 7 – Day 2 Precon PowerShell

Posted on October 12, 2011 by Luke Hayler

Today was PowerShell day for me at the PASS Summit. After just 3 hours of sleep last night, I did not expect too much (from myself). However, the coffee was good and the realisation of a new skill was enough to keep me going.

It’s fair to say that, although the presentation was a little disjointed, I have seen the PowerShell ‘light’ and what it is capable of. I intend to follow up this precon with a hefting helping of new blogs to read and scripts to write.

The following are some of the notes from the precon today.

What is PowerShell?

According to Wikipedia, PowerShell is a task automation framework, that consists of a command-line shell and an associated scripting language. With PowerShell, administrators can perform administrative tasks on both local and remote machines.

Cmdlets
- Are specialised commands in the PowerShell environment that implement specific functions.
- Use a verb-noun naming pattern. i.e.
  - Get-Command
  - Get-Children
- Sets of commandlets can be combined into scripts and executables.
Pipeline
- PowerShell implements a pipeline, which enables the output of one cmdlet to be ‘piped’ as an input to another; this is done using the ‘|’ character.

Variables in PowerShell

Variables in PowerShell are declared (using ‘$’) and set in-line. There is no need to declare them up front, or set their data/object type. The variables will take on the appropriate data/object type for the result(s) that are returned. I don’t know about you, but I think that is all kinds of awesome.

PowerShell Profiles

These help to load up certain modules/snapins when you load up PowerShell. This means you can port your profiles to any environment and be able to load into a familiar setup.

Comparison operators

Operator	Description
-eq	Equal to
-ieq	Equal to (case-insensitive)
-ceq	Equal to (case-sensitive)
-ge	Greater than or Equal to
-gt	Greater than
-le	Less than or Equal to
-lt	Less than

Logical Operators

Operator	Description
-and	And
-ne	Not Equal to
-not	Not
!	Not
-or	OR

more operator notes over at http://ss64.com/ps/syntax-compare.html

PowerShell Resources – great for those just starting out in PowerShell

Journey to the PASS Summit 2001 – Part 6 – Day 0. Getting to Seattle.

Posted on October 12, 2011 by Luke Hayler

My ~~awesome adventure~~ looooooooooong frikkin’ journey to Seattle started well enough in Melbourne, Australia. Airport check-in was the start of the fun-and-games though. It took over an hour to actually check in and then I found out that the flight had been delayed by 2 hrs. Joy!

All was not lost though, as I had met Darren Gosbell (@darrengosbell) at the airport and we proceeded to have a drink and light lunch at one of the bars. We chatted about the PASS Summit quite a bit as well as the BI Maestro program/exams/certification. once we had boarded the plane, and we were underway the flight was reasonably enjoyable; as much as can be over the course of 14hrs.

My troubles started again in LAX (which is a dump btw). I progressed, slowly, through immigration, customs, picked up my bags and dropped them off at the baggage drop and then proceeded to check-in for my next flight to Seattle. Apparently I had no ticket for this flight. Huh. But it’s right there on my itinerary? 3 phone calls and over an hour later I was running to catch the Alaska Air flight. Thankfully I made it to the gate with 10 mins to spare. Which is all I cared about at that point.

The flight was only a couple of hours and I managed to dose off occasionally. When I was awake I was kept entertained by the interesting landscape of the West Coast. An I may have been dreaming when I saw this and thought “Sim City?”

The mountain below is, what I can only assume, Mount Rainier.

I also managed to snap this pic of Seattle right before we came in to land. I couldn’t quite believe it when it was a clear sunny day!

I’m in Seattle!

Journey to the PASS Summit 2011 – Part 5 – MUST have app ‘Guidebook’

Posted on October 10, 2011 by Luke Hayler

If you are one of the lucky ones who get to attend this year’s PASS Summit, then this is the Guidebook for your PASS Summit week. Literally – there’s an app called ‘Guidebook’. If you have an iPhone/Android smartphone. Guidebook is the awesomeness that organises your week at the Summit. No question.

When you download Guidebook and search for ‘PASS’: Bingo! There was the guide for the PASS Summit 2011. W00t! I downloaded the guide, and after having created my schedule for the week all on my phone, I figured you lot need to know about it too. The app and the guide, not my schedule; although if you really want that too then I guess I could share it with you. Just ask.

Here’s why you need Guidebook with the PASS Summit 2011 Guide:

Session Schedule builder
After hours activities
About Seattle
Maps (floor plans) of the Convention Centre
Twitter (for the #sqlpass hash tag)
Sponsor listings
Speak listings
Exhibitor listings
My Schedule

I have no idea who created the guide for the PASS Summit, but it’s great. Thank you!

If you are still not convinced, here are a couple of screen shots:

See? It’s awesome. Go get it.

Journey to the PASS Summit 2011 – Part 4 – My Pre Conference Session Picks

Posted on October 3, 2011 by Luke Hayler

This is Part 4 in a series I am writing about my journey to the PASS Summit 2011. If you missed the previous entries, you can read about them here [Part 1, Part 2, Part 3]. Follow the journey on twitter with the #j2pass2011, and feel free to use the tag for your own journey!

Pre Cons!

Choosing a pre conference session for Monday/Tuesday was rather difficult. Do you choose something completely new to learn, or do you learn something new about a subject area in which you are already fairly familiar? Not to be outdone by my own questions, I chose to do one completely new subject area and one deeper dive into something I am fairly good at (but know I could be even better). Here are my picks:

Keeping it local with Rob Farley (Blog | @rob_farley)– “Fixing Queries with Advanced T-SQL constructs”
Something new with Aaron Nelson (Blog | @SQLVariant) – “After 7 hours you won’t be able to shut up about PowerShell either”

It’s my belief that you can always improve the skills you already have and that’s why I’m going to Rob’s Pre-con. My T-SQL is pretty good, but I know there are areas that could do with some improvement. I hoping to get quite a bit out of this session. No pressure Rob!

I also believe that you should stretch yourself and try to learn something new every now and again. Especially when it comes to software or processes. Learning something new gives you a broader perspective and you’ll be better equipped to make the right decisions. This is why I’m taking Aaron’s Pre-con. I have only ever heard the great things about PowerShell can do. I’ll be looking to apply this new knowledge immediately for all the BI projects I work on.

T-SQL Script to Identify Space Used By Each Table in a Database

Posted on September 12, 2011 by Luke Hayler

There are often times that I need to do checks on the space used by databases on the server, and in particular, the tables in those databases. This is a script that I quickly put together that will return the following information for each of the tables in the database on which the script is run. The following information is returned:

Table Name
Row Count
Reserved Space (KB)
Data space used (KB)
Index size (KB)
Unused space (KB)

The above information is gathered using sp_spaceused. Now I’m sure that there are a number of other ways to get this information, but this was quick and easy to produce and use. If you have anything that you’d like to contribute, I’d love to hear it.

/***
    Script to identify the space used by each table in a database
    Information returned includes:
        - Table name
        - Record Count
        - Reserved Space (KB)
        - Data Size (KB)
        - Index Size (KB)
        - Unused Space (KB)
***/
 
/* Create a table variable to hold the values returned by sp_spaceused  */
DECLARE @Results TABLE 
(
      RowNum            INT IDENTITY(1,1)
    , TableName         VARCHAR(200)
    , Records           INT
    , ReservedSpace     VARCHAR(100)
    , DataSpace         VARCHAR(100)
    , IndexSizeSpace    VARCHAR(100)
    , UnUsedSpace       VARCHAR(100)
    , CheckDateTime     DateTime DEFAULT GETDATE()
)
 
/* Declare a variable to hold the Object Names */
DECLARE @ObjectName VARCHAR(200)
 
/* 
    Declare a cursor to loop through each of the tables in the database 
    and return their space used information. 
*/
DECLARE TableCursor CURSOR
FOR
    SELECT 
        ObjectName = TABLE_SCHEMA + '.' + TABLE_NAME
    FROM 
        INFORMATION_SCHEMA.TABLES
    WHERE
        TABLE_TYPE = 'BASE TABLE'
    
OPEN TableCursor
 
FETCH NEXT FROM TableCursor
INTO @ObjectName
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
    /* Execute sp_Spaceused for each table and load the results into the table variable  */
    INSERT INTO @Results 
    (
          TableName            
        , Records            
        , ReservedSpace        
        , DataSpace            
        , IndexSizeSpace    
        , UnUsedSpace
    )
 
    /* 
        Uncomment the @updateusage option if you want to ensure your stats are up-to-date
        However, this may take some time depending on the size of your database tables. 
        It is set to 'false' in sp_spacedused by default.
    */    
    EXEC sp_spaceused @ObjectName
    --, @updateusage  = 'true'        
    ;    
    
    FETCH NEXT FROM TableCursor
    INTO @ObjectName
END
 
CLOSE TableCursor
DEALLOCATE TableCursor
 
/*    
    Get the list of tables and their associated space usage data.
    Update to your preferred format
*/
SELECT
      TableName            
    , Records            
    , ReservedSpace        = CAST(SUBSTRING(ReservedSpace, 1, LEN(ReservedSpace)-3) AS INT)
    , DataSpace            = CAST(SUBSTRING(DataSpace, 1, LEN(DataSpace)-3) AS INT)
    , IndexSizeSpace    = CAST(SUBSTRING(IndexSizeSpace, 1, LEN(IndexSizeSpace)-3) AS INT)
    , UnUsedSpace        = CAST(SUBSTRING(UnUsedSpace, 1, LEN(UnUsedSpace)-3) AS INT)
FROM @Results
ORDER BY 
    TableName

Copy. Paste. Enjoy.

**UPDATE – 2011/09/14**

My friend Jim McLeod (Blog | Twitter) has informed me that there is a better way to get the space used and row count details for objects in your database. His script utilises the sysindexes table, which means that this is compatible across a wider set of SQL Server versions including 2000/2005/2008. His original script appears in the comments below. I have reproduced it here with a little formatting, commenting and an extra table to restrict the results to just *user* tables:

/***
    This script will generate a list of User tables
    and their associated space used and row count details.
***/
 
SELECT 
      SchemaName    = ss.name
    , ObjectName    = OBJECT_NAME(si.id)
    , ReservedMB    = reserved * 8/1024.0
    , DataMB        = dpages * 8/1024.0
    , RecCount      = rowcnt   
 
FROM 
            sysindexes si
INNER JOIN  sys.objects so  ON    si.id = so.object_id
INNER JOIN    sys.schemas ss    ON    so.schema_id = ss.schema_id
WHERE
/*    Limit the resultset to bring back Heaps & Clustered indexes only 
    (Heap = 0, Clustered Index = 1)
*/
    indid < 2        
/*    We only want to see the 'user' tables at this stage. 
    You can include System tables by adding 'S' to the xtype clause 
*/
AND type = 'U'        
ORDER BY 
    SchemaName,
    ObjectName

I’ve left the first script in the post for comparison. Thanks Jim!

Luke Hayler – SQL Server Developer

A blog about Business Intelligence and other personal interests

Category Archives: Luke Hayler