I had posted a msg in another thread asking about forcing MSSQL to use
query memory vs disk I/O due to long fields.
Is there any way for me to benchmark what SQL is using in Memory vs.
allocated memory.
We are running SQL 2000 Standard, not Enterprise, so it can only
address the 2GB.
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.
Hi guys,
I have a couple of databases in 2 different servers, Test and Production. They are very similar, probably a few rows more in production, but not that many.
We I run an store procedure in test it takes less than when I run it on the Production server it seems slower. The thing is that the second time that I run this query in the test server it is quick like if it keeps the information in memory. But in the production server the second time takes exactly the same.
I'm new with SQL server and I don't know if there is something about cache memory that I didn't take into account.
Please, any help?
Thanks a lot
We seem to have developed a memory leak in our sql server applicationand are getting the above error on occasion. Also, over several hoursof hard usage the memory consumed by the sql server ramps up and isnever released. The only thing we have found to remedy the problem isto stop/start sql server.My question to the group is, how can I debug this problem? Are theresystem stored procedures that would be useful in indentifying any temptables, cursors, etc, not getting cleaned up?Thanks,John
View 2 Replies View RelatedHi,
I am running the following MDX query through a DataReader and a Ado.Net Connection.
SELECT
{[Measures].[Deuda Total Nacional],
[Measures].[Deuda Total Nacional Maximo],
[Measures].[Cupo Nacional],
[Measures].[Porcentaje Utilizacion Maximo],
[Measures].[Pago Minimo Estado Cuenta],
[Measures].[Deuda Ultima Facturacion],
[Measures].[Dias Mora],
[Measures].[Dias Mora Maximo],
}
ON COLUMNS
,[Cuenta].[Cuenta].[Cuenta]*[Cuenta].[Rut].[Rut]*[Cuenta].[Dv].[Dv] ON ROWS
FROM [Bd Rtd]
WHERE [Tiempo].[Mes].&[2007-09-01T00:00:00]
The thing is, when I have about 10 thousand rows It runs in about 50 seconds which is good, but when I run this query and I have processed the cube with 100 thousand rows it runs out of memory and crashes.
I'm working in a shared development server with 1GB of memory for my project.
Is there any way to make it run anyway?? I mean even if it has to swap.
thanks
By the way when this thing goes into production it will have 1.5 million rows
Help,
Getting this error msg when running a bulk insert.
Bulk insert was working fine before and all of a sudden getting this.
Any Ideas?
Is there has any SQL query to check running out of "min memory per query SQL Server"(default value is 1024KB)?
View 2 Replies View RelatedWe're experiencing the following problem on our servers:
Server: Msg 701, Level 17, State 1, Line 3
There is insufficient system memory to run this query.
I've been able to fix the problem by a)Lowering the Max Server Memory and b)Minimum Query Memory.
However, Microsoft state there is a hot fix available for this issue at in KB Article 817262 (
http://support.microsoft.com/default.aspx?scid=kb;en-us;817262&Product=sql2k).
Any idea how one is supposed to contact Microsoft to get this fix without paying?
Thanks,
Nick
Hello,
We receive this error after running a complex query. Could someone please shed some light on what this means exactly?
One of our developer said we needed to purchase a server with more memory, but would SQL Server not simply just run slower by using virtual memory instead of physical RAM?
I know there is a limit and servers must be upgraded as the processing requirements increase, due to data set size increases for example, but we have just been told to "purchase more power because after a while as you process more rows, SQL Server will require more resources"
Any comments on this would be really appreciated.
Here is our problem...We are doing a lot of selects against a table that has one large fieldin it.If we do a select against all the fields except for description, thequery comes back relatively quickly. If we add that last field (768chars) to the query, our query takes 10x longer (5 seconds vs 56seconds.)When we run the one without the description column, we can watchperfmon and see a very quick spike to physical disk. If we add in thedescription field we can see that the server becomes I/O bound - thedisk sits at 100% until the query is complete.We have tweaked the min query memory setting for the server but itseems to have had no effect no matter how high we set it. Is theresome point at which MSSQL decides it cannot perform the transaction inmemory? What would I increase to cure this problem?For example:TMZDIFF int410WRITETIMEcharno 16System_Namecharno 64Timestampcharno16Name charno32Mount_Pointcharno32Size intno4 10Space_Usedintno410Space_Availableintno410Inode_Sizeintno410Inodes_Usedintno410Inodes_Freeintno410Space_Used_Percentintno410Inodes_Used_Percentintno410FS_Type charno8Space_Available_Percentintno410Name_U ncharno32Descriptionncharno768
View 2 Replies View RelatedHi i am using MS SQL 2000 db ,while executing following prepared statement query its working fine ...BUT when i remove "top 300" then tomcat giving OUT OF MEMORY ERROR!!
i think that its because of the cross join....So i want to optimize this query in such a way that it will not give "Out of memory error"
How can i re write this query?
Query :-
"select top 300 haw.DeviceID as deviceid,haw.Description as Description,haw.SerialNo as SerialNo,haw.Suffix as Suffix,haw.HWRev as HWRev,haw.Type as Type,dev.Status as Status from Hardware_PlaceHolder haw,Device_PlaceHolder dev ,Accounts ac where dev.siteId = ac.siteId and ac.CustID = ? ";
thanks in advance..
Just Like these:
-- batch start
Select * from someTable --maybe a query which need much res(I/O,cpu,memory)
/*
can I do something here to free res used by prior statement?
*/
select * from someOtherTable
--batch end
The Sqls above are written in a procedure to automating test for some select querys.
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?
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)
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.
My Integrity job started failing recently with the following error. Msg 701, Level 17, State 123, Line 1 There is insufficient system memory in resource pool 'default' to run this query. Process Exit Code 1. The step failed.
select @@ version
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64)
Jul 9 2014 16:04:25
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
System is having 4GB RAM and SQL is using most of it. It has 2 processors.
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 RelatedHello, 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.
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.
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
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?
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 RelatedHi,
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
Hello frnds Can Anybody explai what does it mean by question itself and how to resole it ?
View 2 Replies View RelatedHi
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?
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;
}
providing a query for fetching the data for CPU Usage, Memory usage, blocking and all details ...
I want to create a job which will run on a Node every 15 min and store data in a table for each instance...
DMV is not giving more stuff and xtended events not sure if i can store that data into a table?
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.
We have a box with dual Intel Xeon 3.4GHz processors, and 8GB RAM, OS and Data on separate RAID5 arrays. It runs:
MS Win2k3 Enterprise for the OS,
MS SQLServer 2k Enterprise for DB,
MS SQL Analysis Services for OLAP,
MS SQL Reporting Services for reports distrib,
MS IIS for web hosting,
SPSS OLAPHub for OLAP web UI, and other small stuff...
My question is this: How should the memory be configured? Should we use the OS' /3GB switch or are there enough apps running here for the OS to need 2GB to track them? What SQLServer switches would you run? I'm not sure that I want to lock more that 3-4GB of SQL data pages into memory and starve the other pigs... How could I set this up to keep 2GB for the OS and yet really use the remaining 6GB most effectively?
All input is sincerely appreciated.
Robert
I had the drives switched out on my sever this weekend and the memory is
running at a consistent 100%.
OS: NT Server 4.0 SP6
SQL Server 7.0 SP1
Drives:
From: 60gb To: 210gb
Memory: 1gb (will be increasing to 2gb)
Pagefile: 3gb
Can anyone tell me why or what would be causing all the memory to be
used?
I have been tracking SQL's memory usage over the past few months with Perfmon. I have noticed a trend that little by little "% Committed Bytes in Use" creeps up starting at 8% and eventually climing as high at 35%. I stop and start the SQL services and the total comes back down to 8%. This happens over a week and a half or two week period. Is this normal SQL activity?
Thanks for your help!
DOUG
I am going to purchase a new server to run Eastman's OpenImage software's CATALOG database. Our current server is SQL 6.5,
but it is not able to be upgraded to SQL 7.0 (known bug with this particular HP server). So I am going to buy a new one. My question is
where to find documentation that could recommend how much memory to purchase for this server. I can find scads of documents
detailing how much RAM to give SQL given a fixed amount of RAM on your server, but nowwhere can I find how much RAM to get when
buying a new server.
Any tips?
Thanks,
Joe
hi, i have a dell server with 4 xeon processors, 4GB RAM, running sql 7 ent ed sp2 on win2k advance server (dedicated sql server). configured my sql to dynamically configure memory. i've been monitoring the memory usage using task manager and the most sql will use is up to 2GB even on very heavy loads. why? everything i read on sql 7 ent ed tells me that it should be able to take advantage of up to 8GB of memory. also, the readings i did on win2k advance server says it supports PAE (Intel's Physical Address Extension). why isn't it using the other 2GB or at least another 1GB? if it's not able to use it, then my co's investment on the other 2GB RAM is wasted! please help!
View 2 Replies View Related