Common MS SQL Design Errors and Their Effects

Common MS SQL design errors slow queries, increase storage, and reduce uptime. Below are three frequent issues, their impact, and how you fix them.

1. Use of GUID Keys on Large Tables

Problem

Using UNIQUEIDENTIFIER as a clustered primary key on large tables creates fragmentation. Random GUID values insert across the index. Pages split often. IO increases. Cache efficiency drops.

Effects

  • Frequent page splits during inserts
  • High index fragmentation, often above 30 percent
  • Larger index size. GUID uses 16 bytes vs 4 bytes for INT
  • Slower joins and foreign key lookups
  • Increased memory pressure in buffer pool

Example

You create an Orders table.

CREATE TABLE Orders
(
    OrderId UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED,
    CustomerId INT,
    OrderDate DATETIME2
)

Every insert writes to a random location in the clustered index. On a table with 50 million rows, fragmentation grows quickly. Rebuild jobs run longer. Insert throughput drops.

Solution

Use INT or BIGINT identity for clustered key. Keep GUID only if required for distributed systems.

Option 1. Replace clustered key with IDENTITY

CREATE TABLE Orders
(
    OrderId BIGINT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    OrderGuid UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    CustomerId INT,
    OrderDate DATETIME2
)

Cluster on OrderId. Keep OrderGuid as nonclustered unique index if external systems require global uniqueness.

Option 2. Use NEWSEQUENTIALID

If GUID required as primary key, use sequential GUID.

CREATE TABLE Orders
(
    OrderId UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED DEFAULT NEWSEQUENTIALID(),
    CustomerId INT,
    OrderDate DATETIME2
)

Sequential GUID reduces page splits.

Practical Impact

  • Insert speed improves by 30 to 50 percent on large tables
  • Fragmentation reduced significantly
  • Smaller index depth

2. Missing or Incorrect Indexes

Problem

Tables lack indexes on join columns, filter columns, or sort columns. SQL Server scans entire tables instead of seeking.

Effects

  • High logical reads
  • Long query times
  • CPU spikes
  • Blocking due to long transactions
  • Increased tempdb usage for sorting

Example

Query:

SELECT OrderDate, CustomerId
FROM Orders
WHERE CustomerId = 42

If no index exists on CustomerId, SQL Server performs full table scan.

Execution plan shows Clustered Index Scan with millions of rows read.

Solution

Create targeted nonclustered index.

CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
ON Orders(CustomerId)
INCLUDE(OrderDate)

This supports seek operation and avoids key lookups.

Before index Logical reads: 1,200,000 Duration: 2.4 seconds

After index Logical reads: 3 Duration: 5 milliseconds

Index Design Guidance

  • Index foreign keys
  • Index columns used in WHERE, JOIN, ORDER BY
  • Use INCLUDE for covering queries
  • Avoid over indexing. Each index adds write cost

Diagnostic Tools

  • sys.dm_db_missing_index_details
  • Actual Execution Plans
  • SET STATISTICS IO ON

3. Excessive Normalization

Problem

Schema split into too many small tables. Queries require many joins. Each join adds CPU and IO cost.

Effects

  • Complex execution plans
  • Increased join operations
  • Higher CPU usage
  • Harder maintenance
  • Developer errors in query logic

Example

Customer data split across:

  • Customer
  • CustomerAddress
  • CustomerPhone
  • CustomerPreferences
  • CustomerStatus
  • CustomerProfile

To display a customer dashboard, query joins six tables.

SELECT c.Name, a.City, p.PhoneNumber, s.Status
FROM Customer c
JOIN CustomerAddress a ON c.Id = a.CustomerId
JOIN CustomerPhone p ON c.Id = p.CustomerId
JOIN CustomerStatus s ON c.Id = s.CustomerId

On millions of rows, join cost becomes significant.

Solution

Apply selective denormalization for read heavy workloads.

Example. Combine frequently accessed attributes.

CREATE TABLE CustomerSummary
(
    CustomerId INT PRIMARY KEY,
    Name NVARCHAR(200),
    City NVARCHAR(100),
    PhoneNumber NVARCHAR(50),
    Status NVARCHAR(50)
)

Maintain via trigger or scheduled job.

Alternative. Use indexed views for aggregation heavy workloads.

CREATE VIEW dbo.vCustomerSummary
WITH SCHEMABINDING
AS
SELECT c.Id, c.Name, a.City
FROM dbo.Customer c
JOIN dbo.CustomerAddress a ON c.Id = a.CustomerId

CREATE UNIQUE CLUSTERED INDEX IX_vCustomerSummary
ON dbo.vCustomerSummary(Id)

When to Normalize

  • High write frequency
  • Strict data integrity requirements
  • Storage constraints

When to Denormalize

  • Read heavy systems
  • Reporting workloads
  • Dashboard style queries

Architecture Overview

Data Design Flow

flowchart TD
A[Workload Analysis] --> B[Read vs Write Ratio]
B --> C[Index Strategy]
B --> D[Key Selection]
B --> E[Normalization Level]
C --> F[Test with STATISTICS IO]
D --> F
E --> F
F --> G[Measure Latency and CPU]

What You Should Do

  • Choose INT or BIGINT clustered keys for large tables
  • Validate indexes using execution plans
  • Measure logical reads before and after changes
  • Balance normalization against query cost
  • Test with production scale data

You improve uptime by reducing IO. You improve performance by designing keys and indexes correctly. You prevent scaling issues by aligning schema with workload patterns.

Ryan Stevens

Software Systems Architect