November 27, 2018 at 9:18 am
Hi,
I have a quick qn: we have about 800 production databases on a single SQL Server Ent Ed instance. I've noticed that every now and then the IX fragmentation on several core columns and tables is high, so I go ahead and rebuild them , and rebuilding indexes will keep stats up to date as well.
My understanding is IX fragmentation causes High CPU issues and not High memory, is that correct?
Thank you for your time.
November 27, 2018 at 9:57 am
It could be both. Fragmenting things across pages could mean more pages are needed, but most of the time this is more CPU as you must search the disk and issue more IOs.
November 28, 2018 at 4:25 am
Statistics out of date affect memory because the allocations will be wrong, causing all sorts of issues. If the stats suggest 1 row when 1 million will be returned, the memory allocation is off. This leads to lots of issues. Keep the stats up to date, regardless of whether or not you rebuild the indexes. Further, index fragmentation is not the driving factor for if/when statistics are out of date. It depends on the data in the tables, not the distribution on the disks.
"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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply