How can I avoid table lock in SQL2000?

To any and all;

I have a very large table (16Mil+ records), for which I want to delete
about 8 Million records. There are a few indexes on this table.

Is there a way that I can run either a query or a series of queries
that will run against each record and delete based on criteria (date)?
If I do a single DELETE query, it will take forever, lock the table,
and my app that runs against it will stop INSERTING, which is bad.

If I do a cursor, I think it locks the table also, so that won't do,
right?

Any help would be appreciated.

Glenn Dekhayser
Contentcatcher.comOn 15 Feb 2005 13:19:01 -0800, "gdekhayser" <gdekhayser@.voyantinc.com> wrote:

>To any and all;
>I have a very large table (16Mil+ records), for which I want to delete
>about 8 Million records. There are a few indexes on this table.
>Is there a way that I can run either a query or a series of queries
>that will run against each record and delete based on criteria (date)?
> If I do a single DELETE query, it will take forever, lock the table,
>and my app that runs against it will stop INSERTING, which is bad.
>If I do a cursor, I think it locks the table also, so that won't do,
>right?
>Any help would be appreciated.
>Glenn Dekhayser
>Contentcatcher.com

With a table that size, have you thought of partitioning it by date? Then,
locks would be held only on the partition(s) that overlap the date range you
are deleting.|||Right, but I need to deal with the records in place before I can do
that...don't I? Once I get the table down in size to something
rational, then I can partition it...|||gdekhayser (gdekhayser@.voyantinc.com) writes:
> I have a very large table (16Mil+ records), for which I want to delete
> about 8 Million records. There are a few indexes on this table.
> Is there a way that I can run either a query or a series of queries
> that will run against each record and delete based on criteria (date)?
> If I do a single DELETE query, it will take forever, lock the table,
> and my app that runs against it will stop INSERTING, which is bad.
> If I do a cursor, I think it locks the table also, so that won't do,
> right?

First of all, if there is no index on this date column, you can never
avoid the table lock.

If there is a clustered index on the date column, you should be able
to say DELETE tbl "WHERE date < @.somedateinthepast" and still have
your insertes coming through. Then again, if you delete day by day,
it may still be leaner.

If there is a non-clustered index on date, it depends on how many
rows there are per date. If there are too many rows per date, a
DELETE per date could table-scan. You could force an index, but
plenty of rows would be locked, for some time.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||For the future, you should consider partitioning , as deleting n
records is o(n) while dropping a table (or truncating it) is O(1).

For now , I would recommend deleting with rowcount< some number ,
probably in the low thousands, which should leave the table in a fairly
operational state while you do it.

Of course after such a massive delete, the table will be in a terribly
fragmented state internally, so the next step is to rebuild the
indexes.

another alternative is to create another table with the same schema but
without the indexes, insert into it the rows that you do want to keep
(the complement of the 'to be deleted') and then build the indexes. you
can then drop the old table and rename the new one.

hope this helps
Tzvika

gdekhayser wrote:
> To any and all;
> I have a very large table (16Mil+ records), for which I want to
delete
> about 8 Million records. There are a few indexes on this table.
> Is there a way that I can run either a query or a series of queries
> that will run against each record and delete based on criteria
(date)?
> If I do a single DELETE query, it will take forever, lock the table,
> and my app that runs against it will stop INSERTING, which is bad.
> If I do a cursor, I think it locks the table also, so that won't do,
> right?
> Any help would be appreciated.
> Glenn Dekhayser
> Contentcatcher.com|||Erland- I wish I would have read your post earlier. I would have
thought that removing the indexes would make the delete faster. I
goofed and left the 4 indexes in place, the date one wasn't clustered.
I really wouldn't have cared about the row locks if I forced an index,
as they were going to be deleted anyway and not accessed.

I ended up just performing the delete- and watching my logs grow REAL
large.

I now have 3 log files where I really only want one- is there a way to
condense back into one log file when you're split into 3?|||gdekhayser (gdekhayser@.voyantinc.com) writes:
> Erland- I wish I would have read your post earlier. I would have
> thought that removing the indexes would make the delete faster.

Yeah, removing the indexes not useful for the DELETE would have speedied
things up a little. It may not be a good idea to drop the clustered index
though.

> I ended up just performing the delete- and watching my logs grow REAL
> large.
> I now have 3 log files where I really only want one- is there a way to
> condense back into one log file when you're split into 3?

Well, you can say ALTER DATABASE REMOVE FILE, but you would have to
truncate the log first. Maybe the best is to use WITH TRUNCATE_ONLY
and the take a full backup of the database. I guess that then you
should be able to remove some of the files.

But I will have to admit that I have never had reason to drop a log
file, so I am just speculating here.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How accurate is dm_db_index_usage_stats

All,

I've been running this script below regularly to see the supposed usage of my indexes:

SELECT object_name(s.object_id) AS ObjectName

, s.object_id

, i.name as IndexName

, i.index_id

, user_seeks

, user_scans

, user_lookups

, user_updates

FROM sys.dm_db_index_usage_stats s

INNER JOIN sys.indexes i

ON i.object_id = s.object_id

AND i.index_id = s.index_id

WHERE database_id = db_id ()

AND objectproperty(s.object_id,'IsUserTable') = 1

ORDER BY (user_seeks + user_scans + user_lookups + user_updates) ASC

Before I start disabling/dropping indexes, just how accurate is the this dmv?

Thanks,

Ian

The data for the dmvs is real time.|||

It records all activity since SQL was last started, in real-time. I have found it to be very reliable and helpful in deciding which indexes are actually being used for reads. Try running this query to get a better picture:

-- Possible bad Indexes (writes > reads)

DECLARE @.dbid int

SELECT @.dbid = db_id()

SELECT 'Table Name' = object_name(s.object_id), 'Index Name' =i.name, i.index_id,

'Total Writes' = user_updates, 'Total Reads' = user_seeks + user_scans + user_lookups,

'Difference' = user_updates - (user_seeks + user_scans + user_lookups)

FROM sys.dm_db_index_usage_stats AS s

INNER JOIN sys.indexes AS i

ON s.object_id = i.object_id

AND i.index_id = s.index_id

WHERE objectproperty(s.object_id,'IsUserTable') = 1

AND s.database_id = @.dbid

AND user_updates > (user_seeks + user_scans + user_lookups)

ORDER BY 'Difference' DESC, 'Total Writes' DESC, 'Total Reads' ASC

How accurate is dm_db_index_usage_stats

All,

I've been running this script below regularly to see the supposed usage of my indexes:

SELECT object_name(s.object_id) AS ObjectName

, s.object_id

, i.name as IndexName

, i.index_id

, user_seeks

, user_scans

, user_lookups

, user_updates

FROM sys.dm_db_index_usage_stats s

INNER JOIN sys.indexes i

ON i.object_id = s.object_id

AND i.index_id = s.index_id

WHERE database_id = db_id ()

AND objectproperty(s.object_id,'IsUserTable') = 1

ORDER BY (user_seeks + user_scans + user_lookups + user_updates) ASC

Before I start disabling/dropping indexes, just how accurate is the this dmv?

Thanks,

Ian

The data for the dmvs is real time.|||

It records all activity since SQL was last started, in real-time. I have found it to be very reliable and helpful in deciding which indexes are actually being used for reads. Try running this query to get a better picture:

-- Possible bad Indexes (writes > reads)

DECLARE @.dbid int

SELECT @.dbid = db_id()

SELECT 'Table Name' = object_name(s.object_id), 'Index Name' =i.name, i.index_id,

'Total Writes' = user_updates, 'Total Reads' = user_seeks + user_scans + user_lookups,

'Difference' = user_updates - (user_seeks + user_scans + user_lookups)

FROM sys.dm_db_index_usage_stats AS s

INNER JOIN sys.indexes AS i

ON s.object_id = i.object_id

AND i.index_id = s.index_id

WHERE objectproperty(s.object_id,'IsUserTable') = 1

AND s.database_id = @.dbid

AND user_updates > (user_seeks + user_scans + user_lookups)

ORDER BY 'Difference' DESC, 'Total Writes' DESC, 'Total Reads' ASC

how 2 know the most requested tables

Is there a way to know the tables the mostly requested in a database
How please?

Because we want to copy indexes from DB2 to SQl tables, but since there re so many tables in the DB we want to start with the most requested tables by the applications

Thanks

Do you want to know from the DB2 database or the SQL Server database?

If you are talking about SQL Server 2005 you can check the DMFs sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Hi,
I want to know the most used tables in SQL 2005 database, but now I have no indexes defined yet. That s why I need to know the most used tables so that we can start creating indexes for these tables first.

The thing is we have to fix the problemes as soon possible, that s why we need to start with the most important tables , that s the mostly used tables so that we create their indexes first

Thanks a lot
|||

If you are on SP 2 (install it if you aren't), you really want to look at the Performance Dashboard Reports. That will help immensily in what you are trying to do. They are meant for troubleshooting performance problems. Looking at just the most used tables may not get you there as quickly as using these reports. You can drill through on missing indexes and look at the gains, impact you get from different indexes. The index recommedations is what you are looking for anyway based on what you just posted. In addition, the problems you are having may be more than just indexes and these reports will help with that. Check the following article and link to the reports:

http://blogs.msdn.com/sqltips/archive/2007/03/29/sql-server-2005-performance-dashboard-reports.aspx

-Sue

hot to rebuidl indexes

Hi,

I want to know how can I rebuild all indexes in all tables using T-SQL?

Thanks..

Hello Jassim,

This script will reindex all tables in a database. Just change the database name in the first line...

USE DatabaseName --Enter the name of the database you want to reindex

DECLARE @.TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@.TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @.TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

Hope this helps! A full discussion of rebuilding indexes can be found here|||

Another command would be:

Code Snippet

use <dbname>

go

select 'DBCC DBREINDEX('''+object_name(id)+''','''',<sampling rate>)' from sys.sysindexes where xtype = 'U' and indid in (1,0) -- For SQL Server 2005

OR

use <dbname>

go

select 'DBCC DBREINDEX('''+object_name(id)+''','''',<sampling rate>)' from sysindexes where xtype = 'U' and indid in (1,0) -- For SQL Server 2000

The above commands would generate the T-SQL commands to re-index the tables of the database. You might want to use DBCC INDEXDEFRAG as this is an online operatio and DBREINDEX is an offline operation.

hot rebuild all indexes

how can run a command to rebuild all indexes in a database?

Do you want to just update the statistics or rebuild the indexes? The latter does more than just update statistics of the index. You can use sp_updatestats to rebuild statistics for all tables in the current database. There is no equivalent one for rebuilding indexes however and you will have to write your own. Lastly, you do not want to perform both these operations without knowing the ramifications. Check out the blog from the SQL Server Storage Engine team for more details on these operations and also a whitepaper:

http://blogs.msdn.com/sqlserverstorageengine/default.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

|||hi
you can use ALTER INDEX for reindexing.
i think if you use from "Indexed View" or "Covering Index" then your speed is very high.
good luck|||It depends. Using indexed views or adding more columns in an index to cover a query can adversely affect DML operations on the base tables. You need to outweigh the pros and cons before using indexed views or creating a covered index. In SQL Server 2005, we also have the ability to INCLUDE columns to non-clustered indexes at the leaf level so that they are not part of the key column(s). This has slightly better performance benefits than adding columns to the index key(s). This is another option to consider and evaluate.|||

Hi,

It is neccesary to check the Index statistics to measure the health of an Index.In Order to see statistics of any index follow the sample T-SQL command you will need to run:

DECLARE

@.ID int,

@.IndexID int,

@.IndexName varchar(128)

--input your table and indexname

SELECT @.IndexName = 'AK_DepartmentName'

SET @.ID = OBJECT_ID('HumanResources.Department')

SELECT @.IndexID = IndID

From sysindexes

Where id = @.ID AND name = @.IndexName

--run the DBCC Command

DBCC SHOWCONTIG( @.id, @.IndexID)

Note:DBCC-->Database Consistency Checker is used for checking lots of entities in SQL Server.

But, as per your requirement you can also run "DBCC SHOW STATISTICS" to see when was the last time the indexes were rebuild.

The same example as above,

DBCC SHOW_STATISTICS ('Humanresources.department' , 'PK_Department_DepartmentID')

After this, you can Reorganize your index using "DBCC DBREINDEX".You can either request a particular index to be re-organized or just re-index all the indexes of the table.

The same example of HumanResources.Department.

--This will Re-index all your indexes belonging to "HumanResources.Department".

DBCC DBREINDEX ([HumanResources.Department])

--This will Re-Index only "AK_Department_Name"

DBCC DBREINDEX ([HumanResources.Department],[AK_Department_Name])

--This will Re-index with a "Fill factor"

DBCC DBREINDEX ([HumanResources.Department],[AK_Department_Name],70)

You can then again run DBCC SHOWCONTIG as in the first sample code to see the results.