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"
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"
--=_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"
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"
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"
--=_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"
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"
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"
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"
--=_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"
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"
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"
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"
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"
--=_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"
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"
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"
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"
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"
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"
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"
--=_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"
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"
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"
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"
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"
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"
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"
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"
--=_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.
> > >
> > >
> > >.
> > >
>
No comments:
Post a Comment