SQL Saturday 85: Buck Woody is a Wanted Man

Hello Dear Reader!  SQL Saturday 85 coming to you from beautiful sunny Orlando Florida is just around the corner.  What better way to start of a Saturday then starting it on Friday!

“But Balls,” you say, “How do you start a Friday on a Saturday?”

You start it off with not One, but TWO Amazing Pre-Conference Seminars.  Today we will be discussing our DBA Pre-Conference Session with Microsoft's Buck Woody.

BUCK WOODY IS A WANTED MAN

Microsoft’s Buck Woody(@buckwoody | Blog) is a wanted man.  He is a highly sought after speaker, he is a highly sought after consultant, and he is an educator in the ways of Database Information Systems, specializing in SQL Server.  He was a Microsoft MVP before he decided to go home to the mother ship and work for Microsoft as a Sr. Technology Specialist.   The man teaches Database Design for the University of Washington. 

If I wanted to go on even further I could mention the hundreds of published articles he has on SQL Server, I could name drop some of the conferences he has presented at in the past, TechEd, the PASS  Summit, or SQL Cruise just to name a few.  Perhaps I could tout his work in the SQL Community, serving as the President for the Tampa Bay SQL Server Users group for FIVE years.

Perhaps I could tell you about the prestigious places he’s worked, other than Microsoft, like when he was a contractor for NASA or the US Air Force.   Perhaps we could look at the multiple books that he has written, or the multiple certifications that he has obtained across multiple database platforms.  We could talk about his antics, his quick wit, his uncanny delivery that stems from his time in radio. 

However you want to spell it Buck Woody is a wanted man.  And I can tell you where he will be Dear Reader.  I can tell you exactly where he will be on Friday September 23^rd from 9 am until 5 pm.  And I can tell you what he will be doing as well.  He will be doing one of the things that he does the very best.  He will be teaching.  And you Dear Reader, if you are lucky enough to attend you will be learning.

And what will you be learning about Dear Reader?  We’ll let’s get that straight from Buck.

SQL SERVER PERFORMANCE TUNING USING APPLICATION PATH ANALYSIS

There are a lot of resources, products and features you can use to tune the performance of your SQL Server system. Many assume you’re familiar with 400-level concepts, others don’t consider the whole stack of the client, the network, the operating system, platform and the database server. Buck Woody, Microsoft’s real-world DBA, will explain a simple, repeatable process you can follow to tune your entire application – from the client to the server. All of the tools we’ll cover are included with Windows and SQL Server:

·         Using Windows System Monitoring Tools

·         The SQL Profiler and Server Trace

·         Database Engine Tuning Advisor

In this pre-conference session you’ll cover not only the process, but also review a real-world evaluation. You’ll take home a system and a spreadsheet you can use to monitor and tune your applications, in a simple, easy-to-understand session.

But that’s not all, he’s not just going to be teaching about the subject.  He wants to give you hands on experience with it.  Not that long ago I received this email from Buck.

“Hello – thanks for signing up for the pre-conference session on SQL Server Performance Tuning. I’ve done quite a lot of speaking and teaching, and the most informative way for us to learn together is by working through some projects during the session. If you’re able, I’d like you to bring a laptop, a copy of SQL Server 2008 (Evaluation Edition is fine, Developer Edition is best) and the AdventureWorks sample database. You can learn more about that here: http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=286

Also, feel free to bring any performance questions you have. As always, make sure you protect your company’s private information, but we’ll work through some real-world issues in class, so feel free to share what you can.

See you there!

Buck”

So you will have a full day with one of the best, brightest, and most respected in our field today.  But to add icing to the cake he wants you to bring a copy of a database and your computer so you can get some hands on training.

This isn’t just telling you what you need to do, and then you leave sitting in a situation at work thinking, “Man that Buck Woody Pre-Con was great!  Now if only I could remember what he said about that DMV!?”.  This is hands on training so you can go back to work and apply what you’ve learned right away.  Did I mention that it includes coffee, tea, and Lunch? 

Here is your chance Dear Reader, go sign up and we’ll see you there!  Click Here to Sign UP!

Thanks,

Brad

T-SQL Tuesday 22 Data Presentation

Hello Dear Reader, today is T-SQL Tuesday #22.  I’ve been out of the T-SQL Tuesday game for a little while and wanted to jump back in.  This edition is being brought to us by Robert Pearl (@PearlKnows| Blog ), so Robert what is the topic this month?

“Therefore, the topic of this month’s T-SQL Tuesday is, “data-presentation” Or put better, formatting data for presentation to the end-user.

We may be the developers, and techno-geeks behind the code, whether simple, advanced, spaghetti, or otherwise.  But, the data the user sees is most critical.  The query output, the report, or data presentation, must be absolutely formatted in such a way that is easily understandable and readable by the end-user.  The end-user can be the boss, supervisor, department head, the analyst, employees, or customers.  And they must be the ones we cater our queries to!

Therefore, I am inviting you all to write about “data presentation” to the user.   This can be in the form of T-SQL code, an SSRS report, etc.  What can you do to streamline data presentation?  I used a CTE, you can use one, but you don’t have to.  No hard format, just be creative, and mention the importance of data presentation. “

Data Presentation It IS!

IT’S ALL IN THE PRESENATION

I must confess I’m a bit of a snob when it comes to code.  I will stop, reformat, and then read code if it is not formatted correctly.  But I also believe that what you do every day becomes habit.   And good coding standards are a habit that you want to get into.  This isn’t cool, this isn’t sexy, it isn’t functional (your code will work poorly formatted), and it may take a little longer to type out.

“So Balls,” you say, “Why should I do this? If it will not benefit me?”

AH Dear Reader because easier isn’t necessarily the best way to go.  This is one place where it is better to put in the extra work.  Let’s just look at a very simple example.  Let’s use some DMV’s to get an execution plan. *You would NOT want to execute this statement on a busy server.  This would pull all active requests and their XML execution plans. *

SELECT * FROM sys.dm_exec_requests der CROSS APPLY sys.dm_exec_query_plan(der.plan_handle)

All of this can fit on one line.  But should it?  When we look at this we are doing a select star, pulling back everything on the server.   When your doing a select *, in most cases, you are trying to figure out what rows you are trying to select.   When you ship to production you should be trimming the columns that you do not need.  We’ll get to that eventually but for now let’s do some formatting.

This is the way I write my code, no one way is right for everyone, so please take this with a grain of salt.  First things first, let’s add some space.

SELECT

     *

FROM

     sys.dm_exec_requests der

CROSS APPLY

     sys.dm_exec_query_plan(der.plan_handle)

I like my key words on separate lines, and I like indentation of values that I want to return and table names.  Now when you read this, it is a lot easier to understand what it is that we are looking at.  So let’s go a little farther and trim down the number of columns we are looking at.  All we need is an execution plan.

SELECT

     der.session_id

     ,deq.query_plan

FROM

     sys.dm_exec_requests der

CROSS APPLY

     sys.dm_exec_query_plan(der.plan_handle) deq

This is starting to shape up.  If you execute the other query you will see that we are pulling back a lot of columns that we do not need.  That is data coming over the wire. 

So these have been simple, but let’s look at another coding example that uses a CTE.  I use this in my demo’s for Compression.  This takes a look at the index operational stats dmv and helps you determine the update and scan activity that are occurring on your tables.  This is sizeable and complex, and if you didn’t have formatting it would be nigh unreadable.

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 AS indexType

     ,'UPDATES'

     ,(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

     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.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

)

SELECT

     iu.tableName

     ,iu.IndexID

     ,iu.IndexType

     ,iu.USAGE

     ,iu.Percentage

FROM

     IndexUsage iu

