MS SQL Server :: Can't Get SQL Server 2005 To Use More Memory

Memory In Sql Server 2005
Hi

I would like to know how much of memory is taken by sql server 2005 entprise edition 64 bit during sql server startup in default configuration.....(4GB RAM being used).

i also want to know about montioring the performance of sql server 2005.Should i continue to use performance monitor (perfmon) or is there any new feature in slq server 2005 which provides performance monitoring.

Thanks in advance

View Replies !

Memory Configurations For SQL Server 2005 EE X64 With SQL Server 2000 EE X32 On Windows Server EE X64 Server
My server is a dual AMD x64 2.19 GHz with 8 GB RAM running under Windows Server 2003 Enterprise Edition with service pack 1 installed. We have SQL 2000 32-bit Enterprise installed in the default instance. AWE is enabled using Dynamically configured SQL Server memory with 6215 MB minimum memory and 6656 maximum memory settings.

I have now installed, side-by-side, SQL Server 2005 Enterprise Edition in a separate named instance. Everything is running fine but I believe SQL Server2005 could run faster and need to ensure I am giving it plenty of resources. I realize AWE is not needed with SQL Server 2005 and I have seen suggestions to grant the SQL Server account the 'lock pages in memory' rights. This box only runs the SQL 2000 and SQL 2005 server databases and I would like to ensure, if possible, that each is splitting the available memory equally, at least until we can retire SQL Server 2000 next year. Any suggestions?

View Replies !

SQL Server 2005 Memory Management
Hi all,

I needed to load some tables in memory on startup because of performance reasons.
I'm using "select * from <table>", but there are few questions:

1. How to pin already selected data in memory ? (DBCC PINTABLE doesn't work for 2005)

2. How to put index data in memory ? (do you read document(s) for advance memory management - index data caching ?)

3. How to pin index data in memory ? (otherwise sound very bad - table data in fast memory, index data - in slow disks)

Thanks in advance:

Siol En

View Replies !

SQL Server 2005 Memory Management

Hi all,
I needed to load some tables in memory on startup because of performance reasons.
I'm using "select * from <table>", but there are few questions:
1. How to pin already selected data in memory ? (DBCC PINTABLE doesn't work for 2005)
2. How to put index data in memory ? (do you read document(s) for advance memory management - index data caching ?)
3. How to pin index data in memory ? (otherwise sound very bad - table data in fast memory, index data - in slow disks)

Thanks in advance:
Siol En

View Replies !

SQL Server 2005 Std Hogging Way Too Much Memory
Hi:

I'm running Windows Small Business Server 2003 Premium R2. I installed SQL Server 2005 Standard Edition on this PC. I'm using Std Edition instead of the Workgroup Edition that comes with SBS since I like the additional features in the Std Edition.

I'm a kind of a newbie to Windows Server administration and SQL Server administration although I've written lots of SQL queries, stored procedures, etc.

I have 2GB of RAM on this PC. When I looked in Task Manager, I saw that SQL Server was using somewhere over 1GB of RAM. So, I opened up SQL Management Studio, right clicked on the server node, clicked to get to the memory configuration page and saw that SQL Server was set to use all 2GB of RAM on the PC. I changed that to 500MB(500000000, or 476MB) and decided to reboot the server. When the server came back up, I forgot to check SQL Server's new RAM usage. The server ran without interruption for over 24 hours. Now, when I checked SQL Server's memory usage in Task Manager, I can see it's using over 750MB of RAM.

How do I fix this?

I have Exchange Server 2003 running on this PC as well.

Thx.

View Replies !

SQL Server 2005 DTS/Memory Usage
We have an application that we currently run on SQL Server 2000 that works by creating a DTS package that it then executes.

Due to performance reasons, we have been considering switching to 2005, for a few reasons. Can anyone confirm clarify the following?

1) SQL Server 2000 caps RAM usage at 2GB, whereas SQL Server 2005 is only limited by the OS - RAM usage is a big current issue for us, so if upgrading to 2005 would solve this it would help a lot. Can anyone confirm my understanding of this?
2) Would using the legacy DTS in SQL Server 2005 take advantage of this RAM difference, or is it running on the old 2000 engine and only able to use the 2GB?

Thanks for any help.

View Replies !

Planning For SQL Server 2005: 64-bit, OS, Processor, Memory
I was browsing Microsoft's SQL Server site, looking forsome details about SQL Server 2005. Didn't find whatI was looking for...I'm thinking about moving an existing SQL Server 2000workload to a new box, using SQL Server 2005, andmaybe the 64-bit version.My questions are:1. What is the current target date for release of SQL Server 2005?Will 64-bit ship when 32-bit ships?2. Will 64-bit SQL Server 2005 require a special versionof Windows Server 2003 (e.g. Windows Server 2003 Enterprise x64)?Will it work with both Intel and AMD processors?3. How many CPUs, and how much memory, will be supported bySQL Server 2005, 32-bit and 64-bit, on each OS that can runSQL Server 2005.I'm looking for a chart here, something like the chart onpage 117 of Kalen Delaney's "Inside SQL Server 2000" book.SQL Server 2005 SQL Server 2005Feature Enterprise 32-bit Enterprise 64-bit------------------- ----------------- -----------------CPUs supportedWin Srvr 2003:Win Srvr 2003 Adv:Win Srvr 2003 Ent x64:Physical memorysupportedWin Srvr 2003:Win Srvr 2003 Adv:Win Srvr 2003 Ent x64:Has Microsoft published this info, and I just can find it?

View Replies !

What Is The Memory Footprint Of Enabling Clr In Sql Server 2005?
we wonder about the tradeoffs of t-sql vs clr in sql server 2005 especially from a memory perspective. Is there documentation available on this subject?

View Replies !

SQL Server 2005 Doesn't Allocate Available Memory
running Sql Server 2005 SP2 on Windows 2003 Server SP1 with 2GB RAM. After start-up, the sqlservr.exe does only take up around 100 MB of RAM, and it stays roughly there even if the DB is used heavily. This leads to very poor performance, even timeouts on simple querys.

In the task manager, I see that of the 2 GB of RAM, more than 1 GB is still available. I don't understand why SQL Server won't take it?

As a test, I configured the min and max amount of RAM SQL Server should used both to 1024 MB and restarted the service - but it is still the same picture. It won't take more than around 100 MB.

The server has just been restarted, but the problem remains.

BTW there is also an instance of SQL 2000 on the same machine. It shows the same behaviour - I even checked the "reserve phyiscal memory" checkbox there, but it stays on a very low number (50 MB) and doesn't adhere to the supposed size.

thanks and best regards,
- Urs

View Replies !

SQL Server 2005 Hogging Processor And Not Using Memory
Hi,

We have recently tested upgrading our web service from sql 2000 to 2005 sp1. The upgrade went smoothly enough, however we now have the problem of the sqlserver.exe process taking 90-100 % of the processors time, but using only 100 MB of memory.

We have 6GB available and we are running the enterprise editions of Windows 2003 and SQL 2005.

Machine specs,

DL380 G2, 2 X 2.8 Ghz Zeon, 6GB ram, Raid 5, database partition of 140 GB, Log partition of 35 GB.

Db is 25 GB, Log is 12 GB. Largest table has 32 million rows.

Any help would be greatly appreciated.

Rob

View Replies !

Memory Stress Test In SQL Server 2005
I have a Windows 2003 Server running SQL 2005. The server has 32 GB of memory and I have enabled AWE in SQL. I have also configured the min and max SQL memory as 1 GB and 28 GB, respectively. However, this server currently has very low activity so I'm not sure whether my AWE-related changes worked. SQLSERVR.EXE process takes up about 100 MB of memory. Is there any tool or scripts that I can use to memory stress SQL to confirm that AWE is really in effect ?

View Replies !

SQL 2005 Server Performance And Maximum Memory Pro
A query was taking 20 seconds and consuming 70% CPU takes only 1 second after setting Maximum Memory property to 2048 MB - why?

Server:
OS Microsoft(R) Windows(R) Server 2003, Enterprise Edition
Version5.2.3790 Service Pack 1 Build 3790
8 GB memory
Two Dual-core AMD Opteron 285 2.6GHz Processors
Server is not configured for AWE
Fiber channel connection to EMC Clarion - two LUNs - one for MDF, one for LDF

SQL 2005
SQL 2005 32 bit Standard Edition - SP1 (version 9.0.2047)
Three instances installed on server - only one instance in use
Binaries and system databases on local mirrored disk
Database file (MDF) on one EMC LUN - dedicated physical drives
Log file (LDF) on one EMC LUN - dedicated physical drives

Query in question:

SELECT TOP 10 Address.Address1, Address.Address2, Address.City, Address.County, Address.State, Address.ZIPCode, Address.Country, Client.Name,
Quote.Deleted, Client.PrimaryContact, Client.DBA, Client.Type, Quote.Status, Quote.LOB, Client.ClientID, Quote.QuoteID, Quote.PolicyNumber,
Quote.EffectiveDate, Quote.ExpirationDate, Quote.Description, Quote.Description2, Quote.DateModified, Quote.DateAccessed, Quote.CurrentPremium,
Quote.TransactionDate, Quote.CreationDate, Quote.Producer FROM ((Client INNER JOIN Address ON Client.ClientID = Address.ClientID) INNER JOIN Quote ON
Client.ClientID = Quote.ClientID) WHERE (Quote.Deleted = 0) AND ((Address.AddressType)='Mailing') ORDER BY Client.Name

Address table - 161,075 rows
Client table - 161,634 rows
Quote table - 59,145 rows

With default maximum memory setting (2,147,483,647 MB) - query runs in 20 seconds and consumes over 70 % of the CPU.

After changing maximum memory setting to 2048 MB, query runs in less than 1 second.

Question is:
What is the best practice for setting the minimum and maximum memory settings for SQL 2005?
What can be monitored to identify the cause of these type of issues - using profiler, PerfMon, other tool?

Thanks

View Replies !

SQL Server 2005 Using All Memory After Installing Cumulative Update 5
Hi,

I have installed cumulative update 5 (version 3215) for SQL Server 2005 SP2, and now SQL service uses 120MB of memory according to task manager. but that's not all, after this install the PF usage went up to 7.5 GB, and now the server has about 400MB of available memory. How do I know this is SQL server? if I turn the service off, the available memory goes up to 7GB and the PF usage down to 500MB

It seems like SQL Server has reserved the whole memory as the process is using 120MB and not 7.5GB... as the PF shows.

The machine has 4 Opteron X2, 8GB of RAM. MS Windows 2003 Server R2 Enterprise, SQL Server AWE option ON

any help will be appreciated.

View Replies !

Memory Settings In SQL Server 2005 Standard Edition
Hi,

I'm having trouble finding the optimum memory settings for SQL Server 2005. I have 4 instances running on a macine with 8 dual-core processors and 18GB of RAM. I have tried the following memory settings so far:

No maximum - one instance used about 12GB of RAM so then the others struggled
Maximum of 4GB each (2GB left for Windows) - meant that 3 instances could be using 1GB each and then another at 4GB and needing more whilst 9GB sat unused.
Minimum of 2GB each - one instance would use up 10GB and then never give any back to the other instances.

I also find that setting a maximum then just causes a high amount of paging. What I would like to do is have each instance use a minimum amount, say 1GB, and then have each instance use a maximum of 13GB (3GB for other 3 instances running at a minimum level and 2GB for Windows). This 13GB should then be released and allocated to another instance when necessary, assuming it is no longer all being used. I do not want paging to occur if an instance reaches 13GB.

How do I go about configuring SQL Server to behave like this? Is it possible?

Thanks.

View Replies !

SQL 2005 Server Performance And Maximum Memory Property
A query was taking 20 seconds and consuming 70% CPU takes only 1 second after setting Maximum Memory property to 2048 MB - why?

Server:

OS Microsoft(R) Windows(R) Server 2003, Enterprise Edition

Version 5.2.3790 Service Pack 1 Build 3790

8 GB memory

Two Dual-core AMD Opteron 285 2.6GHz Processors

Server is not configured for AWE

Fiber channel connection to EMC Clarion - two LUNs - one for MDF, one for LDF

SQL 2005

SQL 2005 Standard Edition - SP1 (version 9.0.2047)

Three instances installed on server - only one instance in use

Binaries and system databases on local mirrored disk

Database file (MDF) on one EMC LUN - dedicated physical drives

Log file (LDF) on one EMC LUN - dedicated physical drives

Query in question:

SELECT TOP 10 Address.Address1, Address.Address2, Address.City, Address.County, Address.State, Address.ZIPCode, Address.Country, Client.Name,

Quote.Deleted, Client.PrimaryContact, Client.DBA, Client.Type, Quote.Status, Quote.LOB, Client.ClientID, Quote.QuoteID, Quote.PolicyNumber,

Quote.EffectiveDate, Quote.ExpirationDate, Quote.Description, Quote.Description2, Quote.DateModified, Quote.DateAccessed, Quote.CurrentPremium,

Quote.TransactionDate, Quote.CreationDate, Quote.Producer FROM ((Client INNER JOIN Address ON Client.ClientID = Address.ClientID) INNER JOIN Quote ON

Client.ClientID = Quote.ClientID) WHERE (Quote.Deleted = 0) AND ((Address.AddressType)='Mailing') ORDER BY Client.Name

Address table - 161,075 rows

Client table - 161,634 rows

Quote table - 59,145 rows

With default maximum memory setting (2,147,483,647 MB) - query runs in 20 seconds and consumes over 70 % of the CPU.

After changing maximum memory setting to 2048 MB, query runs in less than 1 second.

Question is:

What is the best practice for setting the minimum and maximum memory settings for SQL 2005?

What can be monitored to identify the cause of these type of issues - using profiler, PerfMon, other tool?

Thanks

View Replies !

Memory Usage Of SQL Server 2005 Mobile Edition
Hi forum readers,

we are working on a release 2.0 mobile solution right now. In our version 1.0 we did not have to worry about memory issues as our application was the only application running on our target devices (e.g. T-Mobile MDA Compact II Pocket PCs, WM2005).
Now we need to share the available memory with others. As our application relies on its SQL Server 2005 Mobile Edition database we are wondering about memory usage of that server.

We know that a Pocket PC divides its memory into Storage and Program. If our application uses a 5 MB database and 1.5 MB for DLLs and it's exe-file. These files reside in the storage space when not loaded. When the application starts up it is loaded in the program memory. What happens to the 5 MB database file? Is is loaded into Program memory as well? Are only portions of that file loaded? Or is nothing loaded at all?

Does anyone have a deeper insight into that server an can answer my questions.

Best regards,
Tobias

View Replies !

Performance Issues Total Server Memory Vs Target Server Memory
Hi

I did a load testing and found the following observations:

1. The Memory:Pages/sec was crossing the limit beyond 20.

2. The Target Server Memory was always greater than Total Server Memory

Seeing the above data it seems to be memory pressure. But I found that AvailableMemory was always above 200 MB. Also Buffer Cache HitRatio was close to 99.99. What could be the reason for the above behavior?

View Replies !

SQL Server 2005 Workgroup Edition In SBS 2003 R2 Max Memory Question
We have only a small busines and haven't got too much experience with servers and now have a proliant server coming in with SBS 2003 R2 Premium with the 4 GB max memory that SBS 2003 can handle according to the specs.

We weren't planning on using the SQL 2005 worlgroup edition up till now, but now we might. According to the specs of SQL server 2005 workgroup edition however, it has a max RAM of 3GB!

Is this going to be a problem and should we keep using our previous DB, or can we migrate toward SQL server with the 4 GB of RAM?

View Replies !

Setting Memory Values For SQL 2005 Standard (32 Bit) On Windows 2003 Server 64 Bit.
Hi,

I am testing SQL 2005 Standard (32 bit) on a Windows 2003 Server 64 bit with 8GB of RAM? Should I enable the AWP Setting or not and should I change the maximum server memory (currently saying 2GB)?

Thanks!
Tom

View Replies !

How To Make Use Of 4 GB Memory For SQL Server 2005 Standard + Windows 2003 Stardard
We just upgraded the memory of our database server from 2GB to 4GB. And its OS is windows 2003 standard and sql is 2005 standard edition. According to microsoft(http://msdn2.microsoft.com/en-us/library/ms143685.aspx and http://msdn2.microsoft.com/en-us/library/aa366778.aspx#physical_memory_limits_windows_server_2003), this configuration should allow database use up to 4 GB memory. My question is : Do I need to do anything(such as: adjust the maximum memory in management studio) to let our server be able to consume this extra 2 GB memory?

Does this solution http://support.microsoft.com/kb/274750 apply for SQL server 2005?

View Replies !

SQL SERVER EXPRESS BETA1 (2005) IS NOT RUNNING :SHARED MEMORY Provider Error
I'm facing several problems that I tryied to short out by msdn but could not get solution
I'm using visual studio .NET 2005 Beta(WHIDBEY),and
problem:1 sql server2005 Express edition is not running on My stand alone machine(AMD Athlon Xp 2000+,512 MB DDR Ram,CPU Clock speed 1.67 GHz)
Whenever I try to connect to SqlExpress using the server explorer in the VisualStudio.NET 2005 using follwing option in connection properties Dialog Box :
1.server name: the name of my computer(I'm using the Default Instance of SQLEXPRESS 2005) 2.Enter Information to log on to the server:usewindowsNt Integrated Security3.when I go to select the inuilt data base like masters it gives following Error message:-
"Shared memory provider:Specifed Module could not be Found"
problem:2In the process of making of Data Access component,I made an assembly named "TNB.DataLayer.CSharp.dll" and putted it at two place one in the ..in folder and another in the folder having the solution file .Now I want this asssenbly to put in Global Assembly cache so that parts of my project (a window Application and a Web Application) can access the database using this TNB.DataLayer.CSharp.dll Now i need to make my assembly a strong name so I used following at the .NET command Prompt sn.exe -k TNBKeyPair.snkand put this file in the folder where My solotion .sln file exist.now as I have the keypair file,I tryied to sign the assembly TNB.DataLayer.CSharp.dll with this key for this when I added the following in to the AssemblyInfo.cs file[assembly: AssemblyKeyFileAttribute("TNBKeyPair.snk")]
warning on bulding the solution is
Warning 1: Use command line option '/keyfile' or appropriate project settings instead of 'AssemblyKeyFileAttribute' D:gopaldot net newTNBTNBPropertiesAssemblyInfo.cs 19 11
and when I use the /keyfile:TNBKeyfile at the command prompt I got another Error message:The file name, directory name, or volume lable syntax is incorrect.
and finally I'm not able to sign the Dll and to put it in global assembly to use from different envioronments.
so please help me in solving this problem.

Thanks!
ur faithfully

View Replies !

SQL Server 2005 Service Broker - Activation Stored Procedure Memory Usuage
We are looking for some guidance with an issue we have picked up with our implementation of Service Broker here on the ABSA Capital project and I am hoping you can help or point us in the direction of someone.

The architecture we have implemented for service broker is to make use of an Activation stored procedure on two queues (1 SP per queue) to process the messages received. What we have found is that the activation stored procedure runs on a background session and its CPU time and memory just grows to the point where it brought one of our UAT servers to a grinding halt.

Is there anyway we can reduce the memory consumption of the activation stored procedure or is this one of those things that still need to be ironed out in Service Broker?

View Replies !

Sql Server 2000 Using Less Memory After More Memory Added
sql server 2000 is running on windows server 2003 ... 4gb of memory on server .... 2003 was allocated 2.3gb nd sql server was allocated (and using all of it) 1.6gb for total of approx 4gb based on idera monitor software ... all memory allocated betweeen the OS and sql server .... then 4 more gb of memory added for total now of 8g ... now idera monitor shows 1.7gb for OS and 1.0 gb for sql server ..... 'system' info shows 8gb memory with PAE ... so I assume that the full 8gb can now be addressed .... why are less resources being used now with more total memory .... especially sql server ..... i thought about specifying a minimum memmry for sql server but i amnot convinced that would even work since it seems that this 1gb limit is artificial .... it it used 1.6 gb before why would it not use at least that much now ??

thank you

View Replies !

&&"Not Enough Memory For Operation&&" - Sql Server 2005
My Microsoft Visual Basic 6.0 application uses Crystal Reports 8.0 as the
reporting development tool. After the application is distributed to a client computer, when the application attempts to load a report the following error message appears:

"Not enough memory for operation"
NOTE :
The report file uses Stored procedure to process the query and display data. And the client computer is using SQL Server 2005. However, if we install SQL Server 2000, the report generates without any problem.
Moreover in client computer niether MsDtsSrvr is not seen in Running process list (Task manager) nor I am unable to start MsDtsSrvr.Kindly help me on this issue.

Thanks in advance,
Nethesh Kannan

View Replies !

64 Bit SQL 2005 Memory
Hello,I have two servers running SQL 2005.1. Windows Server 2003 x32 with SQL 2005 Enterprise x32 with 3GB memory2. Windows Enterpise 2003 x64 with SQL 2005 Enterprise x64 with 8GBmemoryUsing taskmgr.exe my 32 bit machine shows 700mb being used bysqlservr.exe, which I expect.However, my 64 bit machine shows sqlservr.exe using 27 mb, which seemsreally low. After I build my data warehouse, I see memory usage at1.5GB on my SQL 2000 machine, so I'd expect more than 27mb used on SQL2005 after I build my warehouse.Additionally, taskmgr says PF Usage is 8.33 GB. Where is the missingmemory, why is SQL 2005 x64 using so little memory??The memory configuration for the x64 server is 0 minimum and 2147483647max KB (defaults)Any thoughts?TIARob

View Replies !

SQL 2005 Not Using Memory
Hi I am using SQL 2005 64 Enterprise edition with 30GB memory.
I have allowed SQL to use min 0 memory and maximum 20GB but sql is only showing it can use 100MB of memory

Anyone know the answer
Ap

View Replies !

SQL 2005 64 Bit Only See 2GB Of Memory
We are running Win2003 SP1 64 bit and SQL Server 2005 64 bit. We are only able to set SQL's memory allocation to 2GB even though we have 6GB installed. How do we make more memory available to SQL Server?

View Replies !

MS SQL 2005 Running On Low Memory On X64 OS
Hello

We have a problem with MS SQL 2005 Standard on a Windows 2003 x64 Box.

Server MS 2003 Server x64 R2
Quad Core 2.13GHz
Memory: 12 GB

MS Sql 2005 Standard, Sp2

The Sql process uses only 80MB of RAM (from 8 GB) so this machine is very slow. We set the min and max Memory in SQL without success. SQL ist very slow. Has anyone here some hint's to solve this problem? Thank'.

rainbow1

View Replies !

Help! SQL 2005 Memory Issues
We have been using SQL 2005 for quite sometime however recently I have been seeing memory issues. SQL will grow to use the maximum available! I have set min 1024MB and max to 15360MB leaving approx 5GB for OS.

Windows 2003 64 / SQL2005 64
Lock pages to memory - enabled
Min and Max - 1024, 15360
AWE not checked

We have recently added link server using OLEDB DB2 provider. Could this be the issue?

View Replies !

SQL 2005 X64 ? Leverage Memory
Hi,

Can anyone help ? we are looking at running SQL 2005 on a EMT64 compatible Quad XEON server. If we move to a 64bit OS is it possible to more memory for SQL directly without having to configure the server with the /PAE AWE as we do currently in SQL 2000

Any help would be much appreciated.

Thanks

Marcus

View Replies !

AWE Memory On SQL 2005 Standard
Is AWE enabled on SQL 2005 Standard? We currently have it setup using "locked pages in memory" for a domain account and haven't noticed any performance problems.

This article however states that only the SQL2005 Enterprise Edn supports "locked pages in memory". Can anyone confirm this?

http://support.microsoft.com/kb/918483

If I'm mistaken, how should the memory options be set for a server with 8gb ram running on SQL 2005 Standard which runs on Win2003 Enterprise? Should the min and max memory be set or left alone as default?

View Replies !

Need More Available Memory For SQL 2005...options?
My organization is currently migrating about 35 databases from SQL 2000 to SQL 2005. Most of the databases are small with the largest being about 6gb. These databases are not very transaction intensive. We are about half way through as we started the project a year ago. We purchased new hardware for this SQL 2005 so it is a side-by-side migration and it has been going well so far. I made one oversight which I think will come back to haunt us down the line. I installed Windows Server 2003 Standard R2 Edition and of course now I am considering putting more RAM in the server. We have 4 GB of RAM in the server and of course Standard Edition will not address more 4GB of physical ram. I have not seen any memory pressure yet but as databases are migrated over the amount of physical RAM drops. My buffer hit cahce ratio and page life expectancy performance counters are fine so far. The server is a brand new HP DL 380 G5 with with tons of disk and processing power, but only 4GB of RAM. I can't migrate to another server with more ram obviously.

1. What would you do in a situation like this?

2. I could do an in-place upgrade to Windows Server 2003 Enterprise R2. Has anyone attempted an in-place upgrade? I have no applications
on this server, just SQL 2005 Standard.

3. Would the /3gb switch be an option? I have heard mixed reviews on this one.

As far as database optimization, %75 of the databases are third party so we have little flexibility in tuning them or redesigning
tables.

Thanks,
Alex

View Replies !

Is SQL 2005 Memory Limitation &&<= OS Memory Limitation?
If I install SQL 2005 Standard on Windows 2003 Standard, is SQL limited to 4 gigs of physical RAM?

I'm planning a new system that will run SQL 2005 Standard edition on a Windows 2003 Standard platform. The spec calls for 8 GB of RAM. My experience would lead me to suspect I need to install Windows 2003 Enterprise to take advantage of all the installed memory.

View Replies !

Memory Limitations, 2005 Standard
Hi all,I have recently implemented a SQL 2005 cluster using SQL 2005 Standardon Windows 2003 Enterprise edition.Both nodes have 4GB of RAM and according to the datasheet, SQL 2005Standard can support the OS maximum memory amount and Win2K3 EntEdition can support 64GB!However, in Enterprise Manager, if I go to the "Memory" tab of theinstance properties I can't increase the memory beyond 2147483647(which I assume is around 2GB).I don't have AWE enabled but am unsure as to the ramifications of this.Any advice anyone could provide would be greatly appreciated.Many thanks,Ian

View Replies !

2005 X64 Cluster - Memory Settings
So I'm in the middle of building SQL Server 2005 on my new cluster hardware. I've got all the goodies this time around -4x PowerEdge 68004x dual core x64 procs in each box32GB ram in each boxWindows Server 2k3 R2 Enterprise x64 SP2SQL 2005 Enterprise x64 SP2Active Active Active Passive cluster (4 boxes, 3 instances)dedicated data, log, and tempDB 4Gb 15k fiber channel SAN Volumes for each instanceNow, this is the first x64 box I've configured, so I'm looking for some optimization tips for a couple things.First one's memory. On our 32bit systems, I always added the /PAE switch to the boot.ini file and enable AWE in SQL Server. From what I'm reading, that's no longer needed with the enhanced memory addressing. Is that the case? Is there an MS best practices KB doc in regards to configuration? I can't find one.Also, should I set anything special pagefile-wise? I know the old mantra is 1.5x system memory, but that would create a 48GB page file. Our current cluster has 6GB of ram dedicated to each instance (2k) and Perfmon tells me it's barely touching the pagefile. I'm thinking of just leaving it default, but I want to know if there is a best practice that I'm missing. Again, I can't find an MS doc that addresses this particular scenario.

View Replies !

MS SQL 2005 && Memory Usage Question
OVERVIEW
I run a MS SQL DB and have 7GB of RAM allocated to it to use. My DB size is around 30GB. I have about 30-40 users at any given time accessing this DB, and on any given moment we may have 2-3 queries being processed at the exact same time, but not really a high rate of usage.

The SQL is running on a 2 - Dual Core Xeon 2.8Ghz processor server in RAID 1+0. The average CPU usage is around 8-15% at most times, spikes to 60-70% sometimes then drops back down.

QUESTION
Why or what would cause SQL to show in my Task Manager as using 7GB of RAM constantly? When I restart SQL, system RAM drops to 1.7GB and holds. As traffic increases, as queries begin to be processed in SQL the RAM rises till it reaches the 7GB limit I set. Before it reaches this limit, the ASPX pages run smooth as silk. Once it reaches this 7GB limit they begin to crawl. A process that would take 2-6 seconds, now takes 30-60 seconds.

I know a couple years back I ran into this and it was because processes were not being closed and SQL was holding them in RAM, but I have been assured this is not the case now. I was told that because my DB is 30GB total (MANY tables, most are hardly ever accessed, and the bulk of the data is hardly accessed) that it is common for SQL to use and hold on to 7GB of data - is this true?

What would cause SQL to hold 7GB and slow way down? IS there a KB article that could help me understand this?

Thanks for your help.

View Replies !

SQL 2005 (32-bit) Cluster Memory Usage
We recently did an in-place upgrade of our cluster from SQL 2000 Enterprise to 2005 Enterprise. We are seeing memory utilization on the server that is not expected and wanted to get an idea from others if this is normal. Here is our setup:

32-bit Windows Server 2003 SP1
SQL 2005 SP2, 2-node cluster
16 GB memory on each node
/3GB /PAE switches in the boot.ini file
AWE Enabled, min memory=0, max memory=14000

The strange bit is that in the Task Manager the SQL service is showing as only using 205 MB of memory and the pagefile usage is at 13 GB. This is troublesome since it looks like SQL is using virtual memory instead of physical memory. We recently upgraded the memory from 4 GB to 16 GB, the sql cluster service account was added to the Lock Pages in Memory policy, the boot.ini switches were set and the AWE enabled/max_server_memory script was run before upgrading to 2005. Can anyone confirm either 1) this is normal or 2) how to correct the memory usage?

I have also questioned using the /3GB switch; would it be better to remove that and set the max memory for SQL to 12000?

Any help would be greatly appreciated. If I left off any information that would be helpful, please let me know.

Chris

View Replies !

Memory Support For Sql 2005 STD 64BIT

Hi,

I'm about to install Windows 2003 STD 64BIT and SQL 2005 STD 64BIT with 12GB MEM.

my question is about the switch in the BOOT.INI - do i need to enable any switch with this server configuration?

and do i need to enable the AWE on the SQL?

THX

View Replies !

Win03, SQL 2005 And Memory Settings.
We have two servers needing to proper configure memory. They are dedicated SQL servers.

Server1(S1)
Win03 EntR2 sp1 w/8gb ram, SQL2005 Ent sp1

Server2(S2)
Clustered Win03 EntR2 sp1 w/16gb ram, SQL2005 Ent sp1

I am confused on the settings for each server to maximum memory use.
I gather on each need to allow the service acct right to €œlock pages in memory€?

S1-
What switch needs added to the boot.ini? (just /pae or add /3g)
Enable AWE in SQL?
Set max mem to 7gb?

S2-
What switch needs added to the boot.ini? (just /pae or add /3g)
Enable AWE in SQL?
Set max mem to 15gb?

View Replies !

SQL 2005 Memory Usage Just Keeps Rising
Hi,

I have a three-tier app written in C#, which takes information from a third party source (typically an array of double precision floats) and commits it to a SQL 2005 Db.

The Server then notifies the client that the information is available and the client queries the Db (through the server) in order to display it in "real-time". It all seems to work fine except that the memory usage by SQL 2005 just keeps rising. I have run a memory profiler on the server and client apps and they do not have a leak.

The test I am running has all three apps on the same machine and is reading in about 250k of data a second. It typically runs for just under two days on a machine with 2Gb RAM before falling over with System.OutOfMemoryException.

I have tried setting max memory usage but it seems to make no difference... anyone seen anything similar or know what my problem might be?

Thanks,
Paul G

View Replies !

2000/2005 Memory Best Practices
Does anyone have any advice or useful experience configuring memory management for SQL Server 2000 and 2005 instances residing on the same box? I'm looking for advice on whether I should just completely leave it up to SQL Server and Windows to allocate memory or should I attempt to put ceilings on certain instances.

Any help is appreciated!

View Replies !

2005 Memory Management Questions
I've read and noticed SQL 2005 handles memory differently then 2000. In 2000 if I told a server it had 6GB to use, it allocated the memory. In 2005 I have one 32-bit server with 6GB of memory and one 64-bit server with 32 GB. If Target Server Memory is the amount of memory SQL Server would like to have, how does that correspond to Maximum Server Memory? Also, how is Target Server Memory determined?

32-bit
Physical Memory = 8GB
Target Server Memory = 6GB (Willing to consume)
Total Server Memory = 690MB (Currently consuming)
Minimum Server Memory = 2GB
Maximum Server Memory = 6GB

For the 32-bit server the Target Server Memory matches Maximum Server Memory

64-bit
Physical Memory = 32GB
Target Server Memory = 28GB (Willing to consume)
Total Server Memory = 397MB (Currently consuming)
Minimum Server Memory = 4GB
Maximum Server Memory = 30GB

For the 64-bit server the Target Server Memory is less then the Maximum Server Memory

Lock Pages in Memory is set for the service account. Neither server above has yet to be released to production and only the 32-bit server has any users. In 2000 when SQL Server started I could count on it using about 1.72GB of memory immediately. Seeing the servers above consume only only 690MB and 397MB has me concerned. Is this just a case of SQL Server 2005 handling memory better then 2000?

Thanks, Dave

View Replies !

Memory Leak Issue SQL 2005 SP2
when i'm consistantly updating database on the server, I've noticed that the memory usage goes higher and higher. How can I prevent this? Or is there a way to prevent this other than turning off the computer time to time?

thank you,

View Replies !

SQL 2005 Express Memory Resources
Dear All,

I have 1 problem here. I developed a desktop application and I am using SQL 2005 Express as my application database.

I found that after I closed my application, the SQL 2005 Express memory resources do not release. How do I release the memory resources except of restart the SQL services?

Do you guy have any better recommendation of other database is more suitable for desktop application?

View Replies !

SQL 2005 Express Memory Limits
Hi everybody,

I know that SQL2005 Express has 1 GB memory limitation.

Well, if my server has 2 GB RAM and I have other applications (without memory constraints) how does this relate to the SQL constraints ?

Can I still run SQL2005 Express if other applications exaust only 1 GigaByte of memory ?

Thank you in advance.

Pierluigi

View Replies !

SQL 2005 Memory Consumption Report
When Running the standard Memory Consumption Report in the section Buffer Pages Distribution (# Pages) i get the following error

Unable to retrieve data for this section of the report. Following error occurred. Msg 8114, Level 16, State 1 Error converting data type bigint to int.

Any Ideas

View Replies !

SQL 2005 Express And CPU/Memory Limitations.
SQL Express Ed is limited to one CPU, what happens if the CPU is a dual core CPU? Does SQL Express care? Does it see a dual core as a single CPU?

As for memory, stupid question, SQL Express is limited to 1 GB RAM, if I run it on a server with say 4GB of ram packed into it, will it affect SQL Express' performance? Will SQL Express even run if there is more than 1GB available? Or will SQL Express simply ignore the extra RAM with no negative consequences?

Thanks,

Andy

View Replies !