SQL Performance

By jerry.murphy ·
I Put a new blade center in for a customer's database server.

The new server has 50 gb ram and 6 processors available.

Now, I shoot the query from **** at the database thinking it?s going to be no problem.

Well, what happens is weird. All 6 processors go to 100% but sql doesn?t even use 500Mb of ram. I tried setting it to minimum of 2gb and max at the default limit, but sql still only grabs about 500Mb. I tried turning on the sql AWE memory engine, but still no difference. How can an SQL query peg 6 CPUs while using almost no memory and what can I do about it?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

That's some serious hardware

by marv732 In reply to SQL Performance

So I assume you're talking a large database with a few thousand tables and indexes etc.
Can you give us an idea of why you sized it at 50GB and 6 CPUs and the data volume?
It may just be you really do have the query from **** and it's trying for a cartesian result from the tables you supply in it.
Who is the database vendor, version and OS?
Can we see the query and get an idea of the database structure and application?


Collapse -


by jerry.murphy In reply to That's some serious hardw ...

The reason I put this hardware in is because of performance issues I had and the customer just keeps piling on more and more requests for product expansions, enhancements and reporting options. Bottom line is that we did have a bit of a bottleneck. This beast should do the trick for a few years. Anyway, the db is for our product that we manufacture. It only has about 5 stored procedures that run and 138 tables. It's a pretty well established product and our SQL db has been rock solid for years. I just am having a hard time understanding how the CPUs can be peddeg like that and SQL is using basicall <1% of available RAM. Thanks for the reply.

Collapse -

Need a bit more info to make an educated guess

by marv732 In reply to SQL Performance

but I think you need to do a bit of drudge work to investigate the cpu problem.
Perhaps try starting with
I assume everything (Windows and SQLServer) are actually 32 bit (the reference to AWE). I generally prefer to go 64bit these days especially for something that large.
Anyway, hope you can get something from the trace files to help and maybe check over your query again if it's one you cooked up yourself as opposed to one from the application.


Collapse -

Thanks for the reply

by jerry.murphy In reply to Need a bit more info to m ...

I'll check out the links. 32 bit was ok for this application although I understand your thinking. The query and app hitting the db is a one off from a hired developer. His app works well, however there are bottlenecks and delays when large items need processing from the client down to sql. I am no developer, but when I was working with the guy, he was using and each of his procedures had a "CPU Cost"? Looks to me that he needs to evaluate what and how he's hitting the server. I still don't get why SQL isn't using any RAM. That's just odd.
Thanks for the links.

Related Discussions

Related Forums