SQLTeam.com | Weblogs | Forums

Why does running queries kill my PC?


#1

I'm running SQL Server 2008 R2 on a Windows 7 PC. Most of the time, I use SQL Server Management Studio to connect to databases on our network servers, and everything runs fine. But I also have some small test databases installed directly on my own PC.

Now, I understand that when I run a query that requires any processing time, my PC will be bogged down while the query is running, because of the CPU dedicated to SQL running the query. That part's normal and expected.

But once the query is done running, my computer doesn't recover. The entire PC still runs very slowly, until the next time I reboot. This happens after pretty much any query that takes more than 10 seconds to run. If the query is done, why is my computer still slowed down? I looked to see if there was some process in SQL still running, and there isn't. I checked Task Manager in Windows, and nothing's taking up CPU. My best guess is that the query clogged up the computer's memory, but shouldn't that clear to make space for other things once it's done? Is there a way to control that, or manually clear it out?

Having to restart my computer after everything I do in SQL, especially when it takes forever to shut down because it's running so slow, really sucks.


#2

SQL Server manages its own memory. So it will request more and more memory from the OS as and when it needs, but then would not release the memory back to the OS. You can onfigure the maximum memory that SQL Server is allowed to use. To do this, in SQL Server Management Studio, right click on the server name in object explorer, select Properties, then Memory tab. Set the Maximum memory taking into account the other applications and the operating system needs. On a dedicated server (which is not the case in your situation), people recommend that you leave 1.5 to 2 GB for the operating system.

By the way, even if you don't set the maximum memory, if you want to reset the memory usage, you can restart SQL Server (rather than the computer system itself). Restart is one of the menu's when you right click on the server name in object explorer in SSMS.