DBCC memusage

DBCC memusage reports the buffers used by a particalur object to be far and
away more than any other object. I'm surpised as this table is a historical
system message table for the app that resides on the DB. How does an object
get buffers(I assume reading and writing to the object)? All the views in
the system do select * from tablename, so why this object and not another
transactional table? The ratio of buffers between the 1st and 2nd object is
like 170,000 to 3000. I'm hoping to get clearance to purge this table as ou
r
system is IO bound, disk channels are pegged at 100% and all of it is read
activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
channels RAID5, quad procs with hyperthreading and performance is dismal.Has anyone used DBCC PINTABLE on this table?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
> DBCC memusage reports the buffers used by a particalur object to be far
> and
> away more than any other object. I'm surpised as this table is a
> historical
> system message table for the app that resides on the DB. How does an
> object
> get buffers(I assume reading and writing to the object)? All the views in
> the system do select * from tablename, so why this object and not another
> transactional table? The ratio of buffers between the 1st and 2nd object
> is
> like 170,000 to 3000. I'm hoping to get clearance to purge this table as
> our
> system is IO bound, disk channels are pegged at 100% and all of it is read
> activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
> channels RAID5, quad procs with hyperthreading and performance is dismal.|||Paul's question would be my first guess as well. If that isn't it then you
should run a trace to see how that table is being accessed and how often.
If it is being queried enough and it does a scan it can certainly lead to
this type behavior. When you say "4 disk channels RAID 5" do you mean you
actually have 4 different RAID 5 arrays each on their own channel? Or a 4
disk RAID 5?
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
> DBCC memusage reports the buffers used by a particalur object to be far
> and
> away more than any other object. I'm surpised as this table is a
> historical
> system message table for the app that resides on the DB. How does an
> object
> get buffers(I assume reading and writing to the object)? All the views in
> the system do select * from tablename, so why this object and not another
> transactional table? The ratio of buffers between the 1st and 2nd object
> is
> like 170,000 to 3000. I'm hoping to get clearance to purge this table as
> our
> system is IO bound, disk channels are pegged at 100% and all of it is read
> activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
> channels RAID5, quad procs with hyperthreading and performance is dismal.|||Negative on the pin table aspect although I've been comtemplating pinning a
table myself. I got clearance to purge the table and after purging 60 days
worth of data, other objects are starting to show more than a few hundred
cache buffers. However, another object that holds useless historical data i
s
showing as the top object in memusage. The first table is accessed for evey
message the app server generates but the base view is a select *. Same for
evey other table/view. We have 3 sepearate raid 5 arrays and 1 mirror
array.(Dell 6600 with a 22Os and two dual channel 2960 PERC controllers.)
All data was on one logical drive pegged at 100% utilization and long disk
queues. After moving data around, the three channels are pegged or nearly
pegged all the time. Reads and readaheds are accounting for the usage.
"Andrew J. Kelly" wrote:

> Paul's question would be my first guess as well. If that isn't it then yo
u
> should run a trace to see how that table is being accessed and how often.
> If it is being queried enough and it does a scan it can certainly lead to
> this type behavior. When you say "4 disk channels RAID 5" do you mean you
> actually have 4 different RAID 5 arrays each on their own channel? Or a 4
> disk RAID 5?
> --
> Andrew J. Kelly SQL MVP
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
>
>|||If you have 3 separate arrays and all the channels are pegged you are
probably doing way too much access. You must be scanning most tables (or at
least these big ones you are mentioning) a lot. By the way pinning the
table is usually not a good idea and will go away in 2005 anyway. Sounds
like you just need to optimize your code and or tables so you do more seeks
than scans. You would be amazed that most systems hve just a few calls or
sps that eat up most of the I/O. Once you tackle those others will pop to
the top but you can do a lot of damage control by tuning the top x calls.
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:7C7A5141-70E3-4BFA-9406-D717578EF56E@.microsoft.com...[vbcol=seagreen]
> Negative on the pin table aspect although I've been comtemplating pinning
> a
> table myself. I got clearance to purge the table and after purging 60
> days
> worth of data, other objects are starting to show more than a few hundred
> cache buffers. However, another object that holds useless historical data
> is
> showing as the top object in memusage. The first table is accessed for
> evey
> message the app server generates but the base view is a select *. Same
> for
> evey other table/view. We have 3 sepearate raid 5 arrays and 1 mirror
> array.(Dell 6600 with a 22Os and two dual channel 2960 PERC controllers.)
> All data was on one logical drive pegged at 100% utilization and long disk
> queues. After moving data around, the three channels are pegged or nearly
> pegged all the time. Reads and readaheds are accounting for the usage.
> "Andrew J. Kelly" wrote:
>|||I found that the table in question is accessed by every transaction that hit
s
the system and typically we add 7500-10000 records a day. No data had been
purged for 2 years. Once I purged this and another historical table, the
buffer count leveled out across the top 20 objects.
Now I find out that there is a little monitoring app that hits all the
messaging tables in the system to report status. This is keeping the table
well cached and keeping other transactional tables out of cache. Is there a
n
opposite of pintable, I'd like to excluded this and a few other tables from
cache if at all possible until a data purge process is implemented.
"Andrew J. Kelly" wrote:

> If you have 3 separate arrays and all the channels are pegged you are
> probably doing way too much access. You must be scanning most tables (or
at
> least these big ones you are mentioning) a lot. By the way pinning the
> table is usually not a good idea and will go away in 2005 anyway. Sounds
> like you just need to optimize your code and or tables so you do more seek
s
> than scans. You would be amazed that most systems hve just a few calls or
> sps that eat up most of the I/O. Once you tackle those others will pop to
> the top but you can do a lot of damage control by tuning the top x calls.
> --
> Andrew J. Kelly SQL MVP
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:7C7A5141-70E3-4BFA-9406-D717578EF56E@.microsoft.com...
>
>|||No there isn't anything like that other than to clear the whole cache with
DBCC DROPClEANBUFFERS. SQL Server is pretty good about keeping in memory
what is used most often. If these tables are accessed that often and were
not in cache you would have to go to disk each time and pay that penalty.
It only caches what it reads so maybe if you tuned those status queries you
would read less data and free up that memory for other things. Maybe an
Indexed view would help with status type queries?
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:5505B468-AC5D-4368-93CD-10D0E0309D35@.microsoft.com...[vbcol=seagreen]
>I found that the table in question is accessed by every transaction that
>hits
> the system and typically we add 7500-10000 records a day. No data had
> been
> purged for 2 years. Once I purged this and another historical table, the
> buffer count leveled out across the top 20 objects.
> Now I find out that there is a little monitoring app that hits all the
> messaging tables in the system to report status. This is keeping the
> table
> well cached and keeping other transactional tables out of cache. Is there
> an
> opposite of pintable, I'd like to excluded this and a few other tables
> from
> cache if at all possible until a data purge process is implemented.
> "Andrew J. Kelly" wrote:
>