queries running very slow on a particular table

I have a web application that hits this database 24/7. I have an orders table
and any query that I run on this table is very slow. A simple select query
also runs very slow. This was working fine until this morning. All the other
tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still no
good. Please advise.Have you looked at what the query plan shows for the query on the orders
table? It could be the statistics need to be updated, however the dbcc
dbreindex should have handled that. You may need to run a profiler to
see if the problem is with the table access or perhaps temp tables.
Shahryar
Beginner wrote:
>I have a web application that hits this database 24/7. I have an orders table
>and any query that I run on this table is very slow. A simple select query
>also runs very slow. This was working fine until this morning. All the other
>tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still no
>good. Please advise.
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.|||Any blocking going on?
run sp_who2 and look for the BlkBy column
http://sqlservercode.blogspot.com/

queries running very slow on a particular table

I have a web application that hits this database 24/7. I have an orders tabl
e
and any query that I run on this table is very slow. A simple select query
also runs very slow. This was working fine until this morning. All the other
tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still no
good. Please advise.Have you looked at what the query plan shows for the query on the orders
table? It could be the statistics need to be updated, however the dbcc
dbreindex should have handled that. You may need to run a profiler to
see if the problem is with the table access or perhaps temp tables.
Shahryar
Beginner wrote:

>I have a web application that hits this database 24/7. I have an orders tab
le
>and any query that I run on this table is very slow. A simple select query
>also runs very slow. This was working fine until this morning. All the othe
r
>tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still n
o
>good. Please advise.
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is
legally privileged. The information is solely for the use of the intended
recipient(s); any disclosure, copying, distribution, or other use of this in
formation is strictly prohi
bited. If you have received this e-mail in error, please notify the sender
by return e-mail and delete this message. Thank you.|||Any blocking going on?
run sp_who2 and look for the BlkBy column
http://sqlservercode.blogspot.com/sql

queries running very slow on a particular table

I have a web application that hits this database 24/7. I have an orders table
and any query that I run on this table is very slow. A simple select query
also runs very slow. This was working fine until this morning. All the other
tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still no
good. Please advise.
Have you looked at what the query plan shows for the query on the orders
table? It could be the statistics need to be updated, however the dbcc
dbreindex should have handled that. You may need to run a profiler to
see if the problem is with the table access or perhaps temp tables.
Shahryar
Beginner wrote:

>I have a web application that hits this database 24/7. I have an orders table
>and any query that I run on this table is very slow. A simple select query
>also runs very slow. This was working fine until this morning. All the other
>tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still no
>good. Please advise.
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.
|||Any blocking going on?
run sp_who2 and look for the BlkBy column
http://sqlservercode.blogspot.com/

Queries on linked server are very slow.

This is a multi-part message in MIME format.
--=_NextPart_000_0063_01C35A9F.BBD8E6C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi All,
We have been using linked servers for the past 3 years, and we never had = any issues with the linked servers.
Now suddenly all the queries against the linkes servers are very very = slow, Some queries takes 4 hours to run before they were only taking 5 = secondsa to run.
I have no idea what is causing this delay.
Can any of you please help,
Thanks
Raju
--=_NextPart_000_0063_01C35A9F.BBD8E6C0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi All,

We have been using linked servers for the past 3 = years, and we never had any issues with the linked servers.

Now suddenly all the queries against the linkes = servers are very very slow, Some queries takes 4 hours to run before they were = only taking 5 secondsa to run.

I have no idea what is causing this = delay.

Can any of you please help,

Thanks
Raju
--=_NextPart_000_0063_01C35A9F.BBD8E6C0--This is a multi-part message in MIME format.
--=_NextPart_000_09CC_01C35B33.D6EE3160
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Raju,
of course you need to look at the obvious changes -- what changes have =been made in your systems and network that may cause this.
On the sql server side, you can run profiler to catch the long running =queries and analyze the results.
More information is needed for the folks here to join your trouble =shooting.
Quentin
"Raju" <npraju1@.hotmail.com> wrote in message =news:uWFSoptWDHA.2040@.TK2MSFTNGP11.phx.gbl...
Hi All,
We have been using linked servers for the past 3 years, and we never =had any issues with the linked servers.
Now suddenly all the queries against the linkes servers are very very =slow, Some queries takes 4 hours to run before they were only taking 5 =secondsa to run.
I have no idea what is causing this delay.
Can any of you please help,
Thanks
Raju
--=_NextPart_000_09CC_01C35B33.D6EE3160
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Raju,
of course you need to look at the =obvious changes -- what changes have been made in your systems and network that may =cause this.
On the sql server side, you can run =profiler to catch the long running queries and analyze the results. =
More information is needed for the =folks here to join your trouble shooting.
Quentin
"Raju" wrote =in message news:uWFSoptWDHA.2040=@.TK2MSFTNGP11.phx.gbl...
Hi All,

We have been using linked servers for the past =3 years, and we never had any issues with the linked servers.

Now suddenly all the queries against the =linkes servers are very very slow, Some queries takes 4 hours to run before they were =only taking 5 secondsa to run.

I have no idea what is causing this =delay.

Can any of you please help,

Thanks
Raju

---

--=_NextPart_000_09CC_01C35B33.D6EE3160--|||This is a multi-part message in MIME format.
--=_NextPart_000_0135_01C35CC8.907BE880
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I did use sp_who2 active, I don't see any blocks.

I tried linking the same server to different server and ran the query, =it worked fine.

Net work everything looks fine, I am suspecting it has to do with =protocols but I am not sure.

Even if it is a protocol issue have no idea how to troubleshoot and fix =the protocol related problems.


Thanks
Raju
"Quentin Ran" <ab@.who.com> wrote in message =news:ODdoo11WDHA.2424@.TK2MSFTNGP12.phx.gbl...
Raju,
of course you need to look at the obvious changes -- what changes have =been made in your systems and network that may cause this.
On the sql server side, you can run profiler to catch the long running =queries and analyze the results.
More information is needed for the folks here to join your trouble =shooting.
Quentin
"Raju" <npraju1@.hotmail.com> wrote in message =news:uWFSoptWDHA.2040@.TK2MSFTNGP11.phx.gbl...
Hi All,
We have been using linked servers for the past 3 years, and we never =had any issues with the linked servers.
Now suddenly all the queries against the linkes servers are very =very slow, Some queries takes 4 hours to run before they were only =taking 5 secondsa to run.
I have no idea what is causing this delay.
Can any of you please help,
Thanks
Raju
--=_NextPart_000_0135_01C35CC8.907BE880
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

I did use sp_who2 active, =I don't see any blocks.
I tried linking the same =server to different server and ran the query, it worked =fine.
Net work everything looks =fine, I am suspecting it has to do with protocols but I am not =sure.
Even if it is a protocol =issue have no idea how to troubleshoot and fix the protocol related problems.
Thanks
Raju
"Quentin Ran" wrote in message news:ODdoo11WDHA.2424=@.TK2MSFTNGP12.phx.gbl...
Raju,

of course you need to look at the =obvious changes -- what changes have been made in your systems and network that may =cause this.

On the sql server side, you can run =profiler to catch the long running queries and analyze the results. =
More information is needed for the =folks here to join your trouble shooting.

Quentin

"Raju" =wrote in message news:uWFSoptWDHA.2040=@.TK2MSFTNGP11.phx.gbl...
Hi All,

We have been using linked servers for the =past 3 years, and we never had any issues with the linked =servers.

Now suddenly all the queries against the =linkes servers are very very slow, Some queries takes 4 hours to run before =they were only taking 5 secondsa to run.

I have no idea what is causing this delay.

Can any of you please help,

Thanks
Raju

---

--=_NextPart_000_0135_01C35CC8.907BE880--sql

Queries are slow when accessed from remote machine

Hi,

I have succesfully created a Stored Procedure which runs under 2 seconds locally.

However when i run the same proc from another machine in the LAN, the response times vary from 5 sec to over 40 Secs and even occassionally times out.

My server is SQL 2005 Dev Edition (32 Bit) running on a Dual Core Box with 2GB memory.

Any Ideas why this would be happening?

Does the query return a lot of data? If so, it is very likely that network latency and bandwidth are the bottleneck, since the results have to be sent over the network back to the client.

How many rows are you returning? You can turn on Client Statistics in SSMS, and see how much data (in bytes) is being returned to the client (assuming you are calling the SP from SSMS on one machine, talking to a remote server).

|||

Thanks for the reply.

It is returning about 400k. But what is interesting is that, even that delay is not consistant ( from 5 sec to over 40sec, when i have run over 100 tests) The other machine is on the same lan with 100Mbps network card. I couldnt also see any significant rise in network utilization in both the machines

|||Did you ever resolve this? It really sounds like a network issue.|||Yes. It turns out that the SSRS was in a web farm scenario. I was checking only one server. duh!!!|||

Hi, me too have the same problem:

I made a migration from SQL2000 to SQL2005. Before migration, both IIS and SQL were on the same server and performance was good.

We decided to split application server (IIS) from db server (SQL). Now the wait time to display page is 5/10 more high.

We made migration of db (backup/restore, detach/attach), rebuilded indexes and compiled store procedure.

Both server are in the same LAN, switched to 1Gb

Some help?

Regards

vito

Queries are slow when accessed from remote machine

Hi,

I have succesfully created a Stored Procedure which runs under 2 seconds locally.

However when i run the same proc from another machine in the LAN, the response times vary from 5 sec to over 40 Secs and even occassionally times out.

My server is SQL 2005 Dev Edition (32 Bit) running on a Dual Core Box with 2GB memory.

Any Ideas why this would be happening?

Does the query return a lot of data? If so, it is very likely that network latency and bandwidth are the bottleneck, since the results have to be sent over the network back to the client.

How many rows are you returning? You can turn on Client Statistics in SSMS, and see how much data (in bytes) is being returned to the client (assuming you are calling the SP from SSMS on one machine, talking to a remote server).

|||

Thanks for the reply.

