Thursday, March 22, 2012
ASP Memory Crash
I am running reporting services on a dedicated server with 2GB of memory,
when ever a report is run that contains over 90,000 records the server
crashes and restarts. We have looked at the reports, and are to reduce the
criteria range to make smaller reports, but it then affects role the system
was designed to play in my organisation. If anyone has come across these
memory crashes and come across any ways of resolving them or ways that
haven't resolved them, including an increase in the memory size, it would be
greatly appreciated if you could reply. We are willing and able to throw
another 2GB at the problem, but are concerned this may only be a short term
solution and the problem will reoccur when the reporting size reaches 180,000
records.
Any assistance you may be able to provide would be greatly appreciatedIf you mean that the result set has 90,000 records or 180,000 records then
you have the wrong product. RS is not designed to generate reports that are
1500+ pages. Rendering is done in RAM so there is a direct correlation to
between number of records and the amount of RAM used. If the rendering
output is Excel or PDF then the amount of RAM consumed is even more. If the
destination is another program then there are better ways to do this. I know
that sometimes people are wanting to get a large amount of rows into Excel
for further analysis but it would be better to be using DTS and getting the
data out in CSV for them. Much much faster process.
If the output is not that many records but you have that many because you
are using filters then try to move away from filters. Filters brings over
all the data and then filters it. Use a query parameter instead.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in message
news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> Hi all,
> I am running reporting services on a dedicated server with 2GB of memory,
> when ever a report is run that contains over 90,000 records the server
> crashes and restarts. We have looked at the reports, and are to reduce the
> criteria range to make smaller reports, but it then affects role the
system
> was designed to play in my organisation. If anyone has come across these
> memory crashes and come across any ways of resolving them or ways that
> haven't resolved them, including an increase in the memory size, it would
be
> greatly appreciated if you could reply. We are willing and able to throw
> another 2GB at the problem, but are concerned this may only be a short
term
> solution and the problem will reoccur when the reporting size reaches
180,000
> records.
> Any assistance you may be able to provide would be greatly appreciated|||You might try CSV and see if it takes up less memory than Excel.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:u5P0mDYUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> If you mean that the result set has 90,000 records or 180,000 records then
> you have the wrong product. RS is not designed to generate reports that
are
> 1500+ pages. Rendering is done in RAM so there is a direct correlation to
> between number of records and the amount of RAM used. If the rendering
> output is Excel or PDF then the amount of RAM consumed is even more. If
the
> destination is another program then there are better ways to do this. I
know
> that sometimes people are wanting to get a large amount of rows into Excel
> for further analysis but it would be better to be using DTS and getting
the
> data out in CSV for them. Much much faster process.
> If the output is not that many records but you have that many because you
> are using filters then try to move away from filters. Filters brings over
> all the data and then filters it. Use a query parameter instead.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in
message
> news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> > Hi all,
> >
> > I am running reporting services on a dedicated server with 2GB of
memory,
> > when ever a report is run that contains over 90,000 records the server
> > crashes and restarts. We have looked at the reports, and are to reduce
the
> > criteria range to make smaller reports, but it then affects role the
> system
> > was designed to play in my organisation. If anyone has come across
these
> > memory crashes and come across any ways of resolving them or ways that
> > haven't resolved them, including an increase in the memory size, it
would
> be
> > greatly appreciated if you could reply. We are willing and able to
throw
> > another 2GB at the problem, but are concerned this may only be a short
> term
> > solution and the problem will reoccur when the reporting size reaches
> 180,000
> > records.
> >
> > Any assistance you may be able to provide would be greatly appreciated
>|||Bruce,
Thanks for your responses.
Do you know if there is away to detect if the report is going to generate
over 200 pages and return a message to the user to say something like 'This
report may crash the server, are you sure you want to continue' ? From your
responses, and others I have read about memory, and RS limitations are the
only issue I am encountering, not programming issues, so if we confirm with
users before they run large reports it could solve our problems, unless they
select 'Yes' of course.
Any further assistance you may be able to provide would be greatly
appreciated.
"Bruce L-C [MVP]" wrote:
> You might try CSV and see if it takes up less memory than Excel.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:u5P0mDYUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> > If you mean that the result set has 90,000 records or 180,000 records then
> > you have the wrong product. RS is not designed to generate reports that
> are
> > 1500+ pages. Rendering is done in RAM so there is a direct correlation to
> > between number of records and the amount of RAM used. If the rendering
> > output is Excel or PDF then the amount of RAM consumed is even more. If
> the
> > destination is another program then there are better ways to do this. I
> know
> > that sometimes people are wanting to get a large amount of rows into Excel
> > for further analysis but it would be better to be using DTS and getting
> the
> > data out in CSV for them. Much much faster process.
> >
> > If the output is not that many records but you have that many because you
> > are using filters then try to move away from filters. Filters brings over
> > all the data and then filters it. Use a query parameter instead.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in
> message
> > news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> > > Hi all,
> > >
> > > I am running reporting services on a dedicated server with 2GB of
> memory,
> > > when ever a report is run that contains over 90,000 records the server
> > > crashes and restarts. We have looked at the reports, and are to reduce
> the
> > > criteria range to make smaller reports, but it then affects role the
> > system
> > > was designed to play in my organisation. If anyone has come across
> these
> > > memory crashes and come across any ways of resolving them or ways that
> > > haven't resolved them, including an increase in the memory size, it
> would
> > be
> > > greatly appreciated if you could reply. We are willing and able to
> throw
> > > another 2GB at the problem, but are concerned this may only be a short
> > term
> > > solution and the problem will reoccur when the reporting size reaches
> > 180,000
> > > records.
> > >
> > > Any assistance you may be able to provide would be greatly appreciated
> >
> >
>
>|||What you could do is have an intermediary report that does a count and then
provides the appropriate message and link. You can hide the real report from
the user in list view so they have to go through this. Also you could use
jump to url and render to Excel.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in message
news:29D8180F-C3A4-414B-BE75-1FE9E908AA0F@.microsoft.com...
> Bruce,
> Thanks for your responses.
> Do you know if there is away to detect if the report is going to generate
> over 200 pages and return a message to the user to say something like
'This
> report may crash the server, are you sure you want to continue' ? From
your
> responses, and others I have read about memory, and RS limitations are the
> only issue I am encountering, not programming issues, so if we confirm
with
> users before they run large reports it could solve our problems, unless
they
> select 'Yes' of course.
> Any further assistance you may be able to provide would be greatly
> appreciated.
> "Bruce L-C [MVP]" wrote:
> > You might try CSV and see if it takes up less memory than Excel.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:u5P0mDYUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> > > If you mean that the result set has 90,000 records or 180,000 records
then
> > > you have the wrong product. RS is not designed to generate reports
that
> > are
> > > 1500+ pages. Rendering is done in RAM so there is a direct correlation
to
> > > between number of records and the amount of RAM used. If the rendering
> > > output is Excel or PDF then the amount of RAM consumed is even more.
If
> > the
> > > destination is another program then there are better ways to do this.
I
> > know
> > > that sometimes people are wanting to get a large amount of rows into
Excel
> > > for further analysis but it would be better to be using DTS and
getting
> > the
> > > data out in CSV for them. Much much faster process.
> > >
> > > If the output is not that many records but you have that many because
you
> > > are using filters then try to move away from filters. Filters brings
over
> > > all the data and then filters it. Use a query parameter instead.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in
> > message
> > > news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> > > > Hi all,
> > > >
> > > > I am running reporting services on a dedicated server with 2GB of
> > memory,
> > > > when ever a report is run that contains over 90,000 records the
server
> > > > crashes and restarts. We have looked at the reports, and are to
reduce
> > the
> > > > criteria range to make smaller reports, but it then affects role the
> > > system
> > > > was designed to play in my organisation. If anyone has come across
> > these
> > > > memory crashes and come across any ways of resolving them or ways
that
> > > > haven't resolved them, including an increase in the memory size, it
> > would
> > > be
> > > > greatly appreciated if you could reply. We are willing and able to
> > throw
> > > > another 2GB at the problem, but are concerned this may only be a
short
> > > term
> > > > solution and the problem will reoccur when the reporting size
reaches
> > > 180,000
> > > > records.
> > > >
> > > > Any assistance you may be able to provide would be greatly
appreciated
> > >
> > >
> >
> >
> >|||Thanks for the feedback Bruce I will give it a go !|||Hi There,
We are having a problem in listing subscriptions in Report manager and
getting "OutOfMemory" exception.
Our Application has an event-based subscription management system, and
every-time an event gets fired on the Application-side, it creates an
one-off subscription on the Reporting Services. So overtime the number of
Subscriptions created on the reporting services has grown, and a particular
report has about 8000+ subscriptions to date now.
So when we try to manage the subscriptions (try to delete the irrelevant
ones) in the RS "Report Manager", for that particular report, System gives
an OutOfMemory exception. I think Report Manager Calls internally
"ListSubscriptions" method (as explained in 840709) and couldn't cope up
with.
And i looked at MSDN Knowledgebase Article:840709, and increased the
"MemoryLimit" setting in RSReportServer.config, but there was no use.
By the way our Server has 2GB of RAM and we use Custom Authentication on
Reporting Services.
I posted this question here, because I thought the problem is similar to
what you were talking (Memory management Issue).
Any Suggestions are appreciated.
Regards
Raj Chidipudi
"William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in message
news:29D8180F-C3A4-414B-BE75-1FE9E908AA0F@.microsoft.com...
> Bruce,
> Thanks for your responses.
> Do you know if there is away to detect if the report is going to generate
> over 200 pages and return a message to the user to say something like
'This
> report may crash the server, are you sure you want to continue' ? From
your
> responses, and others I have read about memory, and RS limitations are the
> only issue I am encountering, not programming issues, so if we confirm
with
> users before they run large reports it could solve our problems, unless
they
> select 'Yes' of course.
> Any further assistance you may be able to provide would be greatly
> appreciated.
> "Bruce L-C [MVP]" wrote:
> > You might try CSV and see if it takes up less memory than Excel.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:u5P0mDYUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> > > If you mean that the result set has 90,000 records or 180,000 records
then
> > > you have the wrong product. RS is not designed to generate reports
that
> > are
> > > 1500+ pages. Rendering is done in RAM so there is a direct correlation
to
> > > between number of records and the amount of RAM used. If the rendering
> > > output is Excel or PDF then the amount of RAM consumed is even more.
If
> > the
> > > destination is another program then there are better ways to do this.
I
> > know
> > > that sometimes people are wanting to get a large amount of rows into
Excel
> > > for further analysis but it would be better to be using DTS and
getting
> > the
> > > data out in CSV for them. Much much faster process.
> > >
> > > If the output is not that many records but you have that many because
you
> > > are using filters then try to move away from filters. Filters brings
over
> > > all the data and then filters it. Use a query parameter instead.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in
> > message
> > > news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> > > > Hi all,
> > > >
> > > > I am running reporting services on a dedicated server with 2GB of
> > memory,
> > > > when ever a report is run that contains over 90,000 records the
server
> > > > crashes and restarts. We have looked at the reports, and are to
reduce
> > the
> > > > criteria range to make smaller reports, but it then affects role the
> > > system
> > > > was designed to play in my organisation. If anyone has come across
> > these
> > > > memory crashes and come across any ways of resolving them or ways
that
> > > > haven't resolved them, including an increase in the memory size, it
> > would
> > > be
> > > > greatly appreciated if you could reply. We are willing and able to
> > throw
> > > > another 2GB at the problem, but are concerned this may only be a
short
> > > term
> > > > solution and the problem will reoccur when the reporting size
reaches
> > > 180,000
> > > > records.
> > > >
> > > > Any assistance you may be able to provide would be greatly
appreciated
> > >
> > >
> >
> >
> >|||I have exact same problem. My report is about 28000 rows and it takes about
15 minutes to render and end user gets frustrated and he tries to "End Task"
the browser - every thing hangs up. I can't put drill down, etc., so that
is not my option. Is there any way I can stream data, instead of wait to
retreive all rows from SQL? - just like SQL Query analyzer window - it starts
producing results as soon as you execute the query. This is really a big
issue in our organization. Because of this, people started to hate RS. I
need some kind of solution asap. Please help.
"Bruce L-C [MVP]" wrote:
> You might try CSV and see if it takes up less memory than Excel.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:u5P0mDYUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> > If you mean that the result set has 90,000 records or 180,000 records then
> > you have the wrong product. RS is not designed to generate reports that
> are
> > 1500+ pages. Rendering is done in RAM so there is a direct correlation to
> > between number of records and the amount of RAM used. If the rendering
> > output is Excel or PDF then the amount of RAM consumed is even more. If
> the
> > destination is another program then there are better ways to do this. I
> know
> > that sometimes people are wanting to get a large amount of rows into Excel
> > for further analysis but it would be better to be using DTS and getting
> the
> > data out in CSV for them. Much much faster process.
> >
> > If the output is not that many records but you have that many because you
> > are using filters then try to move away from filters. Filters brings over
> > all the data and then filters it. Use a query parameter instead.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in
> message
> > news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> > > Hi all,
> > >
> > > I am running reporting services on a dedicated server with 2GB of
> memory,
> > > when ever a report is run that contains over 90,000 records the server
> > > crashes and restarts. We have looked at the reports, and are to reduce
> the
> > > criteria range to make smaller reports, but it then affects role the
> > system
> > > was designed to play in my organisation. If anyone has come across
> these
> > > memory crashes and come across any ways of resolving them or ways that
> > > haven't resolved them, including an increase in the memory size, it
> would
> > be
> > > greatly appreciated if you could reply. We are willing and able to
> throw
> > > another 2GB at the problem, but are concerned this may only be a short
> > term
> > > solution and the problem will reoccur when the reporting size reaches
> > 180,000
> > > records.
> > >
> > > Any assistance you may be able to provide would be greatly appreciated
> >
> >
>
>
Thursday, March 8, 2012
AS2005 September CTP Memory Error
One important information: I have installed Sept CTP on clean Windows XP Pro, there were no previous versions of SQL 2005 installed.
Any ideas what's up? Maybe some missing parameter in connection string?I'm having the same problem after I uninstalled the June CTP and installed Sept CTP. I'm getting the error in BIDS and SSMS, I can browse dimensions but when I try to drag a dimension or measure onto cube browser I get the error. I tried deleting the AS database, redeploying, and reprocessing and I'm still getting the error. Any help would be appreciated.|||
From another message board:
This is a known issue with the September CTP and it has been fixed in
post-CTP builds. The problem surfaces with client-side record sizes greater
than 1k, so a possible workaround might be to rework the cube structure to
reduce record sizes.
--
-Raman Iyer
[This posting is provided "AS IS" with no warranties, and confers no
rights.]
Any solutions to this? Any measure,dimension that i use causes the problem.
Reworking the cube structure to reduce record sizes? Anyone been successfull w/ that?
Are there any post-CTP (september) builds available
thanks
|||ttt
Same problem here.
There seems, however, to be some users on the forum who have gotten through the AS2005 tutorial without a hitch. I would assume these people did not have to reduce record sizes or anything like that.
Has anyone had any luck yet? Has anyone tried building a cube from non-AdventureWorks data? I tried but had a problem deploying so I didn't get to even test the memory error.
Anyone from MS have a solution or workaround?|||I encountered this very same error. Can you tell me where I could find a post-CTP build?|||Same problem here. Was this functionality available in earlier betas, and did it work properly? I suppose that would be far easier than trying to get a NEWER build...
|||I have built and depolyed a non-Adventureworks cube on September CTP with no problems at all.
|||I tried deploying a cube from a SQL 2000 data source using the September CTP, but ran into the same problem. Do you have any idea whether your successful deployment had a smaller string size and if so how it got one?
|||I currently use April CTP and everything works fine. I encountered two minor problems: slow first initialization of metadata during reading it with ADOMD (not NET) and there is no way to get value of unary operator property of member.
Best regards!|||Yup. Same here. Any chance of a hotfix or something? This basically halts SSAS development for my team on Sept CTP (we will have to roll back :P)
|||
Luther Smith wrote:
I tried deploying a cube from a SQL 2000 data source using the September CTP, but ran into the same problem. Do you have any idea whether your successful deployment had a smaller string size and if so how it got one?
Sorry, I wasn't specific enough - I just meant that I had deployed non-AdventureWorks cubes sucessfully on SeptCTP. I don't know how to find out the string size, but if you tell me, I'll check it out and let y'all know.
disclaimer - I'm using SQL2005 Sept CTP x64|||
Same problem here.
I've been trying to track this down for a few days now. The problem is reportably fixed in the Nov release, but no hot fix is available.
Based on some configuration testing, the problem appears to be in either the PTS layer or specifically in the OLE DB drivers. I managed to recreate the issue in the June CTP (CTP 15) configuration, where the Sept drivers were installed. I could access the server fine from a remote PC, but localling, the error poped up.
|||Is there a download for the hotfix? It seems ridiculous to have to back out the CTP16 or to have to wait until the RTM is released. I need to start developing on SSAS 2005 immediately. Help?
|||I agree. I have gone through all sorts of uninstalls and reinstalls between the June and September CTP's. Nothing worked to get past the memory error. I need to start learning 2005 right away!AS2005 September CTP Memory Error
One important information: I have installed Sept CTP on clean Windows XP Pro, there were no previous versions of SQL 2005 installed.
Any ideas what's up? Maybe some missing parameter in connection string?I'm having the same problem after I uninstalled the June CTP and installed Sept CTP. I'm getting the error in BIDS and SSMS, I can browse dimensions but when I try to drag a dimension or measure onto cube browser I get the error. I tried deleting the AS database, redeploying, and reprocessing and I'm still getting the error. Any help would be appreciated.|||
From another message board:
This is a known issue with the September CTP and it has been fixed in
post-CTP builds. The problem surfaces with client-side record sizes greater
than 1k, so a possible workaround might be to rework the cube structure to
reduce record sizes.
--
-Raman Iyer
[This posting is provided "AS IS" with no warranties, and confers no
rights.]
Any solutions to this? Any measure,dimension that i use causes the problem.
Reworking the cube structure to reduce record sizes? Anyone been successfull w/ that?
Are there any post-CTP (september) builds available
thanks|||ttt
Same problem here.
There seems, however, to be some users on the forum who have gotten through the AS2005 tutorial without a hitch. I would assume these people did not have to reduce record sizes or anything like that.
Has anyone had any luck yet? Has anyone tried building a cube from non-AdventureWorks data? I tried but had a problem deploying so I didn't get to even test the memory error.
Anyone from MS have a solution or workaround?|||I encountered this very same error. Can you tell me where I could find a post-CTP build?|||Same problem here. Was this functionality available in earlier betas, and did it work properly? I suppose that would be far easier than trying to get a NEWER build...|||I have built and depolyed a non-Adventureworks cube on September CTP with no problems at all.|||I tried deploying a cube from a SQL 2000 data source using the September CTP, but ran into the same problem. Do you have any idea whether your successful deployment had a smaller string size and if so how it got one?|||I currently use April CTP and everything works fine. I encountered two minor problems: slow first initialization of metadata during reading it with ADOMD (not NET) and there is no way to get value of unary operator property of member.
Best regards!|||Yup. Same here. Any chance of a hotfix or something? This basically halts SSAS development for my team on Sept CTP (we will have to roll back :P)|||
Luther Smith wrote:
I tried deploying a cube from a SQL 2000 data source using the September CTP, but ran into the same problem. Do you have any idea whether your successful deployment had a smaller string size and if so how it got one?
Sorry, I wasn't specific enough - I just meant that I had deployed non-AdventureWorks cubes sucessfully on SeptCTP. I don't know how to find out the string size, but if you tell me, I'll check it out and let y'all know.
disclaimer - I'm using SQL2005 Sept CTP x64|||
Same problem here.
I've been trying to track this down for a few days now. The problem is reportably fixed in the Nov release, but no hot fix is available.
Based on some configuration testing, the problem appears to be in either the PTS layer or specifically in the OLE DB drivers. I managed to recreate the issue in the June CTP (CTP 15) configuration, where the Sept drivers were installed. I could access the server fine from a remote PC, but localling, the error poped up.
|||Is there a download for the hotfix? It seems ridiculous to have to back out the CTP16 or to have to wait until the RTM is released. I need to start developing on SSAS 2005 immediately. Help?
|||I agree. I have gone through all sorts of uninstalls and reinstalls between the June and September CTP's. Nothing worked to get past the memory error. I need to start learning 2005 right away!AS2005 September CTP Memory Error
One important information: I have installed Sept CTP on clean Windows XP Pro, there were no previous versions of SQL 2005 installed.
Any ideas what's up? Maybe some missing parameter in connection string?I'm having the same problem after I uninstalled the June CTP and installed Sept CTP. I'm getting the error in BIDS and SSMS, I can browse dimensions but when I try to drag a dimension or measure onto cube browser I get the error. I tried deleting the AS database, redeploying, and reprocessing and I'm still getting the error. Any help would be appreciated.|||
From another message board:
This is a known issue with the September CTP and it has been fixed in
post-CTP builds. The problem surfaces with client-side record sizes greater
than 1k, so a possible workaround might be to rework the cube structure to
reduce record sizes.
--
-Raman Iyer
[This posting is provided "AS IS" with no warranties, and confers no
rights.]
Any solutions to this? Any measure,dimension that i use causes the problem.
Reworking the cube structure to reduce record sizes? Anyone been successfull w/ that?
Are there any post-CTP (september) builds available
thanks
|||ttt
Same problem here.
There seems, however, to be some users on the forum who have gotten through the AS2005 tutorial without a hitch. I would assume these people did not have to reduce record sizes or anything like that.
Has anyone had any luck yet? Has anyone tried building a cube from non-AdventureWorks data? I tried but had a problem deploying so I didn't get to even test the memory error.
Anyone from MS have a solution or workaround?|||I encountered this very same error. Can you tell me where I could find a post-CTP build?|||Same problem here. Was this functionality available in earlier betas, and did it work properly? I suppose that would be far easier than trying to get a NEWER build...
|||I have built and depolyed a non-Adventureworks cube on September CTP with no problems at all.
|||I tried deploying a cube from a SQL 2000 data source using the September CTP, but ran into the same problem. Do you have any idea whether your successful deployment had a smaller string size and if so how it got one?
|||I currently use April CTP and everything works fine. I encountered two minor problems: slow first initialization of metadata during reading it with ADOMD (not NET) and there is no way to get value of unary operator property of member.
Best regards!|||Yup. Same here. Any chance of a hotfix or something? This basically halts SSAS development for my team on Sept CTP (we will have to roll back :P)
|||
Luther Smith wrote:
I tried deploying a cube from a SQL 2000 data source using the September CTP, but ran into the same problem. Do you have any idea whether your successful deployment had a smaller string size and if so how it got one?
Sorry, I wasn't specific enough - I just meant that I had deployed non-AdventureWorks cubes sucessfully on SeptCTP. I don't know how to find out the string size, but if you tell me, I'll check it out and let y'all know.
disclaimer - I'm using SQL2005 Sept CTP x64|||
Same problem here.
I've been trying to track this down for a few days now. The problem is reportably fixed in the Nov release, but no hot fix is available.
Based on some configuration testing, the problem appears to be in either the PTS layer or specifically in the OLE DB drivers. I managed to recreate the issue in the June CTP (CTP 15) configuration, where the Sept drivers were installed. I could access the server fine from a remote PC, but localling, the error poped up.
|||Is there a download for the hotfix? It seems ridiculous to have to back out the CTP16 or to have to wait until the RTM is released. I need to start developing on SSAS 2005 immediately. Help?
|||I agree. I have gone through all sorts of uninstalls and reinstalls between the June and September CTP's. Nothing worked to get past the memory error. I need to start learning 2005 right away!AS2005 error
Attempted to read or write protected memory. This is often an indication
that other memory is corrupt.
SP2 has been installed more than two weeks ago and everything was working
since then.
Any help is appreciated.
GIlgameshHi! Gilgamesh
Were you able to come over this issue? If so can you please let me know why
this issue came up? I am facing the same problem now.
--thanks and regards,
Prasad
AS2005 error
Attempted to read or write protected memory. This is often an indication
that other memory is corrupt.
SP2 has been installed more than two weeks ago and everything was working
since then.
Any help is appreciated.
GIlgamesh
Hi! Gilgamesh
Were you able to come over this issue? If so can you please let me know why
this issue came up? I am facing the same problem now.
--thanks and regards,
Prasad
AS2005 Cube Processing
I have problems with processing cubes on AS2005. I get this error message while processing:
Memory error: Allocation failure: Not enough storage is available to process this command. Error in the OLAP storage engine.
This machine has 6GB of memory and once it hits 3.5GB the processing stops. AWE is turned on.
I would appreciate, if anyone can help on this.
Thanks,
Gopal
Analysis Services is not AWE aware. So it can only use 2GB on a 32bit box (3GB if you have /3GB switch in boot.ini). Looks like the processing operation is running out of memory.
What are the number and sizes of dimension and partition tables? Do you have aggregations? Look at the processing log (in the process dialog) and find out what is the operation that causes out of memory - dimension processing, partition processing, building aggregations, etc.
This could also happen due to excessive parallelism. Try processing the dimensions and partitions individually.
|||Thanks for your reply.
I managed to process the partitions and dimensions individually. That worked.
Gopal
|||Hi.
Just a short question:
Is it really true that SSAS can use only 2GB RAM on a 32bit system? The technical reference for SQL Server 2005 Standard and Enterprise states that SQL Server can use the amount of RAM supported by the operating system which is 4GB for a Server 2003 Standard edition and 32GB for a Server 2003 Enterprise edition, both on 32bit systems. Does this apply only to the SQL Server Database Engine then and not the Analysis Services Server?
Regards
Kjetil
|||T.K. Anand wrote:
Analysis Services is not AWE aware. So it can only use 2GB on a 32bit box (3GB if you have /3GB switch in boot.ini). Looks like the processing operation is running out of memory.
What are the number and sizes of dimension and partition tables? Do you have aggregations? Look at the processing log (in the process dialog) and find out what is the operation that causes out of memory - dimension processing, partition processing, building aggregations, etc.
This could also happen due to excessive parallelism. Try processing the dimensions and partitions individually.
In the 64-bit versions of Analysis Services...has this memory use limit been removed?
...cordell...
|||Yes - 64bit version doesn't have these limitations.AS2000 problem!
We are developing a As 2000 solution and when processing the cube we get an Eventlog message 'The memory conversation threshold was exceeded by more then 12,5% for an extended period'!
Does anyone have any idea about this problem/issue?
Larra
Try upgrading to Service Pack 4. There was a similar issue reported against AS2000 SP3 that was fixed in SP4.
http://www.microsoft.com/downloads/info.aspx?na=22&p=1&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3d8e2dfc8d-c20e-4446-99a9-b7f0213f8bc5%26DisplayLang%3den
Hope that helps.
AS2000 only using 2GB even when /3GB is activated
I've done everything to enable the use of more than 2GB memory for AS 2000 according to http://support.microsoft.com/kb/295443
Still, according to the task manager, the process msmdsrv.exe stops growing around 1 996 000 kB.
I've added the /3GB in boot.ini and set the Memory Conservation Threshold in Analysis Manager to 2.7 gb.
I've also checked that the HighMemoryLimit was set accordingly in the registry.
OS: Windows 2003 server Enterprise Edition
Memory: 3 GB
AS2000 sp4
Any other settings/limitiation I've missed?
Actually, later today the memory usage finally past 2GB, obviously it wasn't needed until then. I stayed at 1.9 so long even though users were querying it heavily so I thought it didn't work. Thanks, and sorry.
One related follow-up question though: when AS gets out of memory while processing cubes it automatically reboots and we get the "Connection to server is lost" when the transaction is to be commited. Will the same thing happen if AS reach the memory limit while users are querying the cube? Will the service be rebooted and queries be aborted, or will it just show as worse performance for the users? Or will AS free up memory automatically when we're getting close to the memory limit?
|||Server restart is not a normal thing. During commit though, when it half-way through commiting, and there is any kind of error happening (like out of memory), it can no longer go back, but it cannot leave transaction half-commited either. So AS simply restarts, and finishes commit on restart. This kind of restart won't happen if out-of-memory happens during querying.Thursday, February 9, 2012
are temp tables written to disk?
table and that the write will not take place till later, there will be no
performance difference on this point?
Let me ask again, "Will temp tables write to disk at some point in time even
during or after the stored procedure is over, OR is there a certain
condition where temp table DO NOT write to disk ever?"
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:%232WwUHBtFHA.2076@.TK2MSFTNGP14.phx.gbl...
Temp tables vs table variables is a fairly common debate topic.
The person you're talking about is kind of right. Every database write
happens to memory first and so, in theory, query performance shouldn't
really be hindered by I/O to disk (as that is essentially done
asynchronously by a background spid). But this assumes you have no great
memory pressure.
IMHO there's really not enough difference, performance-wise, to be
terribly concerned about it. Temp tables are just like any other permanent
table (except that they are automatically dropped when they go out of scope)
and as such SQL Server maintains statistics on the columns in those temp
tables (SQL Server does not collect statistics on table variables). As such
the query optimiser can often come up with better plans for temp tables
(than it can with table variables) and manipulate the data in them faster.
However, table variables implement less locking generally...so it can swing
the other way too. The main reason I would use a table variable would be if
I wanted to return a result set from a UDF, because you can only do that
using a table variable. So that would be a functionality reason more so
than a performance reason.
Aaron Bertrand (SQL MVP) has a great website called ASPFAQ that has heaps
of different articles about different things people ask about SQL Server.
Here's what he says about temp tables vs table variables:
Should I use a #temp table or a @.table variable?
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
I "heard" that since SQL Server 2000 caches the database, that temp
tables are written to memory before disk? and thus there is no significant
performance difference between temp table and table variables if you have
lots of memory to begin with.
I really never heard of this, but I really think this person was just
trying to cover their butt on their lack of knowledge as well as critical
mistake
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:em9qWbAtFHA.3236@.TK2MSFTNGP09.phx.gbl...
Yes they are written to disk, although it's often for only a short
period of time. All database writes, including writes to tempdb which is in
effect handled just like normal user databases, in SQL Server are written to
memory first (making the page in memory "dirty") and then flushed to disk
when the lazy writer process gets around to it.
I guess, in theory, the temp table may be dropped before the lazy
writer writes that data to disk and so it might not ever make it to the disk
but I've never tried to analyse that scenario.
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
are temp tables written to disk? or are they cached like the database is
cached on queries.That really did NOT help.
Is there a case or situation where a #temp table writes to DISK?
And if so, what is this case or situation where a temp table is written to
disk?
"doller" <sufianarif@.gmail.com> wrote in message
news:1126143644.984768.213460@.z14g2000cwz.googlegroups.com...
> Hi,
> If u talk about Temp Table then u should know the meaning of temporary.
> When u create a table using # then MS-SQl Server creates a
> table(structure in Buffer to hold the data)
> and as u finh working with that it frees the memory. Temporary table
> name is basically a alias name for the buffer reserved for
> that temporary data.
> If u execute a query from QA (select * into #temp from temp) then it
> will be in that connection scope till u do not close the connection.
> If u execute a Stored Procedure then as the stored produre execution
> finished the scope of temo file will be end.
> hope this helps u
> from
> Doller
>
>
> Mike Hodgson wrote:
<http://www.microsoft.com/communitie...ams=%7eCMTYData
SvcParams%5e%7earg+Name%3d%22guid%22+Val
ue%3d%22cf9c9e5b-abcf-4495-925b-0cd5
15fcdc89%22%2f%5e%7esParams%5e%7e%2fsPar
ams%5e%7e%2fCMTYDataSvcParams%5e>
is
>|||Ok, it sounds that temp tables in the tempdb will somehow. and in some way.
eventually some other time, make a write to disk.
Thus, the person who I heard that temp tables have the same performance with
regards to table variables is wrong as temp tables somehow write to disk. A
nd in so writing to disk, temp tables would be a performance hit at some poi
nt in time.
Thus, one should try to use temp variables just like it says from microsoft
"if you can and if you need to" before using temp tables. right? I know whe
n I did and it makes a huge difference
http://support.microsoft.com/defaul...kb;en-us;305977
[at the very bottom of the article]
" In general, you use table variables whenever possible except when there is
a significant volume of data and there is repeated use of the table. In tha
t case, you can create indexes on the temporary table to increase query perf
ormance. However, each scenario may be different. Microsoft recommends that
you test if table variables are more helpful than temporary tables for a par
ticular query or stored procedure."
NEVERTHELESS, this part below is a really bad answer from microsoft does NOT
seem true in REAL LIFE as I know from experience, whether I had enough memo
ry or not, table variables are done in memory first, and then , I THINK, ove
rflow are written to disk if necessary. As for Temp Tables, well, one is not
sure what's going on, is it memory or disk? And who is to say SQL Server is
managing this memory correctly anyway and if it's flushed in time for the n
ext stored procedure execution.
Q4: Are table variables memory-only structures that are assured better perfo
rmance as compared to temporary or permanent tables, because they are mainta
ined in a database that resides on the physical disk?
A4: A table variable is not a memory-only structure. Because a table variabl
e might hold more data than can fit in memory, it has to have a place on dis
k to store data. Table variables are created in the tempdb database similar
to temporary tables. If memory is available, both table variables and tempor
ary tables are created and processed while in memory (data cache).
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:uKi
Ol1BtFHA.1168@.TK2MSFTNGP10.phx.gbl...
In thinking this through logically in my own mind, I would say that writes t
o temp tables will always result in one or more writes to disk (although tha
t write may be deferred). This is because once the page is marked dirty in
RAM, even if the page is changed yet again at a later stage to its original
values (by dropping the temp table), the lazy writer will come along and flu
sh that page to disk (i.e. copy what's in memory for that 8K page and write
in in the appropriate place in the relevant data file). The temp table gett
ing dropped (whether automatically or manually) will not reset the dirty pag
e status; only flushing the page to disk will reset the status of that page.
Prior to SQL 7 you used to be able to force SQL Server to materialise tempdb
only in RAM, which would mean temp table writes wouldn't make it to disk, b
ut that's no longer supported in SQL 2000 (tempdb is always on disk).
If nothing else, the transactions associated with creating, populating, mani
pulating and dropping the temp table result in the creation of transaction l
og records. With tempdb those records are continually truncated but not bef
ore they have been committed & flushed to disk. So temp table writes will r
esult in transaction log I/O activity in tempdb.
These are just my thoughts and are based on my understanding of how SQL Serv
er works, not on any particular whitepaper I've read or anything like that.
Perhaps someone better qualified (like Kalen Delaney, Tibor Karaszi, Andrew
Kelly, Paul Randal, Mike Epprecht, etc.) might be able to clarify more.
Having said that, this whitepaper does a pretty good job of explaining SQL I
/O including flushing dirty pages, async I/O, lazy writes and flushing log r
ecords, but skimming it I can't see that it addresses your specific question
exactly, but is very good background info about SQL I/O:
SQL Server 2000 I/O Basics
Hope this helps.
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
So are you saying that since every database write goes to memory for a temp
table and that the write will not take place till later, there will be no pe
rformance difference on this point?
Let me ask again, "Will temp tables write to disk at some point in time even
during or after the stored procedure is over, OR is there a certain conditi
on where temp table DO NOT write to disk ever?"
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%23
2WwUHBtFHA.2076@.TK2MSFTNGP14.phx.gbl...
Temp tables vs table variables is a fairly common debate topic.
The person you're talking about is kind of right. Every database write happ
ens to memory first and so, in theory, query performance shouldn't really be
hindered by I/O to disk (as that is essentially done asynchronously by a ba
ckground spid). But this assumes you have no great memory pressure.
IMHO there's really not enough difference, performance-wise, to be terribly
concerned about it. Temp tables are just like any other permanent table (ex
cept that they are automatically dropped when they go out of scope) and as s
uch SQL Server maintains statistics on the columns in those temp tables (SQL
Server does not collect statistics on table variables). As such the query
optimiser can often come up with better plans for temp tables (than it can w
ith table variables) and manipulate the data in them faster. However, table
variables implement less locking generally...so it can swing the other way
too. The main reason I would use a table variable would be if I wanted to r
eturn a result set from a UDF, because you can only do that using a table va
riable. So that would be a functionality reason more so than a performance
reason.
Aaron Bertrand (SQL MVP) has a great website called ASPFAQ that has heaps of
different articles about different things people ask about SQL Server. Her
e's what he says about temp tables vs table variables:
Should I use a #temp table or a @.table variable?
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
I "heard" that since SQL Server 2000 caches the database, that temp tables a
re written to memory before disk? and thus there is no significant performa
nce difference between temp table and table variables if you have lots of me
mory to begin with.
I really never heard of this, but I really think this person was just trying
to cover their butt on their lack of knowledge as well as critical mistake
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:em9
qWbAtFHA.3236@.TK2MSFTNGP09.phx.gbl...
Yes they are written to disk, although it's often for only a short period of
time. All database writes, including writes to tempdb which is in effect h
andled just like normal user databases, in SQL Server are written to memory
first (making the page in memory "dirty") and then flushed to disk when the
lazy writer process gets around to it.
I guess, in theory, the temp table may be dropped before the lazy writer wri
tes that data to disk and so it might not ever make it to the disk but I've
never tried to analyse that scenario.
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
are temp tables written to disk? or are they cached like the database is
cached on queries.|||I think the take home message is test it.
Try both certainly. Sometimes temp tables will be quicker, sometimes
table variables will be quicker. Each has their place. When you need
to create indexes on your temporary objects - temp tables. When you
need to return datasets from functions (and not just a single scalar) -
table variables. Scope is definitely simpler with table variables. The
reason there are less recompilations of procs using table variables (as
opposed to temp tables) is because the query optimiser doesn't need to
come up with a plan of how to access the data in the table variables
(it's always the same) as there is only 1 way, a table scan, but with
temp tables there can be indexes & density statistics, which make many
more access paths possible.
I wouldn't say the person you were talking to was wrong per se. He was
pretty close. And, unless we're talking about HUGE amounts of temp
table/table variable activity, I'd say debating the performance hit due
to I/O is really splitting straws.
I did do a few tests with this though (with 10 sample iterations for
each average) and came up with the following (and this is on a
completely unloaded, dedicated server - dual Xeon, 2GB RAM, local RAID1
array):
_
_*avg batch duration (msec)*
_ #rows_ _table variable_ _temp table_
512 15.1 14.5
1024 17.9 29.4
2048 29.2 39.9
4096 61.8 71.6
8192 127.9 145.7
16384 254.2 291.7
32768 544.6 583.0
65536 1117.4 1103.4
131072 2174.3 2224.2
262144 4377.1 4553.2
524288 8852.1 8402.0
1048576 17846.6 16188.6
2097152 36335.5 32739.4
As you can see, for small batches the duration is almost identical,
although temp tables are, in general slightly slower. And as the
batches become larger the averages tended to drift apart a bit with temp
tables being quicker. In any case, when you graph this, the 2 lines are
almost on top of each other.
The batches consisted of just this loop essentially (with #tmp instead
of @.mytabvar for the temp table runs):
declare @.i int
set @.i = 1
insert into @.mytabvar (blah) values ('This is a line of text')
while (@.i < 22)
begin
insert into @.mytabvar (blah) select blah from @.mytabvar
set @.i = @.i + 1
end
So you can see it was just doing selects & inserts. The temp table #tmp
was defined as exactly the same as @.mytabvar, with no indexes.
Obviously, the difference, performance-wise, between table variables &
temp tables, comes into play much more when things get more complex -
locking contention, many spids executing the same code simultaneously,
indexes, complex queries, etc.
I also noticed that using table variables always resulted in a user
table being created in tempdb, just like with temp tables:
declare @.mytabvar TABLE
(
ID int identity(1,1) not null,
blah varchar(30) null
)
select * from tempdb.dbo.sysobjects where type = 'U'
CREATE TABLE #tmp
(
ID int identity(1,1) not null,
blah varchar(30) null
)
select * from tempdb.dbo.sysobjects where type = 'U'
drop table #tmp
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
> Ok, it sounds that temp tables in the tempdb will somehow. and in some
> way. eventually some other time, make a write to disk.
> Thus, the person who I heard that temp tables have the same
> performance with regards to table variables is wrong as temp tables
> somehow write to disk. And in so writing to disk, temp tables would be
> a performance hit at some point in time.
> Thus, one should try to use temp variables just like it says from
> microsoft "if you can and if you need to" before using temp tables.
> right? I know when I did and it makes a huge difference
> http://support.microsoft.com/defaul...kb;en-us;305977
>
> [at the very bottom of the article]
> " In general, you use table variables whenever possible except
> when there is a significant volume of data and there is repeated
> use of the table. In that case, you can create indexes on the
> temporary table to increase query performance. However, each
> scenario may be different. Microsoft recommends that you test if
> table variables are more helpful than temporary tables for a
> particular query or stored procedure."
>
> NEVERTHELESS, this part below is a really bad answer from microsoft
> does NOT seem true in REAL LIFE as I know from experience, whether I
> had enough memory or not, table variables are done in memory first,
> and then , I THINK, overflow are written to disk if necessary. As for
> Temp Tables, well, one is not sure what's going on, is it memory or
> disk? And who is to say SQL Server is managing this memory correctly
> anyway and if it's flushed in time for the next stored procedure
> execution.
>
> *Q4: Are table variables memory-only structures that are assured
> better performance as compared to temporary or permanent tables,
> because they are maintained in a database that resides on the
> physical disk?*
> *A4:* A table variable is not a memory-only structure. Because a
> table variable might hold more data than can fit in memory, it has
> to have a place on disk to store data. Table variables are created
> in the *tempdb* database similar to temporary tables. If memory is
> available, both table variables and temporary tables are created
> and processed while in memory (data cache).
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
> news:uKiOl1BtFHA.1168@.TK2MSFTNGP10.phx.gbl...
> In thinking this through logically in my own mind, I would say that
> writes to temp tables will always result in one or more writes to disk
> (although that write may be deferred). This is because once the page
> is marked dirty in RAM, even if the page is changed yet again at a
> later stage to its original values (by dropping the temp table), the
> lazy writer will come along and flush that page to disk (i.e. copy
> what's in memory for that 8K page and write in in the appropriate
> place in the relevant data file). The temp table getting dropped
> (whether automatically or manually) will not reset the dirty page
> status; only flushing the page to disk will reset the status of that page.
> Prior to SQL 7 you used to be able to force SQL Server to materialise
> tempdb only in RAM, which would mean temp table writes wouldn't make
> it to disk, but that's no longer supported in SQL 2000 (tempdb is
> always on disk).
> If nothing else, the transactions associated with creating,
> populating, manipulating and dropping the temp table result in the
> creation of transaction log records. With tempdb those records are
> continually truncated but not before they have been committed &
> flushed to disk. So temp table writes will result in transaction log
> I/O activity in tempdb.
> These are just my thoughts and are based on my understanding of how
> SQL Server works, not on any particular whitepaper I've read or
> anything like that. Perhaps someone better qualified (like Kalen
> Delaney, Tibor Karaszi, Andrew Kelly, Paul Randal, Mike Epprecht,
> etc.) might be able to clarify more.
> Having said that, this whitepaper does a pretty good job of explaining
> SQL I/O including flushing dirty pages, async I/O, lazy writes and
> flushing log records, but skimming it I can't see that it addresses
> your specific question exactly, but is very good background info about
> SQL I/O:
> SQL Server 2000 I/O Basics
> Hope this helps.
> --
> mike hodgson
> blog: http://sqlnerd.blogspot.com
>
> Yu6454 wrote:
> So are you saying that since every database write goes to memory for a
> temp table and that the write will not take place till later, there
> will be no performance difference on this point?
> Let me ask again, "Will temp tables write to disk at some point in
> time even during or after the stored procedure is over, OR is there a
> certain condition where temp table DO NOT write to disk ever?"
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
> news:%232WwUHBtFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Temp tables vs table variables is a fairly common debate topic.
> The person you're talking about is kind of right. Every database
> write happens to memory first and so, in theory, query performance
> shouldn't really be hindered by I/O to disk (as that is essentially
> done asynchronously by a background spid). But this assumes you have
> no great memory pressure.
> IMHO there's really not enough difference, performance-wise, to be
> terribly concerned about it. Temp tables are just like any other
> permanent table (except that they are automatically dropped when they
> go out of scope) and as such SQL Server maintains statistics on the
> columns in those temp tables (SQL Server does not collect statistics
> on table variables). As such the query optimiser can often come up
> with better plans for temp tables (than it can with table variables)
> and manipulate the data in them faster. However, table variables
> implement less locking generally...so it can swing the other way too.
> The main reason I would use a table variable would be if I wanted to
> return a result set from a UDF, because you can only do that using a
> table variable. So that would be a functionality reason more so than
> a performance reason.
> Aaron Bertrand (SQL MVP) has a great website called ASPFAQ that has
> heaps of different articles about different things people ask about
> SQL Server. Here's what he says about temp tables vs table variables:
> Should I use a #temp table or a @.table variable?
> --
> mike hodgson
> blog: http://sqlnerd.blogspot.com
>
> Yu6454 wrote:
> I "heard" that since SQL Server 2000 caches the database, that temp
> tables are written to memory before disk? and thus there is no
> significant performance difference between temp table and table
> variables if you have lots of memory to begin with.
> I really never heard of this, but I really think this person was just
> trying to cover their butt on their lack of knowledge as well as
> critical mistake
>
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
> news:em9qWbAtFHA.3236@.TK2MSFTNGP09.phx.gbl...
> Yes they are written to disk, although it's often for only a short
> period of time. All database writes, including writes to tempdb which
> is in effect handled just like normal user databases, in SQL Server
> are written to memory first (making the page in memory "dirty") and
> then flushed to disk when the lazy writer process gets around to it.
> I guess, in theory, the temp table may be dropped before the lazy
> writer writes that data to disk and so it might not ever make it to
> the disk but I've never tried to analyse that scenario.
> --
> mike hodgson
> blog: http://sqlnerd.blogspot.com
>
> Yu6454 wrote:
> are temp tables written to disk? or are they cached like the database is
> cached on queries.
>
>|||I haven't read all the stuff in this thread, so please forgive me if I'm rep
eating or stating the
obvious below. Just my 2 cents:
Temp tables and table variables are handled internally very very similar. Th
ere is slightly less
logging to the transaction log for table variables (can easily be verified c
hecking number of log
records after a set of modifications). So, please don't forget that for both
temp tables and table
variables, you still have writing to transaction log, same way as for regula
r tables (except a bit
less logging, no REDO information).
As for usage, a rough rule is to use table variables for smaller tables, les
s risks for recompiles,
tiny less over head (no statistics). For bigger tables, use temp tables (sta
tistics, you can create
indexes etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:%23j2jMVEtFHA.3908@.tk2msftngp13.phx.gbl...
>I think the take home message is test it.
> Try both certainly. Sometimes temp tables will be quicker, sometimes
> table variables will be quicker. Each has their place. When you need
> to create indexes on your temporary objects - temp tables. When you
> need to return datasets from functions (and not just a single scalar) -
> table variables. Scope is definitely simpler with table variables. The
> reason there are less recompilations of procs using table variables (as
> opposed to temp tables) is because the query optimiser doesn't need to
> come up with a plan of how to access the data in the table variables
> (it's always the same) as there is only 1 way, a table scan, but with
> temp tables there can be indexes & density statistics, which make many
> more access paths possible.
> I wouldn't say the person you were talking to was wrong per se. He was
> pretty close. And, unless we're talking about HUGE amounts of temp
> table/table variable activity, I'd say debating the performance hit due
> to I/O is really splitting straws.
> I did do a few tests with this though (with 10 sample iterations for
> each average) and came up with the following (and this is on a
> completely unloaded, dedicated server - dual Xeon, 2GB RAM, local RAID1
> array):
> _
> _*avg batch duration (msec)*
> _ #rows_ _table variable_ _temp table_
> 512 15.1 14.5
> 1024 17.9 29.4
> 2048 29.2 39.9
> 4096 61.8 71.6
> 8192 127.9 145.7
> 16384 254.2 291.7
> 32768 544.6 583.0
> 65536 1117.4 1103.4
> 131072 2174.3 2224.2
> 262144 4377.1 4553.2
> 524288 8852.1 8402.0
> 1048576 17846.6 16188.6
> 2097152 36335.5 32739.4
> As you can see, for small batches the duration is almost identical,
> although temp tables are, in general slightly slower. And as the
> batches become larger the averages tended to drift apart a bit with temp
> tables being quicker. In any case, when you graph this, the 2 lines are
> almost on top of each other.
> The batches consisted of just this loop essentially (with #tmp instead
> of @.mytabvar for the temp table runs):
> declare @.i int
> set @.i = 1
> insert into @.mytabvar (blah) values ('This is a line of text')
> while (@.i < 22)
> begin
> insert into @.mytabvar (blah) select blah from @.mytabvar
> set @.i = @.i + 1
> end
> So you can see it was just doing selects & inserts. The temp table #tmp
> was defined as exactly the same as @.mytabvar, with no indexes.
> Obviously, the difference, performance-wise, between table variables &
> temp tables, comes into play much more when things get more complex -
> locking contention, many spids executing the same code simultaneously,
> indexes, complex queries, etc.
> I also noticed that using table variables always resulted in a user
> table being created in tempdb, just like with temp tables:
> declare @.mytabvar TABLE
> (
> ID int identity(1,1) not null,
> blah varchar(30) null
> )
> select * from tempdb.dbo.sysobjects where type = 'U'
> CREATE TABLE #tmp
> (
> ID int identity(1,1) not null,
> blah varchar(30) null
> )
> select * from tempdb.dbo.sysobjects where type = 'U'
> drop table #tmp
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Yu6454 wrote:
>
>|||Also have a look at
http://toponewithties.blogspot.com/...nd.h
tml
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eevY$dEtFHA.1136@.TK2MSFTNGP12.phx.gbl...
>I haven't read all the stuff in this thread, so please forgive me if I'm
>repeating or stating the obvious below. Just my 2 cents:
> Temp tables and table variables are handled internally very very similar.
> There is slightly less logging to the transaction log for table variables
> (can easily be verified checking number of log records after a set of
> modifications). So, please don't forget that for both temp tables and
> table variables, you still have writing to transaction log, same way as
> for regular tables (except a bit less logging, no REDO information).
> As for usage, a rough rule is to use table variables for smaller tables,
> less risks for recompiles, tiny less over head (no statistics). For bigger
> tables, use temp tables (statistics, you can create indexes etc).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
> news:%23j2jMVEtFHA.3908@.tk2msftngp13.phx.gbl...
>|||>
http://toponewithties.blogspot.com/...nd.h
tml
Very informative post. You should remove #8, however. Inserts in SQL
Server never generate a parallel execution plan. The source select or exec
may execute in parallel, but the insert itself--including the generation of
IDENTITY values--always executes in series. This behavior is the same
regardless of whether you're inserting into a table variable, a temporary
table or a normal table. Therefore, there is no difference as far as
parallelism goes between a table variable and a local temporary table.
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:uuqGmIFtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> Also have a look at
>
http://toponewithties.blogspot.com/...riable-and.html[color
=darkred]
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote[/color]
in
> message news:eevY$dEtFHA.1136@.TK2MSFTNGP12.phx.gbl...
similar.
variables
bigger
The
temp
are
#tmp
>|||Additionally, I would like to add that this is an insert.
But in regards to performance, an important category is when there are simul
taneous executions like from a web orders system. So I am guessing an large
number of spid's if I am correct in using that term to begin with.
Reading this article explanation of when table variables and temp tables wri
te to disk is very POOR.
http://support.microsoft.com/defaul...kb;en-us;305977
Q4: Are table variables memory-only structures that are assured better perfo
rmance as compared to temporary or permanent tables, because they are mainta
ined in a database that resides on the physical disk?
A4: A table variable is not a memory-only structure. Because a table variabl
e might hold more data than can fit in memory, it has to have a place on dis
k to store data. Table variables are created in the tempdb database similar
to temporary tables. If memory is available, both table variables and tempor
ary tables are created and processed while in memory (data cache).
It's very unclear of when a table variable writes to disk, it just says, "it
can write to disk if there is need to write to disk when there is no memory
left. HUGE DIFFERENCE.
THIS IS WHAT I THINK.
Table Variables, as microsoft said before, should be used before temp tables
if the data is not huge or very large and several other things. Table varia
bles also clean up after themselves with regard to memory whereas temp table
s it's anyone's guess especially if there are multiple spids and the same sp
roc is being called over and over again.
One has got to ask themselves why table variable were created in SQL 2000 to
begin with versus temp tables that were already available in SQL 7.Thus, my
reasoning is table variables are faster by far as SQL 2000 will use RAM fir
st before going to disk. But with Temp tables it could easily start writing
to disk when you don't want it to even though there is plenty of RAM not in
use.
I am also things SCOPE is very important with regards to memory usage. As fo
r Temp Tables, some please enlighten me on scope as that has to be managed r
ight?
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:#j2
jMVEtFHA.3908@.tk2msftngp13.phx.gbl...
I think the take home message is test it.
Try both certainly. Sometimes temp tables will be quicker, sometimes table
variables will be quicker. Each has their place. When you need to create i
ndexes on your temporary objects - temp tables. When you need to return dat
asets from functions (and not just a single scalar) - table variables. Scop
e is definitely simpler with table variables. The reason there are less rec
ompilations of procs using table variables (as opposed to temp tables) is be
cause the query optimiser doesn't need to come up with a plan of how to acce
ss the data in the table variables (it's always the same) as there is only 1
way, a table scan, but with temp tables there can be indexes & density stat
istics, which make many more access paths possible.
I wouldn't say the person you were talking to was wrong per se. He was pret
ty close. And, unless we're talking about HUGE amounts of temp table/table
variable activity, I'd say debating the performance hit due to I/O is really
splitting straws.
I did do a few tests with this though (with 10 sample iterations for each av
erage) and came up with the following (and this is on a completely unloaded,
dedicated server - dual Xeon, 2GB RAM, local RAID1 array):
avg batch duration (msec)
#rows table variable temp table
512 15.1 14.5
1024 17.9 29.4
2048 29.2 39.9
4096 61.8 71.6
8192 127.9 145.7
16384 254.2 291.7
32768 544.6 583.0
65536 1117.4 1103.4
131072 2174.3 2224.2
262144 4377.1 4553.2
524288 8852.1 8402.0
1048576 17846.6 16188.6
2097152 36335.5 32739.4
As you can see, for small batches the duration is almost identical, although
temp tables are, in general slightly slower. And as the batches become lar
ger the averages tended to drift apart a bit with temp tables being quicker.
In any case, when you graph this, the 2 lines are almost on top of each ot
her.
The batches consisted of just this loop essentially (with #tmp instead of @.m
ytabvar for the temp table runs):
declare @.i int
set @.i = 1
insert into @.mytabvar (blah) values ('This is a line of text')
while (@.i < 22)
begin
insert into @.mytabvar (blah) select blah from @.mytabvar
set @.i = @.i + 1
end
So you can see it was just doing selects & inserts. The temp table #tmp was
defined as exactly the same as @.mytabvar, with no indexes. Obviously, the
difference, performance-wise, between table variables & temp tables, comes i
nto play much more when things get more complex - locking contention, many s
pids executing the same code simultaneously, indexes, complex queries, etc.
I also noticed that using table variables always resulted in a user table be
ing created in tempdb, just like with temp tables:
declare @.mytabvar TABLE
(
ID int identity(1,1) not null,
blah varchar(30) null
)
select * from tempdb.dbo.sysobjects where type = 'U'
CREATE TABLE #tmp
(
ID int identity(1,1) not null,
blah varchar(30) null
)
select * from tempdb.dbo.sysobjects where type = 'U'
drop table #tmp
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
Ok, it sounds that temp tables in the tempdb will somehow. and in some way.
eventually some other time, make a write to disk.
Thus, the person who I heard that temp tables have the same performance with
regards to table variables is wrong as temp tables somehow write to disk. A
nd in so writing to disk, temp tables would be a performance hit at some poi
nt in time.
Thus, one should try to use temp variables just like it says from microsoft
"if you can and if you need to" before using temp tables. right? I know whe
n I did and it makes a huge difference
http://support.microsoft.com/defaul...kb;en-us;305977
[at the very bottom of the article]
" In general, you use table variables whenever possible except when there is
a significant volume of data and there is repeated use of the table. In tha
t case, you can create indexes on the temporary table to increase query perf
ormance. However, each scenario may be different. Microsoft recommends that
you test if table variables are more helpful than temporary tables for a par
ticular query or stored procedure."
NEVERTHELESS, this part below is a really bad answer from microsoft does NOT
seem true in REAL LIFE as I know from experience, whether I had enough memo
ry or not, table variables are done in memory first, and then , I THINK, ove
rflow are written to disk if necessary. As for Temp Tables, well, one is not
sure what's going on, is it memory or disk? And who is to say SQL Server is
managing this memory correctly anyway and if it's flushed in time for the n
ext stored procedure execution.
Q4: Are table variables memory-only structures that are assured better perfo
rmance as compared to temporary or permanent tables, because they are mainta
ined in a database that resides on the physical disk?
A4: A table variable is not a memory-only structure. Because a table variabl
e might hold more data than can fit in memory, it has to have a place on dis
k to store data. Table variables are created in the tempdb database similar
to temporary tables. If memory is available, both table variables and tempor
ary tables are created and processed while in memory (data cache).
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:uKi
Ol1BtFHA.1168@.TK2MSFTNGP10.phx.gbl...
In thinking this through logically in my own mind, I would say that writes t
o temp tables will always result in one or more writes to disk (although tha
t write may be deferred). This is because once the page is marked dirty in
RAM, even if the page is changed yet again at a later stage to its original
values (by dropping the temp table), the lazy writer will come along and flu
sh that page to disk (i.e. copy what's in memory for that 8K page and write
in in the appropriate place in the relevant data file). The temp table gett
ing dropped (whether automatically or manually) will not reset the dirty pag
e status; only flushing the page to disk will reset the status of that page.
Prior to SQL 7 you used to be able to force SQL Server to materialise tempdb
only in RAM, which would mean temp table writes wouldn't make it to disk, b
ut that's no longer supported in SQL 2000 (tempdb is always on disk).
If nothing else, the transactions associated with creating, populating, mani
pulating and dropping the temp table result in the creation of transaction l
og records. With tempdb those records are continually truncated but not bef
ore they have been committed & flushed to disk. So temp table writes will r
esult in transaction log I/O activity in tempdb.
These are just my thoughts and are based on my understanding of how SQL Serv
er works, not on any particular whitepaper I've read or anything like that.
Perhaps someone better qualified (like Kalen Delaney, Tibor Karaszi, Andrew
Kelly, Paul Randal, Mike Epprecht, etc.) might be able to clarify more.
Having said that, this whitepaper does a pretty good job of explaining SQL I
/O including flushing dirty pages, async I/O, lazy writes and flushing log r
ecords, but skimming it I can't see that it addresses your specific question
exactly, but is very good background info about SQL I/O:
SQL Server 2000 I/O Basics
Hope this helps.
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
So are you saying that since every database write goes to memory for a temp
table and that the write will not take place till later, there will be no pe
rformance difference on this point?
Let me ask again, "Will temp tables write to disk at some point in time even
during or after the stored procedure is over, OR is there a certain conditi
on where temp table DO NOT write to disk ever?"
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%23
2WwUHBtFHA.2076@.TK2MSFTNGP14.phx.gbl...
Temp tables vs table variables is a fairly common debate topic.
The person you're talking about is kind of right. Every database write happ
ens to memory first and so, in theory, query performance shouldn't really be
hindered by I/O to disk (as that is essentially done asynchronously by a ba
ckground spid). But this assumes you have no great memory pressure.
IMHO there's really not enough difference, performance-wise, to be terribly
concerned about it. Temp tables are just like any other permanent table (ex
cept that they are automatically dropped when they go out of scope) and as s
uch SQL Server maintains statistics on the columns in those temp tables (SQL
Server does not collect statistics on table variables). As such the query
optimiser can often come up with better plans for temp tables (than it can w
ith table variables) and manipulate the data in them faster. However, table
variables implement less locking generally...so it can swing the other way
too. The main reason I would use a table variable would be if I wanted to r
eturn a result set from a UDF, because you can only do that using a table va
riable. So that would be a functionality reason more so than a performance
reason.
Aaron Bertrand (SQL MVP) has a great website called ASPFAQ that has heaps of
different articles about different things people ask about SQL Server. Her
e's what he says about temp tables vs table variables:
Should I use a #temp table or a @.table variable?
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
I "heard" that since SQL Server 2000 caches the database, that temp tables a
re written to memory before disk? and thus there is no significant performa
nce difference between temp table and table variables if you have lots of me
mory to begin with.
I really never heard of this, but I really think this person was just trying
to cover their butt on their lack of knowledge as well as critical mistake
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:em9
qWbAtFHA.3236@.TK2MSFTNGP09.phx.gbl...
Yes they are written to disk, although it's often for only a short period of
time. All database writes, including writes to tempdb which is in effect h
andled just like normal user databases, in SQL Server are written to memory
first (making the page in memory "dirty") and then flushed to disk when the
lazy writer process gets around to it.
I guess, in theory, the temp table may be dropped before the lazy writer wri
tes that data to disk and so it might not ever make it to the disk but I've
never tried to analyse that scenario.
mike hodgson
blog: http://sqlnerd.blogspot.com
Yu6454 wrote:
are temp tables written to disk? or are they cached like the database is
cached on queries.|||Brian,
8. Parallelism is not possible when you insert into a table variable.
I got the above information from the NG Posts of SQL MVP Erland
Sommarskog.
In several posts Sommarskog confirms that "When you insert
into a table variable, SQL Server can because of implementation reasons
not use parallellism. "
http://tinyurl.com/7znmx
If you search this group, you can also find SK and Tom confirming the
same.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:eqYkfpFtFHA.4076@.TK2MSFTNGP11.phx.gbl...
> http://toponewithties.blogspot.com/...nd
.html
> Very informative post. You should remove #8, however. Inserts in SQL
> Server never generate a parallel execution plan. The source select or
> exec
> may execute in parallel, but the insert itself--including the generation
> of
> IDENTITY values--always executes in series. This behavior is the same
> regardless of whether you're inserting into a table variable, a temporary
> table or a normal table. Therefore, there is no difference as far as
> parallelism goes between a table variable and a local temporary table.
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:uuqGmIFtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> http://toponewithties.blogspot.com/...nd
.html
> in
> similar.
> variables
> bigger
> The
> temp
> are
> #tmp
>|||> Table variables also clean up after themselves with regard to memory
> whereas temp tables it's anyone's guess especially if there are multiple
> spids and the same sproc is being called over and over again.
Why on earth would you think this is "anyone's guess"? Temp tables are
cleaned up when the procedure or batch or session goes out of scope. And
each spid gets its own scope (and hence each spid that calls the same SP
gets its own copy of the temp table), so if the implication is that SQL
Server gets
are wrong.
> Thus, my reasoning is table variables are faster by far as SQL 2000 will
> use RAM first before going to disk.
I don't think you've entirely read all of the content before making your
assumptions. Maybe you should read this article as well:
http://www.aspfaq.com/2475