Out of memory running Query Analyzer and large result set

Out of memory running Query Analyzer and large result set

Post by Kevi » Sat, 18 Jan 2003 04:17:40



I have a single SELECT statement that returns a large result set (half
a million rows or so). The Client installation is MS SQL Server 2000
and the Server is MS SQL Server 7.0. The OS is Win2K.

On machine number 1, I run the query in Query Analyzer. It sits there
for about half an hour and then displays all the rows in Query
Analyzer. During the half hour, Windows virtual memory usage stays
flat.

On machine number 2, I run the query in Query Analyzer. It starts
returning rows to Query Analyzer quickly. As time goes on Windows
virtual memory steadily increases until it maxes out and the process
bombs. (Same server.)

Looks like the two machines are handling cursors in different ways.

My problem is that I don't know how or why the machines are different,
nor how to change it.

 
 
 

Out of memory running Query Analyzer and large result set

Post by chri » Sat, 18 Jan 2003 05:07:21


Ill bet the 7.0 is getting the results in text and the 2000 is getting
results in grid. I chewed on this for months before I figured it out. By
default 7.0 returns results in text and 2000 in grid. Change setting in QA
and try.


Quote:> I have a single SELECT statement that returns a large result set (half
> a million rows or so). The Client installation is MS SQL Server 2000
> and the Server is MS SQL Server 7.0. The OS is Win2K.

> On machine number 1, I run the query in Query Analyzer. It sits there
> for about half an hour and then displays all the rows in Query
> Analyzer. During the half hour, Windows virtual memory usage stays
> flat.

> On machine number 2, I run the query in Query Analyzer. It starts
> returning rows to Query Analyzer quickly. As time goes on Windows
> virtual memory steadily increases until it maxes out and the process
> bombs. (Same server.)

> Looks like the two machines are handling cursors in different ways.

> My problem is that I don't know how or why the machines are different,
> nor how to change it.


 
 
 

Out of memory running Query Analyzer and large result set

Post by Andrew J. Kell » Sat, 18 Jan 2003 05:43:42


I would agree with Chris on this.

--
Andrew J. Kelly
SQL Server MVP


> Ill bet the 7.0 is getting the results in text and the 2000 is getting
> results in grid. I chewed on this for months before I figured it out. By
> default 7.0 returns results in text and 2000 in grid. Change setting in QA
> and try.



> > I have a single SELECT statement that returns a large result set (half
> > a million rows or so). The Client installation is MS SQL Server 2000
> > and the Server is MS SQL Server 7.0. The OS is Win2K.

> > On machine number 1, I run the query in Query Analyzer. It sits there
> > for about half an hour and then displays all the rows in Query
> > Analyzer. During the half hour, Windows virtual memory usage stays
> > flat.

> > On machine number 2, I run the query in Query Analyzer. It starts
> > returning rows to Query Analyzer quickly. As time goes on Windows
> > virtual memory steadily increases until it maxes out and the process
> > bombs. (Same server.)

> > Looks like the two machines are handling cursors in different ways.

> > My problem is that I don't know how or why the machines are different,
> > nor how to change it.

 
 
 

1. way to keep the results in query analyzer results pane across multiple runs

I perpetually am running long scripts in query analyzer. I highlight 1 or 2
sql statements, hit f5 and then copy & paste the results to a word document
to keep a log. Then if the results meet a certain criteria, repeat this
process perhaps 30 times copying & pasting each time. I would like to be
able to copy & paste the results of the entire sql script 1 time, but each
time I hit f5, the results pane is cleared. This means I have to do hundreds
of copy pastes each week.

I have searched high & low for an option in query analyzer that prevents it
from auto clearing the results pane each time I hit the run button, but have
found nothing. I also could not find any alternative software package that
allows for saving the results in the results pane across multiple runs. Does
anyone know how I can find a way to keep the results in the results pane
across multiple runs either in query analyzer or some other querying tool?
The other option I have is re-writing hundreds of sql scripts. I don't want
to go that route if I don't have to.

Much Appreciated


2. Is somebody else trying to gateway info-ingres <--> comp.databases.ingres ???

3. Result Sets vs copying result sets into memory.

4. single simple table, help...

5. Query Analyzer Result Sets

6. oracle forms problem in web

7. running a query on the result set of another query

8. Accessing data from a DBGrid Control in VB5 Pro ???

9. running a query based on the result set of another query

10. Time Outs When running a query

11. Delay in Executing a Query in Query Analyzer with option Results in Text

12. Error running SP as Job but not when run from Query Analyzer

13. Not enough memory/Virtual memory error from large query