There is insufficient system memory in resource pool 'default' to run this query.
-
Dear MVPs
I am trying to run a script containing 500000 update statements similar to the one below:UPDATE dbo.MEMOPOOL_DATA SET MEMODATA = 'URL' where ID = 559061
The ID isnt a primary key its just a normal int column and i cant use a PK because the table is created such that there is a clustered PKWhen i try to run the script i get the error:
Msg 701, Level 17, State 123, Server XXXXXX, Line 17622
There is insufficient system memory in resource pool 'default' to run this query.I even tried to split the file into 5 files each having 100000 records and yet no luck.
Shall i try to use a temporary table?
My testing machine is running:
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)Server is windows 2008 R2
Any ideas?
have a good day
Hurricane -
What do you mean by "The ID isnt a primary key its just a normal int column and i cant use a PK because the table is created such that there is a clustered PK ". If there is a Clustered PK, why can't you use it? is there at least an Index on the column you are using to find the row?
Also, what are the specs of the server? The title says you don't have enough memory, however, you've omitted how much memory your server and SQL has available. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Dear Thom
The server has 4GB of memory and i placed max memory as 3GB
There is no index on the column i will create an index on that column and see what happens
The primary key clustered index involves 4 columns so using it will be difficultHurricane
-
Just an update i already have an index on the column ID as a non-clustered index
USE [DB]
GO
CREATE NONCLUSTERED INDEX [NCI_MEMOPOOL]
ON [dbo].[MEMOPOOL_DATA] ([ID])GO
Could this be an issue with one of the memory buffers in SQL Server?
- hurricaneDBA - Tuesday, April 11, 2017 2:49 AM
You have a tiny amount of RAM there to play with. According to MS, the minimum recommended RAM is 4GB and that should increase with database size. Considering that you have at least one database will at least 500,000 rows, I would imagine that your recommended RAM is bigger now (how big is the database you are working with?). Also, that only leaves 1GB for the server's OS, which is going to effect performance horribly,
I would suggest trying to do the updates in the smallest batches you can get away with (try at 1,000). It's going to take awhile to go through all your records, but it should *hopefully* get there.
I would, however, seriously consider upgrading your resources.
Can you also supply the full SQL you're attempting to run? Not a single UPDATE statement, but the full script that generates your UPDATE batches.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Hi Thom
I am asking the systems team to increase the RAM and see what happens but what is happening is as follows
I have a table with a column storing non unique IDs and URLs in a different column
So you can have the same URL pointing to multiple IDs and the same ID pointing to multiple URls so its a many to many relationship
My script updates the URLs while it searchs for the IDs so i guess its scanning the lines multiple times to update more than 1 record which brings up some questions:1. How do i know from a memory point of view if the memory is paging out data or not?
2. Shall i use a commit after every 5000 lines to make sure that memory flushes out the dirty blocks and is free for the next update?thanks
H. -
For any given ID value, how many rows are returning? Even for a 500,000 row table, presumably a given ID value has what, 3-5 rows? 20? If you have a nonclustered index on ID and a clustered index on something, you should be seeing a seek on the nonclustered index and a lookup operation against the clustered index, more than likely a seek, joined by a nested loop operator. Check the execution plan being generated to see what's happening.
However, you're talking about processing 5000 rows at a time. That's not jiving with the simple query example you supplied. I think we have insufficient information here.
As was already pointed out, 4gb is far to small for a modern installation of SQL Server. That alone is introducing issues that having nothing do with your query and whatever processing you're attempting.
If you really want detailed help, not just suggestions, please post the table definitions, index definitions, and the actual query that you're processing (I'll bet it's not the example you supplied).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Hi Grant
I tried the script on another test server with 16GB of RAM but kept the max server memory as the default value and i used the split files and it worked
However i wanted to know if my query is correct so here are 3 lines from the query but i removed the url as its company information:USE PTW3
UPDATE dbo.MEMOPOOL_DATA SET MEMODATA = 'https://url.dummy.com' where MEMOPOOLID = 446211
UPDATE dbo.MEMOPOOL_DATA SET MEMODATA = 'https://dummy.co/pages/ExternalLinksPage.aspx?LivelinkID=3230853&VersionID=8' where MEMOPOOLID = 446212
UPDATE dbo.MEMOPOOL_DATA SET MEMODATA = 'https://dummy/ExternalLinksPage.aspx?LivelinkID=3230853&VersionID=8' where MEMOPOOLID = 446213
The table definitions are:CREATE TABLE [dbo].[MEMOPOOL_DATA](
[MEMOPOOLID] [int] IDENTITY(1,1) NOT NULL,
[SI_SITE] [varchar](20) NOT NULL DEFAULT ([dbo].[CurrentSite]()),
[SRCTABLENAME] [varchar](64) NOT NULL,
[SRCPRIMARYKEYVALUE] [varchar](127) NOT NULL,
[SRCMEMOREFERENCE] [int] NOT NULL DEFAULT ((0)),
[MEMOTITLE] [nvarchar](255) NULL,
[MEMODATA] [nvarchar](max) NULL DEFAULT (''),
[READONLY] [smallint] NULL DEFAULT ((0)),
[SY_IB] [nvarchar](50) NULL,
[SY_IS] [datetime] NULL,
[SY_UB] [nvarchar](50) NULL,
[SY_US] [datetime] NULL,
[DELETEID] [nvarchar](50) NULL,
[DELETEFLAG] [smallint] NULL DEFAULT ((0)),
[DELETEDATE] [datetime] NULL,
[CM_TIMESTAMP] [int] NULL DEFAULT ((0)),
[SY_AS] [varchar](20) NULL,
[SY_FI_DA] [datetime] NULL,
[SY_EXT_REF] [nvarchar](255) NULL,
[SY_EX] [smallint] NULL DEFAULT ((0)),
[SQL_TIMESTAMP] [timestamp] NOT NULL,
CONSTRAINT [PK_MEMOPOOL] PRIMARY KEY CLUSTERED
(
[SRCTABLENAME] ASC,
[SRCPRIMARYKEYVALUE] ASC,
[SRCMEMOREFERENCE] ASC,
[SI_SITE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
The index definition is:USE [PTW3]
GO
/****** Object: Index [NCI_MEMOPOOL] Script Date: 4/11/2017 3:43:20 PM ******/
CREATE NONCLUSTERED INDEX [NCI_MEMOPOOL] ON [dbo].[MEMOPOOL_DATA]
(
[MEMOPOOLID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
Let me know what you think Grant
thanks
Kal - There's nothing to the query. You're running an update to a URL where an ID is equal to a value. What else is there to say about the query?
The problem is that you're trying to do 50,000 individual updates. That's a tough way to do processing. There's nothing to do about tuning a query. It's about managing your logs and dealing with the updates. If you can figure out a mechanism for changing these individual updates into batch processing, you'll probably be better off.
Check the execution plan. It should be using the nonclustered index to seek for the value to update, then modifying the clustered index. If it's doing a scan of those indexes, it's a problem.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Can you upload the execution plan from the ID 446211 UPDATE statement? It would be good to see what it's planning for one of those updates. You may want to anonymise if you don't want us to see the URL for whatever reason.
Also, you said that the table has a many to many relationship. Considering that you have only 1 ID in your update statement, that means it's only updating one row. Is this understanding correct? Are you therefore creating an update statement for every ID? O.o
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Another thing that should help performance (although not in computing a query plan) is to use explicit transactioning. Have a BEGIN TRAN/CHECK FOR ERROR/COMMIT OR ROLLBACK every 10K plus-or-minus statements.
I would also break up into smaller groupings than what you did. Say 10K or even less. Also, is it possible to make this table driven, where you place the components of the UPDATE into a table and simply join to that to do the updates? One statement per batch then. And yes, I think you should still iterate in batches even with that mechanism.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Ok so the actual relationship is 1 link to many IDs
Here is the execution plan for running the query below:
UPDATE dbo.MEMOPOOL_DATA SET MEMODATA = 'https://thehub.dolphin.co/pages/ExternalLinksPage.aspx?LivelinkID=3230853&VersionID=8' where MEMOPOOLID = 446211
Now for your information if i change the memory max setting to the default of 2147483647KB
The query runs fine with no memory issue as i increased the RAM to 8GB on the server but now the utilization is 95%When you run the script where in memory does the metadat get stored? Or its not releasing it from a specific cache?
thanks
Kal -
Images aren't really helpful here. You need to paste your SQL and upload your .sqlplan file.
Considering that you're changing, what seems like every url to the same thing, why are you not doing?
UPDATE [Your Table]
SET MemoData = 'YourURL'
WHERE MemoID BETWEEN 1 AND 500000; --Or appropriate range
Doing every single row individually is a hell of a lot more work.I'm also still not seeing the many to many relationship you spoke of before. How are you linking a URL to an ID, or an ID to an URL as your defined before, as we're yet to see a secod table, and you're inserting a single URL more than once. This makes me wonder if you're data is quite bloated and not normalised. You might be able to massively decrease the size of data by normalising it.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - hurricaneDBA - Wednesday, April 12, 2017 12:32 AM
Ok so the actual relationship is 1 link to many IDs
Here is the execution plan for running the query below:
UPDATE dbo.MEMOPOOL_DATA SET MEMODATA = 'https://thehub.dolphin.co/pages/ExternalLinksPage.aspx?LivelinkID=3230853&VersionID=8' where MEMOPOOLID = 446211
Now for your information if i change the memory max setting to the default of 2147483647KB
The query runs fine with no memory issue as i increased the RAM to 8GB on the server but now the utilization is 95%When you run the script where in memory does the metadat get stored? Or its not releasing it from a specific cache?
thanks
KalI have to ask:
Why would you run 500,000 individual update statements for this?That is a big part of the reason that the memory error is being thrown. It is not because the individual update is complex or anything like that. It is because there are 500,000 individual updates to process. A cursor would be more memory friendly than doing the update in this manner. Looking at the queries, you can vastly simplify the 500,000 updates down to a single update statement and save yourself grundles of hair pulling.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Hi Thom
Its its not a many to many relationship its 1 URL to many MEMOPOOLIDs
I need 500,000 because its not the same URL and not the same MEMOPOOLIDs so a simple cursor wont work
What happened is i was provided with the links in an oracle system and the new links in SQL Server Sharepoint
I placed the URLs in an excel sheet and then i loaded it into a temporary table and then loaded the memopoolids into that temporary table and generated the update script based on that temporary table and TA TA the script you see is part of itHence i am not updating with the same URL otherwise i wouldve used one statement as Thom mentioned above
If there is an easier way to update 500000 records where the values are different on both the column being updated and the column being seeked do let me knowIs using a temporary table faster?
thanks
Kal
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply