I work almost exclusively with third party applications. I don't get to do a lot of tuning except for reports and automating stuff. One of the apps at the law firm where I work is our document managment system (DMS). Over the years I have learned that the vendor did a great job designing the database and indexes. If there are any problems with this app, I know something unusual is happening.

We recently have been migrating our offices one at a time to a new workstation image among other changes. One thing that was added was a log off script that included querying the DMS to pull the most recently modified documents locally. Each office has its own database and things had been going well as far as I knew. That is, until our second largest office was migrated. The script was taking forever to run. I did not write the script, but got involved eventually to help figure out what to do. In fact, when they finally asked for my help, I didn't realize at first that the problem was confined to just one office, just one database.

Looking at the execution plan I saw that there was a very expensing Bookmark Lookup accounting for 100% of the cost of the plan. I checked the execution plan of the same query in one of the other databases and couldn't find the Bookmark Lookup at all. The most expensive part of the plan in the other database was a clustered index seek.

Since multiple databases were located on the same instance, I was able to check the execution plan of both queries in one batch. Doing so showed me that the "Query Cost (relative to the batch)" was 99% for the query running against the problmatic database. Yikes, there was definately a problem here.

I decided to use the Databse Engine Tuning Adviser. It recommended adding an additional index to the large activitylog table. I figured that this meant that one of the indexes was missing from the table and that the recommended index was probably in place on the other databases. When comparing the indexes on the table in the two different databases, I found that all the indexes were in place and that the columns were identical. The only difference was that the all of the indexes in the table in the problem database were non-clustered.

Since the index was not clustered, the app or, in this case the query in the script, used the index but had to go back to the large table to get additional columns. Why was this index non-clustered when it should have been clustered? We acquired this database 5 years ago when we merged with another law firm. Maybe someone there decided that clustered indexes were bad. In fact, as I looked at other tables, there were no clustered indexes on any of them.

So now, armed with additional information, I decided the best plan of action was to ignore what the tuning adviser had recommended and to rebuild the existing index as a clustered index. I scheduled an outage for the DMS application that night and fixed the index as well as the indexes in the other tables. It was easy to know which ones needed to be clustered as the vendor included a "C" at the end of each of the clustered index names.

After the index was rebuilt, I compared the estimated execution plan again in a batch with queries pointing to two databases. Now, the query in the problem database was performing better than the query in the database that performed well earlier that day.

There can only be one clustered index on a table and this determines how the table is organized. Often you will see the primary key column as the clustered index, but this is not a hard and fast rule.

This taught me a few things: First query and index tuning is really fun, always make sure the clustered index is in place, and sometimes people do stupid stuff to perfectly fine databases.

We are trying to move to version 2 of our accounting data warehouse (more likely called a datamart), but it just doesn't have everything the department needs yet. So, we still have version 1 running, a system that gets updated just once each month instead of each night. The warehouses were designed by the accounting software vendor, so I haven't had much to do with the warehouses. I just need to get data from our production accounting system over to the warehouse staging database and do other routine DBA tasks. 

Version 1 was set up back in 2003. I was given a list of 43 tables and told where these tables needed to be copied.  The easiest thing to do was to use the CopyObject in DTS.  I also added an extra step to the job to compare the number of rows and aggretates of numeric and date data between the accounting tables and the copied staging tables to make sure the everything copied correctly.  That was fine for four years even though it took four hours to run each month.

We just rececntly upgraded our accounting system to a new version of the software and to 64 bit SQL Server 2005 (woo hoo!).  So, I figured all I had to do was point the CopyObject to the new server and all would be well. Or so I thought. I'm sure I could have found the solution if I had worked harder at it, but the CopyObject on the version 1 warehouse server would not talk to the new accounting database server. 

Since I had scripts in place on other servers to copy data around at night using linked servers, I decided to pop that solution into place instead of trying to figure out why the CopyObject would not work.  I am constantly coming up with new tables that must be copied around and this solution lets me do it by just adding the table name to a table called ImportTables.  As long as I have a linked server set up and there are select permissions on the source table, all works well. The new solution copied the 43 tables in just over an hour, 25% of the original time, and all the data checks were correct! To tell the truth, I was expecting worse performance not better. Could the improvement have been because the new source server was a much beefier box? I'm not sure.

When the original CopyObject DTS package was put into place, I hadn't written my linked server solution yet. In fact, I inherited dozens of DTS packages that only copied tables from one SQL Server to another, many copying just one table in a package.  I found that this was a nightmare to maintain, and I came up with the linked server idea.

