Showing posts with label SQL Rally. Show all posts
Showing posts with label SQL Rally. Show all posts

Thursday, June 9, 2011

SQL Rally Q & A: Page Compression, Heaps, and Forwarding Pointers



One of my favorite parts of presenting is the questions you get.  It is always nice to hear the questions people have, because it helps you to expand your knowledge.  I got three questions after my presentation that lead to really nice follow up conversations, and I felt each answer deserved a blog.  One was from SQL MVP Louis Davidson (@DrSQL|Blog)

The question was, and I’m paraphrasing “Does the behavior Forwarding Pointers change in Heaps?”  The short answer is no, and Dr. SQL knew this but was still kind (and smart) enough to ask, but Dear Reader let’s prove it out just for fun.

THERE’S NO DATA HERE JUST POINTERS


Yesterday I wrote about Heaps & Compression, this was to give a bit of an introduction to this topic.  So as we covered yesterday when a Page on a Heap has Page Compression applied to it, subsequent inserts are not in a Page Compressed format, there are three exceptions to this for Bulk Insert, Insert Into with (TABLOCK) specified, or when Rebuilding a table specifying Page Compression.   *Actually it is 4, after you Rebuild your Heap once specifying Page Compression, you can rebuild it after and it will be Page Compressed, unless you specify a different type of Compression.

Let’s start off by creating our Demo Database.

USE master
GO
IF EXISTS(SELECT * FROM sys.databases WHERE name='demoCompressionINTERNALS')
BEGIN
    DROP DATABASE demoCompressionINTERNALS
END
CREATE DATABASE demoCompressionINTERNALS
GO
USE demoCompressionINTERNALS
GO

Now let’s add 460 rows of Data maxed out so that we fill our uncompressed pages.  I’m adding so many rows because I’ve optimized these records for Compression and I want to pack my Page tight so I can guarantee that I will get a Forwarded Record.

DECLARE @i INT, @myID INT
SET @i=0

WHILE (@i<460)
BEGIN
         SET @myID=(SELECT (MAX(myid)+1) FROM dbo.heap1)


         INSERT INTO dbo.heap1(myChar1, myChar2)
         VALUES(
                  (REPLICATE('a', (100- LEN(@myID))) + CAST(@myID AS VARCHAR(3)))
                  ,(REPLICATE('b', (500- LEN(@myID))) + CAST(@myID AS VARCHAR(3)))
             )

    SET @i=@i+1
END

Set Trace Flag 3604 on, so we can use DBCC Page with output to SSMS later.  And here’s a DBCC IND to view our Pages.

DBCC TRACEON(3604)
GO
DBCC IND(demoCompressionINTERNALS, 'heap1',1)

Now let’s apply Page Compression.

ALTER TABLE dbo.heap1
REBUILD WITH (DATA_COMPRESSION=Page)
GO
Here’s another DBCC IND to view our new Pages.

DBCC IND(demoCompressionINTERNALS, 'heap1',1)

If we do a DBCC Page we see Page 170 is empty and Page 224 has our records.  Let’s do a quick view to see the our Page Headers to validate the space.

Page  170
m_pageId = (1:170)                   m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 28     m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594039762944                                
Metadata: PartitionId = 72057594038845440                                 Metadata: IndexId = 0
Metadata: ObjectId = 2105058535      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 4                          m_slotCnt = 0                        m_freeCnt = 8096
m_freeData = 96                      m_reservedCnt = 0                    m_lsn = (50:93:13)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0                      
Allocation Status
GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED              
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                         DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

Page  224
 
m_pageId = (1:224)                   m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x80                m_level = 0                          m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 28     m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594039762944                                
Metadata: PartitionId = 72057594038845440                                 Metadata: IndexId = 0
Metadata: ObjectId = 2105058535      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 4                          m_slotCnt = 460                      m_freeCnt = 5
m_freeData = 7267                    m_reservedCnt = 0                    m_lsn = (50:93:65)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0                      
Allocation Status
GAM (1:2) = ALLOCATED                SGAM (1:3) = NOT ALLOCATED           PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED                 ML (1:7) = NOT MIN_LOGGED           

So now let’s update Row 4, a rewrite will place this data in a Row Compressed Format.

UPDATE dbo.heap1
SET myChar1 = (REPLICATE('c', 99) + CAST(4 AS VARCHAR(1))),
     myChar2 = (REPLICATE('d', 499) + CAST(4 AS VARCHAR(1)))
WHERE myID=4

Let’s take a look at Page 224 again, and we’ll see that a forwarding record is in place of our Data.

DBCC PAGE('demoCOMPRESSIONInternals', 1, 224, 3)
GO

Slot 3 Offset 0x1c59 Length 9

Record Type = (COMPRESSED) FORWARDING_STUB                                Record size = 9

CD Array

Record Memory Dump

63FBDC59:   09aa0000 00010000 00†††††††††††††††††    ª.......               
Forwarding to  =  file 1 page 170 slot 0

And it tells us right there, if we look on Page 170 then we see there is our Data Record, sitting in Slot 0.  So let’s look at page 170.

m_pageId = (1:170)                   m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8
m_objId (AllocUnitId.idObj) = 28     m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594039762944                                
Metadata: PartitionId = 72057594038845440                                 Metadata: IndexId = 0
Metadata: ObjectId = 2105058535      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 4                          m_slotCnt = 1                        m_freeCnt = 7474
m_freeData = 1336                    m_reservedCnt = 0                    m_lsn = (50:171:6)
m_xactReserved = 0                   m_xdesId = (0:1514)                  m_ghostRecCnt = 0
m_tornBits = 0                      

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED              
PFS (1:1) = 0x61 MIXED_EXT ALLOCATED  50_PCT_FULL                         DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED


Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 1

myID = 4                            

Slot 0 Column 2 Offset 0xc Length 100 Length (physical) 100

myChar1 = ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc4

Slot 0 Column 3 Offset 0x70 Length 500 Length (physical) 500

myChar2 = ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd4

So the short answer is that it doesn’t, change the way that Forwarded Records behave, but it sure is a lot of fun to play around with.

Thanks,

Brad

Tuesday, May 17, 2011

TO THE ORGANIZERS OF SQL RALLY THANK YOU!


SQL Rally is over, and before we get too far away from it, I just wanted to say a quick Thank You.  

There was a lot of work done to pull off the SQL Rally.  It was over 1 year in the planning, and it showed.  I’m still relatively new to Florida, I moved here not even a year ago.  And when I did, I had no idea what a rich SQL Community it had as a state.  There are over 12 PASS Chapters in the state of Florida, an average of at 5 SQL Saturday’s a year, and this year the Inaugural SQL Rally.

Going to these events you see a lot of the same people time and time again.  Kendal Van Dyke (@SQLDBA|blog), Gareth Swanepoel (@GarethSwan|blog), Pam Shaw (@PamShaw), Jack Corbett (@UncleBigUns|blog), Karla Landrum (@KarlaKay22|blog), Rodney Landrum (@SQLBeat|blog), Jorge Segarra (@SQLChicken|Blog), Adam Jorgensen (@adam_jorgensen|blog), Andy Warren (@SQLAndy|blog), and this is the second time in less than a year I’ve seen Louis Davidson (@DrSQL|Blog) down in Florida volunteering his time, VA Beach or Tennessee, either way that’s a long way to travel!


If we could group all of these folks in a room, and I’m sure there are even more that I’ve left out, and give them a standing ovation it wouldn’t be enough.  When you take the time to speak, it takes a lot of preparation, planning Demo’s, Slide Decks, and learning, time that it takes outside of work and from your family. 

When you are planning an event like this, coordinating the speakers, finding the venue, getting sponsors, working on Logo’s, Websites, brochures, marketing, meetings, planning nightly activities, pre-con’s, the list goes on and on, you do a lot more.  It takes a lot more, and that is a lot less time you have with your family.



When you see the pictures that Kendal Van Dyke posted on his flickr account, the first couple pictures you see show the work being done while you are on the verge of the event.  Jack, Andy, and Kendal all had really great blogs leading up to the Rally, but nobody is there taking pictures when you have a planning meeting, nobody is handing out swag for time spent weeding out the venues, when you negotiate a deal for rooms, or get funding there is no crowd to break out in applause.

We attend these events because we are passionate about SQL, we are all striving to share with one another to better ourselves and each other.  The people that put on these events, they love this at a whole different level.  I don’t think they get to hear “Thank You”, nearly enough.  Maybe I'm wrong, I hope they do.

So this is the only way I can think to do it.  If you had a hand in Volunteering Thank You, If you had a hand in putting together a Session or a Pre-Con Thank You, and for all the people that put in their time, were away from their families, and made the big picture come together Thank You.

Send them a message on Twitter, go to their blog, or shoot them an email and just say Thank You. I had a blast, I can’t wait to do it again, and I really appreciate all the hard work.  So one more time, because I could never say it enough, Thank You for all you do so that we can learn.

Thanks Again,

Brad

SQL RALLY SLIDE’S & DEMO’S


Hello Dear Reader, I’ve posted the Slides & Demo’s from my SQL Rally Presentation over on my Resources Page.  You can Click Here to download them, as well.  SQL Rally had a lot of great learning and a lot of great presentations.  Head over to the SQL Rally website and view a list of Blogs and Pictures from attendees of the Event!


Thanks,

Brad

SQL Rally Day 2: The Home Stretch

After a fantastic Day 1, and a great night of bonding over SQL Karaoke, the morning of Day 2 was full of promise.   

After a quick breakfast, and a Starbucks my friend Dan Taylor (@DBABulldog | Blog) and I headed over to the beautiful Marriot World Center and SQL Rally Day 2.  

Arriving we quickly caught up with Andy Warren (@SQLAndy|Blog), Jorge Segarra (@SQLChicken|Blog), Kendal Van Dyke (@SQLDBA|Blog),  Jason Strate (@StrateSQL|Blog), and 1 half of my future Law Firm, Bigun’s & Balls, Jack Corbet (@UncleBiguns|Blog).

We traded notes on Day 1, and the night that followed.  As you can see some incredibly talented loud Doppelgangers were out last night impersonating Jorge, Jason, and me.  We would be offended if they weren’t so damn handsome.  The Incredible Picture's taken by Tim Mitchell (@Tim_Mitchell | Blog | Flickr)

 “But Balls,” you say, “What about the sessions?”

Glad you asked Dear Reader, and Away We Go!

PERFORMANCE TUNING ANALYSIS SERVICES BY DEVIN KNIGHT



A lot of my focus this week was on BI.  It is not a subject that I’ve ventured into a lot, but not due to lack of interest.  Devin (@Knight_Devin|Blog) was one of the speakers in the Pre-Con I attended, and as much as I’m looking forward to using Analysis Services, as a DBA in our world it will only be a matter of time before you need to tune it.  Devin is a practiced speaker that is very at ease in front of a crowd, he makes presenting look deceptively easy, which is a sign of a truly great presenter.

Devin started by discussing Parent Child relationships in Dimensions and how you can have a variable number of levels, and possibly recursion.  In those cases when you know the number of layers that you require you should attempt to flatten out your tables to avoid such recursion.

He discussed troubleshooting techniques, making comparisons to DBA tuning work to enable those of us without BI experience to make an easy transition in understanding the technology.  When Devin hears there are performance issues we should double check our Attribute Relationships, the same way we would Indexes on a poorly performing query.

He went on to discuss the Formula Engine and the Storage Engine, how each cache’s data, and the Multi-Threaded nature of the Storage Engine vs. the sometimes Single Threaded nature of the Formula Engine.  Partitioning was discussed as well as when we would want to apply partitioning, and how as a Best Practice SQL Partitioning and Cube Partitions should be aligned.

Next we reviewed Aggregations, when we should build them.  HINT*At the end of Development, after your Dimensions are complete.  Make sure to cleanup non-needed columns as they will cause additional overhead that you could avoid to make your cube more efficient.

SQL SERVER STORAGE ENGINE UNDER THE HOOD: HOW SQL SERVER PERFORMS I/O BY THOMAS GROHSER

