SQL Saturday Houston #107 Recap

I enjoyed a long weekend in Houston starting with Kalen Delaney’s Pre-con on Query Tuning. I have learned over the past years that I should concentrate on 2 or 3 items to take from the training. The first was no matter what kind of T-SQL development is done through an application, there is always a performance gotcha with just Stored Procedures or Parameterized Queries or generated SQL code. It seems a DBA will always be needed to discover what’s in the Plan Cache or a query plan that is hindering performance. The second item was tips and tricks while reading query plans. The zoom to fit was great, Last, the ordered property of an operator and the thought that the plan is read from right to left for data and left to right by calls to the APIs.

In the room Friday was my buddy Rob from Blue Cross-Blue Shield, Jim Murphy from Austin and Nancy from the SQLSaturday Houston crew with Malik. I meet @SQLAvenger again and talked about our presentations from this year. I was surprised the crowd attending the Pre-con did not know about the SQLSaturday event the next day, but somehow found out about the 1-day training as they called it. This Pre-Con sold out (50+ attendees) before the discount price ($88) ran out. Thanks to Idera for bringing Kalen in for Idera Ace training.

I was only able to visit for about an hour at the Speaker/Volunteer appreciation dinner before visiting with an old friend from college. I chatted with Jen and Sean McCown (@MidnightDBA), Wesley Brown (@SQLServerIO) from Austin, Christine, Mike from California, Kendal and many more. The Idera Ace program brought about 10-12 additional speakers in town for SQLSaturday which really boosted the variety of sessions.

Saturday started with a quick registration because of Speed PASS. The parking lot was almost full first thing in the morning, which is really encouraging for SQLSaturdays. I started in Tim Mitchell’s What’s New in SSIS 2012.

  I enjoyed hearing about the new DQS component of SSIS.

Next was me with Dimensional Modeling 101. The room was packed with people standing and 3-4 speakers attending which was nice to have to help answer questions. The talk was well received and I tried improving it from the first go thru in Colorado Springs. It was good to read the reviews and change the flow. I was surprised with the comments, and not so surprised with the suggestions for improvement which included adding more descriptions from the Kimball group process. It would definitely be nicer to have another 15 minutes :) I also converted it to SQL Server 2012 – SSIS, SSAS and the AdventureWorks database.

Next, I went to the speaker’s room to visit and relax. At lunch time, I sat with attendees and ask questions about why they came and what their future looks like. It is great to see younger (20-something) IT professionals hungry for learning and advancing. The YES school IT people were at our table and talked about their school, which helps troubled kids get an education. The school was really nice with tech equipment. One room was decked out with Texas A&M which of course I stayed away from.

Next was Bill Pearson talking DAX and PowerPivot.   If you have never meet this Georgia native, you need to at the next SQLSaturday you attend. He talks almost at every one of them, and has started to do Pre-cons. His knowledge of BI runs for many years.

Execution Plan Basics is a session I have done many times, but the session slowed downed because of questions and discussions about clustered indexes and how a primary key does not have to be a clustered index and is not required for the primary key. This got me thinking about a new session which I have heard other people do – Indexes for Developers.

Back in the speaker room, I talked with Jonathon and Louisiana life and family. It was pleasant to rest a second and just visit. Before long I was helping Nancy and crew clean the speaker’s room and hauling trash to the dumpster. Nancy and Sri rapped things up with talk of SQLRally in Dallas and raffle items from the sponsors.   Idera (home office in Houston) was the main sponsor, but I got to spend time talking with Confio (Ignite), Texas Memory Systems and Joes to Pros writer Rick Morelan. Rick and their company gave away lots of books and DVDs which the speakers were able to give during sessions. I had one attendee excited about taking some training home with her for work.

I ended the evening Saturday with an Astros game. They lost, but relaxing at a ballpark is always enjoyable for me. Houston, see ya next year and some of ya’ll all in Baton Rouge for SQL Saturday #105.

PASS Data Arch VC presents Mike Fal: Eating the Elephant: SQL Server Table Partitioning

Thursday’s (May 17th, 2012) PASS Data Architecture Virtual Chapter has Mike demonstrating some cool Table Partitioning scripts which I was able to attend at SQL Saturday in Colorado Springs in January of this year.

We will also be giving away an Amazon gift card plus someone gets to choose between Itzik’s T-SQL book or Kalen Delaney’s 2008 Internals.

If you would like to volunteer for this VC, please email PASS_DA_VC@HotMail.com

 

Subject:  Eating the Elephant: SQL Server Table Partitioning

Start Time:  Thursday, May 17th, 2012 12:00 PM US Central Time (6:00 PM GMT)

End Time:  Thursday, May 17th, 2012 1:00 PM US Central Time (7:00 PM GMT)

Presenter:  Mike Fal

Live Meeting Link:  https://www.livemeeting.com/cc/UserGroups/join?id=57CCCW&role=attend&pw=H%7DBg5%228qD

Eating The Elephant: SQL Server Table Partitioning

Is your table fat? Do you need to manage a table that has billions of rows within it and are overwhelmed by index rebuilds that take more than 12 hours? SQL Server's table partitioning gives the DBA the tools to manage this beast and support very large tables in a way where index management and data retrieval does not become unwieldy. This presentation will take you step by step through choosing an appropriate partitioning key, setting up the partitioning on the table, and finally maintaining the partitions.

Mike Fal

Mike Fal is a musician turned SQL Server DBA, with 10+ years of experience along with some brief excursions into Oracle and Netezza. He has worked in several different industries, including healthcare, software development, marketing, and manufacturing and has experience supporting databases from 1 GB to 4 TB in size. Mike received his Bachelor's Degree from the University of Colorado at Boulder in 1996 and has been caught playing trombone in public on more than one occasion.

Contact URL: http://www.mikefal.net/

Add to calendar

SQL Saturday #107–Houston

Wow, blessed to speaker at another SQL Saturday in Houston #107. The first task of this weekend is Friday. Kalen Delaney will be doing a Pre-Con on SQL Server Query Plans: Tuning and Management which is sold-out. I pre-registered early whether I was going to be able to go or not, cause I knew this was a great opportunity for $88. Kalen is one of the best and I have read 2 of her books.

The sessions I will be presenting are Dimensional Modeling 101 and Execution Plan Basics. Both talks are full of demos rather than slides. The dimensional session is about getting a summary query from management to create a dimensional model and drill down to see how a Kimball Model can help once the query request changes. The Execution Plan is a session I wish someone had demonstrated to me back in the beginning when I started my SQL Server career, except for the graphical display, most is starter level demonstrations on Table Scan, Clustered and Non-clustered index scan and seek, lookups and properties of operators.

The full schedule is packed with great speakers and sessions. Of course, I have to visit with Bill Pearson and sit in at least one of his sessions. He has more data warehouse experience than anyone I have meet. Any real-life experience is better than most teaching, and he has an abundance.

I think I am going to start Saturday at 8:30AM with Tim Mitchell and The 10 New Features of SSIS in 2012. Next at 9:45AM, is my Dimensional Modeling 101 presentation. I might go hang out in the speaker room after that and visit with old and new SQL friends. Hopefully, I can visit with the sponsors to thank them during lunch and talk with attendees at the lunch tables.

At 1:30PM, I will sit in with Bill for Becoming DAX: An Introduction. 2:45PM will be Execution Plan Basics. Last, I look forward to listening to my northern friend John Sterret for Evaluate Your Daily Checklist with PBM and CMS. John is a native Houstonian and a great guy with lots of energy to help the SQL community.

Other speakers I would love to visit with or watch include Sean and Jen McCown (MidnightDBA), Bryan Smith from Microsoft (smart dude), Wesley Brown from Austin, Christina Leo (skied with in Colorado Springs in Jan), Jim Murphy (Always-On), Kendal Van Dyke (@SQLDBA), and many, many more.

If you live in Houston, you should not miss out of this weekend of FREE SQL Server training. Houston did a good job last year with limited resources, so I am expecting a ten-fold improvement this year.

God Bless, Thomas

Execution Plans – Table, Clustered Index and Non-Clustered Index Scan

An important operator to understand in execution plans is a scan. A Scan can be good and bad, so understanding the difference between them is a basic skill needed by a DBA or developer that is going to do performance tuning.

The Table Scan means that a table does not have a clustered index and the optimal query plan decided to scan all leaf level pages in the table. The table is stored as a Heap because there is not clustered index. A table can have only one clustered index.

We are going to drop the primary key/clustered index on the DatabaseLog table of the AdventureWorks database.

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[DatabaseLog]') AND name = N'PK_DatabaseLog')

ALTER TABLE [dbo].[DatabaseLog] DROP CONSTRAINT [PK_DatabaseLog]

GO

Then, we are going to execute the following code with Include Actual Execution Plan query option activated. In SQL Server Management Studio open a query window, either right-click the query window and choose Include Actual Execution Plan or select Include Actual Execution Plan from the Query menu.

Execute this code:

SELECT [DatabaseLogID]      ,[PostTime]      ,[DatabaseUser]      ,[Event]

      ,[Schema]      ,[Object]      ,[TSQL]      ,[XmlEvent]

  FROM [dbo].[DatabaseLog]

You will get the following execution plan table scan

The optimizer says a full Table Scan is the best plan. The operator Table Scan indicates that the table does not have a clustered index. By default, creating a primary key will make the key a clustered index, but that is not always the case. I am working on a Fact table at work where a Unique Constraint is the clustered index, but the primary key is a 7 column compound key of Surrogate Keys.

By adding a Clustered Index to table DatabaseLog (Primary Key), we can change this Table scan to a clustered index scan. A clustered index changes the logical/physical structure of the table to follow an index tree.

ALTER TABLE [dbo].[DatabaseLog] 

    ADD  CONSTRAINT [PK_DatabaseLog] 

        PRIMARY KEY CLUSTERED 

        ([DatabaseLogID] ASC) 

We get the following if we execute the SELECT query again like above:

So, what is the difference in Cost? None.

    

So what is the big deal, they both costs the same. Well, when you start using a WHERE clause and the no clustered index table still does a full scan, but the clustered index might do an Clustered Index Seek if the density/cardinality gives the optimizer a better choice, you will see the performance difference. More on this is a later blog.

My advice, ALWAYS have a primary AND/OR a clustered index on all tables. The decision on making the primary key a clustered index is dependent on your experience in managing and planning the correct implementation.

All right, lastly we will see a Index Scan. First, we will add a Non-clustered index to the table DatabaseLog.

CREATE NONCLUSTERED INDEX [idxDatabaseLog_DatabaseUserScehmaObject] 

    ON [dbo].[DatabaseLog]

(    [DatabaseUser], [Schema], [Object] )

Now, the query optimizer creates a plan to scan the new non-clustered index to retrieve the data needed in the SELECT statement. If we add a WHERE clause to the SELECT, it will change to a Index Seek.

SELECT [DatabaseUser], [Schema], [Object]

  FROM [dbo].[DatabaseLog]

  WHERE DatabaseUser = 'dbo'

Reading execution plans can be a lot easier than understanding them to help performance tuning. I know I spent many hours thinking I finally got it, before some article on SQL Server Central website or SQL Server Magazine changed the understanding I have about T-SQL statements and indexing for performance. But, you have to start somewhere, then keep improving your understanding.