Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Very large table - performance issues Expand / Collapse
Author
Message
Posted Tuesday, May 21, 2013 5:15 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 19, 2014 3:37 AM
Points: 708, Visits: 3,288
We have a tall table that contains 2.6 billion rows

Table structure:



The application which uses this table has been running slow for the last couple of days and it seems to have happened following the addition of about 400 million rows last weekend.

I think it's because of the index fragmentation although I'm not sure how to check if this the case without affecting the application?

So my first question is, how do I check to see if the indexes are fragmented and whether the stats need updating on such a large table?


---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1454925
Posted Tuesday, May 21, 2013 6:26 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
SELECT  
database_id, object_name(object_id), object_id, index_id, partition_number, index_type_desc,
alloc_unit_type_desc, index_depth,
index_level, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages, page_count,
avg_page_space_used_in_percent, record_count, ghost_record_count, version_ghost_record_count, min_record_size_in_bytes,
max_record_size_in_bytes, avg_record_size_in_bytes, forwarded_record_count
FROM
sys.dm_db_index_physical_stats
( 5, NULL, NULL, NULL, 'DETAILED' )
where object_name(object_id) = 'TableName'

Try this , if avg_fragmentation_in_percent is more than 30 then it is index fragmentation.


Regards
Durai Nagarajan
Post #1454966
Posted Tuesday, May 21, 2013 6:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:13 AM
Points: 39,971, Visits: 36,329
Index fragmentation is quite unlikely to cause sudden slow performance. Index fragmentation is in fact only a concern when you're doing large range scans from disk.

Now statistics, that's a lot more likely to be the problem. Try a stats update with fullscan (yes, it'll take a while) and see if that helps.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1454968
Posted Tuesday, May 21, 2013 6:59 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 19, 2014 3:37 AM
Points: 708, Visits: 3,288
Thanks to you both.

Not sure why I said index fragmentation. When my colleague mentioned it to me, the first thing I asked him was about the stats lol.
Was thinking along the lines of 400 million new rows = out of date stats = inappropriate execution plans for the queries etc...



---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1454979
Posted Tuesday, May 21, 2013 7:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:31 AM
Points: 12,901, Visits: 32,136
yeah i would have thought of statistics as the issue as well;
ok we know it takes 20% of the rows int he table , + 500 rows to trigger auto updates of statistics.

so if you insert 400M rows, is that 400M out of 3000M (13.33 % affected?) or is that 400M of 2600M rows that were already there?(15.38 % affected?)


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1454984
Posted Tuesday, May 21, 2013 7:17 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 19, 2014 3:37 AM
Points: 708, Visits: 3,288
The 2.6 billion total is after the insert of the 400 million.

---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1454991
Posted Tuesday, May 21, 2013 7:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:31 AM
Points: 12,901, Visits: 32,136
Abu Dina (5/21/2013)
The 2.6 billion total is after the insert of the 400 million.


cool to have such big table, I'm actually a bit jealous, because it presents a lot more opportunities for learning when fiddling with lots of data. lots of chances to increase experience in tuning, maintenance, etc.





Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1454997
Posted Tuesday, May 21, 2013 7:46 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 19, 2014 3:37 AM
Points: 708, Visits: 3,288
Lowell (5/21/2013)
Abu Dina (5/21/2013)
The 2.6 billion total is after the insert of the 400 million.


cool to have such big table, I'm actually a bit jealous, because it presents a lot more opportunities for learning when fiddling with lots of data. lots of chances to increase experience in tuning, maintenance, etc.


To be honest a lot of the time I feel out of my depth but as you say, it's a great opportunity to learn new skills in the two key areas you mentioned.

My boss (who owns half the company) and an accomplished developer herself ran a delete statement (this was last month).... It took the whole application down for 5 days!

Oh and it's the first time I got to use COUNT_BIG


---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1455017
Posted Tuesday, May 21, 2013 7:56 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 19, 2014 3:37 AM
Points: 708, Visits: 3,288
I have another question. Any implications running DBCC SHOW_STATISTICS on a live system with this big table?

---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1455025
Posted Tuesday, May 21, 2013 8:08 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:13 AM
Points: 39,971, Visits: 36,329
No. Show statistics doesn't touch the table


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1455033
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse