Can anybody shed some light on my issue? I'm reviewing my client's event log and finding the repetitive warning "A significant part of sql server process memory has been paged out. This may result in a performance degradation." This has been an ongoing issue that I've searched high and low to try and resolve.
The system is Windows Server 2003 Standard x64 Edition - SQL Server 2005 SP2 - 2.33 GHz - 4 GB Ram
Thanks in advance for any help!
Hi all
I frequently see the following message on SQL Server log
2008-06-09 07:46:18.17 spid3s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 1079156, committed (KB): 17156388, memory utilization: 6%.
What does it indicates and what appropriate action has to be taken to fix it.
The database runs on
SQL 2005 Dev 64-bit SP2 9.00.3042.00
Win 2003 standard x64 SP2 16GB RAM
Thanks.
Hi All
I see the following message in SQL Server logs. What does this indicates. What should I do to avoid this.
2008-05-20 01:25:02.12 spid2s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 33920, committed (KB): 15142988, memory utilization: 0%.
The server configuration is
SQL 2005 Dev edition SP2 64bit
Win 2003 R2 SP2 Standard X64 editioin
RAM size is 16GB
Thanks.
In my SQL Server Errorlog, I see the below error. The system has 8 GB of RAM with enough free RAM, something I can do to prevent this alert? (Note: I have no MIN/MAX memory set on this Instance)
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 328 seconds. Working set (KB): 76896, committed (KB): 167628, memory utilization: 45%.
On a SQL Server 2005 x64 Standard Edition cluster I get the error listed below and then the SQL server service restarts. The SQL server is unavailable for 5-10 minutes during that time. Any ideas?
Error:
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 647 seconds. Working set (KB): 11907776, committed (KB): 28731732, memory utilization: 41%%.
I have been searching this topic on and for quite some time and can't seem to find a decent answer. Is it feasible to do your paging strictly from a SQL query? Pass the query the pagesize, what page to return and what to sort by?
View 4 Replies View RelatedIs there a way, other than simply changing the format of the SRS report, to create an old fashond Paged style report - similar to the ones you can get from Crystal Reports?
View 6 Replies View RelatedWhat is the recommended mechanism for selecting paged results from SQL.
Presently I pass various params including the request Max Items Per Page and the requested page.
The I execute the query as a count with the search params.
Then comes the paging logic, which validates the page number against the request page and number of hits etc.
Then a temp table and record variables are created for the results.
Then I run the query again with a cursor and select the appropriate Items into the temp table based on the paging values (First Item and Last Item).
Then I return the temp table & some additional return params with the Total Hits etc.
The Stored procedure is accessed via an ADO.Net client and the system.data.IDBReader populates a .Net strongly typed collection and is for read only display.
Thanks for any input,
Martin.
I have a paging query that uses OpenQuery to access the MSIDXS indexing service and return records where text is matched. The query looks like this:
select top 25 * from
OpenQuery(FileSystem, 'SELECT DocTitle, FileName, Rank, Size, Create from Scope() where contains(''report'') ORDER BY Rank DESC')
as Q, LookupDocuments_dbv AS v where Q.FileName=v.Loc_cst and catname_cst='Main'
and (v.docid_cin not in (select top 600 v.docid_cin from
OpenQuery(FileSystem, 'SELECT DocTitle, FileName, Rank, Size, Create from Scope() where contains(''report'') ORDER BY Rank DESC')
as Q, LookupDocuments_dbv AS v where Q.FileName=v.Loc_cst order by v.docid_cin)) order by v.docid_cin
Not the exact query, but it is just as slow as the query in my program. This query will retrieve the top 25 records starting at record 601 where 'report' is found in a document and where the category = 'Main'
What is really weird about this is that in management studio this query will sometimes return in like 3 seconds, while the exact same query will take 30 seconds using ASP.Net / System.Data.SqlClient (and I mean it actually sits on sqlCommand.ExecuteReader() for a long period of time). Also, does management studio cache the table randomly, because sometimes it'll take forever and other times it'll be fast.
I do not have response issues with this paging query in non-MSIDXS queries.. I can for example return the 5,000th - 5,025th of 50,000 records in less than a second while the OpenQuery manages to get progressively slower very quickly (like 3 extra seconds per page of 25 records, so it takes like 20 seconds to return 150-175 of 600).
I am trying to move my application (asp.net) from a non-paged
select to a paged query.
I am having a problem. Below is my Stored Procedure. The
first Query in the procedure works...the rest (which are commented
out ALL work interactively, but fail when the program tries to
access....The ONLY THING I change is the stored procedure
I switch the comment lines to the non-paged procedure and it
works, I try to use the any of the paged procedures and it
fails with the same error (included below)
I can't see where any of the queries are returning
different results. I have also included the program abort
that happens, it is the same for all of the paged queries.
ALTER PROCEDURE dbo.puse_equipment_GetAllTypedEquipment
(
@EquipmentTypeId int,
@StartRowIndex int,
@MaximumRows int
)
AS
-- ************************************************************************************************
-- Non-Paged OUTPUT
-- THIS WORKS!!!!!
SET NOCOUNT ON
SELECT Equipment.*,
EquipmentType.Name as EquipmentType,
EquipmentCategory.Name as Category
FROM Equipment INNER JOIN EquipmentCategory ON EquipmentCategory.CategoryId = Equipment.CategoryId
INNER JOIN EquipmentType ON EquipmentType.EquipmentTypeId = Equipment.EquipmentTypeId
where Equipment.EquipmentTypeId = @EquipmentTypeId AND
Equipment.IsDeleted = 0
/*
-- ************************************************************************************************
-- Using a Temp Table
--THIS WORKS INTERACTIVELY, But NOT When Called by the program
SET NOCOUNT ON
create table #PagedEquipment
(
IndexId int IDENTITY(1,1) Not NULL,
EquipId int
)
-- Insert the rows from Equipment into the PagedEquipment table
Insert INTO #PagedEquipment (EquipId)
select EquipmentId From Equipment
WHERE IsDeleted = 0
SELECT #PagedEquipment.IndexId, *,EquipmentType.Name as EquipmentType, EquipmentCategory.Name as Category
FROM Equipment
INNER JOIN #PagedEquipment with (nolock) on Equipment.EquipmentId = #PagedEquipment.EquipId
INNER JOIN EquipmentCategory ON EquipmentCategory.CategoryId = Equipment.CategoryId
INNER JOIN EquipmentType ON EquipmentType.EquipmentTypeId = Equipment.EquipmentTypeId
Where #PagedEquipment.IndexId Between (@StartRowIndex) AND (@StartRowIndex + @MaximumRows +1)
*/
/*
-- **********************************************************************************************
--Using the With to create a temp table (in memory)..works interactively but fails when
--called by the application..
--THIS WORKS INTERACTIVELY, But NOT When Called by the program
Set NOCOUNT ON;
With PagedEquipment AS
(
SELECT EquipmentId,
ROW_NUMBER() OVER (Order by Equipment.EquipmentId) AS RowNumber
FROM Equipment
WHERE EquipmentTypeId = @EquipmentTypeId AND
IsDeleted = 0
)
SELECT RowNumber, Equipment.*, EquipmentCategory.Name as Category, EquipmentType.Name as EquipmentType
FROM PagedEquipment
INNER JOIN Equipment ON Equipment.EquipmentId = PagedEquipment.EquipmentId
INNER JOIN EquipmentCategory ON Equipment.CategoryId = EquipmentCategory.CategoryId
INNER JOIN EquipmentType ON Equipment.EquipmentTypeId = EquipmentType.EquipmentTypeId
WHERE PagedEquipment.RowNumber Between (@StartRowIndex+1) AND (@StartRowIndex+1+@MaximumRows)
return
-- *******************************************************************************************
*/
/*
-- ********************************************************************************************
--nested selects
--THIS WORKS INTERACTIVELY, BUT NOT WHEN CALLED FROM THE PROGRAM
SET NOCOUNT ON
Select * From
(
Select Row_Number() OVER (Order By Equipment.EquipmentId) as RowNumber,
Equipment.*,
EquipmentType.Name as EquipmentType,
EquipmentCategory.Name as Category
FROM Equipment INNER JOIN EquipmentCategory ON EquipmentCategory.CategoryId = Equipment.CategoryId
INNER JOIN EquipmentType ON EquipmentType.EquipmentTypeId = Equipment.EquipmentTypeId
where Equipment.EquipmentTypeId = @EquipmentTypeId AND
Equipment.IsDeleted = 0
) equip
Where equip.RowNumber between (@StartRowIndex+1) AND (@StartRowIndex + 1 + @MaximumRows )
-- ************************************************************************************************
*/
Server Error in '/pUse' Application.
--------------------------------------------------------------------------------
Arithmetic overflow error converting expression to data type int.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Arithmetic overflow error converting expression to data type int.
Source Error:
Line 148: {
Line 149: List<EquipmentDetails> equipment = new List<EquipmentDetails>();
Line 150: while (reader.Read())
Line 151: equipment.Add(GetEquipmentFromReader(reader));
Line 152: return equipment;
Source File: c:Documents and SettingsBrianDesktoppuseApp_CodeDALEquipmentEquipmentProvider.cs Line: 150
Stack Trace:
[SqlException (0x80131904): Arithmetic overflow error converting expression to data type int.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932
System.Data.SqlClient.SqlDataReader.HasMoreRows() +150
System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +212
System.Data.SqlClient.SqlDataReader.Read() +9
PredominantUse.DAL.Equipment.EquipmentProvider.GetEquipmentCollectionFromReader(IDataReader reader) in c:Documents and SettingsBrianDesktoppuseApp_CodeDALEquipmentEquipmentProvider.cs:150
PredominantUse.DAL.Equipment.SqlClient.SqlEquipmentProvider.GetTypedEquipmentList(Int32 EquipmentTypeId, Int32 StartRowIndex, Int32 MaximumRows) in c:Documents and SettingsBrianDesktoppuseApp_CodeDALEquipmentSqlClientSqlEquipmentProvider.cs:103
PredominantUse.BLL.Equipment.GetTypedEquipment(Int32 EquipmentTypeId, Int32 StartRowIndex, Int32 MaximumRows) in c:Documents and SettingsBrianDesktoppuseApp_CodeBLLEquipmentEquipment.cs:259
PredominantUse.BLL.Equipment.GetTypedEquipment(Int32 EquipmentTypeId) in c:Documents and SettingsBrianDesktoppuseApp_CodeBLLEquipmentEquipment.cs:238
[TargetInvocationException: Exception has been thrown by the target of an invocation.]
System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +0
System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +72
System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) +371
System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +29
System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method, Boolean disposeInstance, Object& instance) +480
System.Web.UI.WebControls.ObjectDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1960
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Adapters.ControlAdapter.CreateChildControls() +12
System.Web.UI.Control.EnsureChildControls() +128
System.Web.UI.Control.PreRenderRecursiveInternal() +50
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
I have implemented the Stored Procedure "RowCount" method of controllingpaged recordsets in ASP as shown in this pagehttp://www.aspfaq.com/show.asp?id=2120.I have had to make heavy alterations to the code in order for it to workwith my application.It all worked fine until I tried to sort my data by a field which containeda NULL value (even in just one record). Before I added additional criteriafor the Unique_ID field the stored procedure returned zero records whensorting by such a field. I then added the Unique_ID criteria to the ORDER BYand WHERE clauses (as seen below) and it excludes records.In short obviously what I want this to do is order by fields that containNULL values (e.g. fld4 in sample data below). It currently pages and ordersperfectly in fields which have data in every row.If anyone reads this and helps me, thanks *very* much in advance.(P.S. sorry about the horrible state of my code but I've been messing withit trying to get this fixed for ages)(P.P.S the reason for passing in the long SQL string as a parameter in theSP is because the string is generated using complicated loops and I foundthis much easier in ASP than doing it in SQL)******************************************Here is an example of a call to the procedure from ASP:EXEC st_paging_rowcount 7, 1, 50, 'SET ROWCOUNT 50 SELECT utbl7.*,utbl6_1.fld1 AS fld3_Name FROM utbl7 LEFT OUTER JOIN utbl6 utbl6_1 ONutbl7.fld3 = utbl6_1.Unique_ID WHERE utbl7.fld5 >= @fld1val ORutbl7.Unique_ID >= @uniqueid ORDER BY utbl7.fld5',5******************************************Here is the SQL SP:CREATE PROCEDURE st_paging_rowcount@tableid INT,@pagenum INT = 1,@perpage INT = 50,@finalselect NVARCHAR(1500),@sortfield INTASBEGINSET NOCOUNT ONDECLARE@ubound INT,@lbound INT,@pages INT,@rows INT,@querystring1 NVARCHAR(200),@querystring2 NVARCHAR(200),@querystring3 NVARCHAR(200)SELECT @querystring1 = N'SELECT @rows = COUNT(*),@pages = COUNT(*) / @perpageFROM utbl' + CAST(@tableid AS NVARCHAR(15)) + ' WITH (NOLOCK)'--SELECT GOGOGO = @querystring1EXEC sp_executesql@querystring1,N'@rows INT OUTPUT, @pages INT OUTPUT, @perpage INT',@rows OUTPUT, @pages OUTPUT, @perpageIF @rows % @perpage != 0 SET @pages = @pages + 1IF @pagenum < 1 SET @pagenum = 1IF @pagenum > @pages SET @pagenum = @pagesSET @ubound = @perpage * @pagenumSET @lbound = @ubound - (@perpage - 1)SELECTCurrentPage = @pagenum,TotalPages = @pages,TotalRows = @rows-- this method determines the string values-- for the first desired row, then sets the-- rowcount to get it, plus the next n rowsDECLARE @fld1val NVARCHAR(64)DECLARE @uniqueid INTSELECT @querystring2 = N'SET ROWCOUNT ' + CAST(@lbound AS NVARCHAR(15))+ 'SELECT@fld1val = fld' + CAST(@sortfield AS NVARCHAR(15)) + ', @uniqueid= Unique_IDFROMutbl' + CAST(@tableid AS NVARCHAR(15)) + ' WITH (NOLOCK)ORDER BYfld' + CAST(@sortfield AS NVARCHAR(15)) +', Unique_ID 'SELECT test0 = @querystring2EXEC sp_executesql@querystring2,N'@fld1val NVARCHAR(64) OUTPUT, @uniqueid INT OUTPUT',@fld1val OUTPUT, @uniqueid OUTPUT--SELECT test = @finalselectEXEC sp_executesql@finalselect,N'@fld1val NVARCHAR(64), @uniqueid INT',@fld1val, @uniqueidSET ROWCOUNT 0END*********************************************Here is some sample data from the table (e.g. would like to sort by fld4):Unique_ID Date_Added Who_Added Locked fld1fld2 fld3 fld4----------- --------------------------- ----------- ----------- ------------------------------------------------------------3 2005-09-16 16:12:30.200 1 0 SmithJohn 1 NULL4 2005-09-16 16:12:41.013 1 0 JonesChris 1 NULL6 2005-09-16 16:13:10.187 1 0 StamovStilian 1 NULL7 2005-09-16 16:19:15.437 1 0 LewickiSteve 1 Colchester8 2005-09-16 16:19:36.937 1 0 JamesPhil 1 NULL9 2005-09-16 16:20:35.327 1 0 LeroyDidier 1 NULL
View 3 Replies View RelatedWe are running SQL 2005 SP2 x64 on Windows 2003 SP2 X64.
Server specs:
Quad Core, each core runs at 2.33Ghz.
4GB memory
OS and SQL on RAID 1 set
Transaction log on RAID 1 set
Databases on RAID 5 set
-The SQL service account is a domain account with the "lock pages in memory" rights.
-The min memory use for sql server is 3000MBand the maximum is 2147483647MB.
For whatever reason, the sqlsrvr.exe process uses much less physical memory than the Virtual Memory (VM). When I look at the Task manager, I see 230MB for Mem Usage column and 300MB for VM size column under full workload. Why is the SQL server process being paged out? I have 3GB for it to use. Should I be concerned about the my SQL server?
I didn't configure the AWE as MS doesn't recomment using it on x64 bit systems. When I do "dbcc memorystatus", I get the following:
VM Reserved 4269536
VM Committed 196680
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0
I thought granting "lock pages in memory" rights to the sql service account automatically enables AWE in x64 bit version of SQL 2005 standard server.
Thanks
Hi, I am trying to implement filtering on a custome paged stored Procedure, here is my curent Stored Procedure which doesn't error on complie or run but returns no records. Anyone got any ideas on how to make this work???<Stored Procedure>set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author: Peter Annandale-- Create date: 22/10/2006-- Description: Get Filtered Names-- =============================================ALTER PROCEDURE [dbo].[proc_NAMEFilterPaged] -- Add the parameters for the stored procedure here @startRowIndex int, @maximumRows int, @columnName varchar(20), @filterValue varchar(20)ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;SELECT CODE, LAST_NAME, Name, TYPE, NUMBER FROM (SELECT n.CODE, n.LAST_NAME, n.FIRST_NAME + ' ' + n.MIDDLE_NAME AS Name, nt.TYPE, f.NUMBER, ROW_NUMBER() OVER(ORDER BY n.LAST_NAME) as RowNum FROM dbo.NAME n LEFT OUTER JOIN NAMETYPE nt ON n.NAME_TYPE = nt.NAME_TYPE LEFT OUTER JOIN FUNERAL f ON n.CODE = f.DECEASED WHERE @columnName LIKE @filterValue ) as NameInfo WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) -1END </Stored Procedure> Any assistance would be greatly appreciated.. Regards..Peter.
View 1 Replies View RelatedI 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.
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 RelatedHi,
I have a web form that lets users search for people in my database they wish to contact. The database returns a paged set of results using a CTE, Top X, and Row_number().
I would like to give my users to option of removing individual people from this list but cannot find a way to do this.
I have tried creating a session variable with a comma delimited list of ID's that I pass to my sproc and use in a NOT IN() statement. But I keep getting a "Input string was not in a correct format." Error Message.
Is there any way to do this? I am still new to stored procedures so any advice would be helpful.
Thanks
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.
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
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.
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;
}
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