Prev: Get minimum from table
Next: Outer Join showing default values instead of Null values
From: Dan Guzman on 31 Jan 2010 09:45
> I have a sql server with many dbs. I don't get why when I delete a long
> running query(about 1 or 2 minutes) to complete, but while this is
> running, my other separate db on the same server hangs on some queries.

From your description, it seems that the table is large and SQL Server may
be using a parallel plan to compensate for lack of useful indexes. Such a
resource-intensive query will affect performance of other queries on the
server.

I would expect that a delete of 2K rows would take no longer than a few
seconds with an optimized query regardless of table size. Check the DELETE
execution plan for optimization opportunities. Ensure an index is available
to optimize the DELETE query and that expressions in the WHERE clause are
sargable. For example, if your query is like:

DELETE FROM dbo.Orders
WHERE DATEDIFF(day, OrderEntryDate, CAST(GETDATE() AS date)) > 30;

Ensure you have an index on OrderEntryDate and refactor as a sargable
expression:

DELETE FROM dbo.Orders
WHERE OrderEntryDate < DATEADD(day, -30, CAST(GETDATE() AS date));

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/


"link285" <user(a)msgroups.net/> wrote in message
news:eQBqxIloKHA.3948(a)TK2MSFTNGP06.phx.gbl...
> Hi,
> I was deleting about 2k rows. But that's not really my concern. If I'm
> deleting from a table I don't want it to timeout a separate db's query. I
> can understand if it locks the same table but...
>
> ---
> frmsrcurl:
> http://msgroups.net/microsoft.public.sqlserver.programming/Sql-Server-hang-when-deleting-many-records

First  |  Prev  | 
Pages: 1 2
Prev: Get minimum from table
Next: Outer Join showing default values instead of Null values