SQL Server 2016标准版AuditLog表重新索引及主键选型咨询

Alright, let’s tackle your AuditLog table challenge step by step—covering both primary key selection and reindexing strategy, since you’re shifting to supporting both SQL and C#-generated keys with 105 million rows in SQL Server 2016 Standard.

Let’s break down the pros and cons of each option, tailored to your high-volume audit log scenario:

Option 1: int Identity (with Coordinated Value Ranges) #

Pros #

• Tiny storage footprint: At 4 bytes, int is half the size of your current bigint and 1/4 the size of a GUID. This would immediately shrink your PK index (currently 11GB) to roughly 5.5GB, saving significant storage and reducing IO overhead for index operations.
• Better insert performance: Auto-incrementing int values are sequential, so new rows are added to the end of the index—no random page splits, which is critical for a table with frequent audit log inserts.

Cons #

• Requires range coordination: To avoid conflicts between SQL and C# generated keys, you’ll need to split the int value space:
  • Let SQL’s identity start at 1 and increment up to 2^31-2 (about 2.1 billion rows—plenty of headroom for your current 105M rows).
  • Reserve negative integers (from -1 down to -2^31) for C#-generated keys. This way, there’s zero overlap and no need for conflict checks.
• Future capacity limit: If your audit log grows beyond 2.1 billion rows, int will hit its max value. If you want to avoid this upfront, swap int for bigint identity (8 bytes, same as your current PK) and use the same range-splitting logic—it’s just as performant, with unlimited growth.

Option 2: GUID #

Pros #

• No coordination needed: GUIDs are globally unique out of the box. SQL can generate them with NEWID() or NEWSEQUENTIALID(), and C# can use Guid.NewGuid()—no need to split value ranges.

Cons #

• Massive storage overhead: At 16 bytes, a GUID would double the size of your current PK index (to ~22GB) and bloat your IX_AuditLog_RootId index too, since non-clustered indexes include the clustered key. This eats up storage and slows down index scans/seeks.
• Page split hell: Using NEWID() generates random GUIDs, which causes frequent page splits during inserts—catastrophic for a high-volume audit log. NEWSEQUENTIALID() fixes this for SQL-generated keys, but C# can’t generate sequential GUIDs that align with SQL’s sequence, so you’ll still get random inserts from the C# side.

My Recommendation #

Prioritize bigint identity (or int if you’re confident you’ll stay under 2.1B rows) with range splitting. It keeps your indexes lean, maintains fast insert performance, and only requires a simple agreement between your C# and SQL teams. If you absolutely must use GUIDs, implement sequential GUID generation on both sides (C# can use a time-based sequential GUID logic) to minimize page splits.

With 105 million rows and large indexes, you need a maintenance plan that minimizes downtime and optimizes long-term performance.

Step 1: Audit Index Fragmentation First #

Don’t rebuild indexes blindly—check fragmentation levels to target only what needs fixing:

SELECT
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('AuditLog'), NULL, NULL, 'DETAILED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
ORDER BY ips.avg_fragmentation_in_percent DESC;

• >30% fragmentation: Rebuild the index
• 5-30% fragmentation: Reorganize the index
• <5% fragmentation: Leave it alone—no benefit to rebuilding

Step 2: Execute Reindexing #

Clustered Primary Key (PK_AuditLog) #

SQL Server 2016 Standard doesn’t support online clustered index rebuilds, so schedule this during a low-traffic window:

-- Rebuild clustered PK with fill factor to reduce future splits (85% works for insert-heavy tables)
ALTER INDEX PK_AuditLog ON AuditLog REBUILD WITH (FILLFACTOR = 85, SORT_IN_TEMPDB = ON);

• SORT_IN_TEMPDB = ON: Uses tempdb for sorting during rebuild, reducing IO on your user database (make sure tempdb has enough free space).

Non-Clustered Index (IX_AuditLog_RootId) #

You can rebuild this online in Standard edition, so you don’t need to take the table offline:

ALTER INDEX IX_AuditLog_RootId ON AuditLog REBUILD WITH (ONLINE = ON, FILLFACTOR = 85, SORT_IN_TEMPDB = ON);

Step 3: Ongoing Maintenance #

• Set up a weekly SQL Agent job to run the fragmentation check script, and automatically rebuild/reorganize indexes based on the results.
• Monitor index size and fragmentation monthly to adjust your maintenance schedule if needed.

Special Note If Changing Primary Key #

If you’re replacing your existing bigint PK with int/guid, do it gradually to avoid downtime:

1. Add a new nullable column for the new PK (e.g., NewPK bigint identity(1,1) or NewPK uniqueidentifier).
2. Batch-populate the new column with values (use ROW_NUMBER() for int/bigint, or NEWSEQUENTIALID() for GUID).
3. Update your application code to use the new PK, keeping the old PK column active for fallback.
4. Once all traffic uses the new PK, drop the old PK column and its index, then set the new column as the primary key.
5. Rebuild all non-clustered indexes to ensure they’re optimized for the new PK.

内容的提问来源于stack exchange，提问作者Thierry_S