Query Across Servers

Hi,
Is it possible to execute a query asking for joined data
from different tables on two different servers and
databases?
SQLServer1 SQLServer2
-- --
09.0.0.30\TestData 198.160.30.20\TestData2
I need to run a query against tables in the TestData &
TestData2 databases located at the I.P. locations
simulated above. Joins are necessary.
Thanks in advance.You can use linked servers and then fully qualify database objects, for
example:
SELECT * FROM SQLServer1.database.dbo.tablename -- from server2
You can find more info about linked servers in books online...
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"DBa" <anonymous@.discussions.microsoft.com> wrote in message
news:639101c3e5cb$e41662e0$a601280a@.phx.gbl...

quote:

---

> Hi,
> Is it possible to execute a query asking for joined data
> from different tables on two different servers and
> databases?
> SQLServer1 SQLServer2
> -- --
> 09.0.0.30\TestData 198.160.30.20\TestData2
> I need to run a query against tables in the TestData &
> TestData2 databases located at the I.P. locations
> simulated above. Joins are necessary.
> Thanks in advance.

---

|||alternate solution is to use OPENROWSET if you dont have permissions to
create linked server
Ex:
SELECT a.*
FROM OPENROWSET('SQLOLEDB','server1';'user1';
'password1',
'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
FROM OPENROWSET('SQLOLEDB','server2';'user1';
'password1',
'SELECT * FROM pubs.dbo.titleauthor') AS b
where a.au_id = b.au_id
GO
SQL Booksonline has more samples on OPENROWSET
Sethu Srinivasan
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
"Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
news:O2EjQ6h5DHA.2496@.TK2MSFTNGP09.phx.gbl...

quote:

---

> You can use linked servers and then fully qualify database objects, for
> example:
> SELECT * FROM SQLServer1.database.dbo.tablename -- from server2
> You can find more info about linked servers in books online...
> --
> Carlos E. Rojas
> SQL Server MVP
> Co-Author SQL Server 2000 programming by Example
>
> "DBa" <anonymous@.discussions.microsoft.com> wrote in message
> news:639101c3e5cb$e41662e0$a601280a@.phx.gbl...
>

---

Query

Hi

I am trying to execute the below query in Query analyzer, the same query is executed in Sybase with the corresponding columns.

select (a.OnlineAccessId) + CASE
WHEN (a.onlineaccessid2) <> '00000000000000' THEN (a.onlineaccessid2)
else ''
End + CASE
WHEN (a.onlineaccessid3) <> '00000000000000' THEN (a.onlineaccessid3)
else ''
End as onlineaccessids, a.id as AcctInfoId, BankNo, RegionNo, OfficeNo, AcctNo,AcctShortNm ,SecLending, q.id as UsersId
from AcctInfo AS a INNER JOIN "104030" AS p ON a.AdminOfficer = p.CrmCode
inner join Users as q ON p.CrmDesc = q.OnlineaccessId union select
OnlineAccessIds = (a.OnlineAccessId) + CASE
WHEN (a.onlineaccessid2) <> '00000000000000' THEN (a.onlineaccessid2)
else ''
End + CASE
WHEN (a.onlineaccessid3) <> '00000000000000' THEN (a.onlineaccessid3)
else ''
End,q.id, a.Id, BankNo, RegionNo,
OfficeNo, AcctNo,AcctShortNm, SecLending
from AcctInfo as a
inner join users as q on
(q.onlineaccessid = substring(onlineaccessids, 1, 3))

The error message is

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'onlineaccessids'..

It is able to recognize the field "onlineaccessids" and giving me the results in Sybase database and giving the error in SQL Server.
Try to help me out.

Thanx in advanceFirst, double check that both the AcctInfo and users tables have an attribute "onlineaccessids".

Secondly, add an alias to:

(q.onlineaccessid = substring(a/q.onlineaccessids, 1, 3))

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 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 droptable.com
http://www.droptable.com/Uwe/Forums...erver/200601/1
Robert 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 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:
>[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 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 droptable.com
http://www.droptable.com/Uwe/Forums...erver/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
sql

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

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

Queries Disk Bound?

We're attempting to determine why we periodically see single-record queries
requiring up to 20 seconds to execute. We configured SQL Profiler to log
any query requiring at least 5 seconds to complete, and what we're seeing is
that it typically occurs when a different query involving large select or
insert (sometimes requiring 30 seconds to execute) has begun execution just
prior to the affected query, although usually against a different table.
We've eliminated blocking as an issue, and have also taken steps to correct
the parallelism problem (as this is a dual-processor machine). Upon running
performance monitor, we've noticed that at the instant when this problem
occurs that the % Disk Time counter spikes (presumably due to the larger
query), and that the Current Disk Queue Length goes from 0 to anywhere
between 1 and 5. When the large query completes execution, the other
queries finish and the Current Disk Queue Length reverts to 0.
My question is, are we disk bound? It doesn't seem that one large query
should prevent others from completing execution, especially because there's
no blocking and the other queries are typically against different tables.
Are we missing a SQL configuration setting or is this purely a hardware
issue? Are having the transaction logs on the same disks as the DB causing
a problem even though we use the Simple Recovery Model? Our server
configuration is as follows:
Dell 2650
Dual 2.6GHz Xeon
2GB RAM
PERC-3 Controller
(5) 73GB Drives in RAID 5 configuration
Windows 2000 SP3
SQL Server 2000 SP3
The database is set to the Simple Recovery model, and Auto Create and Auto
Update Statistics are both selected.
Application is a mixture of OLTP and OLAP; issue is able to be replicated
even when server volume is extremely light.
Thanks,
Chris
birk@.xyznetwerkes.com
If replying via e-mail, please remove the "xyz" from the address above.Q: w/o the large insert/select queries, what is the avg
disk read/sec? if 0, then most of your data is in memory.
so a heavy write action that does block the disk would not
cause the single row select to be blocked on disk, but
could be blocked for other reasons, ex. inability to
acquire a lock as tom pointed out
the data and log files should be on separate disks for
best disk performance,
RAID 1 for log, RAID 1+0 for data
some arrays controller have absolutely horrible RAID5
performance. the controller should be set to 100% write
cache.
are the large inserts of the type:
INSERT tableA SELECT xx FROM TableA
or a loop or cursor with single row inserts?
try a ROWLOCK hint on the insert to inhibit escalation to
a table lock (and also break up very large inserts into
medium size inserts, 5-10k rows max)
if single row inserts with a loop, try bracketing it with
BEGIN/COMMIT TRAN
w/o the tran, each single row insert causes a log write,
while the TRAN insert log writes are consolidated
>--Original Message--
>We're attempting to determine why we periodically see
single-record queries
>requiring up to 20 seconds to execute. We configured SQL
Profiler to log
>any query requiring at least 5 seconds to complete, and
what we're seeing is
>that it typically occurs when a different query involving
large select or
>insert (sometimes requiring 30 seconds to execute) has
begun execution just
>prior to the affected query, although usually against a
different table.
>We've eliminated blocking as an issue, and have also
taken steps to correct
>the parallelism problem (as this is a dual-processor
machine). Upon running
>performance monitor, we've noticed that at the instant
when this problem
>occurs that the % Disk Time counter spikes (presumably
due to the larger
>query), and that the Current Disk Queue Length goes from
0 to anywhere
>between 1 and 5. When the large query completes
execution, the other
>queries finish and the Current Disk Queue Length reverts
to 0.
>My question is, are we disk bound? It doesn't seem that
one large query
>should prevent others from completing execution,
especially because there's
>no blocking and the other queries are typically against
different tables.
>Are we missing a SQL configuration setting or is this
purely a hardware
>issue? Are having the transaction logs on the same disks
as the DB causing
>a problem even though we use the Simple Recovery Model?
Our server
>configuration is as follows:
>Dell 2650
>Dual 2.6GHz Xeon
>2GB RAM
>PERC-3 Controller
>(5) 73GB Drives in RAID 5 configuration
>Windows 2000 SP3
>SQL Server 2000 SP3
>The database is set to the Simple Recovery model, and
Auto Create and Auto
>Update Statistics are both selected.
>Application is a mixture of OLTP and OLAP; issue is able
to be replicated
>even when server volume is extremely light.
>Thanks,
>Chris
>birk@.xyznetwerkes.com
>If replying via e-mail, please remove the "xyz" from the
address above.
>
>.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0046_01C36263.2107A7A0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Tom,
Our avg disk queue lengths do send to be relatively small, typically =averaging under 1. The part of this issue which is confusing to me is =that the blocked queries can be single row select, inserts, and updates =being performed on a different table than the large query (which is =typically a select). Wouldn't this preclude it from being a lock =escalation issue? I will begin examining lock escalation events in =Profiler right away.
The current disk queue length intrigued me because I can watch the =performance monitor, see the current disk queue length climb above 0 for =a few seconds, and know that one or more SQL Profiler entries for a =simple query lasting more than 5 seconds, and one entry for a complex =query will be immediately forthcoming.
Thanks,
Chris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:urAP7eoYDHA.2592@.TK2MSFTNGP09.phx.gbl...
Your disk queue length should not be > 2 x # of disks =3D 2 x 5 =3D =10, so you're probably not disk bound. BTW, you should use avg disk =queue length, not current disk queue length. Also, you really shouldn't =have the transaction logs on the same drives as your data files. Place =them on separate arrays. Ideally, the logs should be on RAID1.
I would not be so sure that your large query is not blocking your =inserts. Have you looked for lock escalation events in your trace? If =you are getting lock escalation, that can block inserts, updates and =deletes on your target table. Mixing OLAP and OLTP can give you such =problems. Check out KB article 32360, "INF: Resolving Blocking Problems =That Are Caused by Lock Escalation in SQL Server." If you subscribe to =SQL Pro, check out my June 2003 column:
=http://www.pinnaclepublishing.com/SQ/SQmag.nsf/0/80D93C0CC911C79C85256D32=
006D26BC?open&login
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Chris Birk" <birk@.xyznetwerkes.com> wrote in message =news:3f3bc0d9$1_1@.news.vic.com...
We're attempting to determine why we periodically see single-record =queries
requiring up to 20 seconds to execute. We configured SQL Profiler to =log
any query requiring at least 5 seconds to complete, and what we're =seeing is
that it typically occurs when a different query involving large select =or
insert (sometimes requiring 30 seconds to execute) has begun execution =just
prior to the affected query, although usually against a different =table.
We've eliminated blocking as an issue, and have also taken steps to =correct
the parallelism problem (as this is a dual-processor machine). Upon =running
performance monitor, we've noticed that at the instant when this =problem
occurs that the % Disk Time counter spikes (presumably due to the =larger
query), and that the Current Disk Queue Length goes from 0 to anywhere
between 1 and 5. When the large query completes execution, the other
queries finish and the Current Disk Queue Length reverts to 0.
My question is, are we disk bound? It doesn't seem that one large =query
should prevent others from completing execution, especially because =there's
no blocking and the other queries are typically against different =tables.
Are we missing a SQL configuration setting or is this purely a =hardware
issue? Are having the transaction logs on the same disks as the DB =causing
a problem even though we use the Simple Recovery Model? Our server
configuration is as follows:
Dell 2650
Dual 2.6GHz Xeon
2GB RAM
PERC-3 Controller
(5) 73GB Drives in RAID 5 configuration
Windows 2000 SP3
SQL Server 2000 SP3
The database is set to the Simple Recovery model, and Auto Create and =Auto
Update Statistics are both selected.
Application is a mixture of OLTP and OLAP; issue is able to be =replicated
even when server volume is extremely light.
Thanks,
Chris
birk@.xyznetwerkes.com
If replying via e-mail, please remove the "xyz" from the address =above.
--=_NextPart_000_0046_01C36263.2107A7A0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Tom,
Our avg disk queue lengths do send to =be relatively small, typically averaging under 1. The part of this issue which =is confusing to me is that the blocked queries can be single row select, =inserts, and updates being performed on a different table than the large query =(which is typically a select). Wouldn't this preclude it from being a lock escalation issue? I will begin examining lock escalation events in =Profiler right away.
The current disk queue length intrigued =me because I can watch the performance monitor, see the current disk queue length =climb above 0 for a few seconds, and know that one or more SQL Profiler =entries for a simple query lasting more than 5 seconds, and one entry for a complex =query will be immediately forthcoming.
Thanks,Chris
"Tom Moreau" = wrote in message news:urAP7eoYDHA.2592=@.TK2MSFTNGP09.phx.gbl...
Your disk queue length should not be => 2 x # of disks =3D 2 x 5 =3D 10, so you're probably not disk bound. =BTW, you should use avg disk queue length, not current disk queue length. =Also, you really shouldn't have the transaction logs on the same drives as =your data files. Place them on separate arrays. Ideally, the =logs should be on RAID1.

I would not be so sure that your =large query is not blocking your inserts. Have you looked for lock escalation =events in your trace? If you are getting lock escalation, that can block =inserts, updates and deletes on your target table. Mixing OLAP and OLTP =can give you such problems. Check out KB article 32360, "INF: Resolving =Blocking Problems That Are Caused by Lock Escalation in SQL Server." If =you subscribe to SQL Pro, check out my June 2003 column:

http://www.pinnaclepublishing.com/SQ/SQm=ag.nsf/0/80D93C0CC911C79C85256D32006D26BC?open&login= -- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Chris Birk" =wrote in message news:3f3bc0d9$1_1@.news.vic.com...We're attempting to determine why we periodically see single-record queriesrequiring up to 20 seconds to execute. We configured =SQL Profiler to logany query requiring at least 5 seconds to complete, =and what we're seeing isthat it typically occurs when a different =query involving large select orinsert (sometimes requiring 30 seconds to = execute) has begun execution justprior to the affected query, =although usually against a different table.We've eliminated blocking as =an issue, and have also taken steps to correctthe parallelism problem =(as this is a dual-processor machine). Upon runningperformance =monitor, we've noticed that at the instant when this problemoccurs that the =% Disk Time counter spikes (presumably due to the largerquery), and that =the Current Disk Queue Length goes from 0 to anywherebetween 1 and =5. When the large query completes execution, the otherqueries finish =and the Current Disk Queue Length reverts to 0.My question is, are we =disk bound? It doesn't seem that one large queryshould prevent =others from completing execution, especially because there'sno blocking =and the other queries are typically against different tables.Are we =missing a SQL configuration setting or is this purely a hardwareissue? Are =having the transaction logs on the same disks as the DB causinga problem =even though we use the Simple Recovery Model? Our =serverconfiguration is as follows:Dell 2650Dual 2.6GHz Xeon2GB RAMPERC-3 Controller(5) 73GB Drives in RAID 5 configurationWindows 2000 SP3SQL Server 2000 SP3The database is set to the Simple =Recovery model, and Auto Create and AutoUpdate Statistics are both selected.Application is a mixture of OLTP and OLAP; issue is able =to be replicatedeven when server volume is extremely light.Thanks,Chrisbirk@.xyznetwerkes.comIf =replying via e-mail, please remove the "xyz" from the address above.

---

--=_NextPart_000_0046_01C36263.2107A7A0--|||This is a multi-part message in MIME format.
--=_NextPart_000_03BD_01C3626C.C6DB59C0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Don't take this the wrong way but I envy the avg length of your disk =queue. ;-) I have a 12-disk RAID10, which should not have an avg disk =queue length > 24. Can you say 100, 1000, 2500? Have I whined about =this to management? Yes. Have they done anything? No. :-(
Definitely add lock escalation to your profiler trace. We really have =to get the whole picture. I'm wondering about explicit transactions, =too. You may want to look at Data File Autogrow events on all =databases. It could be that you are having to wait for an autogrow =before you can do the insert.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Chris Birk" <birk@.xyznetwerkes.com> wrote in message =news:3f3bcc5a_2@.news.vic.com...
Tom,
Our avg disk queue lengths do send to be relatively small, typically =averaging under 1. The part of this issue which is confusing to me is =that the blocked queries can be single row select, inserts, and updates =being performed on a different table than the large query (which is =typically a select). Wouldn't this preclude it from being a lock =escalation issue? I will begin examining lock escalation events in =Profiler right away.
The current disk queue length intrigued me because I can watch the =performance monitor, see the current disk queue length climb above 0 for =a few seconds, and know that one or more SQL Profiler entries for a =simple query lasting more than 5 seconds, and one entry for a complex =query will be immediately forthcoming.
Thanks,
Chris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:urAP7eoYDHA.2592@.TK2MSFTNGP09.phx.gbl...
Your disk queue length should not be > 2 x # of disks =3D 2 x 5 =3D =10, so you're probably not disk bound. BTW, you should use avg disk =queue length, not current disk queue length. Also, you really shouldn't =have the transaction logs on the same drives as your data files. Place =them on separate arrays. Ideally, the logs should be on RAID1.
I would not be so sure that your large query is not blocking your =inserts. Have you looked for lock escalation events in your trace? If =you are getting lock escalation, that can block inserts, updates and =deletes on your target table. Mixing OLAP and OLTP can give you such =problems. Check out KB article 32360, "INF: Resolving Blocking Problems =That Are Caused by Lock Escalation in SQL Server." If you subscribe to =SQL Pro, check out my June 2003 column:
=http://www.pinnaclepublishing.com/SQ/SQmag.nsf/0/80D93C0CC911C79C85256D32=
006D26BC?open&login
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Chris Birk" <birk@.xyznetwerkes.com> wrote in message =news:3f3bc0d9$1_1@.news.vic.com...
We're attempting to determine why we periodically see single-record =queries
requiring up to 20 seconds to execute. We configured SQL Profiler to =log
any query requiring at least 5 seconds to complete, and what we're =seeing is
that it typically occurs when a different query involving large select =or
insert (sometimes requiring 30 seconds to execute) has begun execution =just
prior to the affected query, although usually against a different =table.
We've eliminated blocking as an issue, and have also taken steps to =correct
the parallelism problem (as this is a dual-processor machine). Upon =running
performance monitor, we've noticed that at the instant when this =problem
occurs that the % Disk Time counter spikes (presumably due to the =larger
query), and that the Current Disk Queue Length goes from 0 to anywhere
between 1 and 5. When the large query completes execution, the other
queries finish and the Current Disk Queue Length reverts to 0.
My question is, are we disk bound? It doesn't seem that one large =query
should prevent others from completing execution, especially because =there's
no blocking and the other queries are typically against different =tables.
Are we missing a SQL configuration setting or is this purely a =hardware
issue? Are having the transaction logs on the same disks as the DB =causing
a problem even though we use the Simple Recovery Model? Our server
configuration is as follows:
Dell 2650
Dual 2.6GHz Xeon
2GB RAM
PERC-3 Controller
(5) 73GB Drives in RAID 5 configuration
Windows 2000 SP3
SQL Server 2000 SP3
The database is set to the Simple Recovery model, and Auto Create and =Auto
Update Statistics are both selected.
Application is a mixture of OLTP and OLAP; issue is able to be =replicated
even when server volume is extremely light.
Thanks,
Chris
birk@.xyznetwerkes.com
If replying via e-mail, please remove the "xyz" from the address =above.
--=_NextPart_000_03BD_01C3626C.C6DB59C0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Don't take this the wrong way but I =envy the avg length of your disk queue. ;-) I have a 12-disk =RAID10, which should not have an avg disk queue length > 24. Can you say 100, =1000, 2500? Have I whined about this to management? Yes. =Have they done anything? No. :-(
Definitely add lock escalation to your =profiler trace. We really have to get the whole picture. I'm =wondering about explicit transactions, too. You may want to look at Data File =Autogrow events on all databases. It could be that you are having to wait =for an autogrow before you can do the insert.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Chris Birk" =wrote in message news:3f3bcc5a_2@.news.vic.com...=Tom,
Our avg disk queue lengths do send to =be relatively small, typically averaging under 1. The part of this issue which =is confusing to me is that the blocked queries can be single row select, =inserts, and updates being performed on a different table than the large query =(which is typically a select). Wouldn't this preclude it from being a lock escalation issue? I will begin examining lock escalation events in =Profiler right away.
The current disk queue length intrigued =me because I can watch the performance monitor, see the current disk queue length =climb above 0 for a few seconds, and know that one or more SQL Profiler =entries for a simple query lasting more than 5 seconds, and one entry for a complex =query will be immediately forthcoming.
Thanks,Chris
"Tom Moreau" = wrote in message news:urAP7eoYDHA.2592=@.TK2MSFTNGP09.phx.gbl...
Your disk queue length should not be => 2 x # of disks =3D 2 x 5 =3D 10, so you're probably not disk bound. =BTW, you should use avg disk queue length, not current disk queue length. =Also, you really shouldn't have the transaction logs on the same drives as =your data files. Place them on separate arrays. Ideally, the =logs should be on RAID1.

I would not be so sure that your =large query is not blocking your inserts. Have you looked for lock escalation =events in your trace? If you are getting lock escalation, that can block =inserts, updates and deletes on your target table. Mixing OLAP and OLTP =can give you such problems. Check out KB article 32360, "INF: Resolving =Blocking Problems That Are Caused by Lock Escalation in SQL Server." If =you subscribe to SQL Pro, check out my June 2003 column:

http://www.pinnaclepublishing.com/SQ/SQm=ag.nsf/0/80D93C0CC911C79C85256D32006D26BC?open&login= -- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Chris Birk" =wrote in message news:3f3bc0d9$1_1@.news.vic.com...We're attempting to determine why we periodically see single-record queriesrequiring up to 20 seconds to execute. We configured =SQL Profiler to logany query requiring at least 5 seconds to complete, =and what we're seeing isthat it typically occurs when a different =query involving large select orinsert (sometimes requiring 30 seconds to = execute) has begun execution justprior to the affected query, =although usually against a different table.We've eliminated blocking as =an issue, and have also taken steps to correctthe parallelism problem =(as this is a dual-processor machine). Upon runningperformance =monitor, we've noticed that at the instant when this problemoccurs that the =% Disk Time counter spikes (presumably due to the largerquery), and that =the Current Disk Queue Length goes from 0 to anywherebetween 1 and =5. When the large query completes execution, the otherqueries finish =and the Current Disk Queue Length reverts to 0.My question is, are we =disk bound? It doesn't seem that one large queryshould prevent =others from completing execution, especially because there'sno blocking =and the other queries are typically against different tables.Are we =missing a SQL configuration setting or is this purely a hardwareissue? Are =having the transaction logs on the same disks as the DB causinga problem =even though we use the Simple Recovery Model? Our =serverconfiguration is as follows:Dell 2650Dual 2.6GHz Xeon2GB RAMPERC-3 Controller(5) 73GB Drives in RAID 5 configurationWindows 2000 SP3SQL Server 2000 SP3The database is set to the Simple =Recovery model, and Auto Create and AutoUpdate Statistics are both selected.Application is a mixture of OLTP and OLAP; issue is able =to be replicatedeven when server volume is extremely light.Thanks,Chrisbirk@.xyznetwerkes.comIf =replying via e-mail, please remove the "xyz" from the address above.

---

--=_NextPart_000_03BD_01C3626C.C6DB59C0--|||Joe,
Without the large queries, Disk Reads/Sec tends to average between 5 and 15.
Probably 90 - 95% of the large queries are selects, and most of the rest are
updates. Nearly all of the large insert operations we do are done a row at
the time with a repetitive single record insert stored proc call from COM+.
We are planning to bring up a new server for this application in the
not-too-distant future, and I appreciate your input on the disk
configuration. We definitely need to make some changes there.
Thanks,
Chris
"joe chang" <jchang6@.yahoo.com> wrote in message
news:0a8701c3628b$18acf470$a001280a@.phx.gbl...
> Q: w/o the large insert/select queries, what is the avg
> disk read/sec? if 0, then most of your data is in memory.
> so a heavy write action that does block the disk would not
> cause the single row select to be blocked on disk, but
> could be blocked for other reasons, ex. inability to
> acquire a lock as tom pointed out
> the data and log files should be on separate disks for
> best disk performance,
> RAID 1 for log, RAID 1+0 for data
> some arrays controller have absolutely horrible RAID5
> performance. the controller should be set to 100% write
> cache.
> are the large inserts of the type:
> INSERT tableA SELECT xx FROM TableA
> or a loop or cursor with single row inserts?
> try a ROWLOCK hint on the insert to inhibit escalation to
> a table lock (and also break up very large inserts into
> medium size inserts, 5-10k rows max)
> if single row inserts with a loop, try bracketing it with
> BEGIN/COMMIT TRAN
> w/o the tran, each single row insert causes a log write,
> while the TRAN insert log writes are consolidated
> >--Original Message--
> >We're attempting to determine why we periodically see
> single-record queries
> >requiring up to 20 seconds to execute. We configured SQL
> Profiler to log
> >any query requiring at least 5 seconds to complete, and
> what we're seeing is
> >that it typically occurs when a different query involving
> large select or
> >insert (sometimes requiring 30 seconds to execute) has
> begun execution just
> >prior to the affected query, although usually against a
> different table.
> >
> >We've eliminated blocking as an issue, and have also
> taken steps to correct
> >the parallelism problem (as this is a dual-processor
> machine). Upon running
> >performance monitor, we've noticed that at the instant
> when this problem
> >occurs that the % Disk Time counter spikes (presumably
> due to the larger
> >query), and that the Current Disk Queue Length goes from
> 0 to anywhere
> >between 1 and 5. When the large query completes
> execution, the other
> >queries finish and the Current Disk Queue Length reverts
> to 0.
> >
> >My question is, are we disk bound? It doesn't seem that
> one large query
> >should prevent others from completing execution,
> especially because there's
> >no blocking and the other queries are typically against
> different tables.
> >Are we missing a SQL configuration setting or is this
> purely a hardware
> >issue? Are having the transaction logs on the same disks
> as the DB causing
> >a problem even though we use the Simple Recovery Model?
> Our server
> >configuration is as follows:
> >
> >Dell 2650
> >Dual 2.6GHz Xeon
> >2GB RAM
> >PERC-3 Controller
> >(5) 73GB Drives in RAID 5 configuration
> >Windows 2000 SP3
> >SQL Server 2000 SP3
> >The database is set to the Simple Recovery model, and
> Auto Create and Auto
> >Update Statistics are both selected.
> >Application is a mixture of OLTP and OLAP; issue is able
> to be replicated
> >even when server volume is extremely light.
> >
> >Thanks,
> >
> >Chris
> >birk@.xyznetwerkes.com
> >If replying via e-mail, please remove the "xyz" from the
> address above.
> >
> >
> >.
> >|||This is a multi-part message in MIME format.
--=_NextPart_000_005F_01C36267.B79FE9D0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Tom,
Ahh, yes, size does matter. But in this particular instance smaller is =better. ;-)
Lock escalation revealed probably 5 - 10 intent share-locks per minute =but nothing which looked insidious because they were occurring on tables =which were not being used by the small queries.
I can further simplify the problem by saying that both the large and =small queries are typically selects, and don't have any tables in =common.
Thanks,
Chris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eP5Pb5oYDHA.4040@.tk2msftngp13.phx.gbl...
Don't take this the wrong way but I envy the avg length of your disk =queue. ;-) I have a 12-disk RAID10, which should not have an avg disk =queue length > 24. Can you say 100, 1000, 2500? Have I whined about =this to management? Yes. Have they done anything? No. :-(
Definitely add lock escalation to your profiler trace. We really have =to get the whole picture. I'm wondering about explicit transactions, =too. You may want to look at Data File Autogrow events on all =databases. It could be that you are having to wait for an autogrow =before you can do the insert.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Chris Birk" <birk@.xyznetwerkes.com> wrote in message =news:3f3bcc5a_2@.news.vic.com...
Tom,
Our avg disk queue lengths do send to be relatively small, typically =averaging under 1. The part of this issue which is confusing to me is =that the blocked queries can be single row select, inserts, and updates =being performed on a different table than the large query (which is =typically a select). Wouldn't this preclude it from being a lock =escalation issue? I will begin examining lock escalation events in =Profiler right away.
The current disk queue length intrigued me because I can watch the =performance monitor, see the current disk queue length climb above 0 for =a few seconds, and know that one or more SQL Profiler entries for a =simple query lasting more than 5 seconds, and one entry for a complex =query will be immediately forthcoming.
Thanks,
Chris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:urAP7eoYDHA.2592@.TK2MSFTNGP09.phx.gbl...
Your disk queue length should not be > 2 x # of disks =3D 2 x 5 =3D =10, so you're probably not disk bound. BTW, you should use avg disk =queue length, not current disk queue length. Also, you really shouldn't =have the transaction logs on the same drives as your data files. Place =them on separate arrays. Ideally, the logs should be on RAID1.
I would not be so sure that your large query is not blocking your =inserts. Have you looked for lock escalation events in your trace? If =you are getting lock escalation, that can block inserts, updates and =deletes on your target table. Mixing OLAP and OLTP can give you such =problems. Check out KB article 32360, "INF: Resolving Blocking Problems =That Are Caused by Lock Escalation in SQL Server." If you subscribe to =SQL Pro, check out my June 2003 column:
=http://www.pinnaclepublishing.com/SQ/SQmag.nsf/0/80D93C0CC911C79C85256D32=
006D26BC?open&login
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Chris Birk" <birk@.xyznetwerkes.com> wrote in message =news:3f3bc0d9$1_1@.news.vic.com...
We're attempting to determine why we periodically see single-record =queries
requiring up to 20 seconds to execute. We configured SQL Profiler =to log
any query requiring at least 5 seconds to complete, and what we're =seeing is
that it typically occurs when a different query involving large =select or
insert (sometimes requiring 30 seconds to execute) has begun =execution just
prior to the affected query, although usually against a different =table.
We've eliminated blocking as an issue, and have also taken steps to =correct
the parallelism problem (as this is a dual-processor machine). Upon =running
performance monitor, we've noticed that at the instant when this =problem
occurs that the % Disk Time counter spikes (presumably due to the =larger
query), and that the Current Disk Queue Length goes from 0 to =anywhere
between 1 and 5. When the large query completes execution, the =other
queries finish and the Current Disk Queue Length reverts to 0.
My question is, are we disk bound? It doesn't seem that one large =query
should prevent others from completing execution, especially because =there's
no blocking and the other queries are typically against different =tables.
Are we missing a SQL configuration setting or is this purely a =hardware
issue? Are having the transaction logs on the same disks as the DB =causing
a problem even though we use the Simple Recovery Model? Our server
configuration is as follows:
Dell 2650
Dual 2.6GHz Xeon
2GB RAM
PERC-3 Controller
(5) 73GB Drives in RAID 5 configuration
Windows 2000 SP3
SQL Server 2000 SP3
The database is set to the Simple Recovery model, and Auto Create =and Auto
Update Statistics are both selected.
Application is a mixture of OLTP and OLAP; issue is able to be =replicated
even when server volume is extremely light.
Thanks,
Chris
birk@.xyznetwerkes.com
If replying via e-mail, please remove the "xyz" from the address =above.
--=_NextPart_000_005F_01C36267.B79FE9D0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Tom,
Ahh, yes, size does matter. But =in this particular instance smaller is better. ;-)
Lock escalation revealed probably 5 - =10 intent share-locks per minute but nothing which looked insidious because they =were occurring on tables which were not being used by the small =queries.
I can further simplify the =problem by saying that both the large and small queries are typically selects, =and don't have any tables in common.
Thanks,Chris
"Tom Moreau" = wrote in message news:eP5Pb5oYDHA.4040=@.tk2msftngp13.phx.gbl...
Don't take this the wrong way but I =envy the avg length of your disk queue. ;-) I have a 12-disk =RAID10, which should not have an avg disk queue length > 24. Can you =say 100, 1000, 2500? Have I whined about this to management? =Yes. Have they done anything? No. :-(

Definitely add lock escalation to =your profiler trace. We really have to get the whole picture. I'm =wondering about explicit transactions, too. You may want to look at Data =File Autogrow events on all databases. It could be that you are =having to wait for an autogrow before you can do the insert.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Chris Birk" =wrote in message news:3f3bcc5a_2@.news.vic.com...= Tom,

Our avg disk queue lengths do send to =be relatively small, typically averaging under 1. The part of this =issue which is confusing to me is that the blocked queries can be single row =select, inserts, and updates being performed on a different table than the =large query (which is typically a select). Wouldn't this preclude it from =being a lock escalation issue? I will begin examining lock escalation =events in Profiler right away.

The current disk queue length =intrigued me because I can watch the performance monitor, see the current disk =queue length climb above 0 for a few seconds, and know that one or more SQL =Profiler entries for a simple query lasting more than 5 seconds, and one entry =for a complex query will be immediately forthcoming.

Thanks,Chris
"Tom Moreau" = wrote in message news:urAP7eoYDHA.2592=@.TK2MSFTNGP09.phx.gbl...
Your disk queue length should not =be > 2 x # of disks =3D 2 x 5 =3D 10, so you're probably not disk =bound. BTW, you should use avg disk queue length, not current disk queue =length. Also, you really shouldn't have the transaction logs on the same drives as =your data files. Place them on separate arrays. =Ideally, the logs should be on RAID1.

I would not be so sure that your =large query is not blocking your inserts. Have you looked for lock =escalation events in your trace? If you are getting lock escalation, that =can block inserts, updates and deletes on your target table. =Mixing OLAP and OLTP can give you such problems. Check out KB article =32360, "INF: Resolving Blocking Problems That Are Caused by Lock Escalation in =SQL Server." If you subscribe to SQL Pro, check out my June 2003 column:

http://www.pinnaclepublishing.com/SQ/SQm=ag.nsf/0/80D93C0CC911C79C85256D32006D26BC?open&login= -- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Chris Birk" =wrote in message news:3f3bc0d9$1_1@.news.vic.com...We're attempting to determine why we periodically see single-record queriesrequiring up to 20 seconds to execute. We =configured SQL Profiler to logany query requiring at least 5 seconds to =complete, and what we're seeing isthat it typically occurs when a different =query involving large select orinsert (sometimes requiring 30 seconds =to execute) has begun execution justprior to the affected query, =although usually against a different table.We've eliminated blocking =as an issue, and have also taken steps to correctthe parallelism =problem (as this is a dual-processor machine). Upon runningperformance = monitor, we've noticed that at the instant when this =problemoccurs that the % Disk Time counter spikes (presumably due to the =largerquery), and that the Current Disk Queue Length goes from 0 to =anywherebetween 1 and 5. When the large query completes execution, the =otherqueries finish and the Current Disk Queue Length reverts to 0.My =question is, are we disk bound? It doesn't seem that one large =queryshould prevent others from completing execution, especially because =there'sno blocking and the other queries are typically against different tables.Are we missing a SQL configuration setting or is this =purely a hardwareissue? Are having the transaction logs on the same =disks as the DB causinga problem even though we use the Simple =Recovery Model? Our serverconfiguration is as follows:Dell 2650Dual 2.6GHz Xeon2GB RAMPERC-3 Controller(5) 73GB =Drives in RAID 5 configurationWindows 2000 SP3SQL Server 2000 =SP3The database is set to the Simple Recovery model, and Auto Create and AutoUpdate Statistics are both selected.Application is a =mixture of OLTP and OLAP; issue is able to be replicatedeven when server =volume is extremely light.Thanks,Chrisbirk@.xyznetwerkes.comIf =replying via e-mail, please remove the "xyz" from the address above.

---

--=_NextPart_000_005F_01C36267.B79FE9D0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0401_01C36270.C92F10F0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
I'm still curious about blocking. Can you run sp_who2 in a loop while =this is going on and check for blocked processes? Also, run DBCC =OPENTRAN for your database and see if there are any long-running =transactions.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Chris Birk" <birk@.xyznetwerkes.com> wrote in message =news:3f3bd410_2@.news.vic.com...
Tom,
Ahh, yes, size does matter. But in this particular instance smaller is =better. ;-)
Lock escalation revealed probably 5 - 10 intent share-locks per minute =but nothing which looked insidious because they were occurring on tables =which were not being used by the small queries.
I can further simplify the problem by saying that both the large and =small queries are typically selects, and don't have any tables in =common.
Thanks,
Chris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eP5Pb5oYDHA.4040@.tk2msftngp13.phx.gbl...
Don't take this the wrong way but I envy the avg length of your disk =queue. ;-) I have a 12-disk RAID10, which should not have an avg disk =queue length > 24. Can you say 100, 1000, 2500? Have I whined about =this to management? Yes. Have they done anything? No. :-(
Definitely add lock escalation to your profiler trace. We really have =to get the whole picture. I'm wondering about explicit transactions, =too. You may want to look at Data File Autogrow events on all =databases. It could be that you are having to wait for an autogrow =before you can do the insert.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Chris Birk" <birk@.xyznetwerkes.com> wrote in message =news:3f3bcc5a_2@.news.vic.com...
Tom,
Our avg disk queue lengths do send to be relatively small, typically =averaging under 1. The part of this issue which is confusing to me is =that the blocked queries can be single row select, inserts, and updates =being performed on a different table than the large query (which is =typically a select). Wouldn't this preclude it from being a lock =escalation issue? I will begin examining lock escalation events in =Profiler right away.
The current disk queue length intrigued me because I can watch the =performance monitor, see the current disk queue length climb above 0 for =a few seconds, and know that one or more SQL Profiler entries for a =simple query lasting more than 5 seconds, and one entry for a complex =query will be immediately forthcoming.
Thanks,
Chris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:urAP7eoYDHA.2592@.TK2MSFTNGP09.phx.gbl...
Your disk queue length should not be > 2 x # of disks =3D 2 x 5 =3D =10, so you're probably not disk bound. BTW, you should use avg disk =queue length, not current disk queue length. Also, you really shouldn't =have the transaction logs on the same drives as your data files. Place =them on separate arrays. Ideally, the logs should be on RAID1.
I would not be so sure that your large query is not blocking your =inserts. Have you looked for lock escalation events in your trace? If =you are getting lock escalation, that can block inserts, updates and =deletes on your target table. Mixing OLAP and OLTP can give you such =problems. Check out KB article 32360, "INF: Resolving Blocking Problems =That Are Caused by Lock Escalation in SQL Server." If you subscribe to =SQL Pro, check out my June 2003 column:
=http://www.pinnaclepublishing.com/SQ/SQmag.nsf/0/80D93C0CC911C79C85256D32=
006D26BC?open&login
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Chris Birk" <birk@.xyznetwerkes.com> wrote in message =news:3f3bc0d9$1_1@.news.vic.com...
We're attempting to determine why we periodically see single-record =queries
requiring up to 20 seconds to execute. We configured SQL Profiler =to log
any query requiring at least 5 seconds to complete, and what we're =seeing is
that it typically occurs when a different query involving large =select or
insert (sometimes requiring 30 seconds to execute) has begun =execution just
prior to the affected query, although usually against a different =table.
We've eliminated blocking as an issue, and have also taken steps to =correct
the parallelism problem (as this is a dual-processor machine). Upon =running
performance monitor, we've noticed that at the instant when this =problem
occurs that the % Disk Time counter spikes (presumably due to the =larger
query), and that the Current Disk Queue Length goes from 0 to =anywhere
between 1 and 5. When the large query completes execution, the =other
queries finish and the Current Disk Queue Length reverts to 0.
My question is, are we disk bound? It doesn't seem that one large =query
should prevent others from completing execution, especially because =there's
no blocking and the other queries are typically against different =tables.
Are we missing a SQL configuration setting or is this purely a =hardware
issue? Are having the transaction logs on the same disks as the DB =causing
a problem even though we use the Simple Recovery Model? Our server
configuration is as follows:
Dell 2650
Dual 2.6GHz Xeon
2GB RAM
PERC-3 Controller
(5) 73GB Drives in RAID 5 configuration
Windows 2000 SP3
SQL Server 2000 SP3
The database is set to the Simple Recovery model, and Auto Create =and Auto
Update Statistics are both selected.
Application is a mixture of OLTP and OLAP; issue is able to be =replicated
even when server volume is extremely light.
Thanks,
Chris
birk@.xyznetwerkes.com
If replying via e-mail, please remove the "xyz" from the address =above.
--=_NextPart_000_0401_01C36270.C92F10F0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

I'm still curious about =blocking. Can you run sp_who2 in a loop while this is going on and check for blocked processes? Also, run DBCC OPENTRAN for your database and see if =there are any long-running transactions.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Chris Birk" =wrote in message news:3f3bd410_2@.news.vic.com...=Tom,
Ahh, yes, size does matter. But =in this particular instance smaller is better. ;-)
Lock escalation revealed probably 5 - =10 intent share-locks per minute but nothing which looked insidious because they =were occurring on tables which were not being used by the small =queries.
I can further simplify the =problem by saying that both the large and small queries are typically selects, =and don't have any tables in common.
Thanks,Chris
"Tom Moreau" = wrote in message news:eP5Pb5oYDHA.4040=@.tk2msftngp13.phx.gbl...
Don't take this the wrong way but I =envy the avg length of your disk queue. ;-) I have a 12-disk =RAID10, which should not have an avg disk queue length > 24. Can you =say 100, 1000, 2500? Have I whined about this to management? =Yes. Have they done anything? No. :-(

Definitely add lock escalation to =your profiler trace. We really have to get the whole picture. I'm =wondering about explicit transactions, too. You may want to look at Data =File Autogrow events on all databases. It could be that you are =having to wait for an autogrow before you can do the insert.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Chris Birk" =wrote in message news:3f3bcc5a_2@.news.vic.com...= Tom,

Our avg disk queue lengths do send to =be relatively small, typically averaging under 1. The part of this =issue which is confusing to me is that the blocked queries can be single row =select, inserts, and updates being performed on a different table than the =large query (which is typically a select). Wouldn't this preclude it from =being a lock escalation issue? I will begin examining lock escalation =events in Profiler right away.

The current disk queue length =intrigued me because I can watch the performance monitor, see the current disk =queue length climb above 0 for a few seconds, and know that one or more SQL =Profiler entries for a simple query lasting more than 5 seconds, and one entry =for a complex query will be immediately forthcoming.

Thanks,Chris
"Tom Moreau" = wrote in message news:urAP7eoYDHA.2592=@.TK2MSFTNGP09.phx.gbl...
Your disk queue length should not =be > 2 x # of disks =3D 2 x 5 =3D 10, so you're probably not disk =bound. BTW, you should use avg disk queue length, not current disk queue =length. Also, you really shouldn't have the transaction logs on the same drives as =your data files. Place them on separate arrays. =Ideally, the logs should be on RAID1.

I would not be so sure that your =large query is not blocking your inserts. Have you looked for lock =escalation events in your trace? If you are getting lock escalation, that =can block inserts, updates and deletes on your target table. =Mixing OLAP and OLTP can give you such problems. Check out KB article =32360, "INF: Resolving Blocking Problems That Are Caused by Lock Escalation in =SQL Server." If you subscribe to SQL Pro, check out my June 2003 column:

http://www.pinnaclepublishing.com/SQ/SQm=ag.nsf/0/80D93C0CC911C79C85256D32006D26BC?open&login= -- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Chris Birk" =wrote in message news:3f3bc0d9$1_1@.news.vic.com...We're attempting to determine why we periodically see single-record queriesrequiring up to 20 seconds to execute. We =configured SQL Profiler to logany query requiring at least 5 seconds to =complete, and what we're seeing isthat it typically occurs when a different =query involving large select orinsert (sometimes requiring 30 seconds =to execute) has begun execution justprior to the affected query, =although usually against a different table.We've eliminated blocking =as an issue, and have also taken steps to correctthe parallelism =problem (as this is a dual-processor machine). Upon runningperformance = monitor, we've noticed that at the instant when this =problemoccurs that the % Disk Time counter spikes (presumably due to the =largerquery), and that the Current Disk Queue Length goes from 0 to =anywherebetween 1 and 5. When the large query completes execution, the =otherqueries finish and the Current Disk Queue Length reverts to 0.My =question is, are we disk bound? It doesn't seem that one large =queryshould prevent others from completing execution, especially because =there'sno blocking and the other queries are typically against different tables.Are we missing a SQL configuration setting or is this =purely a hardwareissue? Are having the transaction logs on the same =disks as the DB causinga problem even though we use the Simple =Recovery Model? Our serverconfiguration is as follows:Dell 2650Dual 2.6GHz Xeon2GB RAMPERC-3 Controller(5) 73GB =Drives in RAID 5 configurationWindows 2000 SP3SQL Server 2000 =SP3The database is set to the Simple Recovery model, and Auto Create and AutoUpdate Statistics are both selected.Application is a =mixture of OLTP and OLAP; issue is able to be replicatedeven when server =volume is extremely light.Thanks,Chrisbirk@.xyznetwerkes.comIf =replying via e-mail, please remove the "xyz" from the address above.

---

--=_NextPart_000_0401_01C36270.C92F10F0--|||Joe,
I replied previously, but don't see the post so I'll try it again. I
apologize if this ends up being a duplicate.
Our disk reads without a large query executing seems to hang in the 5 - 15
per second range. Nearly all of the problematic long queries are selects,
with the remainder being updates. All of our inserts are done by repeatedly
calling a single row insert stored proc from COM+.
Thanks,
Chris
"joe chang" <jchang6@.yahoo.com> wrote in message
news:0a8701c3628b$18acf470$a001280a@.phx.gbl...
> Q: w/o the large insert/select queries, what is the avg
> disk read/sec? if 0, then most of your data is in memory.
> so a heavy write action that does block the disk would not
> cause the single row select to be blocked on disk, but
> could be blocked for other reasons, ex. inability to
> acquire a lock as tom pointed out
> the data and log files should be on separate disks for
> best disk performance,
> RAID 1 for log, RAID 1+0 for data
> some arrays controller have absolutely horrible RAID5
> performance. the controller should be set to 100% write
> cache.
> are the large inserts of the type:
> INSERT tableA SELECT xx FROM TableA
> or a loop or cursor with single row inserts?
> try a ROWLOCK hint on the insert to inhibit escalation to
> a table lock (and also break up very large inserts into
> medium size inserts, 5-10k rows max)
> if single row inserts with a loop, try bracketing it with
> BEGIN/COMMIT TRAN
> w/o the tran, each single row insert causes a log write,
> while the TRAN insert log writes are consolidated
> >--Original Message--
> >We're attempting to determine why we periodically see
> single-record queries
> >requiring up to 20 seconds to execute. We configured SQL
> Profiler to log
> >any query requiring at least 5 seconds to complete, and
> what we're seeing is
> >that it typically occurs when a different query involving
> large select or
> >insert (sometimes requiring 30 seconds to execute) has
> begun execution just
> >prior to the affected query, although usually against a
> different table.
> >
> >We've eliminated blocking as an issue, and have also
> taken steps to correct
> >the parallelism problem (as this is a dual-processor
> machine). Upon running
> >performance monitor, we've noticed that at the instant
> when this problem
> >occurs that the % Disk Time counter spikes (presumably
> due to the larger
> >query), and that the Current Disk Queue Length goes from
> 0 to anywhere
> >between 1 and 5. When the large query completes
> execution, the other
> >queries finish and the Current Disk Queue Length reverts
> to 0.
> >
> >My question is, are we disk bound? It doesn't seem that
> one large query
> >should prevent others from completing execution,
> especially because there's
> >no blocking and the other queries are typically against
> different tables.
> >Are we missing a SQL configuration setting or is this
> purely a hardware
> >issue? Are having the transaction logs on the same disks
> as the DB causing
> >a problem even though we use the Simple Recovery Model?
> Our server
> >configuration is as follows:
> >
> >Dell 2650
> >Dual 2.6GHz Xeon
> >2GB RAM
> >PERC-3 Controller
> >(5) 73GB Drives in RAID 5 configuration
> >Windows 2000 SP3
> >SQL Server 2000 SP3
> >The database is set to the Simple Recovery model, and
> Auto Create and Auto
> >Update Statistics are both selected.
> >Application is a mixture of OLTP and OLAP; issue is able
> to be replicated
> >even when server volume is extremely light.
> >
> >Thanks,
> >
> >Chris
> >birk@.xyznetwerkes.com
> >If replying via e-mail, please remove the "xyz" from the
> address above.
> >
> >
> >.
> >|||This is a multi-part message in MIME format.
--=_NextPart_000_04BC_01C3627D.BAB08330
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
I'm wondering if you are memory starved, though 2GB should be =sufficient. Are any other apps running on the server? Is it a domain =controller? If you are memory starved, you may have to go to Advanced =Server 2000 and SQL Server 2000 Enterprise Edition. Also, have you =configured SQL Server to limit the amount of memory it can use? What do =you get when you run:
sp_configure 'max server memory (MB)'
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Chris Birk" <birk@.xyznetwerkes.com> wrote in message =news:3f3be546_1@.news.vic.com...
Tom,
DBCC OPENTRAN showed no long running transaction. I ran a long query =against one table (which timed out after 30 seconds), and a single row =select against another (which took 8 seconds took complete), and ran =sp_who2 every second during execution. No blocking was reported in the =results. It did take me three tries to get the short query to not =complete quickly, so it doesn't appear to happen every time a long query =is executed.
Thanks,
Chris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:uA1ZVJpYDHA.2484@.TK2MSFTNGP09.phx.gbl...
I'm still curious about blocking. Can you run sp_who2 in a loop while =this is going on and check for blocked processes? Also, run DBCC =OPENTRAN for your database and see if there are any long-running =transactions.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Chris Birk" <birk@.xyznetwerkes.com> wrote in message =news:3f3bd410_2@.news.vic.com...
Tom,
Ahh, yes, size does matter. But in this particular instance smaller =is better. ;-)
Lock escalation revealed probably 5 - 10 intent share-locks per minute =but nothing which looked insidious because they were occurring on tables =which were not being used by the small queries.
I can further simplify the problem by saying that both the large and =small queries are typically selects, and don't have any tables in =common.
Thanks,
Chris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eP5Pb5oYDHA.4040@.tk2msftngp13.phx.gbl...
Don't take this the wrong way but I envy the avg length of your disk =queue. ;-) I have a 12-disk RAID10, which should not have an avg disk =queue length > 24. Can you say 100, 1000, 2500? Have I whined about =this to management? Yes. Have they done anything? No. :-(
Definitely add lock escalation to your profiler trace. We really =have to get the whole picture. I'm wondering about explicit =transactions, too. You may want to look at Data File Autogrow events on =all databases. It could be that you are having to wait for an autogrow =before you can do the insert.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Chris Birk" <birk@.xyznetwerkes.com> wrote in message =news:3f3bcc5a_2@.news.vic.com...
Tom,
Our avg disk queue lengths do send to be relatively small, typically =averaging under 1. The part of this issue which is confusing to me is =that the blocked queries can be single row select, inserts, and updates =being performed on a different table than the large query (which is =typically a select). Wouldn't this preclude it from being a lock =escalation issue? I will begin examining lock escalation events in =Profiler right away.
The current disk queue length intrigued me because I can watch the =performance monitor, see the current disk queue length climb above 0 for =a few seconds, and know that one or more SQL Profiler entries for a =simple query lasting more than 5 seconds, and one entry for a complex =query will be immediately forthcoming.
Thanks,
Chris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:urAP7eoYDHA.2592@.TK2MSFTNGP09.phx.gbl...
Your disk queue length should not be > 2 x # of disks =3D 2 x 5 ==3D 10, so you're probably not disk bound. BTW, you should use avg disk =queue length, not current disk queue length. Also, you really shouldn't =have the transaction logs on the same drives as your data files. Place =them on separate arrays. Ideally, the logs should be on RAID1.
I would not be so sure that your large query is not blocking your =inserts. Have you looked for lock escalation events in your trace? If =you are getting lock escalation, that can block inserts, updates and =deletes on your target table. Mixing OLAP and OLTP can give you such =problems. Check out KB article 32360, "INF: Resolving Blocking Problems =That Are Caused by Lock Escalation in SQL Server." If you subscribe to =SQL Pro, check out my June 2003 column:
=http://www.pinnaclepublishing.com/SQ/SQmag.nsf/0/80D93C0CC911C79C85256D32=
006D26BC?open&login
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Chris Birk" <birk@.xyznetwerkes.com> wrote in message =news:3f3bc0d9$1_1@.news.vic.com...
We're attempting to determine why we periodically see =single-record queries
requiring up to 20 seconds to execute. We configured SQL Profiler =to log
any query requiring at least 5 seconds to complete, and what we're =seeing is
that it typically occurs when a different query involving large =select or
insert (sometimes requiring 30 seconds to execute) has begun =execution just
prior to the affected query, although usually against a different =table.
We've eliminated blocking as an issue, and have also taken steps =to correct
the parallelism problem (as this is a dual-processor machine). =Upon running
performance monitor, we've noticed that at the instant when this =problem
occurs that the % Disk Time counter spikes (presumably due to the =larger
query), and that the Current Disk Queue Length goes from 0 to =anywhere
between 1 and 5. When the large query completes execution, the =other
queries finish and the Current Disk Queue Length reverts to 0.
My question is, are we disk bound? It doesn't seem that one large =query
should prevent others from completing execution, especially =because there's
no blocking and the other queries are typically against different =tables.
Are we missing a SQL configuration setting or is this purely a =hardware
issue? Are having the transaction logs on the same disks as the =DB causing
a problem even though we use the Simple Recovery Model? Our =server
configuration is as follows:
Dell 2650
Dual 2.6GHz Xeon
2GB RAM
PERC-3 Controller
(5) 73GB Drives in RAID 5 configuration
Windows 2000 SP3
SQL Server 2000 SP3
The database is set to the Simple Recovery model, and Auto Create =and Auto
Update Statistics are both selected.
Application is a mixture of OLTP and OLAP; issue is able to be =replicated
even when server volume is extremely light.
Thanks,
Chris
birk@.xyznetwerkes.com
If replying via e-mail, please remove the "xyz" from the address =above.
--=_NextPart_000_04BC_01C3627D.BAB08330
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

I'm wondering if you are memory =starved, though 2GB should be sufficient. Are any other apps running on the =server? Is it a domain controller? If you are memory starved, you may have =to go to Advanced Server 2000 and SQL Server 2000 Enterprise Edition. =Also, have you configured SQL Server to limit the amount of memory it can =use? What do you get when you run:
sp_configure 'max server memory =(MB)'
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Chris Birk" =wrote in message news:3f3be546_1@.news.vic.com...=Tom,
DBCC OPENTRAN showed no long running transaction. I ran a long query against one table (which timed out =after 30 seconds), and a single row select against another (which took 8 =seconds took complete), and ran sp_who2 every second during execution. No =blocking was reported in the results. It did take me three tries to get the =short query to not complete quickly, so it doesn't appear to happen every time a =long query is executed.
Thanks,
Chris
"Tom Moreau" = wrote in message news:uA1ZVJpYDHA.2484=@.TK2MSFTNGP09.phx.gbl...
I'm still curious about =blocking. Can you run sp_who2 in a loop while this is going on and check for blocked processes? Also, run DBCC OPENTRAN for your database and see if =there are any long-running transactions.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Chris Birk" =wrote in message news:3f3bd410_2@.news.vic.com...= Tom,

Ahh, yes, size does matter. But =in this particular instance smaller is better. ;-)

Lock escalation revealed probably 5 - =10 intent share-locks per minute but nothing which looked insidious because they =were occurring on tables which were not being used by the small queries.

I can further simplify the =problem by saying that both the large and small queries are typically =selects, and don't have any tables in common.

Thanks,Chris
"Tom Moreau" = wrote in message news:eP5Pb5oYDHA.4040=@.tk2msftngp13.phx.gbl...
Don't take this the wrong way but =I envy the avg length of your disk queue. ;-) I have a =12-disk RAID10, which should not have an avg disk queue length > =24. Can you say 100, 1000, 2500? Have I whined about this to =management? Yes. Have they done anything? No. :-(

Definitely add lock escalation to =your profiler trace. We really have to get the whole picture. =I'm wondering about explicit transactions, too. You may want to =look at Data File Autogrow events on all databases. It could be that =you are having to wait for an autogrow before you can do the =insert.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Chris Birk" =wrote in message news:3f3bcc5a_2@.news.vic.com...= Tom,

Our avg disk queue lengths do send =to be relatively small, typically averaging under 1. The part of =this issue which is confusing to me is that the blocked queries can be single =row select, inserts, and updates being performed on a different table =than the large query (which is typically a select). Wouldn't this =preclude it from being a lock escalation issue? I will begin examining =lock escalation events in Profiler right away.

The current disk queue length =intrigued me because I can watch the performance monitor, see the current disk =queue length climb above 0 for a few seconds, and know that one or more =SQL Profiler entries for a simple query lasting more than 5 seconds, and =one entry for a complex query will be immediately =forthcoming.

Thanks,Chris
"Tom Moreau" = wrote in message news:urAP7eoYDHA.2592=@.TK2MSFTNGP09.phx.gbl...
Your disk queue length should =not be > 2 x # of disks =3D 2 x 5 =3D 10, so you're probably not disk =bound. BTW, you should use avg disk queue length, not current disk queue =length. Also, you really shouldn't have the transaction logs on the same =drives as your data files. Place them on separate arrays. =Ideally, the logs should be on RAID1.

I would not be so sure that your =large query is not blocking your inserts. Have you looked for lock =escalation events in your trace? If you are getting lock escalation, =that can block inserts, updates and deletes on your target table. =Mixing OLAP and OLTP can give you such problems. Check out KB article =32360, "INF: Resolving Blocking Problems That Are Caused by Lock =Escalation in SQL Server." If you subscribe to SQL Pro, check out my June =2003 column:

http://www.pinnaclepublishing.com/SQ/SQm=ag.nsf/0/80D93C0CC911C79C85256D32006D26BC?open&login= -- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, =SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Chris Birk" =wrote in message news:3f3bc0d9$1_1@.news.vic.com...We're attempting to determine why we periodically see single-record queriesrequiring up to 20 seconds to execute. We =configured SQL Profiler to logany query requiring at least 5 seconds to =complete, and what we're seeing isthat it typically occurs when a different =query involving large select orinsert (sometimes requiring 30 =seconds to execute) has begun execution justprior to the affected query, =although usually against a different table.We've eliminated =blocking as an issue, and have also taken steps to correctthe parallelism =problem (as this is a dual-processor machine). Upon =runningperformance monitor, we've noticed that at the instant when this =problemoccurs that the % Disk Time counter spikes (presumably due to the largerquery), and that the Current Disk Queue Length goes from =0 to anywherebetween 1 and 5. When the large query completes execution, the otherqueries finish and the Current Disk Queue =Length reverts to 0.My question is, are we disk bound? It =doesn't seem that one large queryshould prevent others from completing = execution, especially because there'sno blocking and the other =queries are typically against different tables.Are we missing a SQL configuration setting or is this purely a hardwareissue? =Are having the transaction logs on the same disks as the DB =causinga problem even though we use the Simple Recovery Model? Our serverconfiguration is as follows:Dell 2650Dual =2.6GHz Xeon2GB RAMPERC-3 Controller(5) 73GB Drives in RAID 5 configurationWindows 2000 SP3SQL Server 2000 SP3The =database is set to the Simple Recovery model, and Auto Create and =AutoUpdate Statistics are both selected.Application is a mixture of OLTP =and OLAP; issue is able to be replicatedeven when server volume is = extremely light.Thanks,Chrisbirk@.xyznetwerkes.comIf replying via e-mail, please remove the "xyz" from the address =above.

---

--=_NextPart_000_04BC_01C3627D.BAB08330--|||This is a multi-part message in MIME format.
--=_NextPart_000_00ED_01C3627C.963A0F40
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
There aren't any other apps running, and it's not a domain controller. =The sp_configure returns the full 2GB as the max. The more I look at =this, the less sense it makes.
Thanks,
Chris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eiGl59pYDHA.736@.TK2MSFTNGP09.phx.gbl...
I'm wondering if you are memory starved, though 2GB should be =sufficient. Are any other apps running on the server? Is it a domain =controller? If you are memory starved, you may have to go to Advanced =Server 2000 and SQL Server 2000 Enterprise Edition. Also, have you =configured SQL Server to limit the amount of memory it can use? What do =you get when you run:
sp_configure 'max server memory (MB)'
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Chris Birk" <birk@.xyznetwerkes.com> wrote in message =news:3f3be546_1@.news.vic.com...
Tom,
DBCC OPENTRAN showed no long running transaction. I ran a long query =against one table (which timed out after 30 seconds), and a single row =select against another (which took 8 seconds took complete), and ran =sp_who2 every second during execution. No blocking was reported in the =results. It did take me three tries to get the short query to not =complete quickly, so it doesn't appear to happen every time a long query =is executed.
Thanks,
Chris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:uA1ZVJpYDHA.2484@.TK2MSFTNGP09.phx.gbl...
I'm still curious about blocking. Can you run sp_who2 in a loop =while this is going on and check for blocked processes? Also, run DBCC =OPENTRAN for your database and see if there are any long-running =transactions.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Chris Birk" <birk@.xyznetwerkes.com> wrote in message =news:3f3bd410_2@.news.vic.com...
Tom,
Ahh, yes, size does matter. But in this particular instance smaller =is better. ;-)
Lock escalation revealed probably 5 - 10 intent share-locks per =minute but nothing which looked insidious because they were occurring on =tables which were not being used by the small queries.
I can further simplify the problem by saying that both the large and =small queries are typically selects, and don't have any tables in =common.
Thanks,
Chris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eP5Pb5oYDHA.4040@.tk2msftngp13.phx.gbl...
Don't take this the wrong way but I envy the avg length of your =disk queue. ;-) I have a 12-disk RAID10, which should not have an avg =disk queue length > 24. Can you say 100, 1000, 2500? Have I whined =about this to management? Yes. Have they done anything? No. :-(
Definitely add lock escalation to your profiler trace. We really =have to get the whole picture. I'm wondering about explicit =transactions, too. You may want to look at Data File Autogrow events on =all databases. It could be that you are having to wait for an autogrow =before you can do the insert.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Chris Birk" <birk@.xyznetwerkes.com> wrote in message =news:3f3bcc5a_2@.news.vic.com...
Tom,
Our avg disk queue lengths do send to be relatively small, =typically averaging under 1. The part of this issue which is confusing =to me is that the blocked queries can be single row select, inserts, and =updates being performed on a different table than the large query (which =is typically a select). Wouldn't this preclude it from being a lock =escalation issue? I will begin examining lock escalation events in =Profiler right away.
The current disk queue length intrigued me because I can watch the =performance monitor, see the current disk queue length climb above 0 for =a few seconds, and know that one or more SQL Profiler entries for a =simple query lasting more than 5 seconds, and one entry for a complex =query will be immediately forthcoming.
Thanks,
Chris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:urAP7eoYDHA.2592@.TK2MSFTNGP09.phx.gbl...
Your disk queue length should not be > 2 x # of disks =3D 2 x 5 ==3D 10, so you're probably not disk bound. BTW, you should use avg disk =queue length, not current disk queue length. Also, you really shouldn't =have the transaction logs on the same drives as your data files. Place =them on separate arrays. Ideally, the logs should be on RAID1.
I would not be so sure that your large query is not blocking =your inserts. Have you looked for lock escalation events in your trace? = If you are getting lock escalation, that can block inserts, updates and =deletes on your target table. Mixing OLAP and OLTP can give you such =problems. Check out KB article 32360, "INF: Resolving Blocking Problems =That Are Caused by Lock Escalation in SQL Server." If you subscribe to =SQL Pro, check out my June 2003 column:
=http://www.pinnaclepublishing.com/SQ/SQmag.nsf/0/80D93C0CC911C79C85256D32=
006D26BC?open&login
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Chris Birk" <birk@.xyznetwerkes.com> wrote in message =news:3f3bc0d9$1_1@.news.vic.com...
We're attempting to determine why we periodically see =single-record queries
requiring up to 20 seconds to execute. We configured SQL =Profiler to log
any query requiring at least 5 seconds to complete, and what =we're seeing is
that it typically occurs when a different query involving large =select or
insert (sometimes requiring 30 seconds to execute) has begun =execution just
prior to the affected query, although usually against a =different table.
We've eliminated blocking as an issue, and have also taken steps =to correct
the parallelism problem (as this is a dual-processor machine). =Upon running
performance monitor, we've noticed that at the instant when this =problem
occurs that the % Disk Time counter spikes (presumably due to =the larger
query), and that the Current Disk Queue Length goes from 0 to =anywhere
between 1 and 5. When the large query completes execution, the =other
queries finish and the Current Disk Queue Length reverts to 0.
My question is, are we disk bound? It doesn't seem that one =large query
should prevent others from completing execution, especially =because there's
no blocking and the other queries are typically against =different tables.
Are we missing a SQL configuration setting or is this purely a =hardware
issue? Are having the transaction logs on the same disks as the =DB causing
a problem even though we use the Simple Recovery Model? Our =server
configuration is as follows:
Dell 2650
Dual 2.6GHz Xeon
2GB RAM
PERC-3 Controller
(5) 73GB Drives in RAID 5 configuration
Windows 2000 SP3
SQL Server 2000 SP3
The database is set to the Simple Recovery model, and Auto =Create and Auto
Update Statistics are both selected.
Application is a mixture of OLTP and OLAP; issue is able to be =replicated
even when server volume is extremely light.
Thanks,
Chris
birk@.xyznetwerkes.com
If replying via e-mail, please remove the "xyz" from the address =above.
--=_NextPart_000_00ED_01C3627C.963A0F40
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

There aren't any other apps running, =and it's not a domain controller. The sp_configure returns the full 2GB as the =max. The more I look at this, the less sense it makes.
Thanks,
Chris
"Tom Moreau" = wrote in message news:eiGl59pYDHA.736@.T=K2MSFTNGP09.phx.gbl...
I'm wondering if you are memory =starved, though 2GB should be sufficient. Are any other apps running on the server? Is it a domain controller? If you are memory =starved, you may have to go to Advanced Server 2000 and SQL Server 2000 Enterprise Edition. Also, have you configured SQL Server to limit the =amount of memory it can use? What do you get when you run:

sp_configure 'max server =memory (MB)'

-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Chris Birk" =wrote in message news:3f3be546_1@.news.vic.com...= Tom,

DBCC OPENTRAN showed no long running transaction. I ran a long query against one table (which timed =out after 30 seconds), and a single row select against another (which took 8 =seconds took complete), and ran sp_who2 every second during execution. =No blocking was reported in the results. It did take me three tries =to get the short query to not complete quickly, so it doesn't appear to =happen every time a long query is executed.

Thanks,

Chris
"Tom Moreau" = wrote in message news:uA1ZVJpYDHA.2484=@.TK2MSFTNGP09.phx.gbl...
I'm still curious about =blocking. Can you run sp_who2 in a loop while this is going on and check for =blocked processes? Also, run DBCC OPENTRAN for your database and see =if there are any long-running transactions.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Chris Birk" =wrote in message news:3f3bd410_2@.news.vic.com...= Tom,

Ahh, yes, size does matter. =But in this particular instance smaller is better. ;-)

Lock escalation revealed probably 5 =- 10 intent share-locks per minute but nothing which looked insidious because =they were occurring on tables which were not being used by the small queries.

I can further simplify the =problem by saying that both the large and small queries are typically =selects, and don't have any tables in common.

Thanks,Chris
"Tom Moreau" = wrote in message news:eP5Pb5oYDHA.4040=@.tk2msftngp13.phx.gbl...
Don't take this the wrong way =but I envy the avg length of your disk queue. ;-) I have a =12-disk RAID10, which should not have an avg disk queue length > =24. Can you say 100, 1000, 2500? Have I whined about this to management? Yes. Have they done anything? =No. :-(

Definitely add lock escalation =to your profiler trace. We really have to get the whole =picture. I'm wondering about explicit transactions, too. You may want to =look at Data File Autogrow events on all databases. It could be that =you are having to wait for an autogrow before you can do the =insert.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, =SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Chris Birk" =wrote in message news:3f3bcc5a_2@.news.vic.com...= Tom,

Our avg disk queue lengths do =send to be relatively small, typically averaging under 1. The part of =this issue which is confusing to me is that the blocked queries can be =single row select, inserts, and updates being performed on a different =table than the large query (which is typically a select). Wouldn't this = preclude it from being a lock escalation issue? I will begin = examining lock escalation events in Profiler right =away.

The current disk queue length =intrigued me because I can watch the performance monitor, see the current disk =queue length climb above 0 for a few seconds, and know that one or more =SQL Profiler entries for a simple query lasting more than 5 seconds, =and one entry for a complex query will be immediately =forthcoming.

Thanks,Chris
"Tom Moreau" = wrote in message news:urAP7eoYDHA.2592=@.TK2MSFTNGP09.phx.gbl...
Your disk queue length should =not be > 2 x # of disks =3D 2 x 5 =3D 10, so you're probably not disk =bound. BTW, you should use avg disk queue length, not current disk =queue length. Also, you really shouldn't have the transaction =logs on the same drives as your data files. Place them on separate = arrays. Ideally, the logs should be on =RAID1.

I would not be so sure that =your large query is not blocking your inserts. Have you looked for =lock escalation events in your trace? If you are getting lock escalation, that can block inserts, updates and deletes on your =target table. Mixing OLAP and OLTP can give you such =problems. Check out KB article 32360, "INF: Resolving Blocking Problems =That Are Caused by Lock Escalation in SQL Server." If you subscribe =to SQL Pro, check out my June 2003 column:

http://www.pinnaclepublishing.com/SQ/SQm=ag.nsf/0/80D93C0CC911C79C85256D32006D26BC?open&login= -- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, =SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Chris Birk" =wrote in message news:3f3bc0d9$1_1@.news.vic.com...We're attempting to determine why we periodically see single-record queriesrequiring up to 20 seconds to execute. We =configured SQL Profiler to logany query requiring at least 5 seconds to = complete, and what we're seeing isthat it typically occurs =when a different query involving large select orinsert (sometimes =requiring 30 seconds to execute) has begun execution justprior to the =affected query, although usually against a different table.We've eliminated blocking as an issue, and have also taken steps to correctthe parallelism problem (as this is a dual-processor machine). Upon runningperformance monitor, we've =noticed that at the instant when this problemoccurs that the % Disk Time =counter spikes (presumably due to the largerquery), and that the =Current Disk Queue Length goes from 0 to anywherebetween 1 and =5. When the large query completes execution, the otherqueries finish =and the Current Disk Queue Length reverts to 0.My question is, =are we disk bound? It doesn't seem that one large queryshould =prevent others from completing execution, especially because =there'sno blocking and the other queries are typically against different tables.Are we missing a SQL configuration setting or is this =purely a hardwareissue? Are having the transaction logs on =the same disks as the DB causinga problem even though we use the =Simple Recovery Model? Our serverconfiguration is as follows:Dell 2650Dual 2.6GHz Xeon2GB =RAMPERC-3 Controller(5) 73GB Drives in RAID 5 configurationWindows =2000 SP3SQL Server 2000 SP3The database is set to the Simple =Recovery model, and Auto Create and AutoUpdate Statistics are both selected.Application is a mixture of OLTP and OLAP; issue is =able to be replicatedeven when server volume is extremely light.Thanks,Chrisbirk@.xyznetwerkes.comIf replying via e-mail, please remove the "xyz" from the address =above.=

---

--=_NextPart_000_00ED_01C3627C.963A0F40--|||Chris,
If your Disk Queue backs up then yes you are diskbound. However, you may be
diskbound because of an inefficiency. Fragmented indexes, out of data
statistics, etc. can cause excessive cpu usage and excessive disk I/O. Check
the query plans on your suspect queries and run DBCC SHOWCONTIG to make
sure the indexes and statistics are ok.
There are other performance counters that also point to insufficient disk
bandwidth - Latch Waits/sec. and Avg. Latch Wait Time.
Hope this helps,
Bill
"Chris Birk" <birk@.xyznetwerkes.com> wrote in message
news:3f3be719$1_1@.news.vic.com...
> Joe,
> I replied previously, but don't see the post so I'll try it again. I
> apologize if this ends up being a duplicate.
> Our disk reads without a large query executing seems to hang in the 5 - 15
> per second range. Nearly all of the problematic long queries are selects,
> with the remainder being updates. All of our inserts are done by
repeatedly
> calling a single row insert stored proc from COM+.
> Thanks,
> Chris
> "joe chang" <jchang6@.yahoo.com> wrote in message
> news:0a8701c3628b$18acf470$a001280a@.phx.gbl...
> > Q: w/o the large insert/select queries, what is the avg
> > disk read/sec? if 0, then most of your data is in memory.
> > so a heavy write action that does block the disk would not
> > cause the single row select to be blocked on disk, but
> > could be blocked for other reasons, ex. inability to
> > acquire a lock as tom pointed out
> >
> > the data and log files should be on separate disks for
> > best disk performance,
> > RAID 1 for log, RAID 1+0 for data
> > some arrays controller have absolutely horrible RAID5
> > performance. the controller should be set to 100% write
> > cache.
> >
> > are the large inserts of the type:
> > INSERT tableA SELECT xx FROM TableA
> > or a loop or cursor with single row inserts?
> > try a ROWLOCK hint on the insert to inhibit escalation to
> > a table lock (and also break up very large inserts into
> > medium size inserts, 5-10k rows max)
> > if single row inserts with a loop, try bracketing it with
> > BEGIN/COMMIT TRAN
> > w/o the tran, each single row insert causes a log write,
> > while the TRAN insert log writes are consolidated
> >
> > >--Original Message--
> > >We're attempting to determine why we periodically see
> > single-record queries
> > >requiring up to 20 seconds to execute. We configured SQL
> > Profiler to log
> > >any query requiring at least 5 seconds to complete, and
> > what we're seeing is
> > >that it typically occurs when a different query involving
> > large select or
> > >insert (sometimes requiring 30 seconds to execute) has
> > begun execution just
> > >prior to the affected query, although usually against a
> > different table.
> > >
> > >We've eliminated blocking as an issue, and have also
> > taken steps to correct
> > >the parallelism problem (as this is a dual-processor
> > machine). Upon running
> > >performance monitor, we've noticed that at the instant
> > when this problem
> > >occurs that the % Disk Time counter spikes (presumably
> > due to the larger
> > >query), and that the Current Disk Queue Length goes from
> > 0 to anywhere
> > >between 1 and 5. When the large query completes
> > execution, the other
> > >queries finish and the Current Disk Queue Length reverts
> > to 0.
> > >
> > >My question is, are we disk bound? It doesn't seem that
> > one large query
> > >should prevent others from completing execution,
> > especially because there's
> > >no blocking and the other queries are typically against
> > different tables.
> > >Are we missing a SQL configuration setting or is this
> > purely a hardware
> > >issue? Are having the transaction logs on the same disks
> > as the DB causing
> > >a problem even though we use the Simple Recovery Model?
> > Our server
> > >configuration is as follows:
> > >
> > >Dell 2650
> > >Dual 2.6GHz Xeon
> > >2GB RAM
> > >PERC-3 Controller
> > >(5) 73GB Drives in RAID 5 configuration
> > >Windows 2000 SP3
> > >SQL Server 2000 SP3
> > >The database is set to the Simple Recovery model, and
> > Auto Create and Auto
> > >Update Statistics are both selected.
> > >Application is a mixture of OLTP and OLAP; issue is able
> > to be replicated
> > >even when server volume is extremely light.
> > >
> > >Thanks,
> > >
> > >Chris
> > >birk@.xyznetwerkes.com
> > >If replying via e-mail, please remove the "xyz" from the
> > address above.
> > >
> > >
> > >.
> > >
>