SQL Peak Performance

What Does “Large Page” Mean?

A “Large Page” refers to using bigger blocks of memory all at once. This method is faster for the computer when it needs to allocate and use memory. Normally, Windows manages memory in 4Kb chunks on x64 systems, but with large pages, these chunks grow to at least 2Mb or even up to 16Mb. For a deeper dive, “Microsoft Windows Internals” by Mark Russinovich is a great resource.

What is spinlock.?

Spinlocks are simple locks used in SQL Server to manage access to data when multiple processes try to use it simultaneously. Think of them as quick checks: if a process wants to use a resource that’s already in use, it “spins” in place, checking repeatedly and quickly until the resource becomes available, using CPU cycles during this process.

Why do I need to monitor spinlocks in long term.?

Monitoring spinlocks in the long term is essential for maintaining SQL Server performance. High contention on spinlocks can lead to significant CPU usage, impacting overall system responsiveness. It’s important to note that starting spinlock monitoring can be almost impossible when you’re already facing an issue with 100% CPU utilization.

How to setup long term spinlock monitoring.?

To effectively monitor spinlocks, we’ll set up two tables: one for storing raw data from sys.dm_os_spinlock_stats and another for holding the calculated differences between two snapshots. Additionally, two stored procedures will be introduced—one for capturing the current state and another for calculating the differences between snapshots.