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
No comments:
Post a Comment