@tlbignerd I have checked this, but it is irrelevant as this is out of my hands. The queries are well optimized. Everything is fast after a restart, I need to maintain the state of CPU usage at less than 80%, or empty the CPU-cache frequently without messing up current queries.

Your websites and services are "quite big" yet you are using a database "server" with 1 CPU and 4 GB of RAM? My laptops are far more powerful than that, and I would never use them to host "quite big" anything.

"This system is used by hundreds Search and Rescue teams..." - is this "for real"? If so step away from the dial and call an expert. And stop restarting SQL Server as a fix. Your restart doesn't fix anything, it just makes all the traffic go away.

I hear RAM is cheap these days; unless you buy Apple RAM.

"Clear out the CPU cache" - that's a few MB at most. Why'd you want to clear that out? And yes, it's disabled because you only have one CPU. You can see details about affinity masks here. PS: don't fiddle with affinity masks. It will be removed in the future.

The CPU usage increased until 95%, it stays there even if there are no queries. So the CPU does not need this cache, only some of it, whatever is used currently. Is there no setting that will clear anything that isn't used currently?

@AaronBertrand Yes this is for real, the server is running in Hyper-V, so there is more power available, but should not be necessary - also; it is out of my hands. 1 CPU and 4 GB has not been a problem in the past.

So what? Data never grows? Data never changes? Are you in on a constant sized data set?

@Levi "in the past" - so, what changed around the time you started noticing this symptom?

@AaronBertrand We got new servers, Windows Server 2012. Sadly this was at the same time that I started at the company, so I know little of other changes, but each server was given an increase in CPU and RAM and is dealing with the same amount of activity.

@Marian It says that affinity mask will be removed from the server configuration options. You can still change the CPU affinity using 'Alter Server' TSQL commands.

"given an increase in CPU" ... to 1? Did the VM have 0 before?

@LeviJohansen If DMV queries are hard to read, give a try to Confio's Ignite free edition. It might be easier to spot the guilty queries in their graphs. Forgot to say, don't install it on the same machine as the DB server, install it on a different server, so you avoid inserting new variables into the equation.

@AaronBertrand By "given an increase in CPU", I mean it was given a more powerful CPU, there has always been 1.

I wouldn't expect a single, 2.3 GHz CPU to really change the performance profile of a SQL Server previously using a 2.1 GHz CPU.

@Marian I already know which queries take the most time, but they cannot be changed and are fully optimized. Using something like memcached has been suggested, but that would make the data displayed on the website inaccurate at times. I feel like there is something wrong, the SQL is taking more and more of the CPU over time, and I fail to see why it would need to do so.

@AaronBertrand I guess you're right, but the website was faster before and didn't go to 95% unless there was a lot going on; then it would go down again. Now it'll go there over time nomatter what and stay there. I don't think this is a hardware issue, even though hardware could be the solution.

@Levi and I'll tell you one last time: SOMETHING is causing that CPU activity, it's not just random boogie men. YOU need to investigate further when the CPU spikes again (and we've given you plenty of tools to do that), rather than just come up with wacky random ideas like CPU affinity and "clearing your CPU cache"...

Be nice to him. We have another clueless guy here that is forced to work with low end "business critical" hardware and is way over his knowledge and knee deep in a crappy corporate culture - low end VM, nearly no memory, likely a similar disc subsystem and no dev environment.

@AaronBertrand Of course it is something. But nomatter what it is, it is happenening when NO ONE is using the website or the system. So at that time it is safe to restart the SQL-Server, it has no consequenses, but I shouldn't have to do this. I don't know how all of this works, but I was hoping there was a way to empty the CPU without restarting, so that it's not slow when someone IS using it. I was unaware of the EXEC sp_who2; and will check it out when the CPU usage is high again. Also the parametrization setting could be useful.

@LeviJohansen good god man quit saying things like empty CPU that isn't the scource of your problem. You have to find out what is using the CPU. What queries are bogging your system down. You seem to have a fundimental misunderstanding of how this works. Once a query has finished SQL doesn't just keep reving up the CPU for something to do. There has to be something actively running and you need to determine what that is.

Also you can't just say things like all my queries are fully optimizied. We clearly cannot take you at your word on this. If you are having perfomance problems there is a reason behind it. Use one of the 27 meothods that were given here to determine the problem and come back with that info.

Try the few things that you have been suggested to try. Make sure that no query or stored procedure is running when this high CPU activity is using. Make sure it is a SQL-Server process that is using the CPU and not another process. It would help anyone here narrow the options and help you better. I also notice that you have .2100. which means the RTM version. Perhaps it's a bug that has been fixed with Service Pack 1 or a bugfix.

@Zane The CPU usage is sometimes at 90-95% with only a few queries a minute, just the same as when the usage is at 0-5%. This is the problem, I can't find out what is using the CPU, all suggestions soo far have given me nothing. It could be a bug as suggested by ypercube. The CPU usage is now at 30% and there are fewer queries now than previously. It also takes about 1 day to reach 95% every time and then it stays there nomatter how much activity. It is piling up, and it is the SQL-service, shown by taskmgr and the Performance Monitor.

@Brrian The MDF and IDF files are not running out of disk space. I thought this was the problem too, based on google results. I tried all kinds of tips, like shrinking the log file and increasing the file size or the auto-growth settings. But this did nothing.

@FreshPrinceOfSO RAM is not an issue, it has never run out of RAM, it's the CPU that is at near 100%

BTW, I am using SQL Server Profiler to see what queries are running currently.

@Levi it won't "run out of RAM" in a way that you would notice, but unless you've analyzed how the memory is actually being used, you won't know if there is any side effect to the entire buffer pool being swapped out every 5 minutes because the memory is insufficient for the data size / workload.

Please verify what power plan your VM (and the host) is using. Some older blog of Glen Berry mentioned big differences in performance with different power plans.

When you verify the queries with Profiler, do you also check the CPU column? Is it the same as when everything is silent? Do the queries have the same CPU/Reads/Writes values in both silent and high CPU times? Regarding the host, is there anything capping your virtual CPU during that time? Is there something else that's running on another VM on the same host AT the same time (maybe some intensive calculations)?

There are so many things that could be causing this problem. Are you using dynamic SQL within the application? Are you ever doing index maintenance? Do you ever update the statistics? What jobs are scheduled in SQL Agent? What does the output from sp_whoisactive show (a screenshot would be nice)?

@AaronBertrand It is curently using 2-3 out of 4 GB of RAM. I assume it would be using closer to 4 GB if it were having problems. How would I analyze how the memory is being used?

@Marian Both the Host and the VM were set to "Balanced", I've set them to "High Performance". Thanks!

@Marian When the website is slow, the CPU reads higher than usual. Practically all are at 0 when the CPU usage is at 0-10%. Now a forth of them have a value; usually ranging from 50-150.

After changing the parameterilization to forced, as suggested by an answer, the website has been alot faster. The CPU-usage still hits 95%, but only when there are many queries. When there is little activity, the CPU usage goes down to 10-20%, as it should. (I stated 20-50% on the answer, but I've seen it go down to as little as 8% after that)

It is only my guess that this is because I changed the parameterilization to forced. It is, after all, the only change I've done. But I would like to know why this seems to have solved it. Why would the CPU usage stay at 90% when the parameterilization was set to simple? It makes no sense to me at all.