November 1, 2010 at 7:34 am
I was tweaking some queries, and noticed a high cpu usage on some of those. It then occured to me, that I don't know what sorts of queries/functions and so on are CPU heavy in SQL. Are there any general guidelines, or such which indicate that a given bit of SQL will be cpu intensive or not?
Thanks!
November 1, 2010 at 7:41 am
The most obvious is aggregation work (and indirectly distinct).
So most reports you'll run will be cpu intensive.
However it depends on the query and system. You might have a cpu intensive query but you can't see that because the server is waiting on hd to spit out the data.
The real question here is are they TOO expansive beause of bad coding habits. This is really the only place where you have power to change things beyond adding more cpus...
Once the query does what it needs to do and is coded as best as possible, there's nothing else to do but give the server enough ressources to run it.
November 1, 2010 at 7:52 am
Hmm, I was concerned that would be the sort of answer. Where I am noticing it is a data warehouse/report server, so clearly lots of aggregation and reports on that. On top of that, our IT operates under the minimum possible hardware theory. Except, they always under scope hardware. 2 Gb ram, 2 CPU for a lower midsized warehouse, and that is our most powerful server (VM on top of that.)
So, I guess time to dig in and find a way to get it to work.
Thanks!
November 1, 2010 at 8:30 am
No offense but my dev server is way more powerfull than that. (16 gb ram and 8 cpus).
2 gb of ram for dw is way too short. I applaud the idea to code as efficient code as possible but at some point the hd must be adequate for the task at hand.
November 1, 2010 at 8:31 am
Oh and I forgot a big one... sorting is big on cpu as well. Reindexing is also a big one, must sort and then write to data and log file..
November 1, 2010 at 8:38 am
David Lester (11/1/2010)
Except, they always under scope hardware. 2 Gb ram, 2 CPU for a lower midsized warehouse, and that is our most powerful server (VM on top of that.)
No offense, but my desktop is more powerful than that (Quad core, 4 GB) and my laptop's probably as or more powerful.
If you're concerned by high CPU usage overall on the server, what you should start with is investigating the most cpu-intensive queries overall. That's a factor of CPU usage per query and frequency of execution. Then see what, if anything, can be done to those queries. There's little point in optimising things that aren't a problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 1, 2010 at 8:47 am
No offense at all. My desktop here is more powerful. I have spent years attempting to break through to those here who choose hardware, that the machine is very under powered. But then, I am working with a group who had a report which would take 36 hours to run, using rbar, and when I took that same code and ran it set based, it droped to 10 minutes, they shrugged and asked why it mattered.
(I keep asking my direct boss to run to bestbuy and pick up any given PC so we can have a more powerful server. heh)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply