Hi,
I've read that the parser can use 1/8th of the available memory for the xml-cache, but I've run into this limit much sooner than I'd expect.
I'm not using "sp_xml_preparedocument", but rather operating on columns with XML as datatype. This means I don't have the handle to the document and can't use "sp_xml_removedocument" to free the memory.
It's a simple application written in C#, so should it be sufficient to "reset" the sql-connection or how should I do it?
*I've tried to keep the post as simple as I could, but I might have simplified it too much?*
What is you application doing with XML, what level of concurrency?
When I investigated the 1/8 question I couldn't get any forumla on any machine to get to this number. THe one thing I did find was that it appeared to be related to the amount of memory available at startup of sql server. If running on a user machine you are likely to have lots of other apps running, VS, Managment Studio, help etc. These just take chunks out of the available memory for SQL.
I ahev no experience of the XML data type memory requirements in 2005 sorry.
|||Hi,
My application does nothing more than look-ups and it does it all on one connection. As far as I can see, closing the connection should free the cache though.
My "problem" is that Im not using "sp_xml_preparedocument" and thus not getting the handle for the document.
Perhaps there is a way to see the "cache-status"?
I got the 1/8 from the reference regarding the stored procedure "sp_xml_preparedocument". http://msdn2.microsoft.com/en-us/library/ms187367(SQL.90).aspx
"A parsed document is stored in the internal cache of SQL Server 2005. The MSXML parser uses one-eighth the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory"
|||Can you post the error you are getting, when you are getting it and as much of the code as possible.|||Basically I either get one of the following messages:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
or
XML document could not be created because server memory is low. Use sp_xml_removedocument to release XML documents. (when doing SqlDataReader reader = cmd.ExecuteReader(); )
I'll do a few more tests to see if I can narrow it down a little, because I noticed that the application and sql-process didn't increase their memory usage, but the pagefile-usage kept increasing.
The program is printing some invoices, so the printing-part might influence it.
*heads back to his experiement*
What SQL are you executing? Without this we can't really work out what might be the problem.
When you get the timeout, what processes are using the cpu?
|||I'll clean/write it up, as it is rather embedded right now. I hope to make it clearer and without as much "clutter"-code.
In reality it is many select-statements on a table with a column with the xml-type. I'll eloborate later when I get it "readable"
Im running the application in debug-mode from Visual C# 2005.
|||*slaps his forehead*
I think I found my mistake. The sp_xml_preparedocument did indeed get called (was hidden away in a stored procedure)
I'll run a test and see if it works.
-- *some variable names are in danish*
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[FakturaSP] AS
CREATE TABLE #philTemp
(prim int identity,
connectionID int)
--Fill #philTemp with the ConnectionIDs
INSERT INTO #philTemp SELECT ConnectionID FROM Connections
WHERE XmlData.exist('/Bat2005.Connection[ConnectionType = "FakturaHoved_OrdreHoved"]') = 1
--SELECT * FROM #philTemp
declare @.counter int
declare @.maxCount int
DECLARE @.doc xml
DECLARE @.idoc int
set @.counter = 0
SELECT @.maxCount = COUNT(*) FROM #philTemp
--Create table with the ordrehoveder that has been invoiced
CREATE TABLE #faktureredeOrdrehoveder
(prim int identity,
ordrehovedID int)
while @.counter < @.maxCount begin
set @.counter = @.counter + 1
SELECT @.doc = XmlData FROM Connections
WHERE ConnectionID = (SELECT ConnectionID FROM #philTemp WHERE prim = @.counter)
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
INSERT INTO #faktureredeOrdrehoveder SELECT *
FROM OPENXML (@.idoc, '/Bat2005.Connection',2)
WITH (IDofChild int)
EXEC sp_xml_removedocument @.idoc
end
--SELECT * FROM #faktureredeOrdrehoveder
--
CREATE TABLE #alleOrdrehoveder
(prim int identity,
ordrehovedID int)
--Fill #philTemp with the ConnectionIDs
INSERT INTO #alleOrdrehoveder SELECT ComponentID FROM Components
WHERE XmlData.exist('/BAT.Templates.DanNET.Ordrehoved') = 1
--SELECT * FROM #alleOrdrehoveder ORDER BY ordrehovedID asc;
SELECT #alleOrdrehoveder.ordrehovedID
FROM #alleOrdrehoveder LEFT JOIN #faktureredeOrdrehoveder ON #alleOrdrehoveder.ordrehovedID = #faktureredeOrdrehoveder.ordrehovedID
WHERE (((#faktureredeOrdrehoveder.ordrehovedID) Is Null)) ORDER BY ordrehovedID asc;
--Clean up
DROP TABLE #alleOrdrehoveder
DROP TABLE #faktureredeOrdrehoveder
DROP TABLE #philTemp
|||
A couple of comments.
Always create temporary tables at the very start, avoids the possibility of recompiles ( i know 2005 is better, but this is good practice)
Have you tried using the nodes method, have a look at the following link in BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/7267fe1b-2e34-4213-8bbf-1c953822446c.htm
Avoids the use of openXML.
|||Thanks for the pointers, I'll have a look at the right away.
As for the stored procedure, it's a procedure I made to find the orders that haven't been invoiced yet. I didn't really plan to keep it (but it's doing its job) and I didn't expect to find the error here. (as you could see from the code I'm not really used to making stored procedures)
I just ran the test and it completed, although the pagefile is still increasing, but Im guessing that's due to another mistake somewhere else :-)
/Philip
|||
"Basically I either get one of the following messages:Timeout expired. The timeout period elapsed .."
Your second error "low memory" most probably is caused by the time out error. The reason is when time out happened, you SP has no chance to run the procedure sp_xml_removedocument.
One trick you can do when this happends is to call sp_xml_removedocument in a loop.
DECLARE @.Loop int
SET @.Loop=1
WHILE @.Loop<1000000
BEGIN
exec sp_xml_removedocument @.Loop
SET @.Loop=@.Loop+1
END
It may print lots of errors if @.Loop is not a valid hanlde of xml. But if it's, it will be removed.
For how to avoid memory leaking in this situation, please reference OPENXML and memory leak
|||A good point about xml in a transaction, thankssql
