July 19, 2012 at 1:58 pm
I will be as detailed as possible.
We have a high OLTP Publisher (SQL 2005 Ent.) publishing to 5 subscribers (SQL 2005 Std.). The DB on one of the subscribers was 50GB more in size than other 4 DB's. After little bit of investigation found that one of the table alone which was 500MB in total size on all other servers was 50.5GB on one. Rebuild the index fixed the issue and the size came back to 500MB.
We are noticing that the size of this table increasing on a daily basis. Comparing the PK & NCIX indexes we found under INDEX PROPERTIES-FRAGMENTATION the ghost rows which is 0 in all servers is 40,000,000 on the effected server.
- Trace 661 is not turned to ON
- sp_configure compare is same on all servers
- MSFT has a fix for 2005 SP1 which does not apply in our case (http://support.microsoft.com/kb/932115)
- There are more than 2-3 million changes every hour
- The table has 5 INT & one DATETIME columns i.e. no BLOB or VARCHAR(max) columns
...Kumar
July 19, 2012 at 2:42 pm
Are you sure ghost rows are not being deleted? we had a similar issue where a table was far larger than it should have been. This was caused by a dodgy clustered index on the table which led to many page splits, so much so that page space used was just 2%!
We had many ghost rows and assumed the ghost row cleanup process wasn't working. On testing we found that it was working, just very very slow. And you can get into a situation where so many deletes are happening that the ghost row cleanup process cannot keep up.
July 19, 2012 at 2:55 pm
I am pretty sure its not working as expected, as the other 4 servers with same configuration do not seem to have this issue. The clustered index on this table is on a INT column i.e. all the 5 servers have the same schema (Table,PK,NCIX, FillFactor etc).
Are there any other settings which you would suggest to be checked ?
...Kumar
July 19, 2012 at 3:28 pm
Definitely read this if you haven't already, paul Randal seems to know an awful lot about this.
http://www.sqlskills.com/blogs/paul/post/inside-the-storage-engine-ghost-cleanup-in-depth.aspx
Is it only happening on one table?
Try running this a few times against the database concerned to see if the ghost records go down at all.
select object_id
,index_type_desc
,alloc_unit_type_desc
,avg_fragmentation_in_percent
,page_count
,avg_page_space_used_in_percent
,ghost_record_count
from sys.dm_db_index_physical_stats (
DB_ID()
, Null
, Null
, Null
, 'Detailed'
)
Detailed takes longer but gets a more realistic look at the fragmentation. If the ghost rows are not reducing, try and run update statistics <tablename> and then run the query again to see if ghost rows are reducing.
July 19, 2012 at 5:24 pm
Here is what I found from the information you provided.
Running update stats on the table does help in decreasing the number of ghost rows, however its temporary i.e. the rowcount reduces by 100K from 40 million and then remains there. I tried running update stats again and it reduced by another 100K however remains at that number even after waiting for 30 minutes.
I will need to run update stats thousands of times to clear all the records which is not what i prefer. I also checked that auto update stats is enabled on the DB.
...Kumar
July 19, 2012 at 7:01 pm
Anyone, Please even if you do not know whats causing this, please throw out ideas on what might be causing this and what else would we need to check for. I appreciate your time.
...Kumar
July 20, 2012 at 2:20 am
Hi Kumar, update stats isn't causing the rows to be deleted, just refreshing the view you have on the number of ghost records that exist. As the number is going down it looks like the ghost row cleanup is working. Would you saymany deletes happen against the table in question?
Also have a look at the avg_page_space_used_in_percent value, is it low?
July 20, 2012 at 4:00 am
- I confirm from the behavior i see that running the select alone does not reduce the number of ghost records at all. running it with update stats helps once.
- The avg_page_space_used_in_percent is 99.6% for PK
- The number of deletes/inserts/updates are 2-3 million on this table for every 2 hours, I would say this should not be a concern as the other 4 servers receive the same updates as well.
...Kumar
July 20, 2012 at 4:12 am
What is the page_count of the indexes on the offending table?
What about avg_page_space_used in the NCIX?
Are there more databases on this server than the others? More load?
What is the current size of the table?
July 20, 2012 at 1:55 pm
Please confirm you are on build 9.0.5266 on all instances.
Are the results of this the same on all instances?
DBCC TRACESTATUS(-1);
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 20, 2012 at 3:45 pm
All the SQL Instances are 9.0.5266, The result of DBCC TRACESTATUS (-1) is the same on all i.e. no global trace flags set.
...Kumar
July 20, 2012 at 7:56 pm
Is the hardware equivalent? To Richard's point this one instance may simply not be keeping up.
Another slightly related train of thought, do you have index maintenenace running on all the instances at similar times, or at all? As you have experienced rebuilding the index with the ghost records rids the index of them. If the cleanup process is not keeping up and index maint is not running regularly you may develop this issue.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 20, 2012 at 11:20 pm
All the 5 servers are running Win 2008 R2 Virtual on VmWare, with similar hardware i..e 8 CPU, 16 GB RAM & similar (data, log, teampdb) disks on same SAN subsystems.
We donot have index maintenance on any of the subscribers as these are running Stand. Edt, we take them out of the VIP regularly to rebuild offline & put them back in.
Our main concern is even after rebuilding the effected server, the ghost records & size of the table keeps on increasing exponentially without reducing in number at all.
...Kumar
July 23, 2012 at 11:28 am
We have opened a case with MSFT. I will update one we have a finding. In the meanwhile please do let me know if you guys have any other suggestion or places to look for more information which will help in resolution
-Kranthi
...Kumar
July 23, 2012 at 3:56 pm
Hi Kumar, you said that ghost records were going down when you monitored them and did update statistics and checked them again. So the ghost rows do seem to be clearing down, it's just that before they can clear down, more records are deleted and more ghost records are created.
I suppose you could check the ghost record cleanup process by creating a test database on the server, stick a few records in a table, check the fragmentation for that table using the command I gave you, delete all the records then review fragmentation again. If you see ghost records then you know the cleanup process isn't working. You may see 1 in there as I think sql keeps one hanging around
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply