Webcast Tomorrow!

I’m excited to be doing a webcast tomorrow with the infamous illustrious Brent Ozar for Quest’s Pain-of-the-Week. The title is “Getting Started with SQL Server Management Studio,” and as you’ve probably gathered, it’s pretty entry-level stuff. If you read my blog, then chances are you don’t need to watch this webcast. But if you know anyone who’s trying to learn SQL Server or is trying to make the upgrade from 2000 to 2005/2008, this may be a good webcast for them.

I’ve also got a few other speaking engagements coming up:

June 2nd: Cedar Valley .NET User Group
I’ll be reprising my Iowa Code Camp presentation on “SQL Server for the .NET Developer” for CVINETA. This presentation focuses on what you need to know about good table design, indexing strategies, and fragmentation… you know, what you wish every .NET developer knew about SQL Server. 🙂

June 11th: PoTW: Time-Saving SQL Server Management Studio Tips & Tricks
I’ll also be doing this webcast with @BrentO as a follow-up to our webcast tomorrow. It will focus on how to save time and improve your sanity by using some neat little tricks in SSMS 2008.

PoTW Webcast Materials

For those interested, here’s the links I mentioned during my presentation:

… and here’s the scripts that I used during my presentation:

Use sandbox;
/* Create a table to promote fragmentation */
Create Table dbo.fragmented
    myGuid uniqueidentifier Not Null
    Constraint PK_fragmented Primary Key Clustered (myGuid)
/* Populate our table */
Set NoCount On;
Declare @iterations int = 0;
While @iterations < 250000
    Insert Into dbo.fragmented
    Select NewID();
    Set @iterations += 1;
/* Verify the number of records in our table */
Execute sp_spaceused 'dbo.fragmented';
/* Let's take a look at sys.dm_db_index_physical_stats */
Select Object_Name(ddips.object_id) As 'tableName'
    , i.name As indexName
    , ddips.*
From sys.dm_db_index_physical_stats (DB_ID(), Object_ID(N'fragmented'), NULL, NULL , 'DETAILED') As ddips
Join sys.indexes As i
    On ddips.object_id = i.object_id
    And ddips.index_id = i.index_id;
Select *
From sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'fragmented'), NULL, NULL , 'LIMITED');
/* ShowContig should return us the same information */
DBCC ShowContig ('fragmented');
/* ExtentInfo is an undocumented command that will show us the number of allocated extents */
DBCC ExtentInfo (0, 'fragmented', 1);
/* Grab our page number */
    , i.name AS 'indexName'
    , p.partition_number
    , au.type_desc
      CONVERT (INT, SUBSTRING (au.first_page, 6, 1) +
         SUBSTRING (au.first_page, 5, 1))) +
   ':' + CONVERT (VARCHAR(20),
      CONVERT (INT, SUBSTRING (au.first_page, 4, 1) +
         SUBSTRING (au.first_page, 3, 1) +
         SUBSTRING (au.first_page, 2, 1) +
         SUBSTRING (au.first_page, 1, 1))) AS 'firstPage'
      CONVERT (INT, SUBSTRING (au.root_page, 6, 1) +
         SUBSTRING (au.root_page, 5, 1))) +
   ':' + CONVERT (VARCHAR(20),
      CONVERT (INT, SUBSTRING (au.root_page, 4, 1) +
         SUBSTRING (au.root_page, 3, 1) +
         SUBSTRING (au.root_page, 2, 1) +
         SUBSTRING (au.root_page, 1, 1))) AS 'rootPage'
      CONVERT (INT, SUBSTRING (au.first_iam_page, 6, 1) +
         SUBSTRING (au.first_iam_page, 5, 1))) +
   ':' + CONVERT (VARCHAR(20),
      CONVERT (INT, SUBSTRING (au.first_iam_page, 4, 1) +
         SUBSTRING (au.first_iam_page, 3, 1) +
         SUBSTRING (au.first_iam_page, 2, 1) +
         SUBSTRING (au.first_iam_page, 1, 1))) AS 'firstIAM_page'
FROM sys.indexes AS i
JOIN sys.partitions AS p
    AND i.index_id = p.index_id
JOIN sys.system_internals_allocation_units AS au
    ON p.hobt_id = au.container_id
ORDER BY tableName;
/* View our pages */
DBCC TraceOn (3604);
DBCC Page (sandbox, 1, pageNumberHERE, 3);
DBCC TraceOff (3604);
/* Rebuild our index */
Alter Index PK_fragmented On dbo.fragmented REBUILD;
/* Clean-Up! */
Drop Table dbo.fragmented;

Thank you for watching the webcast! 🙂


Source: http://sqlfool.com/2009/03/potw-webcast-materials/

PASS Update


Hi folks. If lately you’ve noticed a few less blog posts than normal, it’s because I’m spending a lot of time starting up a brand new PASS Chapter. In fact, we’re only one week away from our first meeting! For those who haven’t been following the updates, we’re starting a PASS Chapter in east Iowa (Cedar Rapids, Iowa City, Davenport, etc.). Our first meeting will feature a presentation by Brian Duhn, Senior DBA, MCITP, on server-to-server service broker.

Any and everyone is invited to join us. More information can be found on http://380pass.org.

Pain of the Week Webcast

I’ve also been asked by Quest Software to give a webcast on index fragmentation next week. The webcast will air on Thursday, 12 March 2009, at 10AM CT. This will be my first webcast, so be gentle! More information can be found on Quest’s Pain of the Week website.

SQL Challenge

If you haven’t heard of it yet, Adam Machanic has posted a T-SQL challenge. Check it out… the winner gets a 1-year subscription to MSDN!