June 14, 2011 at 12:54 am
Hi,
Today i was going through paul randauls ghost records.
http://www.sqlskills.com/blogs/paul/post/inside-the-storage-engine-ghost-cleanup-in-depth.aspx
I have two queries on this regard.
1. If the table has a clustered index then we can fix this issue by rebuiding the clustered index. This is fine. I have tested and absolutely working fine.
2. Secondly, which is my actual question, if it is a heap table or has non-clusterred or a table which has duplicate values in all columns, meaning i cannot create a clustered index on the table, the how to resolve this problem.
Also,recreating a table and loading data again can be done but if the table has dependencies then it becomes cumbersome. Am trying to test this on sql server 2005 sp3 and sql server 2008 sp1.
3. last thing, i was looking around. while reading i came accorss SNAPSHOT ISOLATION level. Does ghost cleanup appears only when a database is set snapshot isolation level or what is so special all are taking an example of snapshot isolation level and explaining a ghost cleanup scenario?
Any help would be greatly appreciated.
Thanks in advance.
June 14, 2011 at 2:16 am
Oracle_91 (6/14/2011)
2. Secondly, which is my actual question, if it is a heap table or has non-clusterred or a table which has duplicate values in all columns, meaning i cannot create a clustered index on the table, the how to resolve this problem.
What problem?
3. last thing, i was looking around. while reading i came accorss SNAPSHOT ISOLATION level. Does ghost cleanup appears only when a database is set snapshot isolation level or what is so special all are taking an example of snapshot isolation level and explaining a ghost cleanup scenario?
Don't understand question fully, but 'all' are not switching to snapshot isolation to demonstrate ghost records. Paul doesn't in the post you referenced, I didn't in the post I did recently.
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
June 14, 2011 at 2:36 am
This is not a problem at all. As Paul mentioned "This is a performance optimization that allows delete operations to complete more quickly."
June 14, 2011 at 2:39 am
2. Secondly, which is my actual question, if it is a heap table or has non-clusterred or a table which has duplicate values in all columns, meaning i cannot create a clustered index on the table, the how to resolve this problem.
What will be the ideal solution in such a scenario??
Coming to ,
3. last thing, i was looking around. while reading i came accorss SNAPSHOT ISOLATION level. Does ghost cleanup appears only when a database is set snapshot isolation level or what is so special all are taking an example of snapshot isolation level and explaining a ghost cleanup scenario?
Yes. you are correct. Paul was not refering the snapshot ISOLATION-LEVEL. Sorry my bad.
But i remember while reading through various articles, snapshot isolation level is being refered in conjunction to ghost records. Is there any about snapshot isolation level when dealing with ghost cleanup.???
Again, am sorry if am putting the statement in wrong words.correct me if am wrong.
June 14, 2011 at 4:46 am
The real question is, why are you so concerned with ghost record cleanup? In certain situations where you have extremely high volume deletes you can run into issues, but in general this just runs in the background and no one notices. Are you having problems or are you just trying to understand better?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 14, 2011 at 6:02 am
The ghost cleanup task generally takes care of the ghost records. However, on extremely OLTP-busy systems such as one of our customers, the ghost cleanup task cannot keep up. This task is single threaded. You can force the ghost cleanup by performing a table scan.
Now with snapshot-isolation, the issue isn't with the ghost records, its with the version ghost records. These records are "supposed" to be converted to regular ghost records after the transaction has been completed, then cleaned up with the aforementioned task. However, again, with a busy system, the version ghost records continue to climb. The only way to clean this up is with an index rebuild. I have been battling Microsoft Tech Support on this and they say this cannot cause a performance issue, but we have proven this wrong as the version ghost records are still taking up space on the pages, thus causing considerably more i/o reads.
June 14, 2011 at 10:03 am
You are right. I wanted to understand it better.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply