Wise man or Wise guy? You decide

PASS Summit 2009 – Wrap-up – What’s the Value?

2009-11-09T09:14:00.000-05:00

I’ve already written four posts (1, 2, 3, 4) that discussed what I did at the Summit, including what I learned, now I want to discuss my thoughts on the event.

This being my first PASS Summit, I really wasn’t sure what to expect. I had spoken with several people who have attended in the past, but I don’t think they could really explain the experience.

It’s All in the Family

As I mentioned in another post, the Summit has been described as “A family reunion where you like everyone.” This really sums it up well. I did not expect the community to be so much like a family. Regular attendees would run up and hug each other (men would usually shake hands). The excitement everyone had to see people again was unbelievable! Then the way everyone was welcomed into the ~~community~~ family was great!. By the end of the week it felt like I knew more people than I didn’t know.

I went with a list of about 50 people I wanted to meet, and in every case they were happy to meet me and have a discussion with me. The only people on my list that I did not meet were either sick (Lynda Rab, hope you feel better) or did not attend, and I met more than enough other people to make up for the ones who weren’t there. It also didn’t matter if they were the SQL elite, like Paul Randal, or the relatively unknown like me, everyone was excited to meet you and help you if they could.

The Learning Never Stops

Tuesday through Thursday the day started at about 7am and ran until midnight (later for the karaoke folks), and there was always something to learn at any hour. From the breakfast discussions to sessions to late night talks, I felt like I was bettering myself and hopefully having the same affect on the people I was with. I felt like I was always with someone smarter than me and that made me feel smarter.

The sessions helped me realize how much I actually know about SQL Server. There were definitely things I learned, but much was familiar to me, which I did not expect.

Meeting Other SQL Server Professionals of Like Faith

If you visit the New Tribes Mission website, you can see what I believe and that I try to live what I believe. I’m not pushy about my faith, but I don’t hide it either. It was great to meet several other people who share my faith. This was something that made the Summit even more special for me. In the past I have thought that I was in very small minority, but this event helped me see I’m not alone, but I have friends in the community that I can lean on when it comes to faith. See Mike Walsh’s and K. Brian Kelley’s blogs as well.

The One Bad Thing about PASS

It ends before you can meet everyone! I’m already looking forward to next year. If I can attend I plan on doing things differently. My changes are:

Attend sessions about areas I normally do not work in. This probably means more BI.
Do more to make other attendees feel welcome. I will seek out people sitting or standing alone and speak with them. This year was about me meeting the big wigs of SQL Server, SQL Server Twitterati, and SQLServerCentral friends, next year will be about others

I can’t recommend the Summit enough. It is refreshing (even though you are worn out) and inspiring.

Thanks to all those who made it such a great event, the PASS Board, the PASS HQ Staff, volunteers, speakers, and attendees.

PASS Summit Day 3 – Brain on Overload

2009-11-07T12:23:00.001-05:00

After the first 2 official days of the Summit the brain is already on overload and then the second part of the keynote by Dr. Dewitt and 45 minutes of Itzik Ben-Gan’s tips and tricks totally fried my brain.

I arrived and sat with Bob Hovious with the plan to connect to the wireless and publish a couple of blog posts I had written the Wednesday night but couldn’t publish because I wasn’t about to pay for internet in the $200/night hotel room. Unfortunately I wasn’t able to publish the posts because when I turned on the laptop I got an error, No boot device found. I was totally ticked off, more because of the time I had put in on the blog posts than because of the hardware issue. Fortunately, after lunch when I went to the hotel to print my boarding pass and check to see if I was still under warranty, it booted up, and has worked since. I am planning on getting an image done at the office on Monday.

Keynote

The day started off with recognition of departing board members: Pat Wright (@SQLAsylum), Greg Low, and Kevin Kline (@kekline). It was a tearful goodbye by PASS president Wayne Snider to Kevin as Kevin has served on the board since PASS’s inception. Kevin has been a tireless worker for PASS and deserved all the recognition and the standing ovation given to him.

Following the recognition of departing members was a presentation by DELL. Unfortunately it was not the best presentation of the week, to put it mildly.

Then, we were taught by Dr. DeWitt. He talked about the changes in CPU, memory, and storage since 1980 and how while there have been improvements in each the real improvement especially relative to speed vs. size has been in CPU so I/O is the real bottleneck. One way to offset this, especially in data warehouse applications, is through the development of columnar data stores vs. the traditional row-based database. Columnar data stores narrow the data so that there are fewer I/O operations required. To be honest much of this was over my head, but definitely worth learning more about.

T-SQL Tips and Tricks – Itzik Ben-Gan

I was only able to attend about half of this session because I had to be at the Chapter Leader lunch, but what I heard while in there was mind-blowing. I was sitting in the back with Kathi Kellenberger (@auntkathi) and Tom LaRock (@SQLRockstar) and at one point Tom said, “Where does he come up with this? He’s like a mad scientist”, that may be somewhat paraphrased, but the meaning is there. The 2 new tips for me in that time were:

Using (select null) in the Order By for windowing functions as a constant which eliminates a sort. Select RowNumber() Over(Order By (select null)).
ON clause ordering eliminating the need for multiple LEFT JOIN’s.

customers C LEFT JOIN (sales S JOIN products P ON S.product_id = P.product_id) ON C.customer_id = S.customer_id

Just those 2 tips will help me write better performing and more readable code.

Lunch with Chapter Leaders

As co-president of OPASS, I was tasked to sit at our table at lunch. The idea, I think, was to let people who are not attending chapter meetings where the chapters are located and to meet people involved in the chapters. There are a lot of chapters in Florida, but it didn’t seem like there were a lot of chapter members at the Summit other than the chapter leaders. I only had Kendal Van Dyke (@SQLDba) from the Orlando area sit with me, while Scott Gleason from Jacksonville also joined us because the Jacksonville chapter hadn’t gotten the right paperwork in.

This is definitely a good idea as was the MVP Birds of a Feather lunch.

Proactive DBA: Manage SQL Server Better – Ross Laforte

This session had to do with finding all your SQL Server instances using the MAP tool and using PBM to manage your servers. Another session that had content that was familiar to me. Lots of good information shared, but not necessarily new to me.

Advanced SQL Server 2008 Extended Events: Performance Profiling and Troubleshooting Techniques – Adam Machanic

This was a FANTASTIC session. There was a ton of information, great demos, and humor. Adam is one the best presenters I have seen. I have not done anything with Extended Events as I am running SQL Server 2005, but this session inspired me to try to learn more. Extended Events are powerful, especially when you use Causality. Causality can help you track down what caused the condition that CAUSED the event to happen. I think Extended Events are designed to eventually replace SQL Trace and certainly give you much more flexibility. While I’m not Brad McGehee, I do consider myself to be an expert in Profiler, but I’m looking forward to being able to use Extended Events.

Post-Event

Since I’m not currently a Friend of Redgate, I didn’t have a party to go to, but did have a great dinner with Bob Hovious, a friend from SQLServerCentral. After dinner we went back to the Sheraton Lobby lounge to continue our discussion. We saw a bunch of people and Robert Cain (@arcanecode) and Paul Waters sat down with us for awhile. These conversations are the highlight of each day.

Friday – Heading Home

Had breakfast with my roommate, Tim Mitchell (@tim_mitchell) and saw some of the board a the restaurant with Bill Graziano (@billgraziano) stopping at our table for a short chat. Then it was off to the airport where I met up with Pam Shaw, so for the first hour the Summit continued. I had good flights all the way to Orlando and, while it is great to be home with family, it’s a downer to be away from the SQL family. I’m already excited for next year’s Summit. Fortunately I can make a couple of SQLSaturday’s between now and then to get a small fix of Summit and SQL goodness.

PASS Summit Day 2 – What More Can I Learn

2009-11-05T20:15:00.000-05:00

I actually slept in on Wednesday until 6am. This was a change from being wide awake at 4am. I made my way over to the convention center and was ~~roped into~~ invited to an informal meeting of bloggers with Andy Warren (@sqlandy) and a member of the PASS marketing team. It was a discussion of how PASS and SQL Server bloggers can work together to get the message of PASS out to the community. It was more of a brain storming session than a meeting.

When the meeting closed I spent some time with Gail Shaw and Bob Hovious before the opening remarks and keynote. PASS recognized some of the key volunteers, Tim Ford (@sqlagentman), Grant Fritchey (@gfritchey), and a few others. Then it was time for the PASSion awards. The international PASSion award went to Charlie Hanania while the U.S. award when to Allen Kinsel (@sqlinsaneo), both of whom did yeoman's work for PASS and certainly earned their awards. After the awards were announced I took a break and worked on blogging my experiences.

Paul Randal – Logging and Recovery

This was my first session of the day. Because I read Paul’s blog I did know a lot of the material, but he definitely expanded and explained more. One of his main points is that the Transaction Log is the most important component of the database as ALL changes must be written to the transaction log BEFORE they can be written to the data files. Some takeaways were:

Each data page has the last LSN (Log Sequence Number) stamped on the page for recovery purposes.
While there are minimally logged operations there are NO non-logged operations.
You can enable trace flag 3502 to see checkpoint operations.
A checkpoint writes ALL dirty pages from memory to the log file, even pages that are part of an uncommitted transaction.
The log always reserves enough space to rollback open transactions.
The log cannot be “cleared” (have virtual log files marked as available) while a Full or Differential backup is occurring, even if there is a simultaneous log backup taken.
Only log backups “clear” the transaction log, Full and Differential do not.

Very interesting material on the details of the transaction log and how it is used in recovery.

Lunch

I enjoyed lunch with some newly made Christian friends again, Mike Walsh, Brain Moran, Peter Schott, and Erik Veerman. Always good to spend time with men of faith.

Louis Davidson – Database Design

Louis has an interesting presenting style where he uses a lot of humor to make his point. His main premise is that taking the time to have a well designed database at the start, saves much time later in the process.

Gail Shaw – Insight into Indexes

Gail’s session was about how to find out information about your indexes using the Dynamic Management Views and Functions. I did know a bit about this, but I did not know about sys.dm_index_operational_stats which can help you see what is happening to indexes (inserts, updates, deletes) and how they are affecting locking, i.e. the number of locks taken on the resource by type (page, row). Page Latch (waiting for access to page in memory) and Page IO Latch (waiting for pages to be written to disk or read from disk) wait information is also available in this DMV. Gail did a great job using a book to demonstrate how indexes work, a very good visual example.

Panel Discussion with the PASS Board of Directors

For the first time the BoD made themselves available for a question and answer session, moderated by former board member Joe Webb. This was a late addition to the schedule, which contributed to the light turnout, about 15-20 people, not counting the board members. One of the key points to come out is that there needs to be better communication between the board and other parties (members, chapter leaders, and sponsors/partners). Chapter leaders would like more support from PASS and PASS would like more information from the chapter leaders.

Steve Jones (@way0utwest) asked for more transparency and publication of the goals and accomplishments for each board member. His point is that, to be honest, the community really doesn’t know what they are doing/have done, and they should be publishing their accomplishments.

Another interesting issue, raised by Jessica Moss, was that we, the community, need to know who to contact and how to go about presenting ideas we have for the organization. Kevin Kline (past president) answered that the board was working on a “process” for this. Jessica followed up by asking “When will this process be available?” and, unfortunately, there was not a concrete answer given, probably the low point of the session.

Lastly I asked about the recent BoD election controversy and if that caused them to consider changing the election process. There was clear, “Yes”, and that there may need to be changes made to the by-laws to make the election process better. I also commented that I’d like to see the BoD aim higher for the number of voters than the 1100 or so they are looking for next year. Granted that would be double the votes cast this year, but still a small percentage of membership (30000). I took some grief from Andy Warren as I would not/could not provide a realistic number to shoot for for next year’s election. I’ll be thinking about this later.

It was, I think, a positive experience for both the BoD and those from the community who attended and I applaud the efforts being made by the BoD to be more transparent and make themselves more easily available to the membership. As a relative newcomer to PASS, I can’t speak for how available they have been in the past, but I do believe that they are doing the best they can.

I certainly haven’t covered everything that was discussed, but I covered what I remembered.

I also want to thank the board for making themselves available for this session, it was definitely a step in the right direction. I also want to thank Bill Graziano (@billgraziano) for being willing to hang around a while longer to continue the discussion.

Please take the time to email or talk to the board members and bring them your ideas and goals for PASS. I believe that they truly want to serve the needs of the community, but without feedback they can’t know if they are focusing on the areas we, as a community, are most concerned about.

In the evening

I went over to the party provided by Microsoft at Gameworks, the biggest arcade/game room, I have ever been in. There was good food and unlimited game play made available. I grabbed some food and visited with some of my new friends while there and then went out with old friends before heading in for an early evening (10:00pm).

Trying to save up energy for my last day at the Summit where I will get to “host” the OPASS table at the Chapter Leader lunch.

Let the Learning Begin

2009-11-05T17:11:00.001-05:00

Finally time for the sessions to begin after a day and a half in Seattle being overwhelmed by the people that I’ve had the opportunity to meet.

I started the day meeting with Mike Walsh (@mike_walsh) and Joe Webb (@joewebb) in the Sheraton living area (lobby) where we had a short time of prayer to start the day. We then went on to meet with some other men of like faith to discuss Brian Moran’s idea about reaching others. It was great to meet Brian and encouraging to know others in the SQL Server community that share my faith. Then it was on to the keynote.

Keynote

I attended the keynote by Bob Muglia and Ted Kummert from Microsoft. Mostly stuff about the new technologies available in SQL Server 2008 R2, Windows 2008 R2, and Visual Studio 2010/.NET 4. The coolest thing was the LIVE migration of VM’s using Hyper-V. Move a running VM from one VM server to another without interrupting the virtual server! Wow!

Session 1

My first session was titled Data Access Layers: A road map to smarter, efficient, and effective queries. It was not exactly what I anticipated as it was a session mainly about using Inline Table Valued functions to replace views and direct table access. I’m not a big fan of UDF’s because they are often mis-used and have to admit I tuned out a little bit and spent time following Twitter so I’d know where the people I wanted to meet were.

Lunch

Today’s lunch was a Birds of a Feather lunch with MVP’s hosting topic-focused tables. I went to Paul Randal’s (@PaulRandal) table about Corruption and High Availability. It was very interesting to hear him tell stories and hear the stories of other folks at the table. It was also great that he was able to give me reason why a restored database with no activity would have transaction log growth. It was AutoShrink! AutoShrink is evil in more ways than one. It was also cool that when I introduced myself, after everyone else had left because I was late to lunch, he recognized my name immediately from the SQLServerCentral forums and Twitter and asked why I hadn’t introduced myself earlier! I really try not to put people on a pedestal, but he and Kimberly Tripp (his wife) are pretty much universally recognized as SQL Server royalty, so it’s definitely cool to meet them and then be recognized as well! Both are very nice and desire to help people out. That’s the great thing about the SQL Server community, it seems that the more well-known you are, the more helpful you are.

After Lunch

Because of my discussion with Paul, Kim, and Gail Shaw I didn’t make the first session after lunch so I spent some time in the vendor exhibit hall looking around and talking with some of the vendors.

Next I went to Andy Leonard’s session, A Tale of Careers and User Groups, where Andy explained how he became involved in user groups and the lessons he has learned. He gave some good tips about how to get people involved in a user group. A key way is to ~~recruit~~ encourage those who come early or stay late to be a part of the user group leadership team.

Finally I checked into Kalen Delaney’s session, Indexing Internals. It was a good session on the structure of indexes. The interesting thing was that, having read Kalen’s book and some other resources on indexes, I actually knew the majority of the information. It was interesting to see what I actually know.

After the session I had a great discussion with Lorie Edwards (@lorieedwards) about what to learn in SQL Server and how the size of your environment definitely affects the features you need.

Dinner

I went out to dinner with Andy Warren, Steve Jones, Pam Shaw, Rob, Tim Mitchell and Kendal Van Dyke. We didn’t talk much SQL Server, but more general conversation. Always a good time with this crew.

After dinner I went to a party sponsored by SQL Sentry and met a ton of new people. The guys from SQL Sentry (Greg, Peter Shire), Adam Machanic, Peter Ward, Charlie Hanania, Chuck Heinzelman, and others.

Finally I went back to the hotel about midnight and crashed.

My First PASS Experiences

2009-11-03T12:06:00.001-05:00

Getting to Seattle

I started my travels to the PASS Summit at about 9:30 am (EST) on Sunday and arrived at the Sheraton in Seattle at about 6:30 pm (PST) Sunday. It was an uneventful trip on Southwest from Orlando to Albuquerque, then on to Seattle. On the second leg I met up with Tim Mitchell (@tim_mitchell) which made that leg seem much shorter as we had great conversations about PASS, SQL Server, professional development, and family life. I’m sure we drove the 3rd person in our row crazy as we talked the entire 3 hour flight. When we arrived at SeaTac we met up with Wendy Pastrick (@wendy_dance) and Damon Ripper (@DamonRipper) and shared a town car to the Sheraton.

Sunday Night

Amazingly enough a lot of attendees made it on Sunday so there were some good networking opportunities. Tim and I had originally planned to try to get together with Andy Warren (@sqlandy) who was having dinner with “Small Talk” expert, Don Gabor. Well, since we had met up with Wendy and Damon and then met with a fairly large group of other attendees in the Sheraton lounge we decided to just hang with this crew. Amazingly, I was able to meet several people on my list during the evening. Some of the folks I was able to meet were:

Bill Graziano (@billgraziano) – who organized dinner for about 20 of us.
Jeremiah Peschka (@peschkaj)
Grant Fritchey (@gfritchey)
Wendy Pastrick (@wendy_dance)
Allen Kinsel (@sqlinsaneo)

Then there all the people who were not on my list including:

Louis Davidson (@DrSQL)
Rich Baumet
Scott Stauffer (@SQLSocialite)
Todd McDermid (@Todd_McDermid)
Todd Robinson (@DevSQL)
Chris Massey (@camassey)
Andrew Clarke
Denny Cherry (@mrdenny)

And the people I already knew like Jessica Moss, Andy Leonard (@AndyLeonard), and Pam Shaw. The only downside was that I stayed up until after midnight, but my body decided that it was still on the East coast and woke up at 4 am and refused to go back to sleep.

Monday

Started off the morning with a nice walk with my roommate Tim Mitchell to Top Pot Donuts to meet with Andy Warren and Don Gabor. While there Robert Cain (@arcanecode), on my list, and Greg Larsen, popped in to talk with us, well, they know Andy, so they stopped to talk with him, but, of course, it was great opportunity to meet them.

After that I headed over to the convention center to check into the event and on the way I ran into Andy Leonard and Jim (from the Northern Virginia User Group) and stopped to talk for bit. At registration I also saw Buck Woody (@BuckWoody) again and was able to introduce him to Tim Mitchell. By then it was time for the Chapter Leader meeting, I wasn’t officially on the list, but as co-President of OPASS with Andy Warren I attended. It was nice meeting where the chapter leaders got an opportunity to discuss what PASS can/should do to help chapters. The meeting was led by director Greg Low (another person on my list that I met), he is currently in charge of chapters, assisted by Blythe Morrow (@blythemorrow) who is the community coordinator for PASS. We discussed:

how PASS can better integrate chapter meetings and events into the PASS event calendar.
better list and messaging management for PASS-hosted chapter sites.
re-working the responsibilities of the regional mentors
holding chapters accountable for meeting and growing
PASS providing a speaker bureau
the use and viability of Live meeting as provided by PASS
training for chapter leaders, especially on using Live meeting and the DotNetNuke chapter sites hosted by PASS.
If PASS should consider hosting the Summit in locations other than Seattle.

The best part is that most of the items discussed were items I broached about PASS helping local chapters in my blog post, What Should PASS Strive to Be?. I think it showed that PASS is trying to become more relevant at the Chapter level.

I then went out to see who else was around and met Gail Shaw, who had the quote of the day, “It’s like a family reunion where you want to see everyone.”. That might be a bit of a paraphrase, but the meaning was there. I also met Bob Hovious, Brent Ozar (@BrentO), and ran into Kevin Kline (@kekline) and Steve Jones (@way0utwest). I went to lunch with Steve, always a good time. We ended up at the Fox Sport Grill where I got a very good burger. It’s always good to sit and talk with Steve. We spend more time discussing family and life in general than technical stuff, which is a nice change.

Let the Networking Begin

After lunch I attended a networking session for PASS volunteers with Don Gabor. It was a great session about starting conversations and leaving a good impression. It was fun with lots of exercise to help us apply the concepts. The biggest thing I got out of it was how to remember names. He shared his 5-second plan for remembering names, with, in my opinion, the key point being to concentrate on the other person’s name during the introduction instead of thinking about the next thing you are going to say. During this session I met many people, whose last names I did not get, but there were several people on my list in the session that I did meet and, because they are on my list, I do remember their full names:

Michelle Ufford (@sqlfool)
Tim Ford (@sqlagentman)
TJay Bely (@tjaybelt)
Pat Wright (@SqlAsylum)
Tom LaRock (@SQLRockstar)
Jason Strate (@StrateSQL)

Following the volunteer networking session there was longer, Networking for Business Contacts session with Don, that covered the content from the first session, but also included more information about body language (Smile, Open arms, Forward lean, Touch, Eye contact, Nod).

Opening Reception and SSC Party

From networking to the opening reception where some prizes were given out including the Log Reader Awards for blogging (I didn’t win any). Then three 2-person teams competed in the Quiz Bowl put together by Tim Ford and Louis Davidson. It was done Jeopardy-style with all questions having some kind of relationship to SQL Server. The three teams were:

Paul Randal and Kimberly Tripp – who, not surprisingly won.
Brent Ozar and Kevin Kline – they really knew their pop culture.
Grant Fritchey and Joe Webb

I tried to practice my newly learned networking skills at the reception, especially in one case where I saw someone sitting alone, so I went over and introduced myself to Phil, but I think I failed as I took a quick break to grab some food and one I came back, Phil was gone! Well Don, I think I need more practice. I also met Arlene Gray (@whimSQL).

From there is was on to the SQLServerCentral party where I met Kevin Boles and Wes Brown and saw many other old friends. I played some Texas Hold’em and managed to win more than I lost, but not enough to win a prize. There was no cash involved as you were given a ticket for chips and then played until either time or your chips ran out. I was also able to meet Marc Beacom (@marcbeacom) and re-connect with Tony Davis and Brad McGehee (@bradmcgehee)

After the Party

Many people went out to karaoke later in the evening, but I stayed late at the SSC party, so I was left behind, and to be honest, I was ready for a break and an earlier night.

The amazing thing is that my head is already spinning after meeting so many great people (many of whom I have not named because I only remember first names). I can’t imagine what it will be like when the sessions start and everyone arrives.

Also looking forward to getting together with people of like-faith on Tuesday.

Follow Up to Maintaining Security and Performance Using Stored Procedures Part I – Using Execute As

2009-11-02T08:01:00.001-05:00

This is a follow up to Maintaining Security and Performance using Stored Procedures Part I – Using EXECUTE AS because of a comment on that post on the SQLServerCentral syndicated feed by Ken Lee. He commented:

Like the technique, not the example. ISNULL() is a function, it forces the where clause to look at every record in the table.
SQL is smart enough to identify constants and know the variable name is true or not and will or will not evaluate the second OR statement used below.
First SET @LastName=@LastName+N'%', if it's null it remains null. To make sure it always works declare a nvarchar(51) field and assign it instead. If the field in the table is max 50 characters then this isn't needed.
Replace the function in the first example with "(@LastName IS NULL OR LastName Like @LastName) And" logic and you should get the performance without the dynamic SQL.
Kind of curious about the stats.

Since the purpose of that post was to show how to get better performance using Dynamic SQL without compromising security, I decided I needed to do some testing on his method. Here are the queries I ran (FREEPROCCACHE was run because the Dynamic SQL query plans were not removed from the cache when the stored procedure was altered):

DBCC FREEPROCCACHE();
GO

Exec dbo.FindPhoneByName @FirstName = 'J', @LastName = 'A';

GO

Exec dbo.FindPhoneByName @FirstName = 'J';

GO

Exec dbo.FindPhoneByName @LastName = 'A';

Go

I don’t know why, but his method DOES generate a different execution plan than my method using ISNULL() when you leave the Person.Contact in, what I believe is, the original state. In the original state the optimizer chooses to do a scan of the index, IX_Contact_MiddleName, and bookmark lookup on the clustered index. Here’s the index definition:

CREATE NONCLUSTERED INDEX [IX_Contact_MiddleName] ON [Person].[Contact] 
(
[MiddleName] ASC
)
INCLUDE ( [FirstName],
[LastName])

While the optimizer chose a clustered index scan for Ken’s query. This access path required more reads when both parameters were provided, but fewer when only one was provided.

The Dynamic SQL, however, provided the best of both, using the index when both parameters were provided and using the clustered index when only one parameter was provided.

Intrigued I decided to see what happened if a covering index was made available, so I created this index:

CREATE NONCLUSTERED INDEX [IX_Contact_LastName_FirstName] ON [Person].[Contact] 
(
[LastName] ASC,
[FirstName] ASC,
[MiddleName] ASC
)
INCLUDE ( [Title],
[Suffix],
[Phone])

With a covering index in place both non-dynamic solutions produced the same execution plan, scans of the newly created covering index.  The Dynamic SQL though had seeks on the covering index for queries 1 and 3, and a scan for query 2.

Lastly I decided to see what happened when there was a non-covering index available, here’s that index:

CREATE NONCLUSTERED INDEX [IX_Contact_LastName_FirstName] ON [Person].[Contact] 
(
[LastName] ASC,
[FirstName] ASC,
[MiddleName] ASC
)

This case was similar to the first test, my solution did a scan on the index with a bookmark lookup, and Ken’s solution did a clustered index scan. The dynamic SQL did an index seek/bookmark lookup when both parameters were provided and a clustered index scan when only one parameter was provided.

Here’s the results from statistics IO:

		IsNull() Solution		Ken’s Solution		Dynamic
		Scans	Reads	Scans	Reads	Scans	Reads
Query 1	Original	1	593	1	1116	1	593
	Covered	1	201	1	201	1	13
	Uncovered	1	457	1	1116	1	359
Query 2	Original	1	7783	1	1116	1	1116
	Covered	1	201	1	201	1	201
	Uncovered	1	7647	1	1116	1	1116
Query 3	Original	1	3031	1	1116	1	1116
	Covered	1	201	1	201	1	13
	Uncovered	1	2895	1	1116	1	1116

Conclusion

As you can see from the results above Ken’s solution does provide more consistent results than my original solution, while the Dynamic SQL still provides the best performance. I should note that there is a tradeoff with the dynamic SQL solution, you get better plans because you get a plan for each option that is run, so depending on the number of parameter combinations you could get procedure cache bloat. In most 64-bit implementations this shouldn’t be an issue, but you may see more compiles with a dynamic solution.

As always test your options, monitor, and change as needed to keep your systems running as well possible.

All the files (except the AdventureWorks database, on CodePlex) can be found here. Included are the queries, the results from statistics IO, and a trace file showing the activity and query plans.

What Should PASS Strive To Be?

2009-10-29T08:30:00.000-04:00

Steve Jones had a good editorial earlier this week entitled, What Do We Want from PASS?. It didn’t generate as much discussion as I would have thought, but the discussion is worth reading. A couple of the posts mention how PASS does not seem to do anything to make the daily life of SQL Server professionals better. I have to agree with that sentiment. Until about two years ago I wasn’t even aware PASS existed. I had heard about the Summit in passing, but never even considered attending. Unfortunately I think this is a common occurrence with PASS. What can be done to change this? Here are some things I think PASS should be doing:

Make sure people refer to the conference as the PASS Summit not PASS. If you follow SQL folks on Twitter or many blogs you see people saying “See you at PASS” or “Are you going to PASS?”. PASS needs these people, the influencers, to refer to the PASS Summit so that others understand that PASS is more than the Summit.
PASS should be THE voice for SQL Server Professionals. When there is an issue that affects our profession, PASS should be speaking for us. Regulations, best practices, salary information should all be found at PASS.
PASS should build from the bottom up. If PASS really wants to be relevant it needs strong local Chapters. This is where the majority of the interaction between professionals will always be, make it a priority. Here are some ideas on how it can be done:
1. Speaker list
2. Sponsor contacts
3. Training for Chapter Leaders on how to interact with sponsors and how to market the group.
4. How to be a Technical Speaker seminars at a local/regional level
5. Regional events. SQLSaturday is filling this void, and I’m a big fan of SQLSaturday, but this is really an area where PASS should be. Not everyone can go to the Summit, especially east coast people when it is in Seattle every year. Give them something to go to.
6. SWAG, especially for startups. PASS should be able leverage its reach to get vendors, publishers, recruiters, etc… to get some money and items.
PASS should provide a growth path for leaders. In addition to the international Board of Directors there should be regional boards that help implement the vision of the international board. These groups can also be the one in charge of regional events. Then you have a growth path from chapter leadership to regional leadership to national/international leadership.
PASS should rotate the location of the Summit. East coast, Central, and West Coast. This will give people who can’t travel to Seattle a chance to go. I’ll be honest, if SQLConnections continues to have a spring conference in Orlando (where I live), I’ll choose that over PASS some years. I don’t have to pay for hotel and airfare or lose 2 days in travel. I can fly to Boston in 3 hours. Seattle?

There are some things that PASS does very well:

The PASS Summit. This year will be my first one, but everyone I know who has gone raves about it, so it must be good.
Virtual Chapters (formerly known as Special Interest Groups). Online communities based on areas of interest instead of geographic location. There are regular Live Meetings that are are fantastic.
24 Hours of PASS. Literally 24 straight hours of online training from some of the best SQL Server minds in the world.

I should note that I am good friends with Andy Warren who is on the PASS Board of Directors. We have discussed what PASS should be doing before, but most of these ideas will be new to him as well. I hope to meet and talk with all the members of the board at the Summit and share these ideas with them and hear their vision for PASS.

What do you think PASS should strive to be?

Where Will We PASS Each Other?

2009-10-27T09:00:00.000-04:00

In my a post on Monday, I talked about who I want to meet while at the PASS Summit. Today I’m going to post where I plan to be while at the Summit.

Sunday

I’m scheduled to fly into Seattle at 6pm, which means I hope to be at the hotel (Sheraton) by 7:30pm. I’ll probably try to find someone in addition to my roommate, Tim Mitchell, to have dinner with once I arrive at the hotel.

Monday

I’ll be looking for folks to meet up with for breakfast. Then I’ll probably head to the convention center and depending on who I meet up with, either hang there for lunch or go out.

In the afternoon I have the Networking Seminar for Volunteers with Don Gabor and then the full Networking to Build Business Contacts seminar with Don Gabor. I believe there are still some seats left for this session (only $60) and I’m sure you’ll get your money’s worth. That session leads into the Welcome Reception and Quiz Bowl. I’ll be finishing the night at the SQLServerCentral party (instructions on how to get a ticket are here). I’ll likely pack it in after this party.

Tuesday

I’ll be starting Tuesday by meeting with a group of people of like faith at breakfast, and event I’m really looking forward to. Then I begin my sessions, of course if I get in the right conversation, I’ll skip a session or two. Here’s the plan for the day:

Data Access Layers: A road map to smarter, efficient and effective queries.
Birds of a Feather Lunch with MVP’s.
Dr. DMV: How to Use Dynamic Management Views to Monito and Diagnose Performance Issues with High Volume OLTP Workloads.
A Tale of Careers and User Groups.
Exploring Index Internals.

Nothing planned for the evening so if you know of any good events to crash let me know. I’ve already been told to follow Wendy Pastrick (@wendy_dance).

Wednesday

I’m not a morning person normally, but I plan to start with the Quest Vendor-hosted Breakfast with a discussion of DMV’s. I may not get anything out of it, but I know Brent Ozar (@BrentO) and Tim Ford (@SQLAgentMan) will be there so at least I’ll meet them. Then my session plan for the day:

Understanding Logging and Recovery in SQL Server
Lunch with the people of like-faith.
Database Design
Insight into Indexes
Nordic – turn SQL Server into an Object DB – I want to make sure I meet Paul Neilsen.

Again nothing planned in the evening. Feel free to let me know what’s going on. Wendy will tire of me following her around. She may think I’m a stalker.

Thursday

Nothing planned for breakfast, but I’ll probably try to get some time with Mike Walsh (@mike_walsh) to start out the day with some Christian fellowship. Then the sessions I’ve picked out for the day:

T-SQL Tips & Tricks
Lunch - Featuring Regional Chapter Leaders. I’ll be the host of the OPASS (Orlando PASS) table as my co-President, Andy Warren, has other responsibilities during this time.
Troubleshooting applications accessing SQL Server
Proactive DBA: Manage SQL Server Better
Advanced SQL Server 2008 Extended Events: Performance Profiling and Troubleshooting Techniques.

Again, nothing planned for the evening. I figure I’ll be following Twitter to findn out where everyone is.

Friday

My flight home is at noon, so I’ll probably try to meet up for breakfast, then pack up and head to the airport.

Wow, when you type up the week it seems short. I’m sure that the week will pass much more quickly than I anticipate. I’m looking forward to meeting people and getting as much information as I can process.

If you are on Twitter be sure to have a search for the #sqlpass hash tag. I know I will.

See you in a few days!

Book Review: Murach’s JavaScript and DOM Scripting by Ray Harris

2009-10-26T23:59:00.001-04:00

In early September I received a complimentary copy of Murach’s JavaScript and DOM Scripting by Ray Harris (Amazon) to review. I got the book because my friend, Andy Warren, passed my name along to the publisher when they asked him to review the book. He knew I was attempting to learn JavaScript, so passed along this opportunity to me.

This is the first Murach book I have read and it definitely has an interesting format. The left page is text and the right page is code, examples, and summary of key points. It took a couple of chapters to get used to the format, but once I did I found it to be very helpful. For some of the early chapters I was able to just skim the right page to pick up the concepts as the content was already familiar to me.

The book is broken down into four sections:

Introduction to JavaScript Programming
JavaScript Essentials
Dom Scripting
Other JavaScript Skills

In the first section, Introduction to JavaScript Programming, you get the basics of web development including XHTML, CSS, and beginning JavaScripting. As an inexperienced web programmer I found this information invaluable. I’ve always been confused by CSS this book helped me to gain a basic understanding so I can now read, understand, and intelligently edit CSS pages. An experience web developer will be able to skim/skip much of this section, but as a relative newbie, I ate it all up.

In section two, JavaScript Essentials, you delve deeper into JavaScript functionality. Getting input and displaying output, working with native objects, control statements, arrays, functions, objects, regex, exception handling, and data validation. Some of the topics in this section, like control statements, are common to other languages so I was able to skim parts of this section and just use the examples and summary on the right hand page.

In section three, DOM Scripting, you really get into the deeper topics. This is where you really get to hone your skills and take advantage of the power of the DOM and scripting. You learn to manipulate the DOM, CSS, and build libraries you can re-use to do this. You get understandable examples and exercises that lead you through the concepts and help you build working examples of a slide show, manipulating tables, and animations. From here to the end of the book, I would imagine that even experienced JavaScript developers would learn something.

In section four, Other JavaScript Skills, you learn how to manipulate the browser and leverage existing JavaScript libraries like jQuery to extend your applications.

The book was was easy to read and the examples and exercises were good. Being a Microsoft developer I was a little disappointed that it the book did not really talk about using JavaScript with Visual Studio, but I guess that is to be expected. I also didn’t think enough time was spent on showing how to deal with the situation when a user has disabled scripting in their browser. The last thing was that I found the instructions in some of the exercises were too vague and while I finished them in a manner that worked, I wasn’t sure if I had done it correctly. The finished scripts were included in the download, but I would have liked to have seen it in the book as an appendix.

So I did have a couple of issues, but overall a really good book. I think it works for beginners all the way to experienced web developers. The more you know you can “mine” the parts of the book that address your weak areas. I’d recommend the book and would buy other Murach books based on my experience with this one.

People I Can’t PASS on Meeting

2009-10-26T09:18:00.000-04:00

One week from today I will be in Seattle for my first ever PASS Summit. One of the things I have heard is what a great event this is for networking with other SQL Server people. I think I’ve been able to experience this in a small way at the three SQLSaturday’s I have attended in the last year, but I’m sure the Summit is different. Tom LaRock (@SQLRockstar) blogged about this back in May and listed some people he has met at the Summit and people he wants to meet this year. That’s what this post is, my list of people I want to meet and converse with at the Summit. Most of these people I have interacted with on-line via Twitter or SQLServerCentral, but have not met in person. The list would be longer, but I met a few who would have been on this list at SQLSaturday #21 – Orlando (Kevin Kline, Andy Leonard, Buck Woody, and Joe Webb) all of whom I look forward to spending more time with, but they don’t qualify for this list as this is for people I have not met in person yet.

Let’s start with the PASS Board of Directors both sitting and new members:

Wayne Snider
Rushabh Mehta
Bill Graziano
Greg Low
Christoph Stotz
Lynda Rab
Rick Heiges
Pat Wright
Douglas McDowell
Tom LaRock
Rick Bolesta
Neil Buchwalter
Kevin Yam
Kristina Kerr
Jeremiah Peschka – Newly elected
Brian Moran – Newly elected

Why this group? The board is the group that sets the direction for PASS and I am interested in where they think PASS should go and I have my own opinions on that as well. They are influencers and I’d like to try to influence them. I do have an in, as my friend Andy Warren is on the board and can introduce me. Hopefully they will all be at the pre-conference networking session with Don Gabor.

The next group comes from my peers at SQLServerCentral. SSC is, in my opinion, a true community and these folks have helped me to be a better SQL Server developer and DBA by being willing to answer questions on their time and sharing their experiences. I don’t even know if everyone on the list will be going to the Summit or if I am listing real or screen names in some cases, but I’ll list them anyway:

Grant Fritchey
Gail Shaw
R. Barry Young
Alvin Ramard
Bob Hovious
Paul White
WayneS – obviously a screen name
Lynn Pettis – I don’t think Lynn will be there this year, but if he is, I want to meet him.
Jeff Moden

Then there is the SQL Server Twitter community, and I do mean community. I have received help and given help to people in the Twitter community and think it is a great way to begin a relationship, and has definitely made it less intimidating to attend the Summit because I feel like I already have lots of friends there. Again I don’t know if everyone I list will be there, but I hope they will be:

Brent Ozar (@BrentO)
Michelle Ufford (@SQLFool)
Wendy Pastrick(@Wendy_Dance)
Mike Walsh (@mike_walsh)
Allen Kinsel (@sqlinsaneo)
Mladen Prajdic (@mladenprajdic) – don’t know that he’ll be making the trip from Europe, but he definitely has earned a spot on this list.
Paul Randal (@PaulRandal) – yeah, I know, he’s on everyone’s list. Maybe I can catch him for 5 minutes.
Kimberly Tripp (@KimberlyLTripp)
Linchi Shea (@linchi_shea)
Marlon Rubinal (@MarlonRubinal)
Robert Cain (@arcanecode)
TJay Belt (@tjaybelt)
Mark Wisecarver (@msftwise)
Marc Beacom (@marcbeacom)
Aaron Bertrand (@AaronBertrand)
Jason Strate (@StrateSQL)
Arlene Gray (@whimsql) – I just live her Twitter handle.
Tim Ford (@sqlagentman) – another great Twitter handle.
Eric Humphrey (@lotsahelp)
Blythe Morrow (@blythemorrow)

I’m sure that I’ve left out someone I shouldn’t have, but this is a good starting point, and I hope all these people will be interested in meeting me.

This is definitely not an all-inclusive list. I want to meet as many people as I possible and still get together with the “old friends” from SQLSaturday’s as well. I’m sure it will be a busy, fun, and profitable week!

Hope to see you there. Next up I’ll post what I know of my schedule so on the off chance you want to meet me you’ll know where I plan to be.

SQLSaturday #21 – Orlando What We Can Do Better

2009-10-22T13:32:00.001-04:00

Well, the event is over and the Event Evaluations have been tabulated. I’ve already posted my week leading up to the event and my event recap that means it is time to do a self evaluation. This is the fourth SQLSaturday I’ve been involved with, but the first one that I’ve been part of the planning committee.

Here are a few things I saw that we could improve on:

Better communication. At the event I found out that some of the speakers had not been receiving speaker emails. I’m not sure how you verify this if the messages don’t bounce back. As the scheduler I should have taken ownership of speaker communication while letting Andy Warren handle other communication. There was also some confusion caused by having the mini-sessions that could have been fixed had I been more proactive in communicating with the speakers.
Volunteer scheduling. We had plenty of people willing to help out, but we didn’t do a good job of using them efficiently. The SQLSaturday site has a decent session scheduler and I think that it should be adapted to put together a volunteer schedule. Sure there will have to be tweaks on Saturday, just like for the speakers, but if the volunteers know where, what, and when ahead of time then they can plan their day as well.
Lunch Tickets. Overall lunch went well and everyone got fed, but there were too many unconfirmed lunch payments. I’ve never tried to interface with PayPal so I don’t know how difficult it is, but perhaps having some kind of confirmation that the attendee needs to print and bring to the event to get a ticket? Also some type of identifier for those who ordered a vegetarian lunch (this would apply when lunch is free as well).
Session Scheduling. I wouldn’t say I totally screwed this up, but I definitely made some mistakes, especially putting SSAS/MDX in one of the smaller rooms. I’d never attended those sessions, so I didn’t realize how popular they are. Experience will definitely help here. I also think have a specific beginner track would be good.
Printed Schedule. We may want to at least put level on the printed schedule, if not the abstract as well, especially if we do poster size schedules again.
Presentation Resources. Having the speakers upload them prior to the event or that day would be helpful.
Session Evaluations. We use them as raffle tickets as well, to try to encourage attendees to do them. The only issue is that means they have to be sorted to be compiled. Kendal Van Dyke had an excellent suggestion, a post-event volunteer meeting to sort and compile the evaluations. This could also be used to do a post-event evaluation as well. I still think that there has to be a better way to collect the information. Always open to ideas.

Overall it was a good event and the majority of the feedback was positive, but you always learn something new you can do better.

If you attended and have any comments on what you think can be done better please comment or email me.

SQLSaturday #21 – Orlando Recap

2009-10-21T09:00:00.000-04:00

In my previous post I discussed the week that led up to the SQLSaturday event. In this post I’ll tell you my perspective on how the event went and let you in on a secret.

Starting the Day

Not being a morning person I allowed Andy Warren to take the lead on setup. He arrived at our venue, Seminole State College of Florida (formerly Seminole Community College) Lake Mary campus, at about 6:30am and had several volunteers meet him there. They got the UHaul truck unloaded and were getting the check-in tables setup when I arrived at about 7:40am. I helped put up our schedule posters (including marking room changes and crossing out sessions that had been cancelled) and helped direct people to the check-in station and chatted. Check-in went smoothly due to our volunteers, I’d name names, but they know who they are and I might leave someone out. We had three issues early:

We had some people go to the wrong room because we forgot to put signs on the original rooms directing people to the new location. Kindly pointed out by Joe Healey (DevFish).
We forgot to bring the coffee pots so when the coffee we had purchased was gone, we were out of coffee.
One of the speakers did not show, so we had to move people to other sessions.

I spent the first session directing people to the correct rooms as some were a little out of the way.

Mid-Morning

Fortunately for me we had an opening in the 10:15 block so we could move Joe Webb’s session on Locking and Blocking from 2:45 so he would have enough time to make his flight back home. Fortunate for me because I got to attend his session. I had a great time and learned a lot in the session. I even asked a question that led directly to his next slide.

Lunch Time

After Joe’s session I helped get setup for lunch. This is one of the areas where we were most concerned because we had requested that attendees pay for lunch and there were some quirks in the system so we weren’t sure if we had ordered lunches correctly. Getting people through the lunch line went very smoothly and we had enough lunches for everyone. We also had two “sessions” during lunch. A question and answer with Joe Celko on the quad, and a demo by Confio in one of the rooms. I spent much of the lunch period announcing the Confio demo because we had not put the room on the schedule.

After Lunch

We had another “new” thing, 15 minute mini-sessions immediately after lunch. I think they were received well. The only issue we had was that this was the only time we didn’t have a change over between sessions which caused a little bit of issue because we (read I) didn’t speak with the full session speakers to prepare them. We wanted to give some beginners a chance to speak and yet, still fill the room.

I had my first session at 1:30, a panel discussion with Andy Warren, Kendal Van Dyke, and myself, on Getting Started in Technical Speaking and Blogging. We had a much bigger crowd than I anticipated, standing room only. The interesting thing was that everyone in the room raised their hand when we asked who wanted to be a speaker and no one said the problem was “fear of public speaking”, but one was lack of opportunity. As a PASS Chapter leader in charge of getting speakers, I can say with authority that I can always use speakers, so please contact me! It was well received even though we broke one of the cardinal rules of public speaking, we hadn’t practiced

After that session I took a break to review for my solo session which was coming up at 4pm. While waiting I ran into some of the other 4pm speakers, Patrick Leblanc, Herve Roggero, and Scott Klein, and we had a discussion about who would have the most in attendance in their session since Joe Celko, Buck Woody, and Jonathan Kehayias were also speaking in that block. Scott Klein and his session on XML won with a packed room of 30.

My Session

My session was “Dive into the Default Trace” which I had done at the Space Coast User Group in September. It went very well. My biggest issue was that I could go on forever so I had to pick it up in the end. My audience didn’t mind though, they enjoyed the interaction and the information provided.

Clean up and Raffle

While Andy was having fun passing out raffle gifts I was busy making sure all the rooms were cleaned up. It was a struggle as the maintenance crew were moving very quickly to get everything closed up. Andy almost gave away my coffee cup, but I walked out in time to save it.

After Party

We had a good turnout at JAX Fifth Avenue and I was able to make the rounds and spend time with just about everyone. I was accused by one speaker of guilting them into coming this year because I sent a personal email asking if they were interesting in speaking again since they had spoken last year.

The Secret

Remember how I talked about discussing who would have the most attendees in the 4pm session, well, it wasn’t me, I had the LEAST! I had one person, but as I said, it went really well and he had something to take back to the office and try. Sure I’d like to speak to hundreds, but one on one was fun.

Next up a few things we (really I) could have done better.

A Week of SQL Server

2009-10-20T08:44:00.000-04:00

As anyone who reads this blog knows, SQLSaturday #21 – Orlando was last Saturday (Oct. 17, 2009) and as a lead in to the event Andy Warren and SQLShare put together a week of one day seminars. Here’s how I spent the week.

Monday

It was the one day there was not a lot I had to do for SQLSaturday nor were there any good networking opportunties here. I did answer some email questions about the event and make a schedule tweak so one of our out of town speakers could make her flight home.

Tuesday

We had a volunteer meeting this evening and stiffed the event bags. We had a good group of volunteers and got the bags done quickly. We also made general plans for who would do what jobs on Saturday.

Wednesday

I had the opportunity to attend the Real World Performance Tuning seminar by Kevin Kline. I had never met Kevin so it was good to meet him and have the opportunity to hear and see the method he has developed when performance tuning. It was also neat the one of the other attendees, Dale (can’t remember his last name), recognized me from my picture on SQLServerCentral. I felt famous.

After the seminar we had an informal OPASS meeting Bahama Breeze billed as a meet and greet with Kevin Kline. Not counting Kevin and Andy Leonard, who was in town to do a seminar on Thursday, there were 11 people that came to hang with and talk with Kevin. This was a great time. We talked shop and made contacts.

As we discussed SQL Server and technology in general it was nice to be able to possibly help out with some technical problems others had experienced and it was great to be able to point over to Andy Leonard when an SSIS question came up and say, “There’s the man you want to talk to.”

At the end it was down to Andy Warren, Andy Leonard, Kevin Kline, and myself and we had a great discussion about PASS and the PASS Summit. Andy Leonard has a great story about meeting Ken Henderson at PASS. I mostly listened and learned. It’s like sitting at the feet of the masters.

Thursday

Unfortunately I couldn’t attend Andy Leonard’s seminar on SSIS because I had conference call I had to be at work for. While this was on-going we found out that some of the rooms we planned on using for SQLSaturday would basically be unusable because we would have had to use the instructor computers in order to use the projectors. This would have nearly impossible as those PC’s do not have SQL Server installed. So when taking breaks from the conference call I re-arranged the rooms we used. Unfortunately we had already printed out all the signs and schedules with room numbers on them.

That evening I was invited to dinner with Andy Warren, Buck Woody (in town for a seminar on Friday), and Kendal Van Dyke. It was quite a learning experience as Buck shared some of his experience.

Friday

I as able to attend part of the seminar, A Performance Tuning Methodology by Buck Woody. He talked about Application Path Analysis and how the DBA really needs to know all about the applications that access the database and how they do it. Why, because DBA stands for Default Blame Acceptor, so you need to know what is happening elsewhere so you can find out what changed and identify the problem. Documenting the Application Path is very important to this process.

In the afternoon I met Patrick Leblanc (@PatrickDBA) and he joined Andy Warren and I on a trip to CostCo to pick up supplies for SQLSaturday and helped load up the UHAUL with all the supplies so we’d be ready for the morning. Patrick is a great guy, fun to be around, and knows his stuff. Take a look at his SQLLunch site where he is attempting to provide regular lunch time (EST) SQL Server seminars.

After we were done loading up we went our separate ways to get ready for the Speaker Party at Jax’s Fifth Avenue in Lake Mary. We had a very good turnout with over 20 of the speaker’s in attendance. I could actually name everyone who was there, but I won’t since it would make for a long list. Needless to say it was a really good time talking shop and the next time anyone tells you that computer geeks aren’t social tell them to go to a SQLSaturday speaker party, I’ve never seen so many talkative geeks!!

Summary

As you can see I had a busy week, but it was well worth it. I met at least 30 new people this week, all of whom are SQL Guru’s and I got to re-connect with a bunch of “old” friends from other events. It was like a taste of the PASS Summit just a few weeks early.

I’ll cover Saturday in my next post as I’ll discuss the event, what I thought went well and where I think we can do better.

Here Comes SQLSaturday 21 – Orlando

2009-10-11T16:57:00.001-04:00

Here it comes! The big event on my schedule SQLSaturday #21 –Orlando. We’ve got over 300 registered so it should be a great event. I’ll be presenting Dive into the Default Trace and enjoying being a volunteer and the networking opportunities presented. We’ve got a fantastic lineup of speakers with 9 MVP’s, a couple of MS employees, and then a bunch of other great speakers like ~~myself,~~ SQL University founder Jorge Segarra (@SQLChicken), Chad Miller (@CMille19) and Kendal Van Dyke (@SQLDBA). So check out the schedule, as you can still register right up to through Saturday morning. I believe that there are also seats available for the pre-event seminars hosted at the SQLShare offices.

There’s a lot to get done this week beginning with a volunteer/bag stuffing meeting at the SQLShare offices on Tuesday night, the pre-event seminars by Andy Warren, Brian Knight, Kevin Kline, Andy Leonard, and Buck Woody (I’ll be there Wednesday and Friday), a special OPASS meeting with Kevin Kline on Wednesday evening, and final prep for the big event on Friday afternoon and evening, the speaker party Friday night, and, finally, the big event on Saturday.

Please don’t hesitate to email sqlsaturday21@sqlsaturday.com with any questions you might have about the event or the seminars.

Maintaining Security and Performance using Stored Procedures Part I – Using EXECUTE AS

2009-10-08T17:20:00.001-04:00

Anyone who knows me, or has worked with me, knows that I am a proponent of using stored procedures for all database access. I believe that using stored procedures makes your database more secure and makes it simpler to maintain a well performing system. One area where stored procedures are more difficult to work with than building queries in the GUI or business layer are with dynamic search queries. Here are some traditional issues with dynamic search in stored procedures:

If you use traditional IF, ELSE statements to build the procedure you get a long and hard read procedure, and you are less likely to get plan re-use.
If you try tricks like WHERE LastName = IsNull(@LastName, LastName) and FirstName = IsNull(@FirstName, FirstName) you can get plan re-use, but the plan used may not be, and many times is not, the best plan to use.
If you use dynamic SQL using the EXEC (@sql) syntax you do not get plan re-use, you open yourself up for SQL Injection, and, prior to SQL Server 2005, you had to grant access to the objects used in the query defeating part of the reason for using stored procedures in the first place.
If you use dynamic SQL using sp_executsql and parameters you are more likely to get plan re-use, you are safe from sql injection, but, pre-2005, you still needed to grant access to the queried objects.
Either dynamic SQL option means creating a large string of SQL and concatenating it, so it can be and, in my opinion is, a pain to read and a pain to make sure you have all your syntax right.

See Erland Sommarskog's excellent articles, The curse and blessings of dynamic SQL and Dynamic Search Conditions, for more details.

Early in my career, when working with SQL Server 7 and 2000 I tended to use option 1, sometimes with temporary tables, then I moved to option 2. I never used dynamic SQL because I did not want to grant select access to the tables being queried. I sacrificed performance for security and counted on ownership chaining to handle access to the underlying tables. With the advent of SQL Server 2005 and the EXECUTE AS I have moved to option 4, dynamic SQL using sp_executesql and parameters as I believe it gives me the best of both worlds.

How’s it work

Essentially you create the stored procedure and add the WITH EXECUTE AS Caller/Owner/Self/’user name’/’login name’ (see the Books on Line entry for EXECUTE AS for more details) and this changes the context in which the code within the procedure is run. So you can create a user (SelectAll) in the database that has select rights on all the tables and then no matter who calls the stored procedure the procedure will run correctly. If you choose to use EXECUTE AS OWNER then the procedure executes in the security context of the Owner of the procedure so you can simulate ownership chaining.

Example

Security

A post like this wouldn’t be complete without at least a simple example. I will be using the AdventureWorks database (get it at CodePlex, I’m still using the 2005 version) with some added data (I used RedGate SQLDataGenerator). All the code to run the examples is available for download here.

First you need to create a user with limited persmissions:

Use AdventureWorks;

Go

Create User DynamicSQLTest without login;

Notice that I used the Without Login syntax so I did not need to create a login as well. This is because I will also be using EXECUTE AS before running the stored procedure to change my execution context to this limited rights user instead of creating a new connection with a limited rights login. Next you need to create the stored procedure. I’m going to start with a “normal” stored procedure using Option 2 from above, because I also want to demonstrate the difference in performance. Here’s the procedure:

Use AdventureWorks;
GO

IF OBJECT_ID('dbo.FindPhoneByName', N'P') Is Not Null
    Begin
        Drop Procedure dbo.FindPhoneByName;
    End;

Go
    
CREATE PROCEDURE dbo.FindPhoneByName
    (
    @LastName nvarchar(50) = null,
    @FirstName nvarchar(50) = null
    )
AS

BEGIN
    SET NOCOUNT ON;

    Select
         Title,
         FirstName,
         MiddleName,
         LastName,
         Suffix,
         Phone  
    From
        Person.Contact
    Where 
        LastName Like IsNull(@LastName, LastName) + N'%' And 
        FirstName Like IsNull(@FirstName, FirstName) + N'%';
        
    Return;         
END
GO

This procedure is pretty self explanatory. Now we need to give the limited rights use, DynamicSQLTest, execute rights on our procedure:

Use AdventureWorks;

Go

Grant Exec on dbo.FindPhoneByName to DynamicSQLTest;

To test the security and performance of the stored procedure we are going to execute it 3 times with a dbo user and then repeat as the limited rights user, DynamicSQLTest. Here is what I used:

Exec dbo.FindPhoneByName @FirstName = 'J', @LastName = 'A';

Go

Exec dbo.FindPhoneByName @FirstName = 'J';

Go

Exec dbo.FindPhoneByName @LastName = 'A';

Go

Then execute the same 3 calls, but run this first to change the security context:

Execute AS User = 'DynamicSQLTest';

Go

If you are running the code in the same SSMS session be sure to issue the REVERT command to return to your original security context.

The stored procedure calls should run successfully for both users and should produce the same results and performance for both users. Now we’ll ALTER the procedure to use dynamic SQL:

Alter PROCEDURE dbo.FindPhoneByName
    (
    @LastName nvarchar(50) = null,
    @FirstName nvarchar(50) = null
    )
AS

BEGIN
    SET NOCOUNT ON;

    Declare @sql_cmd nvarchar(2000),
            @select nvarchar(1000),
            @where nvarchar(1000),
            @parameters nvarchar(1000);
    
    Set @parameters = N'@FirstName nvarchar(50), @LastName nvarchar(50)';
    
    Set @select = N'Select
                     Title,
                     FirstName,
                     MiddleName,
                     LastName,
                     Suffix,
                     Phone  
                From
                    Person.Contact';
    
    Set @where = N' Where 1=1 '                

    If @LastName is not null
        Begin
            Set @where = @where + N' And LastName Like @LastName + N''%'' ';
        End;
        
    If @FirstName is not null     
        Begin
            Set @where = @where + N' And FirstName Like @FirstName + N''%''';
        End;

    Set @sql_cmd = @select + @where;
     
    Exec sys.sp_executesql @sql_cmd, @parameters, @LastName = @LastName, @FirstName = @FirstName;
    
    Return;         
END

Now when you run our examples, you’ll see that it runs successfully under your original security context, but you receive an error when you run it as the limited rights user:

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object 'Contact', database 'AdventureWorks', schema 'Person'.

This is because the execution context changed and ownership chaining no longer applies. To get the Dynamic SQL Stored procedure to work add WITH EXECUTE AS OWNER after the parameter definition like this:

ALTER PROCEDURE dbo.FindPhoneByName
    (
    @LastName nvarchar(50) = null,
    @FirstName nvarchar(50) = null
    )
With Execute As owner
AS

Then you can re-run the your stored procedure calls and they should work both for the dbo user and the limited rights user because the EXECUTE AS OWNER has enabled access to the tables.

Perfomance

I ran all of my examples with SET STATISTICS IO ON so I could see the results. Here are those results (also part of the download):

Parameters	Non-Dynamic SQL		Dynamic SQL
Parameters	Scans	Reads	Scans	Reads
@FirstName = 'J', @LastName = 'A'	1	593	1	593
@FirstName = 'J'	1	7792	1	1116
@LastName='A'	1	3039	1	1116

Notice the reduced number of reads required by the Dynamic SQL when only 1 parameter is supplied. This is because it is using a different query plan, while the Non-Dynamic procedure has one query plan which is not optimal when only one parameter is supplied

Conclusions

As you can see some of the limitations of Dynamic SQL have been “cured” by the advent of the EXECUTE AS clause. This has made it simpler to use Dynamic SQL and get the performance benefits provided by getting a proper execution plan and getting plan re-use. Again all code is available here.

Next I’ll be discussing using a Certificate to sign a stored procedure.

Index on a Foreign Key?

2009-10-06T08:52:00.000-04:00

About two weeks ago, I had a discussion on Twitter and via email with Jeremiah Peschka (@peschkaj) about placing indexes on Foreign Key columns. I made the comment that I while indexing a foreign key is not required that I thought they made good candidates. I also shared this blog post by Greg Low with him.

Jeremiah mentioned that he had never had SQL Server recommend an index on a foreign key which I thought was strange so I decided to run some tests. I ran the tests against my copy of AdventureWorks to which I have added data. Here is the query I ran:

SELECT
    SOD.SalesOrderDetailID,
    SOD.OrderQty,
    SOD.ProductID,
    SOD.UnitPrice,
    SOD.UnitPriceDiscount,
    SOD.LineTotal,
    SOD.UnitPrice2,
    SOH.SalesOrderID,
    SOH.RevisionNumber,
    SOH.OrderDate,
    SOH.DueDate,
    SOH.ShipDate,
    SOH.Status,
    SOH.SalesOrderNumber,
    SOH.PurchaseOrderNumber,
    SOH.AccountNumber,
    SOH.CustomerID,
    SOH.SubTotal,
    SOH.TaxAmt,
    SOH.Freight,
    SOH.TotalDue,
    SOH.Comment
FROM
    Sales.SalesOrderHeader AS SOH JOIN
    Sales.SalesOrderDetail AS SOD ON 
        SOH.SalesOrderID = SOD.SalesOrderID 
WHERE
    SOH.OrderDate >= '12/1/2007'

In my copy of AdventureWorks I have 131465 rows in SalesOrderHeader and 1121317 rows in SalesOrderDetail. The Max(SalesOrderHeader.OrderDate) is 12/30/2007 so I just queried for the last month (1095 rows). In this query SalesOrderDetail.SalesOrderID is a foreign key referencing SalesOrderHeader.SalesOrderID and before modification the Clustered Primary Key on SalesOrderDetail is on SalesOrderID, SalesOrderDetailID. Because SalesOrderID is the lead column in the clustering key the optimizer uses a clustered index seek to access SalesOrderDetail as shown here:

Now, to see if the Optimizer would recommend an index on SalesOrderDetail.SalesOrderID, I altered the clustered primary key on SalesOrderDetail, removing SalesOrderID with the following code:

USE [AdventureWorks]
GO

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Sales].[SalesOrderDetail]') AND name = N'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID')
ALTER TABLE [Sales].[SalesOrderDetail] DROP CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]
GO

USE [AdventureWorks]
GO

ALTER TABLE [Sales].[SalesOrderDetail] ADD  CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY CLUSTERED 
(
    [SalesOrderDetailID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

I then re-ran my first query and now you can see that I there is a clustered index scan, a hash match join, and, in green at the top, a index recommended on SalesOrderDetail.SalesOrderID:

Next I added the suggested index (remember that you should not just add indexes to production without checking other queries as well):

USE [AdventureWorks]
GO
CREATE NONCLUSTERED INDEX SalesOrderDetails_SalesOrderId
ON [Sales].[SalesOrderDetail] ([SalesOrderID])
INCLUDE ([SalesOrderDetailID],[OrderQty],
        [ProductID],[UnitPrice],
        [UnitPriceDiscount],[LineTotal],
        [UnitPrice2])
GO

Then I re-ran my query, and here’s the execution plan:

Here are the Statistics IO results from the 3 queries as well:

Results with SalesOrderID as lead column in Primary Key (Original State)

Table 'SalesOrderDetail'. Scan count 147, logical reads 516, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesOrderHeader'. Scan count 1, logical reads 461, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Results with SalesOrderID removed from Primary Key and not added as an index.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesOrderDetail'. Scan count 1, logical reads 12584, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesOrderHeader'. Scan count 1, logical reads 461, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Results with the suggested index on SalesOrderID.

Table 'SalesOrderDetail'. Scan count 147, logical reads 504, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesOrderHeader'. Scan count 1, logical reads 461, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Conclusion

In this case you can see that adding an index on the foreign key, whether the lead column in the Clustered Primary Key or in a non-clustered index, you get much improved performance particularly regarding reads, and that the non-clustered index actual provides a slightly better access path than being part of the clustered primary key. Certainly you need to test for your individual situations but, I would say that the majority of the time a foreign key will benefit from an index since it is used as a filter in the JOIN criteria.

AdventureWorks can be downloaded from codeplex. Execution Plans and T-SQL can be downloaded from here.

Speaking at SQLSaturday #21 – Orlando

2009-10-01T09:12:00.000-04:00

Well, I guess this isn’t a surprise since I put together the schedule, but I’m still excited to be speaking at SQLSaturday #21 – Orlando on October 17th. I’ll be presenting Dive into the Default Trace. Once again I am honored to be on the schedule with the other speakers including, Buck Woody, Kevin Kline, Andy Leonard, Joe Webb, Joe Celko, Andy Warren, Jonathan Kehayias, Kendal Van Dyke, and more… As Jorge Segarra has said on Twitter, it is like a one day PASS SUMMIT East! All the people I mentioned not only will be speaking here in Orlando, but also have sessions at the PASS SUMMIT as well.

We have over 50 sessions in 9 tracks going throughout the day and there is still time to register. Check out the schedule and I’m sure you’ll find sessions that will help you become a better SQL Server DBA or developer. And remember, you can’t beat FREE training that includes lunch (that’s $10), networking, donuts, coffee, water, and soda.

Hope to see YOU there!

On vs. Where

2009-09-30T09:02:00.000-04:00

Does it matter if you put your criteria in the ON clause or the WHERE clause? Well, as with most things SQL the answer is, “It depends”. If you are dealing with INNER JOIN’s then it really doesn’t matter because the query optimizer is smart enough to come up with the same execution plan for both. For example these 2 queries evaluate to the same execution plan:

SELECT
    SOD.SalesOrderDetailID,
    SOH.SalesOrderID
FROM
    Sales.SalesOrderHeader AS SOH JOIN
    Sales.SalesOrderDetail AS SOD ON 
        SOH.SalesOrderID = SOD.SalesOrderID 
WHERE
    SOH.OrderDate >= '7/1/2004' AND 
    SOH.OrderDate < '8/1/2004'    
    
SELECT
    SOD.SalesOrderDetailID,
    SOH.SalesOrderID
FROM
    Sales.SalesOrderHeader AS SOH,
    Sales.SalesOrderDetail AS SOD 
WHERE
    SOH.SalesOrderID = SOD.SalesOrderID AND
    SOH.OrderDate >= '7/1/2004' AND 
    SOH.OrderDate < '8/1/2004'

The old SQL 6.5 OUTER JOIN syntax (*= and =*) has been discontinued beginning with SQL Server 2005, so you have to do the JOIN for OUTER JOIN’s in the ON as demonstrated in this code:

SELECT

   SOD.SalesOrderDetailID,

   SOH.SalesOrderID

FROM

   Sales.SalesOrderHeader AS SOH LEFT JOIN

   Sales.SalesOrderDetail AS SOD

        ON SOH.SalesOrderID = SOD.SalesOrderID

WHERE

   SOH.OrderDate >='7/1/2004' AND

   SOH.OrderDate <'8/1/2004'

Now let’s create a sandbox to play in.

If OBJECT_ID('sales.orders', 'U') Is Not Null
    Begin
        Drop Table sales.orders;
    End;
    
If OBJECT_ID('sales.order_items', 'U') Is Not Null
    Begin
        Drop Table sales.order_items;
    End;
    
 If SCHEMA_ID('sales') Is Not Null
    Begin
        Drop Schema sales;
    End;   

Go

Create Schema sales;

Go
/* 
Tables to hold sample data 
*/ 
Create Table sales.orders
    ( 
    order_id INT IDENTITY(1,1)PRIMARY KEY, 
    customer_id INT 
    ); 

Create Table sales.order_items
    ( 
    order_detail_id INT IDENTITY(1, 1)PRIMARY KEY, 
    order_id INT, 
    product_id INT, 
    quantity INT 
    ) 

/* 
Load Sample data
*/ 
INSERT INTO sales.orders (customer_id) 
    SELECT TOP 5
        AO.[object_id] 
    FROM 
        sys.all_objects AS AO; 

INSERT INTO sales.order_items 
    ( 
    order_id, 
    product_id, 
    quantity 
    ) 
    SELECT 
        1,
        1,
        7
    Union ALL
    Select
        2,
        1,
        4
    Union ALL
    Select
        3,
        2,
        6
     Union ALL
     Select
        4,
        2,
        11
     Union ALL
     Select
        5,
        3,
        1;

Now we want to return all the customers who have placed an order, but we only want to return the items where the quantity is greater than 5.  Here is method 1:

Select
    O.customer_id,
    OI.order_id,
    OI.product_id,
    OI.quantity      
From
    sales.orders AS O LEFT OUTER JOIN
    sales.order_items AS OI ON
        O.order_id = OI.order_id 
Where
    OI.quantity > 5;

This returns:

customer_id order_id    product_id  quantity
----------- ----------- ----------- -----------
3           1           1           7
7           3           2           6
8           4           2           11

Hmmm, we know we have orders from five customers, but this only returns the three rows. Let’s look at the execution plan:

What’s that nest loops (inner join) operator? Well, by putting the criteria for the RIGHT (second) table in the WHERE clause we essentially converted our LEFT OUTER JOIN to an INNER JOIN. The correct way to get the data we want would be this way:

SELECT O.customer_id, OI.order_id, OI.product_id, OI.quantity FROM sales.orders AS O LEFT OUTER JOIN sales.order_items AS OI ON O.order_id = OI.order_id AND OI.quantity > 5;

This returns what we would expect to see:

customer_id order_id    product_id  quantity
----------- ----------- ----------- -----------
3           1           1           7
5           NULL        NULL        NULL
7           3           2           6
8           4           2           11
17          NULL        NULL        NULL

And here is the execution plan:

Where you can see the Nested Loops (left outer join) operator.

So yes, it does matter where you put your criteria when dealing with OUTER JOINS.

Venturing into Powershell

2009-09-23T08:20:00.000-04:00

As you probably know if you read my blog regularly, I recently put together a presentation called, Dive into the Default Trace, and as part of that presentation I wanted to provide examples of different ways to archive the Default Trace files. Here are the option I considered:

fn_trace_gettable to load the data into a table.
SSIS to archive the files.
PowerShell to archive the files.
xp_cmdshell and the copy command.

I immediately eliminated #4 because I don’t have xp_cmdshell enabled and because Chad Miller (@cmille19) had said in his Powershell talk that anything that requires xp_cmdshell is a good candidate for Powershell. So I sent Chad an email asking him where to start and he was kind enough to help out. I got a “working” script together and sent it off to Chad for review and he kindly told me my errors (there were several) and included a modified script that cleaned up my code. I did make a couple of minor modifications after receiving the script from Chad. The idea and starting point were mine, even if much of the finished product is Chad’s. So here’s the code (please comment if you have any improvements):

# Written with much Assistance from Chad Miller 
# (http://chadwickmiller.spaces.live.com/Blog)
param ($serverName)

#######################
function Get-SqlData
{
    param([string]$serverName=$(throw 'serverName is required.'),
            [string]$databaseName=$(throw 'databaseName is required.'),
              [string]$query=$(throw 'query is required.'))
 
    Write-Verbose "Get-SqlData serverName:$serverName 
                    databaseName:$databaseName query:$query"
 
    $connString = "Server=$serverName;Database=$databaseName;
                    Integrated Security=SSPI;"
    $da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($query,$connString)
    $dt = New-Object "System.Data.DataTable"
    [void]$da.fill($dt)
    $dt
 
} #Get-SqlData

#######################
function Get-TraceInfo
{
    param($serverName)

$qry = @"
SELECT
   RIGHT(T.path, CharIndex('\', 
   Reverse(T.path))-1) as current_file,                      
   Substring(T.path, 1, Len(T.Path) - 
       CharIndex('\', Reverse(T.path))+ 1) as trace_path
FROM
    sys.traces T 
WHERE
    T.is_default = 1
"@

    Get-SqlData $serverName 'master' $qry

} #Get-TraceInfo

#######################
# MAIN
#######################

if ($serverName -eq $null)
    {
        $serverName = $env:COMPUTERNAME
    }

$result = Get-TraceInfo $serverName
$copy_path = $result.trace_path + 'Archive' 

if ((Test-Path -Path $copy_path) -eq $false)
    {
        New-Item -Path $result.trace_path -Name 'Archive' -ItemType directory
    }

Get-ChildItem -Path $result.trace_path -Exclude $result.current_file | 
    where {$_.name -like "*.trc"} | Move-Item -destination $copy_path

Once I knew what cmdlet’s I needed the PowerShell was probably the second easiest one to implement. I know SSIS fairly well and it got a bit complex because of having to exclude the file currently in use. Using fn_trace_gettable is simple because it allows me to use T-SQL which I have many years experience in.

Using PowerShell is really a matter of knowing what cmdlet’s you need, once you have that, the built in help (get-help) is very good, so you can just about do anything. PowerShell is very powerful and flexible. It can actually do so much that I think you need to be careful. For example, the New-Item cmdlet can create just about any item depending on the ItemType you use. '

So while I definitely recommend that any DBA learn some PowerShell I also recommend being very careful and Test, Test, Test!

September OPASS Meeting Recap

2009-09-22T11:15:00.001-04:00

OPASS met last Tuesday, September 14th, with a mini (15 minute) presentation by Todd Holmes on Backup Basics and Jorge Segarra (@SQLChicken) speaking on Policy Based Management in the main presentation. We had good crowd of about 20-25.

Meeting Introduction

We started the meeting with some announcements and discussion of who was going to the PASS SUMMIT. I was officially presented as co-President of OPASS and took some time to discuss the upcoming SQLSaturday 21 – Orlando schedule as I was the one responsible for putting the schedule together.

We then had about 10 minutes of networking time. I spent it talking with Mark, who had come to Orlando on a contract job that had been ended and was looking for work and guidance as to where to go to get solid SQL Server training.

Mini Presentation – Backup Basics

Todd did an okay job on his mini presentation on Backup Basics. He is a first time speaker and you could tell he was a little nervous about being in front of a group. He covered recovery models, backup types, and backup and restore command syntax. I thought he did well to cover that much information in 15 minutes. I don’t think I could have covered as much, especially with the number of questions that were asked.

Feature Presentation – Policy Based Management

Jorge did a good job presenting on PBM. This was my first exposure to it and I was impressed by what you can do with it. He went over the architecture and examples of how to use it. I came away convinced that any DBA in a shop with more than a couple of SQL Servers should become familiar with it and use it. We did learn one thing that is a little disturbing about having an active policy in place, like requiring stored procedure names to fit a standard. If you have an existing procedure that does not meet the standard and you, for whatever reason, make changes to that procedure and choose to deploy those changes using a DROP and CREATE the policy will block the rollback the CREATE, but not the DROP. Here’s an example:

/*
Existing Procedure
*/
CREATE PROCEDURE sp_getorder
    (
    @order_id INT
    )

AS

SET NOCOUNT ON 

SELECT
    *
FROM
    dbo.orders
WHERE
    order_id = @order_id;
    
RETURN;    

GO    
/*
Now you create a policy that says that 
stored procedures must begin with usp_%
*/    

/*
You realize that SELECT * is a bad practice
so you want to change the procedure to only
return the required columns and you do a Drop
and Create
*/

IF OBJECT_ID(N'dbo.sp_getorder', N'P') IS NOT NULL 
    BEGIN
        DROP PROCEDURE dbo.sp_getorder ;
    END 

GO

CREATE PROCEDURE sp_getorder
    (
    @order_id INT
    )

AS

SET NOCOUNT ON 

SELECT
    order_no,
    customer_id,
    salesperson_id,
    purchase_order_no
FROM
    dbo.orders
WHERE
    order_id = @order_id;
    
RETURN;    

GO

The PBM violation will not allow the CREATE to take place, but will allow the DROP. There may be ways to work around this, but Jorge didn’t know any off the top of his head. This discussion was driven by a question from Kendal Van Dyke (@SQLDDBA).

Post Meeting Discussion

As usual we had a good post meeting discussion as Kendal, Jorge, Andy, and I stuck around for about an hour and others for about 30 minutes. We talked about networking, PASS, PASS Summit, and keeping up.

As always some good value in the meeting and the post meeting discussion.

Orlando PASS Meeting

2009-09-15T09:30:00.001-04:00

Tonight is the OPASS bi-monthly meeting. Todd Holmes will be giving a mini presentation on SQL Server Backups and out main speaker will be Jorge Segarra (@SQLChicken) speaking on Policy Based Management. Jorge is very involved in the community as a blogger, twitterer, and speaking at local user groups and SQLSaturday’s. This will be my first time hearing Jorge speak, but I am sure it will go well as Jorge is knowledgeable and engaging.

Come on out. Meeting starts at 6 and there is free pizza and always some type of SWAG. We meet at End to End Training’s office in Altamonte Springs, FL, sorry that would be SQLShare’s offices (Map).

Thanks Space Coast SQL User Group

2009-09-11T09:10:00.001-04:00

I want to thank the Space Coast User Group for having me over to speak on the Default Trace last night. I had a great time meeting everyone and hopefully I presented some information that they all can take back to the office and use.

Space Coast is a relatively new user group but they have good core and are an enthusiastic group. They asked good questions and 7 out of 9 (I think there were 9 I didn’t take attendance) attendees (not counting me) went to the after meeting get together at Holiday Inn.

The meeting started with some announcements and then I got to jump in and start my presentation. I started by doing some marketing for SQLSaturday #21 – Orlando and the great seminar series scheduled the week before. I then asked, “Before tonight, how many people knew that there is a trace running in SQL Server 2005/2008?” Once again the majority were not even aware it existed. We discussed what the Default Trace is, what it traces, where it is used, how to query it, and how to archive the data. I went a little longer than an hour so I’ll have to trim it a little for SQLSaturday. I’d probably grade myself a B-/B as I stumbled around as I changed applications to show code and do demos and had a couple of brain cramps. I need to practice this one a few more times. My slide deck and demo scripts are available here on SkyDrive and I have sent them to Bonnie Allard to post on the Space Coast SQL User Group web site so watch there as well.

We had some great discussions after the event about Powershell, hurricanes, software vendors, and the differences in diets around the world.

What happened to that email?

2009-09-10T12:05:00.000-04:00

This question:

Created script to send mails using sp_send_dbmail- working like a charm.
Now searching for a way to get result code of sent mail (like Success = Recipient got it,
Failure = Did not get regardless of the reason).
I mean SP return codes 0 (success) or 1 (failure) refer to correct mail Profile, not missing Recipient, etc.
Frankly not sure this is possible as it looks like outside Sql Server authority/responsibility?!

asked in this thread on SQLServerCentral prompted me to do some research into Database Mail. The result of the research is that there is no way to get this information from SQL Server.

Basically the way Database Mail/sp_send_dbmail works is that the message is placed in a Service Broker queue (sp_send_dbmail returns success), the external Database Mail executable reads the queue and sends the message to the designated SMTP mail server. If the mail server accepts the message then Database Mail is done and the status is set to sent. So, if you have an incorrect email address or the receiving server refuses it, SQL Server has no way to know. In order to find this out you would need to use a valid Reply To or From email address and monitor that mailbox.

Here’s the query I use for checking Database Mail:

SELECT
    SEL.event_type,
    SEL.log_date,
    SEL.description,
    SF.mailitem_id,
    SF.recipients,
    SF.copy_recipients,
    SF.blind_copy_recipients,
    SF.subject,
    SF.body,
    SF.sent_status,
    SF.sent_date
FROM
    msdb.dbo.sysmail_faileditems AS SF JOIN
    msdb.dbo.sysmail_event_log AS SEL
        ON SF.mailitem_id = SEL.mailitem_id

Let me know if you have any better ways to find errors for Database Mail.

Speaking at Space Coast User Group

2009-09-09T12:22:00.000-04:00

I have the privilege of presenting, Dive into the Default Trace, at the Space Coast User Group, tomorrow evening (Sept. 10).

We’ll be discussing what the default trace is, what it collects, where' it is used, how to find it, and how to query it. I have what I think are some interesting demos and hopefully information that will help developers and DBA’s better manage and audit their SQL Servers.

I’m really looking forward to meeting Bonnie Allard and the rest of the group.

Windows 7, UAC, and SQL Server

2009-09-08T00:14:00.001-04:00

This is just a quick note, almost a continuation of my Access Denied, Not Possible post. I have been working on some queries for a Default Trace presentation that I am preparing for the Space Coast User Group and SQLSaturday #21 – Orlando, and one of the queries has to do with trying to find logins that have gained access through a Windows Group. Since I am working on my laptop (no domain), I decided to add the Builtin\Administrators group, delete my explicit login, and get access via the group. Interestingly enough, in order to get access to SQL Server via Builtin\Administrators you need to run SSMS as Administrator. Here’s the error I get when not running SSMS as administrator:

When I did run SSMS as administrator, I was able to successfully login to my local SQL Server.

No, I do not leave Builtin\Adminstrators as sysadmin on my servers and with SQL Server 2008, I do not have it at all.