Optimizing SQL Query

Hello,
I have to run a script which will delete records from 10 tables
based on a certain condition. Instead of having to run the condition 10
times I thought running it once would have better performance and did
something like this
CREATE PROCEDURE testScript AS
set nocount on
declare @.script_table table (row_id int identity(1,1), sid int)
declare @.max int, @.now int, @.result varchar(100)
insert into @.script_table
select sid from Alpha where lname like 'DOWN' and (fname like 'Tom' or
fname like 'Seinfeld')
select @.max = max(row_id) from @.script_table
select @.now=2, @.result=convert(varchar(6), sid) from @.script_table
where row_id=1
while @.max >=@.now
Begin
select @.result = @.result + ',' + convert(varchar(6), sid) from
@.script_table where row_id=@.now
set @.now=@.now + 1;
End
select * from Beta where convert(varchar(5), sid) in ( @.result )
if @.@.error <> 0
BEGIN
ROLLBACK Transaction
RAISERROR ( 'E', 2, 127)
END
...
...
...
but when I run this I dont get any values. Instead when I run this
query I get the output rows
select * from Beta where convert(varchar(5), sid) in (select sid from
Alpha where lname like 'DOWN' and (fname like 'Tom' or fname like
'Seinfeld'))
since @.result has the output from Alpha with a comma delimiter I was
under the impression that this should give me the result but instead I
dont get any rows. Is this because @.result is a varchar? Am I doing
something wrong here? Any help would be great..
Thanks
Khris> Is this because @.result is a varchar?
Yes. You can, however, parse a delimited string and have values returned in
a table, that you can use in your query.
My favourite solution is this one (by Dejan Sarka):
http://solidqualitylearning.com/blo.../10/22/200.aspx
ML
http://milambda.blogspot.com/|||Thanks ML, I will try that out|||Thanks ML I will try that out
Khris

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.
> > > > > > > ----
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > >
> > >
> >
> >
>

Optimization Question: Date ranges, Between Operaror and Clustered Index

I searched Google and while I'm certain this has been discussed, there
are too many hits for my key words, so I'll ask these questions afresh:
1) Is it reasonable to expect improved performance by putting the
clustered index on the field you most use for RANGE searches.
2) Is there any reason you wouldn't create a clustered index on a
DateTime field, if the data in that field was autogenerated by
the server clock and moved only forward chronologically?
3) Is the BETWEEN operator slower than >= and <= Is this ... CallDate BETWEEN '2004-01-15' AND '2004-01-23'
faster or slower than this ...
CallDate >='2004-01-15' AND CallDate <= '2004-01-23'
Thanks in advance for any feedback you have to give on these issues.
--
Danny J. Lesandrini
dlesandrini@.hotmail.com
http://amazecreations.com/datafast/"Danny J. Lesandrini" <dlesandrini@.hotmail.com> wrote in message
news:eDFKzxTVEHA.2508@.TK2MSFTNGP12.phx.gbl...
> 1) Is it reasonable to expect improved performance by putting the
> clustered index on the field you most use for RANGE searches.
Most likely, yes.
> 2) Is there any reason you wouldn't create a clustered index on a
> DateTime field, if the data in that field was autogenerated by
> the server clock and moved only forward chronologically?
No, in my opinion that would be a good candidate for a clustered index
as it would create a hotspot at the end of the table, which is good for
insert performance.
> 3) Is the BETWEEN operator slower than >= and <=> Is this ... CallDate BETWEEN '2004-01-15' AND '2004-01-23'
> faster or slower than this ...
> CallDate >='2004-01-15' AND CallDate <= '2004-01-23'
No. But there are other issues to consider. See:
http://www.aspfaq.com/show.asp?id=2280|||Thanks Adam, that was exactly what I was looking for.
Danny
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ucJkm8TVEHA.2944@.tk2msftngp13.phx.gbl...
> "Danny J. Lesandrini" <dlesandrini@.hotmail.com> wrote in message
> news:eDFKzxTVEHA.2508@.TK2MSFTNGP12.phx.gbl...
> >
> > 1) Is it reasonable to expect improved performance by putting the
> > clustered index on the field you most use for RANGE searches.
> Most likely, yes.
> > 2) Is there any reason you wouldn't create a clustered index on a
> > DateTime field, if the data in that field was autogenerated by
> > the server clock and moved only forward chronologically?
> No, in my opinion that would be a good candidate for a clustered index
> as it would create a hotspot at the end of the table, which is good for
> insert performance.
> > 3) Is the BETWEEN operator slower than >= and <=> > Is this ... CallDate BETWEEN '2004-01-15' AND '2004-01-23'
> > faster or slower than this ...
> > CallDate >='2004-01-15' AND CallDate <= '2004-01-23'
> No. But there are other issues to consider. See:
> http://www.aspfaq.com/show.asp?id=2280
>
>
>
>sql

Optimization Question: Date ranges, Between Operaror and Clustered Index

I searched Google and while I'm certain this has been discussed, there
are too many hits for my key words, so I'll ask these questions afresh:
1) Is it reasonable to expect improved performance by putting the
clustered index on the field you most use for RANGE searches.
2) Is there any reason you wouldn't create a clustered index on a
DateTime field, if the data in that field was autogenerated by
the server clock and moved only forward chronologically?
3) Is the BETWEEN operator slower than >= and <=
Is this ... CallDate BETWEEN '2004-01-15' AND '2004-01-23'
faster or slower than this ...
CallDate >='2004-01-15' AND CallDate <= '2004-01-23'
Thanks in advance for any feedback you have to give on these issues.
--
Danny J. Lesandrini
dlesandrini@.hotmail.com
http://amazecreations.com/datafast/"Danny J. Lesandrini" <dlesandrini@.hotmail.com> wrote in message
news:eDFKzxTVEHA.2508@.TK2MSFTNGP12.phx.gbl...
> 1) Is it reasonable to expect improved performance by putting the
> clustered index on the field you most use for RANGE searches.
Most likely, yes.

> 2) Is there any reason you wouldn't create a clustered index on a
> DateTime field, if the data in that field was autogenerated by
> the server clock and moved only forward chronologically?
No, in my opinion that would be a good candidate for a clustered index
as it would create a hotspot at the end of the table, which is good for
insert performance.

> 3) Is the BETWEEN operator slower than >= and <=
> Is this ... CallDate BETWEEN '2004-01-15' AND '2004-01-23'
> faster or slower than this ...
> CallDate >='2004-01-15' AND CallDate <= '2004-01-23'
No. But there are other issues to consider. See:
http://www.aspfaq.com/show.asp?id=2280|||Thanks Adam, that was exactly what I was looking for.
Danny
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ucJkm8TVEHA.2944@.tk2msftngp13.phx.gbl...
> "Danny J. Lesandrini" <dlesandrini@.hotmail.com> wrote in message
> news:eDFKzxTVEHA.2508@.TK2MSFTNGP12.phx.gbl...
> Most likely, yes.
>
> No, in my opinion that would be a good candidate for a clustered index
> as it would create a hotspot at the end of the table, which is good for
> insert performance.
>
> No. But there are other issues to consider. See:
> http://www.aspfaq.com/show.asp?id=2280
>
>
>
>

Opinions on Option (Keepfixed Plan)

I've got this situation where a certain large stored proc (that's used
all day long) just compiles way too often, to the point where it hurts
the performance. I've tried all the tricks I could find to reduce the
number of compilations. So my last option is to use Option (Keepfixed
Plan) hint.
Are there any downsides to using Option (Keepfixed Plan)?
Regards
Hi Frank
Have you read these article to determine the cause of the recompilations and
address the actual reasons?
Troubleshooting stored procedure recompilation
http://support.microsoft.com/kb/243586
How to identify the cause of recompilation in an SP:Recompile event
http://support.microsoft.com/kb/308737
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Frank Rizzo" <none@.none.com> wrote in message
news:uuunQQy5GHA.4064@.TK2MSFTNGP03.phx.gbl...
> I've got this situation where a certain large stored proc (that's used all
> day long) just compiles way too often, to the point where it hurts the
> performance. I've tried all the tricks I could find to reduce the number
> of compilations. So my last option is to use Option (Keepfixed Plan)
> hint.
> Are there any downsides to using Option (Keepfixed Plan)?
> Regards
|||Kalen Delaney wrote:
> Hi Frank
> Have you read these article to determine the cause of the recompilations and
> address the actual reasons?
> Troubleshooting stored procedure recompilation
> http://support.microsoft.com/kb/243586
> How to identify the cause of recompilation in an SP:Recompile event
> http://support.microsoft.com/kb/308737
Yep, I did all these. I decreased SP:Recompile events in Profiler
substantially, however, they were still happening quite a bit.
Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
(anecdotally) increased the performance of the stored proc. In Perfmon,
however, I still get a large number for the SQL Statistic/SQL
Compilations. It must mean something else than what is reflected by the
SP:Recompile event in Profiler.
|||Frank Rizzo wrote:
> Kalen Delaney wrote:
> Yep, I did all these. I decreased SP:Recompile events in Profiler
> substantially, however, they were still happening quite a bit.
> Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
> (anecdotally) increased the performance of the stored proc. In Perfmon,
> however, I still get a large number for the SQL Statistic/SQL
> Compilations. It must mean something else than what is reflected by the
> SP:Recompile event in Profiler.
If the recompilations are caused by a large amount of
inserts/updates/deletes, then you could experiment with turning off
auto-update statistics and run a schedule to manually update the
statistics at a time that is convenient for you.
Gert-Jan
|||Gert-Jan Strik wrote:
> Frank Rizzo wrote:
> If the recompilations are caused by a large amount of
> inserts/updates/deletes, then you could experiment with turning off
> auto-update statistics and run a schedule to manually update the
> statistics at a time that is convenient for you.
I turned off the auto-update stats from the get go, but the problem is
still happening.

> Gert-Jan

Opinions on Option (Keepfixed Plan)

I've got this situation where a certain large stored proc (that's used
all day long) just compiles way too often, to the point where it hurts
the performance. I've tried all the tricks I could find to reduce the
number of compilations. So my last option is to use Option (Keepfixed
Plan) hint.
Are there any downsides to using Option (Keepfixed Plan)?
RegardsHi Frank
Have you read these article to determine the cause of the recompilations and
address the actual reasons?
Troubleshooting stored procedure recompilation
http://support.microsoft.com/kb/243586
How to identify the cause of recompilation in an SP:Recompile event
http://support.microsoft.com/kb/308737
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Frank Rizzo" <none@.none.com> wrote in message
news:uuunQQy5GHA.4064@.TK2MSFTNGP03.phx.gbl...
> I've got this situation where a certain large stored proc (that's used all
> day long) just compiles way too often, to the point where it hurts the
> performance. I've tried all the tricks I could find to reduce the number
> of compilations. So my last option is to use Option (Keepfixed Plan)
> hint.
> Are there any downsides to using Option (Keepfixed Plan)?
> Regards|||Kalen Delaney wrote:
> Hi Frank
> Have you read these article to determine the cause of the recompilations and
> address the actual reasons?
> Troubleshooting stored procedure recompilation
> http://support.microsoft.com/kb/243586
> How to identify the cause of recompilation in an SP:Recompile event
> http://support.microsoft.com/kb/308737
Yep, I did all these. I decreased SP:Recompile events in Profiler
substantially, however, they were still happening quite a bit.
Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
(anecdotally) increased the performance of the stored proc. In Perfmon,
however, I still get a large number for the SQL Statistic/SQL
Compilations. It must mean something else than what is reflected by the
SP:Recompile event in Profiler.|||Frank Rizzo wrote:
> Kalen Delaney wrote:
> > Hi Frank
> >
> > Have you read these article to determine the cause of the recompilations and
> > address the actual reasons?
> >
> > Troubleshooting stored procedure recompilation
> > http://support.microsoft.com/kb/243586
> >
> > How to identify the cause of recompilation in an SP:Recompile event
> > http://support.microsoft.com/kb/308737
> Yep, I did all these. I decreased SP:Recompile events in Profiler
> substantially, however, they were still happening quite a bit.
> Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
> (anecdotally) increased the performance of the stored proc. In Perfmon,
> however, I still get a large number for the SQL Statistic/SQL
> Compilations. It must mean something else than what is reflected by the
> SP:Recompile event in Profiler.
If the recompilations are caused by a large amount of
inserts/updates/deletes, then you could experiment with turning off
auto-update statistics and run a schedule to manually update the
statistics at a time that is convenient for you.
Gert-Jan|||Gert-Jan Strik wrote:
> Frank Rizzo wrote:
>> Kalen Delaney wrote:
>> Hi Frank
>> Have you read these article to determine the cause of the recompilations and
>> address the actual reasons?
>> Troubleshooting stored procedure recompilation
>> http://support.microsoft.com/kb/243586
>> How to identify the cause of recompilation in an SP:Recompile event
>> http://support.microsoft.com/kb/308737
>> Yep, I did all these. I decreased SP:Recompile events in Profiler
>> substantially, however, they were still happening quite a bit.
>> Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
>> (anecdotally) increased the performance of the stored proc. In Perfmon,
>> however, I still get a large number for the SQL Statistic/SQL
>> Compilations. It must mean something else than what is reflected by the
>> SP:Recompile event in Profiler.
> If the recompilations are caused by a large amount of
> inserts/updates/deletes, then you could experiment with turning off
> auto-update statistics and run a schedule to manually update the
> statistics at a time that is convenient for you.
I turned off the auto-update stats from the get go, but the problem is
still happening.
> Gert-Jan

Opinions on Option (Keepfixed Plan)

I've got this situation where a certain large stored proc (that's used
all day long) just compiles way too often, to the point where it hurts
the performance. I've tried all the tricks I could find to reduce the
number of compilations. So my last option is to use Option (Keepfixed
Plan) hint.
Are there any downsides to using Option (Keepfixed Plan)?
RegardsHi Frank
Have you read these article to determine the cause of the recompilations and
address the actual reasons?
Troubleshooting stored procedure recompilation
http://support.microsoft.com/kb/243586
How to identify the cause of recompilation in an SP:Recompile event
http://support.microsoft.com/kb/308737
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Frank Rizzo" <none@.none.com> wrote in message
news:uuunQQy5GHA.4064@.TK2MSFTNGP03.phx.gbl...
> I've got this situation where a certain large stored proc (that's used all
> day long) just compiles way too often, to the point where it hurts the
> performance. I've tried all the tricks I could find to reduce the number
> of compilations. So my last option is to use Option (Keepfixed Plan)
> hint.
> Are there any downsides to using Option (Keepfixed Plan)?
> Regards|||Kalen Delaney wrote:
> Hi Frank
> Have you read these article to determine the cause of the recompilations a
nd
> address the actual reasons?
> Troubleshooting stored procedure recompilation
> http://support.microsoft.com/kb/243586
> How to identify the cause of recompilation in an SP:Recompile event
> http://support.microsoft.com/kb/308737
Yep, I did all these. I decreased SP:Recompile events in Profiler
substantially, however, they were still happening quite a bit.
Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
(anecdotally) increased the performance of the stored proc. In Perfmon,
however, I still get a large number for the SQL Statistic/SQL
Compilations. It must mean something else than what is reflected by the
SP:Recompile event in Profiler.|||Frank Rizzo wrote:
> Kalen Delaney wrote:
> Yep, I did all these. I decreased SP:Recompile events in Profiler
> substantially, however, they were still happening quite a bit.
> Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
> (anecdotally) increased the performance of the stored proc. In Perfmon,
> however, I still get a large number for the SQL Statistic/SQL
> Compilations. It must mean something else than what is reflected by the
> SP:Recompile event in Profiler.
If the recompilations are caused by a large amount of
inserts/updates/deletes, then you could experiment with turning off
auto-update statistics and run a schedule to manually update the
statistics at a time that is convenient for you.
Gert-Jan|||Gert-Jan Strik wrote:
> Frank Rizzo wrote:
> If the recompilations are caused by a large amount of
> inserts/updates/deletes, then you could experiment with turning off
> auto-update statistics and run a schedule to manually update the
> statistics at a time that is convenient for you.
I turned off the auto-update stats from the get go, but the problem is
still happening.

> Gert-Jan

OPENXML question

I am using the OpenXml command and I have a question (XML posted below)...
I don't know how to get the values of certain elements if they columns
needed are not attributes to the XML...
For instance I can get the value of /attributes/items for the type attribute
but if I try to get the values of the /attributes/items/item elements it
returns null.
'<attributes>
<lot>E070484</lot>
<from-location type="dock">AA01</from-location>
<dest-location type="dock">AA05</dest-location>
<items type="yarn">
<item>QP35990</item>
<item>QP36134</item>
</items>
</attributes>'
JITry the following:
SELECT *
FROM OPENXML ( @.idoc, '/attributes/items/item',2 )
with
([Value] varchar(100) '.')
Q
"JI" wrote:

> I am using the OpenXml command and I have a question (XML posted below)...
> I don't know how to get the values of certain elements if they columns
> needed are not attributes to the XML...
> For instance I can get the value of /attributes/items for the type attribu
te
> but if I try to get the values of the /attributes/items/item elements it
> returns null.
> '<attributes>
> <lot>E070484</lot>
> <from-location type="dock">AA01</from-location>
> <dest-location type="dock">AA05</dest-location>
> <items type="yarn">
> <item>QP35990</item>
> <item>QP36134</item>
> </items>
> </attributes>'
>
> --
> JI|||Try,
use northwind
go
declare @.idoc int
declare @.doc varchar(1000)
set @.doc ='<attributes>
<lot>E070484</lot>
<from-location type="dock">AA01</from-location>
<dest-location type="dock">AA05</dest-location>
<items type="yarn">
<item>QP35990</item>
<item>QP36134</item>
</items>
</attributes>'
exec sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT
*
FROM
OPENXML (@.idoc, '/attributes/items/item')
with (item varchar(10) '/attributes/items/item')
EXEC sp_xml_removedocument @.idoc
"JI" wrote:

> I am using the OpenXml command and I have a question (XML posted below)...
> I don't know how to get the values of certain elements if they columns
> needed are not attributes to the XML...
> For instance I can get the value of /attributes/items for the type attribu
te
> but if I try to get the values of the /attributes/items/item elements it
> returns null.
> '<attributes>
> <lot>E070484</lot>
> <from-location type="dock">AA01</from-location>
> <dest-location type="dock">AA05</dest-location>
> <items type="yarn">
> <item>QP35990</item>
> <item>QP36134</item>
> </items>
> </attributes>'
>
> --
> JI