April 29, 2009 at 9:09 pm
Off the top of my head, I think there would be one procedure cache miss when it looks for tables owner by the user (assuming the user is not aliased to dbo), then it will pick up the existing plan (assuming there is one) when it resolves to the default schema (assuming that is where the unqualified tables are).
Bit rushed, hope it makes sense.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 30, 2009 at 7:11 am
There are absolute, demonstrable, performance issues when you don't schema qualify the tables, views, stored procedures & UDF's. You can get away with not doing it, yes, but you're paying a price. Further, you're assuming a single owner for all objects in the database. I'm working more and more with multi-schema databases seperating out tables for security & functional reasons into different schema's. You can't assume a single schema and unique names in database design any more.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 30, 2009 at 10:59 am
FYI, I just did a small test. I took one of the procedures that I mentioned in another post, you know, the one that has 3 selects, each with 32 joins. None were schema-qualified. I made a copy of the proc, and schema-qualified all the objects in the copy.
I set up profiler per Roy's article, filtered for just the SPID of the query window actually running the procs, with these events:
Lock:Acquired
Lock:Realeased
SP:CacheMmiss
SP:Completed
SP:Recompile
SP:Starting
SQL:BatchCompleted
SQL:BatchStarting
couldn't find: SP:ExecContextHit
In a separate query window (w/ sa access) I ran DBCC FreeProcCache prior to each run of these procs:
Schema-qualified proc, w/ schema-qualified objects: 21,426 events in profiler.
Schema-qualified proc, w/o schema-qualified objects: 21,426 events in profiler.
non-schema-qualified proc, w/ schema-qualified objects: 21,426 events in profiler.
non-schema-qualified proc, w/o schema-qualified objects: 21,426 events in profiler.
all the same.....Go figure :unsure:
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 30, 2009 at 2:45 pm
Wayne,
With an empty proc cache, isn't that what you would expect?
Once the proc in question has run once, you should see a cache hit instead of a cache miss with the schema-qualified version (all things being equal). You should still get a cache miss first with the non-chema-qualified version.
Cheers,
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 30, 2009 at 11:26 pm
For those of you actively following this thread:
I gave my input, and here is the rebuttal I received (see below). I've decided to leave the argument be for now (I def don't want to get fired :crazy:)... But can someone please tell me what the heck this means? We as developers don't login as the special user 'dbo', although we are given dbo access, which looks like may be revoked in the near future.
The response noted here:
Yes, I knew about how the SQL Server resolves objects when the owner is not given, Sybase did the same thing, back in the day. Anyway, there is some work going on about access to the SQL Servers and stripping the owner will help work into the options we are reviewing. One of the issues with specifying the owner is that when a developer is developing a stored procedure, they would have to be logged in as “dbo” in order to compile it for use by the application. Having everyone login as “dbo” is not the best solution because it gives more access then need be. Thus my move for stripping the owner off the object names.
May 1, 2009 at 3:22 am
nataliehinnen (4/30/2009)
The response noted here:Yes, I knew about how the SQL Server resolves objects when the owner is not given, Sybase did the same thing, back in the day. Anyway, there is some work going on about access to the SQL Servers and stripping the owner will help work into the options we are reviewing. One of the issues with specifying the owner is that when a developer is developing a stored procedure, they would have to be logged in as “dbo” in order to compile it for use by the application. Having everyone login as “dbo” is not the best solution because it gives more access then need be. Thus my move for stripping the owner off the object names.
Whoever that is, (s)he is a moroff.
Show them this:
-- Run this as a sysadmin or dbo
go
create procedure dbo.CompileMe
as
begin
select 'Hello World' as my_first_column;
end
go
-- A user without any permissions - they can't even log in
create user Foo without login with default_schema = dbo
-- Grant some rights
grant alter on object::dbo.CompileMe to Foo
grant execute on object::dbo.CompileMe to Foo
grant view definition on schema::dbo to Foo
grant showplan on database::Test to Foo
-- Become the user
execute as user = 'foo'
-- Run the proc
execute dbo.compileme
go
-- Change the proc
alter procedure dbo.CompileMe
as
begin
select 'Hello World Again' as my_first_column;
end
go
-- Run it again
execute dbo.compileme
-- Go back to original user id
revert
The permissions structure in SQL Server is complex and very flexible.
Your colleague needs to do some research.
Cheers,
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 1, 2009 at 3:45 am
nataliehinnen (4/30/2009)
One of the issues with specifying the owner is that when a developer is developing a stored procedure, they would have to be logged in as “dbo” in order to compile it for use by the application. Having everyone login as “dbo” is not the best solution because it gives more access then need be. Thus my move for stripping the owner off the object names.
The conclusion doesn't follow the argument.
If the developer's aren't dbo, or don't have dbo as their default schema with attendant permissions to it then, when they create procedures leaving off the schema the procs will go into whatever their default schema is. That means that the app, unless it's looking in the other schemas and has permissions, won't be able to find them. Not good.
There's another problem too.
Developers with dbo access is fine, on a development machine which is where they should be doing all their development. They should, however have no more rights to prod than any other user. DBAs (with sysadmin) should be doing the deployment. That makes the permissions argument null and void.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 1, 2009 at 3:49 am
I have the oddest feeling that they don't have separate dev/test/prod environments.
Maybe I am wrong...?
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 1, 2009 at 3:56 am
Paul White (5/1/2009)
I have the oddest feeling that they don't have separate dev/test/prod environments.
I suspect as much, which is a far bigger stuff-up than dropping schema names from procs.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 1, 2009 at 4:04 am
Agreed!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 1, 2009 at 6:00 am
I'm on board with questioning the ability of the person who said it's in the plan to drop the schema definition. We've been able to set developers up to create objects, even objects owned by dbo, without having them as database owners since 2000. It's not complicated or difficult. You just have to use the schema name.
Very simply, and this works in 2000/2005/2008, in the development environment we give the devs these privileges through a role:
db_datareader --obvious
db_datawriter --ditto
db_ddladmin --allows them to manipulate objects through ddl commands
db_securityadmin --allows them to assign security to objects
We then explicitly revoke their permissions to create tables, indexes...yada, yada, so that they can only create stored procedures, views and UDF's. It's easy and it absolutely works. We grant execution privileges to an application roll for all the stored procedures, which are usually assigned to the schema in which the tables live, so no extra work required there. Assign the developers to that roll as well so that they can impersonate the app when needed... ta da.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 1, 2009 at 9:02 am
Yes, we do have different dev/test/prod environments. I interpreted the email to mean that they plan on using different schemas for security reasons.... could that be it?
I should probably talk to him, to get a better understanding of what he means, but I'm not sure what to ask... hence my post.
May 1, 2009 at 9:09 am
Why not send him a link to this thread and allow him to respond directly?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 1, 2009 at 9:32 am
Some good advice above, and I'd definitely like to hear the developer chime in.
However I'd argue that developers don't need any access to production unless they use the app as a user. Meaning if they submit expenses, they need to be a regular user on the expense app.
For the dbo issue, honestly to keep track of things I wouldn't want too many people to be able to put something in the main schema. Having a choke point, a couple people that can change the schema from an individual's to the dbo or main schema, is a good thing.
May 1, 2009 at 9:39 am
nataliehinnen (5/1/2009)
I interpreted the email to mean that they plan on using different schemas for security reasons.... could that be it?
Could be, but again there's no logical reason hit to specify schemas and, if they are going for multiple schemas specifying the schema is no longer optional. If the call's not qualified and the object's not in the user's default schema or in dbo, it will not be found.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply