'System.OutOfMemoryException' ?

9 Sep 2006 7:52 PM

nkw

(SQL 2005 on my PC, A table with 13 million rows)

I just did
select count(*) from T13M

It ran for a while and I canceled it.

Then I tried to see what the estimated execution plan is. And I got the
follow error message. Other operations are error prone too. I had to stop and
start the sevice. And it seems the Sql server need to recover the database
for a while at start up.

Error processing execution plan results. The error message is:
The type initializer for
'Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanNodeBuilder' threw an
exception.
Exception of type 'System.OutOfMemoryException' was thrown.

ALTER TABLE [dbo].[T13M] ADD CONSTRAINT [PK_T13M] PRIMARY KEY CLUSTERED
(
    [c_id] ASC, -- int
    [Date] ASC, -- smalldatetime
    [r_id] ASC, -- int
    [a_id] ASC, -- int
    [t_id] ASC, -- int
    [quote] ASC -- char(6)
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
FILLFACTOR = 90, ONLINE = OFF) ON [PRIMARY]

Is it simply the table is too big? Or any clustered index/PK too big?

I also need to check another similar table T2M with the same Primary Key see
if the keys already exists in the T13M, which one is better, "outer join" or
"not exists"?

select T2M.c_id, T2M.date, ...
from T2M left join T13M on .........
where T13M.c_id is null

or

select c_id, date...
from T2M
where not exists (select * from T13M where c_id=T2M.c_id and
date=T2M.date....)

9 Sep 2006 10:53 PM

Erland Sommarskog

nkw (n**@discussions.microsoft.com) writes:
Show quoteHide quote

> (SQL 2005 on my PC, A table with 13 million rows)
>
> I just did
> select count(*) from T13M
>
> It ran for a while and I canceled it.
>
> Then I tried to see what the estimated execution plan is. And I got the
> follow error message. Other operations are error prone too. I had to
> stop and start the sevice. And it seems the Sql server need to recover
> the database for a while at start up.
>
> Error processing execution plan results. The error message is:
> The type initializer for
> 'Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanNodeBuilder'
> threw an exception. Exception of type 'System.OutOfMemoryException' was
> thrown.

Keep in mind that Mgmt Studio is not SQL Server but just a client.
Just because Mgmt Studio runs into problem, does not mean that SQL Server
does.

Then again, if there was some condition on the machine so it was low
a resource like memory, both SQL Server and Mgmt Studio could take a
toll if they are on the same machine.

When you resolved this situation, did you also stop and start Mgmt Studio?

> ALTER TABLE [dbo].[T13M] ADD CONSTRAINT [PK_T13M] PRIMARY KEY CLUSTERED
> (
>      [c_id] ASC, -- int
>      [Date] ASC, -- smalldatetime
>      [r_id] ASC, -- int
>      [a_id] ASC, -- int
>      [t_id] ASC, -- int
>      [quote] ASC -- char(6)
> )WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
> FILLFACTOR = 90, ONLINE = OFF) ON [PRIMARY]
>
> Is it simply the table is too big? Or any clustered index/PK too big?

13 million rows is not an extremely big table. The clustered index key
is a tad log but certainly not too long.

How much memory do you have on your computer? What other things are
you running on it? Web browers? Visual Studio? Anything else that is
memory-hungry?

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

10 Sep 2006 10:44 AM

nkw

"Erland Sommarskog" wrote:
> Keep in mind that Mgmt Studio is not SQL Server but just a client.
> Just because Mgmt Studio runs into problem, does not mean that SQL Server
> does.
>
> Then again, if there was some condition on the machine so it was low
> a resource like memory, both SQL Server and Mgmt Studio could take a
> toll if they are on the same machine.
>
> When you resolved this situation, did you also stop and start Mgmt Studio?

No the problem is never solved. I think sql server returns the estimated
execution plan. However, I tried the same Mgmt Studio on the sql 2000 (msde
version) on the same machine and it works fine.

> 13 million rows is not an extremely big table. The clustered index key
> is a tad log but certainly not too long.
>
> How much memory do you have on your computer? What other things are
> you running on it? Web browers? Visual Studio? Anything else that is
> memory-hungry?

2G. I closed all other apps.

10 Sep 2006 12:51 PM

Erland Sommarskog

nkw (n**@discussions.microsoft.com) writes:
Show quoteHide quote

> "Erland Sommarskog" wrote:
>> Keep in mind that Mgmt Studio is not SQL Server but just a client.
>> Just because Mgmt Studio runs into problem, does not mean that SQL Server
>> does.
>>
>> Then again, if there was some condition on the machine so it was low
>> a resource like memory, both SQL Server and Mgmt Studio could take a
>> toll if they are on the same machine.
>>
>> When you resolved this situation, did you also stop and start Mgmt
>> Studio?
>
> No the problem is never solved. I think sql server returns the estimated
> execution plan. However, I tried the same Mgmt Studio on the sql 2000
> (msde version) on the same machine and it works fine.

But there is a huge difference here: from SQL 2005 Mgmt Studio requests
the showplan information in XML. SQL 2000 cannot produce the information,
so Mgmt Studio gets the information in a different format.

To get a grip of where things are going wrong you could do:

   set showplan_xml on
   go
   select count(*) from yourtable
   go
   set showplan_xml off

Run this first in text mode om Mgmt Studio. If this freaks out, there is
something fishy in SQL Server itself. If this works OK, run it again, now
in grid mode. If this works, open the XML document. If that works out,
save the XML document as something.sqlplan. Close the tab, and find
the .sqlplan in Explorer and double-click it. If everything works out,
you will see the plan.

If you now do "Show Estimated Execution plan" then Mgmt Studio will
do all the steps above (save saving the file do disk).

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

10 Sep 2006 12:52 PM

Dan Guzman

Can you run the script below without problems? If so, please post the
results.

SET SHOWPLAN_XML ON
GO
SET NOEXEC ON
GO
SELECT COUNT(*) FROM T13M
GO
SET NOEXEC OFF
GO
SET SHOWPLAN_XML OFF
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quoteHide quote

"nkw" <n**@discussions.microsoft.com> wrote in message
news:07828EF9-56DE-4FA9-8588-ED3E4867A9E9@microsoft.com...
> (SQL 2005 on my PC, A table with 13 million rows)
>
> I just did
> select count(*) from T13M
>
> It ran for a while and I canceled it.
>
> Then I tried to see what the estimated execution plan is. And I got the
> follow error message. Other operations are error prone too. I had to stop
> and
> start the sevice. And it seems the Sql server need to recover the database
> for a while at start up.
>
> Error processing execution plan results. The error message is:
> The type initializer for
> 'Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanNodeBuilder' threw
> an
> exception.
> Exception of type 'System.OutOfMemoryException' was thrown.
>
> ALTER TABLE [dbo].[T13M] ADD CONSTRAINT [PK_T13M] PRIMARY KEY CLUSTERED
> (
> [c_id] ASC, -- int
> [Date] ASC, -- smalldatetime
> [r_id] ASC, -- int
> [a_id] ASC, -- int
> [t_id] ASC, -- int
> [quote] ASC -- char(6)
> )WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
> FILLFACTOR = 90, ONLINE = OFF) ON [PRIMARY]
>
> Is it simply the table is too big? Or any clustered index/PK too big?
>
> I also need to check another similar table T2M with the same Primary Key
> see
> if the keys already exists in the T13M, which one is better, "outer join"
> or
> "not exists"?
>
> select T2M.c_id, T2M.date, ...
> from T2M left join T13M on .........
> where T13M.c_id is null
>
> or
>
> select c_id, date...
> from T2M
> where not exists (select * from T13M where c_id=T2M.c_id and
> date=T2M.date....)