OpenXML Identity Insert: based

Showing posts with label based. Show all posts

Monday, March 19, 2012

Optimising Query based on Views

quote:

> I would be grateful for some advice.
> I have a query which selects from a view which, in turn, is based on three
> other view. I want to optimise the query.
> The query is built dynamically so cannot be made into a stored procedure.

quote:

> it worth my while making the main view into a user-defined function so

that

quote:

> I can select from it. (I understand that you can't do "SELECT * FROM
> SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC

GROUP

quote:

> BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
> user-defined function.) I am suggesting this because I have the impression
> that stored procedures and user-defined functions are pre-compiled with an
> execution plan by SQL server, whereas this is not possible for views.
> Many thanks in advance,
> Richard Cox.
>
>

|||Thanks very much for your explanation, Geoff. Looks like there is nothing
much to be gained in this case then.
Richard.|||Richard
Query Optimyzer does not produce query plan for views. On other hand when
you create clustered index on view it is materialized and store in the same
way as store clusetred index created on the table. I have seen queries that
after adding clustered index have ran more faster.
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:e8IESL$4DHA.1852@.TK2MSFTNGP10.phx.gbl...

quote:

> Thanks very much for your explanation, Geoff. Looks like there is nothing
> much to be gained in this case then.
> Richard.
>

Optimising Query based on Views

I would be grateful for some advice.
I have a query which selects from a view which, in turn, is based on three
other view. I want to optimise the query.
The query is built dynamically so cannot be made into a stored procedure. Is
it worth my while making the main view into a user-defined function so that
I can select from it. (I understand that you can't do "SELECT * FROM
SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC GROUP
BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
user-defined function.) I am suggesting this because I have the impression
that stored procedures and user-defined functions are pre-compiled with an
execution plan by SQL server, whereas this is not possible for views.
Many thanks in advance,
Richard Cox.Yes and no. Views are totally transparent to the optimizer and are only
useful as an abstraction layer and security feature.. The optimizer looks
at the underlying tables rather than the view, EXCEPT for partitioned views
which I will conveniently ignore here.
The big advantage to a stored procedure is query plan reuse. The optimizer
figures out its 'best' plan once and reuses it until it is no longer valid
or it is aged out of cache. If you call the procedure once a day, this
won't help much. On the other hand, even stored procedures have limits.
Temporary tables and dynamic SQL are two of the biggest reasons why a stored
procedure will be recompiled.
From your description, the result set and filter conditions may change from
execution to execution so the advantage of plan reuse just doesn't apply.
You may have to construct a few samples and see if the optimizer does what
you think it should. Use the 'View Estimated Execution Plan' button in
Query Analyzer to see what SQL will do with various combinations of your
query.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
CareerBuilder.com
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:en6D1q54DHA.2736@.TK2MSFTNGP09.phx.gbl...
> I would be grateful for some advice.
> I have a query which selects from a view which, in turn, is based on three
> other view. I want to optimise the query.
> The query is built dynamically so cannot be made into a stored procedure.
Is
> it worth my while making the main view into a user-defined function so
that
> I can select from it. (I understand that you can't do "SELECT * FROM
> SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC
GROUP
> BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
> user-defined function.) I am suggesting this because I have the impression
> that stored procedures and user-defined functions are pre-compiled with an
> execution plan by SQL server, whereas this is not possible for views.
> Many thanks in advance,
> Richard Cox.
>
>|||Thanks very much for your explanation, Geoff. Looks like there is nothing
much to be gained in this case then.
Richard.|||Richard
Query Optimyzer does not produce query plan for views. On other hand when
you create clustered index on view it is materialized and store in the same
way as store clusetred index created on the table. I have seen queries that
after adding clustered index have ran more faster.
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:e8IESL$4DHA.1852@.TK2MSFTNGP10.phx.gbl...
> Thanks very much for your explanation, Geoff. Looks like there is nothing
> much to be gained in this case then.
> Richard.
>

Optimising a query / stored procedure

I have a Stored Procedure based on a View which is running very slowly, so
we need to rewrite it. (Need to reduce from 10sec to 1sec)
Using Query Analyser we Executed the Stored Procedure and it takes 10 Sec.
However re-Executing with same parameters it only takes 1 sec, it's like the
Query Optimiser has cached or remembered the Execution Plan or something.
(If I use a new set or parameters then it takes 10 secs again)
Unless we can get a consistent result of how long the current SP takes to
run, there's no way of determining whether any changes are beneficial - is
there some way of clearing the "cache" or "un-remembering" the Execution
Plan so that we can get a consistent result to compare with ?
(This is SQL Server 2000 standard edition)Look at
DBCC DROPCLEANBUFFER
DBCC FREEPROCCACHE
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Richard" <richa@.heidmar.co.uk> wrote in message
news:EHNkb.10$to6.7@.newsr2.u-net.net...
> I have a Stored Procedure based on a View which is running very slowly, so
> we need to rewrite it. (Need to reduce from 10sec to 1sec)
> Using Query Analyser we Executed the Stored Procedure and it takes 10 Sec.
> However re-Executing with same parameters it only takes 1 sec, it's like
the
> Query Optimiser has cached or remembered the Execution Plan or something.
> (If I use a new set or parameters then it takes 10 secs again)
> Unless we can get a consistent result of how long the current SP takes to
> run, there's no way of determining whether any changes are beneficial - is
> there some way of clearing the "cache" or "un-remembering" the Execution
> Plan so that we can get a consistent result to compare with ?
> (This is SQL Server 2000 standard edition)
>|||... and CHECKPOINT in the beginning to get rid of dirty pages.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:ei6GoxulDHA.3316@.tk2msftngp13.phx.gbl...
> Look at
> DBCC DROPCLEANBUFFER
> DBCC FREEPROCCACHE
>
> --
> --
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "Richard" <richa@.heidmar.co.uk> wrote in message
> news:EHNkb.10$to6.7@.newsr2.u-net.net...
> > I have a Stored Procedure based on a View which is running very slowly, so
> > we need to rewrite it. (Need to reduce from 10sec to 1sec)
> > Using Query Analyser we Executed the Stored Procedure and it takes 10 Sec.
> > However re-Executing with same parameters it only takes 1 sec, it's like
> the
> > Query Optimiser has cached or remembered the Execution Plan or something.
> > (If I use a new set or parameters then it takes 10 secs again)
> > Unless we can get a consistent result of how long the current SP takes to
> > run, there's no way of determining whether any changes are beneficial - is
> > there some way of clearing the "cache" or "un-remembering" the Execution
> > Plan so that we can get a consistent result to compare with ?
> >
> > (This is SQL Server 2000 standard edition)
> >
> >
>

Monday, March 12, 2012

Optimal configuration for report generator

I am working with a report generator that is based on SQL Server 2000 and
uses ASP as the UI. Basically we have a set of reports that end users can
execute through a web browser. In general the model works fine, but we are
running into some scaling issues.
What I'm trying to determine is, what is the optimal configuration for this
system. It is currently a 2.4G Pentium with a large RAID and 1G of RAM. We
have been using the "fixed" memory configuration, allocating 864M to SQL.
This is on a Windows 2003 server box.
This works fine when a "small" query or two is executed, but the performance
suffers terribly when several users try to run reports in parallel. A single
query might take 10 minutes to run if nothing else is happening on the box,
but if additional users log on an run reports, it's almost impossible to
predict when the queries will finish.
I am also looking at the effect of database size on performance, running
tests against a database with 1 month, 3 months, and say 12 months of data,
running the same query against 2 databases in parallel. With the original
configuration, the results were all over the place, with the 12 month
database outperforming the smaller dbs, while other times there was little
difference. It seems that once the system starts paging, and paging heavily,
it's over; the system never "recovers" and queries that previously ran in a
few minutes now take hours.
I added 3 G more memory to the system, and modified boot.ini to include the
/3GB switch. Now when I run the same tests, the results are much more
consistent, as the system rarely ever has to swap. Then again I've never
seen it go past 1.7G in Task manager, making me think that any more than say
2.5G of memory is a waste?
Things we are trying to determine are:
- in the SQL Server memory configuration, is Fixed better than Dynamic? We
have read that Dynamic is not good at returning memory to the OS once it's
been allocated
- What else can we do to optimize the performance for this application? It
seems to me if the indexes are properly designed, the database size
shouldn't have that much impact on performance, but this appears to be true
only to a point. In comparing the execution plans between say a 12 month and
a 3 month database, the plans are sometimes dramatically different. I assume
this is due to the optimizer deciding that going directly to the base tables
and not using an index will result in better performance, when in reality,
this doesn't always appear to be true.
- Are there other SQL Server switches I should be tweaking? Is there some
number of simultaneous queries that this configuration should be limited to?
- What about other versions of SQL Server (e.g. Enterprise, Data Center,
etc) would these buy us anything?
Thanks for any advice,
-Gary
Gary,
I suppose I would have to have more information. Performance and Tuning
is a step by step process (one is always robbing Peter to pay Paul, so
to speak).
how big is the database in question?
what is the RAID configuration (0,1,5,10)?
what does the queries that are running look like? have you looked at
the execution plan to make sure the indexes are being used? (a nested
iteration join with a table scan can cause massive slow downs).
how big are the tables?
it might be the case that you are suffering from spinlock contention
(context switching on the processor)...in which case you might need to
have more processors. you can run a profile on the processor
performance to get the proper information. however, i would strongly
suggest looking at the logical design of the database, the query
itself, the statistics on the objects involved and the indexes. the
optimizer could be ignoring your indexes because the statistics are out
of wack or the weight of the search is to high.
there are just too many factors that could be responsible for the
slowness for me to answer without knowing them.
there is a decent performance and tuning section in the manual, if you
want to give it a look.
hth.
hans nelsen
|||Here are my two cents. Mileage may vary, past results do not guarantee
future performance etc...
-- in the SQL Server memory configuration, is Fixed better than
Dynamic? We have read that Dynamic is not good at returning memory to
the OS once it's been allocated
CHOOSE DYNAMIC. MSSQL is meant to function on its own box. It will
take over all the resources. If you're running MSFT small business
server - then you might want fixed.
-- What else can we do to optimize the performance for this
application? It seems to me if the indexes are properly designed, the
database size shouldn't have that much impact on performance, but this
appears to be true only to a point. In comparing the execution plans
between say a 12 month and a 3 month database, the plans are sometimes
dramatically different. I assume this is due to the optimizer deciding
that going directly to the base tables
and not using an index will result in better performance, when in
reality,this doesn't always appear to be true.
COMMON PROBLEMS. The most common problems are user locks. Unlike
Oracle, MSSQL is lock crazy. It places locks on everything. Ensure
that read-locks are not competing against write-locks. Also ensure
that your indexes are being regularly defragged. DBCC DBREINDEX or
DBCC INDEXDEFRAG. Looking at the execution plan in query analyzer --
should tell you. Auto create statistics and auto update statistics
should also be turned on.
-- Are there other SQL Server switches I should be tweaking? Is there
some number of simultaneous queries that this configuration should be
limited to?
Look at this free article from microsoft/technet.
http://www.microsoft.com/technet/pro.../c02ppcsq.mspx
Here is a quote from it:
Caution: Setting fixed memory incorrectly can cause serious performance
problems on SQL Server. Use fixed memory only in circumstances when you
need to ensure that an exact amount of memory is available for SQL
Server.
-- What about other versions of SQL Server (e.g. Enterprise, Data
Center, etc) would these buy us anything?
No. Standard works just fine for 99% of users. Enterprise does get
you Reporting Services and indexed views and log shipping.

Optimal configuration for report generator

I am working with a report generator that is based on SQL Server 2000 and
uses ASP as the UI. Basically we have a set of reports that end users can
execute through a web browser. In general the model works fine, but we are
running into some scaling issues.
What I'm trying to determine is, what is the optimal configuration for this
system. It is currently a 2.4G Pentium with a large RAID and 1G of RAM. We
have been using the "fixed" memory configuration, allocating 864M to SQL.
This is on a Windows 2003 server box.
This works fine when a "small" query or two is executed, but the performance
suffers terribly when several users try to run reports in parallel. A single
query might take 10 minutes to run if nothing else is happening on the box,
but if additional users log on an run reports, it's almost impossible to
predict when the queries will finish.
I am also looking at the effect of database size on performance, running
tests against a database with 1 month, 3 months, and say 12 months of data,
running the same query against 2 databases in parallel. With the original
configuration, the results were all over the place, with the 12 month
database outperforming the smaller dbs, while other times there was little
difference. It seems that once the system starts paging, and paging heavily,
it's over; the system never "recovers" and queries that previously ran in a
few minutes now take hours.
I added 3 G more memory to the system, and modified boot.ini to include the
/3GB switch. Now when I run the same tests, the results are much more
consistent, as the system rarely ever has to swap. Then again I've never
seen it go past 1.7G in Task manager, making me think that any more than say
2.5G of memory is a waste?
Things we are trying to determine are:
- in the SQL Server memory configuration, is Fixed better than Dynamic? We
have read that Dynamic is not good at returning memory to the OS once it's
been allocated
- What else can we do to optimize the performance for this application? It
seems to me if the indexes are properly designed, the database size
shouldn't have that much impact on performance, but this appears to be true
only to a point. In comparing the execution plans between say a 12 month and
a 3 month database, the plans are sometimes dramatically different. I assume
this is due to the optimizer deciding that going directly to the base tables
and not using an index will result in better performance, when in reality,
this doesn't always appear to be true.
- Are there other SQL Server switches I should be tweaking? Is there some
number of simultaneous queries that this configuration should be limited to?
- What about other versions of SQL Server (e.g. Enterprise, Data Center,
etc) would these buy us anything?
Thanks for any advice,
-GaryGary,
I suppose I would have to have more information. Performance and Tuning
is a step by step process (one is always robbing Peter to pay Paul, so
to speak).
how big is the database in question?
what is the RAID configuration (0,1,5,10)?
what does the queries that are running look like? have you looked at
the execution plan to make sure the indexes are being used? (a nested
iteration join with a table scan can cause massive slow downs).
how big are the tables?
it might be the case that you are suffering from spinlock contention
(context switching on the processor)...in which case you might need to
have more processors. you can run a profile on the processor
performance to get the proper information. however, i would strongly
suggest looking at the logical design of the database, the query
itself, the statistics on the objects involved and the indexes. the
optimizer could be ignoring your indexes because the statistics are out
of wack or the weight of the search is to high.
there are just too many factors that could be responsible for the
slowness for me to answer without knowing them.
there is a decent performance and tuning section in the manual, if you
want to give it a look.
hth.
hans nelsen|||Here are my two cents. Mileage may vary, past results do not guarantee
future performance etc...
-- in the SQL Server memory configuration, is Fixed better than
Dynamic? We have read that Dynamic is not good at returning memory to
the OS once it's been allocated
CHOOSE DYNAMIC. MSSQL is meant to function on its own box. It will
take over all the resources. If you're running MSFT small business
server - then you might want fixed.
-- What else can we do to optimize the performance for this
application? It seems to me if the indexes are properly designed, the
database size shouldn't have that much impact on performance, but this
appears to be true only to a point. In comparing the execution plans
between say a 12 month and a 3 month database, the plans are sometimes
dramatically different. I assume this is due to the optimizer deciding
that going directly to the base tables
and not using an index will result in better performance, when in
reality,this doesn't always appear to be true.
COMMON PROBLEMS. The most common problems are user locks. Unlike
Oracle, MSSQL is lock crazy. It places locks on everything. Ensure
that read-locks are not competing against write-locks. Also ensure
that your indexes are being regularly defragged. DBCC DBREINDEX or
DBCC INDEXDEFRAG. Looking at the execution plan in query analyzer --
should tell you. Auto create statistics and auto update statistics
should also be turned on.
-- Are there other SQL Server switches I should be tweaking? Is there
some number of simultaneous queries that this configuration should be
limited to?
Look at this free article from microsoft/technet.
http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c02ppcsq.mspx
Here is a quote from it:
Caution: Setting fixed memory incorrectly can cause serious performance
problems on SQL Server. Use fixed memory only in circumstances when you
need to ensure that an exact amount of memory is available for SQL
Server.
-- What about other versions of SQL Server (e.g. Enterprise, Data
Center, etc) would these buy us anything?
No. Standard works just fine for 99% of users. Enterprise does get
you Reporting Services and indexed views and log shipping.

Optimal configuration for report generator

What I'm trying to determine is, what is the optimal configuration for this
system. It is currently a 2.4G Pentium with a large RAID and 1G of RAM. We
have been using the "fixed" memory configuration, allocating 864M to SQL.
This is on a Windows 2003 server box.

This works fine when a "small" query or two is executed, but the performance
suffers terribly when several users try to run reports in parallel. A single
query might take 10 minutes to run if nothing else is happening on the box,
but if additional users log on an run reports, it's almost impossible to
predict when the queries will finish.

I am also looking at the effect of database size on performance, running
tests against a database with 1 month, 3 months, and say 12 months of data,
running the same query against 2 databases in parallel. With the original
configuration, the results were all over the place, with the 12 month
database outperforming the smaller dbs, while other times there was little
difference. It seems that once the system starts paging, and paging heavily,
it's over; the system never "recovers" and queries that previously ran in a
few minutes now take hours.

I added 3 G more memory to the system, and modified boot.ini to include the
/3GB switch. Now when I run the same tests, the results are much more
consistent, as the system rarely ever has to swap. Then again I've never
seen it go past 1.7G in Task manager, making me think that any more than say
2.5G of memory is a waste?

Things we are trying to determine are:

- in the SQL Server memory configuration, is Fixed better than Dynamic? We
have read that Dynamic is not good at returning memory to the OS once it's
been allocated

- What else can we do to optimize the performance for this application? It
seems to me if the indexes are properly designed, the database size
shouldn't have that much impact on performance, but this appears to be true
only to a point. In comparing the execution plans between say a 12 month and
a 3 month database, the plans are sometimes dramatically different. I assume
this is due to the optimizer deciding that going directly to the base tables
and not using an index will result in better performance, when in reality,
this doesn't always appear to be true.

- Are there other SQL Server switches I should be tweaking? Is there some
number of simultaneous queries that this configuration should be limited to?

- What about other versions of SQL Server (e.g. Enterprise, Data Center,
etc) would these buy us anything?

Thanks for any advice,

-GaryGary,
I suppose I would have to have more information. Performance and Tuning
is a step by step process (one is always robbing Peter to pay Paul, so
to speak).
how big is the database in question?
what is the RAID configuration (0,1,5,10)?
what does the queries that are running look like? have you looked at
the execution plan to make sure the indexes are being used? (a nested
iteration join with a table scan can cause massive slow downs).
how big are the tables?

it might be the case that you are suffering from spinlock contention
(context switching on the processor)...in which case you might need to
have more processors. you can run a profile on the processor
performance to get the proper information. however, i would strongly
suggest looking at the logical design of the database, the query
itself, the statistics on the objects involved and the indexes. the
optimizer could be ignoring your indexes because the statistics are out
of wack or the weight of the search is to high.

there are just too many factors that could be responsible for the
slowness for me to answer without knowing them.
there is a decent performance and tuning section in the manual, if you
want to give it a look.

hth.

hans nelsen|||Here are my two cents. Mileage may vary, past results do not guarantee
future performance etc...

-- in the SQL Server memory configuration, is Fixed better than
Dynamic? We have read that Dynamic is not good at returning memory to
the OS once it's been allocated

CHOOSE DYNAMIC. MSSQL is meant to function on its own box. It will
take over all the resources. If you're running MSFT small business
server - then you might want fixed.

-- What else can we do to optimize the performance for this
application? It seems to me if the indexes are properly designed, the
database size shouldn't have that much impact on performance, but this
appears to be true only to a point. In comparing the execution plans
between say a 12 month and a 3 month database, the plans are sometimes
dramatically different. I assume this is due to the optimizer deciding
that going directly to the base tables
and not using an index will result in better performance, when in
reality,this doesn't always appear to be true.

COMMON PROBLEMS. The most common problems are user locks. Unlike
Oracle, MSSQL is lock crazy. It places locks on everything. Ensure
that read-locks are not competing against write-locks. Also ensure
that your indexes are being regularly defragged. DBCC DBREINDEX or
DBCC INDEXDEFRAG. Looking at the execution plan in query analyzer --
should tell you. Auto create statistics and auto update statistics
should also be turned on.

-- Are there other SQL Server switches I should be tweaking? Is there
some number of simultaneous queries that this configuration should be
limited to?

Look at this free article from microsoft/technet.
http://www.microsoft.com/technet/pr...s/c02ppcsq.mspx
Here is a quote from it:
Caution: Setting fixed memory incorrectly can cause serious performance
problems on SQL Server. Use fixed memory only in circumstances when you
need to ensure that an exact amount of memory is available for SQL
Server.

-- What about other versions of SQL Server (e.g. Enterprise, Data
Center, etc) would these buy us anything?
No. Standard works just fine for 99% of users. Enterprise does get
you Reporting Services and indexed views and log shipping.

Opteron vs Xeon

I've recently been attempting to put into production some Itanium based
servers. I'm running (amongst other things) Remedy, which means highly
serialised transactions (thus limiting the effect of the Itanium) and has
lead me to discover that the low clock speed on the Itanium is causing the
application to run slower (the biggest test of this was a basic bulk insert
into a table with no indexes that would run 50% slower on a 4x1.6GHZ
Itanium vs a 2x2.4GHZ Xeon).
I'm now looking into going to back to a 32bit system, folks are touting the
benefits of the Opteron processor as opposed to the Xeon, stating that the
performance difference is pretty large.
My question is, does the slower clock speed on the Opteron translate into
the same problems that I was experiencing on the Itanium, or am I actually
going to find better i/o performance through the AMD processor?
Thanks
NicOn Thu, 01 Sep 2005 04:20:11 -0700, Nicholas Cain
<nicholas.cain@.nospam.t-mobile.com> wrote:
>I've recently been attempting to put into production some Itanium based
>servers. I'm running (amongst other things) Remedy, which means highly
>serialised transactions (thus limiting the effect of the Itanium) and has
>lead me to discover that the low clock speed on the Itanium is causing the
>application to run slower (the biggest test of this was a basic bulk insert
>into a table with no indexes that would run 50% slower on a 4x1.6GHZ
>Itanium vs a 2x2.4GHZ Xeon).
>I'm now looking into going to back to a 32bit system, folks are touting the
>benefits of the Opteron processor as opposed to the Xeon, stating that the
>performance difference is pretty large.
>My question is, does the slower clock speed on the Opteron translate into
>the same problems that I was experiencing on the Itanium, or am I actually
>going to find better i/o performance through the AMD processor?
Seems unlikely that CPU speed is really the limiting factor on a bulk
load.
J.|||JXStern <JXSternChangeX2R@.gte.net> wrote in
news:i7rdh114n67mjuc7uor55clv95k5f8a3uq@.4ax.com:
> Seems unlikely that CPU speed is really the limiting factor on a bulk
> load.
> J.
>
I've had the gurus at HP look and tell me that this is the limiting factor
(after a lot of consideration and followup with MS).
I didn't believe it myself, however all indications point to that problem.|||I've also seen high CPU utilization with bulk inserts, at least the
fully-logged variety.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Nicholas Cain" <nicholas.cain@.nospam.t-mobile.com> wrote in message
news:Xns96C45AE8629B4nicholascainnospamtm@.207.46.248.16...
> JXStern <JXSternChangeX2R@.gte.net> wrote in
> news:i7rdh114n67mjuc7uor55clv95k5f8a3uq@.4ax.com:
>
>> Seems unlikely that CPU speed is really the limiting factor on a bulk
>> load.
>> J.
>>
> I've had the gurus at HP look and tell me that this is the limiting factor
> (after a lot of consideration and followup with MS).
> I didn't believe it myself, however all indications point to that problem.|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
news:ulSIjWvrFHA.1168@.TK2MSFTNGP11.phx.gbl:
> I've also seen high CPU utilization with bulk inserts, at least the
> fully-logged variety.
>
The cpu utilisation was extremely high on the Itanium, the majority of that
was kernel usage.
Changing the max degree of parallelism made no difference, nor did setting
offsets on the disk, nor sp4, adding numa options, setting affinity masks
or anything.
The bulk insert itself was a single 1.5GB file into a table with no
indexes. The db itself was in simple recovery mode, db and logs on seperate
luns on a Hitachi XP1024 SAN with a 40GB cache.
Performance speeds for the bulk insert were idnetical on both a Dell and HP
Itanium based system with the same specs.|||not being an expert in SQL optimizations, i`d recomend Opterons
in task w/ no parallelism (like games) opterons rule, and in smp system
unlike xeons each opteron has its own memory controller
"Nicholas Cain" <nicholas.cain@.nospam.t-mobile.com> wrote in message
news:Xns96C45EC16CAACnicholascainnospamtm@.207.46.248.16...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
> news:ulSIjWvrFHA.1168@.TK2MSFTNGP11.phx.gbl:
>> I've also seen high CPU utilization with bulk inserts, at least the
>> fully-logged variety.
> The cpu utilisation was extremely high on the Itanium, the majority of
> that
> was kernel usage.
> Changing the max degree of parallelism made no difference, nor did setting
> offsets on the disk, nor sp4, adding numa options, setting affinity masks
> or anything.
> The bulk insert itself was a single 1.5GB file into a table with no
> indexes. The db itself was in simple recovery mode, db and logs on
> seperate
> luns on a Hitachi XP1024 SAN with a 40GB cache.
> Performance speeds for the bulk insert were idnetical on both a Dell and
> HP
> Itanium based system with the same specs.|||i believe there were xeons vs opterons tests (w/ DB2 and MySQL) on
anandtech.com
"Nicholas Cain" <nicholas.cain@.nospam.t-mobile.com> wrote in message
news:Xns96C45EC16CAACnicholascainnospamtm@.207.46.248.16...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
> news:ulSIjWvrFHA.1168@.TK2MSFTNGP11.phx.gbl:
>> I've also seen high CPU utilization with bulk inserts, at least the
>> fully-logged variety.
> The cpu utilisation was extremely high on the Itanium, the majority of
> that
> was kernel usage.
> Changing the max degree of parallelism made no difference, nor did setting
> offsets on the disk, nor sp4, adding numa options, setting affinity masks
> or anything.
> The bulk insert itself was a single 1.5GB file into a table with no
> indexes. The db itself was in simple recovery mode, db and logs on
> seperate
> luns on a Hitachi XP1024 SAN with a 40GB cache.
> Performance speeds for the bulk insert were idnetical on both a Dell and
> HP
> Itanium based system with the same specs.|||hehe - me again
and u can get double core opterons - and have 2 cpu while paying licence for
1,
and there r IMHO no double core Xeons
"Nicholas Cain" <nicholas.cain@.nospam.t-mobile.com> wrote in message
news:Xns96C45EC16CAACnicholascainnospamtm@.207.46.248.16...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
> news:ulSIjWvrFHA.1168@.TK2MSFTNGP11.phx.gbl:
>> I've also seen high CPU utilization with bulk inserts, at least the
>> fully-logged variety.
> The cpu utilisation was extremely high on the Itanium, the majority of
> that
> was kernel usage.
> Changing the max degree of parallelism made no difference, nor did setting
> offsets on the disk, nor sp4, adding numa options, setting affinity masks
> or anything.
> The bulk insert itself was a single 1.5GB file into a table with no
> indexes. The db itself was in simple recovery mode, db and logs on
> seperate
> luns on a Hitachi XP1024 SAN with a 40GB cache.
> Performance speeds for the bulk insert were idnetical on both a Dell and
> HP
> Itanium based system with the same specs.|||I was acutally looking at the dual core.
I guess my best course of action would be to throw a Xeon and a Opteron in
a head to head and see what comes out as the leader.
"Coldman" <nomorespam@.mail.com> wrote in
news:#AAvQowrFHA.528@.TK2MSFTNGP09.phx.gbl:
> hehe - me again
> and u can get double core opterons - and have 2 cpu while paying
> licence for 1,
> and there r IMHO no double core Xeons
>|||good idea :)
y dont u post the results here after the test
"Nicholas Cain" <nicholas.cain@.nospam.t-mobile.com> wrote in message
news:Xns96C477204FE4Bnicholascainnospamtm@.207.46.248.16...
>I was acutally looking at the dual core.
> I guess my best course of action would be to throw a Xeon and a Opteron in
> a head to head and see what comes out as the leader.
>
> "Coldman" <nomorespam@.mail.com> wrote in
> news:#AAvQowrFHA.528@.TK2MSFTNGP09.phx.gbl:
>> hehe - me again
>> and u can get double core opterons - and have 2 cpu while paying
>> licence for 1,
>> and there r IMHO no double core Xeons
>|||If you are going to do that you should test a dual core Pentium against the
dual core Opteron. I have several clients with single core Opterons and
they are very happy with them but I think Dual Core processors will rule the
earth very soon<g>.
--
Andrew J. Kelly SQL MVP
"Nicholas Cain" <nicholas.cain@.nospam.t-mobile.com> wrote in message
news:Xns96C477204FE4Bnicholascainnospamtm@.207.46.248.16...
>I was acutally looking at the dual core.
> I guess my best course of action would be to throw a Xeon and a Opteron in
> a head to head and see what comes out as the leader.
>
> "Coldman" <nomorespam@.mail.com> wrote in
> news:#AAvQowrFHA.528@.TK2MSFTNGP09.phx.gbl:
>> hehe - me again
>> and u can get double core opterons - and have 2 cpu while paying
>> licence for 1,
>> and there r IMHO no double core Xeons
>|||but there r no dual core Xeons yet i think, and Pentium 4 lacks server class
motherboards(w/ a lot of 64bit slots and dual power connectors)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e9qbrgxrFHA.2272@.TK2MSFTNGP11.phx.gbl...
> If you are going to do that you should test a dual core Pentium against
> the dual core Opteron. I have several clients with single core Opterons
> and they are very happy with them but I think Dual Core processors will
> rule the earth very soon<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "Nicholas Cain" <nicholas.cain@.nospam.t-mobile.com> wrote in message
> news:Xns96C477204FE4Bnicholascainnospamtm@.207.46.248.16...
>>I was acutally looking at the dual core.
>> I guess my best course of action would be to throw a Xeon and a Opteron
>> in
>> a head to head and see what comes out as the leader.
>>
>> "Coldman" <nomorespam@.mail.com> wrote in
>> news:#AAvQowrFHA.528@.TK2MSFTNGP09.phx.gbl:
>> hehe - me again
>> and u can get double core opterons - and have 2 cpu while paying
>> licence for 1,
>> and there r IMHO no double core Xeons
>>
>

Monday, March 19, 2012

Optimising Query based on Views

Optimising Query based on Views

Optimising a query / stored procedure

Monday, March 12, 2012

Optimal configuration for report generator

Optimal configuration for report generator

Optimal configuration for report generator

Optimal configuration for report generator

Opteron vs Xeon

OpenXML Identity Insert

Blog Archive

About Me