SQL7 server crawls (locks) when deleting records from a 3 million record table

SQL7 server crawls (locks) when deleting records from a 3 million record table

Post by birdi » Sun, 08 Apr 2001 00:54:04



Is the above normal?  Is there a workaround?
It happens as well when a select statement is done thru the IIS ODBC log on my
server which averages about 3 million record.

My server is a dual pentium II 600Mhz with 1GB ram running a 20GB raid-5 array.

Thanks.

 
 
 

SQL7 server crawls (locks) when deleting records from a 3 million record table

Post by Keith Kratochvi » Sun, 08 Apr 2001 01:16:58


You do not mention if you are deleting one row, a range, or the entire
table.
Your question on select statements:  Do you query all data, or do you pass
some criteria to it?  If you are passing criteria, do you have the proper
indexes on the table?  What happens when you query the table from Query
Analyzer?  Same (slow) performance?

Is your server a dedicated SQL box, or are you running IIS on it to?  I ask,
because I wonder how much memory/cpu/disk SQL Server is able to use during
the actions (delete and select).

--
Keith
==============
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Is the above normal?  Is there a workaround?
It happens as well when a select statement is done thru the IIS ODBC log on
my
server which averages about 3 million record.

My server is a dual pentium II 600Mhz with 1GB ram running a 20GB raid-5
array.

Thanks.

 
 
 

SQL7 server crawls (locks) when deleting records from a 3 million record table

Post by birdi » Sun, 08 Apr 2001 01:46:10


Quote:>You do not mention if you are deleting one row, a range, or the entire
>table.

Deleting a date range (1 out of 3 days log actually)

Quote:>Your question on select statements:  Do you query all data, or do you pass
>some criteria to it?

Select by date range

Quote:>If you are passing criteria, do you have the proper indexes on the table?

No

Quote:>What happens when you query the table from Query Analyzer?  Same (slow)

performance?

Everything else which tries to query the server times out or lock up (e.g.
Enterprise Manager)

Quote:>Is your server a dedicated SQL box, or are you running IIS on it to?  I ask,
>because I wonder how much memory/cpu/disk SQL Server is able to use during
>the actions (delete and select).

It's a dedicated SQL box.  No memory limit set on the SQL Server.  Performance of
select or delete statement is not as much an issue as the website or other
programs not being serviced.

Thanks again.

 
 
 

SQL7 server crawls (locks) when deleting records from a 3 million record table

Post by Keith Kratochvi » Sun, 08 Apr 2001 05:58:15


If you are selecting and deleting by a date range, maybe you could create a
clustered index on your date colum.

--
Keith
==============
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:>You do not mention if you are deleting one row, a range, or the entire
>table.

Deleting a date range (1 out of 3 days log actually)

Quote:>Your question on select statements:  Do you query all data, or do you pass
>some criteria to it?

Select by date range

Quote:>If you are passing criteria, do you have the proper indexes on the table?

No

Quote:>What happens when you query the table from Query Analyzer?  Same (slow)

performance?

Everything else which tries to query the server times out or lock up (e.g.
Enterprise Manager)

Quote:>Is your server a dedicated SQL box, or are you running IIS on it to?  I
ask,
>because I wonder how much memory/cpu/disk SQL Server is able to use during
>the actions (delete and select).

It's a dedicated SQL box.  No memory limit set on the SQL Server.
Performance of
select or delete statement is not as much an issue as the website or other
programs not being serviced.

Thanks again.

 
 
 

1. Performance problem in fetching record from table containg 2 million record

Hi
  when I am fetching record using sequential cursor from a table
containing 2 million records  I am getting very much performance
problem.
In the begning it is very fast in milliseconds and after 15 minute it
takes 15 seconds in fetching  a record.
after 1 hour and so it is taking 1.5 minute in fetching each record.
Why it is so. What should i do to improve performance Pls help !!!!!

One more problem I am getting In BEGIN TRANSCATION command it is taking
5 minute pls help....

thanks
ramesh

Sent via Deja.com http://www.deja.com/
Before you buy.

2. Replication or Direct Connection?

3. Cannot add record to Visual Foxpro table after deleting all records in table

4. Problem with Blobs

5. Sql server behavior on UPDATE of Tables containing more than 3 Million records

6. Getting past the Exception java.lang.ClassNotFoundException: OiQueryFileRes - Oracle 8.1.5.0.0

7. Millions of Record in SQL Server Table

8. How do I get the value of a custom database property using ADO?

9. SQL server database table with 5 million records

10. 10 Million records in 1 SQL Server table

11. SQL server database table with 5 million records

12. Delete Millions of Duplicate Records

13. Quickest way to Delete Over a Million Records