Using Print, will it slow down procedure?

Question

Using Print, will it slow down procedure?

Viewing 15 posts - 46 through 59 (of 59 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

ScottPletcher (9/14/2012)
You do realize that comments still take up space in the proc cache, right?

They take up no space in the plan cache. The plan cache is the new (more accurate) name for the procedure cache (which has always stored compiled objects other than procedures, like triggers for example).

Comments are stored as part of the definition in the system table sys.sysobjvalues and accessed when required for short-term needs like recompilation or sys.dm_exec_sql_text. No doubt (part of) the definition is read into some part of memory at some point, but this is quite different from saying comments take up space in the 'proc cache'.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

ScottPletcher SSC Guru Points: 101105 More actions · Answer 2

SQL Kiwi (9/14/2012)
ScottPletcher (9/14/2012)
You do realize that comments still take up space in the proc cache, right?
They take up no space in the plan cache. The plan cache is the new (more accurate) name for the procedure cache (which has always stored compiled objects other than procedures, like triggers for example).
Comments are stored as part of the definition in the system table sys.sysobjvalues and accessed when required for short-term needs like recompilation or sys.dm_exec_sql_text. No doubt (part of) the definition is read into some part of memory at some point, but this is quite different from saying comments take up space in the 'proc cache'.

I never claimed they took space in the plan cache, as I made clear earlier.

The original source must stay available, so must be cached somewhere. If you add 11K of comments to 2K of code, you're wasting HUGE amounts of RAM and delaying everything else running.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Paul White SSC Guru Points: 150467 More actions · Answer 3

ScottPletcher (9/14/2012)
I never claimed they took space in the plan cache, as I made clear earlier.

The procedure cache and plan cache are two names for the same thing.

The original source must stay available, so must be cached somewhere.

It's always available from disk of course. Must be cached? No. Might be cached? Perhaps. Not in the 'proc cache' though.

If you add 11K of comments to 2K of code, you're wasting HUGE amounts of RAM and delaying everything else running.

Why would the full definition of all objects always be in memory all at once? The definition is always available from disk if necessary. My guess is that there is no special cache for definitions; the pages of sys.sysobjvalues are cached in the buffer pool just like the pages of any other table.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

ScottPletcher (9/14/2012)
The original source must stay available, so must be cached somewhere. If you add 11K of comments to 2K of code, you're wasting HUGE amounts of RAM and delaying everything else running.

It's stored in the system tables, hence at most it'll be in the buffer pool with the rest of the tables. 11k is not huge amounts of RAM , that's not even 2 pages. I think there's far bigger gains for performance to be found than from eliminating a few pages from a system table.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101105 More actions · Answer 5

SQL Kiwi (9/14/2012)
The original source must stay available, so must be cached somewhere.
It's always available from disk of course. Must be cached? No. Might be cached? Perhaps. Not in the 'proc cache' though.
If you add 11K of comments to 2K of code, you're wasting HUGE amounts of RAM and delaying everything else running.
Why would the full definition of all objects always be in memory all at once? The definition is always available from disk if necessary. My guess is that there is no special cache for definitions; the pages of sys.sysobjvalues are cached in the buffer pool just like the pages of any other table.

Don't see how it could "always" use the original version.

If you ALTER the procedure, any currently running versions have to complete with the original code ... right?. And if one them needs to recompile a statement, how do they do it if the original source isn't cached?????

11K is large in the context of stored procs. I suspect most stored procs don't reach that length.

Two objects can't be stored in that space at the same time, so huge amounts must force other source code from the cache at some point.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ScottPletcher SSC Guru Points: 101105 More actions · Answer 6

SQL Kiwi (9/14/2012)
My guess is that there is no special cache for definitions; the pages of sys.sysobjvalues are cached in the buffer pool just like the pages of any other table.

So the comments would still push 2 pages of other data out of the buffer.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Paul White SSC Guru Points: 150467 More actions · Answer 7

ScottPletcher (9/14/2012)
If you ALTER the procedure, any currently running versions have to complete with the original code ... right?. And if one them needs to recompile a statement, how do they do it if the original source isn't cached?????

Sounds like you have never encountered error 2801.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 8

ScottPletcher (9/14/2012)
SQL Kiwi (9/14/2012)
My guess is that there is no special cache for definitions; the pages of sys.sysobjvalues are cached in the buffer pool just like the pages of any other table.
So the comments would still push 2 pages of other data out of the buffer.

Not necessarily. There are algorithms to decide which data to evict from which cache under memory pressure. If the sys.sysobjvalues pages were accessed more frequently/recently than other data pages, then perhaps so. However, cached copies of pages on disk have a much lower 'worth' than e.g. cached plans.

No-one is arguing that there might not be some small memory effect from having very large comments in code, though views may vary on how important the effect might be. What we are objecting to, is your assertion that comments are stored in the 'proc cache'. They're not.

I have heard that some places strip comments from code before production deployment. This might be a hangover 'best practice' from SQL Server 6.5 / 7.0 days. In any case, it's not something I have ever found to be a big win. There have always been much greater opportunities.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

ScottPletcher SSC Guru Points: 101105 More actions · Answer 9

ScottPletcher (9/14/2012)
SQL Kiwi (9/14/2012)
My guess is that there is no special cache for definitions; the pages of sys.sysobjvalues are cached in the buffer pool just like the pages of any other table.
So the comments would still push 2 pages of other data out of the buffer.

Not much.

Are you trying to say that 2801 will always occur if you ALTER a stored proc that is currently running? Obviously any ALTER could remove code that was in the earlier version.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Paul White SSC Guru Points: 150467 More actions · Answer 10

ScottPletcher (9/14/2012)
Are you trying to say that 2801 will always occur if you ALTER a stored proc that is currently running? Obviously any ALTER could remove code that was in the earlier version.

I rarely use the word 'always' 🙂 but if a statement-level recompile occurs, yes.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 11

ScottPletcher (9/14/2012)
Not much.

Here's a quick script then:

-- Run this in one query window

USE tempdb;

GO

CREATE PROCEDURE dbo.Forever

AS

BEGIN

SET NOCOUNT ON;

WHILE 1 = 1

BEGIN

DECLARE @i int;

SELECT @i = 0 OPTION (RECOMPILE);

END;

GO

EXECUTE dbo.Forever;

-- While that is running:

ALTER PROCEDURE dbo.Forever

AS

BEGIN

SET NOCOUNT ON;

WHILE 1 = 1

BEGIN

DECLARE @i int;

SELECT @i = 0 OPTION (RECOMPILE);

END;

GO

Msg 2801, Level 16, State 1, Procedure Forever, Line 9

The definition of object 'Forever' has changed since it was compiled.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

GSquared SSC Guru Points: 260824 More actions · Answer 12

I have to say I think the argument is kind of pointless.

I'd need to see some sort of proof, or at least an assertion from the SQL Server devs at MS, that comments use live RAM. The evidence I've seen thus far indicates otherwise, but is far from incontrovertible.

BUT, much more importantly, I don't see how it can possibly matter.

On a 1970s computer, where every byte was sacred, sure. On any computer built since about 1995, I can't see it mattering.

I've debugged and tuned a lot of overloaded database servers. Never once has the problem been "too many comments". On the other hand, I've cursed a lot of devs because their code lacked comments, or had brain-damage level comments ("--Selects rows from MyTable" is NOT a useful comment, it's a sign of brain damage on the part of the person who wrote it: http://www.youtube.com/watch?v=qyMSc97UksM).

It is theoretically possible that 11k, which is some pretty serious commenting, might matter on a system that was living in the swap file. I'd be willing to bet that it has never actually mattered, but I do have to admit that it is theoretically possible.

In order to test this:

SET NOCOUNT ON;

DECLARE Cur CURSOR

FOR

SELECT N'CREATE PROC dbo.ManyComments' + CAST(Number AS NVARCHAR(10))

+ ' (@Number_in INT)

AS /*

This is a HUGE comment block

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

*/

DECLARE @Number INT = @Number_in;

PRINT @Number;

GO

EXEC dbo.ManyComments' + CAST(Number AS NVARCHAR(10)) + ' 1;

GO'

FROM Common.dbo.Numbers;

DECLARE @Cmd NVARCHAR(MAX);

OPEN Cur;

FETCH NEXT FROM Cur INTO @Cmd;

WHILE @@Fetch_status = 0

BEGIN

EXEC sp_executesql @stmt = @Cmd;

FETCH NEXT FROM Cur INTO @Cmd;

END;

CLOSE Cur;

DEALLOCATE Cur;

I ran that on my test server. That's 10,001 copies of a proc, each with 160k of comments in it = 1.6 Gig of comments. RAM usages went from 5.61 GB to 5.63 GB.

So, while I consider the discussion kind of pointless, I say again, I'd need to see some evidence pointing to comments being RAM-resident.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

CptCrusty1 Hall of Fame Points: 3386 More actions · Answer 13

I don't know whether or not to be proud or embarrased... the answer to my question has taken on a life of it's own....

:blink:

Paul White SSC Guru Points: 150467 More actions · Answer 14

GSquared (9/17/2012)
I'd need to see some evidence pointing to comments being RAM-resident.

You can check which pages of sys.sysobjvalues are in the buffer pool and view the page contents using DBCC PAGE:

There are many ways to get to the right page, but the easiest is to run something like this from the DAC:

SELECT

sys.fn_PhysLocFormatter(%%PhysLoc%%),

CONVERT(varchar(max), imageval)

FROM sys.sysobjvalues

WHERE

[objid] = OBJECT_ID(N'dbo.Pointless', N'P');

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 15

CptCrusty1 (9/17/2012)
I don't know whether or not to be proud or embarrased... the answer to my question has taken on a life of it's own....:blink:

I would go with 'proud' 😎

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi