In Microsoft SQL Server Management Studio 12.0.4100.1 I have a table with about 12.600.000 records. If I try to show the TOP 1000 of these records with an ORDER BY [bigint column], the query runs for a few minutes, then fails with a message of "There is insufficient memory available in the buffer pool."
However, if I look in the task manager, I see that the SQL Server Windows NT - 64 Bit process only uses a small part of the 32 GB that the computer has. If I run this query after a reset of the local server, I first see the proces using a fair amount of CPU and Disk capacity, while the memory usage steadily climbs. But after a few seconds the memory usage stops growing. Exactly at what level it stops changes every time I try it. Sometimes it's at 1.5 GB, sometimes around 3.5 GB. In either case, the memory usage no long changes, the Disk and CPU usage drop to 0, and the SQL query will keep executing for 2-4 more minutes before failing.
I checked the Max Memory of the local server in SQL Managment Studio, and it was set to the highest value. I actually tried lowering it to 16 GB, but the query still doesn't use nearly what I think it should be allowed to.
Am I missing something? Is this a limitation of SQL Server Express, which I am using at the moment?