Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Gawan
Starting Member
3 Posts |
 Posted - 2010-02-24 : 01:11:40
|
| Hello,my actual situation:I have a Xeon CPU with 4 Cores and 8 Threads.SQL Server 2005 Developers Edition is running on this machine, configured to 8 processors.I have 8 SQL-Statements, that calculates 8 indepentens key values from one table. (one for each year)Each statement takes about 10 minutes to complete.I've put these 8 queries into a stored procedure. The compiler puts them into a sequence, one after the other. As a result the whole procedure lasts for about 80 minutes.For any reason only one (out of eight) CPU is used for it - this one is running on 100%, but the whole machine is running on 13% only.I've tried to manually divide the 8 queries and start them in separate query-windows.In this case they are calculated in parallel - all 8 CPUs are used, the whole machine is running on 100% CPU (instead of 13%) and the total result is availabe after 12 minuten.Do you have any idea how to tell the compiler to start these queries in parallel and to use all available CPU power ?I've no idea why it does not :(Thanks in advance and best regardsGawan |
|
|
DaleTurley
Yak Posting Veteran
76 Posts |
Posted - 2010-02-24 : 04:15:16
|
| You could try adding OPTION (MAXDOP 8) to the end of the query.Pretty sure it works the other way round, i normally use it to throttle a query to 1 processor... |
 |
|
|
Gawan
Starting Member
3 Posts |
Posted - 2010-02-25 : 14:22:22
|
| hm, unfortunately that didn't change anything at all ... |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-25 : 14:57:11
|
| changing parallelism isn't going to help since you've serialized the queries.you'll need to run 'em in seperate batches |
|
|
|
Gawan
Starting Member
3 Posts |
Posted - 2010-02-26 : 00:15:28
|
| i want to start the whole thing via a function or stored procedureis this possible ?is it possible to create 8 SPs and start them serialized ?or what else can I do to solve this problem using T-SQL only (no SSIS) ? |
|
|
|
Jaime
Starting Member
19 Posts |
Posted - 2010-02-26 : 14:56:46
|
quote:
Originally posted by Gawan
i want to start the whole thing via a function or stored procedure
Why? Asynchronous programming is a good example of where T-SQL is a poor choice of language. The only way I can see to get it done is to write a CLR procedure that calls all eight queries asynchronously. I'm pretty sure that it will end up being a difficult to maintain procedure because it will have to connect back to the server eight separate times. There's few things that are more ugly that having to make an ADO.Net connection back to the current server with a hardcoded connection string from a CLR procedure.The simplest solution is to create a function in the next higher layer in your application stack that calls all eight queries. Of course, you could write eight stored procedures if calling raw SQL statements from the data access layer isn't something you want to do. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-26 : 15:29:23
|
You could launch 8 jobs from your Sproc.But I'll eat my hat if that turns out to be a solution that is actually practicable  |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-26 : 18:35:17
|
| look into service broker if this really needs to be asynchronous |
|
|
|
|
|
|
SQL Queries - Parallelism