Optimizing SQL 2000 SP4 for large amount of memory

Hi
We will within the next week move our SQL 2000 server SP4 to a new high
performance server (2 x ghz XEON, 4 GB RAM)
The database which is to be running on the server has a physical size of 50
mb on disk.
Is there a way to optimize the SQL 2000 to keep as much as possible
in-memory so searching will as quick as possible? The read / write rate is
approx 1000/1 (we read 1000 x as often as writing)
Is the some memory settings we can tweak or are we better of letting sql
server 2000 handle it?
Thanks in regards
Anders JacobsenJust checking 50Mb not 50 Gb. If so then there's not much you can do, with
4Gb of RAM it will all reside in memory.
--
Simon Sabin
SQL Server MVP
http://sqljunkies.com/weblog/simons
"Anders" <anderskj1@.yahoo.dk> wrote in message
news:%23j7EngYXGHA.4432@.TK2MSFTNGP04.phx.gbl...
> Hi
> We will within the next week move our SQL 2000 server SP4 to a new high
> performance server (2 x ghz XEON, 4 GB RAM)
> The database which is to be running on the server has a physical size of
> 50 mb on disk.
> Is there a way to optimize the SQL 2000 to keep as much as possible
> in-memory so searching will as quick as possible? The read / write rate is
> approx 1000/1 (we read 1000 x as often as writing)
> Is the some memory settings we can tweak or are we better of letting sql
> server 2000 handle it?
> Thanks in regards
> Anders Jacobsen
>|||"Anders" <anderskj1@.yahoo.dk> wrote in message
news:%23j7EngYXGHA.4432@.TK2MSFTNGP04.phx.gbl...
> Hi
> We will within the next week move our SQL 2000 server SP4 to a new high
> performance server (2 x ghz XEON, 4 GB RAM)
> The database which is to be running on the server has a physical size of
50
> mb on disk.
> Is there a way to optimize the SQL 2000 to keep as much as possible
> in-memory so searching will as quick as possible? The read / write rate is
> approx 1000/1 (we read 1000 x as often as writing)
> Is the some memory settings we can tweak or are we better of letting sql
> server 2000 handle it?
Generally you're better off letting SQL Server handle it. It will grab as
much RAM as it can (2GB with Standard, more with Enterprise with the proper
switches) and use it to cache.
Check perfmon and look at the cache hit ratio for one thing to see how
you're doing.
If it's truly 50MB, that'll fit into RAM absolutely w/o problems.
> Thanks in regards
> Anders Jacobsen
>|||> Generally you're better off letting SQL Server handle it. It will grab as
> much RAM as it can (2GB with Standard, more with Enterprise with the
> proper
> switches) and use it to cache.
> Check perfmon and look at the cache hit ratio for one thing to see how
> you're doing.
> If it's truly 50MB, that'll fit into RAM absolutely w/o problems.
Sounds great.
Thanks

Optimizing SQL 2000 SP4 for large amount of memory

Hi
We will within the next week move our SQL 2000 server SP4 to a new high
performance server (2 x ghz XEON, 4 GB RAM)
The database which is to be running on the server has a physical size of 50
mb on disk.
Is there a way to optimize the SQL 2000 to keep as much as possible
in-memory so searching will as quick as possible? The read / write rate is
approx 1000/1 (we read 1000 x as often as writing)
Is the some memory settings we can tweak or are we better of letting sql
server 2000 handle it?
Thanks in regards
Anders JacobsenJust checking 50Mb not 50 Gb. If so then there's not much you can do, with
4Gb of RAM it will all reside in memory.
Simon Sabin
SQL Server MVP
http://sqljunkies.com/weblog/simons
"Anders" <anderskj1@.yahoo.dk> wrote in message
news:%23j7EngYXGHA.4432@.TK2MSFTNGP04.phx.gbl...
> Hi
> We will within the next week move our SQL 2000 server SP4 to a new high
> performance server (2 x ghz XEON, 4 GB RAM)
> The database which is to be running on the server has a physical size of
> 50 mb on disk.
> Is there a way to optimize the SQL 2000 to keep as much as possible
> in-memory so searching will as quick as possible? The read / write rate is
> approx 1000/1 (we read 1000 x as often as writing)
> Is the some memory settings we can tweak or are we better of letting sql
> server 2000 handle it?
> Thanks in regards
> Anders Jacobsen
>|||"Anders" <anderskj1@.yahoo.dk> wrote in message
news:%23j7EngYXGHA.4432@.TK2MSFTNGP04.phx.gbl...
> Hi
> We will within the next week move our SQL 2000 server SP4 to a new high
> performance server (2 x ghz XEON, 4 GB RAM)
> The database which is to be running on the server has a physical size of
50
> mb on disk.
> Is there a way to optimize the SQL 2000 to keep as much as possible
> in-memory so searching will as quick as possible? The read / write rate is
> approx 1000/1 (we read 1000 x as often as writing)
> Is the some memory settings we can tweak or are we better of letting sql
> server 2000 handle it?
Generally you're better off letting SQL Server handle it. It will grab as
much RAM as it can (2GB with Standard, more with Enterprise with the proper
switches) and use it to cache.
Check perfmon and look at the cache hit ratio for one thing to see how
you're doing.
If it's truly 50MB, that'll fit into RAM absolutely w/o problems.

> Thanks in regards
> Anders Jacobsen
>|||> Generally you're better off letting SQL Server handle it. It will grab as
> much RAM as it can (2GB with Standard, more with Enterprise with the
> proper
> switches) and use it to cache.
> Check perfmon and look at the cache hit ratio for one thing to see how
> you're doing.
> If it's truly 50MB, that'll fit into RAM absolutely w/o problems.
Sounds great.
Thanks

optimizer & load

So, we know that the optimizer may choose certain join types under different
memory loads or pressures, but will the optimizer vary the degree of
paralellism based on the number of already active threads or the system cpu
load?
--
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----Not at run time... At most the optimizer will output 2 plans, one serial and
one parallell... If for any reason there are not enough resources at run
time to do the parallel plan, the serial plan is run... SQL does not
currently back off of the number of processors, etc...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> So, we know that the optimizer may choose certain join types under
different
> memory loads or pressures, but will the optimizer vary the degree of
> paralellism based on the number of already active threads or the system
cpu
> load?
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
>|||No, is based only upon cost of the query, not current activity not current
load.
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> So, we know that the optimizer may choose certain join types under
different
> memory loads or pressures, but will the optimizer vary the degree of
> paralellism based on the number of already active threads or the system
cpu
> load?
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
>|||> No, is based only upon cost of the query, not current activity not
current
> load.
From the SQL Server 2000 Books Online:
<Excerpt href="http://links.10026.com/?link=architec.chm::/8_ar_sa_163x.htm">
SQL Server monitors CPU usage and adjusts the degree of parallelism at
the query startup time. Lower degrees of parallelism are chosen if CPU
usage is high.
</Excerpt>
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
news:urGQxfthDHA.2212@.tk2msftngp13.phx.gbl...
> No, is based only upon cost of the query, not current activity not
current
> load.
> "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> > So, we know that the optimizer may choose certain join types under
> different
> > memory loads or pressures, but will the optimizer vary the degree of
> > paralellism based on the number of already active threads or the
system
> cpu
> > load?
> >
> > --
> > Kevin Connell, MCDBA
> > ----
> > The views expressed here are my own
> > and not of my employer.
> > ----
> >
> >
>|||That's what I was looking for. Maybe I'll send a request for a "knob" on
this one to sqlwish :)
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:eLpn4XvhDHA.944@.TK2MSFTNGP11.phx.gbl...
> > No, is based only upon cost of the query, not current activity not
> current
> > load.
> From the SQL Server 2000 Books Online:
> <Excerpt href="http://links.10026.com/?link=architec.chm::/8_ar_sa_163x.htm">
> SQL Server monitors CPU usage and adjusts the degree of parallelism at
> the query startup time. Lower degrees of parallelism are chosen if CPU
> usage is high.
> </Excerpt>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --
> "Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
> news:urGQxfthDHA.2212@.tk2msftngp13.phx.gbl...
> > No, is based only upon cost of the query, not current activity not
> current
> > load.
> >
> > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> > > So, we know that the optimizer may choose certain join types under
> > different
> > > memory loads or pressures, but will the optimizer vary the degree of
> > > paralellism based on the number of already active threads or the
> system
> > cpu
> > > load?
> > >
> > > --
> > > Kevin Connell, MCDBA
> > > ----
> > > The views expressed here are my own
> > > and not of my employer.
> > > ----
> > >
> > >
> >
> >
>|||Dan,
That is interesting. Do you know if any number of CPU's is being
considered? Or will SQL-Server scale back to a fixed number of CPU's
(for example 2, 4, 8) or will it simply scale back to 1 (and use the
serial query plan)?
I assume the degree of parallism is determined before the query plan for
this number of CPU's is compiled (or fetched from cache).
Gert-Jan
Dan Guzman wrote:
> > No, is based only upon cost of the query, not current activity not
> current
> > load.
> From the SQL Server 2000 Books Online:
> <Excerpt href="http://links.10026.com/?link=architec.chm::/8_ar_sa_163x.htm">
> SQL Server monitors CPU usage and adjusts the degree of parallelism at
> the query startup time. Lower degrees of parallelism are chosen if CPU
> usage is high.
> </Excerpt>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --
> "Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
> news:urGQxfthDHA.2212@.tk2msftngp13.phx.gbl...
> > No, is based only upon cost of the query, not current activity not
> current
> > load.
> >
> > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> > > So, we know that the optimizer may choose certain join types under
> > different
> > > memory loads or pressures, but will the optimizer vary the degree of
> > > paralellism based on the number of already active threads or the
> system
> > cpu
> > > load?
> > >
> > > --
> > > Kevin Connell, MCDBA
> > > ----
> > > The views expressed here are my own
> > > and not of my employer.
> > > ----
> > >
> > >
> >
> >|||I'm planning on doing some testing. Should be pretty straightforward to
figure out.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3F79D788.139C5712@.toomuchspamalready.nl...
> Dan,
> That is interesting. Do you know if any number of CPU's is being
> considered? Or will SQL-Server scale back to a fixed number of CPU's
> (for example 2, 4, 8) or will it simply scale back to 1 (and use the
> serial query plan)?
> I assume the degree of parallism is determined before the query plan for
> this number of CPU's is compiled (or fetched from cache).
> Gert-Jan
>
> Dan Guzman wrote:
> >
> > > No, is based only upon cost of the query, not current activity not
> > current
> > > load.
> >
> > From the SQL Server 2000 Books Online:
> >
> > <Excerpt href="http://links.10026.com/?link=architec.chm::/8_ar_sa_163x.htm">
> >
> > SQL Server monitors CPU usage and adjusts the degree of parallelism at
> > the query startup time. Lower degrees of parallelism are chosen if CPU
> > usage is high.
> >
> > </Excerpt>
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > SQL FAQ links (courtesy Neil Pike):
> >
> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > --
> >
> > "Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
> > news:urGQxfthDHA.2212@.tk2msftngp13.phx.gbl...
> > > No, is based only upon cost of the query, not current activity not
> > current
> > > load.
> > >
> > > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > > news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> > > > So, we know that the optimizer may choose certain join types under
> > > different
> > > > memory loads or pressures, but will the optimizer vary the degree of
> > > > paralellism based on the number of already active threads or the
> > system
> > > cpu
> > > > load?
> > > >
> > > > --
> > > > Kevin Connell, MCDBA
> > > > ----
> > > > The views expressed here are my own
> > > > and not of my employer.
> > > > ----
> > > >
> > > >
> > >
> > >|||> That is interesting. Do you know if any number of CPU's is being
> considered? Or will SQL-Server scale back to a fixed number of CPU's
> (for example 2, 4, 8) or will it simply scale back to 1 (and use the
> serial query plan)?
> I assume the degree of parallism is determined before the query plan
for
> this number of CPU's is compiled (or fetched from cache).
My interpretation of the following (from the same topic) is that the
number of CPUs is variable and the optimal number of threads is
reevaluated for each execution:
<Excerpt>
SQL Server reexamines the optimal number of thread decisions each time a
query execution plan is retrieved from the procedure cache. For example,
one execution of a query can result in use of a serial plan, a later
execution of the same query can result in a parallel plan using three
threads, and a third execution can result in a parallel plan using four
threads.
</Excerpt>
I haven't played around with this, though. Hopefully, Kevin will post
the results of his tests.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3F79D788.139C5712@.toomuchspamalready.nl...
> Dan,
> That is interesting. Do you know if any number of CPU's is being
> considered? Or will SQL-Server scale back to a fixed number of CPU's
> (for example 2, 4, 8) or will it simply scale back to 1 (and use the
> serial query plan)?
> I assume the degree of parallism is determined before the query plan
for
> this number of CPU's is compiled (or fetched from cache).
> Gert-Jan
>
> Dan Guzman wrote:
> >
> > > No, is based only upon cost of the query, not current activity not
> > current
> > > load.
> >
> > From the SQL Server 2000 Books Online:
> >
> > <Excerpt href="http://links.10026.com/?link=architec.chm::/8_ar_sa_163x.htm">
> >
> > SQL Server monitors CPU usage and adjusts the degree of parallelism
at
> > the query startup time. Lower degrees of parallelism are chosen if
CPU
> > usage is high.
> >
> > </Excerpt>
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > SQL FAQ links (courtesy Neil Pike):
> >
> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > --
> >
> > "Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
> > news:urGQxfthDHA.2212@.tk2msftngp13.phx.gbl...
> > > No, is based only upon cost of the query, not current activity not
> > current
> > > load.
> > >
> > > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > > news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> > > > So, we know that the optimizer may choose certain join types
under
> > > different
> > > > memory loads or pressures, but will the optimizer vary the
degree of
> > > > paralellism based on the number of already active threads or the
> > system
> > > cpu
> > > > load?
> > > >
> > > > --
> > > > Kevin Connell, MCDBA
> > > > ----
> > > > The views expressed here are my own
> > > > and not of my employer.
> > > > ----
> > > >
> > > >
> > >
> > >|||> I'm planning on doing some testing. Should be pretty straightforward
to
> figure out.
Please share your results with the group.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:u7Ux3%238hDHA.944@.TK2MSFTNGP11.phx.gbl...
> I'm planning on doing some testing. Should be pretty straightforward
to
> figure out.
>
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:3F79D788.139C5712@.toomuchspamalready.nl...
> > Dan,
> >
> > That is interesting. Do you know if any number of CPU's is being
> > considered? Or will SQL-Server scale back to a fixed number of CPU's
> > (for example 2, 4, 8) or will it simply scale back to 1 (and use the
> > serial query plan)?
> >
> > I assume the degree of parallism is determined before the query plan
for
> > this number of CPU's is compiled (or fetched from cache).
> >
> > Gert-Jan
> >
> >
> > Dan Guzman wrote:
> > >
> > > > No, is based only upon cost of the query, not current activity
not
> > > current
> > > > load.
> > >
> > > From the SQL Server 2000 Books Online:
> > >
> > > <Excerpt href="http://links.10026.com/?link=architec.chm::/8_ar_sa_163x.htm">
> > >
> > > SQL Server monitors CPU usage and adjusts the degree of
parallelism at
> > > the query startup time. Lower degrees of parallelism are chosen if
CPU
> > > usage is high.
> > >
> > > </Excerpt>
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > --
> > > SQL FAQ links (courtesy Neil Pike):
> > >
> > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > http://www.sqlserverfaq.com
> > > http://www.mssqlserver.com/faq
> > > --
> > >
> > > "Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
> > > news:urGQxfthDHA.2212@.tk2msftngp13.phx.gbl...
> > > > No, is based only upon cost of the query, not current activity
not
> > > current
> > > > load.
> > > >
> > > > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > > > news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> > > > > So, we know that the optimizer may choose certain join types
under
> > > > different
> > > > > memory loads or pressures, but will the optimizer vary the
degree of
> > > > > paralellism based on the number of already active threads or
the
> > > system
> > > > cpu
> > > > > load?
> > > > >
> > > > > --
> > > > > Kevin Connell, MCDBA
> > > > > ----
> > > > > The views expressed here are my own
> > > > > and not of my employer.
> > > > > ----
> > > > >
> > > > >
> > > >
> > > >
>|||Either my test is no good or the documentation is incorrect. DOP is not
affected by system CPU load.
I've been running a machine at 100% CPU load for hours now and large
paralell queries continue to come in at DOP=4
Environment:
4 CPU ML-570G2 (4x2.0Ghz XEON P4)
Win2k Std Sp3
SQL 2K Enterprise Sp3+ms03-031
4GB RAM
sp_config:maxdop = 0
Measuring DOP with profiler event class "Degree of Paralellism" and
verifying execution plan is paralell via QA.
Would love to know if someone else can verify these findings.
Kevin Connell, MCDBA
----
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23zdG5CIiDHA.2192@.TK2MSFTNGP09.phx.gbl...
> > I'm planning on doing some testing. Should be pretty straightforward
> to
> > figure out.
> Please share your results with the group.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
> "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> news:u7Ux3%238hDHA.944@.TK2MSFTNGP11.phx.gbl...
> > I'm planning on doing some testing. Should be pretty straightforward
> to
> > figure out.
> >
> >
> > "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> > news:3F79D788.139C5712@.toomuchspamalready.nl...
> > > Dan,
> > >
> > > That is interesting. Do you know if any number of CPU's is being
> > > considered? Or will SQL-Server scale back to a fixed number of CPU's
> > > (for example 2, 4, 8) or will it simply scale back to 1 (and use the
> > > serial query plan)?
> > >
> > > I assume the degree of parallism is determined before the query plan
> for
> > > this number of CPU's is compiled (or fetched from cache).
> > >
> > > Gert-Jan
> > >
> > >
> > > Dan Guzman wrote:
> > > >
> > > > > No, is based only upon cost of the query, not current activity
> not
> > > > current
> > > > > load.
> > > >
> > > > From the SQL Server 2000 Books Online:
> > > >
> > > > <Excerpt href="http://links.10026.com/?link=architec.chm::/8_ar_sa_163x.htm">
> > > >
> > > > SQL Server monitors CPU usage and adjusts the degree of
> parallelism at
> > > > the query startup time. Lower degrees of parallelism are chosen if
> CPU
> > > > usage is high.
> > > >
> > > > </Excerpt>
> > > >
> > > > --
> > > > Hope this helps.
> > > >
> > > > Dan Guzman
> > > > SQL Server MVP
> > > >
> > > > --
> > > > SQL FAQ links (courtesy Neil Pike):
> > > >
> > > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > > http://www.sqlserverfaq.com
> > > > http://www.mssqlserver.com/faq
> > > > --
> > > >
> > > > "Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
> > > > news:urGQxfthDHA.2212@.tk2msftngp13.phx.gbl...
> > > > > No, is based only upon cost of the query, not current activity
> not
> > > > current
> > > > > load.
> > > > >
> > > > > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > > > > news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> > > > > > So, we know that the optimizer may choose certain join types
> under
> > > > > different
> > > > > > memory loads or pressures, but will the optimizer vary the
> degree of
> > > > > > paralellism based on the number of already active threads or
> the
> > > > system
> > > > > cpu
> > > > > > load?
> > > > > >
> > > > > > --
> > > > > > Kevin Connell, MCDBA
> > > > > > ----
> > > > > > The views expressed here are my own
> > > > > > and not of my employer.
> > > > > > ----
> > > > > >
> > > > > >
> > > > >
> > > > >
> >
> >
>|||I'll see what I can find out.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:O7Z4WPViDHA.2592@.tk2msftngp13.phx.gbl...
> Either my test is no good or the documentation is incorrect. DOP is
not
> affected by system CPU load.
> I've been running a machine at 100% CPU load for hours now and large
> paralell queries continue to come in at DOP=4
> Environment:
> 4 CPU ML-570G2 (4x2.0Ghz XEON P4)
> Win2k Std Sp3
> SQL 2K Enterprise Sp3+ms03-031
> 4GB RAM
> sp_config:maxdop = 0
> Measuring DOP with profiler event class "Degree of Paralellism" and
> verifying execution plan is paralell via QA.
> Would love to know if someone else can verify these findings.
> Kevin Connell, MCDBA
> ----
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:%23zdG5CIiDHA.2192@.TK2MSFTNGP09.phx.gbl...
> > > I'm planning on doing some testing. Should be pretty
straightforward
> > to
> > > figure out.
> >
> > Please share your results with the group.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> >
> > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > news:u7Ux3%238hDHA.944@.TK2MSFTNGP11.phx.gbl...
> > > I'm planning on doing some testing. Should be pretty
straightforward
> > to
> > > figure out.
> > >
> > >
> > > "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> > > news:3F79D788.139C5712@.toomuchspamalready.nl...
> > > > Dan,
> > > >
> > > > That is interesting. Do you know if any number of CPU's is being
> > > > considered? Or will SQL-Server scale back to a fixed number of
CPU's
> > > > (for example 2, 4, 8) or will it simply scale back to 1 (and use
the
> > > > serial query plan)?
> > > >
> > > > I assume the degree of parallism is determined before the query
plan
> > for
> > > > this number of CPU's is compiled (or fetched from cache).
> > > >
> > > > Gert-Jan
> > > >
> > > >
> > > > Dan Guzman wrote:
> > > > >
> > > > > > No, is based only upon cost of the query, not current
activity
> > not
> > > > > current
> > > > > > load.
> > > > >
> > > > > From the SQL Server 2000 Books Online:
> > > > >
> > > > > <Excerpt href="http://links.10026.com/?link=architec.chm::/8_ar_sa_163x.htm">
> > > > >
> > > > > SQL Server monitors CPU usage and adjusts the degree of
> > parallelism at
> > > > > the query startup time. Lower degrees of parallelism are
chosen if
> > CPU
> > > > > usage is high.
> > > > >
> > > > > </Excerpt>
> > > > >
> > > > > --
> > > > > Hope this helps.
> > > > >
> > > > > Dan Guzman
> > > > > SQL Server MVP
> > > > >
> > > > > --
> > > > > SQL FAQ links (courtesy Neil Pike):
> > > > >
> > > > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > > > http://www.sqlserverfaq.com
> > > > > http://www.mssqlserver.com/faq
> > > > > --
> > > > >
> > > > > "Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
> > > > > news:urGQxfthDHA.2212@.tk2msftngp13.phx.gbl...
> > > > > > No, is based only upon cost of the query, not current
activity
> > not
> > > > > current
> > > > > > load.
> > > > > >
> > > > > > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > > > > > news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> > > > > > > So, we know that the optimizer may choose certain join
types
> > under
> > > > > > different
> > > > > > > memory loads or pressures, but will the optimizer vary the
> > degree of
> > > > > > > paralellism based on the number of already active threads
or
> > the
> > > > > system
> > > > > > cpu
> > > > > > > load?
> > > > > > >
> > > > > > > --
> > > > > > > Kevin Connell, MCDBA
> > > > > > > ----
> > > > > > > The views expressed here are my own
> > > > > > > and not of my employer.
> > > > > > > ----
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > >
> > >
> >
> >
>

optimized name-address-zipcode-region design

Whether thin-client with round-trip cycles to the db or a rich-client
loading look-up data into memory on application startup, what is a solid
optimized database design for customer location search functionality. USPS
zip data has about 75K records.
Any articles on this? Thanks. -Greg
**** ** Application supplied data *******
1. First Name (PrimaryContactID)
2. Last Name
3. Client Name
4. Address (AddressID)
****** Vendor supplied data
zipinfo.com,/www.zip-codes.com,www.buyzips.com,etc. *******
5. City (CityID)
6. Region (State,Province, etc) (RegionID)
7. Postal Code. (PostalCodeID)
8. Area Code (AreaCodeID)
an optimized search that would return all customers (within a second) for
any of the following:
1. a given zipcode/postalcode
2. a given State/Region,
3. a given City
4. a given Area Code
5. a given match on name or substring of address."hazz" <hazz@.sonic_net> wrote in
news:ejBkMi7bGHA.3908@.TK2MSFTNGP02.phx.gbl:

> Whether thin-client with round-trip cycles to the db or a rich-client
> loading look-up data into memory on application startup, what is a
> solid optimized database design for customer location search
> functionality. USPS zip data has about 75K records.
> Any articles on this? Thanks. -Greg
> **** ** Application supplied data *******
> 1. First Name (PrimaryContactID)
> 2. Last Name
> 3. Client Name
> 4. Address (AddressID)
> ****** Vendor supplied data
> zipinfo.com,/www.zip-codes.com,www.buyzips.com,etc. *******
> 5. City (CityID)
> 6. Region (State,Province, etc) (RegionID)
> 7. Postal Code. (PostalCodeID)
> 8. Area Code (AreaCodeID)
> an optimized search that would return all customers (within a second)
> for any of the following:
> 1. a given zipcode/postalcode
> 2. a given State/Region,
> 3. a given City
> 4. a given Area Code
> 5. a given match on name or substring of address.
>
>
Here is a link to an article I read this w on ASP FAQ
How do I create a store locator feature?
http://www.aspfaq.com/show.asp?id=2527
- Jose|||Thank you for that link Jose. That is not exactly what I was after but that
is interesting. The vendor db's do have latitude and longitude so this is a
good example of how that info is useful. -Greg
"Jose Hernandez" <acmp_dev@.yahoo.com> wrote in message
news:Xns97B98A2952D76acmpdevyahoocom@.207
.46.248.16...
> "hazz" <hazz@.sonic_net> wrote in
> news:ejBkMi7bGHA.3908@.TK2MSFTNGP02.phx.gbl:
>
> Here is a link to an article I read this w on ASP FAQ
> How do I create a store locator feature?
> http://www.aspfaq.com/show.asp?id=2527
>
> - Jose|||You can also get the postal code database from several vendors such as
http://www.zipcodeworld.com.

Optimal Drive Performance

I am getting ready to setup our first SQL server in house. I have been
looking into hardware requirements for drive performance and memory.
The specs that were sent to me by a vendor sugested that I use 768MB of
memory in the SQL server. I had another vendor laugh and say he would
not install less than 2GB but recommended 4GB. Where is a good place to
be as far as memory was is concearned.
For Drives. One vendor recommended installing
2 x 18GB RAID 1 for OS and SQL logging.
4 x 18GB RAID 0+1 for Data
The configuration they gave only allows for one RAID controller to
access the cage. Because of this I don't see a benifit and would all six
drives in a RAID 5 be better performance?
Another Vendor suggested:
2 x 36GB RAID 1 for OS
2 x 36GB RAID 1 for Data
2 x 36GB RAID 1 for Logging
3 x RAID controller channels for performance
They suggested that this gives the best performance for logging because
logging takes the most IO. If that is the case wouldn't RAID 5 or RAID 0
be better performance for writing. RAID 0 wouldn't give me redundancy
but would give very fast write performance.
Since the first Vendor recommended a Prolient ML350, It is not possible
for me to do the second suggestion since the drive cage can not be
segregated for the 3 channel RAID adapter.
I have a choice to either go with the first suggestion, return the
server and upgrade to a DL380 to allow for drive segregation or my
hybrid of a configuration:
Add a 2 drive Drive Cage in the open 5.25 slots.
Run the Data drive in a RAID 0 + 1 from the primary DRIVE Cage.
Run the OS + Logging in the new Drive Cage using RAID 1.
Any comments or suggestions. This is my first SQL implementation.
Thank You,
John JakusMemory is too cheap these days to not have enough. How large do you expect
your DB to get and of that how much of the data would be read or written to
each day? How many transactions per second do you expect to do? Will you
read large amounts of data at a time or small amounts?
Andrew J. Kelly
SQL Server MVP
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:eTpadQ56DHA.3704@.tk2msftngp13.phx.gbl...

quote:

---

> I am getting ready to setup our first SQL server in house. I have been
> looking into hardware requirements for drive performance and memory.
> The specs that were sent to me by a vendor sugested that I use 768MB of
> memory in the SQL server. I had another vendor laugh and say he would
> not install less than 2GB but recommended 4GB. Where is a good place to
> be as far as memory was is concearned.
> For Drives. One vendor recommended installing
> 2 x 18GB RAID 1 for OS and SQL logging.
> 4 x 18GB RAID 0+1 for Data
> The configuration they gave only allows for one RAID controller to
> access the cage. Because of this I don't see a benifit and would all six
> drives in a RAID 5 be better performance?
> Another Vendor suggested:
> 2 x 36GB RAID 1 for OS
> 2 x 36GB RAID 1 for Data
> 2 x 36GB RAID 1 for Logging
> 3 x RAID controller channels for performance
> They suggested that this gives the best performance for logging because
> logging takes the most IO. If that is the case wouldn't RAID 5 or RAID 0
> be better performance for writing. RAID 0 wouldn't give me redundancy
> but would give very fast write performance.
> Since the first Vendor recommended a Prolient ML350, It is not possible
> for me to do the second suggestion since the drive cage can not be
> segregated for the 3 channel RAID adapter.
> I have a choice to either go with the first suggestion, return the
> server and upgrade to a DL380 to allow for drive segregation or my
> hybrid of a configuration:
> Add a 2 drive Drive Cage in the open 5.25 slots.
> Run the Data drive in a RAID 0 + 1 from the primary DRIVE Cage.
> Run the OS + Logging in the new Drive Cage using RAID 1.
> Any comments or suggestions. This is my first SQL implementation.
> Thank You,
> John Jakus

---

|||Andrew J. Kelly wrote:

quote:

---

> Memory is too cheap these days to not have enough. How large do you expec
t
> your DB to get and of that how much of the data would be read or written t
o
> each day? How many transactions per second do you expect to do? Will you
> read large amounts of data at a time or small amounts?
>

---

I have no idea. This is for an implementation of Axapta. They never gave
me estimates on how many transactions will be performed. I just know it
will slowly ramp up. I know it's better to over build a system like this
because it's easier then upgrading later. I just want to know which
configuration will perform better with an SQL server. Since I am new to
SQL Server.
Sorry for being so vague but this is all I have right now and they are
in a hurry to implement. I just want to make it the most robust that I can.
Thanks,
John Jakus|||John
Take a look at this link you'll find lots of useful info and tips.
http://www.sql-server-performance.com
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:ubcuJq66DHA.2404@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Andrew J. Kelly wrote:
expect[QUOTE]
to[QUOTE]
you[QUOTE]
> I have no idea. This is for an implementation of Axapta. They never gave
> me estimates on how many transactions will be performed. I just know it
> will slowly ramp up. I know it's better to over build a system like this
> because it's easier then upgrading later. I just want to know which
> configuration will perform better with an SQL server. Since I am new to
> SQL Server.
> Sorry for being so vague but this is all I have right now and they are
> in a hurry to implement. I just want to make it the most robust that I

---

can.

quote:

---

> Thanks,
> John Jakus

---

|||Well it's imposable to tell if any of those configurations will ultimately
suite your needs without that kind of information. So with this in mind I
would opt for the first configuration:
2 x 18GB RAID 1 for OS and SQL logging.
4 x 18GB RAID 0+1 for Data
This will separate the log files from he data which is important under heavy
write situations. A RAID 0+1 is good, the only thing is it only has 4
disks. This makes for only 36GB of usable space and as always with database
the more disks the better, not the size. Hope that is going to be enough.
If not and you are stuck with that drive configuration maybe you can use
36GB drives instead of 18GB for the Raid 0+1. since they didn't provide
specs it is most likely not too intensive of an application and this should
be fine. Definitely go with more memory than 768 though. Again how much
depends on the size and use of the db but 2GB should do it.
Andrew J. Kelly
SQL Server MVP
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:ubcuJq66DHA.2404@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Andrew J. Kelly wrote:
expect[QUOTE]
to[QUOTE]
you[QUOTE]
> I have no idea. This is for an implementation of Axapta. They never gave
> me estimates on how many transactions will be performed. I just know it
> will slowly ramp up. I know it's better to over build a system like this
> because it's easier then upgrading later. I just want to know which
> configuration will perform better with an SQL server. Since I am new to
> SQL Server.
> Sorry for being so vague but this is all I have right now and they are
> in a hurry to implement. I just want to make it the most robust that I

---

can.

quote:

---

> Thanks,
> John Jakus

---

|||To try and cover your issues separately:
Memory - as suggested, memory is relatively inexpensive these days and datab
ase servers are generally pretty memory and disk intensive. It really depend
s on the usage profile and DB size but 4GB is not really *that* expensive.
Disk config: Disk config is very important as it can make a major difference
to the performance of the database.
Here are some general recommendations but again this depends on the app and
how it will be used.
RAID 5: Good fault tolerance, good read performance, BAD write performance (
parity has to be written every time the disk is written to). Use this if the
DB is read intensive with few writes. RAID 5 offers good fault tolerance at
a relatively low cost.
RAID 1: Good fault tolerance, and good performance for sequential writes (su
ch as transaction logs). Expensive because you only have effective use of ha
lf the disk.
RAID 0: RAID 0 stripes data across multiple disks. This offers excellent per
formance, but NO fault tolerance.
RAID 1+0 and RAID 0+1: These two must not be confused - people use the terms
interchangeably but they are very different.
RAID 1+0 is the striping of data across multiple RAID 1 mirrors. For example
if you have 8 disks, it would stripe data across 4 mirrors. This offers exc
ellent performance, excellent fault tolerance but a not-so-excellent bank ba
lance!
RAID 0+1 is the mirroring of two stripe sets. In our scenario of 8 disks, yo
u would have 2 striped sets (RAID 0) of 4 disks each, that are in turn mirro
red. This also offers good performance and fault tolerance (as RAID 1+0) but
this will be degraded in t
he event of disk failures (much more than RAID 1+0).
So if faced with the choice between RAID 1+0 and RAID 0+1 I would always cho
ose RAID 1+0.
An example of a high-end disk spec would be:
OS: 2 x 18.2GB RAID1
Logs: 2 x 36.4GB RAID1 - or 4 x 36.4 RAID 1+0
Data: 8 x 36.4GB RAID1+0 (this could be any amount of disks in multiples of
2, the total number constrained by the storage device)
This may or may not be an overkill depending on the actual app.
In terms of controllers, separate controllers sounds like a good idea - agai
n its down to the cost/benefit of doing this.
I hope this helps as a very general guideline.
Regards,
Rob

Optimal Drive Performance

I am getting ready to setup our first SQL server in house. I have been
looking into hardware requirements for drive performance and memory.
The specs that were sent to me by a vendor sugested that I use 768MB of
memory in the SQL server. I had another vendor laugh and say he would
not install less than 2GB but recommended 4GB. Where is a good place to
be as far as memory was is concearned.
For Drives. One vendor recommended installing
2 x 18GB RAID 1 for OS and SQL logging.
4 x 18GB RAID 0+1 for Data
The configuration they gave only allows for one RAID controller to
access the cage. Because of this I don't see a benifit and would all six
drives in a RAID 5 be better performance?
Another Vendor suggested:
2 x 36GB RAID 1 for OS
2 x 36GB RAID 1 for Data
2 x 36GB RAID 1 for Logging
3 x RAID controller channels for performance
They suggested that this gives the best performance for logging because
logging takes the most IO. If that is the case wouldn't RAID 5 or RAID 0
be better performance for writing. RAID 0 wouldn't give me redundancy
but would give very fast write performance.
Since the first Vendor recommended a Prolient ML350, It is not possible
for me to do the second suggestion since the drive cage can not be
segregated for the 3 channel RAID adapter.
I have a choice to either go with the first suggestion, return the
server and upgrade to a DL380 to allow for drive segregation or my
hybrid of a configuration:
Add a 2 drive Drive Cage in the open 5.25 slots.
Run the Data drive in a RAID 0 + 1 from the primary DRIVE Cage.
Run the OS + Logging in the new Drive Cage using RAID 1.
Any comments or suggestions. This is my first SQL implementation.
Thank You,
John JakusMemory is too cheap these days to not have enough. How large do you expect
your DB to get and of that how much of the data would be read or written to
each day? How many transactions per second do you expect to do? Will you
read large amounts of data at a time or small amounts?
--
Andrew J. Kelly
SQL Server MVP
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:eTpadQ56DHA.3704@.tk2msftngp13.phx.gbl...
> I am getting ready to setup our first SQL server in house. I have been
> looking into hardware requirements for drive performance and memory.
> The specs that were sent to me by a vendor sugested that I use 768MB of
> memory in the SQL server. I had another vendor laugh and say he would
> not install less than 2GB but recommended 4GB. Where is a good place to
> be as far as memory was is concearned.
> For Drives. One vendor recommended installing
> 2 x 18GB RAID 1 for OS and SQL logging.
> 4 x 18GB RAID 0+1 for Data
> The configuration they gave only allows for one RAID controller to
> access the cage. Because of this I don't see a benifit and would all six
> drives in a RAID 5 be better performance?
> Another Vendor suggested:
> 2 x 36GB RAID 1 for OS
> 2 x 36GB RAID 1 for Data
> 2 x 36GB RAID 1 for Logging
> 3 x RAID controller channels for performance
> They suggested that this gives the best performance for logging because
> logging takes the most IO. If that is the case wouldn't RAID 5 or RAID 0
> be better performance for writing. RAID 0 wouldn't give me redundancy
> but would give very fast write performance.
> Since the first Vendor recommended a Prolient ML350, It is not possible
> for me to do the second suggestion since the drive cage can not be
> segregated for the 3 channel RAID adapter.
> I have a choice to either go with the first suggestion, return the
> server and upgrade to a DL380 to allow for drive segregation or my
> hybrid of a configuration:
> Add a 2 drive Drive Cage in the open 5.25 slots.
> Run the Data drive in a RAID 0 + 1 from the primary DRIVE Cage.
> Run the OS + Logging in the new Drive Cage using RAID 1.
> Any comments or suggestions. This is my first SQL implementation.
> Thank You,
> John Jakus|||Andrew J. Kelly wrote:
> Memory is too cheap these days to not have enough. How large do you expect
> your DB to get and of that how much of the data would be read or written to
> each day? How many transactions per second do you expect to do? Will you
> read large amounts of data at a time or small amounts?
>
I have no idea. This is for an implementation of Axapta. They never gave
me estimates on how many transactions will be performed. I just know it
will slowly ramp up. I know it's better to over build a system like this
because it's easier then upgrading later. I just want to know which
configuration will perform better with an SQL server. Since I am new to
SQL Server.
Sorry for being so vague but this is all I have right now and they are
in a hurry to implement. I just want to make it the most robust that I can.
Thanks,
John Jakus|||John
Take a look at this link you'll find lots of useful info and tips.
http://www.sql-server-performance.com
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:ubcuJq66DHA.2404@.TK2MSFTNGP11.phx.gbl...
> Andrew J. Kelly wrote:
> > Memory is too cheap these days to not have enough. How large do you
expect
> > your DB to get and of that how much of the data would be read or written
to
> > each day? How many transactions per second do you expect to do? Will
you
> > read large amounts of data at a time or small amounts?
> >
> I have no idea. This is for an implementation of Axapta. They never gave
> me estimates on how many transactions will be performed. I just know it
> will slowly ramp up. I know it's better to over build a system like this
> because it's easier then upgrading later. I just want to know which
> configuration will perform better with an SQL server. Since I am new to
> SQL Server.
> Sorry for being so vague but this is all I have right now and they are
> in a hurry to implement. I just want to make it the most robust that I
can.
> Thanks,
> John Jakus|||Well it's imposable to tell if any of those configurations will ultimately
suite your needs without that kind of information. So with this in mind I
would opt for the first configuration:
2 x 18GB RAID 1 for OS and SQL logging.
4 x 18GB RAID 0+1 for Data
This will separate the log files from he data which is important under heavy
write situations. A RAID 0+1 is good, the only thing is it only has 4
disks. This makes for only 36GB of usable space and as always with database
the more disks the better, not the size. Hope that is going to be enough.
If not and you are stuck with that drive configuration maybe you can use
36GB drives instead of 18GB for the Raid 0+1. since they didn't provide
specs it is most likely not too intensive of an application and this should
be fine. Definitely go with more memory than 768 though. Again how much
depends on the size and use of the db but 2GB should do it.
--
Andrew J. Kelly
SQL Server MVP
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:ubcuJq66DHA.2404@.TK2MSFTNGP11.phx.gbl...
> Andrew J. Kelly wrote:
> > Memory is too cheap these days to not have enough. How large do you
expect
> > your DB to get and of that how much of the data would be read or written
to
> > each day? How many transactions per second do you expect to do? Will
you
> > read large amounts of data at a time or small amounts?
> >
> I have no idea. This is for an implementation of Axapta. They never gave
> me estimates on how many transactions will be performed. I just know it
> will slowly ramp up. I know it's better to over build a system like this
> because it's easier then upgrading later. I just want to know which
> configuration will perform better with an SQL server. Since I am new to
> SQL Server.
> Sorry for being so vague but this is all I have right now and they are
> in a hurry to implement. I just want to make it the most robust that I
can.
> Thanks,
> John Jakus|||To try and cover your issues separately
Memory - as suggested, memory is relatively inexpensive these days and database servers are generally pretty memory and disk intensive. It really depends on the usage profile and DB size but 4GB is not really *that* expensive
Disk config: Disk config is very important as it can make a major difference to the performance of the database
Here are some general recommendations but again this depends on the app and how it will be used
RAID 5: Good fault tolerance, good read performance, BAD write performance (parity has to be written every time the disk is written to). Use this if the DB is read intensive with few writes. RAID 5 offers good fault tolerance at a relatively low cost
RAID 1: Good fault tolerance, and good performance for sequential writes (such as transaction logs). Expensive because you only have effective use of half the disk
RAID 0: RAID 0 stripes data across multiple disks. This offers excellent performance, but NO fault tolerance
RAID 1+0 and RAID 0+1: These two must not be confused - people use the terms interchangeably but they are very different.
RAID 1+0 is the striping of data across multiple RAID 1 mirrors. For example if you have 8 disks, it would stripe data across 4 mirrors. This offers excellent performance, excellent fault tolerance but a not-so-excellent bank balance
RAID 0+1 is the mirroring of two stripe sets. In our scenario of 8 disks, you would have 2 striped sets (RAID 0) of 4 disks each, that are in turn mirrored. This also offers good performance and fault tolerance (as RAID 1+0) but this will be degraded in the event of disk failures (much more than RAID 1+0)
So if faced with the choice between RAID 1+0 and RAID 0+1 I would always choose RAID 1+0
An example of a high-end disk spec would be
OS: 2 x 18.2GB RAID
Logs: 2 x 36.4GB RAID1 - or 4 x 36.4 RAID 1+
Data: 8 x 36.4GB RAID1+0 (this could be any amount of disks in multiples of 2, the total number constrained by the storage device
This may or may not be an overkill depending on the actual app
In terms of controllers, separate controllers sounds like a good idea - again its down to the cost/benefit of doing this
I hope this helps as a very general guideline
Regards
Ro

Operating System Memory. How much?

I currently am looking for a document from microsoft (or close to it)
that tells me that I should have X amount of memory set aside for the
OS for 16 and/or 32 gig memory configurations.
This is also one of those "my boss is asking me for a docuemnt to
support what I'm telling him. things"
this is in ref to SQL/AWE.
On Oct 5, 9:00 am, shawncr...@.yahoo.com wrote:
> I currently am looking for a document from microsoft (or close to it)
> that tells me that I should have X amount of memory set aside for the
> OS for 16 and/or 32 gig memory configurations.
> This is also one of those "my boss is asking me for a docuemnt to
> support what I'm telling him. things"

Operating System Memory. How much?

I currently am looking for a document from microsoft (or close to it)
that tells me that I should have X amount of memory set aside for the
OS for 16 and/or 32 gig memory configurations.
This is also one of those "my boss is asking me for a docuemnt to
support what I'm telling him. things"this is in ref to SQL/AWE.
On Oct 5, 9:00 am, shawncr...@.yahoo.com wrote:
> I currently am looking for a document from microsoft (or close to it)
> that tells me that I should have X amount of memory set aside for the
> OS for 16 and/or 32 gig memory configurations.
> This is also one of those "my boss is asking me for a docuemnt to
> support what I'm telling him. things"

Operating System Memory. How much?

I currently am looking for a document from microsoft (or close to it)
that tells me that I should have X amount of memory set aside for the
OS for 16 and/or 32 gig memory configurations.
This is also one of those "my boss is asking me for a docuemnt to
support what I'm telling him. things"this is in ref to SQL/AWE.
On Oct 5, 9:00 am, shawncr...@.yahoo.com wrote:
> I currently am looking for a document from microsoft (or close to it)
> that tells me that I should have X amount of memory set aside for the
> OS for 16 and/or 32 gig memory configurations.
> This is also one of those "my boss is asking me for a docuemnt to
> support what I'm telling him. things"

OPENXML vs BCP

Hi,
My application writes data into sql server.

Currently it converts data into XML (an in memory XML string) and write
using OPENXML.

I want to know if i write it to a csv file and use BCP, then will it be
faster then OPENXML. (i feel, writing to a csv will create IO operation that
will slow down the process).

ThanksIf you have many records, I would expect BCP to be much faster. The fastest
way to import large amounts of data into SQL Server is using a bulk insert
technique like BCP.

If your data originates as XML, you might also check out XML Bulk Load. See
http://msdn.microsoft.com/library/d...lkload_7pv0.asp

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Sandy" <a@.a.com> wrote in message news:dhr9v4$15v@.netnews.net.lucent.com...
> Hi,
> My application writes data into sql server.
> Currently it converts data into XML (an in memory XML string) and write
> using OPENXML.
> I want to know if i write it to a csv file and use BCP, then will it be
> faster then OPENXML. (i feel, writing to a csv will create IO operation
> that
> will slow down the process).
> Thanks