Solved

How do you determine if tables are pinned correctly in SQL

Posted on 2004-08-26
11
393 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Suggested Solutions

Title # Comments Views Activity
SQL Server Agent Job Error 13 80
Sql does not recognize null value from Oracle source 8 26
database audit for object access 6 42
Section based report in SSRS 14 33
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

739 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