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