October 2, 2012 at 4:05 am
Hi,
I can across this link on the net http://www.sommarskog.se/dyn-search-2005.html.
Somewhere there's this:
On lines 25-28 there is something very important:
FROM dbo.Orders o
JOIN dbo.[Order Details] od ON o.OrderID = od.OrderID
JOIN dbo.Customers c ON o.CustomerID = c.CustomerID
JOIN dbo.Products p ON p.ProductID = od.ProductID
As you can see, I refer to all tables in two-part notation. That is, I also specify the schema (which in SQL 7/2000 parlance normally is referred to as owner.) If I would leave out the schema, each user would get his own his own private version of the query plan, so if there are ten users searching for orders there will be ten instances of the plan in cache. (As discussed in Curse and Blessings... it may work without specifying the schema on SQL 2005, but you should not rely on that.)
Since this article is written over SQL 2005 my question is: is this still true on SQL 2008?
For each users that executes a SP, if the tables' names are not fully qualified, there's a execution plan for each user?
This can be not so bad if each user has a different work mode and makes queries that have nothing to do with other users, I guess...
Thanks,
Pedro
October 2, 2012 at 4:54 am
The dbo prefixing thing seemed to be a problem after changeover from SQL 2000 to SQL 2005, though what it was, I've forgotten.
I've always thought it is generally good practise to prefix tables with the schema, and can remember companies that insisted all stored procedures were gone through to make sure all tables were prefixed with 'dbo.'
But if you haven't upgraded from SQL2000 - then on 2005 or 2008, if the users in question have a different default schemas, then yes, Id' guess they would generate different plans, however usually the default schema is dbo - so if all users have the default schema it would not matter.
Not sure I want to do this myself, but if you create two users in your northwind DB, make sure they have the same default schema, then execute the same statement without the dbo. a few times for each user, looking at the results of this query, to see what plans get cached.
SELECT TOP 10
plan_handle, sql_handle,
ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
,TextData = qt.text
,DateCached = qs.creation_time
,LastExecutionTime = qs.last_execution_time
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.text like '%Customers%' -- or something in your query
ORDER BY plan_handle;
I would guess you would see the plan reused.
Then create another user with a different schema, and see if when that user executes the query you get a new plan created.
I think you would see a different plan. But I'll leave this up to someone else to test out and confirm/refute.
October 2, 2012 at 4:59 am
You will not get different query execution plan per user if you don't specify schema.
Unless your system is designed in a such way (it's very rare case actually) that one group of users have a dedicated default schema and all relevant objects are duplicated in every such schema, it is always good idea to specify schema name.
1. It explicitly suggest which schema the object belongs to
2. Because of #1 you will get some performance benefits as compiler doesn't need to check which schema the object is from.
October 3, 2012 at 2:10 pm
(If SQL2008 is the same as SQL2005 in this regard, and I expect it is,...)
Don't forget that even if my default schema is DBO-- if I execute the following 2 queries, they will NOT use the same cached execution plan.
...They may come up with identical EPs, but they will both be generated.
SELECT (some columns) FROM SomeTable WHERE (some condition)
SELECT (some columns) FROM DBO.SomeTable WHERE (some condition)
... and the following query won't reuse the EP from the first either...
SELECT (some columns) FROM SomeTable WHERE (some condition)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply