DBCC PINTABLE - Max Size?

I have a "Client" table with over 1 million records which I pinned in memory. The DBCC command returns the message:

          " .. If a pinned table is larger, or grows larger, than the available data cache, the server may need to be restarted .. "

I issued a number of broad select statements to load as much of the table in memory as possible. Two questions:

 - How do I determine what the "available data cache" size is, from the DBCC message, and
 - How do I determine the maximum amount of space the pinned table will require?

TIA!
-Bill
BillCaseAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:


BillCase,
>  - How do I determine what the "available data cache" size is, from
> the DBCC message, and

It is not possible to determine how much memory sql server will  use to buffer cache this is automatically done by SQL server


>  - How do I determine the maximum amount of space the pinned table will require?

This will be the size of the table

0
 
ptjcbCommented:
FROM http://www.sql-server-performance.com/q&a14.asp

Your question asks if there is any maximum cutoff size for tables you want to pin. There are no hard and fast rules, except to say that the table or tables you want to pin must be smaller than the available data cache you have available. If you try to pin a table or tables that exceed the size of the data cache, this could cause SQL Server to fail, and SQL Server would then need to be restarted to resolve the problem.

What I like to do before pinning any tables is first to ensure that the SQL Server Buffer Cache Hit Ratio Performance Monitor counter is running at 90% or higher. If the hit ratio is below 90%, not only would I consider not pinning any tables (because pinning tables may make this ratio worse), I would consider adding more RAM to the server in order to boost SQL Server's performance.

If the hit ratio is 90% or  higher, then I will consider pinning tables, but only if after pinning them the hit ratio is still over 90%. If pinning one or more tables reduces your hit ratio to below 90%, then I would not pin that table or table. Of course you won't know until you try. This means that you might try to pin one table, then watch the hit ratio for a day or two to see what happens. If the hit ratio does not fall below 90%, then you are OK, and may want to consider pinning more tables. But only try one table at a time to see what actually happens to the hit ratio and the overall performance of your SQL Server. At the point where the hit ratio goes below 90%, you know you have gone too far.
0
 
ptjcbCommented:
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
BillCaseAuthor Commented:
Aneeshattingal -

>>  - How do I determine what the "available data cache" size is, from the DBCC message, and
>It is not possible to determine how much memory sql server will  use to buffer cache this is automatically done by SQL server

Is it limited by physical memory? Disk space? I have 6GB of RAM in this server with 100+GB of disk space. Is there a way to estimate the maximum data cache?


>>  - How do I determine the maximum amount of space the pinned table will require?
>This will be the size of the table

How do you determine the physical size of a table?

Thanks!
-Bill

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
BillCase,
> How do you determine the physical size of a table?

sp_SpaceUsed 'tablename'
0
 
BillCaseAuthor Commented:
> sp_SpaceUsed 'tablename'
I'm sorry .. I meant, is it just the physical data size? or also the indexes?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
BillCase,
It has to be both
You can determine how much buffer pool space is being used by looking at the "Total Pages" counter under "Buffer Manager" in PerfMon.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.