CXPACKET – that mysterious wait type

At my current employer, we have experience various levels of CXPACKET waits causing or thinking that is causes problems.

The first assumption we made that the CXPACKET wait was a problem came from SQL Server error message that suggested using OPTION (MAXDOP 1). Googling the details of the error, which included the words resource semaphore, was bringing up nothing. Calls to Microsoft Premier Support was not helping either. So, we changed queries that had long CXPACKET waits (some queries would never finish) or the Resource Semaphore deadlocking to use OPTION (MAXDOP 1) and we just waited for the query to run long (at least it finished). This happened to about 5 long running queries in our OLTP system when we converted to SQL Server 2005 and larger production machines.

A good read about this situation can be located here from Bart Duncan. Thanks Bart!!!

So, we thought every time we see CXPACKET waits, we had this same problem. The SQL Server community would make comments like CXPACKET is not the problem or look at your Query Plan. Tune the query, etc. etc. But I could not see the forest through the trees.

Now, after a couple of years of reading and using various tools for performance tuning, I have now realized why some in the SQL community keep saying that CXPACKET is not the problem. The problem I was having with this statement was they were either not communicating what the problem was or my limited understanding of performance tuning hindered me from comprehending the solution. And I really believed I knew what performance tuning was all about. Man, I was wrong. And finally still today I know there is more to learn.

Our OLTP system has 5 to 1 Reads to Writes. An analysis by EMC for a new VMAX gave us these statistics. This means that long processing queries were victim to this perceived notion that CXPACKET wait was our problem. This also included our OLAP system which is Cognos and I am now a member of this department. We just assumed it was a wait problem.

Adam Machanic has a great utility called sp_WhoIsActive. By default, it does not show CXPACKET waits. He also has a recorded session on Parallel Processing which I was blessed to host for Adam and the PASS Performance Virtual Chapter. He also has a series with SQL University, which I suggest reading. Also, Paul White ( Blog | Twitter ) has been trying to clue me in on CXPACKET via Twitter for sometime now. If sp_WhoIsActive is hard to understand, first start with sp_who3, another great monitoring tool. Thanks Mr. Denny

I have 2 executions of sp_whoIsAtive in my shortcuts in SSMS:

Ctrl+7 - exec dbo.[sp_WhoIsActive] '', 'session', 'Replication%', 'program'

Ctrl+8 - exec dbo.[sp_WhoIsActive] @filter = '', @filter_type =  'session', @not_filter = 'Replication%', @not_filter_type = 'program', @get_task_info = 2

The filter is for not seeing transaction replication in the view. The Ctrl+8 has the @get_task_info = 2 which shows a combination of CXPACKET and other waits associated with the SPID execution. This is really cool stuff for a DBA geek.

After reading the above links and riffling through some execution plans, I came to an ah hah moment when I saw a Display Estimated Query Plan and the Actual Query Plan not be the same. The difference included a Nested Loop in the estimate that did a Lookup and then a Hash Loop with 2 Index Scans in the actual plan.

What I came to realize was that if I tune the query and tables(indexes), parallelism sometimes goes away. Did you get that? …it goes away. Why?  Because now the actual plan does not need to run in parallel for the best plan.

Another one came when I realized the table (Fact table in a Data Warehouse with 30+ million rows) did not have a clustered index, but many non-clustered indexes. The suggested index from the Missing Index feature of SSMS showed a new Non-clustered index, when I applied it, it did not help 90+% like the missing index feature said. So, I went to the table, found the no clustered index on table, combined the surrogate keys into a clustered index and the Query Plan cost went from 1000+ to less than 50.

WOW!!! Now I get it. Thanks Adam, Paul and others for being patient with me

One more thing, I do not remember where I saw this, but in the sys.dm_os_waiting_tasks, when  exec_content_id = 0 and the wait is CXPACKET, that means the process is waiting for parallel processing to finish, and is a valid wait.

I think I will dig up some of these old query tuning experiences and try to show others through this blog what I have found. Maybe even a step by step process I go through nowadays to find problems with performance.

Now, back to the ETL on Data Marts.

God Bless,

Thomas

SQLSaturday #56 BI Edition in Dallas

Since I was missing PASS this year, not happy, I decided to drive to Dallas to attend a BI Edition of SQLSaturday on Oct 23rd, 2010.

This event was scaled back from a full SQLSaturday with 2 sponsors, Microsoft and Artis. So, there was not a lot of Vendor information, which help the Dallas SQLSaturday crew not have to attend to them. They were probably able to see some sessions instead.

Got to meet Ryan Adams along with Trevor, Tim Mitchell, Vic Prabhu and some new faces.

    

Above are Vic and Tim, the crowd and Sean.

Here are some more pictures.

         

    

     

More Pictures: http://picasaweb.google.com/dfwsqlsaturday/SQL_Saturday_Oct23#

I presented Transition from DBA to BI Architect, a new presentation. The main points were learning Dimensional Modeling which is not denormalization, but an actual technique and learning the Design Process, SDLC, for a BI department. If you were like me, my DBA duties included more support than design. So, I went through the last couple of months of work I have been doing along with the books I have been reading and bring my developer hat back into the mix.

SQL Server Deep Dives has a great section on BI. 2 authors were at this event, John Welch (Data Profiling) and Erin Welker (BI for the relational guy). It was amazing, as I was presenting and talking about the book, Erin walked into the main room, then I meet John in the Speakers room.

CXPACKET series coming soon.

God Bless,

Thomas

Houston Tech Fest and Vacation

I will be heading to Houston on Friday afternoon to visit with other Microsoft technology geeks and speak/attend  Houston Tech Fest. Patrick LeBlanc ( Blog | Twitter ) and William Assaf ( Blog | Twitter) from Baton Rouge will be on the same track together giving the tech event some SQL Server sessions. It looks like there are some other SQL Server sessions from Tim Mitchell,  Trevor Barkhouse and Geoff Hiten.
It is great to be able to share the experience I have gained over the years, and network with others that have similar interests in SQL Server. I always meet one or 2 people in the industry to help with me in learning more tricks and tips.

Then, starting Sunday, I will drive to Kanuga, North Carolina for the 3rd year in a row to See The Leaves. This is a week of vacation away from the busy days of work and life to read, pray and meditate.
I plan on reading Richard Foster’s book on Spiritual Disciplines and Phillip Yancey’s Soul Survivor. The wood carving has hooked me for arts and crafts for the week and I plan of updating the walking cane and making a gift for Alex and Janet.

This past couple of weeks I have dove into Dimensional Modeling and using SSIS to ETL data from a supposedly Relational Database, but more and more looking like a database created by Object Oriented developers. Lots of inherent looking transaction tables as well as lookup tables.

I am still using the Kimball Methods (and books) to create and populate these Data Marts. I seemed to have a good feel for Dimensions, but the Fact tables I am struggling to hang on to the normalized structure just cause I know how to report off them. Other contractors seemed to do the same thing with the Facts – they are just transaction tables.

A little more practice and reports should get me out of the normalized only mentality. 

God Bless,
Thomas LeBlanc

BI reality is Setting in

The new job in the Business Intelligence group has been on for about a month or two and there is a lot to learn…and a lot to teach the group.

The group’s focus has been reporting to the business user, but there has not been a great deal of time focused on the structure of the databases and tables. This is definitely going to be a give and take job.

To help improve performance, we have purchased Ignite8 from Confio. This has saved me hours if not days of finding the worst performing queries. The software looks at wait stats and organizes the queries by longest waits. You can even name the hash number it generates to label the queries. It even gives you starting points where to look for performance improvements. We have used Idera’s Diagnostic manager for years, which gives great historical data for the instance, but not near the help for tuning queries. I have to say though, I have not dug into Ignite enough to see if it has the alerts we need for real time instance/server issues which seemed to come up once a month.

One query had 200 minutes of CXPACKETS and was running for about 10 hours. One Clustered index and 4 non-clustered indexes improve the query to 10 seconds. Yeah, you heard me. Apparently, a query can start running and endlessly gave data, process, stop running because of CXPACKETS, and start all over again. Our data table was a heap with 7 million rows. Not good for a data warehouse table.

The other items on my list is to get the feel for the flow. Lots of meetings and lots of reading. The Kimball’s Group Data Warehouse Lifecycle Toolkit is what I am reading right now. It dates back to 1989, but it is still the status quo of today’s structures just like Normalization. I have had to re-read some chapters to get the idea, but I am getting it. The first inclination I had from what i have heard, was denormalize. Well, I threw that out the window and adopted the idea of Dimensional Modeling, not denormalization.

