Force a parallel query execution

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.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Force a parallel query execution

Author

Topic

rossbevin
Starting Member

34 Posts

Posted - 2007-06-02 : 13:05:13

I have another post here regarding SQL 2005 running a query 50% slower than on 2000. It was discovered that 2005 runs the query in series whereas 2000 runs it in parallel.

Even with "Cost Threshold For Parallelism" set to a default value – 0, 2005 still executes my query in series. Does anyone know how to force a query to run in parallel in SQL 2005. I specifically want to set it at the database level.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-03 : 01:30:00

What's max degree of parallelism on the server?

X002548
Not Just a Number

15586 Posts

Posted - 2007-06-04 : 10:21:09

It must think that it's cheaper...why not post the query

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rossbevin
Starting Member

34 Posts

Posted - 2007-06-04 : 11:40:28

The max degree of parallelism is set to 0. The statement is as follows.

SELECT * FROM PH_TRDET WHERE CODIV = 'PFL' AND BRANCH = '23' AND PH_YRMTH = '200601'.

X002548
Not Just a Number

15586 Posts

Posted - 2007-06-04 : 14:24:14

How would that incurr parallelism?

Is it a partitioned view

Can you post the plan for it in 2000?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rossbevin
Starting Member

34 Posts

Posted - 2007-06-05 : 11:49:34

Hi Brett,

I am a SQL novice so some of the things you asked I am unfamiliar with. The test SELECT I listed above is on a large table (55,000,000 rows). The three columns mentioned in the select statement are the first 3 columns of the primary index. As I have said, this is just a test that I have used to gauge the speed of performance between SQL 2005 and 2000. My main issue is that I use an application development environment that generates the SELECT statements behind the scenes. When testing half a dozen reports on SQL 2000 and 2005, on identical hardware, I noticed that in all cases 2000 runs 50% faster. That was when I logged a call with Microsoft who determined the problem; the queries always ran in series on 2005 and in parallel in 2000. I just want to know if anyone has come up with a way of forcing 2005 to run all of its queries in parallel; my Microsoft SQL guy didn't have a solution. I have a Quad Xeon server dedicated to SQL 2005 and it just idles along running queries in series.

Kristen
Test

22859 Posts

Posted - 2007-06-05 : 16:49:57

You need to stop trying to run the queries in parallel and just make them run efficiently.

We need to see the DDL for your table PH_TRDET (complete with PK and indexes)

How many rows are involved?

i.e. :

SELECT COUNT(*) FROM PH_TRDET WHERE CODIV = 'PFL' AND BRANCH = '23' AND PH_YRMTH = '200601'

We need to see the Query Plan from SQL 2000 and again, for the exact same database, from SQL 2005. Also the Timing Stats. (See below)

Please make sure that the Stats. are up-to-date in both cases. For a "Port" from SQL 2000 to SQL 2005 the SQL 2005 database should be set to Compatibility Mode 90 and THEN have the indexes and then have the Stats rebuild using FULL_SCAN.

Query plan:


SET SHOWPLAN_TEXT ON
GO

-- ... put query here - e.g.:

SELECT * FROM Northwind.dbo.Products

SET SET SHOWPLAN_TEXT OFF
GO

Timing stats:


SET STATISTICS IO ON; SET STATISTICS TIME ON

-- ... put query here - e.g.:

SELECT * FROM Northwind.dbo.Products

SET STATISTICS IO OFF; SET STATISTICS TIME OFF
GO

Kristen

rossbevin
Starting Member

34 Posts

Posted - 2007-06-08 : 08:33:12

Hi Kristen,

Here are the statistics you asked for. Please note that I no longer have my dual boot test server where I could run the same query for 2000 and 2005 on the same hardware. I also ran the test on the original SELECT statement I used for my testing; the one I sent off to Microsoft together with a database containing the table PH_TRDET. Here are the results of my tests.

***************************************************
PH_TRDET creation script

begin transaction
if exists (select * from sysobjects where name = 'PH_TRDET' and type = 'U')
drop table PH_TRDET
go
create table PH_TRDET
(
CODIV CHAR(3) default ' ' not null
,PC_YRMTH CHAR(6) default ' ' not null
,BRANCH CHAR(2) default ' ' not null
,PC_DAY CHAR(2) default ' ' not null
,PC_TTYPE CHAR(4) default ' ' not null
,PC_TREF CHAR(12) default ' ' not null
,PC_TSEQ CHAR(3) default ' ' not null
,PC_CTCODE CHAR(4) default ' ' not null
,PC_UNITCST decimal(13, 6) default 0 not null
,PC_UNITHRS decimal(13, 6) default 0 not null
,PC_TRDET_SUM_REF CHAR(20) default ' ' not null
,PC_TRDET_SUM_HRS decimal( 8, 3) default 0 not null
,PC_TRDET_SUM_LC decimal( 9, 3) default 0 not null
,PC_TRDET_SUM_QTY decimal(10, 3) default 0 not null
,PC_TRDET_IC03_ITEMS decimal(10, 3) default 0 not null
,PH_TRDET_HISTORY CHAR(1) default ' ' not null
, constraint PK_PH_TRDET primary key clustered (
CODIV
,PC_YRMTH
,BRANCH
,PC_DAY
,PC_TTYPE
,PC_TREF
,PC_TSEQ
,PC_CTCODE
))
go
commit transaction
go

******************************************************

SQL 2005 (much newer faster server)

SET SHOWPLAN_TEXT ON
SELECT * FROM PH_TRDET WHERE CODIV = 'PFL' AND BRANCH = '23' AND (PC_YRMTH BETWEEN '200601' AND '200612')

|--Clustered Index Seek(OBJECT:([DATA].[dbo].[PH_TRDET].[PK_PH_TRDET]), SEEK:([DATA].[dbo].[PH_TRDET].[CODIV]=[@1] AND ([DATA].[dbo].[PH_TRDET].[PC_YRMTH],

[DATA].[dbo].[PH_TRDET].[BRANCH]) >= ([@3], [@2]) AND ([DATA].[dbo].[PH_TRDET].[PC_YRMTH], [DATA].[dbo].[PH_TRDET].[BRANCH]) <= ([@4], [@2])),

WHERE:([DATA].[dbo].[PH_TRDET].[BRANCH]=[@2]) ORDERED FORWARD)

SET STATISTICS IO ON; SET STATISTICS TIME ON
SELECT * FROM PH_TRDET WHERE CODIV = 'PFL' AND BRANCH = '23' AND (PC_YRMTH BETWEEN '200601' AND '200612')

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 435 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

(1724066 row(s) affected)
Table 'PH_TRDET'. Scan count 1, logical reads 76085, physical reads 4, read-ahead reads 76081, lob logical reads 0, lob physical reads 0, lob read-ahead

reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 51977 ms.

SQL 2000 (old slower server)

SET SHOWPLAN_TEXT ON
SELECT * FROM PH_TRDET WHERE CODIV = 'PFL' AND BRANCH = '23' AND (PC_YRMTH BETWEEN '200601' AND '200612')

|--Parallelism(Gather Streams)
|--Clustered Index Seek(OBJECT:([DATA].[dbo].[PH_TRDET].[PK_PH_TRDET]), SEEK:([PH_TRDET].[CODIV]=[@1] AND ([PH_TRDET].[PC_YRMTH], [PH_TRDET].[BRANCH])

>= ([@3], [@2]) AND ([PH_TRDET].[PC_YRMTH], [PH_TRDET].[BRANCH]) <= ([@4], [@2])), WHERE:([PH_TRD

SET STATISTICS IO ON; SET STATISTICS TIME ON
SELECT * FROM PH_TRDET WHERE CODIV = 'PFL' AND BRANCH = '23' AND (PC_YRMTH BETWEEN '200601' AND '200612')

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(1724066 row(s) affected)

Table 'PH_TRDET'. Scan count 4, logical reads 75667, physical reads 8117, read-ahead reads 22872.

SQL Server Execution Times:
CPU time = 27390 ms, elapsed time = 71595 ms.

Kristen
Test

22859 Posts

Posted - 2007-06-08 : 10:22:35

OK, through my eyes I just see questions that I don't know the answer to. Here they are:

(Before that, this particular query DID come from Profiling the queries Pro-IV is sending to SQL Server, right? its not an ASSUMPTION of what it MIGHT be sending is it?)

"(1,724,066 row(s) affected)"

Its a heck of a lot of data to pull ... I presume this is then analysed, or aggregated, or filtered Client site ... which would be much better done Server-side - but that's something that can't be adjusted. Its the same for both SQL 2000 and SQL 2005 of course.

"SQL 2005
Table 'PH_TRDET'. Scan count 1, logical reads 76085
SQL 2000
Table 'PH_TRDET'. Scan count 4, logical reads 75667"

So that does indeed look as though SQL 2000 has split the reads into 4 separate quarters, and read them concurrently. If the whole lot is in RAM to start with I can't see how this is faster, and if its on a single disk channel bouncing the heads around it can't help in that scenario either ...

Or have I got that wrong?

"SQL 2005
physical reads 4, read-ahead reads 76081
CPU time = 0 ms, elapsed time = 51977 ms
SQL 2000
physical reads 8117, read-ahead reads 22872
CPU time = 27390 ms, elapsed time = 71595 ms."

OK, so all the SQL2005 stuff was cached (for this particular instance of this query), and SQL 2000 wasn't.

But how does SQL 2005 figure its ZERO CPU ?

The Query Plans are identical (the SQL 2000 one has a bit truncated off the end, so I'm assuming that was the same)

Given the sequence of the columns in the PK:

CODIV
,PC_YRMTH
,BRANCH

the Query Plan for the:
[CODIV]=[@1]
bit makes sense, but I don't understand why the two columns:
([PC_YRMTH], [BRANCH]) >= ([@3], [@2]) AND
([PC_YRMTH], [BRANCH]) <= ([@4], [@2])
are lumped together, rather than just linear. However, its the same for both 2000/2005.

I'm struggling with how parallelism gets the data quicker for a straightforward Clustered Index Seek. Perhaps the absence of an ORDER BY allows SQL 2000 to split the job across 4 processors as there is no requirement for it to be sorted. But I can't believe that a single CPU cannot keep the Stream to the Client 100% utilised ...

(I presume the Pro-IV stuff is on a DIFFERENT machine?)

Kristen

rossbevin
Starting Member

34 Posts

Posted - 2007-06-08 : 12:31:12

Kristen,

I hope I haven't confused you but the above statement is not generated by Pro-IV; it is the statement I have been using to test on both 2000 and 2005 with Pro-IV out of the picture. Having said that though this statement would be similar to what Pro-IV would produce. When I did my testing on identical hardware for 2000 and 2005 I first noticed that Pro-IV reports took on average 50% longer to run on 2005. I then started to test with my own query which also took 50% longer to run. It was then that I requested Microsoft's help which determined that 2005 ran the query in series and 2000 in parallel.

See my other post regarding how Pro-IV works.

Kristen
Test

22859 Posts

Posted - 2007-06-09 : 04:15:26

"Having said that though this statement would be similar to what Pro-IV would produce"

That's fine provide you have seen, via SQL Profiler, a REAL statement that AS SENT to SQL Server by Pro-IV. Otherwise you are just guessing ... Pro-IV or even the ODBC/OLEDB/ADO layer may have manipulated the query, and whilst it will in essence be the same thing, its important to be trying to solve the actual problem, not a hypothetical example. I'm sure you get my meaning ...

For example: Something may be parameterising the query; your example has no ORDER BY - and if in fact Pro-IV is adding an ORDER BY it may make the world of difference to what actions are need, bu that's all speculation until I know that this is the Real Deal.

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2007-06-11 : 09:47:45

Well, if you did call M$, then what did they say?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2007-06-11 : 10:27:15

"Well, if you did call M$, then what did they say?"

I think it was this:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81423&whichpage=2#310822

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources