SQL Server Performance Tips for Developers

When running SQL Server on a development laptop, it's important to prevent it from using too many system resources. By default, SQL Server may consume almost all available memory, which can slow down your system. Here's how to safely configure memory and processor settings for a smoother development experience.

Recommended SQL Server Settings for Dev Machines

System Example:

• 16 GB RAM
• Intel Core i7 (4 cores / 8 threads)

Memory Settings

• Max Server Memory:
  By default, SQL Server uses up to 2 TB (2147483647 MB).
  This is too much for a dev laptop.

  Recommended: Set to 6–8 GB
  

  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'max server memory', 8192; -- for 8 GB
  RECONFIGURE;

  This leaves enough memory for your operating system and other tools.

• Min Server Memory:
  Leave it at default (0), unless testing specific workloads.

Processor Settings

• Max Degree of Parallelism (MAXDOP):
  Controls how many processors SQL Server uses for a single task.

  🔧 Recommended: Set to the number of physical cores (e.g., 4)

  EXEC sp_configure 'max degree of parallelism', 4;
  RECONFIGURE;

• Maximum Worker Threads:
  Leave at default (0) to let SQL Server auto-manage based on available cores.

Do You Need to Restart SQL Server?

No restart is required. These settings take effect as soon as you run the RECONFIGURE command.

Final Tip

These settings are ideal for development use. For production environments, you should monitor workloads and adjust based on performance needs.