PIII Xeno * 4,
512M * 8 运行以下SQL时出错:
SELECT LA009 AS LA009,CMS.MR003,SAS.LA006,COP.MA002,SUM(LA017) AS LA017,SUM(LA018)AS LA018
FROM SASLA AS SAS
LEFT JOIN COPMA AS COP ON SAS.LA006=COP.MA001
LEFT JOIN CMSMR AS CMS ON SAS.LA009=CMS.MR002 and MR001='1'
Where (SAS.LA009 <> '' and (LA009 IN ('0001')) AND LA015 BETWEEN '20020101 00:00:00.000' AND '20020131 00:00:00.000')
GROUP BY LA009,CMS.MR003,SAS.LA006,COP.MA002
ORDER BY LA009,SAS.LA006 错误如下:
Server: Msg 8650, Level 13, State 127, Line 1
Intra-query parallelism caused your server command (process ID #93) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1).表的情况:
SASLA: 无主键,无索引。 900,000行
COPMA: PK: MA001 3,000行
CMSMR: PK: MR001+MR002 200行在单处理器上是没有问题的,在查询分析器中也看过执行计划,不可能不用多处理器。我应该怎么办?怎么样修改一下这个SQL?
512M * 8 运行以下SQL时出错:
SELECT LA009 AS LA009,CMS.MR003,SAS.LA006,COP.MA002,SUM(LA017) AS LA017,SUM(LA018)AS LA018
FROM SASLA AS SAS
LEFT JOIN COPMA AS COP ON SAS.LA006=COP.MA001
LEFT JOIN CMSMR AS CMS ON SAS.LA009=CMS.MR002 and MR001='1'
Where (SAS.LA009 <> '' and (LA009 IN ('0001')) AND LA015 BETWEEN '20020101 00:00:00.000' AND '20020131 00:00:00.000')
GROUP BY LA009,CMS.MR003,SAS.LA006,COP.MA002
ORDER BY LA009,SAS.LA006 错误如下:
Server: Msg 8650, Level 13, State 127, Line 1
Intra-query parallelism caused your server command (process ID #93) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1).表的情况:
SASLA: 无主键,无索引。 900,000行
COPMA: PK: MA001 3,000行
CMSMR: PK: MR001+MR002 200行在单处理器上是没有问题的,在查询分析器中也看过执行计划,不可能不用多处理器。我应该怎么办?怎么样修改一下这个SQL?
降格为单处理器运行,这样虽然不会并行处理,但其它CPU可以处理其它事务了
参见:
不要对同一查询内的单个表使用多个别名以模拟索引交叉。模拟索引交叉已没有必要,因为 SQL Server 会自动考虑索引交叉并且可以在同一查询内的相同表上使用多个索引。例如,给出下列示例查询:
SELECT * FROM lineitem
WHERE partkey BETWEEN 17000 AND 17100 AND
shipdate BETWEEN '1/1/1994' AND '1/31/1994"SQL Server 可以在 partkey 和 shipdate 列上都使用索引,然后在两个子集之间执行哈希匹配以获得索引交叉。希望这个对你有所提示。
谢谢大家。To peiweiwei(一指残):
设置 max degree of parallelism 为 1 ,不会影响我对4个CPU的使用吧!还是说那就没有并行处理了?