It is returning about 400k. But what is interesting is that, even that delay is not consistant ( from 5 sec to over 40sec, when i have run over 100 tests) The other machine is on the same lan with 100Mbps network card. I couldnt also see any significant rise in network utilization in both the machines

|||Did you ever resolve this? It really sounds like a network issue.|||Yes. It turns out that the SSRS was in a web farm scenario. I was checking only one server. duh!!!|||

Hi, me too have the same problem:

I made a migration from SQL2000 to SQL2005. Before migration, both IIS and SQL were on the same server and performance was good.

We decided to split application server (IIS) from db server (SQL). Now the wait time to display page is 5/10 more high.

We made migration of db (backup/restore, detach/attach), rebuilded indexes and compiled store procedure.

Both server are in the same LAN, switched to 1Gb

Some help?

Regards

vito

q; how to improve this transaction

Hello,
I have four different transactions such as below and I do one insert and one
update in each transaction and it seem it is slow and creates deadlock with
the user interface.
These transactions are performed against the tables that users are accessing
with another user interface. I have following two questions:
1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did
not put index since only two distinct values. Should I put indexes on these
fields?
2. Can I make this transaction let user interface do its task in case
accessing the same rows, I can start transaction again but I do not want
users get disturbed?
.
BEGIN TRANSACTION pTrans
BEGIN
INSERT INTO T1
(fields)
SELECT (fields)
FROM T2 INNER JOIN View1 ON T2.TrID = View1.MyTableID
WHERE (T2.TextField1 = @.TrType AND T2.TextField2 = @.TextField2)
UPDATE T2
SET TextField2 = 'Ok', TextField2Date=@.MyRunDateTime
FROM T2
WHERE (TextField1 = @.TrType AND TextField2 = @.TextField2)
IF @.@.ERROR <> 0
BEGIN
rollback transaction pTrans
return(-1)
END
ELSE
BEGIN
commit transaction pTrans
END
ENDadd a composite, non-clustered index on textfield1,textfield2.
otherwise, you will have to do a table scan for each update.
>|||On Wed, 12 Jul 2006 16:19:01 -0700, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>I have four different transactions such as below and I do one insert and one
>update in each transaction and it seem it is slow and creates deadlock with
>the user interface.
>These transactions are performed against the tables that users are accessing
>with another user interface. I have following two questions:
>1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did
>not put index since only two distinct values. Should I put indexes on these
>fields?
>2. Can I make this transaction let user interface do its task in case
>accessing the same rows, I can start transaction again but I do not want
>users get disturbed?
How long does the transaction take if all alone on the machine?
How much IO does it do (from profiler or "set statistic io on" in
query analyzer)?
Are you familiar with the nolock hint?
Do you have access to the code for the other program accessing the
database? That's the one that might need the nolock.
You might want to do the selects for one or both statements (the
update does an implicit select) and put the results into temp files or
@.tables, then insert and update from them, to minimize locking times.
Depends on times and data volumes and PKs.
Josh

q; how to improve this transaction

Hello,
I have four different transactions such as below and I do one insert and one
update in each transaction and it seem it is slow and creates deadlock with
the user interface.
These transactions are performed against the tables that users are accessing
with another user interface. I have following two questions:
1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did
not put index since only two distinct values. Should I put indexes on these
fields?
2. Can I make this transaction let user interface do its task in case
accessing the same rows, I can start transaction again but I do not want
users get disturbed?
.
BEGIN TRANSACTION pTrans
BEGIN
INSERT INTO T1
(fields)
SELECT (fields)
FROM T2 INNER JOIN View1 ON T2.TrID = View1.MyTableID
WHERE (T2.TextField1 = @.TrType AND T2.TextField2 = @.TextField2)
UPDATE T2
SET TextField2 = 'Ok', TextField2Date=@.MyRunDateTime
FROM T2
WHERE (TextField1 = @.TrType AND TextField2 = @.TextField2)
IF @.@.ERROR <> 0
BEGIN
rollback transaction pTrans
return(-1)
END
ELSE
BEGIN
commit transaction pTrans
END
ENDadd a composite, non-clustered index on textfield1,textfield2.
otherwise, you will have to do a table scan for each update.
>|||On Wed, 12 Jul 2006 16:19:01 -0700, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>I have four different transactions such as below and I do one insert and on
e
>update in each transaction and it seem it is slow and creates deadlock with
>the user interface.
>These transactions are performed against the tables that users are accessin
g
>with another user interface. I have following two questions:
>1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did
>not put index since only two distinct values. Should I put indexes on these
>fields?
>2. Can I make this transaction let user interface do its task in case
>accessing the same rows, I can start transaction again but I do not want
>users get disturbed?
How long does the transaction take if all alone on the machine?
How much IO does it do (from profiler or "set statistic io on" in
query analyzer)?
Are you familiar with the nolock hint?
Do you have access to the code for the other program accessing the
database? That's the one that might need the nolock.
You might want to do the selects for one or both statements (the
update does an implicit select) and put the results into temp files or
@.tables, then insert and update from them, to minimize locking times.
Depends on times and data volumes and PKs.
Josh