Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 405
  • Last Modified:

How do you determine if tables are pinned correctly in SQL

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
mobot
Asked:
mobot
  • 3
  • 2
  • 2
  • +2
1 Solution
 
KhunJeanCommented:
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
 
Jay ToopsCommented:
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
 
mobotAuthor Commented:
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
Jay ToopsCommented:
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
 
Jay ToopsCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
Scott PletcherSenior DBACommented:
>>  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
 
KhunJeanCommented:
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
 
arbertCommented:
"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

Industry Leaders: 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!

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now