ORDER BY

     iu.tableName

     ,iu.IndexName

     ,iu.Percentage DESC

WOW, it is a lot of fun to play with, but without formatting this would be a nightmare to read through.  We’ve all had code that we had to read that gave us trouble.  Think of the next DBA that has to come along and read it, and make things a little easier on them.

Hopefully the DBA before you did the same thing!

Thanks,

Brad

24 Hours Of PASS!

Hello Dear Reader the 24 Hours of PASS are HERE!  If you have never attended a 24 Hours of PASS this is a great one to hop on board with.  This is the fall preview of all the great content and presenters that will be appearing at the PASS Summit in Seattle coming up in October.  Right out of the gate you have not one but TWO of the greatest presenters that I've ever watched.  So before you read any further Click Here to sign up, and Click Here to read more about the total outstanding line up.

Starting this very morning at 8 AM Brian Knight (@BrianKnight | Blog ) President and Founder of Pragmatic Works!  What is Brian presenting on?

Introduction to Data Mining in SQL Server Analysis Services

Presenter: Brian Knight

Session Details:

Data mining is one of the key hidden gems inside of Analysis Services but has traditionally had a steep learning curve. In this session, you’ll learn how to create a data mining model to predict who is the best customer for you and learn how to use other algorithms to spend your marketing model wisely. You’ll also see how to use Time Series analysis for budget and forecast prediction. Finally, you’ll learn how to integrate data mining into your application through SSIS or custom coding.
 

About the Speaker:

Brian Knight, SQL Server MVP, MCITP, MCSE, MCDBA, is the owner and founder of Pragmatic Works. He is also the co-founder of SQLServerCentral.com, BIDN.com and SQLShare.com. He runs the local SQL Server users group in Jacksonville (JSSUG). Brian is a contributing columnist at several technical magazines and does regular webcasts at SQLShare.com. He is the author of a dozen SQL Server books. Brian has spoken at conferences like PASS, SQL Connections and TechEd and many Code Camps. His blog can be found at http://www.bidn.com.

Amazing!  But it doesn't stop there, Up next is the Founder of the Brent Ozar PLF Brent Ozar(@BrentO | Blog) himself!  What is Brent presenting on you might ask?

SAN Basics for DBAs

Presenter: Brent Ozar

Session Details:

They keep telling you it's a SQL problem - but how can you prove where the real problem is? Learn the basics about your storage options like RAID 5, RAID 10, solid state drives, and why your biggest bottleneck might be a $5 cable. No storage experience required. This session is for production database administrators who've never looked inside the black box of storage before.

About the Speaker:

Brent Ozar is a Microsoft Certified Master of SQL Server 2008, a Microsoft SQL Server MVP, and co-founder of Brent Ozar PLF. Brent has over a decade of experience with SQL Server, systems administration, SAN administration, virtualization administration, and project management. Brent spent 2 years at Quest Software as a SQL Server expert and 2 years at Southern Wine & Spirits, a Miami-based wine & spirits distributor. Brent blogs at http://www.BrentOzar.com like it's going out of style.

And from there it keeps going for 12 solid hours.  But the fun doesn't end there this is 24 hours of PASS not 1/2 a day of PASS.   Tomorrow is another 12 hours from 8 am to 8 am.


What will the 24 Hours of PASS cost you?  A Whole lot of your Hard Earned Nothing!  It requires your time, You can even attend while at work!  All you need to do is register, you will get an email with a Live Meeting link inside.  Take the link and click on it when it is time for the Session!

This is a wonderful opportunity for Free Training!  This is a wonderful opportunity to see the value you will get from the PASS Summit!  And this is a wonderful event to see the content you can get live by attending SQL Saturday's!

SQL Saturday's you say!?  Yes I would bet you that every speaker you see today has appeared at, at least, one SQL Saturday event this year.  So if you love this go look for the SQL Saturday coming to your area and sign up.  I hope to see you there!

Thanks,

Brad