I have some users running an application against a SQL 2k enterprise
database with 4 CPU's. When multiple users execute the same task in the
application at the same time, it appears from a duration standpoint that the
process is running serially against the database, where user #2's task
finishes after User #1, and User #3 finishes after User #2.
The configuration of the database has the following values:
name minimum maximum config_value
run_value
-- -- -- -- --
--
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 1 1
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 5 5
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 6079 6079
max text repl size (B) 0 2147483647 65536
65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 6079 6079
nested triggers 0 1 1 1
network packet size (B) 512 32767 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0
Is there anyway to prove that the queries are running in parallel rather than
serially?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1Robert R via SQLMonster.com wrote:
> I have some users running an application against a SQL 2k enterprise
> database with 4 CPU's. When multiple users execute the same task in
> the application at the same time, it appears from a duration
> standpoint that the process is running serially against the database,
> where user #2's task finishes after User #1, and User #3 finishes
> after User #2.
> <snip>
Sounds like you are having blocking issues. That is, one query is blocking
other queries and because of that they are executing in a serial fashion.
Check the execution plans of the queries in the "task" to make sure they are
properly using indexes and make sure you avoid long running transactions.
Feel free to post the table DDL, indexes, queries in question, and the
execution plans for each if you have any questions. You can check much of
this information from Profiler. Use the SQL:BatchCompleted and RPC:Completed
events for a high-level performance overview. If you want more detail add
SQL:StmtCompleted and SP:StmtCompleted events. Look over the cpu, duration,
and reads columns.
David Gugick
Quest Software|||This is a sample of one of the long running queries. Keep in mind the sample
I have included here is only about a quarter of the entire statement. Care to
explain how this might block other queries so that a newbie like me can
understand?
(
SELECT
ol.KY_OPINION_CD_ID AS KY_OPINION_CD_ID,
min(SKU_NOTE_2) AS SKU_NOTE_2,
min(SKU_NOTE) AS SKU_NOTE,
SKU_01 AS CURR_GROUP_CD,
SKU_01 AS CURR_GROUP_TEXT,
'SKU_01' AS GROUP_ORDER_CD,
COUNT(*) AS NUM_CHILDREN,
'2559946|SKU' AS AGGR_NAME,
2626210 AS AGGR_ID,
Min(cs.KY_SKU_ID) AS KY_SKU_ID,
Min(cs.KY_CUST_ID) as KY_CUST_ID,
min(CUST_CD) AS CUST_CD,
min(SKU_CD) AS SKU_CD,
min(BUS_UNIT_CD) AS BUS_UNIT_CD,
min(CUST_01) as CUST_01,
min(CUST_02) as CUST_02,
min(CUST_03) as CUST_03,
min(CUST_04) as CUST_04,
min(CUST_05) as CUST_05,
min(CUST_06) as CUST_06,
min(CUST_07) as CUST_07,
min(CUST_08) as CUST_08,
min(SKU_01) as SKU_01,
min(SKU_02) as SKU_02,
min(SKU_03) as SKU_03,
min(SKU_04) as SKU_04,
min(SKU_05) as SKU_05,
min(SKU_06) as SKU_06,
min(SKU_07) as SKU_07,
min(SKU_08) as SKU_08,
min(SKU_09) as SKU_09,
min(SKU_10) as SKU_10,
min(SKU_11) as SKU_11,
min(SKU_12) as SKU_12,
min(SKU_13) as SKU_13,
min(SKU_14) as SKU_14,
min(SKU_15) as SKU_15,
min(SKU_16) as SKU_16,
min(SKU_17) as SKU_17,
min(SKU_18) as SKU_18,
min(SKU_19) as SKU_19,
min(SKU_20) as SKU_20,
min(SKU_21) as SKU_21,
min(SKU_22) as SKU_22,
min(SKU_23) as SKU_23,
min(SKU_24) as SKU_24,
min(SKU_25) as SKU_25,
min(SKU_26) as SKU_26,
min(SKU_27) as SKU_27,
min(SKU_28) as SKU_28,
min(SKU_29) as SKU_29,
min(SKU_30) as SKU_30,
min(SKU_31) as SKU_31,
min(SKU_32) as SKU_32,
min(SKU_33) as SKU_33,
min(SKU_34) as SKU_34,
avg(SKU_AMT_01) as SKU_AMT_01,
avg(SKU_AMT_02) as SKU_AMT_02,
avg(SKU_AMT_03) as SKU_AMT_03,
min(SKU_AMT_04) as SKU_AMT_04,
avg(SKU_AMT_05) as SKU_AMT_05,
min(SKU_DATE_01) as SKU_DATE_01,
min(SKU_DATE_02) as SKU_DATE_02,
min(SKU_DATE_03) as SKU_DATE_03,
max(CUST_SKU_01) as CUST_SKU_01,
min(CUST_SKU_02) as CUST_SKU_02,
min(CUST_SKU_03) as CUST_SKU_03,
min(CUST_SKU_04) as CUST_SKU_04,
max(CUST_SKU_05) as CUST_SKU_05,
min(CUST_SKU_06) as CUST_SKU_06,
min(CUST_SKU_07) as CUST_SKU_07,
min(CUST_SKU_08) as CUST_SKU_08,
min(CUST_SKU_09) as CUST_SKU_09,
min(CUST_SKU_10) as CUST_SKU_10,
min(CUST_SKU_11) as CUST_SKU_11,
min(CUST_SKU_12) as CUST_SKU_12,
min(CUST_SKU_13) as CUST_SKU_13,
min(CUST_SKU_14) as CUST_SKU_14,
min(CUST_SKU_15) as CUST_SKU_15,
max(CUST_SKU_16) as CUST_SKU_16,
max(CUST_SKU_17) as CUST_SKU_17,
max(CUST_SKU_18) as CUST_SKU_18,
max(CUST_SKU_19) as CUST_SKU_19,
max(CUST_SKU_20) as CUST_SKU_20,
max(CUST_SKU_21) as CUST_SKU_21,
max(CUST_SKU_22) as CUST_SKU_22,
max(CUST_SKU_23) as CUST_SKU_23,
max(CUST_SKU_24) as CUST_SKU_24,
max(CUST_SKU_25) as CUST_SKU_25,
max(CUST_SKU_26) as CUST_SKU_26,
max(CUST_SKU_27) as CUST_SKU_27,
max(CUST_SKU_30) as CUST_SKU_30,
avg(CUST_SKU_AMT_01) as CUST_SKU_AMT_01,
avg(CUST_SKU_AMT_02) as CUST_SKU_AMT_02,
avg(CUST_SKU_AMT_03) as CUST_SKU_AMT_03,
avg(CUST_SKU_AMT_04) as CUST_SKU_AMT_04,
avg(CUST_SKU_AMT_05) as CUST_SKU_AMT_05,
avg(CUST_SKU_AMT_06) as CUST_SKU_AMT_06,
avg(CUST_SKU_AMT_07) as CUST_SKU_AMT_07,
avg(CUST_SKU_AMT_08) as CUST_SKU_AMT_08,
sum(CUST_SKU_AMT_09) as CUST_SKU_AMT_09,
sum(CUST_SKU_AMT_10) as CUST_SKU_AMT_10,
sum(CUST_SKU_AMT_11) as CUST_SKU_AMT_11,
sum(CUST_SKU_AMT_11*p1.OPINION_QTY_01) as CUST_SKU_AMT_12,
'UOM' AS MIN_UM,
'UOM' AS MAX_UM,
(SELECT COUNT(*) from tblNotes n with(rowlock)
WHERE n.KY_SKU_ID = min(cs.KY_SKU_ID)
and NOTE_TYPE IS NOT NULL) AS HAS_NOTE,
0 AS DIRTY_OP,
SPACE(100) AS UPDATE_FLDS,
(SUM(ol.OPINION_QTY_N12)) as OPINION_QTY_N12,
(SUM(ol.OPINION_QTY_N11)) as OPINION_QTY_N11,
(SUM(ol.OPINION_QTY_N10)) as OPINION_QTY_N10,
(SUM(ol.OPINION_QTY_N09)) as OPINION_QTY_N09,
(SUM(ol.OPINION_QTY_N08)) as OPINION_QTY_N08,
(SUM(ol.OPINION_QTY_N07)) as OPINION_QTY_N07,
(SUM(ol.OPINION_QTY_N06)) as OPINION_QTY_N06,
(SUM(ol.OPINION_QTY_N05)) as OPINION_QTY_N05,
(SUM(ol.OPINION_QTY_N04)) as OPINION_QTY_N04,
(SUM(ol.OPINION_QTY_N03)) as OPINION_QTY_N03,
(SUM(ol.OPINION_QTY_N02)) as OPINION_QTY_N02,
(SUM(ol.OPINION_QTY_N01)) as OPINION_QTY_N01,
(SUM(ol.OPINION_QTY_01)) as OPINION_QTY_01,
(SUM(ol.OPINION_QTY_02)) as OPINION_QTY_02,
(SUM(ol.OPINION_QTY_03)) as OPINION_QTY_03,
(SUM(ol.OPINION_QTY_04)) as OPINION_QTY_04,
(SUM(ol.OPINION_QTY_05)) as OPINION_QTY_05,
(SUM(ol.OPINION_QTY_06)) as OPINION_QTY_06,
(SUM(ol.OPINION_QTY_07)) as OPINION_QTY_07,
(SUM(ol.OPINION_QTY_08)) as OPINION_QTY_08,
(SUM(ol.OPINION_QTY_09)) as OPINION_QTY_09,
(SUM(ol.OPINION_QTY_10)) as OPINION_QTY_10,
(SUM(ol.OPINION_QTY_11)) as OPINION_QTY_11,
(SUM(ol.OPINION_QTY_12)) as OPINION_QTY_12,
(SUM(ol.OPINION_QTY_13)) as OPINION_QTY_13,
(SUM(ol.OPINION_QTY_14)) as OPINION_QTY_14,
(SUM(ol.OPINION_QTY_15)) as OPINION_QTY_15,
(SUM(ol.OPINION_QTY_16)) as OPINION_QTY_16,
(SUM(ol.OPINION_QTY_17)) as OPINION_QTY_17,
(SUM(ol.OPINION_QTY_18)) as OPINION_QTY_18,
(SUM(ol.OPINION_QTY_19)) as OPINION_QTY_19,
(SUM(ol.OPINION_QTY_20)) as OPINION_QTY_20,
(SUM(ol.OPINION_QTY_21)) as OPINION_QTY_21,
(SUM(ol.OPINION_QTY_22)) as OPINION_QTY_22,
(SUM(ol.OPINION_QTY_23)) as OPINION_QTY_23,
(SUM(ol.OPINION_QTY_24)) as OPINION_QTY_24,
(SUM(ol.OPINION_QTY_25)) as OPINION_QTY_25,
(SUM(ol.OPINION_QTY_26)) as OPINION_QTY_26,
(SUM(ol.OPINION_QTY_27)) as OPINION_QTY_27,
(SUM(ol.OPINION_QTY_28)) as OPINION_QTY_28,
(SUM(ol.OPINION_QTY_29)) as OPINION_QTY_29,
(SUM(ol.OPINION_QTY_30)) as OPINION_QTY_30,
(SUM(ol.OPINION_QTY_31)) as OPINION_QTY_31,
(SUM(ol.OPINION_QTY_32)) as OPINION_QTY_32,
(SUM(ol.OPINION_QTY_33)) as OPINION_QTY_33,
(SUM(ol.OPINION_QTY_34)) as OPINION_QTY_34,
(SUM(ol.OPINION_QTY_35)) as OPINION_QTY_35,
(SUM(ol.OPINION_QTY_36)) as OPINION_QTY_36,
(SUM(ol.OPINION_AMT_N24)) AS OPINION_AMT_N24,
(SUM(ol.OPINION_AMT_N23)) AS OPINION_AMT_N23,
(SUM(ol.OPINION_AMT_N22)) AS OPINION_AMT_N22,
(SUM(ol.OPINION_AMT_N21)) AS OPINION_AMT_N21,
(SUM(ol.OPINION_AMT_N20)) AS OPINION_AMT_N20,
(SUM(ol.OPINION_AMT_N19)) AS OPINION_AMT_N19,
(SUM(ol.OPINION_AMT_N18)) AS OPINION_AMT_N18,
(SUM(ol.OPINION_AMT_N17)) AS OPINION_AMT_N17,
(SUM(ol.OPINION_AMT_N16)) AS OPINION_AMT_N16,
(SUM(ol.OPINION_AMT_N15)) AS OPINION_AMT_N15,
(SUM(ol.OPINION_AMT_N14)) AS OPINION_AMT_N14,
(SUM(ol.OPINION_AMT_N13)) AS OPINION_AMT_N13,
(SUM(ol.OPINION_AMT_N12)) AS OPINION_AMT_N12,
(SUM(ol.OPINION_AMT_N11)) AS OPINION_AMT_N11,
(SUM(ol.OPINION_AMT_N10)) AS OPINION_AMT_N10,
(SUM(ol.OPINION_AMT_N09)) AS OPINION_AMT_N09,
(SUM(ol.OPINION_AMT_N08)) AS OPINION_AMT_N08,
(SUM(ol.OPINION_AMT_N07)) AS OPINION_AMT_N07,
(SUM(ol.OPINION_AMT_N06)) AS OPINION_AMT_N06,
(SUM(ol.OPINION_AMT_N05)) AS OPINION_AMT_N05,
(SUM(ol.OPINION_AMT_N04)) AS OPINION_AMT_N04,
(SUM(ol.OPINION_AMT_N03)) AS OPINION_AMT_N03,
(SUM(ol.OPINION_AMT_N02)) AS OPINION_AMT_N02,
(SUM(ol.OPINION_AMT_N01)) AS OPINION_AMT_N01,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_01*p3.OPINION_QTY_01)
else 0 end) AS OPINION_AMT_01,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_02*p3.OPINION_QTY_02)
else 0 end) AS OPINION_AMT_02,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_03*p3.OPINION_QTY_03)
else 0 end) AS OPINION_AMT_03,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_04*p3.OPINION_QTY_04)
else 0 end) AS OPINION_AMT_04,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_05*p3.OPINION_QTY_05)
else 0 end) AS OPINION_AMT_05,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_06*p3.OPINION_QTY_06)
else 0 end) AS OPINION_AMT_06,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_07*p3.OPINION_QTY_07)
else 0 end) AS OPINION_AMT_07,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_08*p3.OPINION_QTY_08)
else 0 end) AS OPINION_AMT_08,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_09*p3.OPINION_QTY_09)
else 0 end) AS OPINION_AMT_09,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_10*p3.OPINION_QTY_10)
else 0 end) AS OPINION_AMT_10,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_11*p3.OPINION_QTY_11)
else 0 end) AS OPINION_AMT_11,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_12*p3.OPINION_QTY_12)
else 0 end) AS OPINION_AMT_12,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_13*p3.OPINION_QTY_13)
else 0 end) AS OPINION_AMT_13,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_14*p3.OPINION_QTY_14)
else 0 end) AS OPINION_AMT_14,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_15*p3.OPINION_QTY_15)
else 0 end) AS OPINION_AMT_15,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_16*p3.OPINION_QTY_16)
else 0 end) AS OPINION_AMT_16,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_17*p3.OPINION_QTY_17)
else 0 end) AS OPINION_AMT_17,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_18*p3.OPINION_QTY_18)
else 0 end) AS OPINION_AMT_18,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_19*p3.OPINION_QTY_19)
else 0 end) AS OPINION_AMT_19,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_20*p3.OPINION_QTY_20)
else 0 end) AS OPINION_AMT_20,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_21*p3.OPINION_QTY_21)
else 0 end) AS OPINION_AMT_21,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_22*p3.OPINION_QTY_22)
else 0 end) AS OPINION_AMT_22,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_23*p3.OPINION_QTY_23)
else 0 end) AS OPINION_AMT_23,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_24*p3.OPINION_QTY_24)
else 0 end) AS OPINION_AMT_24,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_25*p3.OPINION_QTY_25)
else 0 end) AS OPINION_AMT_25,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_26*p3.OPINION_QTY_26)
else 0 end) AS OPINION_AMT_26,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_27*p3.OPINION_QTY_27)
else 0 end) AS OPINION_AMT_27,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_28*p3.OPINION_QTY_28)
else 0 end) AS OPINION_AMT_28,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_29*p3.OPINION_QTY_29)
else 0 end) AS OPINION_AMT_29,
case ...
from ...
where ...
David Gugick wrote:
>> I have some users running an application against a SQL 2k enterprise
>> database with 4 CPU's. When multiple users execute the same task in
>[quoted text clipped - 4 lines]
>> <snip>
>Sounds like you are having blocking issues. That is, one query is blocking
>other queries and because of that they are executing in a serial fashion.
>Check the execution plans of the queries in the "task" to make sure they are
>properly using indexes and make sure you avoid long running transactions.
>Feel free to post the table DDL, indexes, queries in question, and the
>execution plans for each if you have any questions. You can check much of
>this information from Profiler. Use the SQL:BatchCompleted and RPC:Completed
>events for a high-level performance overview. If you want more detail add
>SQL:StmtCompleted and SP:StmtCompleted events. Look over the cpu, duration,
>and reads columns.
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1|||Robert R via SQLMonster.com wrote:
> This is a sample of one of the long running queries. Keep in mind the
> sample I have included here is only about a quarter of the entire
> statement. Care to explain how this might block other queries so that
> a newbie like me can understand?
> <snip>
Where did that SQL statement come from? It looks as though your table is
denormalized. I see a SKU_1 through SKU_34 that appear to be columns in
the table. If that's true, table design issues aside, that's an
extremely large and complex query that could very well be accessing the
entire table each time it's executed and / or causing extremely high IO
load on your disk subsystem and / or causing high CPU. You are also
returning a SPACE(100) as a column which is a little strange.
Denormalized tables cause large row sizes also very low row density
which means a lot of page reads. Excessive page reads mean a lot of
physical IO which is slow. A lot of aggregations means higher CPU
utilization. All of these items can translate in to slow executing
queries, long transaction times, extended locks that cause blocking,
high disk IO, hign CPU, etc.
So, first we need to know if your table really looks like what I see in
the query. If so, the answers here will likely point to the table design
as the primary problem. It would be impossible for anyone here to
recommend a solution for the query itself without knowing exactly what
the tables look like, what indexes are in place, what you're hoping to
return from the query, and a complete query with parameters/bind
variables.
Sorry I can't be more help. THe best thing to do is take a step back and
make a new post with all the relevant information.
David Gugick
Quest Software
www.quest.com
Showing posts with label cpu. Show all posts
Showing posts with label cpu. Show all posts
Wednesday, March 21, 2012
Queries in parallel
I have some users running an application against a SQL 2k enterprise
database with 4 CPU's. When multiple users execute the same task in the
application at the same time, it appears from a duration standpoint that the
process is running serially against the database, where user #2's task
finishes after User #1, and User #3 finishes after User #2.
The configuration of the database has the following values:
name minimum maximum config_value
run_value
-- -- -- -- --
--
--
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 1 1
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 5 5
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 103
3
default language 0 9999 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 6079 607
9
max text repl size (B) 0 2147483647 65536
65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 102
4
min server memory (MB) 0 2147483647 6079 607
9
nested triggers 0 1 1 1
network packet size (B) 512 32767 4096 409
6
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 204
9
user connections 0 32767 0 0
user options 0 32767 0 0
Is there anyway to prove that the queries are running in parallel rather tha
n
serially?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1Robert R via droptable.com wrote:
> I have some users running an application against a SQL 2k enterprise
> database with 4 CPU's. When multiple users execute the same task in
> the application at the same time, it appears from a duration
> standpoint that the process is running serially against the database,
> where user #2's task finishes after User #1, and User #3 finishes
> after User #2.
> <snip>
Sounds like you are having blocking issues. That is, one query is blocking
other queries and because of that they are executing in a serial fashion.
Check the execution plans of the queries in the "task" to make sure they are
properly using indexes and make sure you avoid long running transactions.
Feel free to post the table DDL, indexes, queries in question, and the
execution plans for each if you have any questions. You can check much of
this information from Profiler. Use the SQL:BatchCompleted and RPC:Completed
events for a high-level performance overview. If you want more detail add
SQL:StmtCompleted and SP:StmtCompleted events. Look over the cpu, duration,
and reads columns.
David Gugick
Quest Software|||This is a sample of one of the long running queries. Keep in mind the sample
I have included here is only about a quarter of the entire statement. Care t
o
explain how this might block other queries so that a newbie like me can
understand?
(
SELECT
ol.KY_OPINION_CD_ID AS KY_OPINION_CD_ID,
min(SKU_NOTE_2) AS SKU_NOTE_2,
min(SKU_NOTE) AS SKU_NOTE,
SKU_01 AS CURR_GROUP_CD,
SKU_01 AS CURR_GROUP_TEXT,
'SKU_01' AS GROUP_ORDER_CD,
COUNT(*) AS NUM_CHILDREN,
'2559946|SKU' AS AGGR_NAME,
2626210 AS AGGR_ID,
Min(cs.KY_SKU_ID) AS KY_SKU_ID,
Min(cs.KY_CUST_ID) as KY_CUST_ID,
min(CUST_CD) AS CUST_CD,
min(SKU_CD) AS SKU_CD,
min(BUS_UNIT_CD) AS BUS_UNIT_CD,
min(CUST_01) as CUST_01,
min(CUST_02) as CUST_02,
min(CUST_03) as CUST_03,
min(CUST_04) as CUST_04,
min(CUST_05) as CUST_05,
min(CUST_06) as CUST_06,
min(CUST_07) as CUST_07,
min(CUST_08) as CUST_08,
min(SKU_01) as SKU_01,
min(SKU_02) as SKU_02,
min(SKU_03) as SKU_03,
min(SKU_04) as SKU_04,
min(SKU_05) as SKU_05,
min(SKU_06) as SKU_06,
min(SKU_07) as SKU_07,
min(SKU_08) as SKU_08,
min(SKU_09) as SKU_09,
min(SKU_10) as SKU_10,
min(SKU_11) as SKU_11,
min(SKU_12) as SKU_12,
min(SKU_13) as SKU_13,
min(SKU_14) as SKU_14,
min(SKU_15) as SKU_15,
min(SKU_16) as SKU_16,
min(SKU_17) as SKU_17,
min(SKU_18) as SKU_18,
min(SKU_19) as SKU_19,
min(SKU_20) as SKU_20,
min(SKU_21) as SKU_21,
min(SKU_22) as SKU_22,
min(SKU_23) as SKU_23,
min(SKU_24) as SKU_24,
min(SKU_25) as SKU_25,
min(SKU_26) as SKU_26,
min(SKU_27) as SKU_27,
min(SKU_28) as SKU_28,
min(SKU_29) as SKU_29,
min(SKU_30) as SKU_30,
min(SKU_31) as SKU_31,
min(SKU_32) as SKU_32,
min(SKU_33) as SKU_33,
min(SKU_34) as SKU_34,
avg(SKU_AMT_01) as SKU_AMT_01,
avg(SKU_AMT_02) as SKU_AMT_02,
avg(SKU_AMT_03) as SKU_AMT_03,
min(SKU_AMT_04) as SKU_AMT_04,
avg(SKU_AMT_05) as SKU_AMT_05,
min(SKU_DATE_01) as SKU_DATE_01,
min(SKU_DATE_02) as SKU_DATE_02,
min(SKU_DATE_03) as SKU_DATE_03,
max(CUST_SKU_01) as CUST_SKU_01,
min(CUST_SKU_02) as CUST_SKU_02,
min(CUST_SKU_03) as CUST_SKU_03,
min(CUST_SKU_04) as CUST_SKU_04,
max(CUST_SKU_05) as CUST_SKU_05,
min(CUST_SKU_06) as CUST_SKU_06,
min(CUST_SKU_07) as CUST_SKU_07,
min(CUST_SKU_08) as CUST_SKU_08,
min(CUST_SKU_09) as CUST_SKU_09,
min(CUST_SKU_10) as CUST_SKU_10,
min(CUST_SKU_11) as CUST_SKU_11,
min(CUST_SKU_12) as CUST_SKU_12,
min(CUST_SKU_13) as CUST_SKU_13,
min(CUST_SKU_14) as CUST_SKU_14,
min(CUST_SKU_15) as CUST_SKU_15,
max(CUST_SKU_16) as CUST_SKU_16,
max(CUST_SKU_17) as CUST_SKU_17,
max(CUST_SKU_18) as CUST_SKU_18,
max(CUST_SKU_19) as CUST_SKU_19,
max(CUST_SKU_20) as CUST_SKU_20,
max(CUST_SKU_21) as CUST_SKU_21,
max(CUST_SKU_22) as CUST_SKU_22,
max(CUST_SKU_23) as CUST_SKU_23,
max(CUST_SKU_24) as CUST_SKU_24,
max(CUST_SKU_25) as CUST_SKU_25,
max(CUST_SKU_26) as CUST_SKU_26,
max(CUST_SKU_27) as CUST_SKU_27,
max(CUST_SKU_30) as CUST_SKU_30,
avg(CUST_SKU_AMT_01) as CUST_SKU_AMT_01,
avg(CUST_SKU_AMT_02) as CUST_SKU_AMT_02,
avg(CUST_SKU_AMT_03) as CUST_SKU_AMT_03,
avg(CUST_SKU_AMT_04) as CUST_SKU_AMT_04,
avg(CUST_SKU_AMT_05) as CUST_SKU_AMT_05,
avg(CUST_SKU_AMT_06) as CUST_SKU_AMT_06,
avg(CUST_SKU_AMT_07) as CUST_SKU_AMT_07,
avg(CUST_SKU_AMT_08) as CUST_SKU_AMT_08,
sum(CUST_SKU_AMT_09) as CUST_SKU_AMT_09,
sum(CUST_SKU_AMT_10) as CUST_SKU_AMT_10,
sum(CUST_SKU_AMT_11) as CUST_SKU_AMT_11,
sum(CUST_SKU_AMT_11*p1.OPINION_QTY_01) as CUST_SKU_AMT_12,
'UOM' AS MIN_UM,
'UOM' AS MAX_UM,
(SELECT COUNT(*) from tblNotes n with(rowlock)
WHERE n.KY_SKU_ID = min(cs.KY_SKU_ID)
and NOTE_TYPE IS NOT NULL) AS HAS_NOTE,
0 AS DIRTY_OP,
SPACE(100) AS UPDATE_FLDS,
(SUM(ol.OPINION_QTY_N12)) as OPINION_QTY_N12,
(SUM(ol.OPINION_QTY_N11)) as OPINION_QTY_N11,
(SUM(ol.OPINION_QTY_N10)) as OPINION_QTY_N10,
(SUM(ol.OPINION_QTY_N09)) as OPINION_QTY_N09,
(SUM(ol.OPINION_QTY_N08)) as OPINION_QTY_N08,
(SUM(ol.OPINION_QTY_N07)) as OPINION_QTY_N07,
(SUM(ol.OPINION_QTY_N06)) as OPINION_QTY_N06,
(SUM(ol.OPINION_QTY_N05)) as OPINION_QTY_N05,
(SUM(ol.OPINION_QTY_N04)) as OPINION_QTY_N04,
(SUM(ol.OPINION_QTY_N03)) as OPINION_QTY_N03,
(SUM(ol.OPINION_QTY_N02)) as OPINION_QTY_N02,
(SUM(ol.OPINION_QTY_N01)) as OPINION_QTY_N01,
(SUM(ol.OPINION_QTY_01)) as OPINION_QTY_01,
(SUM(ol.OPINION_QTY_02)) as OPINION_QTY_02,
(SUM(ol.OPINION_QTY_03)) as OPINION_QTY_03,
(SUM(ol.OPINION_QTY_04)) as OPINION_QTY_04,
(SUM(ol.OPINION_QTY_05)) as OPINION_QTY_05,
(SUM(ol.OPINION_QTY_06)) as OPINION_QTY_06,
(SUM(ol.OPINION_QTY_07)) as OPINION_QTY_07,
(SUM(ol.OPINION_QTY_08)) as OPINION_QTY_08,
(SUM(ol.OPINION_QTY_09)) as OPINION_QTY_09,
(SUM(ol.OPINION_QTY_10)) as OPINION_QTY_10,
(SUM(ol.OPINION_QTY_11)) as OPINION_QTY_11,
(SUM(ol.OPINION_QTY_12)) as OPINION_QTY_12,
(SUM(ol.OPINION_QTY_13)) as OPINION_QTY_13,
(SUM(ol.OPINION_QTY_14)) as OPINION_QTY_14,
(SUM(ol.OPINION_QTY_15)) as OPINION_QTY_15,
(SUM(ol.OPINION_QTY_16)) as OPINION_QTY_16,
(SUM(ol.OPINION_QTY_17)) as OPINION_QTY_17,
(SUM(ol.OPINION_QTY_18)) as OPINION_QTY_18,
(SUM(ol.OPINION_QTY_19)) as OPINION_QTY_19,
(SUM(ol.OPINION_QTY_20)) as OPINION_QTY_20,
(SUM(ol.OPINION_QTY_21)) as OPINION_QTY_21,
(SUM(ol.OPINION_QTY_22)) as OPINION_QTY_22,
(SUM(ol.OPINION_QTY_23)) as OPINION_QTY_23,
(SUM(ol.OPINION_QTY_24)) as OPINION_QTY_24,
(SUM(ol.OPINION_QTY_25)) as OPINION_QTY_25,
(SUM(ol.OPINION_QTY_26)) as OPINION_QTY_26,
(SUM(ol.OPINION_QTY_27)) as OPINION_QTY_27,
(SUM(ol.OPINION_QTY_28)) as OPINION_QTY_28,
(SUM(ol.OPINION_QTY_29)) as OPINION_QTY_29,
(SUM(ol.OPINION_QTY_30)) as OPINION_QTY_30,
(SUM(ol.OPINION_QTY_31)) as OPINION_QTY_31,
(SUM(ol.OPINION_QTY_32)) as OPINION_QTY_32,
(SUM(ol.OPINION_QTY_33)) as OPINION_QTY_33,
(SUM(ol.OPINION_QTY_34)) as OPINION_QTY_34,
(SUM(ol.OPINION_QTY_35)) as OPINION_QTY_35,
(SUM(ol.OPINION_QTY_36)) as OPINION_QTY_36,
(SUM(ol.OPINION_AMT_N24)) AS OPINION_AMT_N24,
(SUM(ol.OPINION_AMT_N23)) AS OPINION_AMT_N23,
(SUM(ol.OPINION_AMT_N22)) AS OPINION_AMT_N22,
(SUM(ol.OPINION_AMT_N21)) AS OPINION_AMT_N21,
(SUM(ol.OPINION_AMT_N20)) AS OPINION_AMT_N20,
(SUM(ol.OPINION_AMT_N19)) AS OPINION_AMT_N19,
(SUM(ol.OPINION_AMT_N18)) AS OPINION_AMT_N18,
(SUM(ol.OPINION_AMT_N17)) AS OPINION_AMT_N17,
(SUM(ol.OPINION_AMT_N16)) AS OPINION_AMT_N16,
(SUM(ol.OPINION_AMT_N15)) AS OPINION_AMT_N15,
(SUM(ol.OPINION_AMT_N14)) AS OPINION_AMT_N14,
(SUM(ol.OPINION_AMT_N13)) AS OPINION_AMT_N13,
(SUM(ol.OPINION_AMT_N12)) AS OPINION_AMT_N12,
(SUM(ol.OPINION_AMT_N11)) AS OPINION_AMT_N11,
(SUM(ol.OPINION_AMT_N10)) AS OPINION_AMT_N10,
(SUM(ol.OPINION_AMT_N09)) AS OPINION_AMT_N09,
(SUM(ol.OPINION_AMT_N08)) AS OPINION_AMT_N08,
(SUM(ol.OPINION_AMT_N07)) AS OPINION_AMT_N07,
(SUM(ol.OPINION_AMT_N06)) AS OPINION_AMT_N06,
(SUM(ol.OPINION_AMT_N05)) AS OPINION_AMT_N05,
(SUM(ol.OPINION_AMT_N04)) AS OPINION_AMT_N04,
(SUM(ol.OPINION_AMT_N03)) AS OPINION_AMT_N03,
(SUM(ol.OPINION_AMT_N02)) AS OPINION_AMT_N02,
(SUM(ol.OPINION_AMT_N01)) AS OPINION_AMT_N01,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_01*p3.OPINION_QTY_01)
else 0 end) AS OPINION_AMT_01,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_02*p3.OPINION_QTY_02)
else 0 end) AS OPINION_AMT_02,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_03*p3.OPINION_QTY_03)
else 0 end) AS OPINION_AMT_03,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_04*p3.OPINION_QTY_04)
else 0 end) AS OPINION_AMT_04,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_05*p3.OPINION_QTY_05)
else 0 end) AS OPINION_AMT_05,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_06*p3.OPINION_QTY_06)
else 0 end) AS OPINION_AMT_06,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_07*p3.OPINION_QTY_07)
else 0 end) AS OPINION_AMT_07,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_08*p3.OPINION_QTY_08)
else 0 end) AS OPINION_AMT_08,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_09*p3.OPINION_QTY_09)
else 0 end) AS OPINION_AMT_09,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_10*p3.OPINION_QTY_10)
else 0 end) AS OPINION_AMT_10,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_11*p3.OPINION_QTY_11)
else 0 end) AS OPINION_AMT_11,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_12*p3.OPINION_QTY_12)
else 0 end) AS OPINION_AMT_12,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_13*p3.OPINION_QTY_13)
else 0 end) AS OPINION_AMT_13,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_14*p3.OPINION_QTY_14)
else 0 end) AS OPINION_AMT_14,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_15*p3.OPINION_QTY_15)
else 0 end) AS OPINION_AMT_15,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_16*p3.OPINION_QTY_16)
else 0 end) AS OPINION_AMT_16,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_17*p3.OPINION_QTY_17)
else 0 end) AS OPINION_AMT_17,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_18*p3.OPINION_QTY_18)
else 0 end) AS OPINION_AMT_18,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_19*p3.OPINION_QTY_19)
else 0 end) AS OPINION_AMT_19,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_20*p3.OPINION_QTY_20)
else 0 end) AS OPINION_AMT_20,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_21*p3.OPINION_QTY_21)
else 0 end) AS OPINION_AMT_21,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_22*p3.OPINION_QTY_22)
else 0 end) AS OPINION_AMT_22,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_23*p3.OPINION_QTY_23)
else 0 end) AS OPINION_AMT_23,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_24*p3.OPINION_QTY_24)
else 0 end) AS OPINION_AMT_24,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_25*p3.OPINION_QTY_25)
else 0 end) AS OPINION_AMT_25,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_26*p3.OPINION_QTY_26)
else 0 end) AS OPINION_AMT_26,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_27*p3.OPINION_QTY_27)
else 0 end) AS OPINION_AMT_27,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_28*p3.OPINION_QTY_28)
else 0 end) AS OPINION_AMT_28,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_29*p3.OPINION_QTY_29)
else 0 end) AS OPINION_AMT_29,
case ...
from ...
where ...
David Gugick wrote:
>[quoted text clipped - 4 lines]
>Sounds like you are having blocking issues. That is, one query is blocking
>other queries and because of that they are executing in a serial fashion.
>Check the execution plans of the queries in the "task" to make sure they ar
e
>properly using indexes and make sure you avoid long running transactions.
>Feel free to post the table DDL, indexes, queries in question, and the
>execution plans for each if you have any questions. You can check much of
>this information from Profiler. Use the SQL:BatchCompleted and RPC:Complete
d
>events for a high-level performance overview. If you want more detail add
>SQL:StmtCompleted and SP:StmtCompleted events. Look over the cpu, duration,
>and reads columns.
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1|||Robert R via droptable.com wrote:
> This is a sample of one of the long running queries. Keep in mind the
> sample I have included here is only about a quarter of the entire
> statement. Care to explain how this might block other queries so that
> a newbie like me can understand?
> <snip>
Where did that SQL statement come from? It looks as though your table is
denormalized. I see a SKU_1 through SKU_34 that appear to be columns in
the table. If that's true, table design issues aside, that's an
extremely large and complex query that could very well be accessing the
entire table each time it's executed and / or causing extremely high IO
load on your disk subsystem and / or causing high CPU. You are also
returning a SPACE(100) as a column which is a little strange.
Denormalized tables cause large row sizes also very low row density
which means a lot of page reads. Excessive page reads mean a lot of
physical IO which is slow. A lot of aggregations means higher CPU
utilization. All of these items can translate in to slow executing
queries, long transaction times, extended locks that cause blocking,
high disk IO, hign CPU, etc.
So, first we need to know if your table really looks like what I see in
the query. If so, the answers here will likely point to the table design
as the primary problem. It would be impossible for anyone here to
recommend a solution for the query itself without knowing exactly what
the tables look like, what indexes are in place, what you're hoping to
return from the query, and a complete query with parameters/bind
variables.
Sorry I can't be more help. THe best thing to do is take a step back and
make a new post with all the relevant information.
David Gugick
Quest Software
www.quest.com
database with 4 CPU's. When multiple users execute the same task in the
application at the same time, it appears from a duration standpoint that the
process is running serially against the database, where user #2's task
finishes after User #1, and User #3 finishes after User #2.
The configuration of the database has the following values:
name minimum maximum config_value
run_value
-- -- -- -- --
--
--
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 1 1
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 5 5
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 103
3
default language 0 9999 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 6079 607
9
max text repl size (B) 0 2147483647 65536
65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 102
4
min server memory (MB) 0 2147483647 6079 607
9
nested triggers 0 1 1 1
network packet size (B) 512 32767 4096 409
6
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 204
9
user connections 0 32767 0 0
user options 0 32767 0 0
Is there anyway to prove that the queries are running in parallel rather tha
n
serially?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1Robert R via droptable.com wrote:
> I have some users running an application against a SQL 2k enterprise
> database with 4 CPU's. When multiple users execute the same task in
> the application at the same time, it appears from a duration
> standpoint that the process is running serially against the database,
> where user #2's task finishes after User #1, and User #3 finishes
> after User #2.
> <snip>
Sounds like you are having blocking issues. That is, one query is blocking
other queries and because of that they are executing in a serial fashion.
Check the execution plans of the queries in the "task" to make sure they are
properly using indexes and make sure you avoid long running transactions.
Feel free to post the table DDL, indexes, queries in question, and the
execution plans for each if you have any questions. You can check much of
this information from Profiler. Use the SQL:BatchCompleted and RPC:Completed
events for a high-level performance overview. If you want more detail add
SQL:StmtCompleted and SP:StmtCompleted events. Look over the cpu, duration,
and reads columns.
David Gugick
Quest Software|||This is a sample of one of the long running queries. Keep in mind the sample
I have included here is only about a quarter of the entire statement. Care t
o
explain how this might block other queries so that a newbie like me can
understand?
(
SELECT
ol.KY_OPINION_CD_ID AS KY_OPINION_CD_ID,
min(SKU_NOTE_2) AS SKU_NOTE_2,
min(SKU_NOTE) AS SKU_NOTE,
SKU_01 AS CURR_GROUP_CD,
SKU_01 AS CURR_GROUP_TEXT,
'SKU_01' AS GROUP_ORDER_CD,
COUNT(*) AS NUM_CHILDREN,
'2559946|SKU' AS AGGR_NAME,
2626210 AS AGGR_ID,
Min(cs.KY_SKU_ID) AS KY_SKU_ID,
Min(cs.KY_CUST_ID) as KY_CUST_ID,
min(CUST_CD) AS CUST_CD,
min(SKU_CD) AS SKU_CD,
min(BUS_UNIT_CD) AS BUS_UNIT_CD,
min(CUST_01) as CUST_01,
min(CUST_02) as CUST_02,
min(CUST_03) as CUST_03,
min(CUST_04) as CUST_04,
min(CUST_05) as CUST_05,
min(CUST_06) as CUST_06,
min(CUST_07) as CUST_07,
min(CUST_08) as CUST_08,
min(SKU_01) as SKU_01,
min(SKU_02) as SKU_02,
min(SKU_03) as SKU_03,
min(SKU_04) as SKU_04,
min(SKU_05) as SKU_05,
min(SKU_06) as SKU_06,
min(SKU_07) as SKU_07,
min(SKU_08) as SKU_08,
min(SKU_09) as SKU_09,
min(SKU_10) as SKU_10,
min(SKU_11) as SKU_11,
min(SKU_12) as SKU_12,
min(SKU_13) as SKU_13,
min(SKU_14) as SKU_14,
min(SKU_15) as SKU_15,
min(SKU_16) as SKU_16,
min(SKU_17) as SKU_17,
min(SKU_18) as SKU_18,
min(SKU_19) as SKU_19,
min(SKU_20) as SKU_20,
min(SKU_21) as SKU_21,
min(SKU_22) as SKU_22,
min(SKU_23) as SKU_23,
min(SKU_24) as SKU_24,
min(SKU_25) as SKU_25,
min(SKU_26) as SKU_26,
min(SKU_27) as SKU_27,
min(SKU_28) as SKU_28,
min(SKU_29) as SKU_29,
min(SKU_30) as SKU_30,
min(SKU_31) as SKU_31,
min(SKU_32) as SKU_32,
min(SKU_33) as SKU_33,
min(SKU_34) as SKU_34,
avg(SKU_AMT_01) as SKU_AMT_01,
avg(SKU_AMT_02) as SKU_AMT_02,
avg(SKU_AMT_03) as SKU_AMT_03,
min(SKU_AMT_04) as SKU_AMT_04,
avg(SKU_AMT_05) as SKU_AMT_05,
min(SKU_DATE_01) as SKU_DATE_01,
min(SKU_DATE_02) as SKU_DATE_02,
min(SKU_DATE_03) as SKU_DATE_03,
max(CUST_SKU_01) as CUST_SKU_01,
min(CUST_SKU_02) as CUST_SKU_02,
min(CUST_SKU_03) as CUST_SKU_03,
min(CUST_SKU_04) as CUST_SKU_04,
max(CUST_SKU_05) as CUST_SKU_05,
min(CUST_SKU_06) as CUST_SKU_06,
min(CUST_SKU_07) as CUST_SKU_07,
min(CUST_SKU_08) as CUST_SKU_08,
min(CUST_SKU_09) as CUST_SKU_09,
min(CUST_SKU_10) as CUST_SKU_10,
min(CUST_SKU_11) as CUST_SKU_11,
min(CUST_SKU_12) as CUST_SKU_12,
min(CUST_SKU_13) as CUST_SKU_13,
min(CUST_SKU_14) as CUST_SKU_14,
min(CUST_SKU_15) as CUST_SKU_15,
max(CUST_SKU_16) as CUST_SKU_16,
max(CUST_SKU_17) as CUST_SKU_17,
max(CUST_SKU_18) as CUST_SKU_18,
max(CUST_SKU_19) as CUST_SKU_19,
max(CUST_SKU_20) as CUST_SKU_20,
max(CUST_SKU_21) as CUST_SKU_21,
max(CUST_SKU_22) as CUST_SKU_22,
max(CUST_SKU_23) as CUST_SKU_23,
max(CUST_SKU_24) as CUST_SKU_24,
max(CUST_SKU_25) as CUST_SKU_25,
max(CUST_SKU_26) as CUST_SKU_26,
max(CUST_SKU_27) as CUST_SKU_27,
max(CUST_SKU_30) as CUST_SKU_30,
avg(CUST_SKU_AMT_01) as CUST_SKU_AMT_01,
avg(CUST_SKU_AMT_02) as CUST_SKU_AMT_02,
avg(CUST_SKU_AMT_03) as CUST_SKU_AMT_03,
avg(CUST_SKU_AMT_04) as CUST_SKU_AMT_04,
avg(CUST_SKU_AMT_05) as CUST_SKU_AMT_05,
avg(CUST_SKU_AMT_06) as CUST_SKU_AMT_06,
avg(CUST_SKU_AMT_07) as CUST_SKU_AMT_07,
avg(CUST_SKU_AMT_08) as CUST_SKU_AMT_08,
sum(CUST_SKU_AMT_09) as CUST_SKU_AMT_09,
sum(CUST_SKU_AMT_10) as CUST_SKU_AMT_10,
sum(CUST_SKU_AMT_11) as CUST_SKU_AMT_11,
sum(CUST_SKU_AMT_11*p1.OPINION_QTY_01) as CUST_SKU_AMT_12,
'UOM' AS MIN_UM,
'UOM' AS MAX_UM,
(SELECT COUNT(*) from tblNotes n with(rowlock)
WHERE n.KY_SKU_ID = min(cs.KY_SKU_ID)
and NOTE_TYPE IS NOT NULL) AS HAS_NOTE,
0 AS DIRTY_OP,
SPACE(100) AS UPDATE_FLDS,
(SUM(ol.OPINION_QTY_N12)) as OPINION_QTY_N12,
(SUM(ol.OPINION_QTY_N11)) as OPINION_QTY_N11,
(SUM(ol.OPINION_QTY_N10)) as OPINION_QTY_N10,
(SUM(ol.OPINION_QTY_N09)) as OPINION_QTY_N09,
(SUM(ol.OPINION_QTY_N08)) as OPINION_QTY_N08,
(SUM(ol.OPINION_QTY_N07)) as OPINION_QTY_N07,
(SUM(ol.OPINION_QTY_N06)) as OPINION_QTY_N06,
(SUM(ol.OPINION_QTY_N05)) as OPINION_QTY_N05,
(SUM(ol.OPINION_QTY_N04)) as OPINION_QTY_N04,
(SUM(ol.OPINION_QTY_N03)) as OPINION_QTY_N03,
(SUM(ol.OPINION_QTY_N02)) as OPINION_QTY_N02,
(SUM(ol.OPINION_QTY_N01)) as OPINION_QTY_N01,
(SUM(ol.OPINION_QTY_01)) as OPINION_QTY_01,
(SUM(ol.OPINION_QTY_02)) as OPINION_QTY_02,
(SUM(ol.OPINION_QTY_03)) as OPINION_QTY_03,
(SUM(ol.OPINION_QTY_04)) as OPINION_QTY_04,
(SUM(ol.OPINION_QTY_05)) as OPINION_QTY_05,
(SUM(ol.OPINION_QTY_06)) as OPINION_QTY_06,
(SUM(ol.OPINION_QTY_07)) as OPINION_QTY_07,
(SUM(ol.OPINION_QTY_08)) as OPINION_QTY_08,
(SUM(ol.OPINION_QTY_09)) as OPINION_QTY_09,
(SUM(ol.OPINION_QTY_10)) as OPINION_QTY_10,
(SUM(ol.OPINION_QTY_11)) as OPINION_QTY_11,
(SUM(ol.OPINION_QTY_12)) as OPINION_QTY_12,
(SUM(ol.OPINION_QTY_13)) as OPINION_QTY_13,
(SUM(ol.OPINION_QTY_14)) as OPINION_QTY_14,
(SUM(ol.OPINION_QTY_15)) as OPINION_QTY_15,
(SUM(ol.OPINION_QTY_16)) as OPINION_QTY_16,
(SUM(ol.OPINION_QTY_17)) as OPINION_QTY_17,
(SUM(ol.OPINION_QTY_18)) as OPINION_QTY_18,
(SUM(ol.OPINION_QTY_19)) as OPINION_QTY_19,
(SUM(ol.OPINION_QTY_20)) as OPINION_QTY_20,
(SUM(ol.OPINION_QTY_21)) as OPINION_QTY_21,
(SUM(ol.OPINION_QTY_22)) as OPINION_QTY_22,
(SUM(ol.OPINION_QTY_23)) as OPINION_QTY_23,
(SUM(ol.OPINION_QTY_24)) as OPINION_QTY_24,
(SUM(ol.OPINION_QTY_25)) as OPINION_QTY_25,
(SUM(ol.OPINION_QTY_26)) as OPINION_QTY_26,
(SUM(ol.OPINION_QTY_27)) as OPINION_QTY_27,
(SUM(ol.OPINION_QTY_28)) as OPINION_QTY_28,
(SUM(ol.OPINION_QTY_29)) as OPINION_QTY_29,
(SUM(ol.OPINION_QTY_30)) as OPINION_QTY_30,
(SUM(ol.OPINION_QTY_31)) as OPINION_QTY_31,
(SUM(ol.OPINION_QTY_32)) as OPINION_QTY_32,
(SUM(ol.OPINION_QTY_33)) as OPINION_QTY_33,
(SUM(ol.OPINION_QTY_34)) as OPINION_QTY_34,
(SUM(ol.OPINION_QTY_35)) as OPINION_QTY_35,
(SUM(ol.OPINION_QTY_36)) as OPINION_QTY_36,
(SUM(ol.OPINION_AMT_N24)) AS OPINION_AMT_N24,
(SUM(ol.OPINION_AMT_N23)) AS OPINION_AMT_N23,
(SUM(ol.OPINION_AMT_N22)) AS OPINION_AMT_N22,
(SUM(ol.OPINION_AMT_N21)) AS OPINION_AMT_N21,
(SUM(ol.OPINION_AMT_N20)) AS OPINION_AMT_N20,
(SUM(ol.OPINION_AMT_N19)) AS OPINION_AMT_N19,
(SUM(ol.OPINION_AMT_N18)) AS OPINION_AMT_N18,
(SUM(ol.OPINION_AMT_N17)) AS OPINION_AMT_N17,
(SUM(ol.OPINION_AMT_N16)) AS OPINION_AMT_N16,
(SUM(ol.OPINION_AMT_N15)) AS OPINION_AMT_N15,
(SUM(ol.OPINION_AMT_N14)) AS OPINION_AMT_N14,
(SUM(ol.OPINION_AMT_N13)) AS OPINION_AMT_N13,
(SUM(ol.OPINION_AMT_N12)) AS OPINION_AMT_N12,
(SUM(ol.OPINION_AMT_N11)) AS OPINION_AMT_N11,
(SUM(ol.OPINION_AMT_N10)) AS OPINION_AMT_N10,
(SUM(ol.OPINION_AMT_N09)) AS OPINION_AMT_N09,
(SUM(ol.OPINION_AMT_N08)) AS OPINION_AMT_N08,
(SUM(ol.OPINION_AMT_N07)) AS OPINION_AMT_N07,
(SUM(ol.OPINION_AMT_N06)) AS OPINION_AMT_N06,
(SUM(ol.OPINION_AMT_N05)) AS OPINION_AMT_N05,
(SUM(ol.OPINION_AMT_N04)) AS OPINION_AMT_N04,
(SUM(ol.OPINION_AMT_N03)) AS OPINION_AMT_N03,
(SUM(ol.OPINION_AMT_N02)) AS OPINION_AMT_N02,
(SUM(ol.OPINION_AMT_N01)) AS OPINION_AMT_N01,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_01*p3.OPINION_QTY_01)
else 0 end) AS OPINION_AMT_01,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_02*p3.OPINION_QTY_02)
else 0 end) AS OPINION_AMT_02,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_03*p3.OPINION_QTY_03)
else 0 end) AS OPINION_AMT_03,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_04*p3.OPINION_QTY_04)
else 0 end) AS OPINION_AMT_04,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_05*p3.OPINION_QTY_05)
else 0 end) AS OPINION_AMT_05,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_06*p3.OPINION_QTY_06)
else 0 end) AS OPINION_AMT_06,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_07*p3.OPINION_QTY_07)
else 0 end) AS OPINION_AMT_07,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_08*p3.OPINION_QTY_08)
else 0 end) AS OPINION_AMT_08,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_09*p3.OPINION_QTY_09)
else 0 end) AS OPINION_AMT_09,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_10*p3.OPINION_QTY_10)
else 0 end) AS OPINION_AMT_10,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_11*p3.OPINION_QTY_11)
else 0 end) AS OPINION_AMT_11,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_12*p3.OPINION_QTY_12)
else 0 end) AS OPINION_AMT_12,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_13*p3.OPINION_QTY_13)
else 0 end) AS OPINION_AMT_13,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_14*p3.OPINION_QTY_14)
else 0 end) AS OPINION_AMT_14,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_15*p3.OPINION_QTY_15)
else 0 end) AS OPINION_AMT_15,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_16*p3.OPINION_QTY_16)
else 0 end) AS OPINION_AMT_16,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_17*p3.OPINION_QTY_17)
else 0 end) AS OPINION_AMT_17,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_18*p3.OPINION_QTY_18)
else 0 end) AS OPINION_AMT_18,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_19*p3.OPINION_QTY_19)
else 0 end) AS OPINION_AMT_19,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_20*p3.OPINION_QTY_20)
else 0 end) AS OPINION_AMT_20,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_21*p3.OPINION_QTY_21)
else 0 end) AS OPINION_AMT_21,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_22*p3.OPINION_QTY_22)
else 0 end) AS OPINION_AMT_22,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_23*p3.OPINION_QTY_23)
else 0 end) AS OPINION_AMT_23,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_24*p3.OPINION_QTY_24)
else 0 end) AS OPINION_AMT_24,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_25*p3.OPINION_QTY_25)
else 0 end) AS OPINION_AMT_25,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_26*p3.OPINION_QTY_26)
else 0 end) AS OPINION_AMT_26,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_27*p3.OPINION_QTY_27)
else 0 end) AS OPINION_AMT_27,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_28*p3.OPINION_QTY_28)
else 0 end) AS OPINION_AMT_28,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_29*p3.OPINION_QTY_29)
else 0 end) AS OPINION_AMT_29,
case ...
from ...
where ...
David Gugick wrote:
>[quoted text clipped - 4 lines]
>Sounds like you are having blocking issues. That is, one query is blocking
>other queries and because of that they are executing in a serial fashion.
>Check the execution plans of the queries in the "task" to make sure they ar
e
>properly using indexes and make sure you avoid long running transactions.
>Feel free to post the table DDL, indexes, queries in question, and the
>execution plans for each if you have any questions. You can check much of
>this information from Profiler. Use the SQL:BatchCompleted and RPC:Complete
d
>events for a high-level performance overview. If you want more detail add
>SQL:StmtCompleted and SP:StmtCompleted events. Look over the cpu, duration,
>and reads columns.
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1|||Robert R via droptable.com wrote:
> This is a sample of one of the long running queries. Keep in mind the
> sample I have included here is only about a quarter of the entire
> statement. Care to explain how this might block other queries so that
> a newbie like me can understand?
> <snip>
Where did that SQL statement come from? It looks as though your table is
denormalized. I see a SKU_1 through SKU_34 that appear to be columns in
the table. If that's true, table design issues aside, that's an
extremely large and complex query that could very well be accessing the
entire table each time it's executed and / or causing extremely high IO
load on your disk subsystem and / or causing high CPU. You are also
returning a SPACE(100) as a column which is a little strange.
Denormalized tables cause large row sizes also very low row density
which means a lot of page reads. Excessive page reads mean a lot of
physical IO which is slow. A lot of aggregations means higher CPU
utilization. All of these items can translate in to slow executing
queries, long transaction times, extended locks that cause blocking,
high disk IO, hign CPU, etc.
So, first we need to know if your table really looks like what I see in
the query. If so, the answers here will likely point to the table design
as the primary problem. It would be impossible for anyone here to
recommend a solution for the query itself without knowing exactly what
the tables look like, what indexes are in place, what you're hoping to
return from the query, and a complete query with parameters/bind
variables.
Sorry I can't be more help. THe best thing to do is take a step back and
make a new post with all the relevant information.
David Gugick
Quest Software
www.quest.com
Monday, March 12, 2012
Quad dualcore CPU server - can I use SQL2k5 Standard?
Can I use SQL2k5 Standard Edition for server with 4 dualcore CPUs? Licence
said - up to 4 CPUs. Will Standard Edition use all 8 cores?"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:3F997145-B8CD-46F0-9D22-3115C4D4DB7B@.microsoft.com...
> Can I use SQL2k5 Standard Edition for server with 4 dualcore CPUs? Licence
> said - up to 4 CPUs. Will Standard Edition use all 8 cores?
Yes.
David|||Thanks David.
But I can not found it on MS site, may you give link?
"David Browne" wrote:
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:3F997145-B8CD-46F0-9D22-3115C4D4DB7B@.microsoft.com...
> > Can I use SQL2k5 Standard Edition for server with 4 dualcore CPUs? Licence
> > said - up to 4 CPUs. Will Standard Edition use all 8 cores?
> Yes.
> David
>
>|||http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
Note under CPU Comments column, it says "Includes support for multi-core
processors".
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:2DA4C3BD-7DFD-4B1A-8D97-104F969CAD40@.microsoft.com...
> Thanks David.
> But I can not found it on MS site, may you give link?
> "David Browne" wrote:
>> "andsm" <andsm@.discussions.microsoft.com> wrote in message
>> news:3F997145-B8CD-46F0-9D22-3115C4D4DB7B@.microsoft.com...
>> > Can I use SQL2k5 Standard Edition for server with 4 dualcore CPUs?
>> > Licence
>> > said - up to 4 CPUs. Will Standard Edition use all 8 cores?
>> Yes.
>> David
>>
said - up to 4 CPUs. Will Standard Edition use all 8 cores?"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:3F997145-B8CD-46F0-9D22-3115C4D4DB7B@.microsoft.com...
> Can I use SQL2k5 Standard Edition for server with 4 dualcore CPUs? Licence
> said - up to 4 CPUs. Will Standard Edition use all 8 cores?
Yes.
David|||Thanks David.
But I can not found it on MS site, may you give link?
"David Browne" wrote:
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:3F997145-B8CD-46F0-9D22-3115C4D4DB7B@.microsoft.com...
> > Can I use SQL2k5 Standard Edition for server with 4 dualcore CPUs? Licence
> > said - up to 4 CPUs. Will Standard Edition use all 8 cores?
> Yes.
> David
>
>|||http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
Note under CPU Comments column, it says "Includes support for multi-core
processors".
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:2DA4C3BD-7DFD-4B1A-8D97-104F969CAD40@.microsoft.com...
> Thanks David.
> But I can not found it on MS site, may you give link?
> "David Browne" wrote:
>> "andsm" <andsm@.discussions.microsoft.com> wrote in message
>> news:3F997145-B8CD-46F0-9D22-3115C4D4DB7B@.microsoft.com...
>> > Can I use SQL2k5 Standard Edition for server with 4 dualcore CPUs?
>> > Licence
>> > said - up to 4 CPUs. Will Standard Edition use all 8 cores?
>> Yes.
>> David
>>
Quad dualcore CPU server - can I use SQL2k5 Standard?
Can I use SQL2k5 Standard Edition for server with 4 dualcore CPUs? Licence
said - up to 4 CPUs. Will Standard Edition use all 8 cores?"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:3F997145-B8CD-46F0-9D22-3115C4D4DB7B@.microsoft.com...
> Can I use SQL2k5 Standard Edition for server with 4 dualcore CPUs? Licence
> said - up to 4 CPUs. Will Standard Edition use all 8 cores?
Yes.
David|||Thanks David.
But I can not found it on MS site, may you give link?
"David Browne" wrote:
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:3F997145-B8CD-46F0-9D22-3115C4D4DB7B@.microsoft.com...
> Yes.
> David
>
>|||http://www.microsoft.com/sql/prodin...e-features.mspx
Note under CPU Comments column, it says "Includes support for multi-core
processors".
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:2DA4C3BD-7DFD-4B1A-8D97-104F969CAD40@.microsoft.com...[vbcol=seagreen]
> Thanks David.
> But I can not found it on MS site, may you give link?
> "David Browne" wrote:
>
said - up to 4 CPUs. Will Standard Edition use all 8 cores?"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:3F997145-B8CD-46F0-9D22-3115C4D4DB7B@.microsoft.com...
> Can I use SQL2k5 Standard Edition for server with 4 dualcore CPUs? Licence
> said - up to 4 CPUs. Will Standard Edition use all 8 cores?
Yes.
David|||Thanks David.
But I can not found it on MS site, may you give link?
"David Browne" wrote:
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:3F997145-B8CD-46F0-9D22-3115C4D4DB7B@.microsoft.com...
> Yes.
> David
>
>|||http://www.microsoft.com/sql/prodin...e-features.mspx
Note under CPU Comments column, it says "Includes support for multi-core
processors".
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:2DA4C3BD-7DFD-4B1A-8D97-104F969CAD40@.microsoft.com...[vbcol=seagreen]
> Thanks David.
> But I can not found it on MS site, may you give link?
> "David Browne" wrote:
>
Quad dualcore CPU server - can I use SQL2k5 Standard?
Can I use SQL2k5 Standard Edition for server with 4 dualcore CPUs? Licence
said - up to 4 CPUs. Will Standard Edition use all 8 cores?
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:3F997145-B8CD-46F0-9D22-3115C4D4DB7B@.microsoft.com...
> Can I use SQL2k5 Standard Edition for server with 4 dualcore CPUs? Licence
> said - up to 4 CPUs. Will Standard Edition use all 8 cores?
Yes.
David
|||Thanks David.
But I can not found it on MS site, may you give link?
"David Browne" wrote:
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:3F997145-B8CD-46F0-9D22-3115C4D4DB7B@.microsoft.com...
> Yes.
> David
>
>
|||http://www.microsoft.com/sql/prodinf...-features.mspx
Note under CPU Comments column, it says "Includes support for multi-core
processors".
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:2DA4C3BD-7DFD-4B1A-8D97-104F969CAD40@.microsoft.com...[vbcol=seagreen]
> Thanks David.
> But I can not found it on MS site, may you give link?
> "David Browne" wrote:
said - up to 4 CPUs. Will Standard Edition use all 8 cores?
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:3F997145-B8CD-46F0-9D22-3115C4D4DB7B@.microsoft.com...
> Can I use SQL2k5 Standard Edition for server with 4 dualcore CPUs? Licence
> said - up to 4 CPUs. Will Standard Edition use all 8 cores?
Yes.
David
|||Thanks David.
But I can not found it on MS site, may you give link?
"David Browne" wrote:
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:3F997145-B8CD-46F0-9D22-3115C4D4DB7B@.microsoft.com...
> Yes.
> David
>
>
|||http://www.microsoft.com/sql/prodinf...-features.mspx
Note under CPU Comments column, it says "Includes support for multi-core
processors".
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:2DA4C3BD-7DFD-4B1A-8D97-104F969CAD40@.microsoft.com...[vbcol=seagreen]
> Thanks David.
> But I can not found it on MS site, may you give link?
> "David Browne" wrote:
Qry Analyzer: Processor utilization
I have noticed that when using SQL Query Analyzer some of my queries will use 100% CPU on my PC and next to nothing on the SQL server, while other Queries require 100% CPU on SQL server and do next to nothing on my PC. Does anyone know what determines this??
Right now I can produce this by executing two very similar T-SQL selects. The one that runs on the server only has one additional join - a very simply join at that. If I can change my SQL to make it run client side in some situations, that would be VERY HELPFUL!
Thanks in advance!
RyanWhat are you referring to when saying that the query takes 100% on your machine? Are you comparing the performance of MSDE running on your machine with SQL Server engine running on the server?|||No, MSDE is not involved. If I run a query with one join through SQL Query Analyzer on my PC, and against my production 7.0 sql server, it will use 100% of my PC's processor. If I execute a query with two inner joins in it, still from query analyzer and against the same SQL box, the query uses 100% of the server's processor and does not use the client PC processor.
I guess this would probably be occurring based on some decision made by the SQL optimizer, but I am trying to find out if anyone is familiar enough with such a situation that they know tricks to writing queries so that processing occurs on the client side.
I have some users that require atypical adhoc query support. When possible, I would like to write queries for them in such a way that I can place most of the processor requirements on the client PC.
Thanks
Ryan|||I don't think it's possible. ALL processing is done on the server. Unless you're talking about firing queries through MS Access that "claims" to be able to do data processing itself. But even then, whatever statement ends up going to the server from the Jet, - gets processed entirely by the SQL engine, the client just reads the results either from the named pipe file, or from TCP/IP packets (virtual file.)
Right now I can produce this by executing two very similar T-SQL selects. The one that runs on the server only has one additional join - a very simply join at that. If I can change my SQL to make it run client side in some situations, that would be VERY HELPFUL!
Thanks in advance!
RyanWhat are you referring to when saying that the query takes 100% on your machine? Are you comparing the performance of MSDE running on your machine with SQL Server engine running on the server?|||No, MSDE is not involved. If I run a query with one join through SQL Query Analyzer on my PC, and against my production 7.0 sql server, it will use 100% of my PC's processor. If I execute a query with two inner joins in it, still from query analyzer and against the same SQL box, the query uses 100% of the server's processor and does not use the client PC processor.
I guess this would probably be occurring based on some decision made by the SQL optimizer, but I am trying to find out if anyone is familiar enough with such a situation that they know tricks to writing queries so that processing occurs on the client side.
I have some users that require atypical adhoc query support. When possible, I would like to write queries for them in such a way that I can place most of the processor requirements on the client PC.
Thanks
Ryan|||I don't think it's possible. ALL processing is done on the server. Unless you're talking about firing queries through MS Access that "claims" to be able to do data processing itself. But even then, whatever statement ends up going to the server from the Jet, - gets processed entirely by the SQL engine, the client just reads the results either from the named pipe file, or from TCP/IP packets (virtual file.)
Subscribe to:
Posts (Atom)