Memory grants outstanding and Memory grants pending

Memory grants outstanding and Memory grants pending

Post by U3JlZV » Sat, 01 Mar 2008 05:19:03


We have some performance issues on our db server memory on the box is 32GB. I
am looking out for perfmon counters what is the value should for both memory
grants pending/memory grants outstanding?

Memory grants pending the value seems to be 0 for this counter
Memory gants outstanding? counter value is averaging 10

Average Latch waitime is over 35,500 sec.

Any explanation is appreciated.

Thanks,
 
 
 

Memory grants outstanding and Memory grants pending

Post by Andrew J. » Sat, 01 Mar 2008 08:37:07

That isn't a lot to go on. Do you have your max memory set to less than 32?
Is this 32 or 64 bit? What edition and version of SQL Server? What are
your top waits?

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

 
 
 

Memory grants outstanding and Memory grants pending

Post by U3JlZV » Sat, 01 Mar 2008 09:26:01

its 32 bit SQL Server 2000 total memory on the box is 32 gb and AWE is
enabled and allocated 28gb for sql server. Total target server memeory and
total server memory is same number.

I am trying to understand what is memory pending outstanding/memory pending
if the number ranges from 15-25 for memory grants outstanding is that
bad/good? I dont seem to find anywhere regarding ideal numer or what to
monitor.

Thanks in advance!
 
 
 

Memory grants outstanding and Memory grants pending

Post by Andrew J. » Sat, 01 Mar 2008 09:50:35

To be honest in all my years I don't know if I ever looked at those
counters. Yes large numbers of grants are not good but I don't know what
that number is either but I suspect anything over 1 for an extended period
is not good. You need to determine what the overall bottlenecks are first. I
would start with the Wait stats so you can get an idea of what the system is
really waiting on. Here are some links that may help.

http://www.yqcomputer.com/

This is for 2005 but the principles are the same:
http://www.yqcomputer.com/

http://www.yqcomputer.com/
Performance Audit
http://www.yqcomputer.com/
Performance WP's
http://www.yqcomputer.com/
Hardware Performance CheckList
http://www.yqcomputer.com/
SQL 2000 Performance tuning tips
http://www.yqcomputer.com/
Performance
http://www.yqcomputer.com/


--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
 
 
 

Memory grants outstanding and Memory grants pending

Post by U3JlZV » Sun, 02 Mar 2008 02:47:01

i Andrew,

Thanks for information we have been monitoring our db server whenever avg
latch wait time is over 1000 ms we see performance degradation at the same
time we see memory grants pedning go up.

Hopefully some day MS post detail documentation on memory counters
explanation. I am not convinced the fact that why sql server cannot use more
than 4gb even if we allocate 28gb with AWE switch. Our SE's tells us where
the rest of the memory on ths sytem? u see only less than 3 gb.... Perfmon
shows target server memory to 28gb but when we see all transactions combined
it doesn't come upto that number. Its kind of confusing for SEs and end
others.

Thanks for the reply.
Sree


"Andrew J. Kelly" wrote:

 
 
 

Memory grants outstanding and Memory grants pending

Post by Andrew J. » Sun, 02 Mar 2008 09:38:02

ou can't use Task Manager to view AWE memory usage. The target and total
counters are the correct counters and if it says you are using 28GB then you
are. The only parts of SQL Server that can use AWE memory (in your case
anything above 2GB) is the data buffer pool. All other memory that SQL
Server uses has to use the 2GB of directly addressable memory on a 32 bit
server. If you are seeing issues with not having enough of that type of
memory you should consider a x64 bit OS and SQL Server so it can use all the
available memory for what ever it needs to. Hopefully you do not have the
/3GB switch still set in the Boot.ini. If you do take it out. Chances are
you are experiencing issues related to the procedure cache. Do you have lots
of adhoc queries? How large is your procedure cache. If you run DBCC
MEMORYSTATUS you will see a section near the bottom that looks like this:

Buffer Counts Buffers
------------------------------ --------------------
Committed 14400
Target 51328
Hashed 9783
Stolen Potential 44429
External Reservation 0
Min Free 64
Visible 51328
Available Paging File 573836

Procedure Cache Value
------------------------------ -----------
TotalProcs 305
TotalPages 2858
InUsePages 36

See how many procs you have and more importantly what the total pages are.
Did you have a look at the wait stats yet? What are the top waits?


--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"SreeV" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...

 
 
 

Memory grants outstanding and Memory grants pending

Post by U3JlZV » Sun, 02 Mar 2008 10:21:01

elow is the DBCC MEMSTATUS. We are on sql server 2000 are you referring data
from waitstats table? if so is there anyway we could get simliar data from
sql2k?

Some groups do run adhoc queries occassionally but majority are from
apps/sprocs.
Thanks again for you timely response.

Procedure Cache Value
------------------------------ -----------
TotalProcs 2489
TotalPages 13445
InUsePages 9815



"Andrew J. Kelly" wrote:

 
 
 

Memory grants outstanding and Memory grants pending

Post by Andrew J. » Sun, 02 Mar 2008 10:34:11

urprisingly that doesn't look too bad. The links I provided whould get you
atarted on looking into performance issues in general but here are a couple
sps that you can use to view the wait stats in 2000. The first puts a
snapshot into a table. If you call it the first time with a parameter of 1
it will clear out the wait stats counters but that is optional. You need to
take at least 2 snapshots for the report sp to work. Take a snap shot with
the sp then wait an hour and execute it again. Then run the 2nd sp to get a
listing of the waits. One of the links I posted before (
http://sqldev.net/misc/WaitTypes.htm ) will tell you what hey mean.

CREATE PROCEDURE [dbo].[gather_waitstats]
@Clear TINYINT = 0

AS

SET NOCOUNT ON

IF OBJECT_ID('[dbo].[VWaitStats]') IS NULL
BEGIN

CREATE TABLE [dbo].[VWaitStats] (
[wait type] VARCHAR(80),
[requests] NUMERIC(20,1),
[wait time] NUMERIC (20,1),
[signal wait time] NUMERIC(20,1),
[CaptureTime] DATETIME DEFAULT GETDATE()
)
END

IF @Clear = 1
BEGIN
TRUNCATE TABLE [dbo].[VWaitStats]

-- Clear out the waitstats
DBCC SQLPERF (WAITSTATS,CLEAR)

END

INSERT INTO [dbo].[VWaitStats] ([wait type], [requests], [wait time],[signal
wait time])
EXEC ('DBCC SQLPERF(WAITSTATS)')

GO
-------------

CREATE PROCEDURE [dbo].[report_waitstats]
@WithOLEDB TINYINT = 0

AS

SET NOCOUNT ON

DECLARE @Samples INT, @Delay VARCHAR(9), @Now DATETIME, @BeginTime DATETIME,
@EndTime DATETIME
DECLARE @TotalWait NUMERIC(20,1), @FirstSample DATETIME, @SecondSample
DATETIME, @Interval INT
DECLARE @ExcludedWait NUMERIC(20,1)

SET @FirstSample = (SELECT TOP 1 [CaptureTime]
FROM [dbo].[VWaitStats]
ORDER BY [CaptureTime] )

SET @SecondSample = (SELECT TOP 1 [CaptureTime]
FROM [dbo].[VWaitStats]
WHERE [CaptureTime] > @FirstSample
ORDER BY [CaptureTime])


SELECT @Now = MAX(CaptureTime), @BeginTime = MIN(CaptureTime), @EndTime =
MAX(CaptureTime)
FROM [dbo].[VWaitStats]
WHERE [wait type] = 'Total'

SET @Interval = DATEDIFF(mi,@FirstSample,@SecondSample)

SET @Samples = (DATEDIFF(mi,@BeginTime,@EndTime) / @Interval) + 1

SET @Delay = RIGHT('0' + CAST(@Interval / 60 AS VARCHAR(2)),2) + ':' +
RIGHT('0' + CAST(@Interval % 60 AS VARCHAR(2)),2) + ':00'

--- subtract waitfor, sleep, and resource_queue FROM Total
IF @WithOLEDB = 0
BEGIN
SELECT @TotalWait = SUM([wait time]) + 1 FROM [dbo].[VWaitStats]
WHERE [wait type] not in
('WAITFOR','SLEEP','RESOURCE_QUEUE','Total', '***total***','OLEDB') and
CaptureTime = @Now


SELECT @ExcludedWait = SUM([wait time]) FROM [dbo].[VWaitStats]
WHERE [wait type] in ('WAITFOR','SLEEP','RESOURCE_QUEUE','OLEDB')
and CaptureTime = @Now
END
ELSE
BEGIN
SELECT @TotalWait = SUM([wait time]) + 1 FROM [dbo].[VWaitStats]
WHERE [wait type] not in
('WAITFOR','SLEEP','RESOURCE_QUEUE','Total', '***total***') and CaptureTime
= @Now

SELECT @ExcludedWait = SUM([wait time]) FROM [dbo].[VWaitStats]
WHERE [wait type] in ('WAITFOR','SLEEP','RESOURCE_QUEUE') and
CaptureTime = @Now
END

PRINT 'Started at: ' + CONVERT(VARCHAR(20),@BeginTime,20) + ' Ended at:
' +
CONVERT(VARCHAR(20),@EndTime,20)
PRINT ' '
PRINT 'Duration: ' + CONVERT(VARCHAR(10), DATEDIFF(mi,@BeginTime,@EndTime))
+ ' Minutes'
PRINT ' '

PRINT 'Numer of Sa
 
 
 

Memory grants outstanding and Memory grants pending

Post by U3JlZV » Sun, 02 Mar 2008 11:20:00

hanks for the sproc Andrew! I do run dbcc sqlperf waitstatus but
unfortunately too much of data too little of documenation on the output. The
results of dbcc memorystatus doesn't look too bad because there is not much
activity on the server we are planning on doing another load test will run
wait stats and let you know. Thanks for your prompt reply.

Sree
"Andrew J. Kelly" wrote: