What Should PASS Strive To Be?

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:

1. 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.
2. 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.
3. 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.
4. 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.
5. 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:

1. 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.
2. 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.
3. 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?

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

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:

1. Introduction to JavaScript Programming
2. JavaScript Essentials
3. Dom Scripting
4. 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

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

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:

1. 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.
2. 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.
3. 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).
4. 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.
5. 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.
6. Presentation Resources.  Having the speakers upload them prior to the event or that day would be helpful.
7. 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

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:

1. 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).
2. We forgot to bring the coffee pots so when the coffee we had purchased was gone, we were out of coffee.
3. 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

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

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

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:

1. 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.
2. 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.
3. 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.
4. 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.
5. 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
	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.

Part Ib, Part II

Index on a Foreign Key?

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

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!