I remember seeing Thomas (@TGrohser) present on the topic of NUMA, Non Uniform Memory Access, during the Fall 24 hours of PASS in 2009.  He is an extremely knowledgeable presenter, and has used SQL Server in some amazingly large ways. 

Thomas discussed the 3 Characteristics for measuring I/O’s Capacity, Throughput, and Latency.  He discussed how outstanding writes to the Transaction log where handled in different bit sizes in 2000, 2005 (x86), 2005 (x64), and 2008.  We discussed how the gradual increase would affect the performance of systems, and it gives DBA’s another tool to use when discussing the infamous “WHY” question that always accompanies the “Upgrade” conversation.

ZERO TO OLAP CUBES IN 60 MINUTES BY ADAM JORGENSEN FEAT. JULIE SMITH

Adam (@Adam_Jorgensen) walked we the audience through creating a cube in 60 minutes.  But we don’t just create a Cube Dear Reader, we build reports against it as well.  One of the biggest things that I got out of this weekend is that BI is not difficult, but it takes practice.  Once you begin to understand it, and use it, like any other skill we have, it will get sharper.  The first step is often the hardest, but Adam showed us that we should jump in and start.

His hand suffering from a wound, opening a box at his office defending Nun’s and Orphan’s from a knife wielding maniac, kept him from driving.  So he asked the audience for a Volunteer and Julie Smith (@datachix1 | Blog) came to his rescue, to help him build his Cube “ItsSoSmall”. 

Step 1. Get Data for the Cube, create data source (check), create Data Source View (check), create Cube (check),  SAVE, Deploy Cube (check), Hide Surrogate Keys (check), and Congratulations you’ve done it!  Seriously hijinks ensued; we discussed Named Calculations vs. MDX Calculations and before you know it our hour was up, and we had a cube. 

Adam is a phenomenal Speaker, he is one of the top Industry experts in BI, and it is clear why.  I’m inspired to work more with Cubes and SSAS after this week.  


PAGE AND ROW COMPRESSION HOW WHEN AND WHY BY BRADLEY BALL

I’m going to do this one a little different Dear Reader, I wasn’t in the audience.  That’s me up front, proud as all get out to be one of the speakers at the First EVER SQL RALLY!  I had worked up a new demo because I wanted to make sure that I showcased the internals of what happens when you compress.

It went over very well, others had some errors that I needed to proof out.  Every presentation is different and a big part of that is the audience, I got some great questions from Jim Murphy (@SQLMurph|blog) and had a really great discussion with SQL MVP Louis Davidson (@DrSQL|Blog) who had some great questions about compression Internals.

To be honest my favorite part is the questions, they push us they make us grow as professionals, and they help us learn more about the things we love to do.  I’ll be doing a couple posts following up on those questions later this week.  And I’ll get my Scripts and Deck Posted tomorrow as well.

This was the close to a great week.  I met new friends, and bonded more with those that I’ve been fortunate enough to make this year.  I was honored to be Invited to Present, and I can’t wait to keep presenting.  If you were in my session Thank You for attending!  I hope I get the chance to see you all again!

Thanks,

Brad

Monday, May 16, 2011

SQL RALLY DAY 1 RECAP

Day 1 at SQL Rally!  When I first learned about SQL Rally it was watching the PASS Keynote Day 3 remotely from one of our Conference Rooms with a couple other DBA’s back in November 2010.  I’m not going to lie I was excited! 

It was close by, and that would make it an easier sell to our company. 


So fast forward 6 months, and here we are.  Finally the Big Day has arrived, here at the Beautiful World Conference Center Marriot in Orlando FL.



SQL SERVER PARALLEL DATA WAREHOUSE- UNDER THE HOOD BY BRIAN MITCHELL

First Session of the first day and did it ever start out right!  Brian Mitchell (@BrianwMitchell | Blog)  is a SQL 2008 MCM and a Microsoft Premier Field Support Engineer specializing in Parallel Data Warehouse.  

Brian discussed how PDW is a black box, that the OS and SQL Server installations receive updates quarterly.  From a physical stand point if your organization buys one, you won’t be patching it, it comes pre-configured with software installed, so the majority of the work is already done.

After reviewing the basics Brian took us into the physical architecture.  Each system will occupy 2 Racks, within each you will have a Control Rack and a Data Rack.  Within each rack are Nodes that relate to the management of the application.  Familiar technologies are in use under the hood as well as some new components.  RAID, Failover Clustering, Resource Governor, Soft NUMA, are all utilized to ensure that the system has High Availability, redundancy, and stable yet high performance is achieved for the end user.  

Brian did a fabulous job, go to his blog and read more about PDW, and if you get the chance to see him live I would highly suggest it.

TROUBLESHOOTING PERFORMANCE PROBLEMS BY READING THE WAITS BY EDDIE WUERCH


Eddie Wuerch (@EddieW| Blog)  continued on the SQL goodness with his presentation on Waits.  SQL Server has come a long way over the years, and one of the great things that it does in SQL 2005, 2008, and 2008 R2 is collect statistics on where it is spending it’s time.  When SQL Server is working perfectly fine it will have Waits. 

An easy thing to remember about Waits is that the SQL OS, SQL Scheduling, aka the way SQL Server optimizes itself to use CPU’s is very similar to traffic patterns in a city.  If everything is working fine you will have a red light you stop at eventually.  Nothing is wrong, you just have to stop and wait until a resource, the road ahead, is available for you to use.  Until then other cars may be using it.  However when a car accident happens and a lot of traffic is backed up in a way that could have been avoided, high Wait times can show you where your problem is located at. 

I always think that a good session is where I learned something, a great one is where I learned something I can apply quickly.  The two things I learned from Eddies great session, Latches are locks on metadata in Pages and the Wait Stats that I occasionally see for Service Broker, on systems that are not using Service Broker, are there because DBMail is enabled.

UNDERSTANDING STORAGE SYSTEMS AND SQL SERVER BY WESLEY BROWN


Wes Brown (@WesBrownSQL| Blog) an amazing presenter. He is at ease in front of an audience, and he had a large one.  He has a great cadence and presence, which is not something that can be taught. 

He says funny things like how becoming an Accidental DBA was an act of him being "Volun-told" what he would do.  He says brilliantly simple things like “If you are a DBA the fastest part of your machine doesn’t matter, the slowest does”.  He knows storage systems inside and out, and he covered why you should never be using the write cache on a local disk, the differences in speed between SAN, Local Disk, and Solid State.  He covers RAID and how your reads and writes will be affected and how to weight the costs vs. benefit. 

One of the greatest points he made was that Transaction Logs are all about Sequential Rights.  If you load a lot of busy Transaction Logs onto one disk, you take Sequential Logs on Disks that work great with Sequential Reads and Writes, and you make them do Random Reads and Random Writes.  It was a great point to make we the audience think about our disk architecture.

WIT LUNCHEON AND PANNEL DISCUSSION PRESENTED BY SQL SENTRY


Karen Lopez (@DataChick | blog), Adam Jorgensen (@Adam_Jorgensen), and Melinda White where the panel moderated by Jennifer McCown (@MidnightDBA | Blog).  It was a very good discussion of how to get a Mentor and how to be a Mentor that crosses to both genders.  It is a subject that will be near and dear to me forever.  I’m a Dad, I have 4 kids, 2 boys and 2 girls.  Encouraging them, all of them, to be who they want and dream for anything is just something that has always come with the territory.  It was also great to catch up with my friend and former co-worker Wayne Sheffield (DBAWayne) at the lunch! 

T-SQL CODE SINS: THE WORST THINGS WE DO TO CODE, AND WHY BY JENNIFER McCOWN


Jennifer McCown is ½ of the Dynamic Duo of the Midnight DBA’s.  Here presentation was on the worst things that we do and have seen, and some suggestions in how to handle it.  Jen is a fabulous presenter, and she did a very dynamic presentation with a lot of audience participation.

Jen did this presentation as 24 Hours of PASS, view it here Session 8.  I had really enjoyed the presentation and wanted to see it again live, and it did not disappoint!  In any presentation with a lot of audience participation you really end up Herding Cat’s, Jen did this masterfully.

SQL UNIVERSITY: LIGHTNING TALKS BY JORGE SEGARRA

Jorge Segarra (@SQLChicken| Blog)  EL Polo Loco himself, a master on the microphone, everything from Lady Ga Ga (Although I suspect that was an evil twin), to the Cranberries (Death Metal Chicken), to the Dean of SQL University.

 If you are unfamiliar with SQL University, stop right now and click on the link.  Top SQL Professionals are offering up a free curriculum of learning and have been since the Fall Semester of 2009.  MVP’s, MCM’s, all headliners are blogging in order to teach and help further the knowledge of anyone looking to participate and better themselves.

The presentation was 5 minutes by each presenter, and then a panel discussion with the audience.  Jorge, Karen Lopez, Jen McCown, Grant Fritchey (@GFritchey | blog), Brian Moran (@BriancMoran | Blog), Mike Walsh ( @Mike_Walsh | Blog), and Aaron Nelson (@SQLvariant| Blog) where the presenters.  As well are they professors.  We discussed Database Design, Backup & Restore strategies, Professional Development, using Twitter for #SQLHELP, and Powershell.

SQLRALLY OVERDRIVE – SPEED NETWORKING, SPONSORED BY MAGICPASS

Kendal Van Dyke (@SQLDBA| Blog) the chapter leader of MAGICPass, the chapter that I regularly attend, hosted a session on speed networking.  We talked about handshakes, how to talk to people by asking questions that cannot be answered with the typical yes or no answers, and the do’s and don’ts of reading names off of lanyards.

It was a great session and was well put together, it was actually difficult to break up the conversations and rotate people.  Which naturally helped lead to the end of the evening and a good dinner, rousing conversation, and I think there was some Karaoke…..but that was difficult to recall.

While the Rally may be over, I'll recap day 2 tomorrow, better late than never J

Thanks,

Brad

Wednesday, May 11, 2011

SQL Rally: Pre Con Business Intelligence Workshop BI END to END


Hello and Greetings from SQL Rally in Sunny Orlando FL!  


Today was the day of Pre-Con’s, four different tracks to choose from and I went with the Business Intelligence Track.  All of the other track’s had One presenter, heck every pre-con I’ve ever been to has had One presenter, but not today.


Today was an end to end presentation covering SSIS, SSAS, SSRS, Powerpivot, Sharepoint, and Performance Point.  Something that big requires not One, not Two, no Dear Reader not even Three, but Four Presenters.


Mike Davis (@MikeDavisSQL), SQL MVP Patrick Leblanc (@PatrickDBA), Devin Knight (@Knight_Devin), and the Big Man Himself, The Sultan of SSAS, The Conqueror of Cubes, SQL BI MVP Adam Jorgensen (@Adam_Jorgensen) delivered a presentation that was large in scope and in delivery.  All of the presenters work for Pragmatic Works, all are published authors, and all will be speaking during the main SQL Rally event.

Pragmatic Works is a company that offers Consulting, SQL Products, and Training.  They are very active in the SQL Community.  If you’ve ever been to a SQL Saturday then you have probably had the opportunity to hear one of them Speak.  If you have visited or posted a BI question on BIDN you have probably had one of them assist you in some way.  They are good and gracious people and it has been a pleasure interacting with them in the past, and seeing them again today.


So with my buddy Gareth Swanepoel (@GarethSwan) showing the way to the Coffee, the experts were in place and we were Ready to Roll!

FUNDAMENTALS & SSIS

I thought it was very cool and original to have a rotating staff during a full day Pre-con, it kept it very fresh and lively, and as an audience member keep lulls from occurring.    Patrick started out the day by introducing himself and the other members of his team.  They quickly moved into a format where one person would be presenting and another would be up front as a co-presenter as well. 

Devon quickly briefed us on the concepts and terminology that we would be using for the remainder of the work shop.  We needed to know the fundamentals:

 Fact Tables -which will be the center of our BI “Data Model”, but as a rule should contain columns that you can aggregate.

 Dimensions Tables- the support tables that will provide information that we would use to sort or filter data by.  Think of the information in Dimension tables similar to the data you would use in a Where clause in T-SQL.

