|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
'System.OutOfMemoryException' ?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....) nkw (n**@discussions.microsoft.com) writes:
Show quote > (SQL 2005 on my PC, A table with 13 million rows) Keep in mind that Mgmt Studio is not SQL Server but just a client.> > 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. 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 13 million rows is not an extremely big table. The clustered index key> ( > [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? 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 "Erland Sommarskog" wrote: No the problem is never solved. I think sql server returns the estimated > 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? 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 2G. I closed all other apps.> 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? nkw (n**@discussions.microsoft.com) writes:
Show quote > "Erland Sommarskog" wrote: But there is a huge difference here: from SQL 2005 Mgmt Studio requests>> 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. 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 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 -- Show quoteHope this helps. Dan Guzman SQL Server MVP "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....)  |
|||||||||||||||||||||||
Other interesting topics