April 6, 2006 at 7:33 am
There was a high memory usage at some point of time on production server. where do I need to start investigating that problem?
April 6, 2006 at 8:09 am
There cannot be simple one command solution for this.
There are few places you should look for at first
1. Check error log to figure out what was going on Db around the time of your high memory usage.
2. Look at the job history in the same time to see if any job has command that uses too much of resources.
3. Keep eye on connection value to see that during any time in day you get too many connections open.
4. Run Performance monitor and log few counters for few hours e.g. Memory Pages/Sec, Buffer hit ratio, % Disk Time and % processor time.
This are all good indicators to resolve such issues.
Hope this helps,
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
April 6, 2006 at 8:15 am
Where can I see the job history?
April 6, 2006 at 8:26 am
Execute following command in Query Analyzer
SELECT sj.job_id,sj.name,step_id,step_name,
CASE WHEN run_status = 1 THEN 'Success'
ELSE 'Failed'
END AS Status ,
CAST ( LEFT(CAST(run_date AS VARCHAR(8)),4) + '/' + SUBSTRING(CAST(run_date AS VARCHAR(8)), 5,2) + '/' +
RIGHT(cast(run_date AS VARCHAR(8)), 2) + ' ' +
CAST( ((run_time/10000) %100) AS VARCHAR ) + ':' + CAST( ((run_time/100) %100) AS VARCHAR ) + ':' +
CAST( (run_time %100) AS VARCHAR )AS DATETIME ) AS RunDate,
( run_duration % 100 )+ -- seconds
(((run_duration/100) % 100 ) * 60) + -- minutes in seconds
(((run_duration/10000) % 100 ) * 3600) AS RunSeconds,
CASE WHEN run_status <> 1 THEN jh.Message
ELSE ''
END AS Message
FROM MSDB..SysJobHistory jh INNER join MSDB..SysJobs sj ON jh.job_id =sj.job_id
WHERE Run_Date >=CONVERT(VARCHAR(20),GETDATE(),112)
ORDER BY sj.name,
CAST ( LEFT(CAST(run_date AS VARCHAR(8)),4) + '/' + SUBSTRING(CAST(run_date AS VARCHAR(8)), 5,2) + '/' +
RIGHT(cast(run_date AS VARCHAR(8)), 2) + ' ' +
CAST( ((run_time/10000) %100) AS VARCHAR ) + ':' + CAST( ((run_time/100) %100) AS VARCHAR ) + ':' +
CAST( (run_time %100) AS VARCHAR ) AS DATETIME )
,Step_Id
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
April 6, 2006 at 1:34 pm
If I run performance monitor and log few counters for few hours, there will be a performance impcat on the server right? I checked the errorlog and event log and I didnot find anything around that time.
what might be the problem?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply