?
Solved

DBCC PINTABLE - Max Size?

Posted on 2006-05-16
7
Medium Priority
?
369 Views
Last Modified: 2008-03-17
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
0
Comment
Question by:BillCase
  • 3
  • 2
  • 2
7 Comments
 
LVL 27

Expert Comment

by:ptjcb
ID: 16692138
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
 
LVL 27

Expert Comment

by:ptjcb
ID: 16692159
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 16692160


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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:BillCase
ID: 16692456
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16692462
BillCase,
> How do you determine the physical size of a table?

sp_SpaceUsed 'tablename'
0
 

Author Comment

by:BillCase
ID: 16692588
> sp_SpaceUsed 'tablename'
I'm sorry .. I meant, is it just the physical data size? or also the indexes?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16692658
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question