Monday, November 30, 2009

Maintaining Security and Performance Using Stored Procedures Part II – Signing

Well, it has been a couple of weeks since my last blog post and over a month since Maintaining Security and Performance Using Stored Procedures Part I – Using EXECUTE AS was posted, although I did spend time working on the originally unplanned follow up to that post when I would have been doing this post.  I know you all have been anxiously awaiting this post.

EXECUTE AS vs. Signing

Like EXECUTE AS, the ability to sign a module (stored procedure, DML trigger, function, or assembly) with a certificate was added in SQL Server 2005.  In addition to being able to allow access to objects within the current database context, signing also allows you to access resources in another database or that require server level permissions.  With EXECUTE AS on functions, stored procedures, and DML triggers you are limited by database context, so if you need access to objects in another database you are out of luck, even if the EXECUTE AS user has proper rights in the database.  You can download code that demonstrates this behavior here.

Demonstrations of Signing

Laurentiu Cristofor has an excellent blog post that demonstrates signing a stored procedure to grant server level permissions here, so I am not going to duplicate his work in this post.  I will demonstrate how to sign a procedure for use within a database and when accessing another database.
Using Signing to enable Dynamic SQL within the database
USE AdventureWorks;
GO

/*
Create a restricted_login
*/
CREATE LOGIN restricted_login WITH Password = '$tr0ngPassword';

GO

/*
Create a Certificate first
*/    
CREATE CERTIFICATE cert_dynamic_sql 
ENCRYPTION BY PASSWORD = 'c3rtificatePa$$w0rd'
WITH subject = 'Dynamic SQL Security'   
GO 

/* 
Create user based on the certificate
*/ 
CREATE USER certificate_user FROM CERTIFICATE cert_dynamic_sql;

/*
Give the certificate_user select on all objects
*/
GRANT SELECT ON SCHEMA::Person TO certificate_user;
GO

/*
Create a restricted rights database user
*/    
CREATE USER restricted_user FROM LOGIN restricted_login;

GO

/*
Create the procedure
*/
CREATE 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

GO

/*
Give the restricted user exec on the proceduere
*/
GRANT EXEC ON dbo.FindPhoneByName TO restricted_user; 

GO
/*
Change context to the restricted rights user
*/
EXECUTE AS LOGIN = 'restricted_login';

GO

/*
Exec the procedure - will fail on the dynamic portion
*/
EXEC [dbo].FindPhoneByName;

GO

/* 
Return to sysadmin rights
*/
revert;

GO

/*
Sign the procedure
*/
ADD SIGNATURE TO [dbo].[FindPhoneByName]
BY CERTIFICATE cert_dynamic_sql
WITH PASSWORD = 'c3rtificatePa$$w0rd';
GO

/*
Change context to the restricted rights user
*/
EXECUTE AS LOGIN = 'restricted_login';

GO

/*
Exec the procedure - will work now
*/
EXEC [dbo].FindPhoneByName;

GO

/* 
Return to sysadmin rights
*/
revert;

GO
Using Signing to Access an Object in Another Database
In this example I’ll use a signed procedure to access a table in the Northwind database (download here) from the AdventureWorks database (I use a 2005 copy with extra data, the unmodified version is available here).  One thing I found in my testing is that you have to use a private key file in this case.  If anyone knows how to do it without the file please let me know.  Here is the code:

USE MASTER;
GO

/*
Create the restricted_login
*/
CREATE LOGIN restricted_login WITH Password = '$tr0ngPassword';

GO

USE Northwind;

GO

/*
Create the Certificate
*/    
CREATE CERTIFICATE cert_access_other_db 
ENCRYPTION BY PASSWORD = 'c3rtPa$$word'
WITH subject = 'Access Other DB'   
GO 

/* 
Backup the certificate being sure to use a Private Key
*/
BACKUP CERTIFICATE cert_access_other_db TO FILE = 'C:\Certificates\cert_access_other_db.cer'
WITH PRIVATE KEY (FILE = 'C:\Certificates\cert_access_other_db.pvk' ,
ENCRYPTION BY PASSWORD = '3ncRyptKeyPa$$word',
DECRYPTION BY PASSWORD = 'c3rtPa$$word');
GO

/*
Create the certificate user in the Northwind and give needed permissions
*/
CREATE USER certificate_user FROM CERTIFICATE cert_access_other_db;

GO

GRANT SELECT ON dbo.Categories TO certificate_user;

GO 

USE AdventureWorks;

GO

/*
Create a restricted rights database user
*/    
CREATE USER restricted_user FROM LOGIN restricted_login;

GO

/*
Create the procedure
*/
CREATE PROCEDURE [dbo].access_other_db
AS
SET NOCOUNT ON

SELECT 
SYSTEM_USER AS USERName, 
*
FROM
[Northwind].dbo.[Categories] AS C;

RETURN;    

GO

/*
Give the restricted_user execute rights on the sp
*/
GRANT EXEC ON dbo.access_other_db TO restricted_user;

GO

/*
Create the certificate in this database from the file
*/
CREATE CERTIFICATE cert_access_other_db FROM FILE = 'C:\Certificates\cert_access_other_db.cer'
WITH PRIVATE KEY (FILE = 'C:\Certificates\cert_access_other_db.pvk',
DECRYPTION BY PASSWORD = '3ncRyptKeyPa$$word', /*The password used to create the private key */
ENCRYPTION BY PASSWORD = 'D3cryptKeyPa$$word');
GO

/* 
Execute as a sysadmin - works - this is my user
*/
EXEC [dbo].[access_other_db];

GO


/*
Now execute as the restricted user 
*/
EXECUTE AS LOGIN = 'restricted_login';

GO

/*
This will fail.
*/
EXEC [dbo].[access_other_db];

GO

/*
Back to the sysadmin level
*/
Revert;

GO

/*
Sign the procedure 
*/
ADD SIGNATURE TO dbo.access_other_db
BY CERTIFICATE cert_access_other_db WITH Password = 'D3cryptKeyPa$$word'
GO

/*
Now execute as the restricted user 
*/
EXECUTE AS LOGIN = 'restricted_login';

GO

/*
This will now work.
*/
EXEC [dbo].[access_other_db];

GO

/*
Back to the sysadmin level
*/
Revert;

GO

/*
Be sure to delete the certificate and
private key when done.
*/

Summary

As you can see from this post and the previous post (or two), the SQL Server team has given you some good options when it comes to using Stored Procedures for data access and manipulation while still maintaining security.  You can use EXECUTE AS to allow cross-schema or within database access and you can use module signing to allow access to system objects or cross-database queries without specifically granting users access to the objects.

Resources

After having planned and started the post because I had not found anything outside of Books On Line, I found a few resources that covered the material as well, and I used each to help me write this post once I found them.
  • I linked to Laurentiu Cristofor’s post earlier
  • Erland Sommarskog has an excellent write-up on Giving Permissions through Stored Procedures which handles this subject very thoroughly as you would expect form Erland.  Erland’s post helped me get past the fact that I needed to use a private key in order to get the cross database piece working as none of my other resources did this.
  • Jonathan Kehayias also answered this this forum post with an example.
In reality I could have just posted these links as these other folks covered the subject on signing thoroughly, but I decided that since I did the research and wanted to try the code myself that I’d share my experiences as well, crediting these guys for their work and hopefully sending them some traffic, however limited, from this blog.

Finally all the MY code from this post can be downloaded from here.

Monday, November 9, 2009

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

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:

  1. Attend sessions about areas I normally do not work in.  This probably means more BI.
  2. 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.

Saturday, November 7, 2009

PASS Summit Day 3 – Brain on Overload

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.

Thursday, November 5, 2009

PASS Summit Day 2 – What More Can I Learn

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

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.

Tuesday, November 3, 2009

My First PASS Experiences

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:

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

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:

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.

Monday, November 2, 2009

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

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.