Thursday, December 18, 2014

PASS – Connect, Share and Learn

The Professional Association of SQL Server (PASS) has a slogan for what they do – CONNECT, SHARE and LEARN. So, what does this mean to me and the SQL Server professionals I have meet in this community (#SQLFamily)?



First, I connected. Where did that start? SQLServerCentral.Com (SSC). I started reading articles on this site over a decade ago, and made it the main SQL Server resource for me beginning a DBA career. Back then a DBA was someone who either did one of 3 task types (or any combination).

  • DBA Administrator – worked to keep a system up and running including Windows Admin(Clustering), SAN usage, Security and Permissions (ActiveDirectory) and Performance Tuning (Monitor System resources).
  • DBA Developer – wrote T-SQL for stored procedures, triggers and jobs as well as create databases, tables and indexes usually because you were also a developer of software
  • DBA Business Intelligence – ETL with DTS (now SSIS), cubes in SSAS (multidimensional) and reports in SSRS (it was Crystal Report writer for me because SSRS was not released and packaged with Visual Basic) in addition to creating databases, tables and indexes.

Reading articles helped me develop a talent in the administrator position I was in along with getting a MCDBA in SQL Server 2000 (now I have MCITP in 2005 & 2008). The CONNECT part came when I ask my boss if I could go to the PASS Summit in Denver (2007). He had the funds in our department’s budget and included himself in the trip. There, I watched sessions based on the titles and abstracts. I did not know many speaker names except those I saw write articles on SSC. Those main ones I recognized were the ones who answered lots of questions posted on SSC forums as well as Microsoft message boards.

The next year, I was able to attend the Summit in Seattle and this is where I really started to CONNECT. Before that year’s Summit, I watched Patrick LeBlanc start the Baton Rouge SQL Server User Group. Patrick was able to use the resources from PASS to get it started and the networking with local IT professionals started this CONNECT for me. The group was created after the .Net User Group was started in Baton Rouge.

The second Summit I attended got me started wondering about the SHARE aspect. 2 or 3 sessions I attended got me thinking “I can do that”. Now, it was not magic and I was presenting. The first presentations I start doing were every other week Lunch and Learns at work regarding upgrading SQL Server from 2000 to 2005. The first presentation outside of work was for the local SQL Server User Group about SQL Server monitoring with Performance Dashboards. It went well, and I received very candid feedback from employees I worked with. I have learned today that feedback (good and bad) helps me improve the presentations over time.

Getting involved with PASS, I have helped the local user group with presentations, volunteering for 4 SQLSaturday events and promoting locally. The Data Architecture Virtual Chapter needed help and I became the Chairperson for 2 years and have been able to encourage another DBA to assume the role after I retired which led into a VC mentor role.

The last item is LEARN. Well, if you have read this far, you should already know that all of the things above have helped me LEARN SQL Server - connecting to get the right people to go to with questions and sharing which enhances my skills. They are all learning opportunities which help advance my career and skill set.

Friday, November 7, 2014

PASS Summit: Blogger Q&A with Board of Directors Executive Committee

On Thursday, I was invited as a blogger to attend an intimate conversation (Q&A) with the PASS Board of Directors: Adam Jorgensen (EVP – Executive Vice President), Denise McInerney (VP Marketing) and Thomas LaRock (President). I started to try to understand why I was asked, but gave up due to mental exhaustion from attending the PASS Summit 2014. It was a great event with just way too much to do.

I decided to fish the internet and other community people for questions to ask. They boiled down to info about the BA conference, BOD structure/commitment, location of Summit and inquire about growth of DBAs in our community.  There were other questions, but the hour ran out. But, all three assured us that if you (Us and Ya’ll all) have a question or concern about PASS, each is individually willing to talk one on one through emails or phone calls. They even gave examples of individuals in the community that talked one on one to resolve an issue or understanding without have to use twitter or a blog post be load. We talked some about being mature members (growing up).

BA Conference – Why was it started, who is pushing it and why will it continue?

Thomas LaRock used my career  - from developer to DBA to BI Architect to a want to progress to Business Analytics. I never even thought about that until he mentioned it. Microsoft re-branded its SQL Server area for us as Microsoft Data Technologies – adding what is happening to data in this time and age. These are new opportunities for us data people.

There was no direct answer to the “pushing”, but the word Microsoft came up here and there. They are booked for the BA conference for the next 2 years in Silicon Valley (Santa Clara, CA) where there is a pool of speakers and talent in this area. Microsoft knows we (PASS) do community well, so they want us to help develop the community around BA. Adam mentioned the boards intent is not to split the community, but bring us together.

Denise mentioned how the first PASS Summit was an initiation only conference and they are going in that direction this year to help us get some understanding of what BA is really all about and help start to grow the speaker pool. It made sense. (I just read Mickey’s blog and she mentions there are vendors/exhibitors available as well in this area).

Summit Location – It will be in Seattle for the next 3-4 years (2015-2018 I believe)

There was no answer if it will be in another location. The deal is booking an event like this is starting to get more difficult with convention centers booking 4-5 years out. The BA conference is booked for 2 years (I believe they said that was the original plan/commitment).

Mark’s Question – Talk about the Election problems and why it took awhile for a response

The board and HQ had to abide by PASS By-Laws and laws of the state of IL. After HQ talk will Legal and quorums (12 people needed) were available, then decisions could be made (By the way, I think the decision and action were on the mark). The quorum problem got me to follow up my question about the board structure and time needed to be active in the decisions).

There were many conversations before quorum about what was the right thing to do. Denise ave credit to HQ for the movement of the process.

Board Structure and Time Required

I was amazed when Denise said she spends sometimes 2 hours a day on average (most made up on weekends because of having a ‘job’) and sometimes up to 30 hours in a week a month before a conference. Director At-Large maybe 5 hours a week. It could be as low a 2 hours a week if not much is going on.

The By-laws have the details if you want to read them (that is what they told me), but they did an excellent job making it simple for me.

Each Executive Member (elected – EVP, President and VP-Marketing, and Past-President stands on committee) of Board servers 2 year terms (max 8 years). The other 2 are Microsoft appointed (CA vacated their appointees last year). I asked if they will be replaced and that is being looked at (for help on the Executive Board). The total Board of Directors votes on the 3 Executive Positions.

The opening for president has a requirement of serving at least one year on executive committee. This past election we voted on was for At-Large members of the board. There are something like 6-8 at-large members. Tim Ford was one of them that sat in on the Q&A. I am sure there is more, but I will stop there.

Bill G (Past President) is pushing the Exec Board to get more HQ staff to take load off.

Growth of DBA members of PASS

No growth, but about 1/3 are DBAs. I do not know what to think about this, so I might follow up after it sinks in a little more.


Please visit Mark’s site. I am sure he will have a blog on this session. -


Side Notes:

All agreed that HQ is doing a fantastic job!!!

The board believes the best way to communicate items to the masses is up to HQs to make that call. The Board is trying to let HQ do the business work of PASS, while the board works on strategy.

The BA stuff is becoming a portfolio with a director (from current At-large board members).

LaRock was all about growth, which is still happening.

Wednesday, October 29, 2014

PASS Summit 2014

Well, it is that time of year again. The Professional Associations for SQL Server (PASS) annual summit. This year we go back to Seattle where Microsoft mostly lives and hope we can get lots of their employees to visit and talk shop.


One of my main goals is to Network and get contacts from around the SQL Server world, but I always find 3 new things to learn at the Summit to bring back to my job. There, I can show the value of going to this event to my boss(es). This year the first goal is to find new information about maintaining a robust Data Warehouse since this is my main job at eQHealth. The second is to learn about PowerBI and what can be done in the Cloud (yes, you heard that right I said Cloud). Finally, I need to learn about Column Store Indexes and other improvements in performance of queries. There is always something to learn in this area and many sessions to be a part of.


I fly in on Monday night, so I will miss the annual Networking event put on by Steve Jones and Andy Warren. If you are there Monday night, it is worth the time. Networking is a big deal at these events, so get out and start talking.


Tuesday, I will start the day with a visit with other PASS volunteers, then attend one of the many great Pre-Conference All-Day sessions with Alberto Ferrari – From 0 to DAX. Tuesday evening is the Welcome Reception and a Volunteer Party.




Wednesday morning I will start the morning at the Community Zone from 7:30AM to 8:30AM. Then, listen to the Keynote. The 10:15AM session will be with Reza Rad on Data Mining. I meet Reza last year and it was a pleasure talking and learning from him. Smart guy. At noon, I will be in the Virtual Chapter lunch as I am a VC Mentor and need to meet and greet our leaders. At 1:30PM, Jen Stirrup is talking PowerBI & R. Peter Meyers (one of the best presenters I know) will be presenting on Master Data Services is at 2:45PM, which might be helpful at my job. At the end of the day it is a toss up between Marco Russo – DAX Patterns and Jon Welch – Continuous Delivery of DW & DMs. Wednesday night is the Exhibitor Reception.


Thursday morning has another Keynote on The Cloud, followed by a 10:15 session by Reza Rad (again) on Query M Functions. I will probably have to leave early to man the Community Zone from 11:30-12:30 during lunch. Hosting a Lightening Round session is next at 1:30 which I am really excited about. I will probably have to chill out after the Lightening Round and take a leisure break at the community zone and just visit with people or end the day at Alberto Ferrari’s Adv Model w/ SSAS Tabular. Thursday night is the Community Appreciation Part – EMP Museum which is always fun.


Friday morning I present at 10:15AM – Excel 2013 Tips and Tricks for SSAS Multidimensional. Unless I am totally comfortable with the presentation, I will probably take it easy getting up in the morning, rehearse the presentation at the hotel and then end up in the Speaker Ready room and try to sneak in the presentation room to test everything. After that all bets are off, except to fly home late on a red-eye to get back to Baton Rouge.

Wednesday, October 15, 2014

Can’t Create A Timeline in Excel on Multidimensional Cube

Excel 2013 - We can’t create a Timeline for this report because it doesn’t have a field formatted as Date.

After getting excited about the Timeline object in Excel 2013, I tried to add this cool feature of Excel 2013 and got an error – “We can’t create a Timeline for this report because it doesn’t have a field formatted as Date.”



After some Bing searches :), I found the solution to be adding the ValueColumn property of the Date Key attribute to be a date value. In this example, my Date key attribute in the Date dimension is an integer like 20140101 not the actual date itself. The NameColumn property is a text description of the date.



After adding the actual date (FullDate), the first error went away.



The following error was new – We couldn’t create a Timeline because the date field contains values that are not supported as dates in Excel.




In the initial stages of loading data, we had source dates that were unknown and we replaced those dates with 01/01/1800 (12/31/2099 for End Dates) or the date would come in as 01/01/1000 from the source. These values do not work in the Timeline. Once I verified that the unknown dates were cleared up, I changed the Date source in the Data Source View of the cube from including these date values:


SELECT DateKey, FullDate, DateName, DayOfWeek, DayNameOfWeek, DayOfMonth, DayOfYear, WeekdayWeekend,
    WeekOfYear, MonthName, MonthOfYear, IsLastDayOfMonth, CalendarQuarter, CalendarYear,
    CalendarYearMonth, CalendarYearQtr, 'Q' + CONVERT(varchar(1), CalendarQuarter) AS CalendarQuarterName,
    DateRange, CONVERT(int, REPLACE(CalendarYearMonth, '-', '')) AS MonthKey,
    RTRIM(LTRIM(MonthName)) + ' ' + CONVERT(varchar(4), CalendarYear) AS MonthYear
FROM dbo.DimDate
WHERE (DateKey BETWEEN 20090101 AND 20141231)
  OR (DateKey = - 1)
  OR (DateKey = - 2)

The OR in the WHERE includes a lower (1800-01-01) and upper (2099-12-31) in the Date dimension. These are no longer needed because the data in scrubbed first to exclude or correct the date problems.


SELECT DateKey, FullDate, DateName, DayOfWeek, DayNameOfWeek, DayOfMonth, DayOfYear, WeekdayWeekend,
    WeekOfYear, MonthName, MonthOfYear, IsLastDayOfMonth, CalendarQuarter, CalendarYear,
    CalendarYearMonth, CalendarYearQtr, 'Q' + CONVERT(varchar(1), CalendarQuarter) AS CalendarQuarterName,
    DateRange, CONVERT(int, REPLACE(CalendarYearMonth, '-', '')) AS MonthKey,
    RTRIM(LTRIM(MonthName)) + ' ' + CONVERT(varchar(4), CalendarYear) AS MonthYear
FROM dbo.DimDate
WHERE (DateKey BETWEEN 20090101 AND 20141231)

Now, no more error:





Saturday, September 27, 2014

PASS got it right

This morning I received an email (really 3 emails) about an extension for voting for the Board of Directors giving anyone registered with PASS before June of 2014. I think this is fair and gives those that have been active, but did not update their profile a chance to vote.

I read some posts about not being able to vote, passwords exposed, etc. with concern but patience. One thing I have learned over the years is I have to listen to all sides of the story before making a statement. Usually, I do not do this because of my inpatient personality. This happened with work this past Friday while I should have been enjoying PTO because of the birth of my son. :)

PASS (Professional Association of SQL Server) has enabled me and many others I know, advance in our careers. There is money involved with this organization, and I think that causes some tension in our minds. I really believe some have voiced concern to get more attention to their blog (which is their consultant company).

I received 3 emails because I have at some point in the past registered with PASS under 3 different accounts. I do not remember why and when I did this, but I need to inactivate the 2 I do not use. This is my part in helping PASS maintain the list of valid members.

I read Andy Warren’s post about who he is voting for and why, and see the problems with PASS opening the association to more than just SQL Server professionals. I actually agree with him even though my career is migrating from a DBA role to Business Analytics. I also agree with his suggestions for the following years.

Here is why I was on PTO last week.

DSCN1425    DSCN1421

Wednesday, September 3, 2014

PASS Summit 2014 Announcements

In case you do not receive the PASS connector, here are some announcements for this year’s summit on Nov 3rd – 7th.

Day 2 Keynote -  Dr. Rimma Nehme from Jim Gray’s System Lab

Apparently, Dr. Mehme is a Polybase and query optimization expert from Microsoft and she will be talking Cloud 101. I am not involved in the Cloud, YET, but I feel it coming. This might be a great opportunity to listen to what is coming down the pipes. Polybase is the Microsoft product for Big Data in the Cloud, so I am sure we are going to hear how MS is going to lead the way in this technology. There are no sessions during the Keynotes, so everybody should have time to attend these morning starters.

24 Hours of PASS

Last year, I was able to present during the Summit Preview Edition and have hosted many sessions over the years. This is a great event to showcase the up coming Summit. Did I mention these are FREE? The session have speakers from the 2014 Summit and are going to show what they will be presenting on at the Summit.

WIT luncheon speaker Kimberly Bryant

During the summit, there are special events continuously happening all through the 5 day summit (1st 2 days are pre-conference sessions and last 3 days are regular and half-day sessions). One was started as a Women In Technology (WIT), but has morphed into getting everybody involved with promoting careers in technology. This year must be a little different because previous years usually have a panel. Kimberly Bryant from Black Girls Code (BGC) has been selected as the luncheon speaker on the Nov 6th luncheon.

Featured Blog: “PASS Summit 2014 Speaker Idol Competition”

“PASS Summit is hosting its first Speaker Idol competition, a chance for 12 new speakers to improve their skills and win a slot at next year’s Summit. Are you ready to take the challenge?Read Denny Cherry’s post announcing the rules, and qualifications to join as a contestant.” Pass Connector

Friday, August 8, 2014

Upcoming PASS Virtual Chapter Webcast (FREE!!!)

As always, there are many free ways to learn more about SQL Server and the Professional Association of SQL Server (PASS) free membership to Virtual Chapters is one of those ways.

Here is what I would suggest watching:


Next Meeting: Tue, Aug 12 2014

Bridging the Communication Gap Between DBA and Developer

Online Meeting URL:

Database Administrators and Developers are all working toward the same common goal yet sometimes they seem to be talking different languages. This session is for developers and DBAs alike and helps bridge the communication gap between the two groups.

Better communication means better relationships and better communication begins with understanding the other person. Learn how to think like the other person to understand what they are saying and to know how to respond back in a way that they will understand your point of view.


Robert is a SQL Server Certified Master, MVP, and an experienced Database Administrator, SQL Server evangelist, speaker, writer, and trainer. He has worked with SQL Server for 14+ years. He has recently worked for Outerwall, Idera Software, and Microsoft, He served as PM for the SQL Server Certified Master Program at Microsoft Learning, and as a production DBA at Microsoft. Robert is co-founder and co-leader of the Security Virtual Chapter for PASS.


UTC : Tue, Aug 12 2014 16:00 - 17:00
Event Time : Tue, Aug 12 2014 12:00 - 13:00 Eastern Daylight Time


Next Meeting: Tue, Aug 12 2014

Tue, Aug 12 2014 12:00 Central Daylight Time

Mirroring Start to Finish



Mirroring is not dead! If you don't have Enterprise Edition, then this is still your tried and true solution. We’ll cover what mirroring is from start to finish, how it can fit into an HA/DR plan, the rules surrounding its use, configuration via the GUI and T-SQL, as well as how to monitor mirroring. This presentation is designed to walk you through a basic implementation. At the end you will have learned what mirroring is, how it can fit into your environment, what business requirements it solves, and how to configure it.

Ryan Adams has worked for Verizon for 16 years. His primary focus is the SQL Server Engine, high availability, and disaster recovery. Previously he was a Senior Active Directory Architect and designed the company's worldwide Active Directory infrastructure. He serves on the Board of Directors for the North Texas SQL Server User Group and is President of the PASS Performance Virtual Chapter


Wed, Aug 13 2014 10:00 Mountain Daylight Time

Optimizing SQL Server Performance in a Virtual Environment


Building Your Own SQL Server Virtual Playground

As the number of SQL Server versions and features grow over time so does the the difficulty in maintaining a set of tools for working with all of them on your local machine. Add in that some features require multiple physical instances and suddenly a single installation isn't enough. Need to quickly switch between versions and editions of SQL Server? Want to learn how to set up a cluster? Or maybe you want to try out the latest features in SQL 2012...virtualization offers a convenient way to do any or all of these. In this session we'll show you how by covering the fundamentals of desktop virtualization and demonstrating how to set up a virtual playground on your own machine.


Kendal is a database strategist, community advocate, public speaker, and blogger. He is passionate about helping leaders use Microsoft SQL Server to solve complex problems that protect, unlock and optimize data's value. Since 1999, Kendal has specialized in SQL Server database management solutions and provided IT strategy consulting.


Wed, Aug 13 2014 12:00 Mountain Daylight Time

Best Practices for your Very Large Databases– sponsored by Dell Software



Abstract: As businesses' data needs keep growing so do the sizes of the databases we are entrusted to manage. Faced with constant growth and the scale of multiple Terabytes, more DBA's are facing the challenges to properly tune and maintain the VLDB's in their environment. In this session we will go into the best practices, tips and tricks learned from helping customers in the field with this common growing pain. We will cover the best way to do integrity checks, stats updates, index maintenance, partitioned tables and more so you can to tame your Very Large Databases and keep them running in top shape.

Warner is a SQL Server MCM and SQL Server Principal Consultant at Pythian, a global Canada-based company specialized in DBA services. A brief stint in .NET programming led to his early DBA formation working for enterprise customers in Hewlett-Packard ITO organization. From there he transitioned to his current position at Pythian, managing multiple customers and instances in many versions and industries while leading a highly talented team of SQL Server DBAs.


Tue, Aug 19 2014 11:00 Central Daylight Time

The Roadmap to Better Performance: Reading Query Plans



Whether you’re a DBA or a developer, you probably know that when you submit a query to SQL Server it generates a plan – a map if you will – that determines how it will get the data you’ve requested. But if you’re not familiar with how to read that map, then how do you know how to create a faster route to the data? How can you tune a query? If you’ve been lost when looking at plans before, then sign up for this session where we will cover the basics of query plans. We’ll step through how to capture them, discuss the essential information to review in a plan, and highlight a couple patterns to look for when tuning queries. As usual, expect lots of demos to highlight key points, and “plan” to walk away with new methods to use when reviewing query plans in SQL Server. Level: 200

Erin Stellato is a Principal Consultant with SQLskills and lives in Cleveland, OH. She has over 12 years of technology experience and has worked with SQL Server since 2003. Her areas of interest include internals, performance tuning, and high availability and disaster recovery. Erin is a SQL Server MVP and an active member of the SQL Server community. She is involved with the Ohio North SQL Server User Group and blogs at You can find her on Twitter at @ErinStellato.

Monday, July 21, 2014

PASS Data Arch VC presents SQL Server MVP Grant Fritchey - How to Build a Database Deployment Pipeline

Please come join the Data Architecture VC this Wednesday to get info from MVP Grant about Database Deployment something all DBAs should be doing to work with developers.

Grant Fritchey – Microsoft MVP

July 23, 2014 Noon CST

How to Build a Database Deployment Pipeline.

The pace of business accelerates fairly continuously and application development moves right with it. But we’re still trying to deploy databases the same way we did 10 years ago. This session addresses the need for changes in organizational structure, process and technology necessary to arrive at a nimble, fast, automatable and continuous database deployment process. We’ll use actual customer case studies to illustrate both the common methods and the unique context that led to a continuous delivery process that is best described as a pipeline. You will learn how to customize common practices and tool sets to build a database deployment pipeline unique to your environment in order to speed your own database delivery while still protecting your organization’s most valuable asset, its data.

Speaker Bio:

Grant Fritchey, SQL Server MVP, works for Red Gate Software as Product Evangelist. In his time as a DBA and developer, he has worked at three failed dot–coms, a major consulting company, a global bank and an international insurance & engineering company. Grant volunteers for the Professional Association of SQL Server Users (PASS). He is the author of the books SQL Server Execution Plans (Simple-Talk

Webinar Link Click here

Tuesday, July 8, 2014

Upcoming Free PASS VC Webcasts

Here is a list of upcoming webcast presented by various PASS Virtual Chapters that are always free. Some are recorded to view later.


Database Administration VC

Date: July 9

Noon Mountain

Topic: How to be a great DBA– sponsored by Dell Software

Registration: You must register if you want to attend. You can register at

When you register, you will receive a link to the meeting. All registrants will be included in a drawing for a $50 Amazon gift card.

Presenter: Jeff Garbus

Abstract: This presentation describes the tasks a DBA needs to perform to do a great job. Formal training classes and certifications only take you so far; this session talks from experience about everything from preventive maintenance to planning to scaling to communication. Great for beginner to intermediate DBAs, as well as Developers who think this is a direction in which they might want to move.

Bio: Jeff Garbus is a well-known author, lecturer and consultant with more than 20 years of expertise in architecture, tuning and the administration of Microsoft SQL Server, Sybase ASE, Oracle, and other databases. Jeff has assisted clients of all sizes, from .com startups to the most demanding F100 clients. His specialty has always been maximizing database performance for his clients. Jeff has recently released his 16th book: Microsoft Transact SQL – The Definitive Guide. Jeff is also the founder and CEO of Soaring Eagle Consulting, a consulting and management consulting firm specializing in all aspects of database and information management.


DBA Fundamental VC


WHAT       Rolling Upgrades, The Easy Way!

WHEN:      Tuesday, August 5,   11:00 AM – 12:00 PM CDT

WHO:        Argenis Fernandez


In this session we will discuss how to perform upgrades while maintaining high availability of your SQL server infrastructure by leveraging rolling upgrades. We will review how to use database mirroring or AlwaysOn Availability Groups for this, along with a simpler version using Log Shipping. This is a demo-intensive session that you won't want to miss. 

There will be a drawing for a $25  Amazon gift card at the end of the meeting.  A winner will be randomly chosen from all those in attendance for the whole webcast who provide their email address.


Data Architecture VC


Grant Fritchey – Microsoft MVP

July 23, 2014 Noon CST

How to Build a Database Deployment Pipeline.

The pace of business accelerates fairly continuously and application development moves right with it. But we’re still trying to deploy databases the same way we did 10 years ago. This session addresses the need for changes in organizational structure, process and technology necessary to arrive at a nimble, fast, automatable and continuous database deployment process. We’ll use actual customer case studies to illustrate both the common methods and the unique context that led to a continuous delivery process that is best described as a pipeline. You will learn how to customize common practices and tool sets to build a database deployment pipeline unique to your environment in order to speed your own database delivery while still protecting your organization’s most valuable asset, its data.

Speaker Bio:

Grant Fritchey, SQL Server MVP, works for Red Gate Software as Product Evangelist. In his time as a DBA and developer, he has worked at three failed dot–coms, a major consulting company, a global bank and an international insurance & engineering company. Grant volunteers for the Professional Association of SQL Server Users (PASS). He is the author of the books SQL Server Execution Plans (Simple-Talk



Virtualization VC


Wed, Jul 09 2014 10:00 Mountain Daylight Time

SQL Server Virtualization 301



Can you objectively demonstrate that your SQL Servers are running the same or better after they were virtualized than when they were physical servers? Do you have your key system monitoring in place so you know your steady-state system performance metrics? What is your methodology and key items for benchmarking and baselines, and what are your long-term projections for when you will need more infrastructure capacity? Performance and capacity management topics are discussed in this interactive session, and key tips and tricks will be presented to help you squeeze the most performance from your virtualized SQL Servers.

David Klee, VMware vExpert, is a SQL Server performance and virtualization expert. With over seventeen years of IT experience, David spends his days handling performance and HA/DR architecture of physical and virtualized critical SQL Servers as the Founder of Heraflux Technologies. His areas of expertise are virtualization and performance, datacenter architecture, and risk mitigation through high availability and disaster recovery. When he is not geeking out on database and infrastructure techno.

Thursday, June 19, 2014

5 Things to Know About Indexes for SQL Server

One of the first questions I got while interviewing for a potential position as a DBA was “If there is performance problem, where is the first place you would look?”

Now at this time, I was trying to move from Software Developer to DBA, and I answered that I would look at IO, Network and errors in code. The interviewer said, “Indexes.” I always assumed that all database designers knew you needed good indexes. This changed my thinking about what I was learning to start reading more about being a DBA. SQLServerCentral.Com became my friend and I started reading one article a day and answering the Question of the Day every morning when I got to work.

1. Clustered Index

What is a clustered index? Or better yet, what do I need to know about a clustered index? The first thing to know is that you can have only one. The logical structure of the table becomes the order of the column(s) you use in a clustered index. You might hear someone say that the primary key is always the clustered index. This is not true. Any index can be the clustered index. By default, the primary key becomes the clustered index unless you change this in a script or the User Interface creating/modifying a table.

ALTER TABLE [Person].[Address]
    ADD  CONSTRAINT [PK_Address_AddressID]
  ( [AddressID] ASC )


2. Non-Clustered Index

You can have more than one non-clustered index. I would suggest creating a clustered index before creating any non-clustered indexes. The clustered index column(s) become part of the non-clustered index. A query plan might need to go to the clustered index to retrieve more columns not available in the non-clustered index after a scan or search of non-clustered index. The non-clustered index can be one or more columns, and the order is important. You want the most specific column (as far as values go) as the first column. It also important to have the first column(s) to be used in the join or where clause that you are trying to be used for a query.

    ON [Person].[Address]
( [AddressLine1] ASC, [AddressLine2] ASC, [City] ASC,
     [StateProvinceID] ASC, [PostalCode] ASC)


3. Include Columns (i.e. Covering Index)

This is where Microsoft started shining in my opinion. The include columns in a non-clustered index can be these columns that your query is trying to retrieve from the clustered index mentioned above. Instead of retrieving more columns from the clustered index, the include columns are the ones the query needs. The are stored in the Data Page of the non-clustered index and not in the index tree.  Be careful not to go overboard with include columns, because you can eventually create a whole new table (even though it is an index) by including all the columns not specified in the index itself. I have also seen a form of Deadlock with Include Column indexes.

    ON [Person].[Address]
( [StateProvinceID] ASC)
INCLUDE ([AddressLine1], [AddressLine2], [City], [PostalCode])


4. Filtered Indexes

Filtered Indexes should be used carefully because they could become more of a performance problem with Insert/Update/Delete statements. They are really nice for large data warehouses where some tables are not structure. Also, I have seen them used for a column that has one value other than a empty value. Filtered indexes are new to SQL Server, so you should see performance improvements in future versions.

    ON [Person].[Address]
    (AddressLine1, [AddressLine2], [City], [PostalCode])
  WHERE [StateCode] = 'LA'


5. Missing Index Feature

Now, this is really cool. With SQL Server 2008, the Query Plan now includes a Missing Index feature which basically gives you a hint on what index might be beneficial. Again, you should be cautious when it suggests include columns that look like all the columns in the table. See number 3 above.


In conclusion, indexes are a great help with query performance. There are some basics shown above to get you started with understanding indexes. Do not close your mind to more information about indexes after you start to understand what was mentioned in this article. I know today after doing DBA work for over 10 years, there is always more to learn.

Wednesday, May 7, 2014

SQLSaturday #308 Houston, TX Saturday May 10th

This will be the second SQLSaturday in Houston I have spoken at and I am excited about talking Execution Plans and Multidimensional Analysis Services. These are 2 topics I deal with on a daily basis, so it is great I get to help others understand these technologies.


I also get to talk with John Sterret, Alan Kinsel and Nancy Hide Wilson who are #SQLFamily. Their hard work on spreading the knowledge of SQL Server is a pleasure to watch.

There are many great speakers coming to Houston because the MS TechED event is the following week in Houston. So, MVPs, MCMs and veteran speakers will be at San Jacinto College – South Campus this Saturday to help all us learn to love SQL Server.

Hope to see you there!!!

Monday, May 5, 2014

SQLSaturday #305 Business Analytics Edition Dallas–RECAP

So, I am flying home thinking about the event Saturday and many things are running through my head. No, not work on Monday. First, I would like to say that Hadoop is not going to replace SQL Server nor Business Intelligence. Second, a new thing is coming but it is only going to compliment what we already do. And last, Dallas did an excellent job of spreading the learning over 4 tracks with lots to choose from – Visualization, Deployment, Big Data and Analytics. You always had something good to choice from.

The Dimensional Modeling 101 session I presented was great. Lots of sharing of individual experience with creating dimensions and facts. Most of the time was spent on talking about surrogate keys and performance. When I first started doing dimensional modeling, I had the idea that all data came from one transactional system with foreign keys on all tables. The real world proves different. That is what happens after you use some technology for awhile. There was much talk about making mistakes with dimensional modeling and learning from success and failure. There were mostly good comments and 4-5 conversations after the session, during lunch and between other sessions. I look forward to reviewing the critical comments to improve my session abstracts, session content and presentation.

The first session I attended was Intro to Hadoop. The MSBIC group from Dallas was able to get HortonWorks’ speakers which help with explaining a lot about how Big Data works. It is obvious this is a good thing for high output manufacturing systems, logging systems or internet data. Future versions will expand the current features. You could hear many questions about how it all works because you want to wrap your head around the inner workings. The visual display of the Hadoop stack helped tremendously. There is definitely a programming background needed before diving into this new technology. You can download a free sandbox from HortonWorks.

After my session (9:45), I went to a session on pig. It was very hard to follow, so afterwards I went to the speaker room to discuss with others. There, I meet back up with Meagan from Kansas City and Kristen, the leader of the Oklahoma City SQL Server user group. This conversation continued from where we left off from the Friday night speaker social. There is a lot of excitement within the SQL Server community no matter where you are.

The lunch session involved a Microsoft Technical Architect (David Brown) from Dallas that shared his experience with clients in the BI stack. He had great examples that had people laughing and relating to his project management. One comment he made that has stuck in my head is that the problems presented with BI – Self-service, Master Data, Security, Performance, etc. – was “Maybe these problems are good to have for IT.” I think I understand.

The afternoon included a HDInsight session. The cloud is here and you can use it. Next was statistics with R and Mahout which was more about how excited the speaker was rather than demos (that worked). Lastly, was a Slalom consultant talking about Time Intelligence in PowerPivot. He did a good job of listing the functions, but more time should have been spent on simple examples rather than showing everything. The crowd started to ask to many questions that he had to back up and explain.

Finally, I wanted to mention something that happened during the last PASS Summit 2013 in Charlotte that triggered me  to come to this SQLSaturday. Karla was talking with me about the Business Analytics conference coming up and I said something about how this was not SQL Server. Her comments made me think back 6-7 years ago when BI started to gain exposure at the Summit and I and others where saying “This is not SQL Server.” Now, I am in the BI stack and love it. I still love DBA work, but I expanded my skills by learning SSIS and SSAS. In Baton Rouge, you need a variety of skills to be employed in the SQL Server realm. You can not just be an expert in SSIS and expect to be employed.

Now might be the time to learn some more skills – Big Data.

Winking smile

Wednesday, April 23, 2014

SQLSaturday #305 Dallas Business Analytics Edition

I am excited about being selected to speak and attend the May 3rd SQLSaturday BA Edition in Dallas, TX. There are a number of sessions I want to attend including Eric Mezell’s An Introduction to Hadoop, Andy Egger’s Applied Predictive Analytics, David Browne on Enterprise Business Intelligence (for lunch) and Garrett Edmonson – Measuring Data Warehouse Performance. The full schedule is here -

I hope to create some time to visit with old friends in the Dallas area like Brian Smith (MS) and Tim Costello. I hope this is a precursor for Dallas to host a PASS Business Analytics week long conference in the future. California was a little to far to travel this year even though I should have went.

I am presenting one of my first BI presentations I have ever given – Dimensional Modeling 101. When I move over to Business Intelligence, I had a strong background in Data Normalization. Transitioning to a Dimension Model was quite difficult and I wish I had someone to explain like Warren Thornthwaite did at a pass PASS Summit before I started. He did a great job using PASS registration data to great a simple Data Mart and Analysis Services cube while previewing in Excel. That hooked me.

Thanks to all the sponsors for helping make this a free event for all attendees.


Friday, April 11, 2014

Call for Speakers – SQLSaturday #324 Baton Rouge

Baton Rouge is now on its 6th SQLSaturday and the event grows year after year. It all started with Patrick LeBlanc back in 2009 with #17 and with help from local User Groups (William AssafSparkhound), it has expanded its topics and attendees. This event is also called Baton Rouge Tech Fest because there are more than just SQL Server related topics. Last year, we had a CIO/Manager track, Windows Phone, SharePoint and .Net.

So, this is a Call for Speakers to the next SQLSaturday August 2dn 2014 in Baton Rouge at the Business Center on LSU’s campus. Most speakers get at least one session, so do not be shy about submitting. If you need help, practice by giving a Lighting Round at the local User Group (SQL and .Net) meeting the 2nd Wednesday of every month. There is even a Java user group meeting at the same time.

Pictures from previous SQLSaturdays in Baton Rouge


Tuesday, March 25, 2014

5 Tools Every DBA Should Know About

After monitoring enterprise SQL Server instances for over 10 years now, there are 5 tools that are used every day for spot check views of a system. The order here is not a countdown from 5 to 1. They are presented here in no particular order, because each has its own area where it is number one. Over the years, those around me have gain more understanding of a SQL Server instance and fine tuning that is needed to optimize the usage of these tools. Though most are free, there is a cost in performance on using these assistants and you better have a good understanding of their output.

Performance Dashboard

This feature was added in SQL Server 2005, but has versions available for 2008 and 2012. The 2008 version is actually the 2005 version with a modification to the script. It is a central location from Management Studio to view what is going on in the last 15 minutes plus drill-thru reports for current OS and SQL statistics along with historical statistics. The dashboard is divided into 4 areas – CPU Utilization, Current Waits, Current Activity and Historical/Miscellaneous Information. The links on the dashboard provided in each area drill into reports (rdl files) that get statistics from DMOs (Dynamic Management Objects) which are DMFs and DMVs you might be familiar with.


The top-left quadrant is for CPU Utilization. The blue part of the percentage bar is the amount of CPU used from SQL Server process, while the rest is outside of SQL Server CPU usage – Windows OS and other applications. Clicking the blue part of the CPU bar will show current running queries ordered by CPU usage. This is convenient information about was is taking up SQL CPU time at the current time. The Current Waits section lists the waits categorized by type with drill-thru capabilities to see the queries with waits. Blocking is one common wait I see. The lower left Current Activity table lets you see the requests happening along with all current connections from the User Sessions link. The 4th section has links to more historical statistics like IO by database files, Waits summed up by category, along with reports about queries sorted by Reads, Writes, CPU, Duration and more. The database IO usage can help with SAN Administrators.



Adam Machanic has done a great job with this tool, and it is suggested to go visit the 29 days of SP_WhoIsActive to really understand what he has done. I would take my time reading his blogs and definitely let each day sink in before going to the next post. This tool gives the user the ability to see what is running with all kinds of statistics like CPU, reads, writes and the list goes on and on. I have 2 short cut keys in Management Studio (SSMS) with 2 different versions: one has the Query Plan in a column and the other does not. I also filter out some logins (sa) and databases (master & msdb).

Ctrl-6 – no Query Plan and some filtering


Ctrl-7 – include Query Plan with different filtering

The filter assists with clearing the noise of executing queries from certain applications. Being able to drill into the query plan from the selected query is great because you can see the execution plan in SSMS.


I have to say I still use sp_Who3 ‘Active’ first before going to SP_WhoIsActive. I remember first using this in SQL Server 2000, but Denny Cherry might have created it before then. The query uses DBCC InputBuffer to get the actual SQL statement that is associated with the SPID of concern which is not available in some cases with DMOs.


Built-In Reports

Back to Microsoft and the ability to use reports right from Management Studio. My favorite is Disk Usage and Disk Usage by Top Tables. Right-clicking on a database, then selecting Reports/Standard Reports/Disk Usage from the context menu gives a summary of the database data and log files. You can see how full these files are and space free. If the files have expanded recently, you can expand the amounts and time it took. We recently used this to see a new Distribution database for replication was expanding at 1MB but 507 times each minute which showed up as IO Wait.

image  image

I really like the Schema Change and backup and Restore Events, but as you can see below there are many to help with exploring a instances and its databases.




PlanExplorer is a tool to view Query/Execution Plans. This tool integrates into Management Studio (SSMS) so it can be launch from viewing a query plan, maybe after using SP_WhoIsActive. The ability to sort by different columns of statistics from the plan is great. It also compresses the graphical display for easier reading. Highlights with various colors indicate the high object usage percentages so you can drill to the iterators that are the heavy hitters. See below for a view (Fit to Screen) in SSMS followed by the PlanExplorer view.





Should not be hard to see the difference this tools makes with viewing execution plans.



Even though Extended Events will be replacing Profiler in a few versions, Profiler is still being used every day around the world. Once you master the events to trap and columns to view, this tool becomes a great way to see what is going on. Profiler enables you to save templates once you get the columns and events setup that you like. Recalling these templates helps quickly start a profiler trace.


Caution should be notes here that profiler can cause performance problems on systems that are used heavily. I once frozen a production system that had 16 processors and 128GB of RAM by creating a client profile and trying to save the Query Plan event on a production system. The boss was not happy. This is where I learned you can script the Profile trace out and use as a Server-Side trace, saving the trace to a file to be read later. We also removed capturing the Query Plan which I do not suggest using in a trace.


Sunday, March 9, 2014

5 Things a developer should know about databases


Database Normalization

Theoretical versus real-world

There is not a strong need for developers to know the theoretical definition of 1st thru 5th normal form, but there needs to be an understanding of some design practices. The keys are:

  1. No Duplicate data in columns in one table
  2. Relationships built as foreign keys
  3. Primary Key to identify a row
  4. Learn Many to Many relationships (this could be a whole blog)

Always look for natural key when using identity for Primary Key

The first of which is a primary key. Over the years, I have finally grasped the understanding of using an Identity column as the primary key but always design a table with a natural key. The following example that shows the difference in a Customer table.

CREATE TABLE [dbo].[Customer](
    [CustomerID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerName] [varchar](40) NOT NULL,
    [AddressLine1] [varchar](60) NULL,
    [City] [varchar](30) NOT NULL,
    [StateAbbreviation] [varchar](2) NOT NULL,
    [PostalCode] [varchar](15) NOT NULL,
( [CustomerID] ASC) 
    ON [dbo].[Customer]
([CustomerName] ASC)

The key is that I understand that the Identity column is not the column used to locate a customer, but the the Customer Name is really how we identify a customer. From my experience, I saw the identity column come into software development for 3 reasons:

  1. The int column takes up less space in non-clustered indexes
  2. It helps with lookups in Objects for Object Oriented coding
  3. The joins are faster and helps with related tables when used as part of the related table’s primary and/or foreign key

Lookup versus Parent-Child tables

The Customer table can be can be considered a Lookup table for customers that have Orders. The relationship between an Order Header and Order Detail would be a Parent-Child relationship. The Order Header and Detail still need to have a Natural Key just like the Customer table even though Identity is used strongly in these tables.


The example above show OrderID as the primary key of the OrderHeaders table, but there is a natural key to identify rows which is CustomerID + OrderDate. The OrderID column is carried down into the OrderDetail table. The column is combined with ProductID to form the primary key of the table.


Data Types

char vs. varchar

Remember that using a char data type will use the space required in the size in the data pages of ever row, where varchar will only take up the space used in the actual data in the column for that row.

Money not decimal

The money data type is a much better option for dollar amounts in a database than trying to specify the size and number of decimal places in a decimal data type.

nvarchar or nchar

The use of nvarchar and nchar are for storage of all Unicode characters while char and varchar are for non-Unicode characters. The nchar/nvarchar data types take up 2 times the space for a column in each row. In 25 years of IT work, I have never seen a need for Unicode characters, though some would say differently.

integers – tiny, small, int and bigint

The integer data type now have various size that are different in space in the rows of a table. If you know that a lookup table only has 10 possible rows, you do not need to use int or bigint for the ID column. You should go ahead and use smallint. See Books Online for more information.


Learn Primary Key defaults to Clustered

As we saw in the diagram above, the CustomerID was the primary, clustered index of the Customer table without even specifying clustered. This is by default for SQL Server. You can change this to be a non-clustered primary key index, but you should always have a clustered index on a table in a transaction type database (OLTP). You could have easily specified the Unique Index on CustomerName as Clustered but be warned. The clustered index columns are included in all non-clustered indexes on a table.

Non-clustered indexes

The non-clustered index is usually created for improving the performance of queries that request data from a different column than the primary key. In the example above, a query on CustomerName in a WHERE clause of a query can return results faster because of the Unique Non-Clustered index. Another place to look for non-clustered indexes is on foreign key columns of related tables.

Overuse of Include clause

Creating covering indexes used to require including the additional columns in the actual index for help with eliminating a lookup in a query plan. The addition of the INLCUDE clause in SQL Server now lets you include additional columns at the leaf level of the index so the additional columns do not have to take of space in the tree of the index. Watch out not to overuse this option, because you can end up with more data space used in indexes than the table itself. Also, I have seen where include indexes cause Deadlocking, which is not a good thing.


Do not use SELECT * (specify columns)

Using SELECT * causes a couple of issues. First, it might take a full scan of the table (Clustered Index Scan). This has higher IO and Memory usage than just specifying the columns you need. Second, if someone adds additional columns to the table, it can break views and code that are not equipped to handle the additional columns

Learn difference between LEFT and INNER

Joins in T-SQL go back to the idea of Database Normalization. The INNER JOIN will return all the rows that match in both tables. The LEFT will return all rows from the table specified in the FROM part of the T-SQL and returns data to the columns specified in the JOIN table that have a match. The rows that do not have a match will have Nulls in those specified columns.

New Features of SQL Server

Windows Functions

Every new version of SQL Server comes out with improvements and additions. Yes, Microsoft does actually improve our lives. I am extremely excited to use the ROW_NUMBER() OVER PARTITION to find the latest or earliest rows in a data set. This eliminates the need to use MAX on a Date in a sub-query to join to the main query to find some matching data that is not related to the keys of the tables. There is even more functions like RANK, DENSE_RANK and NTILE.

Common Table Expressions (replaces cursors in some cases)

If you want to learn something really cool, try common table expressions. This can eliminate the use of CURSORS to loop through rows with T-SQL code. It will get you on path to understanding Set Based querying of your database.

There are many of features of SQL Server that a developer should learn, and these are just some of the common items I see new developers lack in their experience that are usually the first for me to help as a co-worker. Happy programming!!!

Tuesday, January 28, 2014

Jan PASS Data Architecture VC presents Ike Ellis on Tips & Tricks

Please join the PASS Data Architecture virtual chapter on Thursday Jan 30th at noon central for SQL Server Tips and Tricks presented in 5 minute lectures.

Next Meeting

Thu, Jan 30 2014 12:00 (GMT-06:00) Central Time (US & Canada)


This is a SQL presentation for the YouTube generation.  Perfect for half-attentively listening in on your lunch break.  It's really twenty separate five minute lectures.  We'll cover tips and tricks on a range of topics, including data visualization, performance, architecture, and ETL processes.
About Ike Ellis:
Ike Ellis is a 17-year veteran with SQL Server.  He's written millions of lines of code, designed many databases, and troubleshot hundreds of performance problems (some of which he wasn't even the cause of.)  He's a SQL Server MVP, TechEd speaker, RedGate speaker, user group chairperson, and makes a mean guacamole.  Right now, he's neck-deep in several business intelligence projects.  He's designed dashboards, written ETL frameworks, created reports, and all that goes with it.  He doesn't know everything, but he loves to share and teach the things he does know.   Come join him at the SQL Pass Book Readers Virtual Chapter

Wednesday, January 15, 2014

PASS BI/DW Virtual Chapter–Analysis Services Attributes and Hierarchies

Please join the PASS BI/DW VC today January 15th at 7PM Central for a presentation on Attributes and Hierarchies in Analysis Services 2012. This is a presentation I have shown at 2 SQLSaturdays and the Baton Rouge SQL Server User Group as well as 24 Hours of PASS.

Next Meeting

Wed, Jan 15 2014 7PM Central

Thu, Jan 16 2014 12:00 (GMT+10:00) Canberra, Melbourne, Sydney

Attributes & Hierarchies in Analysis Services 2012



The session explains Attributes and Hierarchies in Analysis Services 2012 Semantic Model. They are used to slice (filter) and dice (group) measures and dimensions used to view analytical data. The slicing and dicing of data involves fields, columns, or whatever you want to labeled them. But, in SQL Server Semantic model these are called Attributes and Hierarchies. The configuration of these slicers and dicers are important in SQL Server Analysis Services. This session will demonstrate the difference between an attribute and columns in a hierarchy in the dimension as well as the configuration of these for best performance and viewing.

Thomas LeBlanc (MCSA BI 2008, MCITP 2005/2008 DBA & MCDBA 2000) is a Data Warehouse Architect in Baton Rouge, LA. He has been in the IT field since 1989 starting as a COBOL programmer, graduating to dBase, FoxPro, Visual FoxPro, upgrading to Visual Basic versions 3-6 and even some .Net(C#). Designing and developing normalized database has become his passion. Dimensional Modeling is now his future. Full-time DBA work started in 2001 for Thomas while working at a Paper Mill. DBA related work involved supporting 600+ GB databases with replication to a Data Warehouse and DB Mirroring to a disaster recovery site. Performance tuning and reviewing database design and code are an everyday occurrence. Using the Microsoft BI stack for analytical reporting has sharpened his skills with SSIS, SSAS, SSRS and PerformancePoint.

Wednesday, January 8, 2014

Jan 8th – Baton Rouge SQL Server User Group - Azure

Please join us tonight, Wednesday Jan 8th starting at 5:45PM with networking and food. The information below is from the BRSSUG Website:

January '14 Baton Rouge User Groups Meeting - Azure!

Topic: Microsoft Azure!

This is an all user groups on deck meeting! SQL, .net, SharePoint, IT Pro, La-WIT are all invited!

Date: Wednesday, January 8, 2014

Location: The Louisiana Technology Park -

Sponsored By: Microsoft 


5:45 - 6:15 pm: Networking and Refreshments

6:15 - 6:30 pm: Announcements

6:30 - 8:00 pm: Microsoft!

Main Topic: Microsoft Azure Info for ALL User Groups

Main Topic Speakers: Ryan Roussel, Microsoft and Bobby Lee, Microsoft

Main Topic Summary:

Azure is an exciting platform and between IAAS, PAAS, big data, storage, media, mobile services, etc. the possibilities in Azure truly seem limitless.  This user group session will focus on an introduction to Azure with a focus on the agenda topics below.  The session will include lots of live Azure demonstrations and answering questions from the group.  

Every person in attendance will have free access to Azure so they can do everything demoed at the user group meeting on their own after the session!

· Review Azure IAAS (Infrastructure As A Service) – Windows and Linux
· Review Azure PAAS (Platform As A Service) and Azure for the web 

    o How do developers leverage Azure for .Net, JAVA, Node.js, PHP, Python, Ruby, etc.
· Review Azure Mobile Services – Azure development for mobile apps on iOS, Android, Windows Phone
· Azure storage options review Azure storage (tables, blob, queue)
    o Developer perspective, when to look at Azure Storage, IAAS database (SQL or Oracle) for SQL Database (Azure PAAS database)
· Free Azure access, review options:
    o MSDN users have free Azure access of $150-$200 free Azure capacity per month. Activate today no credit card needed:

    o Free Azure access for Non-MSDN users, NO credit card needed: