Solved

How do you determine if tables are pinned correctly in SQL

Posted on 2004-08-26
11
397 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
11 Comments
 
LVL 2

Expert Comment

by:KhunJean
ID: 11907187
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
ID: 11907218
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
ID: 11907337
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 10

Expert Comment

by:Jay Toops
ID: 11907377
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
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11907431
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:
Scott Pletcher earned 250 total points
ID: 11907447
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:Scott Pletcher
ID: 11907460
>>  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
ID: 11907633
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
ID: 11907813
"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

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

623 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