I don't recommend that you scrap DTS or SSIS for linked servers. I'm just saying that you just need to experiment once in a while to see what will work best. 

When I first got into IT full time 10 years ago, I noticed that my mind was constantly solving programming problems or thinking of programs I could write. If I had someone else to talk to at the time, I was a normal human, or at least as normal as I can be. As soon as I was walking the dog or driving my car alone, my mind usually went into programming mode.

I guess my brain was telling me that I was definately working in the right field. I also loved the fact that I could go to sleep at night with a seeminly unsolveable problem and awaken the next morning knowing the solution. I have heard many other programmers say the same.

As I have moved from the developer role to the DBA role, my job involves less programming. I love learning about and working with SQL Server, but I don't have as much of an obsession with it as I had with VB back in the day.

Two years ago I was working on the SSIS book. I only had two chapters to write, but those chapters had to become my priority outside of my job. One weekend during the process, I worked on an article on parameters for SSC. I probably did need a break from the book, but I felt a bit guilty spending the time on an article that I could write when I had more time.

All I can say is that article was in my brain and was screaming to get out. It ended up being the most successful article I have ever written and recently made SSC's most popular list. I can't say that the article is perfect, but I hope it has helped some programmers learn to pass parameters and start using procs instead of dynamic SQL.

So now I have a different obsession: writing. While driving or walking, I think of articles I could write and stories I want to tell. I believe the weekend that the parameter article was demanding to be written, I became a writer.

This was my 5th year at PASS and every year I enjoy catching up with old friends and meeting many more.

SUNDAY

I arrived safely and was in my hotel room by about 2 pm. I am the Program Committee Manager and the board member I report to, Bill Graziano, was ready to meet with me about the speaker orientation.

Later that evening, my best friend at PASS, Tom LaRock, arrived. We walked around trying to find a good place to have dinner but downtown was really packed and it took awhile to find someplace that didn't have a 40 minute wait. It was such a beautiful evening that walking around was very enjoyable. Once we made it to a bar and grill with no wait, Tim Ford called. He decided to join us at the bar. Tim was recently published on SimpleTalk: Alpacas. Congratulations, Tim!

After dinner we headed back to the hotel bar. Now this is where I start to lose track of who I met and when. No, I wasn't drinking, there were just so many people I met, chatted with at lunch or hung out with during the evening events.

Here is a list of a few of the people I ran into at PASS in no particular order. I apologize for the many people that I chatted with but don't remember their names:

Bill Graziano, Tom LaRock, Tim Ford, Sheila Acker, Chuck Heinzelman, Rick Heidges, Todd Robinson, Peter Ward and his wife, Alex Ji, Andy Warren, Tony Davis, Bryan Oliver, Grant Fritchey, Ben Debow, Ayad Shammout, Peter DeBetta, Roman Rehak, Jean-Rene Roy, Allen White, Andy Leonard, Eric Veerman,Chris Shaw, Wayne Snyder, Melissa Demcsak, Allen Kinsel, Josh Crosby, Pat Wright, Neil Watkins, Brian Guthals, Kevin Kline, Denny Cherry, Nancy Hidy Wilson, Erin Welker, Kalen Delaney, Mike Ross, Gene Hardy, Julie Bloomquist, Dan McClain, Tim Martin, Denise McInerney, Karaoke Ed, Lance Harra, Mark DeWaard, Greg Low, Lynda Rab, Christoph Stotz, John Allmon, Tjay Belt, Sujata Mehta, Louis Davidson, Natasha Kentish, Donna Shaver, Joe Yung,Steve Jones, Anvesh Gupta, Owen from Houston,Andreas (?? not sure of name, he came to the Program Committee meeting), a gentleman from Denmark, a new MVP from Canada who is also a BNL fan, Warren (??) from Microsoft, Joyce at the WIT Luncheon, Anna and others I missed who went out for Karaoke with us.

MONDAY

After a long day at the pre-con a bunch of had dinner at the Hyatt. Tom has a lifelong friend named Spencer who works at a bar a couple of miles from the convention. Spencer said that the bar would have Karaoke that night. Once we got to the bar a band was playing. We ended up going to the bar next door and never got to sing. I was sad about that, but it was really nice that Tom was able to see his friend. Of course it was fun hanging out with the group even if no Karaoke was involved.

TUESDAY

As Program Committee Manager, the last responsibility I had all week was the Speaker Orientation. Evidently, the best part of the meeting was when someone asked about the prizes for filling out evals, and I couldn't remember the name Zune. I accidently said "Microsoft iPod". After the meeting, I realized that most of the top SQL Server people in the world were sitting in the audience. Luckily, I didn't think about this before so I wasn't a bit nervous during the talk and had a blast doing it.

Tuesday night of the conference begins with the Welcome Reception. Tim Ford did a fantastic job organizing and hosting the SIG Bowl. Also at the Welcome Reception, the 2007 PASSion Award was announced. Denise McInerney won the award. Congratulations, Denise!

After the Welcome Reception, anyone lucky enough to have included the "SSC" code when registering went to the SQL Server Central party. I'm not into gambling, but I would not miss this party for anything. Once again, Steve put on a great event with lots of food, prizes and fun.

After the SSC party a bunch of us headed to Karaoke at Bender's on 13th street. I'm not going to a lot of detail here except to mention the highlight of the evening when everyone sang "Bohemian Rapsody". Other details about the outing can be found on my personal blog on www.myspace.com/sqlgoddess (apologies to Kim Tripp, the original and more appropriate SQL Goddess).

WEDNESDAY

Wednesday night was the vendor reception followed by the Volunteer Appreciation party at ESPN Zone. There was food, drinks, games and meeting more new people.

THURSDAY

The Women In Technology Luncheon was held on Thursday. Kudos to Denise McInerney for making the luncheon bigger and better every year.

Thursday evening I attended the very crowded but also very nice Quest party. After that party some of us headed back to Bender's for more fun and Karaoke. I had invited many people to join us. Luckily not all of them showed up because the place was extremely crowded.

FRIDAY

This is always a very difficult day. I had to say goodbye to so many people whom I will not see again until Seattle.

So why do I know so many people at PASS? It is because I am a volunteer. Once you attend the conference as a volunteer, it is an entirely different conference. So think about becoming a volunteer if you have some time to spare.

Somehow between the sessions, networking and socializing I never found time to post anything last week. So, I'm going to post two summaries, one for what I learned, one about the social stuff.

MONDAY

I attended Itzik Ben-Gan's all day pre-con session. He covered a lot of material and it really helped that I recently read his "Inside T-SQL Server 2005 T-SQL Programming" book. Otherwise I may have been really lost.

My favorite part of the day was the time he spent going over 2008 features. I am most interested in the new Merge statement that lets you insert new rows and update existing rows in one statement.

TUESDAY

On Tuesday I attended the volunteer training day. Greg Low gave a presentation on building a sense of community at the local user groups and gave each of us a copy of his new book on the same subject. Unfortunately, I was getting paged from work and didn't get to hear everything he had to say.

We heard reports from several board members and found that the move to the new managment company, though painful, had been the right decision.

Finally, we broke into groups for brainstorming sessions about how to solve the issues that are facing PASS.

WEDNESDAY

The best part of Wednesday was the general session where some of the Katmai features were demonstrated. I am especially interested in the new policy functionality. It looks really flexible and I think it will really help out DBAs who administer many servers.

I was hoping to attend Itzik's session at the end of the day titled "What's Between Index Internals, Isolation Levels, Locking and the Consistency of Reads", but work was calling again. Luckily, all the sessions will be available online in a month or so and I'll be able to view it.

THURSDAY

I attended three excellent sessions on Thursday. A session on MOM by Tom LaRock, one on DMVs by Rick Heidges and one on system engingeering by Peter Ward.

Tom's session was the most valuable to me because we have MOM at Brian Cave, but so far I do not have access to it. The server team that is responsible for it will set up alerts to page me when bad things happen, but haven't given me rights to view anything using the MOM console. Now that I know what can be accomplished with it, maybe I have a better shot at getting the tool. The ironic thing is that MOM broke recently and they came to me to fix it.

FRIDAY

I attended a really cool Q&A session Friday afternoon with Ken Henderson. I read one of his books when I first became a DBA so I credit him with some of my success. This session was more about Ken than T-SQL. I was just amazed at what a humble guy he is. He doesn't consider himself a guru but more of a seeker of knowledge. He also gave some good advice for people wanting to get into writing and how quality is so much more important than quantity.

SUMMARY

Once again I left PASS armed with knowledge that will help me on Monday back at work as well as glimpse into what I can expect in the future.