SQL Server Performance Forum – Threads Archive
Does the Pinned Tables effect server’s performance
Can anybody tell,Does the Pinned Tables effect SQL server’s performance? Thanks,
HJ
Declaring a table to be pinned means that records from this table which enter the cache will never be flushed out. This means that once all records from the table are in cache, the full table can be selected without any I/O required to read the actual data. In this respect performance does increase. However, you must be careful not to pin any tables which are large, or could grow large over time. Obviously while the table is pinned there is a certain amount of cache memory committed to it, and this will then be unavailable for use. Be aware that there will be a point when too much cache committed to pinned tables will actually start impacting performance detrimentally, as SQL server will be unable to use cache optimally for other tables. For this reason only use pinning on relatively small tables which are frequently accessed. Also my advice is only to use pinned tables when you have identified the need for a performance increase, avoid the temptatoin to pin tables just because you think it will keep things nice and speedy in the future. Chappy
Great advice Chappy. If your buffer cache hit ratio stays the same as before pinning a table, then the pinned table is not hurting. But if you do see the buffer cache hit ratio go down after pinning a table, then unpin the table as this is an indication performance has been hurt by pinning the table.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
Thanks. Another question,
The same small table will be accessed by several highly-used SPs, The contents of SPs are like, Begin trans
update table1 set A1=number+1 where….
select max(A1) where….
Commit trans Do you see any problems? Any improvement?
Thanks.
Revised… Begin trans
update table1 set A1=number+1 where….
select max(A1) from table1 where….
Commit trans
Are you trying to do key generation with these stored procs? Use IDENTITIES instead!
Am I correct that you are saying that the table you want to pin will be updated? I guess what I really need is some more details of what these SPs are doing, and to which tables, for what reasons, before I can address your question of "do you see any problems, improvements."
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
Thank you all. The function is really as [email protected] mentioned
" Are you trying to do key generation with these stored procs? Use IDENTITIES instead!" Our developers can’t change it to IDENTITIES because the way applicaiton designed before. They really need a meanful key instead of IDENTITIES. Even using a maping table is still not a good idea for them. I’m just wandering whether T-SQL has return code 0 or 1 which can identify the statement succeed or fail? Thanks again.
The SQL variable @@ERROR can be used to determine if the most recent SQL statement completed without errors (@@ERROR==0). If there was an error @@ERROR contains its error code. Remember that if a statement in a batch fails, but the subsequent statement succeeds, testing @@ERROR at this point will equate to 0.<br /><br />I agree with anil. If A1 is to be used as a key, try to use identities unless its really not possible. Granted, your app is designed in a certain way, but you should weigh up the costs associated with redesigning portions of your app Vs problems associated with attempting to maintain your own incrementing key (performance may be degraded etc). <br />Sometimes spending a little longer doing things right first time round, will save a whole world of pain down the road <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /><br /><br /><br />
Also keep in mind that using keys that have a "meaning" is generally considered by database design. Keys should be meaningless, but designed in such a way as to reduce index size and to help reduce some INSERT issues, such as contributing to excess page breaks. That’s why using the identities is so popular, and are often used for primary keys. But of course, if you are stuck with a design you can’t change, you often have to live with it. I am just mentioning this issue for others new to database design.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
]]>