SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Slows system performance


Slows system performance

Author
Message
Krishna1
Krishna1
Old Hand
Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)

Group: General Forum Members
Points: 348 Visits: 556
using sp_spaceused
I get -
DB size - 10011.13 MB
Unallocated space - 288.22 MB
Data - 3779736 KB
Index - 5912608 KB
Unused - 140264 KB
Reserved - 9832608 KB

(Note: I am doing performance testing in my db where i have only 2 tables.)
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16061 Visits: 19529
Can you run this please?

EXEC sp_spaceused 'mytable'

- change the table name to the name of the table you are deleting from.

Also, run this:

DBCC SQLPERF ( LOGSPACE )

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Krishna1
Krishna1
Old Hand
Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)

Group: General Forum Members
Points: 348 Visits: 556
Result of EXEC sp_spaceused 'tablename'
Rows = 12309864
REserverd = 6976112 KB
Data = 5038728 KB
Index = 1795848 KB
Unused = 141536 KB


Result of DBCC SQLPERF ( LOGSPACE )
Log size 7584.117
Log space used = 97.98335
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16061 Visits: 19529
Last of all, can you post the actual batch please? Change the table name if you need to.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Krishna1
Krishna1
Old Hand
Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)

Group: General Forum Members
Points: 348 Visits: 556
BEGIN TRY

CREATE TABLE #aaa
(
Id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY ,
xyzmasterCode VARCHAR(15) NOT NULL )


INSERT INTO#aaa (xyzmasterCode,NumberOfRows)
SELECT DISTINCT xyzmasterCode, COUNT(*)
FROM xyz
WHERE xyzmasterCode BETWEEN '0' AND 'Z'
AND filterDate <= @filterDate
GROUP BY xyzmasterCode



SET @Id = 0

WHILE 1=1
BEGIN
BEGIN TRAN

DELETE a
FROM xyz a,#aaa
WHERE a.xyzmasterCode = #aaa.xyzmasterCode
AND #aaa.Id between @Id and @Id + 100
AND filterDate <= @filterDate


DELETE J
FROM xyzmaster J,#aaa
WHERE J.Code = #aaa.xyzmasterCode
AND#aaa.Id between @Id and @Id + 100


COMMIT TRANSACTION ;
CHECKPOINT
IF @DeleteRowcount <=0
BREAK

SET @Id = @Id + 100

END



END TRY
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16061 Visits: 19529
That looks ok to me, other than the joins to the temp table not being optimized. You don't really need the temp table anyway:

SET @RowsDeleted = 1000;
WHILE @RowsDeleted > 0
BEGIN

BEGIN TRAN;

DELETE TOP(1000) s
FROM [Experimental].[dbo].[SampleData] s
WHERE xyzmasterCode BETWEEN '0' AND 'Z'
AND filterDate <= @filterDate;

SET @RowsDeleted = @@ROWCOUNT;

COMMIT TRAN;

CHECKPOINT;


BEGIN TRAN;

DELETE TOP(1000) s
FROM [Experimental].[dbo].[SampleData] s
WHERE xyzmasterCode BETWEEN '0' AND 'Z';

SET @RowsDeleted = @RowsDeleted + @@ROWCOUNT;

COMMIT TRAN;

CHECKPOINT;

END



You could run it as one transaction spanning both deletes or as two as shown -I'd try both.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
baabhu
baabhu
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1669 Visits: 1218
ChrisM@Work (4/15/2013)
That looks ok to me, other than the joins to the temp table not being optimized. You don't really need the temp table anyway:

SET @RowsDeleted = 1000;
WHILE @RowsDeleted > 0
BEGIN

BEGIN TRAN;

DELETE TOP(1000) s
FROM [Experimental].[dbo].[SampleData] s
WHERE xyzmasterCode BETWEEN '0' AND 'Z'
AND filterDate <= @filterDate;

SET @RowsDeleted = @@ROWCOUNT;

COMMIT TRAN;

CHECKPOINT;


BEGIN TRAN;

DELETE TOP(1000) s
FROM [Experimental].[dbo].[SampleData] s
WHERE xyzmasterCode BETWEEN '0' AND 'Z';

SET @RowsDeleted = @RowsDeleted + @@ROWCOUNT;

COMMIT TRAN;

CHECKPOINT;

END



You could run it as one transaction spanning both deletes or as two as shown -I'd try both.


Add a waitfor delay statement after the commit. This will help to reduce the blocking.
ChrisM@home
ChrisM@home
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2022 Visits: 10345
baabhu (4/17/2013)
ChrisM@Work (4/15/2013)
That looks ok to me, other than the joins to the temp table not being optimized. You don't really need the temp table anyway:

SET @RowsDeleted = 1000;
WHILE @RowsDeleted > 0
BEGIN

BEGIN TRAN;

DELETE TOP(1000) s
FROM [Experimental].[dbo].[SampleData] s
WHERE xyzmasterCode BETWEEN '0' AND 'Z'
AND filterDate <= @filterDate;

SET @RowsDeleted = @@ROWCOUNT;

COMMIT TRAN;

CHECKPOINT;


BEGIN TRAN;

DELETE TOP(1000) s
FROM [Experimental].[dbo].[SampleData] s
WHERE xyzmasterCode BETWEEN '0' AND 'Z';

SET @RowsDeleted = @RowsDeleted + @@ROWCOUNT;

COMMIT TRAN;

CHECKPOINT;

END



You could run it as one transaction spanning both deletes or as two as shown -I'd try both.


Add a waitfor delay statement after the commit. This will help to reduce the blocking.


Can you explain what you mean by this, baabhu? I can understand why a delay might be useful - removing obsolete log data isn't instantaneous - but I don't understand what this has to do with blocking. Cheers.


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Igor Micev
Igor Micev
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5838 Visits: 5080
Hi

From your results it seems you have much schema locks.
Just had similar scenario yesterday, without locks but with high cpu. Some update scripts were running for 'forever'. We stopped them, rebuilt the indexes and run again and they passed well. Maybe will help you.

Regards
IgorMi

Igor Micev,
SQL Server developer at Seavus
My blog: www.igormicev.com
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86477 Visits: 45238
IgorMi (4/21/2013)
Just had similar scenario yesterday, without locks but with high cpu. Some update scripts were running for 'forever'. We stopped them, rebuilt the indexes and run again and they passed well.


That was stale statistics resulting in a sub-optimal exec plan (probably estimating one row and getting thousands or millions). Next time just update stats, don't waste time doing a full index rebuild.

p.s. All queries take shared schema locks when they run, this is normal behaviour. Hence having lots of schema locks is not a problem (unless you have a bunch of schema mod locks too)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search