How can I force SQL Server to use more CPU

views:

answers:

How can I force SQL Server to use more CPU

Hello,

I have an data transformation query which takes a long time to run on my development machine (Core i7 920 running at 3.9GHz, and with 12GB of RAM under Windows Server 2003 x86 and with 2 Velociraptors 300GB iN RAID0).

When I look at the task manager, the CPU stays around 26%, with the third (out of 4) core being the most active.

As this is not a production environment, is there any way to tell SQL Server 2008 that I am alright with it using more of my CPU or is it because my query can not be parallelized for some reason? If, shouldn't SQL Server be smart enough to cut the query in smaller chunks and run it across several threads so each core can get it?

Thanks.

+1 A:

Optimize your query. Chances are that the issue is with it and not SQL Server.

Oded 2010-06-30 14:07:14

It can't be much more optimized than that unfortunately, based on previous questions asked there.

Kharlos Dominguez 2010-06-30 14:10:51

@Kharlos Dominguez - and all the tables are properly indexed? And you are not doing any long running tasks withing SQL?

Oded 2010-06-30 14:11:46

@Kharlos Dominguez You need to optimise your query to allow it to be split over multiple threads. That may actually involve de-optimising it. Without seeing the query and execution plan it is impossible to say more.

Robin Day 2010-06-30 14:16:14

Yes, I'm not seeing any Table Scan, just Clustered Index Scans and SSMS's execution plan does not suggest any index to added. That's the only task running on my local SQL Server at the moment.I have several left self-joins in that query though and based on the execution plan, it seems that it spends half of its time doing hashing for the joins.

Kharlos Dominguez 2010-06-30 14:23:41

@Kharlos Dominguez - can you post the details I asked for? It is not possible to help with the query without seeing it.

Oded 2010-06-30 14:39:13

Sorry, just posted them in the first post.http://stackoverflow.com/questions/3038067/get-non-overlapping-dates-ranges-for-prices-history-data/3038275#3038275 < This is more or less the query I use. The source table is around 150 000 rows.Actually, it is more a theoretical question since it isn't production code, users will never suffer of this long query (aside me) but I need to run this once in a while and it is painful to wait several hours for it to finish, and I thought I could learn one trick of two.

Kharlos Dominguez 2010-06-30 14:43:12

@Kharlos Dominguez : you should either post the query plan for analysis or post the query you're using. The former being preferable.

etliens 2010-07-02 08:02:14

@Kharlos Dominguez : A Clustered Index Scan == Table Scan; so if you're seeing a lot of them there is a good chance of this being a source of your problems -- especially if the source table has 150k rows and does self-joins.

etliens 2010-07-02 08:04:34

It already knows that it's okay unless you specifically limited it to use only a certain number of CPUs either through configuration or through setting the MAXDOP parameter.

It sounds like you may be constrained by your hard drives or memory more than anything.

Note that because you are running an x86 version of windows (and by extension sql server), you may be RAM limited to around 3GB. And even with the PAE (physical addressing extensions) turned on, it's going to be a world of difference slower than if you have an x64 OS and SQL Server to begin with.

In other words, you might consider reinstalling the machine from the ground up to take advantage of all the x64 goodness you have.

Chris Lively 2010-06-30 14:08:17

Thanks. However, I'm not seeing much HDD activity and everything else runs fast... my RAID array delivers 200MB/s and I can work on other stuff without problems at almost normal speed.

Kharlos Dominguez 2010-06-30 14:11:58

If that's the case, you should also know that sql server will only use multiple processors depending on how parallelizable the query is. If the query can only be parted out to 2 or 3 processors then that's all sql server will use.

Chris Lively 2010-06-30 18:30:01

ansaurus

tags:

views:

answers:

How can I force SQL Server to use more CPU

related questions