March 10, 2008 at 1:01 pm
I have been reviewing some older code, and found a delete statement without a where clause.
Not a big deal, but the statement is my 2nd worst performing statement in the DB for Logical Writes so I thought I would change it to a truncate.
I was told by the developer, that it could not be changed since it would corrupt the statistics.
Now I have done this in other situations and it has HELPED significantly, but I thought I would verify the statement.
I can't find anywhere that mentions what happens with stats when you perform a truncate vs delete.
I read the following and it was a great at explaining the differences, but still nothing about stats.http://www.sqlservercentral.com/articles/delete/61387/
Now I tested my assumption (that stats are updated) and I think my test proves I am right.
IF OBJECT_ID('tblMyTest') IS NOT NULL
DROP TABLE tblMyTest
SELECT *
INTO tblMyTest
FROM sysindexes
CREATE UNIQUE CLUSTERED INDEX UK_tblMyTest ON tblMyTest(ID,indID)
SELECT 'Insert',id,
rowcnt,
rowmodctr,
[name],
maxlen,
[rows]
FROM sysindexes WHERE id = OBJECT_ID('tblMyTest')
TRUNCATE TABLE tblMyTest
SELECT 'Truncate',id,
rowcnt,
rowmodctr,
[name],
maxlen,
[rows]
FROM sysindexes WHERE id = OBJECT_ID('tblMyTest')
INSERT
INTO tblMyTest
SELECT *
FROM sysindexes
SELECT 'Insert (again)',id,
rowcnt,
rowmodctr,
[name],
maxlen,
[rows]
FROM sysindexes WHERE id = OBJECT_ID('tblMyTest')
DELETE FROM tblMyTest
SELECT 'delete',id,
rowcnt,
rowmodctr,
[name],
maxlen,
[rows]
FROM sysindexes WHERE id = OBJECT_ID('tblMyTest')
rowmodctr is the only column that appears to be reset. I also know that identity columns are also reset when you truncate, but that isn't an issue here.
I am looking for feedback on opinions on reasons NOT to use truncate when the obvious reasons are already taken into consideration.
1) All Data needs removed.
2) There are no FK's
3) Identity values are safe to reset.
March 10, 2008 at 8:46 pm
If you are deleting ALL the data from the table who cares about the statistics. They will be meaningless until new data is inserted and the stats get updated. If there is a major concern about it just truncate and then update the stats on the table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 11, 2008 at 5:54 am
Thanks Jack. I brought up that point also, but I was assured that performance would be negatively impacted based upon past experience.
I should clairfy that is the primary reason for this post. I have VERY LITTLE experience with SQL Server. I have been using it for about a year now. We have been on 2k5 for about 9 months, and I have only been using SS for a year. The developers have been working on this system and SQL Server for at least 5 years now.
Right now the only issue "I" see that I didn't mention before is a table lock will be acquired with the truncate. However I also believe a table lock (will/can) be acquired when you delete all data.
March 11, 2008 at 6:14 am
Truncate is the fastest way ! (minimal logged)
Delete is a logged operation and will cause the most locking overhead as well as logging overhead (db-logfile)!
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 11, 2008 at 6:33 am
I can almost guarantee the Delete without a where caluse will take a table lock. What is the business reason for the purging of the table? Are the developers forcing index use with hint? This could actually cause the performance problem.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 11, 2008 at 6:48 am
This is basically a staging table for a data mart.
The delete/truncate is called fairly rarely in relation to the other delete, but it is called
if @SecurityID is not null and @user-id is not null
delete from mdlClientData
where MasterSecurityID = @MasterSecurityID and
MasterSecuritySource = @MasterSecuritySource and
UserID = @user-id
else
delete from mdlClientData
Per current performance data it has executed the delete 787x and has take 45s CPU, 52s duration, and 21093244 Logical Reads.
March 11, 2008 at 7:11 am
Per current performance data it has executed the delete 787x and has take 45s CPU, 52s duration, and 21093244 Logical Reads.
Yep, that's what occurs with delete.
A truncate is only a catalog operation flagging a table empty.
If datapages get cleaned up, it is because background processes clean it or db maintenance reclaims the space.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 11, 2008 at 7:44 am
btw you can exclude a table for autoupdate statistics by using
sp_autostats [ @tblname = ] 'table_name'
[ , [ @flagc = ] 'stats_flag' ]
[ , [ @indname = ] 'index_name' ]
Check BOL.
I don't say you should do this !
So if your users have the confidence they can outsmart the sql engine,
you could exclude your table from auto update statistics.
Keep in mind the statistics should contain the "expected distribution"
at the time you shut it off.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 11, 2008 at 8:23 am
Perhaps I wasn't clear. My developers are convinced that if we use truncate the statistics will be invalid, but with delete they will be ok. I do have auto create and update statistics enabled. My tests show that after either a delete or truncate the statistics are updated and accurate.
There are also other benefits I see with truncate that make it much more desireable. Less locking, freeing of space, blah blah blah. I don't see a good reason not to use truncate. So far, it looks like everyone is agreeing with me. Can anyone give me a good reason to use delete instead?
March 11, 2008 at 8:27 am
Bob Fazio (3/11/2008)So far, it looks like everyone is agreeing with me. Can anyone give me a good reason to use delete instead?
To keep your developers happy:P
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 11, 2008 at 8:55 am
If its a staging table that is emptied and reloaded everytime, then I'm sorry but truncate would be faster.
What you may want to do is setup a seperate stage environment and test the process from start to finish using both the delete and truncate and see for yourself which is more effecient.
By demonstration, you may be able to show your developers that things have changed with SQL Server since they first started using it. This reminds me of the monkey story (hopefully someone knows the link that I am thinking of). We do it this way because, and aren't willing to revisit it to see if things may have changed.
I have a staging database here, and all tables are truncated prior to loading. It is simply faster than doing a delete from.
😎
March 11, 2008 at 9:01 am
If they're worried about statistics - why not just drop the table and recreate it? If truncate works, then you don't have any FK constraints.
There should be 0 stats by doing that. Of course - it's a bit longer in the code, but hey - in SQL, that's not really an indication of anything....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 11, 2008 at 9:13 am
- another thing to keep in mind is that you need more authority to
be able to use truncate compared to delete.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 11, 2008 at 10:39 am
Jack Corbett (3/11/2008)
Bob Fazio (3/11/2008)So far, it looks like everyone is agreeing with me. Can anyone give me a good reason to use delete instead?
To keep your developers happy:P
Developers always have to make things difficult ... just smack 'em and say "because I said so". Just make sure you look angry and they'll get the point. You may want to add a snarl for added effect. Trust me, it works.
As stated before, your statistics won't matter regardless if you're deleting the entire table or truncating it. SQL is smart enough to know when to auto update stats. If I remember correctly, it will update stats if the table has seen more than 30% change.
Regardless, if you want to see when your stats are updating, use the STATS_DATE system function.
Also, depending on the frequency of how often you reload, you're tearing up your indexes by doing this. You may want to rebuild the index after you reload the table ... The best bet for performance gains on the load would probably be to drop the index before delete/truncate, load, apply index.
March 11, 2008 at 11:16 am
I know that I can't drop and create the table. Rebuilding of indexes could also cause issues since I know that several users could be sending data at the same time.
What I still haven't gotten a clear understading of is why I can empty the table that can be populated from several locations, and I am fairly sure this all occurs outside of a transaction.
As for permissions, the application account has the needed access.
Although from discussing this with another developer, it sounds like the whole process needs re-worked, not just the one statement. Something that was done a long time ago, never really worked efficent, but wasn't high enough on the list to get fixed.
I suspect this will sit there until it becomes ripe enough to pick.
Thanks for all the feedback, I did get some great suggestions. But it looks like I might go with just keeping the developers happy. 🙂
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply