Solved

How do you determine if tables are pinned correctly in SQL

Posted on 2004-08-26
11
356 Views
Last Modified: 2008-01-09
I am running SQL 2000 on a Windows 2000 Server.  I just added three tables into memory using the DBCC Pinntable command.  How do I determine that this procedure is working correctly?
0
Comment
Question by:mobot
  • 3
  • 2
  • 2
  • +2
11 Comments
 
LVL 2

Expert Comment

by:KhunJean
Comment Utility
I guess you did this for a performance improvement. Maybe you can use a difference in performance to prove it really works.

If dbcc didn't give any errors it did its work.
Isn't that enough?

Please explain the reason why, maybe i don't understand your question correct.




0
 
LVL 10

Expert Comment

by:Jay Toops
Comment Utility
ALSO,
if those tables where used frequently sqlserver may have cached them to memory anyway. so the diference could be minimal or non-existant.

Jay
0
 

Author Comment

by:mobot
Comment Utility
It is for performance reason.  The reason why I asked is because after running the command I got the message Warning: Pinning tables should be carefully considered. If a pinned table is larger, or grows larger, than the available data cache, the server may need to be restarted and the table unpinned.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I believe the message is fine, I was just expecting to see something that mentioned how many rows affected.  Yes, we will be running the same procedures again to see if we got performance.  I was just curious to know if there was a monitor tool or counter I could use to monitor.

I have another question, we are thinking about adding a 2nd CPU into Windows 2000 Server.  Is there something I need to do to tell SQL to take advantage of the 2nd processor.
0
 
LVL 10

Expert Comment

by:Jay Toops
Comment Utility
after you add the processor it should take advantage of it because the default setting
is to use all available processors.
However id still go to the processor tab of the server properites of enterprise manager and check it anyway to make sure it registers correctly.

Jay
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 10

Expert Comment

by:Jay Toops
Comment Utility
Cache query commands are as follows

DBCC MEMORYSTATUS: Lists a breakdown of how the SQL Server buffer cache is divided up, including buffer activity. Undocumented command, and one that may be dropped in future versions of SQL Server.

Example:

DBCC MEMORYSTATUS

DBCC CACHESTATS: Displays information about the object currently in the buffer cache, such as hit rates, compiled objects and plans, etc. Note in the sample results below that each of these SQL Server objects can be cached in the buffer cache of SQL Server.

Example:

DBCC CACHESTATS

Jay
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
Comment Utility
Although one would think it might, please realize that the DBCC PINTABLE does not actually load the table immediately.  Instead, the table is loaded into memory as normal.  The difference is that it is never released.

If you want to "pre-load" the table to memory, you can issue the PINTABLE command and then do this:

SELECT *
FROM pinnedTable

Now the table is fully read and loaded into memory.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
>>  Instead, the table is loaded into memory as normal. <<

That is, as requests are made for row(s) of the table, they are loaded, same as any other table rows are loaded -- upon demand, when requested.
0
 
LVL 2

Expert Comment

by:KhunJean
Comment Utility
DBCC MEMORYSTATUS
DBCC CACHESTATS

These won't tell you if it is cached or in memory BECAUSE you pinned them. If the table is used frequently it is probably in the cache anyway.

I think you have to mix all the above.
DBCC MEMORYSTATUS
DBCC CACHESTATS
SELECT *
FROM pinnedTable
and again
DBCC MEMORYSTATUS
DBCC CACHESTATS
then
select * from HugeTable
and to confirm the pinned table is still there
DBCC MEMORYSTATUS
DBCC CACHESTATS

But it won't be a 100% sure way of testing it.

There is a change in sysobjects in the master database which you can use to confrim if a table is pinned..
You can check it with this statement
Declare @TableId int
Set @TableId = [insert table id]
use master
Select      Case When (Status & 1048576) > 0 Then
                       'This table in pinned!'
                Else
                       'This table is NOT pinned!'
                End
From        sysobjects
Where      id = @TableId
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
"If the table is used frequently it is probably in the cache anyway."

Depends on how much memory is in the server and how many other objects are being used....Personally, the improvements you see from this are very rare and can actually hurt performance on other things--let SQL handle the memory caching itself....

" Is there something I need to do to tell SQL to take advantage of the 2nd processor."

You need to pay for licensing on the second processor....
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Determine next b-weekly date 12 52
Update foreign key reference after insert 9 30
c# code 19 57
Azure SQL DB? 3 13
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now