Sfrgttk

Do warforged have souls?

Are spiders unable to hurt humans, especially very small spiders?

ELI5: Why do they say that Israel would have been the fourth country to land a spacecraft on the Moon and why do they call it low cost?

How do you keep chess fun when your opponent constantly beats you?

Presidential Pardon

"... to apply for a visa" or "... and applied for a visa"?

Loose spokes after only a few rides

Windows 10: How to Lock (not sleep) laptop on lid close?

Why not take a picture of a closer black hole?

60's-70's movie: home appliances revolting against the owners

How did the audience guess the pentatonic scale in Bobby McFerrin's presentation?

Working through the single responsibility principle (SRP) in Python when calls are expensive

Drawing vertical/oblique lines in Metrical tree (tikz-qtree, tipa)

US Healthcare consultation for visitors

Sub-subscripts in strings cause different spacings than subscripts

Is it ethical to upload a automatically generated paper to a non peer-reviewed site as part of a larger research?

Circular reasoning in L'Hopital's rule

What's the point in a preamp?

How to type a long/em dash `—`

Single author papers against my advisor's will?

Identify 80s or 90s comics with ripped creatures (not dwarves)

Does Parliament need to approve the new Brexit delay to 31 October 2019?

What information about me do stores get via my credit card?

What was the last x86 CPU that did not have the x87 floating-point unit built in?

SQL Server - Extremely low avg_page_space_used_in_percent for heap table

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}

0

While analyzing a SQL Server 2012 database bloat issue, I found that fragmentation might be a major contributor to how much space is being taken up by certain tables, so after some Googling, I'm looking into the sys.dm_db_index_physical_stats function to try to understand what's going on. Here's my query:

SELECT

index_id,

index_type_desc,

alloc_unit_type_desc,

avg_fragmentation_in_percent,

avg_page_space_used_in_percent,

record_count,

page_count,

min_record_size_in_bytes,

max_record_size_in_bytes,

avg_record_size_in_bytes

FROM sys.dm_db_index_physical_stats

(DB_ID(), NULL, NULL, NULL , 'SAMPLED')

WHERE OBJECT_ID = 516196889

ORDER BY avg_fragmentation_in_percent DESC;

And my results:

There are 4 non-clustered indexes on the table, so I'm guessing those are what the top 4 result rows represent. I haven't figured out why the IN_ROW_DATA portion of the heap has such low page usage (6%). If I'm reading the results correctly, there are more pages for this part than there are rows, but max record size is less than 8kb, so I'm trying to understand how that's even possible. How do I find out what's causing this low page usage, and what are some things I can do to fix it?

Any help greatly appreciated!

sql-server sql-server-2012

share

asked 5 mins ago

user3250365user3250365

1

New contributor

user3250365 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

0

While analyzing a SQL Server 2012 database bloat issue, I found that fragmentation might be a major contributor to how much space is being taken up by certain tables, so after some Googling, I'm looking into the sys.dm_db_index_physical_stats function to try to understand what's going on. Here's my query:

SELECT

index_id,

index_type_desc,

alloc_unit_type_desc,

avg_fragmentation_in_percent,

avg_page_space_used_in_percent,

record_count,

page_count,

min_record_size_in_bytes,

max_record_size_in_bytes,

avg_record_size_in_bytes

FROM sys.dm_db_index_physical_stats

(DB_ID(), NULL, NULL, NULL , 'SAMPLED')

WHERE OBJECT_ID = 516196889

ORDER BY avg_fragmentation_in_percent DESC;

And my results:

There are 4 non-clustered indexes on the table, so I'm guessing those are what the top 4 result rows represent. I haven't figured out why the IN_ROW_DATA portion of the heap has such low page usage (6%). If I'm reading the results correctly, there are more pages for this part than there are rows, but max record size is less than 8kb, so I'm trying to understand how that's even possible. How do I find out what's causing this low page usage, and what are some things I can do to fix it?

Any help greatly appreciated!

sql-server sql-server-2012

share

asked 5 mins ago

user3250365user3250365

1

New contributor

user3250365 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

While analyzing a SQL Server 2012 database bloat issue, I found that fragmentation might be a major contributor to how much space is being taken up by certain tables, so after some Googling, I'm looking into the sys.dm_db_index_physical_stats function to try to understand what's going on. Here's my query:

SELECT

index_id,

index_type_desc,

alloc_unit_type_desc,

avg_fragmentation_in_percent,

avg_page_space_used_in_percent,

record_count,

page_count,

min_record_size_in_bytes,

max_record_size_in_bytes,

avg_record_size_in_bytes

FROM sys.dm_db_index_physical_stats

(DB_ID(), NULL, NULL, NULL , 'SAMPLED')

WHERE OBJECT_ID = 516196889

ORDER BY avg_fragmentation_in_percent DESC;

And my results:

There are 4 non-clustered indexes on the table, so I'm guessing those are what the top 4 result rows represent. I haven't figured out why the IN_ROW_DATA portion of the heap has such low page usage (6%). If I'm reading the results correctly, there are more pages for this part than there are rows, but max record size is less than 8kb, so I'm trying to understand how that's even possible. How do I find out what's causing this low page usage, and what are some things I can do to fix it?

Any help greatly appreciated!

sql-server sql-server-2012

share

asked 5 mins ago

user3250365user3250365

1

New contributor

user3250365 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

SELECT

index_id,

index_type_desc,

alloc_unit_type_desc,

avg_fragmentation_in_percent,

avg_page_space_used_in_percent,

record_count,

page_count,

min_record_size_in_bytes,

max_record_size_in_bytes,

avg_record_size_in_bytes

FROM sys.dm_db_index_physical_stats

(DB_ID(), NULL, NULL, NULL , 'SAMPLED')

WHERE OBJECT_ID = 516196889

ORDER BY avg_fragmentation_in_percent DESC;

share

asked 5 mins ago

user3250365user3250365

1

New contributor

user3250365 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share

asked 5 mins ago

user3250365user3250365

1

New contributor

user3250365 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 5 mins ago

user3250365user3250365

1

New contributor

user3250365 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 5 mins ago

user3250365user3250365

1