[dba-SQLServer] Processing diverse where clauses

jwcolby jwcolby at colbyconsulting.com
Wed May 16 12:15:40 CDT 2007


In the future I might try a subquery. 

John W. Colby
Colby Consulting
www.ColbyConsulting.com
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Jim
Lawrence
Sent: Wednesday, May 16, 2007 12:59 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Processing diverse where clauses

Hi John:

The IN statement usually is a pig on resources.

Jim

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, May 16, 2007 8:47 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Processing diverse where clauses

I had never seen this machine "lock up".  It is a dual core 3.8G with 4 g of
RAM and even when the cores are pegged it will usually switch tasks and
stuff.  And of course I can't absolutely 100% determine that SQL Server is
the cause.  The lockup happened last night late, at the very end of a query
that pulled a count based on the four queries mentioned in other emails.  It
happened again this morning, again at the very end of the same query.  

When it happens, the task manager shows 100% cpu utilization (for both
cores).  Unfortunately I did not get a chance to go check which task was
using what of that 100%.  It locked it up so tight that I couldn't even
switch between tasks.  As soon as the query completed, the machine returned
to normal so I still assume that it was SQL Server.

Again though, what does the IN clause have to do with the network.  You
still are not explaining that.  From my understanding, this is all happening
on one machine and so I am curious what is being passed over the network,
and to whom?

John W. Colby
Colby Consulting
www.ColbyConsulting.com
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Jim
Lawrence
Sent: Wednesday, May 16, 2007 11:32 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Processing diverse where clauses

Hi john:

This type of statement 'IN' may grind a network of users to a stand still
but you are the only user so who cares but... Maybe it was a time-out
issue.... but to lock it up?? Wow, never heard of that happening. 

Have you tried nested queries?

Jim

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, May 16, 2007 7:39 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Processing diverse where clauses

I can tell you that at the very end it locked up my dual proc machine as it
pulls the results all together.  I have never seen that happen before.

Aside from that, what does the network have to do with anything?  This is
running in SQL Server 2005, using tables / drives on the same machine.


John W. Colby
Colby Consulting
www.ColbyConsulting.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list