Home > Performence Tuning > Resolve the memory issue quickly at SQL server 2008

Resolve the memory issue quickly at SQL server 2008


Memory issue is very common stress for DBA but SQL server 2008 introduce new DMV which we can use to handle the issue quickly. Need to follow the below step to resolve the issue quickly.

sys.dm_os_sys_memory This DMV was introduced in SQL Server 2008 with an intention of making life easier for all those systems where monitoring memory usage was a mandatory requirement due to the nature of deployment. The “system_memory_state_desc” column output of this DMV has three common states:

  • · Available physical memory is high
  • · Available physical memory is low
  • · Physical memory usage is steady

Step 1

select * from sys.dm_os_sys_memory where system_memory_state_desc=’Available physical memory is low’. We can use the script to get the notification whenever memory use by SQL server high and take some proactive action before database stop response.

Step 2: If we are getting alert like that SQL server Available physical memory is low then we can run the below script to know which database using most memory

–Memory Occupied by each Database

SELECT

(CASE WHEN ([is_modified] = 1) THEN ‘Dirty’ ELSE ‘Clean’ END) AS ‘Page State’,

(CASE WHEN ([database_id] = 32767) THEN ‘Resource Database’ ELSE DB_NAME (database_id) END) AS ‘Database Name’,

COUNT (*) AS ‘Page Count’

FROM sys.dm_os_buffer_descriptors

GROUP BY [database_id], [is_modified]

ORDER BY [database_id], [is_modified];

GO

Step 3: To freed up the memory we can use the below script together.

DBCC FREESYSTEMCACHE (‘ALL’) WITH MARK_IN_USE_FOR_REMOVAL;

DBCC FREESESSIONCACHE WITH NO_INFOMSGS;

GO

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE;

Advertisements
Categories: Performence Tuning
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: