Saturday, May 28, 2011

More #SqlRally pictures and observations

Wow, it is a picture of me taken by Tim Mitchell, a very cool dude from Dallas and SSIS MVP.

image  image

This was at the raffle outside and my friend from Wheeling, WV John Sterrett.

Geaux Tigers!!!

image   SQLLunch and ex-MVP Patrick Leblanc image

Picture taker Tim imageimage

Kevin Kline in the Pre-con for Personal Development I attended working on leadership and manager skills

Me at a distance, at least 30-40 people in the picture, so there was probably close to 100. That is crazy, but really cool.

image

Up next #SqlSat77 in Pensacola.

God Bless,

Thomas

Friday, May 13, 2011

SQLRally, what a Ride


The week was a wonderful learning, networking and speaking experience.

It all started with Kevin Kline { Blog | Twitter } giving a Personal Development session on Leadership and Management. Got some great statistics and experience from the 30+ attendees at this Pre-Con.

Bill Graziano and Kevin Kline at SQLRally  Jack Corbett, Mike Walsh and Kendal Van Dyke  After hours at SQLRally Orlando - Johnnie's Hideaway

Wednesday and  Tuesday evenings were spent visiting with SQL Peeps at various places. I meet lots of new SQL People that are joining our community. 2 from Ohio – Tim and Dustin – were great to hang around and share about  what we do sharing SQL information and tools. Both were amazed by the end of Friday of everything that is available to them from SQLRally, SQLPass and SQLSaturdays plus blogs and webcasts. I am sure they were just like me when I first experienced all the sharing.

http://www.flickr.com/photos/62704669@N07/

Thursday, I spent the day in sessions and visiting vendors. Brian Mitchell from MS showed Parallel Data Warehouse with the single SKU for everything to some internals and client tools. A no show from a speaker (black list) cleared some time to go by Confio, Idera and Melissa Data booths.  During the week , I spent sometime with Heather from Idera about their new ACE program. Vicki and another developer from Idera chatted about the SQLdm product. I was also able to demo Ignite for David at the Confio booth. Brian McDonald did an awesome job with SSRS Boot camp, good presentation and flow even though he was late.

Kendal Van Dyke (center) and friends  Confio and Idera at SQLRally  Aaron Nelson-The Dirty Dozen, PowerShell Scripts for the Busy DBA

Lunch each day was a great box lunch with a big cookie which over road the apple for me each day. I made sure to sit with different people each day to chat about real world stuff. The last day I sat with SQL People from Utah, California, Indiana, Florida, North Carolina and of course me from Louisiana. That was great. SQLRally hit it good with this one.

Thursday afternoon I saw Scott Shaw talk about The Enterprise SQL DBA, Stacia Misner demo SSRS visualizations and Jen Underwood explain the workings of PerformancePoint in SharePoint. All in one afternoon!!! I went back to my room for room service and a review of my presentation on 3rd Normal Form: That’s Crazy Talk!!! One last review and some practice on the Visio diagrams to demo and then off to bed.

Friday at 8:30 I presented to a 2/3s full room (probably 60-70) people about database normalization. Louis Davidson (DrSQL) { Blog | Twitter } was the ‘moderator’ for my presentation. What a blessing to have a guru in the industry watching me, I hope he has some constructive criticism for me!  Lots of conversation after the session and plenty of comments the rest of the day from others. It was the first time I felt I did not leave anything out or felt I tried to explain too much. I believe it helped to run through the session with developers at work before the Rally, and take some things out to reduce the session to under an hour. I noticed a lot of sessions either did not have enough time (60 minutes) or the presenters needed to reduce the content to finish on time and not leave the attendees missing something.

The rest of the day was spent in Eric’s SSIS Data Flow Logging, Devin Knight performance tuning SSAS, Julie Smith’s Cool Tricks for SSIS, Adam Jorgenson guiding Julie in a cube creation and Excel reporting and finally watching Jeremiah flying through SQL Server internals. Man, that is a lot. But, I have learned from past SQLPass Summits that I should just try to bring 2-3 new ideas back to the office. The first week back, implement one to show the value to the bosses. Play with the second one for a month and then implement. And summarize the last to the some group or department.

God Bless,

Thomas

Thursday, May 5, 2011

My PASS Summit 2011 Submissions

Before I list what presentations I have submitted this year to PASS, I want to briefly talk about SQLRally.

SQLRally gives me a wonderful opportunity to present on Database Normalization. This passion of mine is expressed with others in the SQL Server community and I see a really need for others to teach the basics of normalization. It is not an academic knowledge of 1, 2, 3… but more importantly to help performance, reporting and now business intelligence. Another session I have started working on is Database Standard Operating Procedure which came from questions I have received from attendees at SQLSaturday and in-house at Amedisys.

The next opportunity is SQLSaturday #77 in Pensacola. Karla and gang looks like the have many sponsors and the schedule is set. I am going to attend 4 others sessions while presenting at 2.

So here are my PASS Summit submissions:

3rd Normal Form: That's crazy talk!!!

How did the development world conclude that an integer is the best primary key? What has been added to SQL Server in 2005 and 2008 to help change the way database design has evolved over the years? Can we still use a VarChar(xx) for a primary key? What is the difference between a lookup and Parent/Child relationship? What is an example of a Many-to-Many relationship? What is 4th and 5th normal form? This session will go through the history of 22 years of experience with various database designs – normalized and denormalized. The discussion will include the benefits and forward looking that should be required for using various design techniques. The flow will be a discussion with attendee participation to share success and pains in database development, leading to standards for all of us to take advantage of while designing databases.

Execution Plan Basics

This will be a Beginners session highlighting the starting point for using the execution plans from SQL Server to assist in query tuning. Briefly, we will look at the history to get an idea of how Microsoft has improved the display through Graphical Plans and Missing Index suggestion. Examples from the AdventureWorks database will be shown so anyone can take the queries after the session to try on the own, which will be encouraged. Questions will be answered like: What is the difference between a Table and Clustered Index Scan? What is a Lookup? How do you improve performance of Lookups? What are the different types of Loops? How to get more information from the Plan with the properties window? What other options are available in Management Studio to assist with query tuning?

Transition from DBA to BI Architect

Database Normalization and Dimension Modeling are the same but different. Development in today ‘s larger industries require the design and analysis of Online Transaction Processing (OLTP) to take into account Data Warehousing/OLAP (Online Analytical Processing). My transition from Senior DBA to BI Architect at Amedisys has been a process of sanding the rough edges of my passion from fully normalized databases. The Dimensional Modeling started a re-tooling of my mind to look at end result analytics and statistics from smaller and smaller transactions. From previous experience, I know I am not going to get it right the first time. Lessons learned will mold me into a great BI Guy.

RML Utilities\SQL Nexus

Microsoft Support (CSS, formerly PSS) has made available a utility to do an analysis of a trace file. This session will go through an explanation of how to use this utility and interpret the results. RML Utilities can answer questions about where the most resources are being consumed, queries that are responsible for heavy usage, changes in plans during trace and if queries are running slow in comparison to other traces

Good to all submitters and look forward to seeing y’all all in Orlando and Seattle.

God Bless

Saturday, April 16, 2011

SQLSaturday #77 Pensacola, sp_WhoIsActive & EFM

2 sessions have been selected for SQLSaturday #77 in Pensacola, FL – Execution Plan Basics and Transition From DBA to BI Architect. I really do not believe Karla has a real job because of everything she has been doing in the SQL Community, just kidding Karla.

image  image  image

The first session goes through some history of query tuning tools from Microsoft and then into a No Fluff, Just Stuff (I am boring this expression from Patrick LeBlanc’s SQL Lunch) examples in SSMS. I am really liking this session, and hope to expand for intermediate level. Need to get a SQLSaturday to accept the next session somewhere.

The second session will be the third time I have talked on a transition at my current employer over the last year. From Database Normalization to Dimensional Modeling has been enlightening. I use the Kimball books as references and some query tuning experience that has helped me gain respect from others in the BI department.

Adam Machanic has been doing a series on his ever expanding and brilliant monitoring stored procedure SP_WhoIsActive.  Part 15 of 30 helps explain CXPACKETS which is a mysterious wait that has taken me a year or 2 to full understand. Every time I read a post about CXPACKETS I learn a little more.

image

In between SQLRally and SQLSaturday, I am taking a mentoring class for EFM (Education for Ministry) in Robert, LA at the Solomon Center. EFM is a 4 year course of learning about the Old Testament, New Testament, Church History and Theologians in the Anglican (Episcopal) Church. I hope to be an assistant then mentor in EFM in the near future. I completed the course from 2003 – 2007.

Welcome To The Solomon Episcopal Conference Center

There is nothing better than working on the Spirit and Mind from the Bible. Yes, even better than SQL Server and work.

God Bless

Thursday, March 31, 2011

Data Architecture Chapter restarted

 

Here is a link to @SqlSoldier blog on the restarting of the PASS Data Architecture Virtual Chapter.

I was involved with the Performance Chapter for about 6 months, until I changed positions at Amedisys.

http://www.sqlsoldier.com/wp/sqlserver/darcvcreborn

If you would like to speak, please email us from the Virtual Chapter site

PASS Data Architecture Virtual Chapter

This weekend is time for some spiritual food listening to speakers talk about the walk with God. It will be nice to take a break from life, and work on the spirit.

God Bless,

Thomas

Visit the site: http://dataarch.sqlpass.org/
Follow us on Twitter: @DArchVC
Like us on Facebook: Data Architecture VC

Next Meeting Announced

Subject:
Database Normalization

Start Time:
Thursday, April 21, 2011 12:00 PM US Central Time (6:00 PM GMT)

End Time:
Thursday, April 21, 2011 1:00 PM US Central Time (7:00 PM GMT)

Presenter:
Thomas LeBlanc (blog|@TheSmilingDBA)

Live Meeting Link:
https://www.livemeeting.com/cc/usergroups/join?id=28NR92&role=attend&pw=PTSs%26%5E%282Z

Database Standards SOP
Join Normalization nut Thomas LeBlanc for a review of a standard operating procedure used among DBAs at an employer. See the changes he made after joining the BI group at this employer. The session will go through naming conventions, check list for creating a table, formatting in stored procedures, and more. A brief preview of the SQLRally talk 3rd Normal Form: That’s Crazy Talk!!! Will be given about Lookup tables. This discussion comes from 21+ years of developing databases for application developers. The use of identity columns for primary keys, and the need for a unique constraint on transaction tables that do resort to ID columns will be covered.

Add to calendar Add to your calendar

Saturday, March 19, 2011

SQLRally

Well, I have my flight booked and registered as a speaker. Looking over the speaker provided schedule, there are many sessions to choose from in the Business Intelligence track. My first goal is to get as much SSIS and Data Mart design training as I can over the 2 days of sessions.

I still have not decided about what Pre-Conference session I want to attend. Grant Fritchey’s Query Tuning is number one on the list, but learning BI from the Pragmatic Motley Crew would probably benefit my career as a better choice. Then, there is Kevin Kline teaching Personal Development. Since I will be attending regular BI sessions, I am leaning towards Kevin Kline’s PD, with Grant a close second (you can never get too much performance tuning advice).

sqlrally_banner

This past week at work, I was tasked with adding columns to a Fact table that a consultant started to work on before the end of the contract with their company. I noticed performance problems with the additions to the Inserts and Updates. After investigating, I found out using NOT Null and Default on the Alter Table Add column was best since some of the existing rows were not going to have values. The ETL had to be changed for Daily and Monthly updates.

The Monthly Updates were taking between 4 and 8 hours. After digging a little more, the Monthly Updates where not updating just the current period, but all data. By Adding a WHERE CURRENT_PERIOD = ‘Y"’, the processed reduced to 10 minutes.

Also, a fact table had 6 Non-clustered indexes, no primary key and no clustered index. This is taking a little more work because of the structure of the fact table, but an additional clustered index after removing all non-clustered. Then, adding Non-cluster indexes back after updating their structure to exclude the new non-clustered columns were appropriate, help take the 9-10 hour process down to 3 hours 10 minutes. Also, a big thanks to the Network/Infrastructure group that has enabled us to have a dev/test/prod environment that are almost all equal. That is a blessing not many shop s have.

Unbelievable!!! Whenever I am able to work on something like this, I have a tendency to try and tune more and more and more… never finishing. But it also reinforces all the training and real world experience in action and validate I am on the right track.

There is so much to learn in the Database world, but stopping and concentrating on a few items to become an expert gets harder and harder.

God Bless,

Thomas

Thursday, February 17, 2011

Got an Issue, get a Tissue…

I heard a preacher say this in a sermon, and thought about ‘issues’ on our servers. Rather than just complain about developers, I decided to tackling the queries my self. So, here is a list of the ones I dealt with in the past month or 2. please enjoy.

CXPACKET is not the problem 

I found a query in our month end process that had a lot of CXPACKETS. This was discovered through Ignite8 from Confio. This tool brings to the top of the heap, the most costly queries based on waits. I can also just look at a day, like end of moth, or even a database among other slice and dice capabilities.

image

I took the query and placed it in SSMS, and did a Display Estimated Query Plan and got an index suggestion that would improve performance 99%. It was the Sub-Query columns, PatNo + LocationCode with Include Column ClaimDate. The new index took about 15 minutes to create on a 48 million row table, but the Cost went from 3300+ to <5. WOW!!! From a 3-4 hour data retrieval to ~1 hour and no more parallelism.

image

Thanks again Confio!!!

 

Bad SQL

Over the last 6 months in the BI group, I have discovered some SQL that turned bad after more and more rows accumulate in the tables that are queried. The problem with the query is that the function has to be performed on ever row.

Taking these trims out, which were not needed because it was populating a staging table, then comparing to the Fact table, reduced the TempDB usage tremendously. If you look at the SET STATISTICS IO ON values below, you can see the before and after effects.

WHERE LTrim(TRim(column) = LTrim(RTrim(column)

WORKTABLE used

Table 'Worktable'. Scan count 1929907, logical reads 33462081, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'AUDIT_STAGING'. Scan count 10, logical reads 193381, physical reads 161, read-ahead reads 26709, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'AUDIT'. Scan count 6, logical reads 85351, physical reads 20, read-ahead reads 82695, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

WHERE column = column

Removed RTrim(LTrim())

(4622 row(s) affected)

Table 'AUDIT_STAGING'. Scan count 2, logical reads 188552, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'AUDIT'. Scan count 2, logical reads 145602, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The ‘Worktable’ was being created in TempDB, thus slowing down the query. Amazing what some investigation can do

Answer To SQLSaturday #57 Question

Hash and non-hash Aggregate Streams – Someone in the session Execution Plan Basics asked me what was the difference between these two, and I did not have an answer. Rather than trying to make something up, I told the person I did not know, explained how many different new Execution Plan objects I see all the time, and said I would find out. The SQL Community is amazing!!!

Well, Craig Freedman’s blog answers this question.

Here is a quote from him:

“Stream aggregate is great for scalar aggregates and for aggregations where we have an index to provide a sort order on the group by column(s) or where we need to sort anyhow (e.g., due to an order by clause).

The other aggregation operator, hash aggregate, is similar to hash join.  It does not require (or preserve) sort order, requires memory, and is blocking (i.e., it does not produce any results until it has consumed its entire input).  Hash aggregate excels at efficiently aggregating very large data sets.”

God Bless,

Thomas