Hi,
We have an SQL 2000 server with 1.2GB of RAM. Memory useage runs at 1.23GB
Total Commit Charge. Sqlservr.exe takes up about 980MB of this memory.
We have a few intensive data extractions that we would like to speed up and
the server seems to be the bottle neck.
Is there a point in adding more RAM to this server or will sqlservr.exe just
keep on taking as much memory as we give it? It is does keep taking the
memory, how do we know when enough is enough?
Svend.Svend wrote:
> Hi,
> We have an SQL 2000 server with 1.2GB of RAM. Memory useage runs at
> 1.23GB Total Commit Charge. Sqlservr.exe takes up about 980MB of this
> memory.
> We have a few intensive data extractions that we would like to speed
> up and the server seems to be the bottle neck.
> Is there a point in adding more RAM to this server or will
> sqlservr.exe just keep on taking as much memory as we give it? It is
> does keep taking the memory, how do we know when enough is enough?
> Svend.
Depends entirely on your server, your databases, and how they are used.
There's no question that more RAM is helpful. Data extraction routines
might not benefit much from a increase, however. You are likely
accessing a lot of data during the extraction. SQL Server loads this
data into cache as it is read and consumes memory during the process.
Once memory is depleted, it dumps the oldest pages as it reads new ones
from disk. If you're not going back to the same data pages a second
time, then you might benefit from more efficient disk access or added
CPU to speed up these routines depending on where the bottlenexk really
is. For normal production use, added memory helps if users continually
access the same data since it prevent SQL Server from having to pull the
information from disk.
David Gugick
Imceda Software
www.imceda.com
Showing posts with label total. Show all posts
Showing posts with label total. Show all posts
Wednesday, March 21, 2012
Monday, March 12, 2012
memory usage and configuration...
How much memory is in your system in total, and what is
your drive configuration beyond the 6 disk RAID 5 set for
SQL Server? That may not be enough spindles, but I can't
say for sure. If you stick all of your SQL Server files
(system DBs, data/index, logs, tempdb) all on the same
stripe set, you are not buying yourself anything.
So if your goal as I saw is to put everything on the one
R5 stripe, I'd say wholeheartedly no, it is not an optimal
setup at all based on my experience.
However, you need to have done some system profiling to
tell you definitively.
I've 4gb of RAM
I want to control the usage of my hard drive to keep a good performance.
it's why I want to separate the tempdb database in 1 disk or 2 stripped
disks.
And I'll keep 4 disks in raid 5 for my data. for security reasons.
Maybe I can put my indexes on a separate disk.
Config 1:
Disk0: OS + Applications (10Gb only)
Disk0,1: Stripped - 22Gb + 32 Gb - tempdb - user db indexes - Swap file
Disk3,4,5,6: Raid 5 for system DB and user DB
Config 2:
Disk0: OS + Swap + Applications (10Gb only)
Disk0: tempdb (22gb)
Disk1: indexes (32gb)
Disk3,4,5,6: Raid 5 for system DB and user DB
each disk is a 32gb disk
what do you think about this?
which config is better?
or what is your recommendation?
Also, what is the swap file size required?
"Allan Hirt" <anonymous@.discussions.microsoft.com> a crit dans le message
de news:5ab501c474a8$a67500b0$a601280a@.phx.gbl...
> How much memory is in your system in total, and what is
> your drive configuration beyond the 6 disk RAID 5 set for
> SQL Server? That may not be enough spindles, but I can't
> say for sure. If you stick all of your SQL Server files
> (system DBs, data/index, logs, tempdb) all on the same
> stripe set, you are not buying yourself anything.
> So if your goal as I saw is to put everything on the one
> R5 stripe, I'd say wholeheartedly no, it is not an optimal
> setup at all based on my experience.
> However, you need to have done some system profiling to
> tell you definitively.
your drive configuration beyond the 6 disk RAID 5 set for
SQL Server? That may not be enough spindles, but I can't
say for sure. If you stick all of your SQL Server files
(system DBs, data/index, logs, tempdb) all on the same
stripe set, you are not buying yourself anything.
So if your goal as I saw is to put everything on the one
R5 stripe, I'd say wholeheartedly no, it is not an optimal
setup at all based on my experience.
However, you need to have done some system profiling to
tell you definitively.
I've 4gb of RAM
I want to control the usage of my hard drive to keep a good performance.
it's why I want to separate the tempdb database in 1 disk or 2 stripped
disks.
And I'll keep 4 disks in raid 5 for my data. for security reasons.
Maybe I can put my indexes on a separate disk.
Config 1:
Disk0: OS + Applications (10Gb only)
Disk0,1: Stripped - 22Gb + 32 Gb - tempdb - user db indexes - Swap file
Disk3,4,5,6: Raid 5 for system DB and user DB
Config 2:
Disk0: OS + Swap + Applications (10Gb only)
Disk0: tempdb (22gb)
Disk1: indexes (32gb)
Disk3,4,5,6: Raid 5 for system DB and user DB
each disk is a 32gb disk
what do you think about this?
which config is better?
or what is your recommendation?
Also, what is the swap file size required?
"Allan Hirt" <anonymous@.discussions.microsoft.com> a crit dans le message
de news:5ab501c474a8$a67500b0$a601280a@.phx.gbl...
> How much memory is in your system in total, and what is
> your drive configuration beyond the 6 disk RAID 5 set for
> SQL Server? That may not be enough spindles, but I can't
> say for sure. If you stick all of your SQL Server files
> (system DBs, data/index, logs, tempdb) all on the same
> stripe set, you are not buying yourself anything.
> So if your goal as I saw is to put everything on the one
> R5 stripe, I'd say wholeheartedly no, it is not an optimal
> setup at all based on my experience.
> However, you need to have done some system profiling to
> tell you definitively.
memory usage and configuration...
How much memory is in your system in total, and what is
your drive configuration beyond the 6 disk RAID 5 set for
SQL Server? That may not be enough spindles, but I can't
say for sure. If you stick all of your SQL Server files
(system DBs, data/index, logs, tempdb) all on the same
stripe set, you are not buying yourself anything.
So if your goal as I saw is to put everything on the one
R5 stripe, I'd say wholeheartedly no, it is not an optimal
setup at all based on my experience.
However, you need to have done some system profiling to
tell you definitively.I've 4gb of RAM
I want to control the usage of my hard drive to keep a good performance.
it's why I want to separate the tempdb database in 1 disk or 2 stripped
disks.
And I'll keep 4 disks in raid 5 for my data. for security reasons.
Maybe I can put my indexes on a separate disk.
Config 1:
Disk0: OS + Applications (10Gb only)
Disk0,1: Stripped - 22Gb + 32 Gb - tempdb - user db indexes - Swap file
Disk3,4,5,6: Raid 5 for system DB and user DB
Config 2:
Disk0: OS + Swap + Applications (10Gb only)
Disk0: tempdb (22gb)
Disk1: indexes (32gb)
Disk3,4,5,6: Raid 5 for system DB and user DB
each disk is a 32gb disk
what do you think about this?
which config is better?
or what is your recommendation?
Also, what is the swap file size required?
"Allan Hirt" <anonymous@.discussions.microsoft.com> a crit dans le message
de news:5ab501c474a8$a67500b0$a601280a@.phx.gbl...
> How much memory is in your system in total, and what is
> your drive configuration beyond the 6 disk RAID 5 set for
> SQL Server? That may not be enough spindles, but I can't
> say for sure. If you stick all of your SQL Server files
> (system DBs, data/index, logs, tempdb) all on the same
> stripe set, you are not buying yourself anything.
> So if your goal as I saw is to put everything on the one
> R5 stripe, I'd say wholeheartedly no, it is not an optimal
> setup at all based on my experience.
> However, you need to have done some system profiling to
> tell you definitively.
your drive configuration beyond the 6 disk RAID 5 set for
SQL Server? That may not be enough spindles, but I can't
say for sure. If you stick all of your SQL Server files
(system DBs, data/index, logs, tempdb) all on the same
stripe set, you are not buying yourself anything.
So if your goal as I saw is to put everything on the one
R5 stripe, I'd say wholeheartedly no, it is not an optimal
setup at all based on my experience.
However, you need to have done some system profiling to
tell you definitively.I've 4gb of RAM
I want to control the usage of my hard drive to keep a good performance.
it's why I want to separate the tempdb database in 1 disk or 2 stripped
disks.
And I'll keep 4 disks in raid 5 for my data. for security reasons.
Maybe I can put my indexes on a separate disk.
Config 1:
Disk0: OS + Applications (10Gb only)
Disk0,1: Stripped - 22Gb + 32 Gb - tempdb - user db indexes - Swap file
Disk3,4,5,6: Raid 5 for system DB and user DB
Config 2:
Disk0: OS + Swap + Applications (10Gb only)
Disk0: tempdb (22gb)
Disk1: indexes (32gb)
Disk3,4,5,6: Raid 5 for system DB and user DB
each disk is a 32gb disk
what do you think about this?
which config is better?
or what is your recommendation?
Also, what is the swap file size required?
"Allan Hirt" <anonymous@.discussions.microsoft.com> a crit dans le message
de news:5ab501c474a8$a67500b0$a601280a@.phx.gbl...
> How much memory is in your system in total, and what is
> your drive configuration beyond the 6 disk RAID 5 set for
> SQL Server? That may not be enough spindles, but I can't
> say for sure. If you stick all of your SQL Server files
> (system DBs, data/index, logs, tempdb) all on the same
> stripe set, you are not buying yourself anything.
> So if your goal as I saw is to put everything on the one
> R5 stripe, I'd say wholeheartedly no, it is not an optimal
> setup at all based on my experience.
> However, you need to have done some system profiling to
> tell you definitively.
Wednesday, March 7, 2012
Memory Problem, please help
My SQL server has 3.68 G memory in total. It has no other application on it, the server is not budy.
The current memory usage of SQL server stays at 1.8, in sys.dm_os_memory_clerks, I see
MEMORYCLERK_SQLBUFFERPOOL Default 0 0 400 1636152 1636152
MEMORYCLERK_SQLCLR Default 0 720 1088 101888 22512
MEMORYCLERK_SQLSTORENG Default 0 1480 104 4480 4480
MEMORYCLERK_SQLCLRASSEMBLY Default 0 0 0 4208 4208
OBJECTSTORE_LOCK_MANAGER Lock Manager : Node 0 0 1912 0 4096 4096
The problem is I can not run a CLR function which read a xml file from harddisk, it gives error
.NET Framework execution was aborted by escalation policy because of out of memory.
I tried before, if I restart my SQL server, the function works, but what is the problem? it doesn't look like it is wrong that SQL server allocate 1.8 G memory, and the SQLCLR is only using 24M, it said if there is not enough memory, SQLCLR will upload appdmain to release memory, so even SQLCLR can reused the 24M , it should be able to load my file, which is only 1M/
Any idea?
thanks
It looks like you are running 32-bit SQL Server 2005. If so, you need to make sure that your boot.ini file has the /3GB switch in the boot.ini file (at the root of your C: drive). Then you need to enable AWE in SQL Server 2005. I would also suggest that you give the SQL Server Service Account the "Lock Pages in Memory" right.
This will allow SQL Server to use more than 2GB of memory and will allow SQL to control paging itself.
|||Nice, really helpful, I will try it
thanks!
|||Hi David,
Not trying to be contentious here but AWE is only useful if your server has more than 4GB of RAM which is not the case here.
Do you see any errors in the SQL Server error log?
regards
Jag
|||Hi
Not trying to be contentious here but AWE is only useful if your server has more than 4GB of RAM which is not the case here.
Is there any errors in the SQL Server error log.
regards
Jag
|||The problem you describe is a known bug in SQL Server 2005 SP1 with a hot fix available:
http://support.microsoft.com/kb/928083
However the fix list for SQL Server 2005 SP2 indicates that the fix is included in the new service pack.
-
The KB article suggests that a workaround is to not use the context connection in your CLR code but rather create a standard connection object.
|||thanks for your suggestions.
My server doesn't have 4G memory, I did think about it when I applied the changes.
My SQL server looks ok now, after reboot that day after applied the changes. It consume less than 150M now.
I do see a lot of message in SQL log, like
Date 09/03/2007 4:03:41 PM
Log SQL Server (Current - 09/03/2007 8:44:00 PM)
Source spid23s
Message
The query notification dialog on conversation handle '{C7311ACC-9ACE-DB11-9441-000D5670268E}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8494</Code><Description>You do not have permission to access the service 'SqlQueryNotificationService-a1e3d318-87b4-4ae6-84e7-8ef2f04241c1'.</Description></Error>'.
not sure what it affects, but it is not good since it logs a dozen entried every minute, any suggestion what causes that?
thanks
Subscribe to:
Posts (Atom)