SP cache miss q's? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SP cache miss q's?

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-04-05 : 18:24:17

I've got a couple of procedures that just *always* seem to generate a cache miss. It's particularly annoying because one of them is the most commonly used SP in my database, so whenever I take a look at cache misses with profiler, I see lots of this one.

The thing is, I can't figure out why. It's being called properly from an ADO command object, with the dbo.p_name syntax (not sp_ or anything). Everything looks good.

However, the procedure itself consists of a select, then exec's of 4 different procedures (all referred to with owner.p_name). Is there something inherent about doing an exec inside an SP that causes a cache miss? Or any other ideas?

Thanks
-b

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-04-08 : 15:04:23

Ok, this is a weird one, but I think I figured it out. Or maybe it's not weird, but it certainly wasn't what I was expecting.

I can now fix and re-create the cache miss issue easily from my ASP app. Apparently, some part of ADO / SQL server is case sensitive.

CREATE PROCEDURE p_Test_Case AS
select 1

--- Test_miss.asp
Dim oDB, oCmd, oRS

Set oDB=GetDB() ' Get a DB connection one way or another
Set oCmd=Server.CreateObject("ADODB.Command")
With oCmd
  .ActiveConnection=oDB
  .CommandType=adCmdStoredProc
  .CommandText="dbo.p_test_case"
End With
Set oRS=Server.CreateObject("ADODB.RecordSet")
oRS.Open oCmd
oRS.Close
Set oRS=nothing
Set oCmd=nothing
oDB.Close
Set oDB=nothing

--- Test_hit.asp
Dim oDB, oCmd, oRS

Set oDB=GetDB() ' Get a DB connection one way or another
Set oCmd=Server.CreateObject("ADODB.Command")
With oCmd
  .ActiveConnection=oDB
  .CommandType=adCmdStoredProc
  .CommandText="dbo.p_Test_Case"
End With
Set oRS=Server.CreateObject("ADODB.RecordSet")
oRS.Open oCmd
oRS.Close
Set oRS=nothing
Set oCmd=nothing
oDB.Close
Set oDB=nothing

...A simple test, and watching with profile confirms the issue. Having the CommandText of an ADODB.Command not match the case of the procedure casues a SP:CacheMiss.

Is this one of those "Gee, you mean I shouldn't use cursors?" moments? Or does this surprise anyone else?

Cheers
-b

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-08 : 15:21:57

Have you looked at syscacheobjects to see what ends up there?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-04-08 : 15:27:52

When the case of the call agress with the procedure name, the correct thing appears there. When the case is different, nothing is ever cached; the cache misses will just go on forever, one after another, and nothing new appears in syscacheobjects.

Thanks for turning me on to that table, though. Didn't even know about that.

In case it's just me, it's SQL server 2000, NT4, SP2 + hotfixes.

Cheers
-b

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-08 : 15:35:00

I think it hasn't spotted that it's the same procedure from the call but it does when it resolves the object ID which is why you get a cache miss but no insert.

It does the same when calling from query analyser.

I thought that maybe it would cause problems if the first call wasn't in the same case as the SP name but it takes itr from the SP definition.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-04-09 : 14:39:55

I think you're quite right. Thanks for the cogent explanation.

I'm off to fix any other stray mixed-case situations.

Cheers
-b

SnapJag
Starting Member

1 Post

Posted - 2011-05-12 : 15:02:53

This is an old post, but wanted to provide the link that answers the question why ... the answer, because you have to call the stored procedures using the owner name and the case-sensitive name. Hashing is done by the sql compiler for caching and when it doesn't find the cached stored proc by the hashed name (which is case-sensitive, and requires the full qualifed owner and SP name).

http://support.microsoft.com/kb/263889

...this has helped in many occasions of troubleshooting and performance, increased production activity, and has helped many other DBAs and Developers I've mentored to understand the sensitive requirement for more efficient SP execution from applications.

Greg L. Wright
Sr. DBA/Contractor
SnapJag.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources