December 28, 2001 at 8:32 am
I have been monitoring the procedure cache on my SQL 2000 DB. It is an Ecommerce DB. The procedure cache percentage is way low, about 12% average. Compiles are way up to around 100 average. Any ideas where I should start looking.
Rob DeMotsis
Sr. SQL Server DBA
December 28, 2001 at 9:07 am
How are the stored procedures named on your databases? They aren't sp_ per chance, are they?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
December 28, 2001 at 9:17 am
Why yes they are.
Rob DeMotsis
Sr. SQL Server DBA
December 28, 2001 at 3:18 pm
Yeowch! And you can quote me.
sp_ is reserved for SQL Server system stored procedures, but they aren't reserved... Which means Microsoft doesn't want you to use them, but they didn't stop you from doing so. When you have a stored procedure called sp_, here is where SQL Server looks for the stored procedure (in order):
1. master database
2. Look based on any qualifiers (e.g., MyDatabase.dbo.sp_MyProc)
3. Look in the current database with dbo as the owner
Since it always looks at the master database first, unless the stored procedure exists in the master database, you will almost always get an Cache Miss. If you have Profiler from the SQL Server 2000 client tools (the Cache Miss event isn't in the SQL Server 7 Profiler), you'll see the Cache Miss as it occurs. It's looking to match the cached execution plan against a stored procedure existing in the master database.
Since it's recompiling the stored procedure each time, there is also another issue with naming stored procedures sp_ and that is SQL Server generates an exclusive compile lock while compiling.
Brian Moran goes into more detail in an article that appeared in the December 2001 SQL Server Magazine:
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=23011
Hope that helps.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
Edited by - bkelley on 12/28/2001 3:18:09 PM
K. Brian Kelley
@kbriankelley
December 28, 2001 at 3:30 pm
That's great information. I vaguely remember seeing something on it before. After looking into the stored procedures more closely, I found that there are only a few Stored Procs that begin with sp_, most begin with usp_.
It looks like for some unknown reason the cache is now fine. The average is back up to acceptable range. I'm still looking into it. I will post a follow up if I find the cause.
Rob DeMotsis
Sr. SQL Server DBA
December 28, 2001 at 9:07 pm
Had you recently stopped/started the service?
Andy
December 30, 2001 at 3:03 pm
I haven't tested it since v7 no sp but for that it would execute the version in the database for user sps but the version in master for system sps. I guess it must still look in master first to find if it is system or user though.
Cursors never.
DTS - only when needed and never to control.
December 31, 2001 at 8:02 am
I hadn't restarted the service. I was monitoring it when it started getting low, and watched it plummet. I did forget to mention that I am running the Database on a cluster. I checked the event logs during that time and noticed that the nodes were having a hard time seeing each other. It could be network related.
Rob DeMotsis
Sr. SQL Server DBA
Pier 1 Imports, Inc.
Rob DeMotsis
Sr. SQL Server DBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply