DBA Blogging With A Smile

Baton Rouge SQL Server User Group

2012-02-06T17:59:00.001-08:00

I will be speaking at the February Baton Rouge SQL Server User Group meeting on Wednesday the 8th. The session will be an continuation of Execution Plan Basics that I shared last year and at 2 SQL Saturday events plus Houston Tech Fest in 2010. This one is called Execution Plan: Beyond The Basics. The topics include Digging deeper into Loops, Missing Indexes in Query Plans, WHERE clause and LEFT JOIN, TempDB usage, SQL Server 2012 improvements to Query Plan and Tools like SQLSentry’s Plan Explorer, Database Tuning Advisor and SP_WhoIsActive from Adam Mahanic.

I am hoping this session gets pick by some SQL Saturday events in the future, first at Houston with Nancy Hide Wilson and Jonathon Gardner. Still looking at Pensacola and waiting for John Sterret and gang in West Virginia to add one to the schedule. Really would like to do a half day execution plan session, that would be nice.

More Info:

February '12 Baton Rouge SQL Server User Group Meeting

Topic: Execution Plans: Beyond the Basics

Date: Wednesday, February 8, 2012
Location: LSU Campus, Turead Hall Room 103 - http://g.co/maps/xb5pf

Agenda:
5:45 - 6:15 pm: Networking and Refreshments
6:15 - 6:55 pm: Lightning Round Topics
6:55 - 8:00 pm: Main Topic
8:00 - until: Open Forum for questions and Raffle
Afterwards: An open invitation to join us at Walk-Ons
Lightning Round Speaker: Patrick LeBlanc, Microsoft

Topic: New Features of SQL 2012

Lightning Round Speaker: Matt Maddox, Lamar Advertising

Main Topic:Execution Plans: Beyond the Basics
Main Topic Speaker: Thomas Leblanc, Turner Industries
Summary: This will session will be a deeper look into execution plans for query tuning. The Nested, Merge and Hash Join loops will be discussed. A look at TempDB usage when memory grants are not enough for the plan. How do you use MAXDOP and Cost Threshold to understand parallel plans? We will examine Sort, Compute scalar and more iterators for performance tuning. Missing indexes in query plans will be used to help performance.

SQL Saturday #104 Colorado Springs, CO

2012-01-25T19:57:00.001-08:00

Janet, my girlfriend, and I went to Colorado Springs a day early, Thursday, to ski on Friday before SQL Saturday #104. It was her first time skiing and for me it has been about 3 years since the last trip on a mountain. The altitude at Monarch was about 10,700 feet above sea level at the base, and this had me breathing hard just going up the stairs.

We meet up with Jason Horner and Christian Leo on Monarch Mountain. It was great to spend some social time with other SQL geeks before the event. Janet is trying to get used to techy talk but at least she is trying.

I meet Jeremy Lowell from Data Realized as sponsor of the Friday night event. They gave us ski vests with SQL Patrol on the back like the Ski Patrols on the mountains. The ski theme was even part of the schedule, Chris Shaw and gang did a great job. There were others that promoted the event and helped the day go by that were awesome.

I attended an Introduction to BI which was given by 2 individuals, Marc Beacom and someone else, because it was right before mine. The rooms were small, but packed 20-30 people. I thought that would be a problem, but actually was good for everybody could see without using Zoomit. They introduced a new term for Data Warehouse calling it a Data Vault. Very interesting!!!

My session was on Dimensional Modeling 101. I tried to demonstrate methods from the Kimball Group while showing flow in SSIS and display in SSAS. The reviews were what I expected for the first time doing this session – from excellent to you were all over the place. 60 minutes is just not enough time, need 75-90 minutes.

The best session of the day was with Thomas LaRock and Jason Strate, that I attended. It was an attendee group effort to get to the root cause of a performance problem. Jason drove while Thomas guided. I am are sure this helped every one understand that the native tools of SQL Server can diagnosis a problem while helping all learn how to cooperate with each other to solve a problem. They also guided us in how to communicate with other departments and the importance of being able to say the right things and showing your worth in the company.

Lunch was spent socializing with attendees and catching up with some SQL friends William (Bill) Pearson and Grant Fritchey. Janet and I talked more with Grant waiting for the plane Sunday morning.

Mike Fal did an excellent job with Partitioning, so good he is doing the session for the PASS Performance and Data Architecture Virtual Chapters. Also, David Eicher showed some tips for using maps in Reporting Services. I caught some of Always ON 2012 from Austin nut Jim Murphy.

I am amazed that I gained all this knowledge and excitement and fun from a SQLSaturday. This site is loaded with 20-30 events coming up in the next 4-5 months, so please do not missed these opportunities to learn and/or teach.

God Bless, Thomas

PASS Data Architecture VC presents: Audrey Hammonds–Database Makeover: Renovate Your Data Model

2012-01-17T17:27:00.001-08:00

Subject: Database Makeover: Renovate Your Data Model

Start Time: Thursday, January 19, 2011 12:00 PM US Central Time (January 19, 2011 6:00 PM GMT)

End Time: Thursday, January 19, 2011 1:00 PM US Central Time (January 19, 2011 7:00 PM GMT)

Presenter: Audrey Hammonds (Blog|@DataAudrey)

Live Meeting Link: https://www.livemeeting.com/cc/UserGroups/join?id=QZCH9D&role=attend&pw=g3d%3F%7BspDw

Database Makeover: Renovate Your Data Model
You know the concepts of normalization, logical modeling, and physical implementation. But what happens when you’ve inherited a less than perfect data model? You need to renovate while keeping the production system humming. In this session, we’ll talk about how to evaluate an existing model, how to approach design when your system is up and running and how to incrementally apply database design changes in a fast-moving environment. Every database, even ones consisting of a collection of flat, unrelated tables, has the potential to become a beautiful database. All you need is an eye for design and a strategy to get you there.

Audrey Hammonds
Audrey Hammonds is a Database Developer for ista North America in Atlanta, GA and a SQL Server MVP. Fifteen years ago, she volunteered for DBA training to escape COBOL, and never looked back. A firm believer that good fundamentals and solid design can save a database professional’s sanity, she has devoted much of her career to designing (hopefully) elegant databases and straightening up others’ not-so-elegant databases. She blogs at http://datachix.com with her partner in crime, Julie Smith.

Add to calendar

SQL Saturday #104: Colorado Springs

2011-12-23T14:22:00.001-08:00

January 8th, 2012 will be the first SQL Saturday for 2012. 104 SQL Saturdays is an amazing number for our community. I will be presenting on Dimensional Modeling based on the Kimball books. Database design and the planning for them has been a necessary start for successful projects.

I selected Colorado Springs because I have not been skiing in 3 years. My girlfriend and I will be skiing on Friday before the event. There is a planned skiing trip for Sunday with the event, but we have to get back work on Monday. She will be visiting a friend while I am attending/speaking at this great event.

There are 3 Pre Cons on tap for Friday, which is well worth the $100 for a day of training from MVPs and SQL Server experts. I would be at one if not for skiing.

1. "Scaling SQL Server" (Glenn Berry)
2. "Data Warehouse Dimensional Design and Architecture Planning" (Erik Veerman)
3. "What's In Your Utility Belt?" (Chris Shaw and TJay Belt)

The Saturday schedule is pack full of great sessions. I will probably will go over my presentation first thing in the morning in the Speaker room, then I am up at the second BI session of the day at 9:30AM. Next, I will stay in the BI room and watch Doug Lane with some report service info.

The other tracks have sessions from MVPs Tim Ford, Grant Fritchey and Jason Strate, all great and knowledgable speakers. There is also Thomas LaRock, Karen Lopez and William Pearson. It is amazing to be around these people in the SQL community willing to speak at these Free Saturdays. Joe Sack will be there from SQLSkills and Colorado local Carlos Bossy and Austin man Jim Murphy have some SQL Server 2012 sessions.

I will probably hang out in the BI track, wondering over to DBA sessions and enjoying the lunch. It sounds like there will a great Friday night speaker/sponsor event that is great for networking and thanking the sponsor.

If you live around Colorado Springs, come hang out with some SQL Server geeks getting some SQL learning on!!!

God Bless, Thomas

PASS Data Architecture VC: Jeremy Huppatz - Row Versioned Data Warehouses

2011-12-14T18:24:00.001-08:00

Subject: Row Versioned Data Warehouses

Level: 200-300 (Intermediate)

Start Time: Thursday, December 15th, 2011 8:00 PM US Central Time

Presenter: Jeremy Huppatz (Blog|@OzzieMedes)

Live Meeting Link:

https://www.livemeeting.com/cc/UserGroups/join?id=B77TSH&role=attend&pw=dGC-%3B88%275

Row Versioned Data Warehouses
Jeremy will discuss the strengths and disadvantages of a row-versioned data warehouse design in the context of a real world case study, sharing lessons learnt and demonstrating some of the technologies and techniques used to build a row-versioned data warehouse. He will discuss row-versioning in the context of Kimball slowly-changing dimensions, and will also provide some details on the relative strengths of row-versioning as applied to measure information in situations where such data cannot be considered finalized at the time it is loaded into the warehouse.

Jeremy Huppatz
Jeremy has been a SQL Server data guy going back as far as 1997. During his 14 years working with Microsoft’s flagship database, he has filled the roles of DBA, data modeller, database designer, data analyst, BI developer and data architect in a multitude of projects, which have included departmental database apps, enterprise data warehouses and a bit of just about everything in between. Jeremy now runs his own IT consulting firm called Solitaire Systems and lives in the scenic Adelaide Hills with his partner Alison and her cat. He also plays, writes and records music and writes in his spare time, and has been described optimistically as an avid (as opposed to obsessive) computer gamer.

DataArch.SQLPass.org

The PASS Data Architecture Virtual Chapter will focus on data architecture concerns as they impact users, developers and DBAs on the Microsoft SQL Server platform. We want to make data architecture accessible to all data practitioners, and drive the point home that Data Architecture is a set of practices and a body of knowledge that overlaps almost all database professionals to some degree.

PASS Data Arch. Virtual Chapter: On beyond Zebra AdventureWorks OR where did I go wrong?

2011-11-15T19:05:00.001-08:00

Subject: On beyond Zebra AdventureWorks or where did I go wrong?

Level: 200 (Intermediate)

Start Time: Thursday, November 17, 2011 8:00 PM US Eastern Time (November 18, 2011 1:00 AM GMT)

End Time: Thursday, November 17, 2011 9:00 PM US Eastern Time (November 18, 2011 2:00 AM GMT)

Presenter: Steve Simon (LinkedIn)

Live Meeting Link: https://www.livemeeting.com/cc/UserGroups/join?id=K32S7T&role=attend&pw=q%3Fp4cW%24Mg

On beyond Zebra AdventureWorks OR where did I go wrong?
No matter how well planned and executed, the data structures of any data warehouse oft times eventually land up resembling something out of a Dr. Seuss book. In this presentation we shall be looking at and discussing a few of the “perfect” data structures (that I have inherited) and discussing the flaws that have only recently surfaced. We shall be looking at the ‘before’, discussing a few alternatives to make these structure more efficient and effective, then we shall then look at final “production structure” and the resulting improvement metrics; all of which help us to help our clients’ make better decisions in a timely manner.

Steve Simon
Steve Simon is a Senior Business Intelligence Engineer with MyWebGrocer in Burlington Vermont. He has been involved with database design and analysis for over 26 years. Steve has presented papers at eight North American PASS Summits (in Orlando, Seattle WA (4), Denver CO (2) and San Francisco CA), two at PASS Europe 2009 and one at PASS Europe 2010. He has just recently presented at the Johannesburg and Cape Town SQL Server Saturdays.

Steve has also had two papers published in Information Builders’ Systems Journal, is the chairman of the Boston User Forum and is a regular User Forum and Webinar presenter for Information Builders.

Add to calendar

PASS Summit 2001 In Review…WOW!!!

2011-10-28T16:42:00.001-07:00

It has been a couple of weeks and I have had time to get back in the groove at a new job as Senior SQL Server DBA, after trying Informatica Administrator via Enterprise Architect for about 3 months. Long story, but I am glad to be back as a DBA.

My week started on a Sunday travel day where I got to Seattle about 8pm Pacific and close to 10pm getting to bed at the 6th Avenue Inn after taking the Light Rail. The rail was cheap ($2.75), but took at little more work and time. The hotel suggestion was from a blog by Brent Ozar and cost ~$600 for Sunday thru Saturday.

Monday was off to Rooooobbbb Farley’s Query Tuning Pre-con. I learned a lot about Residual Predicates and GROUP BY versus HAVING versus WHERE clauses. He was ad-hoc writing queries to show performance metric and I hope to get a copy of the sql before long to review.

The second day was about networking. I started in the Virtual Chapter leaders meeting with Geoff and Karla, They did a good job informing us about options and sharing from different chapter leaders. Our Data Architecture VC can give away a $25 gift that I did not know about. If we get a sponsor, up to $100 a session. It was nice to talk with the DBA VC which had 3 people on attendance, and I got a nice shirt, that I wore while manning the VC tables during the Welcome Reception.

Tuesday was not over. Next, SQLSaturday leader meeting. This was led by Andy Warren who is a well-seasoned leader and moderator. Various suggestions were given for the website, which I think is invaluable, no matter what does not work perfectly. Anything free is nice in my opinion. Lots of information in starting a Pre-con for a SQL Saturday and talk about speaker cancelations. This seems to be a growing concern from leaders and ways were discussed to help grow more speakers and get speakers to notify about needs to cancel.

Above are pictures of Andy, Karla and the PASS IT dude. And right is Greg Larson and Sri.

The second half of Tuesday was spent at Adam Machanic’s Pre-con and performance tuning. Man, this dude knows his stuff. He and Alan White helped me understand the difference between CXPACKET problems, versus request Waits through twitter and blogs. sp_WhoIsActive is a tool you have to learn, plus read his 30 days of explanation on what it does. Adam is well organized and well rehearsed. His training is a must.

Tuesday evening was the Welcome Reception and SQLServerCentral.Com and RedGate’s Casino night. The exceptional DBA ward is given at the later event.

Wednesday morning was time to tackle the regular sessions. I made it a BI day. Started with Craig Utley from SolidQ doing SSAS Aggregates. Then, Devin Knight from PragmaticWorks presenting common SSAS mistakes. Both sessions were great and well prepared. Lunch was Chapter day and wear your SQL Saturday shirts. Jen Stirrup presented on PDW and reporting which got me wanting to investigate more into the monitoring of instances. Last was Many to many relationships in DAX by Alberto Ferrari. The last session kind of got me thinking about how to model the data to not have to write expressions in Excel. Wednesday night was Vendor appreciation dinner at the Summit. I visited with SQLSentry, Confio and Idera, ending with Quest while I had questions about Spotlight used at my current employer.

Thanks to DELL for my favorite place at PASS!!! And thanks to Robert from BCBS helping me find this treat.

Thursday morning I got to blog from the bloggers table at the morning Keynote. See previous blog for my take - http://thesmilingdba.blogspot.com/2011/10/sql-pass-2011-live-blog-keynote-day.html. It was really cool to sit next to Kevin Kline and Stacia Misner among others.

Thursday morning was Reporting Service Tips and Tricks from Bob Meyers. I really enjoyed this session. Though my favorite session on the whole week was Ami Levin talking about Physical Join operators. After I go through his examples again, and apply the lessons to some real world queries, I will have to blog about it. Do not miss this guy, he was well organized and very knowledgeable. I might be wrong about the best session, because next was Kalen Delaney talking Plan Cache. 500-600 people attending can tell you she knows her stuff. Check out her internals book for more info - http://www.sqlserverinternals.com/books.html. Last was a chance to see a Kimball employee Joy Mundy talked about Multivalued Dimension relationships – no demos and all talk left me wanting more. Maybe my brain was already toast.

I took Thursday might off to head to my room and grab a bite. There was a need to rehearse my 3rd Normal Talk before Friday. Well rested, Friday morning started. First up, Adam Machanic again with a 500 level Query Memory session. Another great, well prepared session with demos. After lunch, I briefly went to Peter Meyers talk about KPIs in the MS BI Stack. Peter came to SQLSaturday in Baton Rouge, so I want to say hi and thank him again. All the SolidQ presenters are great in the information they share and well prepared. My talk happened fast. Only 2-3 people left out of ~150 and 5-6 questions at the end. Hope to learn from comments. I think next time, I will not go to 400 or 500 level sessions before my 200 level. I felt I did not present enough advanced stuff, but I know the database normalization talk is needed in the development community. Last, I took it easy in a T-SQL session with Aubrey Hammonds. She showed some CTEs and other cool SQL. It was a good break from the other advanced sessions. The data Architecture VC will have her presenting in the future.

The PASS Summit is a not miss event. I would rather go to it than any TechEd event. No other conference have I been able to see networking, fellowshipping and free sharing of knowledge ever. I am so glad I have been able to meet people like Grant Fritchey, Sean and Jen McCown, Andy Warren and Steve Jones just to name a few. And thank the companies that sent or let me go.

God Bless and GEAUX Tigers!!!

Thomas

Jeremy Huppatz presents Row Versioned Data Warehouse

2011-10-20T03:39:00.001-07:00

Subject: Row Versioned Data Warehouses

Level: 200-300 (Intermediate)

Start Time: Thursday, December 15th, 2011 8:00 PM US Central Time

Presenter: Jeremy Huppatz (Blog|@OzzieMedes)

Live Meeting Link:

https://www.livemeeting.com/cc/UserGroups/join?id=B77TSH&role=attend&pw=dGC-%3B88%275

DataArch.SQLPass.org

SQL PASS 2011: Live Blog Keynote Day 2–Quentin Clark

2011-10-13T07:56:00.001-07:00

7:50AM – arrived at bloggers table and positioned between Rooob Farley and Kevin Kline. Just meet SQLBalls, in front is Ryan Adams and Mike Walsh.

Hopefully, this will be a demo Keynote and not just talk, talk, talk…

8:10AM – Room is filling up, cannot believe so many people here at #SQLPASS, they are saying a 22% increase from last year. All the rooms are pack, some have people on the floor. Andy Warren and Stacia just sat down and are typing and talking. John Sterret just came and said hi.

8:25AM – Laptop crashes OOOHHH!!!!

8:30-8:40am – Bill talks about the wonderful volunteers around the country for SQL Server. Lori Edwards receives PASSion award. Tim and Jeff mentioned about their work

8:45AM – Quentin Clark invited Bob Erickson from Interlink Transport – talks about operations and uses of SQL Server (Mission critical is Their Business)

8:50AM – demonstrating Always ON for Interlink – Wizard setup and ASync versus Sync. Listening is a DNS name and connect to name and does the redirecting

8:55AM – on to Blazing fast speeds – VertiPak built into Analysis Services, takes advantage of the technology and in the RDBMS. Some customers are reporting 10, 20 & 30 times processing speed.

9:00AM – Power View + PowerPivot – SSAS on the back-end but control by IT (SharePoint). Alerts on reporting, configurable. A BI Semantic Model – ‘who do you know…’ Semantic understanding. As the model is used, the engine learns from queries. Now, to get it in a data model. Master Data Services – managing the location of where everything is located.

9:05AM – @SQLGal is on stage. Using Contoso database. Columnstore index demonstration on query in report going from 45 seconds to .13 seconds after ColumnStore index and linking data to Cloud location for objects in map not appearing where there are actually located. Also,

9:10Am – Parallel Data Warehouse – Appliances. Deep Dive to workloads, and understand characteristics and find the architecture to solve problems. Appliance developer kit to fit customer. Demos DELL parallel warehouse, more than one appliance.The stage has the DELL racks for PDW. And th HP rack that has been out over a year. Also, have your own Private Cloud.

9:20AM – ODBC drivers to Linux – more and better drivers.

9:25AM – integrating CDC with Oracle, SQL Server 2012 has it – Now Beyond relational. file Stream 2D Spatial, Semantic search

Beyond full-text search – better to be seen – Demo time

The demos need some Zooming

Semantic search extracts the keywords from the documents. More than Full-text search.

9:30pm – ‘Juneau’ – now SQL Server data Tools -

Now embed SQL Server Express DB in an Application

Scaling to the Cloud – spin up many many instances in the cloud to distribute queries – DEMO time

Nicholas Dritsas SQLCAT team – SQL Azure demo

9:35AM – Who is @CajunSQL – did not know of any other CoonAxxes here at PASS

9:40AM - Real customer demo with SQLAzure – moved to cloud form

9:45am – Using Federation in the Cloud – sizing and scaling in the cloud

Example is a blog site that does not know what blog goes viral – Blogs r’ Us

No developer rework of code

Neat interface/management tools – Schema walker

Overview of tasks going on – Powerful reporting

Ability to create large database – up to 150 GBs in size

9:50am – Azure and SQL in same code base.

NOW FOR SOME TRAINING!!!

God Bless,

Thomas

PASS Summit 2011: Day One

2011-10-10T21:07:00.001-07:00

After getting a little lost from the rail to the hotel, I finally got to sleep Sunday night after a 10+ hour airplane/airport travel time. Well worth it especially after seeing LSU whoop up on Florida in Tiger Stadium on Saturday afternoon.

Started by meeting Robert from BCBS to have an early registration and breakfast and the networking started at 7:30am with a gentleman from New York and another from California. We chatted about performance tuning on SQL Server as well as Sybase. Rob started to see what I meant about the value of PASS right away. He went to Brent Ozar’s SANs and Virtualization Pre-con and I hit Rob Farley’s 1/2 comedy/music show and Advanced TSQL for query tuning.

Lunch break had Robert and I at a table with Ryan and David from Dallas talking about SQL Saturday’s. We also had some new friends for lunch to chat about different companies we worked for.

The second half of the day was more Rob Farley and TSQL. He was able to explain some property window information about query plans. Never realized sometimes you have to read the plan from left to right to really understand them. WHERE and HAVING clauses were explained in more detail. The highlights was a new term for me: Residual Predicates. You really need to read up on this to understand advanced query tuning.

Rob even helped promote my 3rd Normal Talk on Friday.

The evening was a well organized Networking dinner that Andy Warren and Steve Jones put together at Lowell’s. The food line moved at a good pace for outside conversations, but the food was served fast and it was good. Saw @SqlDiva from New York and Kathy K from Microsoft. Idera was there with Dave and Heather.

Robert and I called it a night at 10PM CST (we are on Pacific time, so it was 8pm), but we were both tired.

Tomorrow is all about networking with Virtual Chapter leaders, local user group leaders and SQL Saturday organizers. Should be interesting.

Here are some links:

Rod Farley Residual Predicate - http://sqlblog.com/blogs/rob_farley/archive/2011/03/22/probe-residual-when-you-have-a-hash-match-a-hidden-cost-in-execution-plans.aspx

Lowell’s in Seattle - http://www.eatatlowells.com/

24-Hours of PASS DB SOP - Standard Operating Procedure for Normalized Database Design

God Bless,

Thomas LeBlanc

PASS Summit 2011: My Itinerary

2011-09-29T19:18:00.001-07:00

Here goes with what I hope to achieve the following week in Seattle, WA Oct 9-15 for my 4th summit.

I have spent about 1 hour a day reviewing what I have selected for the week with the really cool Build Your Schedule for Summit 2011.

Sunday Oct 9th is travel day, which starts about 1PM central in Baton Rouge and end at 9pm Pacific in Seattle. I will use this time to review my presentation and read parts of Louis Davidson’s book about relational databases.

Monday morning I hope to have breakfast will some SQL friends (Rob) and head to Rob Farley’s Pre-con on Fixing Queries with Advanced T-SQL constructs. I saw a session on 24 Hours of Pass by Rob, and realized I need to learn from this guy. He seems to have some real good customer/employer experience with queries, plus a dynamic personality. I like the fact his abstract says there will be not much PowerPoint

Monday night Andy Warren and Steve Jones have a meet and greet social that I hope to attend with a new Summit person I encouraged to attend. Hopefully, I get to see John from West Virginia that night. Cool dude!!!

Tuesday is full of social activities with SQL people that volunteer on different levels. First, 8am meeting with Virtual Chapter leaders. I am the Co-Chair with Lorra Newton on the PASS Data Architecture VC. After that, there is a meeting of SQLSaturday people up until lunch. The chair of local PASS chapters meet in the afternoon, and I am going to try and represent the Baton Rouge chapter for William Assaf and Patrick Leblanc. I really hope to meet some new faces in the SQL Server world during this day. There is always someone new that is so fired up about helping the community. I love being around these people of just listen and encourage.

Tuesday night is the Quiz bowl and Welcome reception which is always entertaining.

In addition to Quiz Bowl, Tuesday night is the best evening at Pass with SQLServerCentral.com and RedGate Casino Night. Steve Jones is a great guy to be around and SQL Server Central is where I got my first look at how giving the SQL Server community is with information. Thanks Steve, Andy and Brian!!!

If I do not get to blog the summit keynotes, I will skip the first keynote and go to the Lab and play around with Denali. These hands on labs are a great way to introduce new stuff. I will go to the Keynote on Friday with David DeWitt - http://www.sqlpass.org/summit/2011/SummitContent/Keynotes.aspx. Maybe Thursday morning I will go chat with the SQL First Aid station and just listen in on some debugging. If you have a problem, this is the place to be to get Microsoft Support to assist. I have used these people with replication problems in the past.

Wednesday starts the 3-day summit with 75, 90 and 1/2 day sessions. It is really hard to pick, but here goes.

I have multiple options for Wednesday thru Friday, the only sure thing is my 3rd Normal Form: That’s Crazy Talk!!! on Friday at 2:30pm.
Wednesday Oct 12th

10:15 AM - 11:30 AM -
[BIA-404] Add It Up: Analysis Services Aggregations
[RM:4C3]

1:30 PM - 2:45 PM
[BIA-305] Common Analysis Services Design Mistakes and How to Avoid Them
[RM:602-604]
1:30 PM - 2:45 PM
[DBA-304] SQL2008 Query Statistics
[RM:2AB]
1:30 PM - 4:30 PM
[DBA-500-HD] Inside Tempdb
[RM:6E]

3:00 PM - 4:15 PM
[BID-306] Intelligent Laziness with the Management Data Warehouse – why work harder, when you can work smarter?
[RM:608]
4:45 PM - 6:00 PM
[AD-318] Characteristics of a Great Relational Database
[RM:602-604]
4:45 PM - 6:00 PM
[DBA-320] Why are we Waiting..
[RM:606-607]

Wednesday is the Exhibitor Reception and I need to get with Quest people because my new employer uses SpotLight. Still need to say hi to Confio who have Ignite, the best Wait State performance monitor around.
Thursday, October 13, 2011

10:15 AM - 11:30 AM
[DBA-403] Advanced SQL Server 2008 Troubleshooting
[RM:6E]

1:30 PM - 2:45 PM
[BID-301] Reporting Services Techniques and Tricks
[RM:613-614]
1:30 PM - 2:45 PM
[AD-400] Physical join operators
[RM:615-617]
1:30 PM - 4:30 PM
[BIA-303-HD] So How Does the BI Workload Impact the Database Engine?
[RM:606-607]

3:00 PM - 4:30 PM
[DBA-314-S] What Happened? Exploring the Plan Cache
[RM:6E]

5:00 PM - 6:15 PM
[BID-303] Creating Business Intelligence Dashboards with PerformancePoint Services 2010
[RM:4C3]
5:00 PM - 6:30 PM – I have to see at least one presentation by Peter Myer
[BID-305-S] End-to-End SQL Server PowerPivot
[RM:2AB]

Thursday night I am taking the night off, have some dinner and rest for my presentation on Friday afternoon. I might go by the Community Appreciation Party sponsored by Microsoft for a quick bite.
Friday, October 14, 2011

10:15 AM - 11:45 AM
[AD-500-S] Query Tuning Mastery: Zen and the Art of Workspace Memory
[RM:6E]
10:15 AM - 11:30 AM
[DBA-402] Windows Operating System Internals for Database Pros
[RM:4C1-2]
10:15 AM - 11:30 AM
[BIA-307] Vertipaq vs OLAP: Change Your Data Modeling Approach
[RM:608]

1:00 PM - 2:15 PM
[BIA-308] Delivering KPIs with Analysis Services
[RM:602-604]

2:30 PM - 3:45 PM
[AD-206] 3rd Normal Form: That's crazy talk!!!
[RM:609-610]

4:15 PM - 5:30 PM
[AD-303] Parameter Sniffing: the Query Optimizer vs. the Plan Cache
[RM:608]
4:15 PM - 5:30 PM
[BID-302] Multidimensional Reporting: MDX Essentials for Report Design
[RM:602-604]

Friday night find some people to have dinner and wind down, maybe blog some about the Summit. This is definitely the best week of training and networking I have ever experienced. Please go if you ever get a chance.

Saturday I travel home and hopefully I get some sleep on the plane and get some idea how LSU is doing against Tennessee. GEAUX TIGERS!!!

24 Hours of PASS: Answer questions…

2011-09-20T08:25:00.001-07:00

I will try my best to answer some of the questions after the Standard Operating Procedure webcast for 24 Hours of PASS.

First one is what design tools are out there. Here is a list I found from Louis Davidson’s book Pro SQL Server 2008 Relational Database Design and Implementation:

All Fusion ERwin Data Modeler
Toad data Modeler
ER/Studio
Visible Analyst DB Engineer
Visio Enterprise Edition

I would not suggest using the one built into SQL Server, because if you change the design then and save it, the tables will be updated automatically with the changes.

The second one had to do with having views in a data modeler tool and showing the relationship between other tables or views: I do not have a good answer to this, and have asked others with no available tool.

3. What ER diagram tool are you using for the presentation? Which do you recommend?

I used Visio for my presentation because it is what I have a license for, but the companies I have worked for recently use ERwin.

4. Where can we download the example document I used?

I cannot let you have it because it is from a company I worked for, but I did relink the Parts 1 thru 6 of a generalization of the SOP.

5. One comment was for me to read up more on normalization –

This is true, I gave a brief (very brief) explanation of 1st, 2nd and 3rd normal forms. I should not have done that and just referenced a website. Or suggested to go to the PASS Summit and watch my session 3rd Normal Form: That’s crazy talk!!!

6. Singular or Plural on table name?

Singular, like Louis says, pick a standard and be consistent.

7. The CHAR(1) for IsActive is much better for using the column directly in Reports (for example - If the value is Y or N it is much easier to simply write the sql to display than to logically determine the meaning of the Boolean.

That is a good point, reporting is very important. Some report writers might even convert Boolean to a text equivalent.

8. Please repeat the contact/blog/web site information. Thank you! --> Here it is

9. Will the recording be available - answered yes.

Keep checking back on 24HOP site, or you can watch it from the PASS Data Architect Virtual Chapter

10. will there be an example of the SOP available? –> see Number 4 above

11. Have you found an ERI application which allows joins to be defined on a view. Visio and Erwin do not allow this. –> see Number 2 above

12. I recall sometime ago while trying to use Visio 2010 to import SQL 2008 database for database diagrams it wasn't supported. Any ideas on future support for Visio and database diagrams?

Look on the database menu choice to reverse engineer a database, then it connects thru ODBC (DSN)

13. Preferred data type for ID?

It depends on the amount of rows in the table, but you can go with tinyint, smallint, int or biint

God Bless,

Thomas

Database Standards: Links to past blogs

2011-09-08T18:54:00.001-07:00

After a great session on 24 Hours of PASS, lots of people wanted the SOP document. While I cannot give the one from the company to you, here is a post of the links to the blogs about them

Parts 1 thru 6 and Lookup tables. Please review and place comments on the blog!!!

Database Standards Part I: Defs, Abbreviation & Data Types

Database Standards Part II: Schemas, Tables & View and Columns

Database Standards Part III: Indexes, Constraints & Primary/Foreign Keys

Database Standards Part IV: Stored Procedures

Database Standards Part V: Triggers and User-defined Functions

Database Standards Part VI: code and design

Lookup tables

I will answer the other questions in a blog next week as soon as I can get come reliable answers. Thanks again for attending and all the great questions and comments. Thanks to PASS for this opportunity

God Bless,

Thomas

24 Hours of PASS: More free training from experts (and me)

2011-09-05T13:58:00.001-07:00

I try not to say I am an expert, I just have some years of experience with some failures that were turned into successes. Experience is a good teacher.

I was an alternate for 24 Hours of PASS (#24HOP twitter hash tag) until Karen Lopez had to cancel because of a flight she was going to be on.

I will be giving a Database Standard Operating Procedure talk as a preview to the 3rd Normal Form: That’s Crazy Talk!!! that I was selected to do at the PASS Summit 2011.

If you have never been to a PASS Summit, I would bet you would leave there more excited about SQL Server than when you arrived. The Summit has some of the most enthusiastic SQL Server professionals I have ever meet.

Most talks are by IT people in the field, not salesman trying to sell you software. You get real world experience. The 24 Hours of PASS will have some of these speakers giving previews of their Summit talks. Some Summit talks are regular sessions like mine(75 minutes), but most are doing either 90 minute Spotlight sessions, 1/2 day sessions or Pre-conference sessions which are Monday and Tuesday of the Summit.

I have been to the PASS Summit 3 of the last 4 years, have gone to pre-conference sessions every year and plan of doing the same this year. I have also watched 24 Hours of PASS the last 2 years when I can at work.

Please join us this Wednesday and Thursday for 24 hours of free training by SQL Server experts and ME!!!

RECAP: Baton Rouge SQLSaturday #64 and TechDay

2011-08-28T14:03:00.001-07:00

This year I decide to help with volunteering and not speak. Since being selected for SQL PASS Summit

I decided not to speak and give others, especially local people an opportunity to start their speaking careers. The first time I spoke was at a local user group then SQLSaturday #28. I did my best to pick a topic (RML Utilities) that I thought would impress people. What I realized over time, that the majority of attendees at SQLSaturdays were new to the technology and it would be better to appeal to the newbies.

Execution Plan Basics and 3rd Normal Form: That’s Crazy Talk!!! are the types of talks I concentrate more on lately. The more advanced attendees (including myself) get to criticize the session about the lack of advanced/intermediate topic, but I have to come to accept these comments to only improve the next talk.

I did get to introduce the event at the keynote SQL side which was a basic description of the event and how to read the schedule, visit sponsors and participate in raffles. Serving Jambalaya and tallying speaker evaluations were some of the volunteering I did. Keeping up with speakers and cancelations/replacements were not fun as 1/2 of the speaker coordinator, but it had to be done.

There was a wonderfully giving group of volunteers that meet periodically to plan this event. Laurie, Sara, Eric & Beth from Antares and William, Mike & Justin from Sparkhound and Mr. Mike Verret (LSU).

Proficient (Mike, Ryan & Val) sponsored as a Platinum level with Microsoft (THANKS!!! Zain and Patrick LeBlanc) and HP. There were many Gold and Silver sponsors

We had 35+ attendees pick volunteer when the registered which gave us enough people to monitor each speaker room to help collect close to 1000 speaker evaluations and help with a book giveaway at each session.

I cannot say enough of the speakers who came from all around the country, Steve Jones and Carlos from Colorado, Peter Myers from SolidQ, Sean & Jen from Dallas, Stuart from Atlanta, Rob Vetter (C# MVP), Jose from Tampa, AvePoint and K2 as Sharepoint/Sponsors and filling in for speaker cancellations. Even the late arriving William Pearson from JawJa.

More volunteers (There were probably 20-30 extras helping us).

Here is the link to more pictures: https://picasaweb.google.com/110051938285440518693/SQLSaturday64?authuser=0&authkey=Gv1sRgCOSS5LS5l_TkXg&feat=directlink#

Until next year, God Bless!!!

Future Reserved Dates

Here is a quick view of dates and locations that have been reserved for future SQLSaturday events. Click here to reserve a date!

Aug 04, 2012
Taylor Hall, Louisiana State University Campus
Baton Rouge
LA

August 18th Data Arch VC Meeting: John Racer on Data Warehouse Architecture

2011-08-16T18:56:00.001-07:00

August 18 Data Arch VC Meeting: John Racer on Data Warehouse Architecture

Subject:
Data Warehouse Architecture

Level:
200 (Intermediate)

Start Time:
Thursday, August 18, 2011 1:00 PM US Central Time (August 18, 2011 6:00 PM GMT)

End Time:
Thursday, August 18, 2011 2:00 PM US Central Time (August 18, 2011 7:00 PM GMT)

Presenter:
John Racer (blog|@SpeedRacer)

Live Meeting Link:
https://www.livemeeting.com/cc/UserGroups/join?id=TQ6GQT&role=attend&pw=5p_%28%3BB3df

Data Warehouse Architecture
Data Warehousing is a large and often misunderstood concept. Marketers, Authors, and IT Departments all have differing spins and opinions on what is involved and just how to go about building one. In this session we will discuss some of the more common architectures and practices. We will cover various aspects of what goes in to Data Warehouse including the systems, modeling, data storage, data integration and data access.

John Racer
Database Administrator, Business Intelligence Developer and Data Warehouse Designer with strong knowledge of telecommunications, care center, compensation and business operations systems. Experienced with implementation and maintenance of SQL Server and Oracle Multi-Terabyte High-Availability environments. Broad experience with project management through SDLC, RAD and Scrum. Driven by the challenge to understand business and processes and apply this knowledge create innovative applications of technology to improve organization and operational efficiency.

John's blog:
http://speeddba.com

Add to your calendar

Business Intelligence Track–SQLSaturday #64 Baton Rouge

2011-07-28T19:12:00.001-07:00

Just in case you did not know, the annual Baton Rouge SQLSaturday #64 and Tech Day will be August 6th in Baton Rouge, LA at the LSU campus. Patrick LeBlanc started it all in 2009 and there is wonder group of Technology Geeks keeping the trend going.

If I were not volunteering for #SqlSat64, this is where my but would be all day, except during lunch eating jambalaya and watching with Steve Jones represent RedGate

http://www.sqlsaturday.com/64/schedule.aspx

Business Intelligence for Managers/Decision Makers (1111) - Beginner

BI Track – 8:30-9:30AM

Business Intelligence is an essential component for any business today and a successful BI implementation has the power to change the way your organization functions. In this non-technical session Carlos Bossy will show you what a BI project looks like and how it is different from other IT projects, how to determine the ROI of a BI project, the makeup of a good BI team, how to define the success of a BI implementation, and how to fix a BI project gone bad. You will also learn how to evaluate your BI & Analytical Strategy and you should leave this session with better knowledge of how to plan, build and deploy effective Business Intelligence solutions.

Carlos Bossy (MCTS SQL Server 2008 BI, CBIP) is a Consultant with 25 years of experience in software and database development. Carlos is an independent consultant and focuses on developing Business Intelligence solutions including data warehouses, predictive analytics, data integration and reporting. He has worked with SQL Server for 10 years and is very enthusiastic about its powerful features. Carlos has developed warehouses and BI solutions for a variety of industries and state agencies.

Handling Advanced Data Warehouse Scenarios in SSIS (1111) - Intermediate

BI Track – 9:45-10:45AM

So you’ve used SSIS to populate a simple star schema data mart, and everybody’s happy. But now you have new requirements that require more advanced data warehouse approaches, like late arriving dimensions, bridge tables, parent child dimensions, and Type 3 or Type 6 slowly changing dimensions (SCD). How do you handle those in a scalable, efficient way in SSIS? This session will present some common patterns for handling these scenarios. You’ll learn when to use each advanced approach and the pros and cons associated with each pattern. You will learn how to implement these patterns in SSIS, and how to tune them for high performance

John Welch is BI Architect with Varigence. Varigence builds tools and frameworks that enable the creation and management of end-to-end business intelligence solutions with unprecedented ease and speed. John has been working with business intelligence and data warehousing technologies since 2001, with a focus on Microsoft products in heterogeneous environments. He is a Microsoft Most Valued Professional (MVP), and a frequent presenter on SQL Server BI topics.

Introduction to Analysis Services 2008 R2 Cubes (1111) - Intermediate

BI Track – 11:00-12:00PM

Analysis Service’s OLAP component allows organizations to implement a cube that is designed for rapid ad hoc information retrieval of their data. The cube, as a single version of the truth, can be enriched to encapsulate business rules and calculations, and advanced Business Intelligence features including KPIs and actions. For organizations that have a Standard or Enterprise SQL Server license, Analysis Services is a possible zero-cost opportunity for your organization today. In the session learn how to exploit the capabilities and features, and the basics of best practice design. Be prepared for numerous compelling demonstrations and to leave the session energized by the potential

Peter Myers has 14 years of solid experience working in OLTP database design and Business Intelligence with SQL Server and SharePoint. In April 2010, Peter was re-awarded Microsoft MVP (Most Valuable Professional) for the fourth time. Peter enjoys sharing his enthusiasm for Microsoft technologies by presenting at SQL Server user group meetings and technical events, including TechEd in North America, Europe and Australia

Designing and Optimizing SSAS Hierarchies (1111) - Advanced

BI Track – 1:30-2:30PM

In this session Jose will do an overview of the different types of hierarchies and how to design user hierarchies in SSAS by defining attribute relationships and key columns the right way. Attendees will learn how to implement natural & unnatural hierarchies, balanced and ragged hierarchies, and Parent-Child hierarchies. Jose will show how to optimize SSAS hierarchies by specifying attribute and dimension types, member uniqueness, and aggregations. Jose will also do quick intro on querying hierarchies with MDX

Jose Chinchilla is a Microsoft Certified Database Administrator and Business Intelligence Developer working as a Sr. BI Consultant and trainer for Pragmatic Works. Jose has12+ years of experience in IT and has focused his career in OLTP and OLAP database design, development and administration and specializes in ETL/ELT using SSIS, Data Warehousing and Multidimensional Analysis using SQL Server 2008 BI tools. He is also the current president of the Tampa Bay Business Intelligence User Group

Visualizing SQL Saturday (1111) - Intermediate

BI Track – 2:45-3:45PM

This session will cover the best practices of dashboard design and data analytics as we explore and create data visualizations from the history of SQL Saturday. This session will focus on analyzing trends over time, categorical trending and dashboard design

Tim Costello is an MCITP, MCTS specializing in etl and data analytics for Interworks Inc. Tim is an international speaker with 25 presentations in the US and Canada since January of 2010. Tim is active in the SQL community and leads a data visualization focused user group in the Dallas area. Tim Started working with data in Access 95 and has worked in every version of SQL from SQL 7 forward

An Overview of PowerPivot (1111) - Beginner

BI Track – 4:00-5:00PM

In this presentation, Microsoft BI Architect and SQL Server MVP Bill Pearson explores “self-service BI” as a concept. We then provide a walkthrough of the primary features of PowerPivot, focusing on how we can exploit its capabilities to offer the benefits of BI to decision makers and analysts throughout our organizations. “There’s no service like self-service …”

Bill Pearson created Island Technologies Inc. in 1997, and has developed a large and diverse customer base since. Bill's background as a CPA, Internal Auditor, Management Accountant and SQL Server MVP (BI) enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, and focuses his practice upon the integrated Microsoft business intelligence solution

SQLSaturday #64 Baton Rouge Edition

2011-07-16T18:04:00.001-07:00

AAAAYYYEEE!!! as they say down on the Bayou.

Time to get some free SQL Server, .Net and Sharepoint training in Baton Rouge. This is annual Tech Day in Baton Rouge for the third year. MVPs from all over the south and beyond are gathering to train people through 8 Tracks with 6 sessions each, that is 48 hours of free training plus a Window 7 Phone garage (not sure about this one).

Please register her: http://sqlsaturday.com/64/register.aspx

See the session lineup here: http://sqlsaturday.com/64/schedule.aspx

There are too many presenters to mention, so will not name them because I do not want to miss anyone, but this year the will be 46 different speakers and I will not be one of them. I am concentrating on helping the volunteers put on the show.

Also, I have been accepted as a speaker (and alternate) at the PASS Summit in Seattle October 10-15 2011. Still have to get approval from my new job to attend.

Oh yeah, I forgot to mention that I have a new Job. I now work at Blue Cross-Blue Shield of Louisiana in Baton Rouge in the Enterprise Architecture team, probably to change to Enterprise Architect Data Performance title. I am in New York right now training on Informatica which is an enterprise ETL tool that sorry to say, blows SSIS out of the water.

Maybe Denali will catch up with Informatica, but right now it does not look close.

Well, good night and back to work

God Bless,

Thomas

July 21 Data Arch VC Meeting: Gaurav Aggarwal on Microsoft – Readying on “Hadoop”

2011-07-12T18:11:00.001-07:00

July 21 Data Arch VC Meeting: Gaurav Aggarwal on Microsoft – Readying on “Hadoop”

Subject:
Microsoft – Readying on “Hadoop”

Start Time:
Thursday, July 21, 2011 9:00 PM US Eastern Time (July 22, 2011 2:00 AM GMT)

End Time:
Thursday, July 21, 2011 10:00 PM US Eastern Time ( July 22, 2011 3:00 AM GMT)

Presenter:
Gaurav Aggarwal (blog)

Live Meeting Link:
https://www.livemeeting.com/cc/UserGroups/join?id=R9J5WW&role=attend&pw=Zf%2B-Q%6098k

Microsoft – Readying on “Hadoop”
This session will provide overview of different options on Microsoft platform for Hadoop. Session will cover pro and cons of different options and architecture level coverage of HPC, PDW and Dyrad.

Gaurav Aggarwal
Gaurav Aggarwal is a Senior Architect with 16+ years of experience. He has been working with Microsoft for the past 7+ years. He helps senior management in aligning their IT capabilities to innovate and achieve momentum for their business goals. He has been architecting and delivering solutions using various technologies for nearly 17 years. He has delivered solutions in the areas of Investment Banking, Insurance, HR, Manufacturing, Retail Banking and ITES. These solutions were based on a wide range of technologies including Microsoft and Java platform based solutions.

He is a Microsoft Certified Master (SQL Server), Microsoft Certified Architect (SQL Server), and Microsoft Certified Architect (Solutions), IASA Certified Architect Professional and a practicing Six Sigma and PMP (Project Management Professional) professional.

Gaurav’s blog:
http://blogs.msdn.com/b/gauravagg/

Add to your calendar

PASS Data Architect presents Karen Lopez–You’ve just inherited a Data Model: Now What?

2011-06-09T18:06:00.001-07:00

Please come join the PASS Virtual Chapter for Data Architect on Thursday June 16th at 2PM Eastern for InfoAdvisor's principal consultant Karen Lopez sharing her experience with inheriting a Data Model.

This is the third month in a row the rebirth of this virtual chapter has hosted. The schedule is booked thru December, Robert, Lorra and I are excited about the presentations and volunteers that have helped this chapter teach SQL People about data architect.

The foundation of data is very important and some loss site of the basics while enjoying all the new stuff in SQL Server.

Subject:
You've Just Inherited a Data Model: Now What?

Start Time:
Thursday, June 16, 2011 2:00 PM US Eastern Time (7:00 PM GMT)

Presenter:
Karen López (blog|@datachick)

Live Meeting Link:
https://www.livemeeting.com/cc/UserGroups/join?id=T4ZKMM&role=attend&pw=Q%3A%26%24h%28-9m

You've Just Inherited a Data Model: Now What?
The good news is that someone else has done the hard work of architecting a data model and you just have to take on minor maintenance…or is that the bad news? Or have you been tasked with implementing a pattern or industry standard data model? Perhaps a team member has sent the world's best resignation letter and won't be helping you with the model. Learn the 5 steps you MUST take before working with a new data model.

Attendees will also receive a detailed checklist for the 5 steps.

Karen López
Karen López is a principal consultant at InfoAdvisors, Inc., a Toronto-based consulting firm. Karen is a frequent speaker at DAMA, SQLSaturdays and PASS conferences, including 24 Hours of PASS, the PASS Summit and PASS SQLRally. She has 20+ years of experience in project and data management on large, multi-project programs. Karen specializes in the practical application of data management principles.

Karen is also the ListMistress and moderator of the InfoAdvisors Discussion Groups at www.infoadvisors.com.

Karen's blog: http://www.infoadvisors.com/Home/tabid/36/BlogID/1/Default.aspx

Twitter:@datachick

Add to your calendar

More #SqlRally pictures and observations

2011-05-28T08:14:00.001-07:00

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

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

Geaux Tigers!!!

SQLLunch and ex-MVP Patrick Leblanc

Picture taker Tim

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.

Up next #SqlSat77 in Pensacola.

God Bless,

Thomas

SQLRally, what a Ride

2011-05-13T18:49:00.001-07:00

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.

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.

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

My PASS Summit 2011 Submissions

2011-05-05T17:06:00.001-07:00

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

SQLSaturday #77 Pensacola, sp_WhoIsActive & EFM

2011-04-16T10:43:00.001-07:00

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.

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.

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.

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

God Bless

Data Architecture Chapter restarted

2011-03-31T18:02:00.001-07:00

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 your calendar

SQLRally

2011-03-19T15:09:00.001-07:00

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

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

Got an Issue, get a Tissue…

2011-02-17T11:09:00.001-08:00

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.

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.

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

WHAT?!?! TheSmilingDBA speaking at #SQLRally

2011-01-25T07:55:00.000-08:00

WOW!!! That is all I have been thinking for the last 12-14 hours. My Session (3rd Normal Form: That's Crazy Talk!!! has been selected as the Design catagory for SQLRally )SQLRally.com. Thanks for the votes, and Thank You Lord!!!

Please go vote for the next round of sessions. http://www.zoomerang.com/Survey/WEB22BQZ99LQMG/ I have a second session (Execution Plan Basics) in the DBA Admin secion, but now that I have one session, you can vote for someone else's session. Do not want to be a hog.

God Bless,
Thomas

SQLRally voting, SQL Saturday #57 Houston, etc.

2011-01-18T18:23:00.001-08:00

I bet I cannot copy these copyrighted pictures, but here goes.

Please go to SQLRally to vote for my session (3rd Normal Form: That’s Crazy Talk) in Orlando. This would be a great opportunity for me to expand the user base with my favorite session. This session was created when a SQL Server Central QOD (Question of the Day) had a large percentage of people not correctly answering a question about table design. Database design is such an important part of the Development Process (SDLC) that the new developers and DBAs in the SQL Community need to be taught these simple concepts.

Paul White { Blog | Twitter } has a great article today on Database Design on SQL Server Central. Paul Nielsen and Louis Davidson are some of the better known Database Design SQL Server people.

There are many other great sessions you can vote for, so please be apart of the community. The Rally is May 11-13, 2011 in Orlando. There are some pre-rally all day sessions. A great presenter is Grant Fritchey doing Execution Plans and Tuning. I hope I can make the session because it is sure to be informative and entertaining.

SQLSaturday #57 in Houston is less than 2 weeks away. If you can get to Houston on January 29th, do not miss this FREE day of SQL Server training. Click here to see a session list of the 7 tacks with 6 sessions per track. #SqlSat has gone viral. Yes, I said viral…and did I say FREE!!!

These FREE events are an unbelievable source of FREE SQL Server education, networking and chances for new presenters to get there feet wet.

The reading and work in the BI department has keep me so busy, I have not had time to Blog much…more reading than blogging. I do have 2 drafts saved about when not to use functions in WHERE clauses and when not to use the Missing Index suggestion on a Query plan.

Hopefully, I can get to these in February.

God Bless,
Thomas

CXPACKET – that mysterious wait type

2010-12-23T05:48:00.001-08:00

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

2010-11-03T19:00:00.001-07:00

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

2010-10-07T18:53:00.001-07:00

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

2010-09-11T07:05:00.001-07:00

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

2010-09-06T08:12:00.001-07:00

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

2010-08-13T06:40:00.000-07:00

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

2010-07-30T14:33:00.000-07:00

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

Database Lookup Tables

2010-07-16T12:42:00.000-07:00

Over the years, I have noticed a trend in building databases. A developer will get on a certain path when it comes to what I define as Lookup tables. There are many names or labels the community has placed on this Type/Code/Category of tables, but I want to give 3 examples of the types we ran into in the latest creation of a database.

The below list and table structure was standardized once so everybody (developer, DBA, BI, etc.) would be in agreement. After the Create Table below, please read for an explanation of the columns and types.

1. Drop-down list - this table is really just a pick list for a transaction or category type.
    Example
        BillingClass: B - Billable, NB - NonBillable, etc.
        RequstType: P - Phone, L - Letter, etc.

2. Developer Control - developers usually like to have some tables that cannot be change by end user because it would change the flow of the application code. This group of developers wanted to enumerate the IDs in .Net code. Too bad we (DBAs) can use enumeration in a Query window 
    Example
        WorkFlow - I - Initial, U - Updated, P - Processed & C - Close

3. End User Control - end users want to be able to add to the list for use in the application, but unlike the drop-down list, there are other properties to the Item.
    Example
        RevenueCode - 435 - Inventory stream, 321 - visit cost, etc.

The first 2 types have a consistent structure:

CREATE TABLE [Billing].[lkClass](
    [Code] [varchar](25) NOT NULL,
    [ID] [int] NOT NULL,
    [Name] [varchar](100) NOT NULL,
    [Description] [varchar](1000) NULL,
    [DisplayOrder] [int] NOT NULL,
    [RowState] [int] NOT NULL,
    [TimeStamp] [timestamp] NULL,
CONSTRAINT [PK_lkClass] PRIMARY KEY CLUSTERED
    ([Code] ASC)
      WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
        IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [uc_lkClass_ID] UNIQUE NONCLUSTERED
    ( [ID] ASC )
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
       IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
       ALLOW_PAGE_LOCKS = ON) ON [DB_INDEX],
CONSTRAINT [uc_lkClass_Name] UNIQUE NONCLUSTERED
    ( [Name] ASC )
   WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
        IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON) ON [DB_INDEX]
) ON [PRIMARY]

The code is 25 characters because we voted to have a varchar () that would satisfy all possibilities. Why still use Code when there is a unique ID field? End users still like to be able to type known code/type values into an interface for fast data entry. Even with the intelliSence possibilities, I personally do not see codes going away with end users. Also, reports need abbreviated versions of lookup to help compact display. This is the foreign key to a related table, not the ID.

The ID is used for .Net objects that want to reference a list with an integer, rather than using a Search/Find method to locate the entry in a list/collection. The Name is really the long description, while the Description is what you might call a memo field (notice MS Access reference).

DisplayOrder is self-explanatory and RowState has values like 0-active, 1-inactive, and other numbers reserved for future use. This allows the row to be ‘deleted’ – inactive, but not removed from the table. Timestamp is really the RowVersion data type from SQL Server data types. We have not changed this to RowVersion, yet.

Even though State Abbreviation is known as only 2 characters and you need only 2 columns in the table, we still use this structure because the Object Oriented interface/object code using the same structure is global throughout the project. The end user control type table will include the additional columns on the same table. We do not create a one-to-one relationship to another table just for more attibutes, in most cases.

Primary key is in the Primary file group because it is the clustered index, but the other Unique Constraints are in the INDEX File Group in order to place the Clustered Index (table) on a different file group than the Indexes. The Unique constraints are used to make sure duplicate IDs and/or Names are never possible even though the developers promise that will prevent that in the Code.

Database Standards Part VI: code and design

2010-07-08T13:33:00.000-07:00

Database Standards Part VI: code and design

This is Part 6 of 6 blogs about database standards. This last post is about coding standards that should be followed.

Design
A data dictionary and diagram should be maintained. The diagram can be a bird's eye view. The dictionary contains tables with description and columns with data type, size, default, constraints and descriptions. The foriegn keys would be listed along with primary keys.

Coding

- Avoid using SELECT *.
- Use CTEs instead of Cursors
- Dynamic SQL is difficult to read, confuses security and not very maintainable.
- NOLOCK and READUNCOMITTED can only be used when absolutely necessary.
- INNER JOIN not JOIN
- prefix objects with schema, even dbo.
- Use TRY/CATCH for error trapping
- only use parentheses for AND\OR expressions
- spaces between expressions and variables
   Type = 'S' NOT type='S'

- BEGIN/END should only be used with there is more than one execution line after condition

Formatting
    Bad:
Select Sample.*, Analysis.*,

Sample.DueDate, Sample.EnteredBy
FROM Sample JOIN
Analysis on Sample.SampleID = Analysis.SampleID

   Better:
SELECT s.SampleID, s.Description AS SampDesc,
    a.AnlCode AS AnalysisCode
FROM Sample s
      INNER JOIN Analysis a
        ON a.SampleID = s.SampleID
WHERE s.SampleID = @SampleID

Next set of Blogs will be a diary of the transition from production senior DBA to BI Data Integration Lead Developer.

Thomas LeBlanc
Outoging Production Senior DBA

Database Standards Part V: Triggers and User-defined Functions

2010-06-17T13:40:00.000-07:00

Database Standards Part V: Triggers and User-defined Functions

This is Part 5 of 6 blogs about database standards. Again, though we do not enforce these with an iron fist, they are advised for clarity and consistency. The important point, in my opinion, is to establish a set of standards and stick to them.

Triggers
Naming convention for triggers are 'it' for insert, 'ut' for update and dt for delete (itPatient). Trigger can slow CRUD statements so we warn developers to be careful when creating triggers. It also can 'hide' logic from development. Over and over, we get confused people trying to find how data gets added to a table because of a trigger on another table. Verify during a review that the trigger can handle multi-row updates when joining to the inserted or deleted recordsets. Most triggers I see are related to auditing Ins/Upd/Del statements.

User-Defined Functions
User-defined functions are prefixed with udf. They can be helpful with string manipulation commonly used in stored procedures or SQL statements. UDFs do add processing time to the execution and can cause scans when used on fields in a WHERE clause. Looking at an execution plan might exclude the code from the UDF. I see table-valued UDFs used like DMFs from dynamic management objects from Microsoft, mostly on small data sets.

Also, when profiling, you get a statement for each call to the udf for each row in the SELECT statement if used in a query, so you might want to exclude the data in the profiled output.

Repeating again, and again…

Though my opinion is not the same as some of these standards, I do believe standards should be discussed and established, then followed. If a deviation is needed, a group discussion should be voted on to change.

Next week I will talk about coding and deisgn standards.

Thomas LeBlanc

2010-06-04T06:55:00.000-07:00

Database Standards Part IV: Stored Procedures

This is Part 4 of 6 blogs about database standards. Again, though we do not enforce these with an iron fist, they are advised for clarity and consistency. The important point, in my opinion, is to establish a set of standards and stick to them.

Stored Procedure
Stored procedures should be required for all create, read, update and delete (CRUD) DML (Data Manipulation Language) statements. Using stored procedures benefits security, consistence, performance and manageability on the DBA side of applications.

Security can be managed by giving GRANT EXECUTE to logins requiring access to DML statements, or in 2005 and greater EXECUTE can be granted to a login on the entire database. Performance gains are realized in compiled query plans and SQL Server’s ability to re-compile only the statement in the SP that needs a new/better plan.

Naming conventions:
Read - uspPatientGet or GetPatient
Delete - uspPatientDel or DelPatient
Report – rptSecurityAudit
Process – prcProcessPatient

The use of Schemas can better organize the SPs. Do not use prefixes sp, sp_ or xp_. These are used by the SQL Server system or custom extended procedures.

Precompiled SQL can use less memory in the Procedure Cache and require less look up in Proc Cache for existing plans.

Remember not to repeat the schema name in the SP – Patient.GetDetails instead of Patient.GetPatientDetails.

SET ANSI_NULLS ON can be a requirement because it will be depreciated in future SQL Server versions.

SET NOCOUNT ON can be prevent round trips to the client - http://msdn.microsoft.com/en-us/library/ms189837.aspx

Using a Source Control application can reduce comments in a SP. The versioning in TFS or Source Safe gives the developer the ability to associate comments with the check in. Our SPs begin with the following structure

IF NOT EXISTS (SELECT * FROM Sys.Objects
                              WHERE Object_Id = OBJECT_ID(N’[Ptient].[rptPatientReleased] ’)
                                 AND type in (N’P’, N’PC’))
BEGIN
    CREATE PROCEDURE [Ptient].[rptPatientReleased]
      AS RETURN
    GO
END

ALTER PROCEDURE [Aptient].[rptPatientReleased] AS
    SELECT TOP 10 * FROM NewProcedureView
GO

Always end the SP in GO when creating or altering. The Alter is preferred because it retains permissions on SP. If DROP/CREATE is used, remember to apply the permissions. Return should not be used to return data to the calling application. Return should be reserved for returning the status of the SP. Use INPUT and OUTPUT parameters in the SP.

Repeating again, and again…
Though my opinion is not the same as some of these standards, I do believe standards should be discussed and established, then followed. IF a deviation is needed, a group discussion should be voted on to change.

Next week I will talk about Triggers and User-Defined Functions.

Thomas LeBlanc

Database Standards Part III: Indexes, Constraints & Primary/Foreign Keys

2010-05-25T14:27:00.000-07:00

This is Part 3 of 6 blogs about database standards we use at my current employer. Repeating myself, though we do not enforce these with an iron fist, they are advised for clarity and consistency. The important point, in my opinion, is to establish a set of standards and stick to them.

Indexes
The naming of indexes is not unanimous. The standard is for a prefix of idx plus the table name then the field names with an underscore between the table and field name – idxPatient_LocationCode. This will create some very long names. I prefer abbreviation of the table and fields names, or like another DBA a reference name of where the index will be used.

A reminder to developers and contractors is that a primary key clustered index is an index. We have seen duplicate indexes created because of this. I kid not. Also, clustered indexes are included in non-clustered indexes, so a multi-column clustered index has to be seriously reviewed before implemented.

Understanding covering indexes and how they are used is important to reduce the columns used in some indexes. Fields that are regular updated probably should not be in a clustered index.

A reindex maintenance plan needs to be implemented. I will try to cover the custom one we use in a later blog or webcast.

Constraints
A unique constraint has its own index, so do not create an index on a unique constraint.

Use prefix chk (chkDigits) for check constraint and uc (ucPatientNumber) for unique constraint naming.

Default definitions should be in line with the create table statement and default objects have been deprecated, so do not use. Do not use data type conversion in constraint definitions.

Primary Key and Foreign keys
All tables have to have a primary key. When an identity field is used, a unique constraint must be created from a separate field(s) to uniquely identify a row. Every table needs to be able to be queried based on the contents, not an identity field. The normalization talks I have been privileged to give, explains more about this with the 1st normal form portion.

All foreign keys have to be indexes and should be the primary or unique constraint key from the parent table.

Naming convention is prefixed with pk for primary key (pkPatientNumber). Foreign Key naming is prefix fk then parent table name underscore column(s) underscore child table name and last underscore column(s) – fkPatient_ID_Admit_PatientID

Repeating Again, and again…
Though my opinion is not the same as some of these standards (I say you can abbrev. anything), I do believe standards should be discussed and established, then followed. IF a deviation is needed, a group discussion should be voted on to change.

Next week I will talk about Stored Procedures.

Thomas LeBlanc

Database Standards Part II: Schemas, Tables & View and Columns

2010-05-13T14:04:00.000-07:00

This is Part 2 of 6 blogs about database standards we use at my current employer. Repeating myself, though we do not enforce these with an iron fist, they are advised for clarity and consistency. The important point, in my opinion, is establish a set of standards and stick to them.

Schema

The seperation of schmea name and logins was a great addition in SQL Server 2005. Schema names should be used in place of dbo. This makes viewing in SSMS great with filters. It also categorizes the objects so a new DBA or developer can get a good view of the organization in a database. We suggest a Common schema for objects that do not fit in a group, then names that are specific to functionality - Payroll, HR, Billing, etc.

Tables
Tables names should reflect its functionality and they cannot start with a number, contain spaces, underscores or be reserved words. ANSI_NULL (OFF is deprecated in a future version) should be SET ON. Pascal Casing should be used like ClientBill and singular like Admit not Admits.

There are 3 different kinds of Lookup tables we see - developer populated (workflow), small lists (state) and known list that are updattable (RevenueCode). Small lookup tables can use a character primary key. If the developer insists on ID (identity), then a character code/description column created with a unique contraint must be included. The code should be used in the transaction table. (I will clarify this is a seperate blog). Lookup tables use camel case - lkState.

The transaction table(s) should use a ID (indentity or sequential int) as the primary, but should have a unique contraint for uniquely identifying a row. Third Key Normal Form and higher is required. The ID field will be the foreign key in the child table, and named ID (PatientID in an Episode table).

Views
Camel casing should be used in naming views - vwPatientEpisode. Future thinking should be used if the view will eventually become an indexed view, then schema binging and ansi settings need to be looked at.

Columns
Column names should describe the data placed in them, using Pascal Casing - LastName and not start with a number/special character, no spaces, dashes or underscores. Beware of reserve words or key words as object names. Do not repeat table name - use LastName not PatientLastName unless a foreign key to another table - PatientID. No data type in the name - intPatientCount, but Date is acceptable in a date name - AdmitDate.

Be aware of new features in 2008 like a seperate DATE and TIME data types. Before, you had to use midnight as the time in a date column type in order to not specify a time.

Repeating
Though my opinion is not the same as some of these standards (I say you can abbrev. anything), I do believe standards should be discussed and established, then followed. IF a deviation is needed, a group discussion should be voted on to change.

Next week I will talk about Indexes, constraints and Primary/Foreigh Keys.

Thomas LeBlanc

Database Standards Part I: Defs, Abbreviation & Data Types

2010-05-06T13:40:00.000-07:00

This is Part 1 or 6 blogs about database standards we use at my current employer. Though we do not enforce these with an iron fist, they are advised for clarity and consistency. The important point, in my opinion, is establish a set of standards and stick to them.

Definitions

We list some definitions are the beginning of our document. Included are Camel casing (vwVisitOrders) and Pascal casing (MedicalData)

Abbreviations

Though most developers like to spell everything out because they can use IntelliSense, abbreviations are ok for some common and industry standard abbreviations that can be used. Examples are Id (identification), SSN, DOB or Rpt (Report).

Data Types

Now, the standards for these are good because of the consistency factor, comparisons (WHERE clause) and SQL Server features. Some features of SQL Server get deprecated, and others are improvements. A good example is using varchar (max) instead of text data type.
   Yes/No: Use bit data type. We also suggest naming the field like a question: Isdeleted
   Flag/Status: use tinyint (1-Active, 0-Inactive, other numbers can be used for future status)
   Use Unicode only if necessary: nvarchar(100)
   Integers: Be sure to properly size up front, if not sure use int or bigint
   Fixed field character lengths: State - char(2)
   Variable character length: LastName varchar(25)
   Money/Decimal – use over float/real. Price decimal(10,2)
   Null/Not Null – won’t even discuss
   Max – use with varchar, nvarchar, vbinary instead of text and binary.

Though my opinion is not the same as some of these standards (I say you can abbrev. anything), I do believe standards should be discussed and established, then followed. It a deviation is needed, a group discussion should be voted on to change.

God Bless,
Thomas LeBlanc

SQLSaturday New York: Smiling all day

2010-04-29T14:28:00.000-07:00

SQLSaturday is catching on all over the place. It is amazing how many people are thirsting for SQL Server knowledge. One of the best experiences I have when going to a session, is the validation that what I am doing at work is on the right track.

First, I would like to thank the sponsors for funding the event. Without them the event is not possible.

Confio (Ignite) even invited the speakers and volunteer to a Friday evening welcoming. It was just what was needed the night before the event. Thank you Matt Larson and David Waugh.

Other sponsors included Quest, Idera, Expressor, Redgate, Pearl, XLeratorDB, PASS, Pragmatic Works, TwentySix, SetFocus, Agile Technologies, Microsoft, CozyRoc and Apress.

Biggest thank you goes out to the NJ SQL Server User Group and volunteers, who did a top notch job. Thanks Alex for helping the speakers and SQLDiva for leading the group.

Breakfast was plentiful, coffee, orange juice, milk, donuts, bagels, etc.

The first session I attended with Andy Leonard SSIS Design Patterns. Andy did a great job with 2-3 patterns involving variables and events in SSIS.

Next was Matt Larson(Confio), showing how to use sysprocesses and DMV/DMFs to get performance information from SQL Server. A gentleman from the audience encourage all in attendance to install the trial version on Ignite and we would be amazed with the results from this tool. I am trying to schedule a day or 2 for a trial run.

Lunch was in the speakers room chatting with other speakers and volunteers. Next to me was Thomas LaRock, Andy Leonard, Grant Fitchey, Slava Kokaev, Adam Jorgensen and others. Lunch was box style and worked great.

Grant was up next with a great presentation on More Unnecessary Tuning. Basically, some do's and do not's up front when designing data access.

Andy Leonard continued in the afternoon with Incremental Loads in SSIS, but I had to leave before the end to prepare for my session. The Hash compare for changes interested me.

To close the day, I presented 3rd Key Normal Form: That's Crazy Talk!!! The room was a conference room with a table in the middle and chairs all arounnd with somewhere betwee 20-30 attendees(1 or 2 had to sit on the floor). The session was directed at good and bad DB designs, but I probably only got through the first 10-12 slides before the discussion went to Visio diagrams and SSMS scripting.

The participation from the attendees was great, I just had to keep the focus on the topic - kinda of like leading a meeting at work. It turned out better than I imagined and had 5-6 attendees stay a little while afterwards to thank me and offer some great advice and comments.

One open question was about Attributes on an Entity that vary based on a product type. I have never used XML as a data type, but this seemed to be a good case. Another peson suggested looking into EAVs design. I displayed a design in Visio that got a couple of people saying, 'That is not normal form.' OUCH!!!

It seems like a good session topic for 4th and 5th key normal form, and some industry standards or examples.

Plenty of prizes were given away by the sponsors and the conference.

My next Blog post will be a series on the DB Standards document from my current employer. I could not place this with my session because it belongs to Amedisys.

God Bless,
Thomas

Maintenance Weekend...

2010-04-16T14:20:00.000-07:00

This Saturday is the third Saturday of the month, which means I will be working. We have a shutdown were the networking, DBA and security teams can update the systems while it is offline to end users.

These functions used to have to be done on unscheduled evenings when ever possible, but now it has been on Saturday for over a year. The time has gone from 6-10 hours to 2-3 if no problems occur. Also, because of failover systems, the work has been lighter.

LSU plays Alabama in baseball and I will be able to go to Sat and Sun. They beat Tulane on Wednesday night 10-4. Always enjoyable to see use take down the greenies.

God Bless,
ThomBeaux

Passed 70-431, now MCITP DB 2005

2010-04-08T13:28:00.000-07:00

I assumed by reading the title of 70-447, that all I needed to upgrade MCDBA to MCITP was to take this test. Wrong!!!

You need to pass 70-431 and become a MCT first, then take 70-447. Well, I did it in reverse and now can say I am a MCITP. Next, 2008 certification.

The test was 35 multiple choice questions, which did not seemed to hard. What I did not realize, was an interactive 12 questions was coming down the pipe.

You actually would be given a scenario, then have to use a stripped down verison of SSMS (Management Studio) to solve the problem. I have to say, I was not prepared for this, but still finished and Passed with 880. Not bad.

Certifications might not be what they were years ago, but the experience in my opinion is good. There is alot of studying required, plus it questioned some of the pratices we DBAs do at our site.

Some questions about CLRs, security, Serice Broker were areas we do not use and I had to learn from reading. Others like database mirror, replication, log shipping, network protocols were areas we use and were easy.

God Bless,
Thomas

SQLSaturday Baton Rouge

2010-04-01T13:49:00.000-07:00

Great lunch with the planning group. Looks like we will have 9 total tracks, 5 SQL Server and 4 development.

The submitted sessions have us about 75% full with addtional sessions and alternates available.

I spoke last year, but was not involved with the planning. Alot of good people helping.

This week at work required some DBA reviews of developers code. I am also reading an 2008 Analysis Service book which has been great. Microsoft gave us a copy and I am on chapter 12, with 4-5 to go.

It seems the tool is great, but the design has to follow some best pratices in order to work well as designed. The are many properties to change in a dimension or cube to get the output just right. Another case of if you do not start using it, you will forget where to adjust output.

God Bless,
ThomBeaux

New York, New York

2010-03-18T13:28:00.000-07:00

Very excited about going to New York April 24th and speaking at SQLSaturday. My girlfirend and I will go down on Thursday and watch some Broadway shows and visit a museum or 2.

The talk is '3rd Key Normal Form: That's crazy talk!!!' - a fellow employee has this on his whiteboard in his cube. In all my time as a developer and DBA, there is always some denormalization, or really no fully normalization to begin with.

The session is going to show some of the experiences I have encountered over the years. One is in the Laboratory Information System Management arena. My first job out of college was to help normalize data from a file system (written in Profressional Basic - DOS 3.3). We used FoxPro DOS to create a report writer for our clients. Many years later MS Access came along, and so did Visual Basic. I had some great ideas that the developers (Chemistists by training) did not want to implement.

The second example is a FEMA program for funding distribution and Performance Indicator tracking. This was a great example of compound primary keys geeting larger and larger. Two lessons here: Not every table is part of a hierachy and Parent-child can benefit from ID (identity field) primary keys.

If I have time, I think I want to get the audience into a 4-6th normal form discussion. Paul Nielson will be there, and it would be great to visit with him about database normalization.

God Bless,
Thomas

Winton Marsalis and Fragmentation

2010-03-11T13:46:00.000-08:00

With replication and mirroring on our large databases, there is a limit on the amount of reindexing that can be done each evening. We have a job that loops through a couple of tables to pick what to attempt to reindex/reorg on a nightly basis. Recently, it has been updated to a better structure of lookup tables and 3 levels of possible work:

1. Rebuild if AvgFrag > 60 or AvgPageSpaceUsed <> 30 or AvgPageSpaceUsed < 65
ELSE
3. Update Stats

I added a History table to record State/End Date and Before/After AvgFrag & SpaceUsed

We hope after a couple of months we can get some useful statistics to change FillFactor on some indexes.

Tuesday night I brought my girlfriend to see Winton Marsalis and the Jazz Lincoln Center ensemble performing in Baton Rouge. The set started with some old and new swing, then finished with 7 works dedicated to 7 different artists. The composer explained how the song was written with the artist and his/her works of art in mind.

http://www.wyntonmarsalis.org/2009/12/23/marsalis-tour-march-2010/

It gave a unique view in how any type of work you do can be art...

God Bless and keep smiling,
Thomas :)

Updating large tables that are Replicated and Mirrored

2010-03-05T06:33:00.000-08:00

I am working on an article for submission to explain the steps we use to update a Transactional Replicated table to a Data Warehouse where the database is also mirrored to an off-site Disaster recovery site.

The mirrored site connection and transfer rate ranges from 500k/sec to 2000k/sec and the added columns to a large (20+million rows) produce alot of TLog to transfer to mirrored site. The replicated transaction is delayed by the mirror.

In general, here are the steps:
1. Bring OLTP and OLAP systems down. This is already done because of release.
2. Make sure all transactions have been committed to subscribers. Use Replication Monitor.
3. Generate scripts for DROP and CREATE Publication and Subscription(s)
4. DROP Publication and subscription(s)
5. ALTER TABLE or any other scriptions on all databases, publication and subscribers
6. Create Publication without running any add subscriptions parts of the scripts
7. Use system SP - sp_scriptpublicationcustomprocs to generate replication stored procedures.
8. Execute SPs on Subscriber(s)
9. Run Create subscription script with @sync_type='none' not 'automatic'
10. Use Replication Validation from Replication Monitor to see if everything is OK

The only issue has been if the mirror still has not finished the transaction. Sometimes we have to wait 1-2 hours for this to finish.

There is a new trace flag -T1448, that is suppose to help with this, but the test we have tried have not shown this to be so.

God Bless,
Thomas

BR Symphony w/ Chris Botti

2010-02-02T07:59:00.001-08:00

Chris Botti's second visit to the Baton Rouge Symphony was another great performance. It is wonderful to see him share the stage with other musicians and the symphony. The interaction with the audience was pleasant.

This week tasks include diagramming a BI project's database and discovery of data for the tables. Our reindex routine on the large production system will be modified to store historical data and use both fragmentation for reindexing.last week

I attended a PeopleSoft data management and upgrade 5-day class. The activity work done online line was helped with th lectors.

God Bless,
Thomas :)

Start blogging with a smile

2010-01-22T11:33:00.000-08:00

The creation of an upbeat blog on SQL Server DBA work is on its way. AMEN!!!

Thank you Lord for this opportunity

God Bless,
The Smiling DBA