June 24, 2010 at 12:36 am
Hi All,
Currenly I have a SQL Server 2005 instance with a few databases sitting on a 16GB RAM machine.
We realise that the server memory utlization of the machine is between 85% to 90% most of the time.
I did some research and understand that the way sql server 2005 works is that it will use as much memory as required and will free up memory only when the os require it. It won't deallocate the memory if no other process requires it. Kindly correct me if I'm wrong.
In this case, is there anyway I can find out if the server is facing memory resource issue since gauging by server memory utlization won't be accurate? We would like to do some healthcheck on it to prove that sql server doesn't have memory resource issue.
Is there any place where I can find out how much memory sql server is utlizing out of the 16GB memory as some memory of the utilized 90% i believe is used by the OS.
Please advise.
thanks
June 24, 2010 at 12:40 am
SQL will allocate as much memory as needed/available.
Configure your maximum memory allocation within SQL, leaving adequate memory for the OS and other services.
On a 16gb server, I'd configure SQL to use maximum 12gb, leaving about 4gb to the OS and other apps, but this could be different on your servers.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
June 24, 2010 at 12:56 am
Hi
It would be better if you check why memory utilisation is so high. Is it due to some badly written code or does sql really requires so much memory.
"Keep Trying"
June 24, 2010 at 12:59 am
thanks all.
I wish to check how much the sql server is utilizing now. Can advise how do i check it as i understand looking at the sqlserver.exe under task manager is inaccurate.
also from where can i check which db is utilizaing the most memory?
thanks
June 24, 2010 at 1:22 am
Here is a good script...credits to the authors...
/****************************************************************************************
NAME:DATABASE I/O STATISTICS
BY:MARK JONES
DATE:19-02-2008
PURPOSE: Provides useful statistics about Database I/O requests. Uses the function
::fn_virtualfilestats to retreieve all the database file statistics includeing
log files for all databases on a server. A number of Result sets are generated
which detail:
-ALL I/O STATS FOR ALL DATABASES
-TOP 5 DABATASES BASED ON READS + WRITES
-TOP 5 DABATASES BASED ON BYTESREAD + BYTESWRITTEN
-TOP 5 DABATASES BASED ON IoStallMS
-TOP 5 DABATASES IO STALL RATIO TO IO REQUESTS RATIO
USE:Run Against any SQL SERVER 2000 Database. (2005 not tested)
REVISION HISTORY
DateDeveloperDetails
19/02/08Mark JonesCreated
*****************************************************************************************/
----------------------------------------------------------------
-- DECLARATIONS
----------------------------------------------------------------
SET NOCOUNT ON
USE TEMPDB
----------------------------------------------------------------
-- BODY
----------------------------------------------------------------
-- Create tbale to store file I/O statistics
IF NOT EXISTS ( SELECT 1 FROM tempdb.dbo.sysobjects WHERE name = 'tbl_DatabaseFileStats' AND
type = 'U')
CREATE TABLE tempdb.dbo.tbl_DatabaseFileStats
(
DatabaseNamevarchar(255),
FileNamevarchar(255),
TimeStampnumeric(18,0),
NumberReadsnumeric(18,0),
NumberWritesnumeric(18,0),
BytesReadnumeric(18,0),
BytesWrittennumeric(18,0),
IOStallMSnumeric(18,0)
)
ELSE
TRUNCATE TABLE dbo.tbl_DatabaseFileStats
-- Use sp_MSForEachDB to scroll through each db to obtain the DBID
-- to run as a parameter in the function fn_virtualfilestats
-- Insert results into results table.
EXECUTE master.dbo.sp_msforeachdb '
DECLARE @DBIDint;
USE [?];
SET @DBID = DB_ID();
INSERT INTO tempdb.dbo.tbl_DatabaseFileStats
SELECT
DB_NAME(DBID) AS DatabaseName,
FILE_NAME(FileID) AS FileName,
TimeStamp,
NumberReads,
NumberWrites,
BytesRead,
BytesWritten,
IoStallMS
FROM ::fn_virtualfilestats(@DBID, -1);'
----------------------------------------------------------------
-- RESULTS
----------------------------------------------------------------
-- GET ALL RESULTS
SELECT '<< ALL DATABASES >>'
SELECT *
FROM
tempdb.dbo.tbl_DatabaseFileStats
ORDER BY
1,2
-- TOP 5 DABATASES BASED ON READS + WRITES
SELECT '<< TOP 5 DABATASES BASED ON READS + WRITES >>'
SELECT TOP 5
DataBaseName,
TimeStamp,
SUM(NumberReads) + SUM(NumberWrites) AS 'NumberRead/Writes',
SUM(BytesRead) AS BytesRead,
SUM(BytesWritten) AS BytesWritten,
SUM(IoStallMS) AS IoStallMS
FROM
tempdb.dbo.tbl_DatabaseFileStats
GROUP BY
DataBaseName,TimeStamp
ORDER BY
[NumberRead/Writes] DESC
-- TOP 5 DABATASES BASED ON BYTESREAD + BYTESWRITTEN
SELECT '<< TOP 5 DABATASES BASED ON BYTESREAD + BYTESWRITTEN >>'
SELECT TOP 5
DataBaseName,
TimeStamp,
SUM(NumberReads) AS NumberReads,
SUM(NumberWrites)AS NumberWrites,
SUM(BytesRead) + SUM(BytesWritten) AS 'BytesRead/Written',
SUM(IoStallMS) AS IoStallMS
FROM
tempdb.dbo.tbl_DatabaseFileStats
GROUP BY
DataBaseName,TimeStamp
ORDER BY
[BytesRead/Written] DESC
-- TOP 5 DABATASES BASED ON IoStallMS
SELECT '<< TOP 5 DABATASES BASED ON IO STALL >>'
SELECT TOP 5
DataBaseName,
TimeStamp,
SUM(NumberReads) AS NumberReads,
SUM(NumberWrites)AS NumberWrites,
SUM(BytesRead)AS BytesRead,
SUM(BytesWritten) AS BytesWritten,
SUM(IoStallMS) AS IoStallMS
FROM
tempdb.dbo.tbl_DatabaseFileStats
GROUP BY
DataBaseName,TimeStamp
ORDER BY
IoStallMS DESC
-- TOP 5 DABATASES IO STALL RATIO TO IO REQUESTS RATIO
SELECT '<< TOP 5 DABATASES IO STALL TO IO REQUESTS RATIO >>'
SELECT TOP 5
DataBaseName,
TimeStamp,
SUM(BytesRead) + SUM(BytesWritten) AS IORequests,
SUM(IoStallMS) AS IoStallMS,
SUM(IoStallMS) / (SUM(BytesRead) + SUM(BytesWritten)) AS IOStallRatio
FROM
tempdb.dbo.tbl_DatabaseFileStats
GROUP BY
DataBaseName,TimeStamp
ORDER BY
IOStallRatio DESC
----------------------------------------------------------------
-- CLEANUP & EXIT
----------------------------------------------------------------
DROP TABLE tempdb.dbo.tbl_DatabaseFileStats
Plus these views might give you an indication as well.
select * from sys.dm_os_sys_memory
select * from sys.dm_os_process_memory
select * from sys.dm_os_memory_nodes
select * from sys.dm_os_memory_node_access_stats
select * from sys.dm_os_memory_brokers
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply