ROWLOCK

hi everone
if we have a table with huge amount of data in it
how much performance loss will occur (%) , if we use rowlock in our queries
on that table
Thanks in advance
Erdem KEMER
The only way to tell is to test... Most people let SQLs optimizer choose the
locking unless you are seeing some particular problem
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"erdem" <erdem@.kulube.net> wrote in message
news:%23DIxxKlcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> hi everone
> if we have a table with huge amount of data in it
> how much performance loss will occur (%) , if we use rowlock in our
> queries on that table
> Thanks in advance
> Erdem KEMER
>
|||erdem,
As Wayne suggested, I would leave the locking hints out unless you are
experiencing poor index selection from the optimiser, or poor
performance. If row locks become too expensive, SQL Server will escalate
to a table lock. If you are finding that row locks are too expensive and
table locks do not cut it, try specifying page locks. I would have
thought this to be unnecessary though.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
erdem wrote:
> hi everone
> if we have a table with huge amount of data in it
> how much performance loss will occur (%) , if we use rowlock in our queries
> on that table
> Thanks in advance
> Erdem KEMER
>
|||As another poster already pointed out, you shouldn't use locking hints
without a very good reason; SQL Server does a pretty good job of choosing the
proper lock for the job.
But you can estimate the performance impact if you know your row size and
the number of records being updated in each query. For instance, assume
rows of 800 bytes (incl. overhead), so 10 rows fit on each 8K data page. If
your update affects one hundred rows, then row locking would incur 10X the
overhead as page locking (and 100X the overhead of a table lock). That
doesn't mean ten times slower, as lock overhead is only a fraction of the
total work done in an update, but the impact is still significant, especially
on a cpu-bound server.
Rowlocks increase concurrency though, so the performance of other queries
running may increase substantially. In the case above, rowlocks would reduce
lock contention by other processes by (roughly) 90%. Of course, if the
server is thrashing mightily trying to acquire and release tens of thousands
of rowlocks, all queries will slow down, and it might wind up being slower
after all.
This is why SQL Server usually does a better job of choosing the locks than
we do. It makes a decision based on the actual size of the update, current
available memory, etc. Overriding this is useful only in rare cases.

ROWLOCK

hi everone
if we have a table with huge amount of data in it
how much performance loss will occur (%) , if we use rowlock in our queries
on that table
Thanks in advance
Erdem KEMERThe only way to tell is to test... Most people let SQLs optimizer choose the
locking unless you are seeing some particular problem
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"erdem" <erdem@.kulube.net> wrote in message
news:%23DIxxKlcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> hi everone
> if we have a table with huge amount of data in it
> how much performance loss will occur (%) , if we use rowlock in our
> queries on that table
> Thanks in advance
> Erdem KEMER
>|||erdem,
As Wayne suggested, I would leave the locking hints out unless you are
experiencing poor index selection from the optimiser, or poor
performance. If row locks become too expensive, SQL Server will escalate
to a table lock. If you are finding that row locks are too expensive and
table locks do not cut it, try specifying page locks. I would have
thought this to be unnecessary though.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
erdem wrote:
> hi everone
> if we have a table with huge amount of data in it
> how much performance loss will occur (%) , if we use rowlock in our queries
> on that table
> Thanks in advance
> Erdem KEMER
>|||As another poster already pointed out, you shouldn't use locking hints
without a very good reason; SQL Server does a pretty good job of choosing the
proper lock for the job.
But you can estimate the performance impact if you know your row size and
the number of records being updated in each query. For instance, assume
rows of 800 bytes (incl. overhead), so 10 rows fit on each 8K data page. If
your update affects one hundred rows, then row locking would incur 10X the
overhead as page locking (and 100X the overhead of a table lock). That
doesn't mean ten times slower, as lock overhead is only a fraction of the
total work done in an update, but the impact is still significant, especially
on a cpu-bound server.
Rowlocks increase concurrency though, so the performance of other queries
running may increase substantially. In the case above, rowlocks would reduce
lock contention by other processes by (roughly) 90%. Of course, if the
server is thrashing mightily trying to acquire and release tens of thousands
of rowlocks, all queries will slow down, and it might wind up being slower
after all.
This is why SQL Server usually does a better job of choosing the locks than
we do. It makes a decision based on the actual size of the update, current
available memory, etc. Overriding this is useful only in rare cases.

ROWLOCK

hi everone
if we have a table with huge amount of data in it
how much performance loss will occur (%) , if we use rowlock in our queries
on that table
Thanks in advance
Erdem KEMERThe only way to tell is to test... Most people let SQLs optimizer choose the
locking unless you are seeing some particular problem
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"erdem" <erdem@.kulube.net> wrote in message
news:%23DIxxKlcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> hi everone
> if we have a table with huge amount of data in it
> how much performance loss will occur (%) , if we use rowlock in our
> queries on that table
> Thanks in advance
> Erdem KEMER
>|||erdem,
As Wayne suggested, I would leave the locking hints out unless you are
experiencing poor index selection from the optimiser, or poor
performance. If row locks become too expensive, SQL Server will escalate
to a table lock. If you are finding that row locks are too expensive and
table locks do not cut it, try specifying page locks. I would have
thought this to be unnecessary though.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
erdem wrote:
> hi everone
> if we have a table with huge amount of data in it
> how much performance loss will occur (%) , if we use rowlock in our querie
s
> on that table
> Thanks in advance
> Erdem KEMER
>|||As another poster already pointed out, you shouldn't use locking hints
without a very good reason; SQL Server does a pretty good job of choosing th
e
proper lock for the job.
But you can estimate the performance impact if you know your row size and
the number of records being updated in each query. For instance, assume
rows of 800 bytes (incl. overhead), so 10 rows fit on each 8K data page. I
f
your update affects one hundred rows, then row locking would incur 10X the
overhead as page locking (and 100X the overhead of a table lock). That
doesn't mean ten times slower, as lock overhead is only a fraction of the
total work done in an update, but the impact is still significant, especiall
y
on a cpu-bound server.
Rowlocks increase concurrency though, so the performance of other queries
running may increase substantially. In the case above, rowlocks would reduc
e
lock contention by other processes by (roughly) 90%. Of course, if the
server is thrashing mightily trying to acquire and release tens of thousands
of rowlocks, all queries will slow down, and it might wind up being slower
after all.
This is why SQL Server usually does a better job of choosing the locks than
we do. It makes a decision based on the actual size of the update, current
available memory, etc. Overriding this is useful only in rare cases.sql

Row Sampling Transform

The "Sampling Value" is now exposed on the Control Flow so we can use expressions to determine the amount of rows that flow through a pipeline . What is missing as far as I can see is the ability to say "ALL".

I know I could remove the transform but what if I do not want to touch the packages. The way i will set the value is through DTExec.

This would be useful for testing in that I could have a series of runs and each one ramping up the row counts. The final one would be "ALL" but without something on the transform to say "ALL" I would need to determine the count of rows up front. A waste IMHO.
I suppose the workaround is to use the % sampling transform and specify 100%.
Allan
www.sqlis.com
Allan\stick it on the SSIS Requests thread Allan...

Row numbers etc.

I have a sales report that includes dollar amount, tonnage, and profit margin among other things. They are currently sorted by tonnage sold from highest to lowest. I'd like to be able to place a number in a column counting 1 up for tonnage ranking. I'd also like to get a number ranking for sales amount ranking along with profit margin ranking. The most tonnage sold might not have been the biggest sale nor had the highest profit margin.

Does this sound like something that can be done within SSRS?

I should ad I'm runing MDX queries against a cube so I can't use T-SQL for ranking.

John,

You might be able to do this in the code window of the reports, but I think it might be simpler for you to do this in your MDX.

Here's a sample query (based on the FoodMart cube) that gives a rank for Store Sales and a rank for a made up member of Negative Store Sales, to show that the rankings work independently:

Code Snippet

with

member [Measures].[Negative Store Sales]

as [Measures].[Store Sales] * -1

member [Measures].[Store Sales Rank]

as Rank([Store].CurrentMember, [Store].[Store Name], [Measures].[Store Sales])

member [Measures].[Negative Store Sales Rank]

as Rank([Store].CurrentMember, [Store].[Store Name], [Measures].[Negative Store Sales])

select

{[Measures].[Store Sales Rank]

, [Measures].[Store Sales]

, [Measures].[Negative Store Sales Rank]

, [Measures].[Negative Store Sales]} on columns

, [Store].[Store Name] on rows

from [Sales]

Will that work for you?
Jessica

|||

Thanks Jessica.

I played around yesterday and came up with this MDX code, which does almost everything I need (I think):

with

SET [Sales Rank 2006] AS

ORDER

(

NONEMPTY([Customer Name].[Customer Name].members),

[Measures].[Sales], BDESC

)

MEMBER [Measures].[Sales Rank] AS

RANK([Dim Customer].[Customer Name].CurrentMember, [Sales Rank 2006])

SELECT NON EMPTY

{[Measures].[Sales Rank], [Measures].[Sales]} on 0,

NON EMPTY [Dim Customer].[Customer Name].MEMBERS on 1

from [Heidtman DW]

WHERE [Date Shipped].[Year].[2006]

I'm not sure what to do with it. Do I create calculate members in my cube? Do I create a calculated member in my data set in SSRS? It doesn't seem like I can use this code directly to do either. I use year as a parameter in my reports. Will SSRS run the ranking code against the set of data (year) chosen at run time? Or do I have to create rankings by each year in my data set and store them somewhere?

Thanks again.

|||

If you think there will be multiple queries/reports that will need to use sales rank, I would put the calculated measure in your cube. If this is a one time reporting requirement, I would put it in the query.

As for getting the rank in a calculated measure, there are a few modifications I would make. Since you are passing the year in as a parameter to your query, you will not need to directly specify it in your calculated measure. Once you slice on the year and use your calculated measure, it will rank over the slice you have specified. Also, if you pass the measure into the rank function, you don't need to order your set beforehand.

So to use your example of customers, I would create a calculated measure that looks similar to this:

Code Snippet

with

member [Measures].[Sales Rank Over Customers] as

Rank([Dim Customer].[Customer Name].CurrentMember, [Dim Customer].[Customer Name], [Measures].[Sales])

Then you can use [Measures].[Sales Rank Over Customers] in your query on the same axis as your sales measure. As long as your [Dim Customer].[Customer Name] hierarchy is on the other axis, and you are slicing on the date, that should work for you.

-Jessica

|||

That builds an akward data set, increases the processing time by several orders of magnitude, and doesn't produce a rank result.....back to the drawing board.

Could I just create a named set in my cube that was:

ORDER

(

NONEMPTY([Customer Name].[Customer Name].members),

[Measures].[Sales], BDESC

)

This, to me, would order the customers by sales from highest to lowest? Then all I need to do is somehow assign an integer to each customer in the ordered list, counting from 1 to n? I don't need to rank them as they're already sorted in the order I'm looking for?

How to assign an integer?

Thanks.

Row numbers etc.

I have a sales report that includes dollar amount, tonnage, and profit margin among other things. They are currently sorted by tonnage sold from highest to lowest. I'd like to be able to place a number in a column counting 1 up for tonnage ranking. I'd also like to get a number ranking for sales amount ranking along with profit margin ranking. The most tonnage sold might not have been the biggest sale nor had the highest profit margin.

Does this sound like something that can be done within SSRS?

I should ad I'm runing MDX queries against a cube so I can't use T-SQL for ranking.

John,

You might be able to do this in the code window of the reports, but I think it might be simpler for you to do this in your MDX.

Here's a sample query (based on the FoodMart cube) that gives a rank for Store Sales and a rank for a made up member of Negative Store Sales, to show that the rankings work independently:

Code Snippet

with

member [Measures].[Negative Store Sales]

as [Measures].[Store Sales] * -1

member [Measures].[Store Sales Rank]

as Rank([Store].CurrentMember, [Store].[Store Name], [Measures].[Store Sales])

member [Measures].[Negative Store Sales Rank]

as Rank([Store].CurrentMember, [Store].[Store Name], [Measures].[Negative Store Sales])

select

{[Measures].[Store Sales Rank]

, [Measures].[Store Sales]

, [Measures].[Negative Store Sales Rank]

, [Measures].[Negative Store Sales]} on columns

, [Store].[Store Name] on rows

from [Sales]

Will that work for you?
Jessica

|||

Thanks Jessica.

I played around yesterday and came up with this MDX code, which does almost everything I need (I think):

with

SET [Sales Rank 2006] AS

ORDER

(

NONEMPTY([Customer Name].[Customer Name].members),

[Measures].[Sales], BDESC

)

MEMBER [Measures].[Sales Rank] AS

RANK([Dim Customer].[Customer Name].CurrentMember, [Sales Rank 2006])

SELECT NON EMPTY

{[Measures].[Sales Rank], [Measures].[Sales]} on 0,

NON EMPTY [Dim Customer].[Customer Name].MEMBERS on 1

from [Heidtman DW]

WHERE [Date Shipped].[Year].[2006]

I'm not sure what to do with it. Do I create calculate members in my cube? Do I create a calculated member in my data set in SSRS? It doesn't seem like I can use this code directly to do either. I use year as a parameter in my reports. Will SSRS run the ranking code against the set of data (year) chosen at run time? Or do I have to create rankings by each year in my data set and store them somewhere?

Thanks again.

|||

If you think there will be multiple queries/reports that will need to use sales rank, I would put the calculated measure in your cube. If this is a one time reporting requirement, I would put it in the query.

As for getting the rank in a calculated measure, there are a few modifications I would make. Since you are passing the year in as a parameter to your query, you will not need to directly specify it in your calculated measure. Once you slice on the year and use your calculated measure, it will rank over the slice you have specified. Also, if you pass the measure into the rank function, you don't need to order your set beforehand.

So to use your example of customers, I would create a calculated measure that looks similar to this:

Code Snippet

with

member [Measures].[Sales Rank Over Customers] as

Rank([Dim Customer].[Customer Name].CurrentMember, [Dim Customer].[Customer Name], [Measures].[Sales])

Then you can use [Measures].[Sales Rank Over Customers] in your query on the same axis as your sales measure. As long as your [Dim Customer].[Customer Name] hierarchy is on the other axis, and you are slicing on the date, that should work for you.

-Jessica

|||

That builds an akward data set, increases the processing time by several orders of magnitude, and doesn't produce a rank result.....back to the drawing board.

Could I just create a named set in my cube that was:

ORDER

(

NONEMPTY([Customer Name].[Customer Name].members),

[Measures].[Sales], BDESC

)

This, to me, would order the customers by sales from highest to lowest? Then all I need to do is somehow assign an integer to each customer in the ordered list, counting from 1 to n? I don't need to rank them as they're already sorted in the order I'm looking for?

How to assign an integer?

Thanks.

routine to archive records

Howdy,
I'm working on a routine to archive records. It is
the basic copy a massive amount of records from table
a to table b and then delete the records copied from
table a. I plan to do this using a trigger for delete
on table a. Neither table has a clustered index tho
they both have some non clustered indexes.
Please look at the outline and let me know if this is
a good plan. Is there anything that I might do to
improve? I know that the trigger will affect the
delete query performance ... but I'm not sure how best
to make sure that the deleted records are copied to
the archive database.
First I'm going to create a following trigger on
the source table Active_DB.dbo.Important_Records
CREATE TRIGGER ArchiveRecord ON [dbo].[Important_Records]
FOR DELETE
AS
INSERT INTO Archive_DB.dbo.Important_Records
(...columns...)
SELECT ...columns...
FROM deleted
The actual delete routine will be something like:
USE Archive_DB
ALTER DATABASE Archive_DB
SET RECOVERY SIMPLE
--DISABLE TRANSACTION LOG BACKUP JOB
USE Active_DB
ALTER DATABASE Active_DB
SET RECOVERY SIMPLE
--DISABLE TRANSACTION LOG BACKUP JOB
DECLARE @.DayCount int
SET @.DayCount = 576 --(for example)
DECLARE @.RowsDeleted int
SET @.RowsDeleted = -1
SET ROWCOUNT 1000
WHILE @.RowsDeleted <> 0
BEGIN
DELETE
FROM dbo.Important_Records
WHERE EXISTS (SELECT OCCURRED WITH (NOLOCK) FROM
dbo.Important_Records WHERE < (getdate() - @.DayCount))
SET @.RowsDeleted = @.@.ROWCOUNT
END
END
SET ROWCOUNT 0
--sp_updatestats @.resample = 'resample'
CHECKPOINT
--ENABLE TRANSACTION LOG BACKUP JOB
ALTER DATABASE Active_DB
SET RECOVERY BULK_LOGGED
USE Archive_DB
--sp_updatestats @.resample = 'resample'
CHECKPOINT
--ENABLE TRANSACTION LOG BACKUP JOB
ALTER DATABASE Archive_DB
SET RECOVERY BULK_LOGGED
--Full Backup of Active_DB
--Full Backup of Archive_DBA faster way would be:
1. bcp the data out to a flat file
2. delete the data in batches (10000 rows each would be fine)
3. bulk insert the data in in batches
-oj
<NTuser_Man@.msn.com> wrote in message
news:1125450831.524829.115380@.z14g2000cwz.googlegroups.com...
> Howdy,
> I'm working on a routine to archive records. It is
> the basic copy a massive amount of records from table
> a to table b and then delete the records copied from
> table a. I plan to do this using a trigger for delete
> on table a. Neither table has a clustered index tho
> they both have some non clustered indexes.
> Please look at the outline and let me know if this is
> a good plan. Is there anything that I might do to
> improve? I know that the trigger will affect the
> delete query performance ... but I'm not sure how best
> to make sure that the deleted records are copied to
> the archive database.
> First I'm going to create a following trigger on
> the source table Active_DB.dbo.Important_Records
> CREATE TRIGGER ArchiveRecord ON [dbo].[Important_Records]
> FOR DELETE
> AS
> INSERT INTO Archive_DB.dbo.Important_Records
> (...columns...)
> SELECT ...columns...
> FROM deleted
> The actual delete routine will be something like:
> USE Archive_DB
> ALTER DATABASE Archive_DB
> SET RECOVERY SIMPLE
> --DISABLE TRANSACTION LOG BACKUP JOB
> USE Active_DB
> ALTER DATABASE Active_DB
> SET RECOVERY SIMPLE
> --DISABLE TRANSACTION LOG BACKUP JOB
> DECLARE @.DayCount int
> SET @.DayCount = 576 --(for example)
> DECLARE @.RowsDeleted int
> SET @.RowsDeleted = -1
> SET ROWCOUNT 1000
> WHILE @.RowsDeleted <> 0
> BEGIN
> DELETE
> FROM dbo.Important_Records
> WHERE EXISTS (SELECT OCCURRED WITH (NOLOCK) FROM
> dbo.Important_Records WHERE < (getdate() - @.DayCount))
> SET @.RowsDeleted = @.@.ROWCOUNT
> END
> END
> SET ROWCOUNT 0
> --sp_updatestats @.resample = 'resample'
> CHECKPOINT
> --ENABLE TRANSACTION LOG BACKUP JOB
> ALTER DATABASE Active_DB
> SET RECOVERY BULK_LOGGED
> USE Archive_DB
> --sp_updatestats @.resample = 'resample'
> CHECKPOINT
> --ENABLE TRANSACTION LOG BACKUP JOB
> ALTER DATABASE Archive_DB
> SET RECOVERY BULK_LOGGED
> --Full Backup of Active_DB
> --Full Backup of Archive_DB
>|||oj, thanks for the advice.
I haven't used bcp. This morning I experimented with the northwind db
to get a feel for bcp. I would like to know how can I ensure that the
records I bcp from the dbo.Important_Records are the very same records
that I delete from dbo.Important_Records? That is the concern that
leads me to want to use a trigger for delete.
oj wrote:
> A faster way would be:
> 1. bcp the data out to a flat file
> 2. delete the data in batches (10000 rows each would be fine)
> 3. bulk insert the data in in batches
> --
> -oj
>
> <NTuser_Man@.msn.com> wrote in message
> news:1125450831.524829.115380@.z14g2000cwz.googlegroups.com...|||bcp can take a query. That would be the way to extract only the desired
rows.
e.g.
bcp "select * from Northwind..Orders where OrderID<10250" queryout
"c:\Orders.txt" -T -w
-oj
<NTuser_Man@.msn.com> wrote in message
news:1125500222.595819.253710@.z14g2000cwz.googlegroups.com...
> oj, thanks for the advice.
> I haven't used bcp. This morning I experimented with the northwind db
> to get a feel for bcp. I would like to know how can I ensure that the
> records I bcp from the dbo.Important_Records are the very same records
> that I delete from dbo.Important_Records? That is the concern that
> leads me to want to use a trigger for delete.
> oj wrote:
>|||Thanks again for the input.
I have the bcp part working ... it copies data to text files and then
uploads to the destination beautifully.
But I'm having a performance issue on the delete query ( appended below
). The select statement runs very quickly and uses a clustered index
s. But when the delete action is held up by sorts on three non
clustered indexes. The sorts account for 76% of the query cost.
I'm wondering if it would make sense to drop the non clustered indexes
before running the delete. The table itself has 28 million records so
dropping and rebuilding the non clustered indexes might be quite a
chore in itself. My other thought was to modify the non clustered
indexes to include the clustered index ...
Query:
CREATE PROCEDURE dbo.DeleteImportant_Record
@.DayCount int
AS
SET NOCOUNT ON
SET ROWCOUNT 1000
WHILE 1=1
BEGIN
DELETE
FROM dbo.Important_Record
WHERE EXISTS (SELECT OCCURRED FROM dbo.Important_Record WITH
(NOLOCK) WHERE RecordDate < (getdate() - @.DayCount))
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
SET NOCOUNT OFF
GO
oj wrote:
> bcp can take a query. That would be the way to extract only the desired
> rows.
> e.g.
> bcp "select * from Northwind..Orders where OrderID<10250" queryout
> "c:\Orders.txt" -T -w|||There is really no need for the subquery. Try:
declare @.dt datetime
set @.dt=(getdate() - @.DayCount)
WHILE 1=1
BEGIN
DELETE
FROM dbo.Important_Record
WHERE RecordDate < @.dt
IF @.@.ROWCOUNT = 0
BREAK
END
-oj
<NTuser_Man@.msn.com> wrote in message
news:1125524729.117694.268840@.g49g2000cwa.googlegroups.com...
> Thanks again for the input.
> I have the bcp part working ... it copies data to text files and then
> uploads to the destination beautifully.
> But I'm having a performance issue on the delete query ( appended below
> ). The select statement runs very quickly and uses a clustered index
> s. But when the delete action is held up by sorts on three non
> clustered indexes. The sorts account for 76% of the query cost.
> I'm wondering if it would make sense to drop the non clustered indexes
> before running the delete. The table itself has 28 million records so
> dropping and rebuilding the non clustered indexes might be quite a
> chore in itself. My other thought was to modify the non clustered
> indexes to include the clustered index ...
>
> Query:
> CREATE PROCEDURE dbo.DeleteImportant_Record
> @.DayCount int
> AS
> SET NOCOUNT ON
> SET ROWCOUNT 1000
> WHILE 1=1
> BEGIN
> DELETE
> FROM dbo.Important_Record
> WHERE EXISTS (SELECT OCCURRED FROM dbo.Important_Record WITH
> (NOLOCK) WHERE RecordDate < (getdate() - @.DayCount))
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0
> SET NOCOUNT OFF
>
> GO
>
> oj wrote:
>|||Hey, thanks for all the help. Now the query deletes over 10,000
records/minute. Now I have only 27188456 recods to go.
oj wrote:
> There is really no need for the subquery. Try:
> declare @.dt datetime
> set @.dt=(getdate() - @.DayCount)
> WHILE 1=1
> BEGIN
> DELETE
> FROM dbo.Important_Record
> WHERE RecordDate < @.dt
> IF @.@.ROWCOUNT = 0
> BREAK
> END
>
>
> --
> -oj
>
> <NTuser_Man@.msn.com> wrote in message
> news:1125524729.117694.268840@.g49g2000cwa.googlegroups.com...