0

I have a table with 220 rows. It has about 20 columns. One of the non-clustered indexes has a datetime as the key value and includes a unique identifier and a varchar(128). The clustered PK is a bigint.

The datetime for every row is updated very frequently (40 times a second).

If I rebuild the index it is about 200KB but after a couple of hours, it will have grown to about 50MB. (And it appears to keep growing). Given how often it is accessed, scanning 50MB compared with 200KB is hurting a lot. SQL 2016 SP2 13.0.5201.2

Can anyone explain, or point me to something that explains, how an index with only 3 columns, on a 200 row table can get to be 50MB? And even better anything I can do about this? (The 40 updates a second is not something happy with but I haven't been able to convince anyone that this is excessive yet).

Unfortunately, in-memory is not an option. Nor is changing the code.

Edit: Added output from sys.dm_db_index_physical_stats function as suggested by Andrew Sayer Output from sys.dm_db_index_physical_stats function. requestsed by Andrew Sayer

Possibly related info - Server is not in an availability group. Allow Snapshot Isolation is false but is_read_committed_snapshot_on is true.

17
  • 1
    This table would probably benefit from being made an in-memory table instead of a disk-table. Are you using on-prem SQL Server or some hosted version? (as not all editions support in-memory tables). – Dai Mar 5 at 2:14
  • If you can't change it to in-memory, and it is updated much more often than it is queried, then a better solution is to only insert, not update. All inserts should monotonically increasing (at the end of the index), then the select queries just aggregate and take the most recent insert. Every so often you run a job that deletes old, extraneous rows and rebuilds the index – Charlieface Mar 5 at 2:25
  • 1
    Have you tried dropping the indexes altogether, or dropping the BIGINT clustered index for something people actually search on? The entire table itself isn't going to be that large and doing a table scan will probably satisfy everything without causing a lot of splits on a non clustered index. – bbaird Mar 5 at 3:17
  • 2
    SQL Server will leave ghost records behind when it updates the index - this will cause the index to keep growing when you update it until it does a clean up. The cleanup should just happen every few seconds but can be hindered - usually by open transactions. Check for ghosts by querying the sys.dm_db_index_physical_stats function. – Andrew Sayer Mar 5 at 11:40
  • 1
    @BruceBenson the output shows there is definitely a problem with ghosts. I would next start by checking for old open transactions by running dbcc opentran against this database (you might need to do the same from other databases that sessions could start transactions from that would modify this database - I'm not 100% sure on the rules here). michaeljswart.com/2017/08/… is a good starting point for reading if you want more complete explanations – Andrew Sayer Mar 5 at 21:38
3

This should not be possible, from an architectural standpoint:

The non-clustered index has 220 rows. Even if you have only one row per page in your index, you would have only 1.7 MB (plus a little bit in the non-leaf levels, but that is marginal). So, something is "not normal" here - this should be impossible.

Are you sure you aren't looking at the size of the data, and that the table is a heap table? SQL Server has a well known issue with not free up space for heaps. That would explain it. The solution is to not have a heap. I see, however that you don't have a heap, since you mention that the clustered PK.

A very far-fetched theory is that SQL Server's meta-data doesn't reflect reality regarding the space used by the index. We had that prior to SQL Server 7.0 (or was it 2005 that this was fixed?). We refreshed the meta-data using DBCC UPDATEUSAGE. You can of course use this, just in case. It doesn't explain bad performance, but it is a quick thing to verify. I.e., the theory is that you are exposed to some code-path in SQL Server where this issue (non-correct space usage meta-data) isn't handled.

If this was me, I would first rule out the meta-data aspect. Look at space usage using several methods, just to verify that they agree on the size. And last resort would probably be to see what is really there using commands such as DBCC PAGE, sys.dm_db_database_page_allocations() and such...

(Come to think of it, row versioning could potentially explain some extra space usage, but that would be in tempdb...)

3
  • You are right this shouldn't be possible. The op said "scanning 50MB compared with 200KB is hurting a lot" but maybe that was speculation rather than a measured observation. – Dan Guzman Mar 5 at 11:23
  • It was measured observation. Using query store to force plans, trace data shows 34 reads for a clustered index scan and 3365 for an index seek. – Bruce Benson Mar 5 at 19:51
  • Confirming it is not a heap. And every way I look at it the index is 'impossibly' large. name rows reserved data index_size unused [System.Activities.DurableInstancing].[LockOwnersTable] 220 493080 KB 488 KB 491848 KB 744 KB DBCC UPDATEUSAGE didn't have any impact. Digging into an index with DBCC PAGE etc would be new ground for me. We've requested the server owner patch it - but not based on finding anything to suggest it will actually help. Failing that we'll raise a call with MS. – Bruce Benson Mar 5 at 20:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.