June 13, 2007 at 9:32 pm
June 13, 2007 at 9:48 pm
I think I found the answer:
http://blogs.sqlservercentral.com/blogs/michael_coles/archive/2006/08/09/695.aspx
June 19, 2007 at 2:26 am
What was said in the article is correct. However, there are also other reasons why you would get a miss / hit.
The old time fav is when people name their procs "sp_" Whenever SQL sees a proc called sp_, it assumes it is a system proc and looks in master first. if it does not find it (miss) it looks in sysobjects. No found, looks in the current DB connected. if found, cache hit. if not found (miss), looks in sysobjects, if found, compiles (Cache insert)
Having a high number of misses is a concern in a OLTP environment.
Also "Select FirstName from MyTable Where ID = 2" is not the same as "Select FirstName from MyTable Where ID = 3" (Although 2005 does a better job at it and is not the same as "Select firstname from MyTable Where ID = 2"
HTH
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
March 18, 2008 at 9:26 am
This blog entry has a new link
March 18, 2008 at 10:10 am
One other item to consider in SQL 2005 stored procs is ths use of fully qualified names. So will improve cache hit/miss events.
DAB
March 19, 2008 at 4:19 pm
Also:
Case matters. Refer to your objects in the exact case in which they were created. Not doing so will result in recompilations.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 21, 2008 at 11:54 am
Marios Philippopoulos (3/19/2008)
Also:Case matters. Refer to your objects in the exact case in which they were created. Not doing so will result in recompilations.
Really? Do you a link for this? This is news to me and very interesting ...
March 21, 2008 at 6:22 pm
Adam Bean (3/21/2008)
Marios Philippopoulos (3/19/2008)
Also:Case matters. Refer to your objects in the exact case in which they were created. Not doing so will result in recompilations.
Really? Do you a link for this? This is news to me and very interesting ...
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
CASE (and spacing, mind you!!) matters for ad-hoc queries and batches. The article above does not mention CASE as a factor for stored procedures though. Admittedly, I had not realized that little detail in my earlier posting... 😉
It would be interesting to test it out for sprocs as well, just to be sure...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 26, 2008 at 10:13 am
if your proc call is not the same case as your proc def then you'll get a cache miss event. What's more worrying is that in a highly concurrent environment you may get serialisation of procedure plans.
create proc dbo.MyProcID
exec dbo.MyProcId will give a cache miss event. procs are case sensitive even in a case insensitive environment. I am not convinced that case of tables and columns within procs has any affect. Missing a dbo. ( or schema ) from a table within a proc may cause problems.
I do not see any recompilations caused by incorrect case but I do often see serialisation of proc calls, and hence serious blocking chains.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 26, 2008 at 10:18 am
colin Leversuch-Roberts (3/26/2008)
if your proc call is not the same case as your proc def then you'll get a cache miss event. What's more worrying is that in a highly concurrent environment you may get serialisation of procedure plans.create proc dbo.MyProcID
exec dbo.MyProcId will give a cache miss event. procs are case sensitive even in a case insensitive environment. I am not convinced that case of tables and columns within procs has any affect. Missing a dbo. ( or schema ) from a table within a proc may cause problems.
I do not see any recompilations caused by incorrect case but I do often see serialisation of proc calls, and hence serious blocking chains.
Thank you for the clarification, much appreciated.
Can you explain what you mean by "serialization of proc calls", I'm not sure I followed that part.
thx!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 15, 2008 at 8:59 am
SQLServerLifer (3/18/2008)
One other item to consider in SQL 2005 stored procs is ths use of fully qualified names. So will improve cache hit/miss events.DAB
I used to think so too, until a few minutes ago when I tried the following test:
DBCC FREEPROCCACHE;
create table dbo.t1 (col1 int);
insert into dbo.t1 values (1);
create procedure dbo.sproc1
as
set nocount on
select col1 from t1 --note that table name is not fully qualified
go
In your test db, create 2 users, u1 and u2, and give them exec permissions on the sproc.
Connect as u1 and run "exec dbo.sproc1".
Run:
select sql, cacheobjtype, uid, usecounts
from sys.syscacheobjects
You will get this line - among others:
sql: create procedure dbo.sproc1 as set nocount on select col1 from t1
cacheobjtype: Compiled Plan
uid: 1
usecounts: 1
Connect as u2 and run "exec dbo.sproc1".
Run:
select sql, cacheobjtype, uid, usecounts
from sys.syscacheobjects
You will get this line - among others:
sql: create procedure dbo.sproc1 as set nocount on select col1 from t1
cacheobjtype: Compiled Plan
uid: 1
usecounts: 2
The plan has been re-used with u2!
This simple test suggests that qualification of objects by schema inside stored procedures is not necessary for plan reuse!
Thoughts anyone?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 16, 2008 at 4:02 pm
that's always been my experience too, it's still best practice to qualify objects within procs ( with owner/schema )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 16, 2008 at 8:11 pm
I was about to tell a developer to add owner/schema for all objects referenced in a sproc, but after this test, it seems that does not matter...
I do agree though that it is still a good idea to fully qualify all objects in a sproc or elsewhere.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply