May 13, 2009 at 10:57 am
Hi All,
I have a Cluster Setup for sqlserver 2005 enterprise etd.I have 8 Gigs.
Now, Can some one help to find what is the Memory Used by the Processes. (What are the Symptomps of memory Leaks)
As the Task manager shows 7.5 gigs is used and my Queries executing is running quite slow and some times timeout.?
What are the checklist should i check to track the Exact issue.
Thanks,
Gagan
May 13, 2009 at 1:09 pm
Oh you poor soul.
I recently had the same issue at my previous job. It was so bad we failed over ever night just to clear the memory issues.
It turns out there is a patch for SQL Server. I sent an email to my old boss to see which patch it was as I do not remember. I will post here as soon as I get it. You may want to check MS website under SQL for information.
Sorry I cant be of more help atm, but I no longer work there and dont have access to the documentation.
Raymond Laubert
Exceptional DBA of 2009 Finalist
MCT, MCDBA, MCITP:SQL 2005 Admin,
MCSE, OCP:10g
May 15, 2009 at 12:24 am
Thanx Ray,
I tried my best to find.But no luck..
Still would need your help
May 15, 2009 at 8:16 am
gaganks (5/13/2009)
Hi All,I have a Cluster Setup for sqlserver 2005 enterprise etd.I have 8 Gigs.
Now, Can some one help to find what is the Memory Used by the Processes. (What are the Symptomps of memory Leaks)
As the Task manager shows 7.5 gigs is used and my Queries executing is running quite slow and some times timeout.?
What are the checklist should i check to track the Exact issue.
Thanks,
Gagan
-Use RML tools to identify the resource-intensive queries. On MS site you can find a lot of information about that. Keyworkds to check: Timeout (Attention event), Rollup by CPU/IO, etc)
It is the magic tool (together with some manual query redesign after) to solve such issues.
May 21, 2009 at 6:49 am
Have you installed SP3 and the latest CU?
There are some mem leak fixes included.
May 24, 2009 at 1:36 pm
Gagan,
Is it 32 or 64 bit?
Post the results of this query. This will tell you exactly where is the bottleneck.
-- Isolate top waits for server instance since last restart or statistics clear
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN( 'SLEEP_TASK', 'BROKER_TASK_STOP',
'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
'LAZYWRITER_SLEEP')) -- filter out additional irrelevant waits
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold
You may want to bookmark this page for future reference.
http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!1340.entry
http://technet.microsoft.com/en-us/library/cc966540.aspx
These will tell you how your memory is being used.
--Buffer usage by database
select db_name(database_id) as dbName, count(*)*8/1024 as BufferPoolMB
from sys.dm_os_buffer_descriptors
group by db_name(database_id)
order by 2 desc
--Identify if the adhoc sql cache is bloated
select
objtype,
count(*)as number_of_plans,
sum(cast(size_in_bytes as bigint))/1024/1024 as size_in_MBs,
avg(usecounts)as avg_use_count
from sys.dm_exec_cached_plans
group by objtype
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
May 24, 2009 at 1:49 pm
If you have 8GB memory, make sure that SQL's max memory setting is set to no more than 6 GB. This is especially important on 64 bit.
What service pack do you have installed?
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply