Both pre-cons were worth every penny, all the presenters are just excellent speakers and very knowledgeable.

The next three days were the main conference.  Since so many people have already wrote daily posts I’ll just list them all here for your reading pleasure:

Wednesday

SQL PASS Keynote Notes by Denny Cherry
Summit Sessions Day 1 #sqlpass by Tim Radney
PASS Summit 2011 Day #1 Post #1 by Grant Fritchey
PASS Summit 2011, Day 1 by Kevin Kline
Liveblog for #SQLPASS 2011 Day 1 Keynote by Brent Ozar
PASS Summit 2011:  Wednesday Keynote by Andy Leonard
Microsoft Loves Your Big Data by Mike Walsh
Day 4 Summit Keynotes Live, DENALI HAS A NAME! SQL Server 2012! by Bradley Ball
PASS Summit Day 1 – Keynote Live Blog by Jen McCown
PASS Community Summit Day One Re-Cap by Tim Ford
PASS Summit Keynote Day 1 Hightlights by Jorge Segarra

Thursday

PASS Summit 2011 Day 2 – Keynote Live Blog by Jen McCown
PASS Summit 2011 Day 2 Keynote Liveblog #sqlpass by Brent Ozar
PASS Summit Day 2- Key Notes by Grant Fritchey
PASS Summit Day Two – Electric Boogaloo by Tim Ford
Blogging from the PASS Summit Keynote:  Day 2 by Aaron Bertrand
PASS Summit 2011 Day 2 – Women In Technology & SQLKilt Day by Allen White
Data, Information and Knowledge by Rob Farley
PASS Summit 2011 – Day 2 Keynote by Chris Webb
Day four of Summit – Keynote 2 #sqlpass by Tim Radney
PASS Summit Day 2 – Women in Technology Live Blog by Jen McCown
SQL PASS 2011 Keynote Day 2 by Denny Cherry
PASS Summit Keynote Day 2 by Wes Brown

Friday

PASS Summit 2011 Day 3 – Keynote Live Blog with Dr. DeWitt by Jen McCown
Day 6: Friday PASS Summit 2011 Keynote Live by Bradley Ball
PASS Summit 2011 – Keynote Day #3 by Jason Strate
PASS Keynote Day #3: Dr. Dewitt by Grant Fritchey
PASS Summit 2011, Day 3 – A Tribute to Wayne Snyder by Kevin Kline
Final Summit Keynote – NoSQL = Not Only SQL by Tim Radney
PASS Summit 2011 – Day 3 Keynote by Chris Webb
Blogging from the PASS Summit Keynote: Day 3 by Aaron Bertrand
PASS Summit 2011 – The Final Day by Allen White
PASS Community Summit 2011 Day 3 Keynote by Todd McDermid

I am sure there are more of these posts, I will continue to add to this post.

Well, this issue is near and dear to my heart.  And this is the reason why:

Deanna and I

My daughter, Deanna, just turned 18 this month.  And next week she starts her Senior year in high school.  So, the question “What I am I going to do with my life?” is very much on her mind.  And on mine.

But as much as I would like to push promote the idea of IT to my daughter, I temper my words for two very good reasons:

1. My daughter is the least geeky member of the family.  I am geeky, my husband is geeky, our son is…well he is so geeky I am convinced that he took all of the geekiness out of the family gene pool and didn’t leave any for his sister!  Seriously, when Deanna was 14 she took a computer fundamentals class and just hated it.  So, at that point, I did not dare suggest she should consider IT as a career choice.  That’s because a long time ago I learned that trying to push people into something they did not want to do was a waste of time…and I learned that from the next reason…
2. During my teen years, my mother was a nurse.  She loved her job caring for premature babies and worked with several women who loved it as well.  My mother knew the last thing I wanted was to work in a hospital with sick people (Yuck!).  But well-intentioned co-workers, friends and relatives kept asking me, “You are going to be a nurse when you grow up, just like your Mom, right?”  I cringed every time I heard this line and promised to myself that I would never treat any child like this.

So, I never pushed my daughter toward IT at all.   I encouraged her to take challenging classes in high school and do her best in them.  And I kept telling her that she could take on the hardest classes and do well in them.  And she did do well in all of her classes.

But this summer I got the shock of my life:

“Mom, I am going to take AP Computer Science next year”

Amazing!  Here is my daughter, who four years ago didn’t even want to take a computer class to learn about word processing, now willing to study programming!  Wow!

Now, she still doesn’t know what she wants to do after college.  But I am so glad that she is willing to try different subjects, including those she’s rejected in the past.

So, the moral of the story is this:  You may be having a large influence on someone and not even know it.  And it may not reveal itself until many years later.

A data source is a connection string that is created at the project level.  Once a data source is created all of the packages in that project can use it when creating a package connection through the Connection Manager.  Data sources are optional, you can still create the package connection without it. Data sources are helpful in that they provide a way to change the connection string information in one place and then that change will cascade down to all the packages in a project that reference that Data source.  Unfortunately, data sources come with some drawbacks, listed here in this article by Matthew Roche: Reusing Connections with Data Sources and Configurations.

Package Connections are connection strings saved at the package level.  They can reference a project Data Source or they can hold connection string information independent of the data sources for that project.

So, now that you know the difference between the two, how can you tell them apart?  Easy, just take a look at this screen shot.  The package connection based on the data source has the same icon as the data source shown in the Solution Explorer:

The Internet has all the T-SQL you’ll ever need. Google it!

Now, let me count that again…make sure it is 11 words!

Dynamic Search Conditions in T-SQL – Version for SQL 2005 and Earlier by Erland Sommarskog

Dynamic Search Conditions in T-SQL Version for SQL 2008 (SP1 CU5 and later) by Erland Sommarskog

Sometimes the Simplest Solution Isn’t the Best Solution by Bart Duncan

• SQLShare.com has a nice list of SSIS videos on specific tasks. 
• SSIS Videos (and more!) at MidnightDBA.  You’ll enjoy the humor in these videos just as much as knowledge you’ll gain.  Do a search on the list to find the SSIS Videos.
• List of official Microsoft videos on SSIS.
• The BI VC for  SQLPass has an archive of their past presentations.  Again, you will have to search for the SSIS videos.
• Pragmatic Works holds monthly webinars – some on SSIS.  Once again, search through the list to find what interests you.
• Jamie Thompson (Blog | Twitter) has created several short SSIS videos – SSIS Nuggets.

I’ll add more to the list as I find them. 

So, in no particular order, are my top ten worst indexing practices:

1. Not considering the best column for the clustered index.

Too often, when building a new table, the developer will simply make the primary key the clustered index. But there are many good reasons to make another column(s) the clustered index. For example, if the table has a datetime column and that select queries will be looking for a range of dates, then that column should be part of the clustered index. Additionally, there are other columns you want to avoid putting in your clustered index, such as columns that are constantly updated or columns that are very wide. Since you can create only one clustered index per table, this decision deserves some thought before you decide on which column(s) to set as your clustered index. If you can’t think of a good reason to make another column(s) the clustered index, by all means keep the primary key the clustered index, but make sure you have a clustered index, which leads to the next worst practice…

2. No clustered index

A table without a clustered index is called a heap, which is just an unordered list of data. So, why is this bad? Well, as rows increase in size they are moved to new rows, creating what are called forward pointers. Kalen Delaney explains this very well in her blog post: Fragmentation and Forwarded Records in a Heap. So what about very small tables with static data, such as lookup tables? Well, yes, they don’t really need a clustered index, but I’d rather just put one on every table, so there is no chance of any table having this problem.

3. Incorrect fill factor for the index.

The default fill factor is 0, which means that the leaf page for the index will be full.  If your data is static, this is good, but inserts will cause page splits, which in a database with high write activity can degrade performance.  Choose a fill factor based on minimizing page splits.

4. Forgetting that foreign key constraints do not create indexes by default.

When you create a Foreign Key constraint, SQL Server does not include an index. If you need the index, you must create it. Review all Foreign Keys to determine if they will benefit from an Index.

5. Forgetting to show indexes some love.

Ok, DBA’s this one is for you. After all that coding and testing to find the best indexes, you forget to schedule the proper maintenance for them. Indexes won’t give the best performance if they are allowed to become excessively fragmented. And keep those statistics updated too, will ya! I’ll make a deal with you, Mr(s). DBA, you keep my indexes nice and tidy and I won’t be asking for SA rights on production. Deal?

6. Creating lots of indexes without a real reason why.

Once you see the performance benefits of indexes, it’s easy to fall in love with them and think the more the better. No, this is not the case. Too many indexes can drag down insert, update and delete operations and led to wasted resources and time during maintenance operations. And your DBA will be cursing you…oh yes they will.

7. Low selectivity of an Index.

If there are only a few unique values in a column, a nonclustered index will not be used. So, just how low is too low? Tibor Karaszi explains all in his post How Selective Do We Need to be for an Index to be used?

8. Use of index hints.

Yea, they do work. For a while. For an indeterminate amount of time. And the time they stop working, will be so, so inconvenient. You will be buried in work and users will be hollowing for you to fix it. Immediately. Trust me, I’ve been there, it’s ugly. Please, find some other way to fix your troubled query.

9. Non-sargable where clauses.

If you create a WHERE clause that includes search conditions such as <>, !=, NOT EXISTS, NOT IN, or math operations or functions, it will not use an index. For an deeper explanation, see Sargable Functions in SQL Server by Rob Farley or Chapter 11 in the book SQL Server 2008 Query Performance Tuning Distilled by Grant Fritchey and Sajal Dam.

10. No naming standards for your indexes.

What does “IDX_1”, “Index5, or “IX_Customers” mean? Please, put just a little bit of thought into your index naming. I like to see the table name and column(s) names in the in index name. Then I don’t have to script out the index just to see what’s in it. The next time you are tempted to put a useless name on an index, ask yourself, “Will I remember what columns are in this index 6 months from now?”

Got any other indexing worst practices you would like to share?  Leave ‘em in the comments!

P.S.:  Michael, I am team Indexes!

…or is it?  In a big company, with lots of resources (to include at least one full time DBA, usually with several years of experience), this makes perfect sense.  But for smaller companies, especially when there is a part-time, “accidental” DBA supporting the database environment, this may not be the best way.

Consider this situation:  Users are complaining that the system is slow…it appears that the database is to blame.  (Maybe not, maybe it is the application, but play along with me here, its my story and I say the database is the problem, hehe.) The DBA is an “accidental” DBA who has been a part time DBA for 1 year.  His main job is the company Lead System Administrator.  He can set up backups, do restores, grant security and migrate code to production, but that’s about it.  The Database Developer has over 10 years of full time experience with SQL Server, to include a couple of stints as a DBA, and has plenty of experience in performance tuning.

So, who should troubleshoot this problem?

Unless there is some regulatory, legal, or company policy  reason to keep the DB Developer completely out of production (in which case the company **really** needs a full time DBA!, one with plenty of experience!) , the DB Developer is the better choice.  The “accidental” DBA doesn’t have the knowledge or experience to find the problem.

…ok, so the DB Developer will investigate the problem.  But what permissions do we give him?   Just enough to get the job done, that’s what.  So, lets go over what he might need to troubleshoot a performance issue in one database:

GRANT db_datareader – this is usually the first one that comes to mind.  This permission is set at the database level and allows the user to select from any table in the database.  A good place to start.

GRANT VIEW DEFINITION – This permission is set at the database level and allows the user to view the database metadata such as stored procedures, functions and views.

GRANT VIEW SERVER STATE – This permission will allow the user to run Dynamic Management Views and Functions.  Lots of yummy troubleshooting data in those babies.  This must be run in master database and this permission is server wide.

GRANT ALTER TRACE – If SQL Profiler is needed for troubleshooting, then this permission needs to be set. It also will allow the user to see SHOWPLAN data for an individual query. This permission is also needed for replaying a trace as well.  This must be run in master database and this permission is server wide.

Now these permissions are only available on SQL Server 2005/2008, so if you have SQL 2000, sorry, these permissions don’t exist on that version.

Of course, none of these permissions will give the user any ability to make any permanent changes on the production server, so that job is still left to the DBA.

Each day of the cruise was sponsored by a different company.  And they each gave away a netbook to one lucky cruiser!  Here are the lucky winners:

Monday – Rebecca Mitchell (Blog – Twitter) won the SQL Sentry “Search the Ship Team-Building Event”

Tuesday – Karen Lopez (Blog – Twitter) won the Quest “Through Your Laptop Bag”

Wednesday – Yanni Smith (Blog – Twitter) won the Redgate “Show Us Your Hairy Execution Plans” contest.

Thursday – Erin Stellato (Blog –Twitter) won the MSSQLTips.com “SQL Server Poolside Trivia” contest.

Congrats to these lucky gals!  And yes, all the winners were ladies.  Sorry guys, better luck next time!

The technical sessions included a very informative session by Tim on DMV’s (check out his DMV Starter Pack) and a great T-SQL tuning session by Brent. Brent & Tim put on a outstanding event, thanks for all of your time and hard work!  And thanks to Gareth Swanepoel (Twitter) and his lovely wife Jen (Twitter)for hosting the SQLBBQ Sunday night before the cruise started!

One thing I want to note here – I paid out of my own pocket to go to this.  And took vacation time too.  And it was worth every penny (including the overpriced drinks on the ship!!).

I highly recommend SQL Cruise to my fellow members of the SQL Server community.  If you get a chance to go, do it!

1. Define Success. Before you do anything, decide what the goal is?  A 50% reduction in time needed.  75%?  What??  And once you have met the goal stated for success, stop!  I’m sure you have other tasks on your to-do list.  Nobody in your company is going to care if you make it faster then needed. When the customer/end user is happy, move on.
2. Do no harm.  Whatever you do, don’t make the problem worst then it is.  For example don’t throw a bunch of indexes into your database in an attempt to fix one procedure, without understanding the rest of the queries in your system.  It is better to start off with a review of the code first.  Changing the code will only affect that procedure where as creating or dropping indexes will surely affect other queries on your system.  And please fully test all changes you do make! The last thing you want to do is introduce logical errors into your code.
3. Document your changes.  Oh yes, the dreaded need for documentation.  I know all of the excuses for not documenting a code change…you’re too busy…nobody will read it…nobody will care.  Trust me, the new developer who comes in behind you will be so grateful if you do this.  And if you need to work with the same code again, you’ll be happy if you document your changes, because you will probably forget what you did 6 months later!  So, write down what you changed before you forget!