Handle Error: queries

Showing posts with label queries. Show all posts

Monday, March 19, 2012

Hardware Tuning for Queries

Hi,
I have several queries in a complicated database that join almost 10 tables
each. The query plans are good because there are a lot of index seeks and
merge/loop joins (Hash join have been eliminated by appropriate indexes). It
seems it doesn't lack any index. But as an example, a particular query that
joins 12 tables and returns 136,000 rows almost takes about 40 seconds in
best time (sometimes 100 seconds). I believe that index/query tuning cannot
make this query better, only hardware upgrade can help. I'd like to know
upgrade to which hardware components can help such queries?
Some information that might help:
CPU: 2
Memory: 1GB
Disk: 1
Cache Hit Ratio: above 99%
Concurrent Connections: 40-50
It is mentionable that there is no bottleneck while monitoring with
Performance Monitor in any hardware component like CPU, Disk, Memory. Also
this query doesn't have complex where condition, but it has a lot of CASE in
columns.
Any help would be greatly appreciated,
LeilaI cant really speak to the hard ware side of things but as a practice for
large joins like this I like to use Indexed Views and query the views
intead.
Read this article for some insight...
http://www.microsoft.com/technet/prodtechnol/sql/2005/ipsql05iv.mspx
thanks,
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Leila" <Leilas@.hotpop.com> wrote in message
news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have several queries in a complicated database that join almost 10
> tables each. The query plans are good because there are a lot of index
> seeks and merge/loop joins (Hash join have been eliminated by appropriate
> indexes). It seems it doesn't lack any index. But as an example, a
> particular query that joins 12 tables and returns 136,000 rows almost
> takes about 40 seconds in best time (sometimes 100 seconds). I believe
> that index/query tuning cannot make this query better, only hardware
> upgrade can help. I'd like to know upgrade to which hardware components
> can help such queries?
> Some information that might help:
> CPU: 2
> Memory: 1GB
> Disk: 1
> Cache Hit Ratio: above 99%
> Concurrent Connections: 40-50
> It is mentionable that there is no bottleneck while monitoring with
> Performance Monitor in any hardware component like CPU, Disk, Memory. Also
> this query doesn't have complex where condition, but it has a lot of CASE
> in columns.
> Any help would be greatly appreciated,
> Leila
>|||Indexed views might not be the answer. As I understand it, the benefits
that indexed views (and partitioned tables) provide are only available
within the Developer and Enterprise Editions of SQL Server. It is possible
to create an indexed view within other versions of SQL Server, but the query
optimizer cannot use the index.
If I am mistaken please correct me!
--
Keith Kratochvil
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:uo5zHd%235GHA.4116@.TK2MSFTNGP03.phx.gbl...
>I cant really speak to the hard ware side of things but as a practice for
>large joins like this I like to use Indexed Views and query the views
>intead.
> Read this article for some insight...
> http://www.microsoft.com/technet/prodtechnol/sql/2005/ipsql05iv.mspx
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have several queries in a complicated database that join almost 10
>> tables each. The query plans are good because there are a lot of index
>> seeks and merge/loop joins (Hash join have been eliminated by appropriate
>> indexes). It seems it doesn't lack any index. But as an example, a
>> particular query that joins 12 tables and returns 136,000 rows almost
>> takes about 40 seconds in best time (sometimes 100 seconds). I believe
>> that index/query tuning cannot make this query better, only hardware
>> upgrade can help. I'd like to know upgrade to which hardware components
>> can help such queries?
>> Some information that might help:
>> CPU: 2
>> Memory: 1GB
>> Disk: 1
>> Cache Hit Ratio: above 99%
>> Concurrent Connections: 40-50
>> It is mentionable that there is no bottleneck while monitoring with
>> Performance Monitor in any hardware component like CPU, Disk, Memory.
>> Also this query doesn't have complex where condition, but it has a lot of
>> CASE in columns.
>> Any help would be greatly appreciated,
>> Leila
>|||The engine considers the index on the view automatically for Ent/Dev
edition. For other editions, you need NoExpand hint to get the indexed view
to be considered.
e.g.
select *
from indexedview with (noexpand)
--
-oj
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:%23ZPfWY$5GHA.2464@.TK2MSFTNGP06.phx.gbl...
> Indexed views might not be the answer. As I understand it, the benefits
> that indexed views (and partitioned tables) provide are only available
> within the Developer and Enterprise Editions of SQL Server. It is
> possible to create an indexed view within other versions of SQL Server,
> but the query optimizer cannot use the index.
> If I am mistaken please correct me!
> --
> Keith Kratochvil
>
> "Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
> news:uo5zHd%235GHA.4116@.TK2MSFTNGP03.phx.gbl...
>>I cant really speak to the hard ware side of things but as a practice for
>>large joins like this I like to use Indexed Views and query the views
>>intead.
>> Read this article for some insight...
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/ipsql05iv.mspx
>> thanks,
>> --
>> /*
>> Warren Brunk - MCITP - SQL 2005, MCDBA
>> www.techintsolutions.com
>> */
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have several queries in a complicated database that join almost 10
>> tables each. The query plans are good because there are a lot of index
>> seeks and merge/loop joins (Hash join have been eliminated by
>> appropriate indexes). It seems it doesn't lack any index. But as an
>> example, a particular query that joins 12 tables and returns 136,000
>> rows almost takes about 40 seconds in best time (sometimes 100 seconds).
>> I believe that index/query tuning cannot make this query better, only
>> hardware upgrade can help. I'd like to know upgrade to which hardware
>> components can help such queries?
>> Some information that might help:
>> CPU: 2
>> Memory: 1GB
>> Disk: 1
>> Cache Hit Ratio: above 99%
>> Concurrent Connections: 40-50
>> It is mentionable that there is no bottleneck while monitoring with
>> Performance Monitor in any hardware component like CPU, Disk, Memory.
>> Also this query doesn't have complex where condition, but it has a lot
>> of CASE in columns.
>> Any help would be greatly appreciated,
>> Leila
>>
>|||Because of outer joins, Indexed View cannot be used
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:uo5zHd%235GHA.4116@.TK2MSFTNGP03.phx.gbl...
>I cant really speak to the hard ware side of things but as a practice for
>large joins like this I like to use Indexed Views and query the views
>intead.
> Read this article for some insight...
> http://www.microsoft.com/technet/prodtechnol/sql/2005/ipsql05iv.mspx
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have several queries in a complicated database that join almost 10
>> tables each. The query plans are good because there are a lot of index
>> seeks and merge/loop joins (Hash join have been eliminated by appropriate
>> indexes). It seems it doesn't lack any index. But as an example, a
>> particular query that joins 12 tables and returns 136,000 rows almost
>> takes about 40 seconds in best time (sometimes 100 seconds). I believe
>> that index/query tuning cannot make this query better, only hardware
>> upgrade can help. I'd like to know upgrade to which hardware components
>> can help such queries?
>> Some information that might help:
>> CPU: 2
>> Memory: 1GB
>> Disk: 1
>> Cache Hit Ratio: above 99%
>> Concurrent Connections: 40-50
>> It is mentionable that there is no bottleneck while monitoring with
>> Performance Monitor in any hardware component like CPU, Disk, Memory.
>> Also this query doesn't have complex where condition, but it has a lot of
>> CASE in columns.
>> Any help would be greatly appreciated,
>> Leila
>|||> Memory: 1GB
This is a bit low by today's standards. More memory can reduce disk i/o.
> Disk: 1
So you have only one disk drive on the server? No RAID? Data and log on
the same drive? I would at least add disks to ensure fault tolerance and
separate data and log files.
> It is mentionable that there is no bottleneck while monitoring with
> Performance Monitor in any hardware component like CPU, Disk, Memory.
What is the physical disk transfers/sec and bytes/sec on the disk? Note
that a disk bottleneck will not necessarily manifest itself as queue time.
You need to know the transfers/sec your particular disk can sustain in order
to determine whether or not you have a disk bottleneck.
> But as an example, a particular query that joins 12 tables and returns
> 136,000 rows almost takes about 40 seconds in best time (sometimes 100
> seconds).
What is your maximum response time requirement for this query? 136,000 rows
is a lot of data to return and client-site processing of large data volumes
like this can be more significant than query execution time. It would be
embarrassing to throw server hardware at a performance problem that turns
out to be on the client side ;-)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have several queries in a complicated database that join almost 10
> tables each. The query plans are good because there are a lot of index
> seeks and merge/loop joins (Hash join have been eliminated by appropriate
> indexes). It seems it doesn't lack any index. But as an example, a
> particular query that joins 12 tables and returns 136,000 rows almost
> takes about 40 seconds in best time (sometimes 100 seconds). I believe
> that index/query tuning cannot make this query better, only hardware
> upgrade can help. I'd like to know upgrade to which hardware components
> can help such queries?
> Some information that might help:
> CPU: 2
> Memory: 1GB
> Disk: 1
> Cache Hit Ratio: above 99%
> Concurrent Connections: 40-50
> It is mentionable that there is no bottleneck while monitoring with
> Performance Monitor in any hardware component like CPU, Disk, Memory. Also
> this query doesn't have complex where condition, but it has a lot of CASE
> in columns.
> Any help would be greatly appreciated,
> Leila
>|||Thanks indeed Dan!
Today I discovered very strange thing that almost solved the problem! We
added another 512MB memory to the server, now it has 1.5GB. I tried that
particular query again, the result was the same, about 40 seconds. I stopped
the SQL Server service and restarted it manually. I couldn't believe the
result! The query took 11 seconds at first try, and when cached it stayed on
8 seconds for several tries! I restarted the windows again, and then tried
the query after booting; again 40 seconds. Then manually stopped and
restarted SQL Server service and it gained 8 seconds again!
I monitored the memory consumed by SQL Server. Before manual restart of
service, SQL Server consumed memory very slowly, and it took 40 seconds to
fill about 120MB of RAM. After manual restart, the memory consumed by SQL
Server(up to 120MB) was growing too fast (8 seconds)!
I have no idea that why a manual restart of service must have this effect! I
am suspected that it can be a bug in Windows 2003.
However I must take this server back to production environment to test it
under load of users.
Any ideas?...
Thanks again,
Leila
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23pqMKZH6GHA.940@.TK2MSFTNGP03.phx.gbl...
>> Memory: 1GB
> This is a bit low by today's standards. More memory can reduce disk i/o.
>> Disk: 1
> So you have only one disk drive on the server? No RAID? Data and log on
> the same drive? I would at least add disks to ensure fault tolerance and
> separate data and log files.
>> It is mentionable that there is no bottleneck while monitoring with
>> Performance Monitor in any hardware component like CPU, Disk, Memory.
> What is the physical disk transfers/sec and bytes/sec on the disk? Note
> that a disk bottleneck will not necessarily manifest itself as queue time.
> You need to know the transfers/sec your particular disk can sustain in
> order to determine whether or not you have a disk bottleneck.
>> But as an example, a particular query that joins 12 tables and returns
>> 136,000 rows almost takes about 40 seconds in best time (sometimes 100
>> seconds).
> What is your maximum response time requirement for this query? 136,000
> rows is a lot of data to return and client-site processing of large data
> volumes like this can be more significant than query execution time. It
> would be embarrassing to throw server hardware at a performance problem
> that turns out to be on the client side ;-)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have several queries in a complicated database that join almost 10
>> tables each. The query plans are good because there are a lot of index
>> seeks and merge/loop joins (Hash join have been eliminated by appropriate
>> indexes). It seems it doesn't lack any index. But as an example, a
>> particular query that joins 12 tables and returns 136,000 rows almost
>> takes about 40 seconds in best time (sometimes 100 seconds). I believe
>> that index/query tuning cannot make this query better, only hardware
>> upgrade can help. I'd like to know upgrade to which hardware components
>> can help such queries?
>> Some information that might help:
>> CPU: 2
>> Memory: 1GB
>> Disk: 1
>> Cache Hit Ratio: above 99%
>> Concurrent Connections: 40-50
>> It is mentionable that there is no bottleneck while monitoring with
>> Performance Monitor in any hardware component like CPU, Disk, Memory.
>> Also this query doesn't have complex where condition, but it has a lot of
>> CASE in columns.
>> Any help would be greatly appreciated,
>> Leila
>|||You might take a look at the execution plans. I would expect these to be
the same with the same query/data but the only way to make sure is to check.
Given the same plans, resource contention is the most likely cause. I
suggest you monitor CPU and disk to see if you can spot what the contention
might be during slowness.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:egcODSL6GHA.4476@.TK2MSFTNGP04.phx.gbl...
> Thanks indeed Dan!
> Today I discovered very strange thing that almost solved the problem! We
> added another 512MB memory to the server, now it has 1.5GB. I tried that
> particular query again, the result was the same, about 40 seconds. I
> stopped the SQL Server service and restarted it manually. I couldn't
> believe the result! The query took 11 seconds at first try, and when
> cached it stayed on 8 seconds for several tries! I restarted the windows
> again, and then tried the query after booting; again 40 seconds. Then
> manually stopped and restarted SQL Server service and it gained 8 seconds
> again!
> I monitored the memory consumed by SQL Server. Before manual restart of
> service, SQL Server consumed memory very slowly, and it took 40 seconds to
> fill about 120MB of RAM. After manual restart, the memory consumed by SQL
> Server(up to 120MB) was growing too fast (8 seconds)!
> I have no idea that why a manual restart of service must have this effect!
> I am suspected that it can be a bug in Windows 2003.
> However I must take this server back to production environment to test it
> under load of users.
> Any ideas?...
> Thanks again,
> Leila
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23pqMKZH6GHA.940@.TK2MSFTNGP03.phx.gbl...
>> Memory: 1GB
>> This is a bit low by today's standards. More memory can reduce disk i/o.
>> Disk: 1
>> So you have only one disk drive on the server? No RAID? Data and log on
>> the same drive? I would at least add disks to ensure fault tolerance and
>> separate data and log files.
>> It is mentionable that there is no bottleneck while monitoring with
>> Performance Monitor in any hardware component like CPU, Disk, Memory.
>> What is the physical disk transfers/sec and bytes/sec on the disk? Note
>> that a disk bottleneck will not necessarily manifest itself as queue
>> time. You need to know the transfers/sec your particular disk can sustain
>> in order to determine whether or not you have a disk bottleneck.
>> But as an example, a particular query that joins 12 tables and returns
>> 136,000 rows almost takes about 40 seconds in best time (sometimes 100
>> seconds).
>> What is your maximum response time requirement for this query? 136,000
>> rows is a lot of data to return and client-site processing of large data
>> volumes like this can be more significant than query execution time. It
>> would be embarrassing to throw server hardware at a performance problem
>> that turns out to be on the client side ;-)
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have several queries in a complicated database that join almost 10
>> tables each. The query plans are good because there are a lot of index
>> seeks and merge/loop joins (Hash join have been eliminated by
>> appropriate indexes). It seems it doesn't lack any index. But as an
>> example, a particular query that joins 12 tables and returns 136,000
>> rows almost takes about 40 seconds in best time (sometimes 100 seconds).
>> I believe that index/query tuning cannot make this query better, only
>> hardware upgrade can help. I'd like to know upgrade to which hardware
>> components can help such queries?
>> Some information that might help:
>> CPU: 2
>> Memory: 1GB
>> Disk: 1
>> Cache Hit Ratio: above 99%
>> Concurrent Connections: 40-50
>> It is mentionable that there is no bottleneck while monitoring with
>> Performance Monitor in any hardware component like CPU, Disk, Memory.
>> Also this query doesn't have complex where condition, but it has a lot
>> of CASE in columns.
>> Any help would be greatly appreciated,
>> Leila
>>
>

Hardware Tuning for Queries

Monday, February 27, 2012

Handling very large XML result sets

I am writing a .NET based application to create large XML data files using
SQLXML classes and FOR XML EXPLICIT queries. What are some strategies I can
use to break up and process these large result sets? The overhead of issuing
multiple queries by breaking them up via WHERE clause filters isn’t the way I
want to go since my queries are very large and take significant time to
process within SQL server.
I am currently experiencing out of memory exceptions on some larger result
sets (~50-60 Mbytes total XML file size). My first attempt was using
SqlXmlCommand.ExecuteXmlReader and an XmlDocument via this snippet of code:
XmlReader xr = forXMLCommand.ExecuteXmlReader();
XmlDocument xd = new XmlDocument();
xd.Load(xr);
This throws a System.OutOfMemoryException on the call to ExecuteXmlReader
when the result set gets very large.
I also tried using SqlXmlCommand.ExecuteStream thinking I could read a
buffer of chars at a time to process these large result sets but this also
resulted in a System.OutOfMemoryException on the call to ExecuteStream:
Stream s = forXMLCommand.ExecuteStream();
StreamWriter sw3 = new StreamWriter(mResultsFileName);
using (StreamReader sr = new StreamReader(s))
{
char[] c = null;
while (sr.Peek() >= 0)
{
c = new char[10000];
intnumRead = sr.Read(c, 0, c.Length);
sw3.Write(c, 0, numRead);
}
}
I have tried running my application on two different systems one with 1G of
main memory and the other a Win2K3 server with 8G of main memory. Both
systems seem to run out of memory at the same 50-60 Mb limit) Are there any
..NET memory settings I can tweak to give my .NET application more memory?
Thanks for your suggestions and ideas,
Scott
The XmlReader is a streaming interface which should not run out of memory
via the SqlXmlCommand.ExecuteStream method.
Loading into an XmlDocument however will cache the entire document into
memory.
Can you remove the following two lines from your repro and see if you are
still having the problem:
XmlDocument xd = new XmlDocument();
xd.Load(xr);
Thanks -
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad
|||Andrew,
That was exactly my thought as well, but ExecuteStream is throwing an
OutOfMemoryException. I am NOTcalling XmlDocument .Load in the code that
uses ExecuteStream.
Here is my full method I am using:
private void ExecuteSQLXMLCommandExecuteStream()
{
try
{
SqlXmlCommandforXMLCommand = new SqlXmlCommand("Provider=SQLOLEDB;DATA
SOURCE=Gibraltar;Initial Catalog=RDCModel;User ID=sa;Password=XXXX");
forXMLCommand.CommandType = SqlXmlCommandType.Sql;
StreamReadersr1 = new StreamReader(mQueryFileName);
stringquery = sr1.ReadToEnd();
sr1.Close();
query = query.Replace("\r\n", " ");
query = query.Replace("\t", " ");
forXMLCommand.CommandText = query;
Stream s = forXMLCommand.ExecuteStream();
StreamWriter sw3 = new StreamWriter(mResultsFileName);
using (StreamReader sr = new StreamReader(s))
{
char[] c = null;
while (sr.Peek() >= 0)
{
c = new char[10000];
intnumRead = sr.Read(c, 0, c.Length);
sw3.Write(c, 0, numRead);
}
}
sw3.Close();
}
catch (SqlXmlException ex)
{
ex.ErrorStream.Position = 0;
string sqlErrorString;
sqlErrorString = new StreamReader(ex.ErrorStream).ReadToEnd();
Console.WriteLine(sqlErrorString);
RDCUtilities.WriteToLog(sqlErrorString);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.WriteLine(ex.StackTrace);
RDCUtilities.WriteToLog(ex.Message);
}
""Andrew Conrad"" wrote:

> The XmlReader is a streaming interface which should not run out of memory
> via the SqlXmlCommand.ExecuteStream method.
> Loading into an XmlDocument however will cache the entire document into
> memory.
> Can you remove the following two lines from your repro and see if you are
> still having the problem:
> XmlDocument xd = new XmlDocument();
> xd.Load(xr);
> Thanks -
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad
>
|||Try using SqlXmlCommand.ExecuteToStream() instead of ExecuteStream.
Because of some technical limitations with COM interop, ExecuteStream
caches results.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad

Handling very large XML result sets

() >= 0)
{
c = new char[10000];
int numRead = sr.Read(c, 0, c.Length);
sw3.Write(c, 0, numRead);
}
}
I have tried running my application on two different systems one with 1G of
main memory and the other a Win2K3 server with 8G of main memory. Both
systems seem to run out of memory at the same 50-60 Mb limit) Are there any
.NET memory settings I can tweak to give my .NET application more memory?
Thanks for your suggestions and ideas,
ScottThe XmlReader is a streaming interface which should not run out of memory
via the SqlXmlCommand.ExecuteStream method.
Loading into an XmlDocument however will cache the entire document into
memory.
Can you remove the following two lines from your repro and see if you are
still having the problem:
XmlDocument xd = new XmlDocument();
xd.Load(xr);
Thanks -
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad|||Andrew,
That was exactly my thought as well, but ExecuteStream is throwing an
OutOfMemoryException. I am NOTcalling XmlDocument .Load in the code that
uses ExecuteStream.
Here is my full method I am using:
private void ExecuteSQLXMLCommandExecuteStream()
{
try
{
SqlXmlCommand forXMLCommand = new SqlXmlCommand("Provider=SQLOLEDB;DATA
SOURCE=Gibraltar;Initial Catalog=RDCModel;User ID=sa;Password=XXXX");
forXMLCommand.CommandType = SqlXmlCommandType.Sql;
StreamReader sr1 = new StreamReader(mQueryFileName);
string query = sr1.ReadToEnd();
sr1.Close();
query = query.Replace("\r\n", " ");
query = query.Replace("\t", " ");
forXMLCommand.CommandText = query;
Stream s = forXMLCommand.ExecuteStream();
StreamWriter sw3 = new StreamWriter(mResultsFileName);
using (StreamReader sr = new StreamReader(s))
{
char[] c = null;
while (sr.P

() >= 0)
{
c = new char[10000];
int numRead = sr.Read(c, 0, c.Length);
sw3.Write(c, 0, numRead);
}
}
sw3.Close();
}
catch (SqlXmlException ex)
{
ex.ErrorStream.Position = 0;
string sqlErrorString;
sqlErrorString = new StreamReader(ex.ErrorStream).ReadToEnd();
Console.WriteLine(sqlErrorString);
RDCUtilities.WriteToLog(sqlErrorString);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.WriteLine(ex.StackTrace);
RDCUtilities.WriteToLog(ex.Message);
}
""Andrew Conrad"" wrote:

> The XmlReader is a streaming interface which should not run out of memory
> via the SqlXmlCommand.ExecuteStream method.
> Loading into an XmlDocument however will cache the entire document into
> memory.
> Can you remove the following two lines from your repro and see if you are
> still having the problem:
> XmlDocument xd = new XmlDocument();
> xd.Load(xr);
> Thanks -
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad
>|||Try using SqlXmlCommand.ExecuteToStream() instead of ExecuteStream.
Because of some technical limitations with COM interop, ExecuteStream
caches results.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad

Monday, March 19, 2012

Hardware Tuning for Queries

Hardware Tuning for Queries

Hardware Tuning for Queries

Monday, February 27, 2012

Handling very large XML result sets

Handling very large XML result sets

Handle Error

Blog Archive

About Me