Surrogate Keys – These are keys without a natural identifier.  Think of using and Integer Identity Column in SQL.  But the usage in BI is important on two fronts, not only will they be used as an identifier but to insulate us, the users, from source data changes in Natural Keys.

Before the day was over we would covered Type 0, Type 1, and Type 2 Dimensions, Attributes, Members, Hierarchies, and Role Playing Dimensions.    We looked at Star Schemas and discussed the what makes a Snow Flake Schema, a BI Server in a VERY COLD room.

After an introduction we moved to SSIS and spent the next several hours there.  We reviewed the many ways we could use SSIS, and in some cases SSIS & T-SQL, for Extract, Transform, and Load.  Configuring SSIS packages for migration through environments, storage of packages MSDB vs. File System, and deployment of packages took almost the remainder of the morning.

CHANGE DATA CAPTURE

When you start talking about loading millions or billions of rows of data into a data warehouse every night, as a DBA I get nervous.  We all know the overhead that BCPing Data out of and into systems can have.  The overhead of disabling and rebuilding indexes, the ETL involved, Latches, Locks, and Blocks OH MY!  Part of you wants to say there has to be a better way, how do we find the data that has changed and migrate only that?
Change Data Capture was the way home.  Patrick had code and demo’s that I’m really looking forward to diving through and testing out.  This was a concept that really clicked, and it was one that I had not considered before.  Using the Log Sequencing Number, and tracking the columns that we need to capture (AND ONLY THOSE COLUMNS), we can minimize the overhead required to load data into a data warehouse.

LUNCH

Lunch was a great bag lunch with gourmet sandwiches, chips, water, apple, and a very delicious chocolate chip cookie.  Tasty and delicious, but the best part was the company I walked around and had great conversations with Jack Corbett (@unclebigguns), Kendal Van Dyke (@SQLDBA), Andy Warren (@SQLAndy), met Grant Fritchey (@Gfrichey), and finally sat down and enjoyed the warm Florida sunshine with my cohort Dan Taylor (@DBABulldog) and Stacia Misner (@StaciaMisner).  The SQL Community is a tight one, and every time I get together with people it seems like we pick up the conversation right where it left off.

SSAS-IT'S ALL ABOUT THE CUBE BABY


Adam was up next to discuss SSAS.  He tied together concepts to show us how the regular SQL Engine & the SSAS Engine were similar and different all at the same time.  How integers are more efficient in queries, which is why in a Date Dimension the SK should be an INT instead of a date time value, regardless of the fact that small date time and INT are both 4 bytes;  How XMLA is to DDL as MDX is to DML, and the drawbacks of calculated measures vs. Transparent Aggregation were all on the table.

He blew the audience away with the Usage Based Optimization wizard, which literally is a wizard that based off of Internal Usage Statistics can be used to tune SSAS at a granular user level.  He briefly touched on partitioning strategies, MOLAP, HOLAP, ROLAP, and when you would and should see each.

SSRS & SHAREPOINT



Mike did a great job of taking us through Sharepoint, demonstrating many of the configuration options, SSRS, Performance Point Reporting, and looking at the dynamic built in nature of SSRS 2008 R2 when it has the power of a data cube behind it.


TIME TO WRAP IT UP



Needless to say these guys are some of the top professionals in the world when it comes to BI, and it was fun to watch them work.   

The crowd was very responsive and if you can’t tell I enjoyed it immensely.

This was 7 hours of training, 1 hour of lunch, and 7 pages of notes.  Let me say that one more time 7 PAGES OF NOTES!  No matter what I’ve written I couldn’t do justice to the information provided!

I’ve only touched on the highlights of the topics Dear Reader, if you’re interested in more I’d encourage you to go to the Pragmatic Works website and watch some of the free video’s they have up for training.  They offer free webinars monthly on a variety of topics, and head over to the SQL Lunch website as well of which Patrick is the founder.

Now I’m off to bed, so I can be up and ready for tomorrow, SQL Rally Day 1!

Thanks,

Brad