Speaking of normalization, I am blessed to present 2 sessions on Normalization at Houston Tech Fest on Oct 9th at University of Houston. 3rd Key Normal Form: That’s Crazy Talk!!! is my bread and butter. I get 90 minutes for this presentation. I have presented this talk at the Baton Rouge PASS SQL Server User group and SQLSaturday in Baton Rouge and New York City. Whiteboard Normalization will be a continuation of the first talk. I will be able to catch up with Trevor Barkhouse from Dallas and William Assaf and Patrick LeBlanc. Patrick will be doing a CDC + SSIS = SCD for data warehouse population I will finally be able to see.

God bless,

Thomas LeBlanc

SQLSaturday #28 Baton Rouge, LA

 

A wonderful and informative SQLSaturday was held in Baton Rouge on the campus of LSU.

Steve Jones captured it on video:

Blogs about the event”

 

Tim Mitchell - http://www.sqlservercentral.com/blogs/tim_mitchell/archive/2010/08/21/sql-saturday-28-baton-rouge-recap.aspx

Wes Brown - http://sqlserverio.com/2010/08/16/what-a-great-sql-saturday-baton-rouge/

My new Friend Eli Wienstock-Herman -http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/sql-saturday-28-baton-rouge

Here are some pictures:

     

  

  

SQLSaturday Baton Rouge #28

Come one, come all to the largest FREE technology event ever in Baton Rouge and probably in Louisiana.

http://sqlsaturday.com/28/eventhome.aspx

600+ registered attendees to network with and 58 sessions from beginners to advanced. Many MS MVPs from SQL Server and .Net

I will be presenting about Database Normalization and how important it it. http://sqlsaturday.com/viewsession.aspx?sat=28&sessionid=1323


Track Starts Session Title Speaker


.Net 1 07:30 AM .NET 3.5 Fundamentals Mike Huguet

.Net 1 9:00 AM Zen Coding Brian Rigsby

.Net 1 10:15 AM Getting Started with the Entity Framework 4.0 Rob Vettor

.Net 1 11:30 AM Zen Testing Brian Rigsby

.Net 1 01:30 PM C# Ninjitsu Chris Eargle

.Net 1 2:45 PM Advance Your Debugging Skills with VS 2010 Rob Vettor

.Net 1 4:00 PM 6 Months of putting VS2010 and TFS thru the Paces Michael Moles

.Net 2 9:00 AM Exploratory Testing with Microsoft Test Manager Vaneshia Leachman

.Net 2 10:15 AM Building Richer Web Applications with SP2010 Kyle Kelin

.Net 2 11:30 AM The Best of Visual Studio 2010 Zain Naboulsi

.Net 2 01:30 PM 3P's (Principles, patterns and performance) of SD Chander Dhall

.Net 2 2:45 PM Introduction to NHibernate and Fluent NHibernate Brian Sullivan

.Net 2 4:00 PM RESTful Data Chris Eargle

Apps\Cloud\Personal Development 9:00 AM Building a Testable Data Access Layer Todd Anglin

Apps\Cloud\Personal Development 10:15 AM The Modern Resume: Building Your Brand Steve Jones

Apps\Cloud\Personal Development 11:30 AM Getting SQL Service Broker Up and Running Denny Cherry

Apps\Cloud\Personal Development 01:30 PM Intro to Windows Azure Ryan Duclos

Apps\Cloud\Personal Development 2:45 PM Azure - Best Practices Chander Dhall

Apps\Cloud\Personal Development 4:00 PM SharePoint 2010 Management with PowerShell Cody Gros

BI\SSRS 07:30 AM Breakfast Basics, SSAS Cube Creation Barry Ralston

BI\SSRS 9:00 AM An Introduction to Power Pivot Bryan Smith

BI\SSRS 10:15 AM Get your Mining Model Predictions out to all Steve Simon

BI\SSRS 11:30 AM Can you control your reports? Ryan Duclos

BI\SSRS 01:30 PM Data Mining.. Making $mart financial decisions Steve Simon

BI\SSRS 2:45 PM College Football and MSFT Business Intelligence Barry Ralston

BI\SSRS 4:00 PM My first SQL Report Mark Verret

DB Design & App Dev 9:00 AM iPhone Development using .NET and Monotouch Jason Awbrey

DB Design & App Dev 10:15 AM Efficient Data Warehouse Design Suresh Rajappa

DB Design & App Dev 11:30 AM Introduction to Windows Phone 7 Development Carlos Femmer

DB Design & App Dev 01:30 PM Conceptual Data Modeling: Defining Our Data Eli Weinstock-Herman

DB Design & App Dev 2:45 PM 3rd Normal Form: That's crazy talk!!! Thomas LeBlanc

DB Design & App Dev 4:00 PM Parallelism Options in .NET 4.0 Al Manint

Microsoft .NET Framework from Scratch 9:00 AM Microsoft .NET Framework from Scratch Keith Elder

SQL Admin I 07:30 AM Basic SQL Server Steve Jones

SQL Admin I 9:00 AM Common TSQL Programming Mistakes Kevin Boles

SQL Admin I 10:15 AM An Introduction to Profiler and SQL Trace Trevor Barkhouse

SQL Admin I 11:30 AM Database Maintenance Essentials Brad McGehee

SQL Admin I 01:30 PM Understanding Storage Systems and SQL Server Wesley Brown

SQL Admin I 2:45 PM Running mixed workloads on SQL Server Jason Massie

SQL Admin I 4:00 PM Beginning Powershell Sean McCown

SQL Admin II 9:00 AM Best Practices Every SQL Server DBA Must Know Brad McGehee

SQL Admin II 10:15 AM Introduction to DMVs in SQL 2005/2008 William Assaf

SQL Admin II 11:30 AM SSIS and Powershell: A winning combination Sean McCown

SQL Admin II 01:30 PM SQL Server Memory Deep Dive Kevin Boles

SQL Admin II 2:45 PM Deciding if VMs are a good choice for your SQL Svr Denny Cherry

SQL Admin II 4:00 PM A PowerShell Cookbook for DBAs Trevor Barkhouse

SSIS\BI 07:30 AM Build Your First SSIS Package Tim Mitchell

SSIS\BI 9:00 AM Data Mining in Action: A case study Drew Minkin

SSIS\BI 10:15 AM Dynamic SSIS with Expressions and Configurations Tim Mitchell

SSIS\BI 11:30 AM CDC + SSIS = SCD Patrick LeBlanc

SSIS\BI 01:30 PM MDX 101 Bryan Smith

SSIS\BI 2:45 PM ssis templates: the easy way to win. Tim Costello

SSIS\BI 4:00 PM SQL Source Control: Poor man's data dude. Tim Costello


Thomas LeBlanc

SQLSaturday, BI and What I am reading

SQLSaturday # 28 (http://www.sqlsaturday.com/28/eventhome.aspx) is coming to Baton Rouge on August 14th at LSU in the CEBA building. We have 58 sessions and 490+ registered attendees.

This is the second SQLSaturday for BR thanks to SQL Server MVP Patrick LeBlanc leadership and employees of Sparkhound and Antares as well as LSU. Patrick is my brother from a different mother.

I spoke last year on RML Utilities and SQLNexus, but this year the session I will be presenting is “Third Normal Form: That’s Crazy Talk” (http://www.sqlsaturday.com/viewsession.aspx?sat=28&sessionid=1323). This seems to be my calling as a presentation that I have locked down. Volunteering has been interesting as we watch things change month to month by our fearless leader and great politician.


The other news is I have started a new position at Amedisys, moving from Senior DBA to BI Data Integration Lead Developer. The last 3 weeks have been filled with meetings and documentation review. They have already assigned me tech specs to complete, assist with latest Data Mart ETL and improve performance on the End of Month process. My whole career has revolved around normalized databases, which I am very passionate about. Changing my thinking is difficult (I am 42), and seems to be the biggest barrier. Please pray for me.

Louis Davidson's book Pro SQL Server 2008 Relational Database Design and Implementation (http://drsql.org/ProSQLServerDatabaseDesign.aspx)  is a great and long read that I believe is very comprehensive when it comes to educational reading for database design. I am using the reading of this book to help me prepare for SQLSaturday #28 as well as Houston TechFest (http://www.houstontechfest.com/)  on Oct. 9th. It looks like Houston has accepted 2 sessions, the one above and Whiteboarding Normalization (http://www.houstontechfest.com/dotnetnuke/HoustonTechFest/Sessions/tabid/56/CodecampId/3/SessionId/218/Default.aspx) for their sessions packed event.

Wish me luck, and come back for some more talk about Normalization, and Denormalization at this blog.

God Bless,
Thomas LeBlanc