February 14, 2008 at 1:46 pm
I work for a company that has 4-500 SQL Server. We do the physical DBA support and some tuning work. Application teams administer the application and some due to the nature of their application have more access than I personally believe they should. Recently there was an issue where a user had a database that was crashing every 15 minutes due to insufficient memory. Turns out that they had pinned a huge table in memory that was growing quite large. When a load got on the system--down it came. Nobody on the apps side is owning up to pinning the table in memory--so trying to be open minded here (turning off DBA genes) I know you can turn on some parameters in visual studio that will when executed pin something in memory.
My question--does someone have a method (since we usually don't know the code or the app) that would help us quickly identify objects pinned in memory. I hate using the undocumented stored procs like DBCC BUFFER, but just thought I'd pose the question to see what you guys could suggest.
February 15, 2008 at 12:08 am
Are you running SQL 2000 or 2005?
In 2005, DBCC PinTable is a no-op. It's deprecated and ignored.
Not sure, in 2000, how to find pinned tables, but if you want to see when a tables gets pinned and by who, set up a profiler trace and filter the TextData on PinTable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2008 at 2:35 am
To find pinned tables, you'll have to query properties on each table:
SELECT OBJECTPROPERTY(OBJECT_ID('table name'), 'TableIsPinned')
K. Brian Kelley
@kbriankelley
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply