March 14, 2017 at 2:54 am
Hello,
I have two servers (different versions) with two same databases, but on Server2 one table is more bigger than on Server1.
I see that on Server2 the unused table and index space is more bigger, than on Server1 (Index rebuild run every day on both servers).
Do you have any idea that why?
Here is more details:
- Server1:
- Version: 2012
- Database level: 90 (SQL 2005)
- Server2:
- Version: 2016
- Database level: 130 (2016)
Server....IXName....RowCounts....fill_factor...TotalSpaceKB...UsedSpaceKB....UnusedSpaceKB...productversion....compatibility_level
Server1...IX1.......7,894,997....90............553,688........553,608........80..............11.0.3368.0.......90
Server1...IX2.......7,894,997....90............269,360........268,272........1,088...........11.0.3368.0.......90
Server1...PK........7,894,997....90............3,094,128......3,092,536......1,592...........11.0.3368.0.......90
Server....IXName...RowCounts....fill_factor...TotalSpaceKB...UsedSpaceKB....UnusedSpaceKB....productversion....compatibility_level
Server2...IX1......7,895,499....90............807,568........392,784........414,784..........13.0.4411.0.......130
Server2...IX2......7,895,499....90............643,280........290,312........352,968..........13.0.4411.0.......130
Server2...PK.......7,895,499....90............4,711,000......3,205,432......1,505,568........13.0.4411.0.......130
Thanks
March 14, 2017 at 4:34 am
Additional information:
After rebuild the UnusedSpaceKB was little (about 1000) and the UnusedSpaceKB is growing fast to 1,505,568 under SQL Server 2016. But under SQL Server 2012 is not.
March 14, 2017 at 12:12 pm
salliven - Tuesday, March 14, 2017 4:34 AMAdditional information:
After rebuild the UnusedSpaceKB was little (about 1000) and the UnusedSpaceKB is growing fast to 1,505,568 under SQL Server 2016. But under SQL Server 2012 is not.
Sorry but I don't recognize what it is you are using to get the space usage but it seems that you can get a better idea of where the differences are if you use sys.dm_db_index_physical_stats
Sue
March 15, 2017 at 12:19 am
Here is the query:
SELECT
'ServerX' as [server],
t.NAME AS TableName,
p.rows AS RowCounts,
i.[name],
i.[fill_factor],
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
serverproperty('ProductVersion') as productversion,
max([ca].[compatibility_level]) as [compatibility_level]
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
cross join (
select .[compatibility_level]
from sys.[databases] b
where b.[database_id] = db_id()
) ca
WHERE [t].[name] = '<table_name>'
GROUP BY
t.Name, s.Name, p.Rows, i.[name], i.[fill_factor]
ORDER BY
i.[name]
March 15, 2017 at 1:05 am
I ran this query on both servers and here are the results:
SELECT ips.*
FROM sys.dm_db_index_physical_stats (DB_ID(),898102240,1,NULL,'DETAILED') ips
where ips.database_id = DB_ID()
order by ips.page_count
Server1:
database_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.compressed_page_count
----------- ----------- ----------- ---------------- ---------------- --------------------- ----------- ----------- ---------------------------- --------------- -------------------------- ----------- ------------------------------ ------------- -------------------- -------------------------- ------------------------ ------------------------ ------------------------ ---------------------- ---------------------
5...........1026102696..1...........1................CLUSTERED.INDEX..IN_ROW_DATA...........4...........3...........0............................1...............1..........................1...........8.40128490239684...............24............0....................0..........................13.......................27.......................26.416...................NULL...................0....................
5...........1026102696..1...........1................CLUSTERED.INDEX..IN_ROW_DATA...........4...........2...........0............................24..............1..........................24..........43.9332715591796...............2945..........0....................0..........................13.......................27.......................26.995...................NULL...................0....................
5...........1026102696..1...........1................CLUSTERED.INDEX..IN_ROW_DATA...........4...........1...........1.25636672325976.............2944............1.00033967391304...........2945........46.5222881146528...............382598........0....................0..........................13.......................27.......................26.999...................NULL...................0....................
5...........1026102696..1...........1................CLUSTERED.INDEX..IN_ROW_DATA...........4...........0...........1.35783624416483.............16082...........23.7902002238528...........382594......97.0192241166296...............7881180.......1....................0..........................192......................1380.....................379.31...................NULL...................0....................
Server2:
database_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.compressed_page_count.hobt_id..............columnstore_delete_buffer_state.columnstore_delete_buffer_state_desc
----------- ----------- ----------- ---------------- -------------------------------------- ----------- ----------- ---------------------------- --------------- -------------------------- ----------- ------------------------------ ------------- -------------------- -------------------------- ------------------------ ------------------------ ------------------------ ---------------------- --------------------- -------------------- ------------------------------- ------------------------------------------------------------
6...........898102240...1...........1................CLUSTERED.INDEX..IN_ROW_DATA...........4...........3...........0............................1...............1..........................1...........3.19990116135409...............9.............0....................0..........................27.......................27.......................27.......................NULL...................0.....................72057594160218112....0...............................NOT.VALID...................................................
6...........898102240...1...........1................CLUSTERED.INDEX..IN_ROW_DATA...........4...........2...........22.2222222222222.............9...............1..........................9...........67.1347788485298...............1687..........0....................0..........................27.......................27.......................27.......................NULL...................0.....................72057594160218112....0...............................NOT.VALID...................................................
6...........898102240...1...........1................CLUSTERED.INDEX..IN_ROW_DATA...........4...........1...........21.8731475992887.............459.............3.67538126361656...........1687........80.5763281443044...............379508........0....................0..........................26.......................27.......................26.999...................NULL...................0.....................72057594160218112....0...............................NOT.VALID...................................................
6...........898102240...1...........1................CLUSTERED.INDEX..IN_ROW_DATA...........4...........0...........5.58414557987684.............34334...........11.0532708102755...........379503......93.0503953545836...............7502975.......1....................0..........................66.......................1380.....................379.046..................NULL...................0.....................72057594160218112....0...............................NOT.VALID...................................................
How to help me these resultsets?
Thanks
March 16, 2017 at 12:02 pm
Sue
March 23, 2017 at 11:21 am
Here is the result of sp_spaceused:
server...database_name.database_size..unallocated space
server1: <db name>.....20480.00 MB....11893.83 MB
server2: <db name>.....33408.00 MB....24551.65 MB
server...reserved......data..........index_size...unused
server1: 3549360 KB....2674104 KB....872056 KB....3200 KB
server2: 3826024 KB....2767168 KB....892984 KB....165872 KB
March 23, 2017 at 1:55 pm
salliven - Thursday, March 23, 2017 11:21 AMHere is the result of sp_spaceused:
server...database_name.database_size..unallocated space
server1: <db name>.....20480.00 MB....11893.83 MB
server2: <db name>.....33408.00 MB....24551.65 MBserver...reserved......data..........index_size...unused
server1: 3549360 KB....2674104 KB....872056 KB....3200 KB
server2: 3826024 KB....2767168 KB....892984 KB....165872 KB
From this, it looks like your difference is in unallocated space, not really unused space.
Unallocated space is space that's available. Unused space is space that's reserved but not in use by any object - basically from extents not being filled which is always going to happen. That number is going to vary day to day just due the activity in the database and they would likely be different between the two servers.
But going though this one and doing some calculations (feel free to check my math, it's not my strength), if used is data + index size then you would have:
used = data + index_size
select (2674104 + 872056)/1024 --used similar, #2 less than 200 MB larger
select (2767168 + 892984)/1024
reserved = used + unused
select ((2674104 + 872056) + 3200)/1024 --reserved similar, #2 less than 300 MB larger
select ((2767168 + 892984) + 165872)/1024
database_size = reserved + unallocated space + log space
select 20480.00 - (3466 + 11893.83) --log sizes similar
select 33408.00 - (3736 + 24551.65)
The difference in the unused space:
select (165872 - 3200)/1024 --158 MB
So then what's left is the difference on unallocated:
select 24551.65 - 11893.83 --so #2 has just over 12 GB more unallocated space
Not sure how you ended up that difference there. Maybe someone did shrinks with one of the files or you could have different growth increments or the increments are by percent - those are the ones that I can think of right now.
Sue
March 15, 2018 at 11:03 am
Hello,
The problem is solved.
The bulk insert was changed in SQL Server 2016.
I need to use trace flag 692 (which is introduced in SQL Server 2016).
See it here:
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql
Thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply