Maximum Memory Problem
Nov 28, 2007
Hello, everyone:
I have SQL Server 2000 Standard Edition running on Windows 2000 Server. There are always memory full alter. I am going to configure that.
I hope a clearation about a concept. In my enviroment, does SQL Server take maximum memroy upto 2GB, no matter if QWE is enabled?
BOL said AWE is available only in the SQL Server 2000 Enterprise and Developer editions. Does it mean AWE cannot be configured on SQL Server 2000 Standard Edition?
Thanks
ZYT
View 4 Replies
ADVERTISEMENT
Nov 4, 2007
I need to up the Maximum Memory setting.
Out of the box it is set to 2147483647 MB.
I assume this is ~2GB. I have been instructed
to set it to 1GB less than total server memory
which is 16GB. When I change the value
it just changes back.
How do I change it and how are these Megabyte
numbers calculated?
OS: Windows 2003 R2 Enterprise
SQL: SQL Server 2005 Standard
Thanks!
View 2 Replies
View Related
Sep 15, 1998
I am trying to find a suitable size for TempDB in RAM .
Is it possible to find the instantaneous maximum usage of the TempDB in RAM?
In other words how can I find the maximum amount of memory used* by TempDB ?
bim@sifas.com.tr
mikiz@turk.net
View 3 Replies
View Related
Feb 26, 2004
If I change the Maximum Memory option (sp_configure or vai EM), do I need to restart the MSSQL service?
Clive
View 2 Replies
View Related
Feb 20, 2002
Hello,
I have been trying to find out what the maximum memory we can have on SQL7 standard edition server. Have not been able to find it anywhere...
Anuy ideas would be greately appreciated.
TIA
B.
View 1 Replies
View Related
May 21, 2015
I am curious about maximum memory setting .
Should we set maximum memory setting for each SQL server? For example a server has 6 GB memory then should we set maximum memory setting = 3.5 GB ?
View 9 Replies
View Related
Aug 13, 2007
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 2 Replies
View Related
Apr 28, 2015
How to set maximum server memory by using sql scripts in sql server 2014?
View 5 Replies
View Related
Dec 3, 2007
We have several 2005 servers with "Maximum server memory" set to 214 gig, which I believe is the default at installation time. I am told that this means "use all the memory there is including paging." Well, this is nuts but the servers seem to work fine with this setting no matter how much physical memory they have.
One of our 2005 servers recently started paging like crazy, so I reduced "Maximum server memory" to 6000 and the paging disappeared (server has 8 gig of physical memory) and the server appears happy.
I can not explain why only this one server has this paging issue and the others do not. Should I be setting "Maximum server memory" on all my servers? Are there other considerations which might cause the server to eat-up all the memory? As far as I know no other applications run on this box.
Thanks,
Michael
View 6 Replies
View Related
Sep 12, 2015
I have some code I build 2 weeks ago which I’ve been running daily but it’s suddenly stopped working with the following error.
“The table "tbl_Intraday_Tmp" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit” When I google this there seems to be a related to tables with vast numbers of columns.
My table tbl_Intraday_tmp is relatively small. It has 7 columns. 1 of varchar(5), 3 of decimal(9,3) and 2 of decimal(18,0). The bit I’m puzzled with is it was working and stopped.
I don’t recall changing anything but I wouldn’t rule that out. I ‘ve inspected the source files and I don’t believe they have changed either.
DECLARE
@FileName varchar(50),
@Path varchar(50),
@SqlCmd varchar(1000)
= '',
@ASXCode varchar(5),
@Offset decimal(18,0),
[code]....
View 5 Replies
View Related
Apr 20, 2012
I am using MS SQL server 2008, and i have a table with 350 columns and when i m trying to create one more column its giving error with below message -
Warning: The table XXX has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes.
INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.
how can i resolve this?
View 14 Replies
View Related
Oct 10, 2005
SQL Server 2000 8.00.760 (SP3)I've been working on a test system and the following UDF worked fine.It runs in the "current" database, and references another database onthe same server called 127-SuperQuote.CREATE FUNCTION fnGetFormattedAddress(@WorkID int)RETURNS varchar(130)ASBEGINDECLARE@Address1 As varchar(50)@ReturnAddress As varchar(130)SELECT@Address1 = [127-SuperQuote].dbo.tblCompany.Address1FROM[Work] INNER JOIN[127-SuperQuote].dbo.tblCompany ON [Work].ClientID =[127-SuperQuote].dbo.tblCompany.CompanyIDWHERE[Work].WorkID = @WorkIDIF @Address1 IS NOT NULLSET @ReturnAddress = @ReturnAddress + @Address1 + CHAR(13)+ CHAR(10)RETURN @ReturnAddressENDSo now the system has gone live and it turns out that the live"SuperQuote" database is on a different server.I've linked the server and changed the function as below, but I get anerror both in QA and when checking Syntax in the UDF builder:The number name 'Zen.SuperQuote.dbo.tblCompany' contains more than themaximum number of prefixes. The maximum is 3.CREATE FUNCTION fnGetFormattedAddress(@WorkID int)RETURNS varchar(130)ASBEGINDECLARE@Address1 As varchar(50)@ReturnAddress As varchar(130)SELECT@Address1 = Zen.SuperQuote.dbo.tblCompany.Address1FROM[Work] INNER JOINZen.SuperQuote.dbo.tblCompany ON [Work].ClientID =Zen.SuperQuote.dbo.tblCompany.CompanyIDWHERE[Work].WorkID = @WorkIDIF @Address1 IS NOT NULLSET @ReturnAddress = @ReturnAddress + @Address1 + CHAR(13)+ CHAR(10)RETURN @ReturnAddressENDHow can I get round this? By the way, I've rather simplified thefunction to ease readability. Also, I haven't posted any DDL because Idon't think that's the problem!ThanksEdward
View 2 Replies
View Related
Jul 13, 2015
I am looking to test this feature - and the "Transaction Performance Collector" has recommended me a table to port to In-Memory OLTP.
I have now tried the "Table Memory Optimization Advisor" tool.
After a couple of tweaks to the table design - the tool is now passing validation but the tool is not allowing to progress to the next step:
Could it be down to not having enough memory? But would this not show in the advisor?
View 4 Replies
View Related
Sep 28, 2007
Hello. I have received the follwoing error upon an attempt to Browse the Cube. All other tabs are functional, including the Calculations tab. We are running Windows Server 2003 SP2 and SQL Server 2005 SP2. Any suggestions would be greatly appreciated!
**EDIT** - Have confirmed SP1 for VS2005 is installed both locally and on server, also.
Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (Microsoft Visual Studio)
------------------------------
Program Location:
at Microsoft.Office.Interop.Owc11.PivotView.get_FieldSets()
at Microsoft.AnalysisServices.Controls.PivotTableFontAdjustor.TransformFonts(Font font)
at Microsoft.AnalysisServices.Browse.CubeBrowser.UpdatePivotTable(Boolean translate)
at Microsoft.AnalysisServices.Browse.CubeBrowser.UpdateAll(Boolean translate)
at Microsoft.AnalysisServices.Browse.CubeBrowser.InitialUpdate()
at Microsoft.AnalysisServices.Browse.CubeBrowser.SupportFunctionWhichCanFail(FunctionWhichCanFail function)
View 4 Replies
View Related
Oct 11, 2007
I've been researching AWE to determine if we should enable this for our environment.
Currently we have a quad core box with 4 gb of RAM (VMware). OS: Windows 2003 std, SQL Server 2005 std. 3GB is not set but will be as soon as we can perform maintenance on the server.
I have read mixed feedback on AWE, either it works great or grinds you to a hault. I would assume that the grinding to a hault is due to not setting the min/max values correctly or not enabling the lock page in memory setting.
We only have one instance of SQL on the server and this box won't be used for anything else aside from hosting SQL services. We do plan on running SSRS off of this server as well.
1. Will running SSRS and enabling AWE cause me problems? Will I have to reduce the max setting by the SSRS memory usage or will it share and play nice?
2. How do I go about setting the Max value? Should it be less than the physical RAM in the box? Right now its set to the default of 214748364, even if I don't enable AWE should this default value be changed?
3. It seems that even at idle the SQL server holds a lot of memory and the page file grows. If I restart the process in the morning, memory usage in taskmon is at 600mb or so. By the end of the day, its up around 2gb. How can I track down whats causing this, should this even concern me?
4. The lock Page in memory setting worries me. Everything I've read on this seems to give a warning about serious OS and other program support degradation. In some cases to the point where they have to restore the settings on the server before they can bring it back up. What are your thoughts on this.
View 3 Replies
View Related
Aug 28, 2015
I have a Windows sever 2012 with sql server 2012 enterprise. Ram size is 22GB. Sometimes SQL sever takes 95% memory.My question, How to reduce memory size without killing any process because it's production server.So there are many background process is running. And,Is there any guides to learn why Memory is raise d so high and how to reduce it.
View 10 Replies
View Related
Mar 27, 2008
Hello, I understand that we should use SSMS -> Server Properties -> Memory to put a cap on the SQL server memory usage, therefore it gives some space memory for OS, this is based on the fact if the max memory is not specified, SQL will use whatever available memory and eventually crash the system.
My question is that when a server has SSIS and SSAS services installed along with the SQL service. Would the max memory setting covers the SSIS and SSAS memory usage, or the SSIS and SSAS has to shared the memory with OS?
Thanks,
fshguo.
View 1 Replies
View Related
Dec 6, 2006
I am running Visual Studio 2005. I have an SSIS Package which is consuming a huge amount of memory. During the execution of the package the memory keeps increasing. Until finally i get an Out of Memory exception. I have run this package using dtexec, and in the BIDS. No difference. I do have some script components and have added some code to get the assemblies in the current appdomain. I do see that one particular assembly is increasing on every loop. VBAssembly every time it hits the script component is increasing by 6, and along with it the memory is climbing. What is this VBAssembly being used for is there an update to SQL Server Integration Services that I need?
Thanks! Aaron B.
View 6 Replies
View Related
Aug 22, 2007
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 4 Replies
View Related
Oct 4, 2015
i want to create a lot of index for my database for performance.but i need find memory usage by indexes.
How to find memory usage by index in sql server?
View 9 Replies
View Related
Jun 15, 2015
I've a database with a memory optimized filegroup on it. How can I remove it?I have removed the memory optimized table I had on it, but when I try to remove the filegroup I receive an error.
View 12 Replies
View Related
Jul 17, 2006
Hi,
I am going to install SQL Server 2000 (then SQL 2K5) on a Win Server 2K3 with 8 GB of ram, but it will be 16 GB in the near future.
I would like to reserve a fixed memory (for momemt less than 3-4 GB) for SQL Server and the rest for application (virtualization).
Without AWE enabled, max memory for SQL Server 2K5 is 4GB as for SQL Server 2000?
How can I manage and optimize memory keeping in mind AWE. (any doc, website available?)
Thank
View 5 Replies
View Related
Feb 10, 2015
So I started a new job recently and have noticed a few strange configurations. Typically I would never mess with min memory per query option and index create memory option configuration because i just haven't seen any need to. My typical thought is that if it isn't broke... They have been modified on every single server in my environment.
From Books Online:
• This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server technician.
• The index create memory option is self-configuring and usually works without requiring adjustment. However, if you experience difficulties creating indexes, consider increasing the value of this option from its run value.
View 3 Replies
View Related
Mar 20, 2008
Hello frnds Can Anybody explai what does it mean by question itself and how to resole it ?
View 2 Replies
View Related
Aug 2, 2006
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 1 Replies
View Related
Feb 15, 2007
Hi~, I have 3 questions about memory based bulk copy.
1. What is the limitation count of IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit(true)?
For example, how much insert row at below sample?(the max value of nCount)
for(i=0 ; i<nCount ; i++)
{
pIFastLoad->InsertRow(hAccessor, (void*)(&BulkData));
}
2. In above code sample, isn't there method of inserting prepared array at once directly(BulkData array, not for loop)
3. In OLE DB memory based bulk copy, what is the equivalent of below's T-SQL bulk copy option ?
BULK INSERT database_name.schema_name.table_name FROM 'data_file' WITH (ROWS_PER_BATCH = rows_per_batch, TABLOCK);
-------------------------------------------------------
My solution is like this. Is it correct?
// CoCreateInstance(...);
// Data source
// Create session
m_TableID.uName.pwszName = m_wszTableName;
m_TableID.eKind = DBKIND_NAME;
DBPROP rgProps[1];
DBPROPSET PropSet[1];
rgProps[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProps[0].colid = DB_NULLID;
rgProps[0].vValue.vt = VT_BSTR;
rgProps[0].dwPropertyID = SSPROP_FASTLOADOPTIONS;
rgProps[0].vValue.bstrVal = L"ROWS_PER_BATCH = 10000,TABLOCK";
PropSet[0].rgProperties = rgProps;
PropSet[0].cProperties = 1;
PropSet[0].guidPropertySet = DBPROPSET_SQLSERVERROWSET;
if(m_pIOpenRowset)
{
if(FAILED(m_pIOpenRowset->OpenRowset(NULL,&m_TableID,NULL,IID_IRowsetFastLoad,1,PropSet,(LPUNKNOWN*)&m_pIRowsetFastLoad)))
{
return FALSE;
}
}
else
{
return FALSE;
}
View 6 Replies
View Related
Jul 6, 2006
hi, im trying to get the last row of a column by using this code
cmd.CommandText = "SELECT max(orderID) as orderID from orders where memberNo = '" & Session.Item("memberNum") & "'"but it return me 1 instead which is the smallest value in my orderID column n located in the first rowthen i try to use this code but they say got syntax errorcmd.CommandText = "SELECT max(orderID) as orderID from orders where memberNo = '" & Session.Item("memberNum") & "'" ' order by orderID asc " ' and orderID = '" & Session("ordersID") & "'"
can anyone tell me the right way to write the syntax??
thx in advance
View 2 Replies
View Related
Aug 7, 2002
Does anyone know the specific website that documents the maximum RAM SQL 7.0 Standard and Enterprise edition can handle within Windows 2000 Server and Windows 2000 Advanced server? All help appreciated!!
darius
View 2 Replies
View Related
May 29, 2001
Can anyone tell me the maximum number of (if there is a maximum) for the following :-
1. SQL Server Users per Database
2. Roles per Database
3. Connections per Server
4. Logins per Server
Thanks.
View 1 Replies
View Related
Nov 29, 2000
Hi Everybody,
Our development team is doing some stuff in ASP. They are interested only in char and varchar
data types. They clearly told us that they are not going with text and image data types.
Is it possible to make a row size behind 8060 bytes in a table without text and image data types?.
tks in advance,
Sri.
View 1 Replies
View Related
Feb 9, 1999
Hi,
I have a table with 188376 rows and the data size = 3012 KB, index size = 5884 KB .
LE threshold max is set to 2000 and LE threshol percent to 20%
I have an index on that table and observed that it is not getting used.
I would like to know whether sql optimizer uses the index based on the cost
of the query plan or does the table scan once the LE thresholdlimit is reached
overriding the optimized plan.
thanks,
MMS
View 1 Replies
View Related
Jun 3, 2004
I'm using replication between two server, but I don't know the maximum record will be move from Database 1 on Server1 to Database2 on Server2 in per minute.please help me, thanks!
View 2 Replies
View Related
Oct 23, 2013
how to filter on a maximum value ?
My query is this:
SELECT
ProdspecUID
,itemcode
,model
,revno
FROM prodspec
The revision numbers (revno) are unique per itemcode.I would like to get only the rows with the highest revision per itemcode, but I also need the prodspecUID with is the index key and therefore unique.I have tried MAX() and TOP 1's in sub queries, but up until now without any result.
View 3 Replies
View Related
