dbcc memorystatus & sysperfinfo

Question

dbcc memorystatus & sysperfinfo

Debbi Porter

Old Hand

Points: 364
More actions
February 28, 2003 at 1:10 pm

#105470

Does running dbcc memorystatus have any impact (truncate) on the sysperfinfo table? Sysperfinfo is not populated as it should be. I am unable to find any detailed documentation for dbcc memorystatus.
I have a SQL2K oltp database for which I need to capture performance statistics. The application and database is expected to increase by a factor of 5 when we incorporate new functionality. I need to be sure we purchase enough memory and cpus to avoid performance problems. Any help will be greatly appreciated! TIA!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 1

No, dbcc memorystatus does not truncate the sysperfinfo table. It's a straight forward counter giving stats cumulative since the server started. And an excellent source of info.

I also use dbcc sqlperf(waitstats) to monitor wait states caused by resource shortages. The CMEMTHREAD counter gives counter info on memory shortages telling you how many requests had to wait and for how long. If you store these values every night, and subtract the previous nights values from them, you can graph resource shortages easily. RESOURCEQUEUE can be memory related as well, in case your interested.

I like to rank my daily waits for a priority list of improvement areas, and then trace back through the cause of it, fix it, and see the system improvements visually in the graphed data. On one job I worked, I suggested adding a network card to a server as we were experiencing a higher load of traffic than was expected on a particular server yet the server was practically idleing except for the traffic. The waits dropped dramatically, and of course, on the chart over the next two or three days, looked impressive. I bet my boss at the time carried that chart around for a month, cause every time I saw him, he had a copy on his clipboard showing someone.

Debbi Porter Old Hand Points: 364 More actions · Answer 2

Thanks for the great information. I'll implement the dbcc sqlperf(waitstats) right away. How cool to have such a great impact on your environment. Sounds like your boss was very impressed!

I still really need a good source of information for this dbcc memorystatus.To analyze the output, I need to understand, for example: the Other category under buffer distribution (it appears high - but how do I know), XDES under Global Memory Objects, or whether hashing is high,etc. I've been checking the forums, Books Online and books I've purchased to no avail. The type of information Scorpion_66 included in the previous post is exactly the kind of information I'm looking for.

Thanks!

zach_john SSCertifiable Points: 5922 More actions · Answer 3

I can see where and how you would manage this manually. Were you able to automate it and if so how? Would you mind sharing.

quote:
If you store these values every night, and subtract the previous nights values from them, you can graph resource shortages easily

John Zacharkan

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 4

Create a scheduled job that writes the results of the dbcc command to a table.

Create an Excell sheet that queries the table your writing the prior steps results to. I use tab2 for last nights results, and tab 3 for the previous nights values and store the differentials on tab1 in an one of thirty one columns (one per day of the month). Alternately, a stored proc to write the same resultsets to excell would work fine.

Now create a graph off the differential's you end up with (include all thirty one columns for a rolling month window).

TA, DA!!!!!! Perfmon made over.........for custom data even.....

I created a seperate excell sheet to do the subtracting and graphing, and have a proc that writes my data to a different excell sheet automatically at night. This way, I do not have to update my queried data manually, I simply open the excell sheet that contains the graph, and let that update automatically.

I have given a lot of thought to having the stored proc do the subtracting, and just write the end results out, but ended up needing to drill down to a day's numbers, so left that function within excell. It could be done many different ways, depending on your particular needs.

zach_john SSCertifiable Points: 5922 More actions · Answer 5

Everyhting you wrote is fabulous and sincerly apprecative, but my Jersey brain is still frozen from this most miserable winter here. I still don't get how to execute the dbcc statement in a job and have the result set redirect to a table. I know how to direct it to a text file, but a table I'm lost.

thanks for everything

quote:
Create a scheduled job that writes the results of the dbcc command to a table.
Create an Excell sheet that queries the table your writing the prior steps results to. I use tab2 for last nights results, and tab 3 for the previous nights values and store the differentials on tab1 in an one of thirty one columns (one per day of the month). Alternately, a stored proc to write the same resultsets to excell would work fine.
Now create a graph off the differential's you end up with (include all thirty one columns for a rolling month window).
TA, DA!!!!!! Perfmon made over.........for custom data even.....
I created a seperate excell sheet to do the subtracting and graphing, and have a proc that writes my data to a different excell sheet automatically at night. This way, I do not have to update my queried data manually, I simply open the excell sheet that contains the graph, and let that update automatically.
I have given a lot of thought to having the stored proc do the subtracting, and just write the end results out, but ended up needing to drill down to a day's numbers, so left that function within excell. It could be done many different ways, depending on your particular needs.

John Zacharkan

sarmistead SSCommitted Points: 1671 More actions · Answer 6

Here's a quick way to get the output into a temporary table:

create table #t1 (

bufferdist varchar(80) null,

numbuffs int null)

insert into #t1 exec ('dbcc memorystatus')

--drop table #t1

select * from #t1

HTH,

Steve Armistead,

Database Administration

Panther Systems Northwest, Inc.

Vancouver, WA

Steve

zach_john SSCertifiable Points: 5922 More actions · Answer 7

The ice has melted, the fog is lifting.

Thanks guys,

John Zacharkan

Chief78CJ7 SSC Veteran Points: 227 More actions · Answer 8

Scorpian,

How often do you run the scheduled 'dbcc sqlperf(waitstats)' command? Once a minute maybe?

chinn-76143 SSC Enthusiast Points: 133 More actions · Answer 9

Scorpian,

I am having tough time to read the output

of dbcc sqlperf(waitstats)

can anybody explain it for me.

your help i greatly appreciated.

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 10

It very much depends on what your wanting to see as to how often to run it. In most cases, I run it every hour for logging, to allow me to see trends during the day. There are some servers I manage, however, I do run it several times an hour, and of course, the opposite is also true on a few where I run it a single time a day. Just remember that it is cumulative, and determine how granular you need your samples.

As far as reading the outputs, when I have a few minutes, I'll post another message to address that, as I have a meeting to go to, and not enough time to go into it right this minute, but I will get back to it.

detzu SSC Journeyman Points: 87 More actions · Answer 11

create table #t1 ( bufferdist varchar(80) null, numbuffs int null)

use numbuffs bigint null

if you have more than 4Gigs of Ram