Friday, May 20, 2011

VOTE FOR MY PASS SESSIONS!

Hello Dear Reader, this is a quick post to ask for your vote, or just for you to vote in general.  Today is the last day of voting for sessions for the 2011 PASS SUMIT.
Here is the link to follow CLICK HERE TO VOTE. (You will need a PASS account to vote, but they are FREE!)
And here is a list of the Sessions I have submitted.  I’ve presented on Compression at SQL Saturday 62, SQL Saturday 74, SQL Rally, for the MAGICPASS SSUG, and for the Virtual DBA Chapter of PASS.  If you’ve viewed the presentation and can spare the time I’d appreciate it.

Page and Row Compression How, When, and Why

Page and Row Compression are powerful new tools. Vardecimal shipped with SQL 2005 SP2, Page & Row with SQL 2008 RTM, and Page & Row with Unicode Compression with SQL 2008 R2. Get an overview into how each version of compression works internally. Learn how your Allocation Units will determine if your data is a candidate for compression. Understand how your tables Update and Scan pattern’s affect the compression types you should consider. And what you should you take into consideration for additional overhead.

Page & Row Compression Deep Dive

Page and Row Compression are powerful new tools. Page & Row with SQL 2008 RTM, and Page & Row with Unicode Compression with SQL 2008 R2.  We can turn it on, we can turn it off, but we want more!  What are Access Methods and how in the SQL Engine do they affect Compression?  What are the “Gotchas” of Page Compression?  How does Compression differ in the way it treats Non-Leaf & Leaf Level pages?  What additional functionality did we get in DBCC Page, DMV’s, Perfmon Counters, and Extended Events to support our usage of Compression?  How do complex query plans affect Compression?  Come find out!

Transparent Data Encryption Inside and Out

Security is a very important part of your job and in how data is utilized.  We have many tools to make data more secure, and starting in SQL 2008 we were able to add Transparent Data Encryption to that list.  Find out What it does, What it doesn’t do, how it effects Read-Only Filegroups, Performance, Compression (Backup and Row/Page), and other Advance Features as well as some tips on how to manage it.
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

Monday, May 9, 2011

T-SQL Tuesday 18: Common Table Expressions AKA CTE’s

T-SQL SQL Tuesday is brought to us this week by Bob Pusateri (blog|@SQLBob).  So Bob what is the topic this month?

Have you ever solved or created a problem by using CTEs? Got a tip, trick, or something nifty to share? I’d love to see your posts about any of the above. Also don’t forget that T-SQL Tuesday is not limited to only T-SQL:
So we aren’t limited to CTE’s but I do enjoy them, so off we go!


HOT FUZZ FIZZ BUZZ


I first learned about Common Table Expressions while studying for the 70-432 exam back in 2007.  They were neat, they were cool.  They have a lot of power when it comes to recursion.  The first time I used them in any practical sense was in a job interview.  I was asked the Fizz Buzz question.

I remembered reading a blog Rob Boek (blog|@robboek) had written about this exact same question, read it here, Fizz Buzz by Rob Boek

I thought it was simple. I thought it was elegant.  It was a great example of Set Based logic.  I was trying to make a good impression, and so I re-created it.  I was honest about it, when asked why I came up with the solution that I had.  It led to a great conversation about how we study as IT professionals, and learn from others.

“So Balls”, you say “We’re going to talk Fizz Buzz?”

Nope but if you’re interested in CTE’s you should stop and go read it, were going to talk about one of my favorite topics Compression.

SIMPLIFY THE COMPLEX


Common Table Expressions can be used as an alias to complex queries.  The example I’m going to work with today is one from the White Paper on Compression, Data Compression: Strategy, Capacity Planning and Best Practices by Sanjay Mishra.  I use some of the Queries from this white paper, to detect Scan and Update Usage Patterns of Indexes, in my Presentation on Page & Row Compression.  If you’re in the Orlando Area you can come see that presentation this Friday at SQL Rally!

The queries look at the sys.dm_db_index_operational_stats DMF to gage the Scan and Update Patterns of Indexes.  This is important when you are determining a Compression Strategy. 

If you have a High Percentage of Updates then you will experience more overhead with Page Compression, than if you had a High Percentage of Scans.  A good example is an OLTP system vs. an OLAP system, Reference Data vs. Data that is constantly in motion.

The queries are not overly complicated, but getting the information you want requires a certain amount of massaging.  A CTE can help this process by providing a wrapper for a very complex query so you can work with the returning data in additional ways.

We’ll start out with our 2 original queries and then move to the CTE that I’ve put together.

Original Query to Detect Scans

SELECT
     so.name AS tableName,
     si.name AS indexName,
     ios.partition_number AS [Partition],
     ios.index_id AS indexID,
     si.type_desc AS indexType,
     (ios.range_scan_count *100.0/(ios.range_scan_count +
     ios.leaf_delete_count + ios.leaf_insert_count + ios.leaf_page_merge_count + ios.leaf_update_count + ios.singleton_lookup_count)) AS percentScan
FROM
     sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL) ios
     JOIN sys.objects so
     ON so.object_id=ios.object_id
     JOIN sys.indexes si
     ON si.object_id = ios.object_id AND si.index_id = ios.index_id
WHERE
     (ios.range_scan_count + ios.leaf_insert_count + ios.leaf_delete_count + ios.leaf_update_count +ios.leaf_page_merge_count + ios.singleton_lookup_count)!=0
     AND OBJECTPROPERTY(ios.object_id, 'IsUserTable')=1  
ORDER BY
     percentScan;
GO

Original Query to Detect Updates

SELECT
     so.name,
     si.NAME AS [indexName],
     ios.partition_number AS [Partition],
     ios.index_id AS [IndexID],
     si.type_desc AS [IndexType],
     (ios.leaf_update_count *100/(ios.range_scan_count +
     ios.leaf_insert_count +
     ios.leaf_delete_count +
     ios.leaf_update_count +
     ios.leaf_page_merge_count +
     ios.singleton_lookup_count)) AS [Update_Percentage]
FROM
     sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios
     JOIN sys.objects so
     ON so.object_id = ios.object_id
     JOIN sys.indexes si
     ON ios.object_id=si.OBJECT_ID AND ios.index_id=si.index_id
WHERE
     (ios.range_scan_count + ios.leaf_insert_count + ios.leaf_delete_count + ios.leaf_update_count + ios.leaf_page_merge_count + ios.singleton_lookup_count) !=0
     AND OBJECTPROPERTY(ios.object_id, 'IsUserTable') =1
ORDER BY
     [Update_Percentage] ASC;
GO

Both of these queries will get you the information you need, but if you want to see the results side by side you would need to perform a Union.  Further manipulation would require you edit both queries, and could get complicated.  This is where our CTE can help simplify things.

We’ll start out with our expression where we set the CTE name, the Data to be returned, and then we define our query.  Final we’ll have a separate select were we can manipulate our CTE and return the data as we see fit.

Our CTE

WITH IndexUsage(tableName, IndexName, [Partition], IndexId, IndexType, Usage, Percentage)
AS (
SELECT
     (ss.name + '.'+ so.name)
     ,si.NAME
     ,ios.partition_number
     ,ios.index_id
     ,si.type_desc
     ,'UPDATES'
     ,(ios.leaf_update_count *100/(ios.range_scan_count +
     ios.leaf_insert_count +
     ios.leaf_delete_count +
     ios.leaf_update_count +
     ios.leaf_page_merge_count +
     ios.singleton_lookup_count))
FROM
     sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios
     JOIN sys.objects so
     ON so.object_id = ios.object_id
     JOIN sys.indexes si
     ON ios.object_id=si.OBJECT_ID AND ios.index_id=si.index_id
     JOIN sys.schemas ss
     ON so.schema_id=ss.schema_id
WHERE
     (
          ios.range_scan_count +
          ios.leaf_insert_count +
          ios.leaf_delete_count +
          ios.leaf_update_count +
          ios.leaf_page_merge_count +
          ios.singleton_lookup_count) !=0
     AND OBJECTPROPERTY(ios.object_id, 'IsUserTable') =1
UNION All
SELECT
     (ss.name + '.'+ so.name)
     ,si.name
     ,ios.partition_number
     ,ios.index_id
     ,si.type_desc
     ,'SCANS'
     ,(ios.range_scan_count *100.0/(ios.range_scan_count +
     ios.leaf_delete_count +
     ios.leaf_insert_count +
     ios.leaf_page_merge_count +
     ios.leaf_update_count +
     ios.singleton_lookup_count))
FROM
     sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL) ios
     JOIN sys.objects so
     ON so.object_id=ios.object_id
     JOIN sys.indexes si
     ON si.object_id = ios.object_id AND si.index_id = ios.index_id
     JOIN sys.schemas ss
     ON so.schema_id=ss.schema_id
WHERE
          (ios.range_scan_count +
          ios.leaf_insert_count +
          ios.leaf_delete_count +
          ios.leaf_update_count +
          ios.leaf_page_merge_count +
          ios.singleton_lookup_count)!=0
     AND OBJECTPROPERTY(ios.object_id, 'IsUserTable')=1  
)

SELECT
     iu.tableName
     ,iu.IndexName
     ,iu.IndexType
     ,iu.Usage
     ,iu.Percentage
FROM
     IndexUsage iu
ORDER BY
     iu.tableName
     ,iu.IndexName
     ,iu.Percentage desc
   
   
Despite the complexity of the internal statements, out final select is simple and easy to change and manipulate.

There’s an old phrase, “Work Smarter Not Harder”.  Using CTE’s is a good way to achieve the smarter.  I hope you find this helpful.

Thanks,

Brad