I posted yesterday that a particular query takes 50 minutes on a new, much
more powerful, server compared to the old one.
The new one is running Windows 2000 as opposed to NT 4.
Both are running SQL Server 7 standard edition, though the new one has SP4
installed.
I think of real importance here is that the new one has dual Xeon 2.4 Ghz
processors with hyperthreading. This makes it appear to the OS and to SQL
Server as if it has 4 processors. The old server had dual PII 400 Mhz
processors.
I have improved things a lot by going into the SQL Server properties in
Enterprise Manager and, under Processor, changing the Parallelism to use 1
processor. Note that because it's hyperthreaded, 4 CPUs are shown.
I've run the Profiler on the query in question and the Execution Plan event
differs substantially between configurations.
Now I need help interpreting them! Here they are below.
I wonder whether I should spend my time rewriting the query, ie, breaking it
into two, rather than trying to make SQL Server work quicker.
I notice "nested loops" doesn't appear with the new server.
Anyhow, advice v. much appreciated:
(1) Old server, works a treat, executes in tens of seconds
Execution Tree
--
Nested Loops(Inner Join)
|--Bookmark Lookup(BOOKMARK:([Bmk1001]),
OBJECT:([TriSysV5].[dbo].[Contract]) WITH PREFETCH)
| |--Nested Loops(Inner Join)
| |--Hash Match(Inner Join,
HASH:([Skill].[SkillId])=([ContractSkill].[SkillId]))
| | |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill]),
SEEK:([Skill].[SkillCategoryId]=105) ORDERED)
| | |--Nested Loops(Inner Join)
| | |--Hash Match(Inner Join,
HASH:([Skill_2].[SkillId])=([ContractSkill_4].[SkillId]))
| | | |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill] AS [Skill_2]),
SEEK:([Skill_2].[SkillCategoryId]=42) ORDERED)
| | | |--Merge Join(Inner Join, MANY-TO-MANY
MERGE:([ContractSkill_4].[ContractId])=([ContractSkill_5].[ContractId]),
RESIDUAL:([ContractSkill_4].[ContractId]=[ContractSkill_1].[ContractId]))
| | | |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20] AS
[ContractSkill_4]), ORDERED)
| | | |--Sort(ORDER
BY:([ContractSkill_1].[ContractId] ASC))
| | | |--Hash Match(Inner Join,
HASH:([Skill_1].[SkillId])=([ContractSkill_2].[SkillId]))
| | | |--Bookmark
Lookup(BOOKMARK:([Bmk1008]), OBJECT:([TriSysV5].[dbo].[Skill] AS [Skill_1]))
| | | | |--Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[PK_Skill_1__11] AS [Skill_1]),
SEEK:([Skill_1].[SkillId] BETWEEN 281 AND 284) ORDERED)
| | | |--Hash Match(Inner Join,
HASH:([ContractSkill_5].[ContractId])=([ContractSkill_1].[ContractId]))
| | | |--Merge Join(Inner Join,
MANY-TO-MANY
MERGE:([ContractSkill_2].[ContractId])=([ContractSkill_5].[ContractId]),
RESIDUAL:([ContractSkill_2].[ContractId]=[ContractSkill_5].[ContractId]))
| | | | |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20] AS
[ContractSkill_2]), ORDERED)
| | | | |--Sort(ORDER
BY:([ContractSkill_5].[ContractId] ASC))
| | | | |--Nested
Loops(Inner Join)
| | | | |--Clustered
Index Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill] AS
[Skill_3]), SEEK:([Skill_3].[SkillCategoryId]=104) ORDERED)
| | | | |--Clustered
Index Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[CLusteredIndex] AS
[ContractSkill_5]), SEEK:([ContractSkill_5].[SkillId]=[Skill_3].[SkillId])
ORDERED)
| | | |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[CLusteredIndex] AS
[ContractSkill_1]), SEEK:([ContractSkill_1].[SkillId] BETWEEN 264 AND 266)
ORDERED)
| | |--Index
Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20]),
SEEK:([ContractSkill].[ContractId]=[ContractSkill_5].[ContractId]) ORDERED)
| |--Index
Seek(OBJECT:([TriSysV5].[dbo].[Contract].[PK_Contract_1__11]),
SEEK:([Contract].[ContractId]=[ContractSkill_5].[ContractId]) ORDERED)
|--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[ContractConfigFields].[PK_ContractConfigField
s]), SEEK:([ContractConfigFields].[EntityId]=[ContractSkill_5].[ContractId])
ORDERED)
(2) New server, default settings, ie, all processors used for parallelism.
Takes tens of minutes to run and uses up 100% cpu.
Execution Tree
--
Parallelism(Gather Streams)
|--Hash Match(Inner Join,
HASH:([Skill_3].[SkillId])=([ContractSkill_5].[SkillId]))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([Skill_3].[SkillId]))
| |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill] AS [Skill_3]),
SEEK:([Skill_3].[SkillCategoryId]=104) ORDERED)
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([ContractSkill_5].[SkillId]))
|--Hash Match(Inner Join,
HASH:([Skill_2].[SkillId])=([ContractSkill_4].[SkillId]))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([Skill_2].[SkillId]))
| |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill] AS [Skill_2]),
SEEK:([Skill_2].[SkillCategoryId]=42) ORDERED)
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([ContractSkill_4].[SkillId]))
|--Hash Match(Inner Join,
HASH:([Skill].[SkillId])=([ContractSkill].[SkillId]))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([Skill].[SkillId]))
| |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill]),
SEEK:([Skill].[SkillCategoryId]=105) ORDERED)
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([ContractSkill].[SkillId]))
|--Merge Join(Inner Join, MANY-TO-MANY
MERGE:([ContractConfigFields].[EntityId])=([ContractSkill_1].[ContractId]),
RESIDUAL:([ContractSkill_1].[ContractId]=[ContractConfigFields].[EntityId]))
|--Merge Join(Inner Join, MANY-TO-MANY
MERGE:([ContractConfigFields].[EntityId])=([ContractSkill].[ContractId]),
RESIDUAL:([ContractSkill].[ContractId]=[ContractConfigFields].[EntityId]))
| |--Merge Join(Inner Join,
MANY-TO-MANY
MERGE:([ContractConfigFields].[EntityId])=([ContractSkill_4].[ContractId]),
RESIDUAL:([ContractSkill_4].[ContractId]=[ContractConfigFields].[EntityId]))
| | |--Merge Join(Inner Join,
MANY-TO-MANY
MERGE:([ContractSkill_5].[ContractId])=([ContractConfigFields].[EntityId]),
RESIDUAL:([ContractSkill_5].[ContractId]=[ContractConfigFields].[EntityId]))
| | |
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([ContractSkill_5].[ContractId]), ORDER
BY:([ContractSkill_5].[ContractId] ASC))
| | | | |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20] AS
[ContractSkill_5]), ORDERED)
| | | |--Merge Join(Inner
Join, MERGE:([ContractConfigFields].[EntityId])=([Contract].[ContractId]),
RESIDUAL:([Contract].[ContractId]=[ContractConfigFields].[EntityId]))
| | |
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([ContractConfigFields].[EntityId]), ORDER
BY:([ContractConfigFields].[EntityId] ASC))
| | | | |--Clustered
Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractConfigFields].[PK_ContractConfigField
s]), ORDERED)
| | | |--Sort(ORDER
BY:([Contract].[ContractId] ASC))
| | | |--Hash
Match(Inner Join,
HASH:([ContractSkill_2].[ContractId])=([Contract].[ContractId]))
| | |
|--Parallelism(Distribute Streams, PARTITION
COLUMNS:([ContractSkill_2].[ContractId]))
| | | |
|--Nested Loops(Inner Join)
| | | |
|--Bookmark Lookup(BOOKMARK:([Bmk1008]), OBJECT:([TriSysV5].[dbo].[Skill] AS
[Skill_1]))
| | | |
| |--Index Seek(OBJECT:([TriSysV5].[dbo].[Skill].[PK_Skill_1__11] AS
[Skill_1]), SEEK:([Skill_1].[SkillId] BETWEEN 281 AND 284) ORDERED)
| | | |
|--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[CLusteredIndex] AS
[ContractSkill_2]), SEEK:([ContractSkill_2].[SkillId]=[Skill_1].[SkillId])
ORDERED)
| | |
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([Contract].[ContractId]))
| | |
|--Clustered Index
Scan(OBJECT:([TriSysV5].[dbo].[Contract].[ContractContactId]))
| | |--Parallelism(Repartition
Streams, PARTITION COLUMNS:([ContractSkill_4].[ContractId]), ORDER
BY:([ContractSkill_4].[ContractId] ASC))
| | |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20] AS
[ContractSkill_4]), ORDERED)
| |--Parallelism(Repartition
Streams, PARTITION COLUMNS:([ContractSkill].[ContractId]), ORDER
BY:([ContractSkill].[ContractId] ASC))
| |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20]),
ORDERED)
|--Sort(ORDER
BY:([ContractSkill_1].[ContractId] ASC))
|--Parallelism(Repartition
Streams, PARTITION COLUMNS:([ContractSkill_1].[ContractId]))
|--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[CLusteredIndex] AS
[ContractSkill_1]), SEEK:([ContractSkill_1].[SkillId] BETWEEN 264 AND 266)
ORDERED)
(3) New server, forcing only 1 processor to be used under Parallelism, works
but still much slower than (1) above.
Execution Tree
--
Hash Match(Inner Join,
HASH:([Skill_3].[SkillId])=([ContractSkill_5].[SkillId]))
|--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill] AS [Skill_3]),
SEEK:([Skill_3].[SkillCategoryId]=104) ORDERED)
|--Hash Match(Inner Join,
HASH:([Skill_2].[SkillId])=([ContractSkill_4].[SkillId]))
|--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill] AS [Skill_2]),
SEEK:([Skill_2].[SkillCategoryId]=42) ORDERED)
|--Hash Match(Inner Join,
HASH:([Skill].[SkillId])=([ContractSkill].[SkillId]))
|--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill]),
SEEK:([Skill].[SkillCategoryId]=105) ORDERED)
|--Merge Join(Inner Join, MANY-TO-MANY
MERGE:([ContractConfigFields].[EntityId])=([ContractSkill_1].[ContractId]),
RESIDUAL:([ContractSkill_1].[ContractId]=[ContractConfigFields].[EntityId]))
|--Merge Join(Inner Join, MANY-TO-MANY
MERGE:([ContractConfigFields].[EntityId])=([ContractSkill].[ContractId]),
RESIDUAL:([ContractSkill].[ContractId]=[ContractConfigFields].[EntityId]))
| |--Merge Join(Inner Join, MANY-TO-MANY
MERGE:([ContractConfigFields].[EntityId])=([ContractSkill_4].[ContractId]),
RESIDUAL:([ContractSkill_4].[ContractId]=[ContractConfigFields].[EntityId]))
| | |--Merge Join(Inner Join, MANY-TO-MANY
MERGE:([ContractSkill_5].[ContractId])=([ContractConfigFields].[EntityId]),
RESIDUAL:([ContractSkill_5].[ContractId]=[ContractConfigFields].[EntityId]))
| | | |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20] AS
[ContractSkill_5]), ORDERED)
| | | |--Merge Join(Inner Join,
MERGE:([ContractConfigFields].[EntityId])=([Contract].[ContractId]),
RESIDUAL:([Contract].[ContractId]=[ContractConfigFields].[EntityId]))
| | | |--Clustered Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractConfigFields].[PK_ContractConfigField
s]), ORDERED)
| | | |--Sort(ORDER
BY:([Contract].[ContractId] ASC))
| | | |--Hash Match(Inner Join,
HASH:([ContractSkill_2].[ContractId])=([Contract].[ContractId]))
| | | |--Nested Loops(Inner Join)
| | | | |--Bookmark
Lookup(BOOKMARK:([Bmk1008]), OBJECT:([TriSysV5].[dbo].[Skill] AS [Skill_1]))
| | | | | |--Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[PK_Skill_1__11] AS [Skill_1]),
SEEK:([Skill_1].[SkillId] BETWEEN 281 AND 284) ORDERED)
| | | | |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[CLusteredIndex] AS
[ContractSkill_2]), SEEK:([ContractSkill_2].[SkillId]=[Skill_1].[SkillId])
ORDERED)
| | | |--Clustered Index
Scan(OBJECT:([TriSysV5].[dbo].[Contract].[ContractContactId]))
| | |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20] AS
[ContractSkill_4]), ORDERED)
| |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20]),
ORDERED)
|--Sort(ORDER BY:([ContractSkill_1].[ContractId] ASC))
|--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[CLusteredIndex] AS
[ContractSkill_1]), SEEK:([ContractSkill_1].[SkillId] BETWEEN 264 AND 266)
ORDERED)This is a multi-part message in MIME format.
--=_NextPart_000_0100_01C39497.D88010F0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
I'd focus on cleaning up this one query. I wouldn't turn off =parallelism for the whole SQL Server instance. Rather, I would use the =OPTION (MAXDOP 1) for this one query. Also, I'd run the query through =the Index Tuning Wizard to see if there are some suggestions there. =Finally, if you could post the DDL and the query, we may be able to give =it a tweak.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Paul Welsh" <reply.to.group.please@.microsoft.com> wrote in message =news:eRqU8aLlDHA.2528@.TK2MSFTNGP10.phx.gbl...
I need some expert help here!
I posted yesterday that a particular query takes 50 minutes on a new, =much
more powerful, server compared to the old one.
The new one is running Windows 2000 as opposed to NT 4.
Both are running SQL Server 7 standard edition, though the new one has =SP4
installed.
I think of real importance here is that the new one has dual Xeon 2.4 =Ghz
processors with hyperthreading. This makes it appear to the OS and to =SQL
Server as if it has 4 processors. The old server had dual PII 400 Mhz
processors.
I have improved things a lot by going into the SQL Server properties in
Enterprise Manager and, under Processor, changing the Parallelism to use =1
processor. Note that because it's hyperthreaded, 4 CPUs are shown.
I've run the Profiler on the query in question and the Execution Plan =event
differs substantially between configurations.
Now I need help interpreting them! Here they are below.
I wonder whether I should spend my time rewriting the query, ie, =breaking it
into two, rather than trying to make SQL Server work quicker.
I notice "nested loops" doesn't appear with the new server.
Anyhow, advice v. much appreciated:
(1) Old server, works a treat, executes in tens of seconds
Execution Tree
--
Nested Loops(Inner Join)
|--Bookmark Lookup(BOOKMARK:([Bmk1001]),
OBJECT:([TriSysV5].[dbo].[Contract]) WITH PREFETCH)
| |--Nested Loops(Inner Join)
| |--Hash Match(Inner Join,
HASH:([Skill].[SkillId])=3D([ContractSkill].[SkillId]))
| | |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill]),
SEEK:([Skill].[SkillCategoryId]=3D105) ORDERED)
| | |--Nested Loops(Inner Join)
| | |--Hash Match(Inner Join,
HASH:([Skill_2].[SkillId])=3D([ContractSkill_4].[SkillId]))
| | | |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill] AS =[Skill_2]),
SEEK:([Skill_2].[SkillCategoryId]=3D42) ORDERED)
| | | |--Merge Join(Inner Join, MANY-TO-MANY
MERGE:([ContractSkill_4].[ContractId])=3D([ContractSkill_5].[ContractId])=,
RESIDUAL:([ContractSkill_4].[ContractId]=3D[ContractSkill_1].[ContractId]=))
| | | |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20] =AS
[ContractSkill_4]), ORDERED)
| | | |--Sort(ORDER
BY:([ContractSkill_1].[ContractId] ASC))
| | | |--Hash Match(Inner Join,
HASH:([Skill_1].[SkillId])=3D([ContractSkill_2].[SkillId]))
| | | |--Bookmark
Lookup(BOOKMARK:([Bmk1008]), OBJECT:([TriSysV5].[dbo].[Skill] AS =[Skill_1]))
| | | | |--Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[PK_Skill_1__11] AS [Skill_1]),
SEEK:([Skill_1].[SkillId] BETWEEN 281 AND 284) ORDERED)
| | | |--Hash Match(Inner Join,
HASH:([ContractSkill_5].[ContractId])=3D([ContractSkill_1].[ContractId]))=
| | | |--Merge Join(Inner Join,
MANY-TO-MANY
MERGE:([ContractSkill_2].[ContractId])=3D([ContractSkill_5].[ContractId])=,
RESIDUAL:([ContractSkill_2].[ContractId]=3D[ContractSkill_5].[ContractId]=))
| | | | |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20] =AS
[ContractSkill_2]), ORDERED)
| | | | |--Sort(ORDER
BY:([ContractSkill_5].[ContractId] ASC))
| | | | |--Nested
Loops(Inner Join)
| | | | =|--Clustered
Index Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill] AS
[Skill_3]), SEEK:([Skill_3].[SkillCategoryId]=3D104) ORDERED)
| | | | =|--Clustered
Index Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[CLusteredIndex] AS
[ContractSkill_5]), =SEEK:([ContractSkill_5].[SkillId]=3D[Skill_3].[SkillId])
ORDERED)
| | | |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[CLusteredIndex] AS
[ContractSkill_1]), SEEK:([ContractSkill_1].[SkillId] BETWEEN 264 AND =266)
ORDERED)
| | |--Index
Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20]),
SEEK:([ContractSkill].[ContractId]=3D[ContractSkill_5].[ContractId]) =ORDERED)
| |--Index
Seek(OBJECT:([TriSysV5].[dbo].[Contract].[PK_Contract_1__11]),
SEEK:([Contract].[ContractId]=3D[ContractSkill_5].[ContractId]) ORDERED)
|--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[ContractConfigFields].[PK_ContractConfigFi=eld
s]), =SEEK:([ContractConfigFields].[EntityId]=3D[ContractSkill_5].[ContractId])=
ORDERED)
(2) New server, default settings, ie, all processors used for =parallelism.
Takes tens of minutes to run and uses up 100% cpu.
Execution Tree
--
Parallelism(Gather Streams)
|--Hash Match(Inner Join,
HASH:([Skill_3].[SkillId])=3D([ContractSkill_5].[SkillId]))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([Skill_3].[SkillId]))
| |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill] AS =[Skill_3]),
SEEK:([Skill_3].[SkillCategoryId]=3D104) ORDERED)
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([ContractSkill_5].[SkillId]))
|--Hash Match(Inner Join,
HASH:([Skill_2].[SkillId])=3D([ContractSkill_4].[SkillId]))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([Skill_2].[SkillId]))
| |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill] AS =[Skill_2]),
SEEK:([Skill_2].[SkillCategoryId]=3D42) ORDERED)
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([ContractSkill_4].[SkillId]))
|--Hash Match(Inner Join,
HASH:([Skill].[SkillId])=3D([ContractSkill].[SkillId]))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([Skill].[SkillId]))
| |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill]),
SEEK:([Skill].[SkillCategoryId]=3D105) ORDERED)
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([ContractSkill].[SkillId]))
|--Merge Join(Inner Join, MANY-TO-MANY
MERGE:([ContractConfigFields].[EntityId])=3D([ContractSkill_1].[ContractI=d]),
RESIDUAL:([ContractSkill_1].[ContractId]=3D[ContractConfigFields].[Entity=Id]))
|--Merge Join(Inner Join, =MANY-TO-MANY
MERGE:([ContractConfigFields].[EntityId])=3D([ContractSkill].[ContractId]=),
RESIDUAL:([ContractSkill].[ContractId]=3D[ContractConfigFields].[EntityId=]))
| |--Merge Join(Inner Join,
MANY-TO-MANY
MERGE:([ContractConfigFields].[EntityId])=3D([ContractSkill_4].[ContractI=d]),
RESIDUAL:([ContractSkill_4].[ContractId]=3D[ContractConfigFields].[Entity=Id]))
| | |--Merge Join(Inner Join,
MANY-TO-MANY
MERGE:([ContractSkill_5].[ContractId])=3D([ContractConfigFields].[EntityI=d]),
RESIDUAL:([ContractSkill_5].[ContractId]=3D[ContractConfigFields].[Entity=Id]))
| | |
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([ContractSkill_5].[ContractId]), ORDER
BY:([ContractSkill_5].[ContractId] ASC))
| | | | |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20] =AS
[ContractSkill_5]), ORDERED)
| | | |--Merge Join(Inner
Join, =MERGE:([ContractConfigFields].[EntityId])=3D([Contract].[ContractId]),
RESIDUAL:([Contract].[ContractId]=3D[ContractConfigFields].[EntityId]))
| | |
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([ContractConfigFields].[EntityId]), ORDER
BY:([ContractConfigFields].[EntityId] ASC))
| | | | =|--Clustered
Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractConfigFields].[PK_ContractConfigFi=eld
s]), ORDERED)
| | | |--Sort(ORDER
BY:([Contract].[ContractId] ASC))
| | | |--Hash
Match(Inner Join,
HASH:([ContractSkill_2].[ContractId])=3D([Contract].[ContractId]))
| | |
|--Parallelism(Distribute Streams, PARTITION
COLUMNS:([ContractSkill_2].[ContractId]))
| | | |
|--Nested Loops(Inner Join)
| | | |
|--Bookmark Lookup(BOOKMARK:([Bmk1008]), =OBJECT:([TriSysV5].[dbo].[Skill] AS
[Skill_1]))
| | | |
| |--Index Seek(OBJECT:([TriSysV5].[dbo].[Skill].[PK_Skill_1__11] AS
[Skill_1]), SEEK:([Skill_1].[SkillId] BETWEEN 281 AND 284) ORDERED)
| | | |
|--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[CLusteredIndex] AS
[ContractSkill_2]), =SEEK:([ContractSkill_2].[SkillId]=3D[Skill_1].[SkillId])
ORDERED)
| | |
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([Contract].[ContractId]))
| | |
|--Clustered Index
Scan(OBJECT:([TriSysV5].[dbo].[Contract].[ContractContactId]))
| | =|--Parallelism(Repartition
Streams, PARTITION COLUMNS:([ContractSkill_4].[ContractId]), ORDER
BY:([ContractSkill_4].[ContractId] ASC))
| | |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20] =AS
[ContractSkill_4]), ORDERED)
| |--Parallelism(Repartition
Streams, PARTITION COLUMNS:([ContractSkill].[ContractId]), ORDER
BY:([ContractSkill].[ContractId] ASC))
| |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20]),
ORDERED)
|--Sort(ORDER
BY:([ContractSkill_1].[ContractId] ASC))
|--Parallelism(Repartition
Streams, PARTITION COLUMNS:([ContractSkill_1].[ContractId]))
|--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[CLusteredIndex] AS
[ContractSkill_1]), SEEK:([ContractSkill_1].[SkillId] BETWEEN 264 AND =266)
ORDERED)
(3) New server, forcing only 1 processor to be used under Parallelism, =works
but still much slower than (1) above.
Execution Tree
--
Hash Match(Inner Join,
HASH:([Skill_3].[SkillId])=3D([ContractSkill_5].[SkillId]))
|--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill] AS =[Skill_3]),
SEEK:([Skill_3].[SkillCategoryId]=3D104) ORDERED)
|--Hash Match(Inner Join,
HASH:([Skill_2].[SkillId])=3D([ContractSkill_4].[SkillId]))
|--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill] AS =[Skill_2]),
SEEK:([Skill_2].[SkillCategoryId]=3D42) ORDERED)
|--Hash Match(Inner Join,
HASH:([Skill].[SkillId])=3D([ContractSkill].[SkillId]))
|--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill]),
SEEK:([Skill].[SkillCategoryId]=3D105) ORDERED)
|--Merge Join(Inner Join, MANY-TO-MANY
MERGE:([ContractConfigFields].[EntityId])=3D([ContractSkill_1].[ContractI=d]),
RESIDUAL:([ContractSkill_1].[ContractId]=3D[ContractConfigFields].[Entity=Id]))
|--Merge Join(Inner Join, MANY-TO-MANY
MERGE:([ContractConfigFields].[EntityId])=3D([ContractSkill].[ContractId]=),
RESIDUAL:([ContractSkill].[ContractId]=3D[ContractConfigFields].[EntityId=]))
| |--Merge Join(Inner Join, MANY-TO-MANY
MERGE:([ContractConfigFields].[EntityId])=3D([ContractSkill_4].[ContractI=d]),
RESIDUAL:([ContractSkill_4].[ContractId]=3D[ContractConfigFields].[Entity=Id]))
| | |--Merge Join(Inner Join, MANY-TO-MANY
MERGE:([ContractSkill_5].[ContractId])=3D([ContractConfigFields].[EntityI=d]),
RESIDUAL:([ContractSkill_5].[ContractId]=3D[ContractConfigFields].[Entity=Id]))
| | | |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20] =AS
[ContractSkill_5]), ORDERED)
| | | |--Merge Join(Inner Join,
MERGE:([ContractConfigFields].[EntityId])=3D([Contract].[ContractId]),
RESIDUAL:([Contract].[ContractId]=3D[ContractConfigFields].[EntityId]))
| | | |--Clustered Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractConfigFields].[PK_ContractConfigFi=eld
s]), ORDERED)
| | | |--Sort(ORDER
BY:([Contract].[ContractId] ASC))
| | | |--Hash Match(Inner Join,
HASH:([ContractSkill_2].[ContractId])=3D([Contract].[ContractId]))
| | | |--Nested Loops(Inner =Join)
| | | | |--Bookmark
Lookup(BOOKMARK:([Bmk1008]), OBJECT:([TriSysV5].[dbo].[Skill] AS =[Skill_1]))
| | | | | |--Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[PK_Skill_1__11] AS [Skill_1]),
SEEK:([Skill_1].[SkillId] BETWEEN 281 AND 284) ORDERED)
| | | | |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[CLusteredIndex] AS
[ContractSkill_2]), =SEEK:([ContractSkill_2].[SkillId]=3D[Skill_1].[SkillId])
ORDERED)
| | | |--Clustered Index
Scan(OBJECT:([TriSysV5].[dbo].[Contract].[ContractContactId]))
| | |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20] =AS
[ContractSkill_4]), ORDERED)
| |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20]),
ORDERED)
|--Sort(ORDER BY:([ContractSkill_1].[ContractId] ASC))
|--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[CLusteredIndex] AS
[ContractSkill_1]), SEEK:([ContractSkill_1].[SkillId] BETWEEN 264 AND =266)
ORDERED)
--=_NextPart_000_0100_01C39497.D88010F0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I'd focus on cleaning up this one =query. I wouldn't turn off parallelism for the whole SQL Server instance. =Rather, I would use the OPTION (MAXDOP 1) for this one query. Also, I'd run =the query through the Index Tuning Wizard to see if there are some =suggestions there. Finally, if you could post the DDL and the query, we may be =able to give it a tweak.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Paul Welsh"
--=_NextPart_000_0100_01C39497.D88010F0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0270_01C394CE.533657B0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Well, here's the query:
SELECT Contract.ContractId, Contract.Reference, Contract.UserId, =Contract.ContactId, Contract.CompanyId, Contract.AddressId, =Contract.CandidateId, Contract.StartDate, Contract.EndDate, =Contract.DateSigned, Contract.Contract, =ContractConfigFields.DaysPerWeek, Contract.HoursPerWeek, =ContractConfigFields.StandardCostRate, Contract.StandardChargeRate, =Skill_1.Skill AS [Standard Rate Type], Skill.Skill AS [Account Manager =2], Skill_2.Skill AS [Charge Currency], Skill_3.Skill AS [Cost Currency]
FROM ((((((ContractSkill AS ContractSkill_1 INNER JOIN (Contract INNER =JOIN ContractSkill ON Contract.ContractId =3D ContractSkill.ContractId) =ON ContractSkill_1.ContractId =3D Contract.ContractId) INNER JOIN Skill =ON ContractSkill.SkillId =3D Skill.SkillId) INNER JOIN ContractSkill AS =ContractSkill_2 ON Contract.ContractId =3D ContractSkill_2.ContractId) =INNER JOIN Skill AS Skill_1 ON ContractSkill_2.SkillId =3D =Skill_1.SkillId) INNER JOIN (Skill AS Skill_2 INNER JOIN ContractSkill =AS ContractSkill_4 ON Skill_2.SkillId =3D ContractSkill_4.SkillId) ON =Contract.ContractId =3D ContractSkill_4.ContractId) INNER JOIN =(ContractSkill AS ContractSkill_5 INNER JOIN Skill AS Skill_3 ON =ContractSkill_5.SkillId =3D Skill_3.SkillId) ON Contract.ContractId =3D =ContractSkill_5.ContractId) INNER JOIN ContractConfigFields ON =Contract.ContractId =3D ContractConfigFields.EntityId
WHERE (((Skill_1.SkillId)>=3D281 And (Skill_1.SkillId)<=3D284) AND =((Skill.SkillCategoryId)=3D105) AND ((ContractSkill_1.SkillId)>=3D264 =And (ContractSkill_1.SkillId)<=3D266) AND =((Skill_2.SkillCategoryId)=3D42) AND ((Skill_3.SkillCategoryId)=3D104));
It was created by Access and, as I say ran in 30 seconds on my old dual =PII-400.
Sorry, what's the DDL?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:Oa8a7lLlDHA.2312@.TK2MSFTNGP12.phx.gbl...
I'd focus on cleaning up this one query. I wouldn't turn off =parallelism for the whole SQL Server instance. Rather, I would use the =OPTION (MAXDOP 1) for this one query. Also, I'd run the query through =the Index Tuning Wizard to see if there are some suggestions there. =Finally, if you could post the DDL and the query, we may be able to give =it a tweak.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Paul Welsh" <reply.to.group.please@.microsoft.com> wrote in message =news:eRqU8aLlDHA.2528@.TK2MSFTNGP10.phx.gbl...
I need some expert help here!
I posted yesterday that a particular query takes 50 minutes on a new, =much
more powerful, server compared to the old one.
The new one is running Windows 2000 as opposed to NT 4.
Both are running SQL Server 7 standard edition, though the new one has =SP4
installed.
I think of real importance here is that the new one has dual Xeon 2.4 =Ghz
processors with hyperthreading. This makes it appear to the OS and to =SQL
Server as if it has 4 processors. The old server had dual PII 400 Mhz
processors.
I have improved things a lot by going into the SQL Server properties =in
Enterprise Manager and, under Processor, changing the Parallelism to =use 1
processor. Note that because it's hyperthreaded, 4 CPUs are shown.
I've run the Profiler on the query in question and the Execution Plan =event
differs substantially between configurations.
Now I need help interpreting them! Here they are below.
I wonder whether I should spend my time rewriting the query, ie, =breaking it
into two, rather than trying to make SQL Server work quicker.
I notice "nested loops" doesn't appear with the new server.
Anyhow, advice v. much appreciated:
(1) Old server, works a treat, executes in tens of seconds
Execution Tree
--
Nested Loops(Inner Join)
|--Bookmark Lookup(BOOKMARK:([Bmk1001]),
OBJECT:([TriSysV5].[dbo].[Contract]) WITH PREFETCH)
| |--Nested Loops(Inner Join)
| |--Hash Match(Inner Join,
HASH:([Skill].[SkillId])=3D([ContractSkill].[SkillId]))
| | |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill]),
SEEK:([Skill].[SkillCategoryId]=3D105) ORDERED)
| | |--Nested Loops(Inner Join)
| | |--Hash Match(Inner Join,
HASH:([Skill_2].[SkillId])=3D([ContractSkill_4].[SkillId]))
| | | |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill] AS =[Skill_2]),
SEEK:([Skill_2].[SkillCategoryId]=3D42) ORDERED)
| | | |--Merge Join(Inner Join, MANY-TO-MANY
=MERGE:([ContractSkill_4].[ContractId])=3D([ContractSkill_5].[ContractId])=,
=RESIDUAL:([ContractSkill_4].[ContractId]=3D[ContractSkill_1].[ContractId]=))
| | | |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20] =AS
[ContractSkill_4]), ORDERED)
| | | |--Sort(ORDER
BY:([ContractSkill_1].[ContractId] ASC))
| | | |--Hash Match(Inner Join,
HASH:([Skill_1].[SkillId])=3D([ContractSkill_2].[SkillId]))
| | | |--Bookmark
Lookup(BOOKMARK:([Bmk1008]), OBJECT:([TriSysV5].[dbo].[Skill] AS =[Skill_1]))
| | | | |--Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[PK_Skill_1__11] AS [Skill_1]),
SEEK:([Skill_1].[SkillId] BETWEEN 281 AND 284) ORDERED)
| | | |--Hash Match(Inner Join,
=HASH:([ContractSkill_5].[ContractId])=3D([ContractSkill_1].[ContractId]))=
| | | |--Merge Join(Inner =Join,
MANY-TO-MANY
=MERGE:([ContractSkill_2].[ContractId])=3D([ContractSkill_5].[ContractId])=,
=RESIDUAL:([ContractSkill_2].[ContractId]=3D[ContractSkill_5].[ContractId]=))
| | | | |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20] =AS
[ContractSkill_2]), ORDERED)
| | | | |--Sort(ORDER
BY:([ContractSkill_5].[ContractId] ASC))
| | | | |--Nested
Loops(Inner Join)
| | | | =|--Clustered
Index Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill] AS
[Skill_3]), SEEK:([Skill_3].[SkillCategoryId]=3D104) ORDERED)
| | | | =|--Clustered
Index Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[CLusteredIndex] =AS
[ContractSkill_5]), =SEEK:([ContractSkill_5].[SkillId]=3D[Skill_3].[SkillId])
ORDERED)
| | | |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[CLusteredIndex] AS
[ContractSkill_1]), SEEK:([ContractSkill_1].[SkillId] BETWEEN 264 AND =266)
ORDERED)
| | |--Index
=Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20]),
SEEK:([ContractSkill].[ContractId]=3D[ContractSkill_5].[ContractId]) =ORDERED)
| |--Index
Seek(OBJECT:([TriSysV5].[dbo].[Contract].[PK_Contract_1__11]),
SEEK:([Contract].[ContractId]=3D[ContractSkill_5].[ContractId]) =ORDERED)
|--Clustered Index
=Seek(OBJECT:([TriSysV5].[dbo].[ContractConfigFields].[PK_ContractConfigFi=eld
s]), =SEEK:([ContractConfigFields].[EntityId]=3D[ContractSkill_5].[ContractId])=
ORDERED)
(2) New server, default settings, ie, all processors used for =parallelism.
Takes tens of minutes to run and uses up 100% cpu.
Execution Tree
--
Parallelism(Gather Streams)
|--Hash Match(Inner Join,
HASH:([Skill_3].[SkillId])=3D([ContractSkill_5].[SkillId]))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([Skill_3].[SkillId]))
| |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill] AS =[Skill_3]),
SEEK:([Skill_3].[SkillCategoryId]=3D104) ORDERED)
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([ContractSkill_5].[SkillId]))
|--Hash Match(Inner Join,
HASH:([Skill_2].[SkillId])=3D([ContractSkill_4].[SkillId]))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([Skill_2].[SkillId]))
| |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill] AS =[Skill_2]),
SEEK:([Skill_2].[SkillCategoryId]=3D42) ORDERED)
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([ContractSkill_4].[SkillId]))
|--Hash Match(Inner Join,
HASH:([Skill].[SkillId])=3D([ContractSkill].[SkillId]))
|--Parallelism(Repartition Streams, =PARTITION
COLUMNS:([Skill].[SkillId]))
| |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill]),
SEEK:([Skill].[SkillCategoryId]=3D105) ORDERED)
|--Parallelism(Repartition Streams, =PARTITION
COLUMNS:([ContractSkill].[SkillId]))
|--Merge Join(Inner Join, MANY-TO-MANY
=MERGE:([ContractConfigFields].[EntityId])=3D([ContractSkill_1].[ContractI=d]),
=RESIDUAL:([ContractSkill_1].[ContractId]=3D[ContractConfigFields].[Entity=Id]))
|--Merge Join(Inner Join, =MANY-TO-MANY
=MERGE:([ContractConfigFields].[EntityId])=3D([ContractSkill].[ContractId]=),
=RESIDUAL:([ContractSkill].[ContractId]=3D[ContractConfigFields].[EntityId=]))
| |--Merge Join(Inner Join,
MANY-TO-MANY
=MERGE:([ContractConfigFields].[EntityId])=3D([ContractSkill_4].[ContractI=d]),
=RESIDUAL:([ContractSkill_4].[ContractId]=3D[ContractConfigFields].[Entity=Id]))
| | |--Merge Join(Inner =Join,
MANY-TO-MANY
=MERGE:([ContractSkill_5].[ContractId])=3D([ContractConfigFields].[EntityI=d]),
=RESIDUAL:([ContractSkill_5].[ContractId]=3D[ContractConfigFields].[Entity=Id]))
| | |
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([ContractSkill_5].[ContractId]), ORDER
BY:([ContractSkill_5].[ContractId] ASC))
| | | | |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20] =AS
[ContractSkill_5]), ORDERED)
| | | |--Merge =Join(Inner
Join, =MERGE:([ContractConfigFields].[EntityId])=3D([Contract].[ContractId]),
=RESIDUAL:([Contract].[ContractId]=3D[ContractConfigFields].[EntityId]))
| | |
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([ContractConfigFields].[EntityId]), ORDER
BY:([ContractConfigFields].[EntityId] ASC))
| | | | =|--Clustered
Index
=Scan(OBJECT:([TriSysV5].[dbo].[ContractConfigFields].[PK_ContractConfigFi=eld
s]), ORDERED)
| | | |--Sort(ORDER
BY:([Contract].[ContractId] ASC))
| | | |--Hash
Match(Inner Join,
HASH:([ContractSkill_2].[ContractId])=3D([Contract].[ContractId]))
| | |
|--Parallelism(Distribute Streams, PARTITION
COLUMNS:([ContractSkill_2].[ContractId]))
| | | |
|--Nested Loops(Inner Join)
| | | |
|--Bookmark Lookup(BOOKMARK:([Bmk1008]), =OBJECT:([TriSysV5].[dbo].[Skill] AS
[Skill_1]))
| | | |
| |--Index Seek(OBJECT:([TriSysV5].[dbo].[Skill].[PK_Skill_1__11] =AS
[Skill_1]), SEEK:([Skill_1].[SkillId] BETWEEN 281 AND 284) ORDERED)
| | | |
|--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[CLusteredIndex] AS
[ContractSkill_2]), =SEEK:([ContractSkill_2].[SkillId]=3D[Skill_1].[SkillId])
ORDERED)
| | |
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([Contract].[ContractId]))
| | |
|--Clustered Index
Scan(OBJECT:([TriSysV5].[dbo].[Contract].[ContractContactId]))
| | =|--Parallelism(Repartition
Streams, PARTITION COLUMNS:([ContractSkill_4].[ContractId]), ORDER
BY:([ContractSkill_4].[ContractId] ASC))
| | |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20] =AS
[ContractSkill_4]), ORDERED)
| |--Parallelism(Repartition
Streams, PARTITION COLUMNS:([ContractSkill].[ContractId]), ORDER
BY:([ContractSkill].[ContractId] ASC))
| |--Index
=Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20]),
ORDERED)
|--Sort(ORDER
BY:([ContractSkill_1].[ContractId] ASC))
|--Parallelism(Repartition
Streams, PARTITION COLUMNS:([ContractSkill_1].[ContractId]))
|--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[CLusteredIndex] AS
[ContractSkill_1]), SEEK:([ContractSkill_1].[SkillId] BETWEEN 264 AND =266)
ORDERED)
(3) New server, forcing only 1 processor to be used under Parallelism, =works
but still much slower than (1) above.
Execution Tree
--
Hash Match(Inner Join,
HASH:([Skill_3].[SkillId])=3D([ContractSkill_5].[SkillId]))
|--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill] AS =[Skill_3]),
SEEK:([Skill_3].[SkillCategoryId]=3D104) ORDERED)
|--Hash Match(Inner Join,
HASH:([Skill_2].[SkillId])=3D([ContractSkill_4].[SkillId]))
|--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill] AS =[Skill_2]),
SEEK:([Skill_2].[SkillCategoryId]=3D42) ORDERED)
|--Hash Match(Inner Join,
HASH:([Skill].[SkillId])=3D([ContractSkill].[SkillId]))
|--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill]),
SEEK:([Skill].[SkillCategoryId]=3D105) ORDERED)
|--Merge Join(Inner Join, MANY-TO-MANY
=MERGE:([ContractConfigFields].[EntityId])=3D([ContractSkill_1].[ContractI=d]),
=RESIDUAL:([ContractSkill_1].[ContractId]=3D[ContractConfigFields].[Entity=Id]))
|--Merge Join(Inner Join, MANY-TO-MANY
=MERGE:([ContractConfigFields].[EntityId])=3D([ContractSkill].[ContractId]=),
=RESIDUAL:([ContractSkill].[ContractId]=3D[ContractConfigFields].[EntityId=]))
| |--Merge Join(Inner Join, MANY-TO-MANY
=MERGE:([ContractConfigFields].[EntityId])=3D([ContractSkill_4].[ContractI=d]),
=RESIDUAL:([ContractSkill_4].[ContractId]=3D[ContractConfigFields].[Entity=Id]))
| | |--Merge Join(Inner Join, MANY-TO-MANY
=MERGE:([ContractSkill_5].[ContractId])=3D([ContractConfigFields].[EntityI=d]),
=RESIDUAL:([ContractSkill_5].[ContractId]=3D[ContractConfigFields].[Entity=Id]))
| | | |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20] =AS
[ContractSkill_5]), ORDERED)
| | | |--Merge Join(Inner Join,
MERGE:([ContractConfigFields].[EntityId])=3D([Contract].[ContractId]),
=RESIDUAL:([Contract].[ContractId]=3D[ContractConfigFields].[EntityId]))
| | | |--Clustered Index
=Scan(OBJECT:([TriSysV5].[dbo].[ContractConfigFields].[PK_ContractConfigFi=eld
s]), ORDERED)
| | | |--Sort(ORDER
BY:([Contract].[ContractId] ASC))
| | | |--Hash Match(Inner Join,
HASH:([ContractSkill_2].[ContractId])=3D([Contract].[ContractId]))
| | | |--Nested Loops(Inner =Join)
| | | | |--Bookmark
Lookup(BOOKMARK:([Bmk1008]), OBJECT:([TriSysV5].[dbo].[Skill] AS =[Skill_1]))
| | | | | |--Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[PK_Skill_1__11] AS [Skill_1]),
SEEK:([Skill_1].[SkillId] BETWEEN 281 AND 284) ORDERED)
| | | | |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[CLusteredIndex] AS
[ContractSkill_2]), =SEEK:([ContractSkill_2].[SkillId]=3D[Skill_1].[SkillId])
ORDERED)
| | | |--Clustered Index
Scan(OBJECT:([TriSysV5].[dbo].[Contract].[ContractContactId]))
| | |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20] =AS
[ContractSkill_4]), ORDERED)
| |--Index
=Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20]),
ORDERED)
|--Sort(ORDER BY:([ContractSkill_1].[ContractId] =ASC))
|--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[CLusteredIndex] AS
[ContractSkill_1]), SEEK:([ContractSkill_1].[SkillId] BETWEEN 264 AND =266)
ORDERED)
--=_NextPart_000_0270_01C394CE.533657B0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Well, here's the query:
SELECT Contract.ContractId, =Contract.Reference, Contract.UserId, Contract.ContactId, Contract.CompanyId, =Contract.AddressId, Contract.CandidateId, Contract.StartDate, Contract.EndDate, =Contract.DateSigned, Contract.Contract, ContractConfigFields.DaysPerWeek, =Contract.HoursPerWeek, ContractConfigFields.StandardCostRate, Contract.StandardChargeRate, Skill_1.Skill AS [Standard Rate Type], Skill.Skill AS [Account Manager =2], Skill_2.Skill AS [Charge Currency], Skill_3.Skill AS [Cost =Currency]FROM ((((((ContractSkill AS ContractSkill_1 INNER JOIN (Contract INNER JOIN ContractSkill ON Contract.ContractId =3D ContractSkill.ContractId) ON ContractSkill_1.ContractId =3D Contract.ContractId) INNER JOIN Skill ON ContractSkill.SkillId =3D Skill.SkillId) INNER JOIN ContractSkill AS ContractSkill_2 ON Contract.ContractId =3D ContractSkill_2.ContractId) =INNER JOIN Skill AS Skill_1 ON ContractSkill_2.SkillId =3D Skill_1.SkillId) INNER =JOIN (Skill AS Skill_2 INNER JOIN ContractSkill AS ContractSkill_4 ON =Skill_2.SkillId =3D ContractSkill_4.SkillId) ON Contract.ContractId =3D =ContractSkill_4.ContractId) INNER JOIN (ContractSkill AS ContractSkill_5 INNER JOIN Skill AS Skill_3 =ON ContractSkill_5.SkillId =3D Skill_3.SkillId) ON Contract.ContractId =3D ContractSkill_5.ContractId) INNER JOIN ContractConfigFields ON Contract.ContractId =3D ContractConfigFields.EntityIdWHERE (((Skill_1.SkillId)>=3D281 And (Skill_1.SkillId)=3D264 =And (ContractSkill_1.SkillId)<=3D266) AND =((Skill_2.SkillCategoryId)=3D42) AND ((Skill_3.SkillCategoryId)=3D104));
It was created by =Access and, as I say ran in 30 seconds on my old dual PII-400.
Sorry, what's the DDL?
"Tom Moreau"
I'd focus on cleaning up this one =query. I wouldn't turn off parallelism for the whole SQL Server instance. =Rather, I would use the OPTION (MAXDOP 1) for this one query. Also, I'd =run the query through the Index Tuning Wizard to see if there are some =suggestions there. Finally, if you could post the DDL and the query, we may =be able to give it a tweak.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Paul Welsh"
--=_NextPart_000_0270_01C394CE.533657B0--|||This is a multi-part message in MIME format.
--=_NextPart_000_02C4_01C394D0.12D78B10
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
The index analysis doesn't recommend any indexes - less than 10 data =pages, supporting indexes already, etc.
"Paul Welsh" <reply.to.group.please@.microsoft.com> wrote in message =news:e$PZ4UMlDHA.708@.TK2MSFTNGP10.phx.gbl...
Well, here's the query:
SELECT Contract.ContractId, Contract.Reference, Contract.UserId, =Contract.ContactId, Contract.CompanyId, Contract.AddressId, =Contract.CandidateId, Contract.StartDate, Contract.EndDate, =Contract.DateSigned, Contract.Contract, =ContractConfigFields.DaysPerWeek, Contract.HoursPerWeek, =ContractConfigFields.StandardCostRate, Contract.StandardChargeRate, =Skill_1.Skill AS [Standard Rate Type], Skill.Skill AS [Account Manager =2], Skill_2.Skill AS [Charge Currency], Skill_3.Skill AS [Cost Currency]
FROM ((((((ContractSkill AS ContractSkill_1 INNER JOIN (Contract INNER =JOIN ContractSkill ON Contract.ContractId =3D ContractSkill.ContractId) =ON ContractSkill_1.ContractId =3D Contract.ContractId) INNER JOIN Skill =ON ContractSkill.SkillId =3D Skill.SkillId) INNER JOIN ContractSkill AS =ContractSkill_2 ON Contract.ContractId =3D ContractSkill_2.ContractId) =INNER JOIN Skill AS Skill_1 ON ContractSkill_2.SkillId =3D =Skill_1.SkillId) INNER JOIN (Skill AS Skill_2 INNER JOIN ContractSkill =AS ContractSkill_4 ON Skill_2.SkillId =3D ContractSkill_4.SkillId) ON =Contract.ContractId =3D ContractSkill_4.ContractId) INNER JOIN =(ContractSkill AS ContractSkill_5 INNER JOIN Skill AS Skill_3 ON =ContractSkill_5.SkillId =3D Skill_3.SkillId) ON Contract.ContractId =3D =ContractSkill_5.ContractId) INNER JOIN ContractConfigFields ON =Contract.ContractId =3D ContractConfigFields.EntityId
WHERE (((Skill_1.SkillId)>=3D281 And (Skill_1.SkillId)<=3D284) AND =((Skill.SkillCategoryId)=3D105) AND ((ContractSkill_1.SkillId)>=3D264 =And (ContractSkill_1.SkillId)<=3D266) AND =((Skill_2.SkillCategoryId)=3D42) AND ((Skill_3.SkillCategoryId)=3D104));
It was created by Access and, as I say ran in 30 seconds on my old =dual PII-400.
Sorry, what's the DDL?
--=_NextPart_000_02C4_01C394D0.12D78B10
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
The index analysis doesn't recommend =any indexes - less than 10 data pages, supporting indexes already, etc.
"Paul Welsh"
Well, here's the query:
SELECT Contract.ContractId, =Contract.Reference, Contract.UserId, Contract.ContactId, Contract.CompanyId, =Contract.AddressId, Contract.CandidateId, Contract.StartDate, Contract.EndDate, Contract.DateSigned, Contract.Contract, =ContractConfigFields.DaysPerWeek, Contract.HoursPerWeek, ContractConfigFields.StandardCostRate, Contract.StandardChargeRate, Skill_1.Skill AS [Standard Rate Type], Skill.Skill AS [Account Manager 2], Skill_2.Skill AS [Charge =Currency], Skill_3.Skill AS [Cost Currency]FROM ((((((ContractSkill AS ContractSkill_1 INNER JOIN (Contract INNER JOIN ContractSkill ON Contract.ContractId =3D ContractSkill.ContractId) ON =ContractSkill_1.ContractId =3D Contract.ContractId) INNER JOIN Skill ON ContractSkill.SkillId =3D = Skill.SkillId) INNER JOIN ContractSkill AS ContractSkill_2 ON Contract.ContractId =3D ContractSkill_2.ContractId) INNER JOIN Skill =AS Skill_1 ON ContractSkill_2.SkillId =3D Skill_1.SkillId) INNER JOIN (Skill AS =Skill_2 INNER JOIN ContractSkill AS ContractSkill_4 ON Skill_2.SkillId =3D ContractSkill_4.SkillId) ON Contract.ContractId =3D =ContractSkill_4.ContractId) INNER JOIN (ContractSkill AS ContractSkill_5 INNER JOIN Skill AS =Skill_3 ON ContractSkill_5.SkillId =3D Skill_3.SkillId) ON Contract.ContractId ==3D ContractSkill_5.ContractId) INNER JOIN ContractConfigFields ON Contract.ContractId =3D ContractConfigFields.EntityIdWHERE (((Skill_1.SkillId)>=3D281 And (Skill_1.SkillId)=3D264 And (ContractSkill_1.SkillId)<=3D266) AND =((Skill_2.SkillCategoryId)=3D42) AND ((Skill_3.SkillCategoryId)=3D104));
It was created by =Access and, as I say ran in 30 seconds on my old dual PII-400.
Sorry, what's the = DDL?
--=_NextPart_000_02C4_01C394D0.12D78B10--|||This is a multi-part message in MIME format.
--=_NextPart_000_00D6_01C394E2.B1063680
Content-Type: multipart/alternative;
boundary="--=_NextPart_001_00D7_01C394E2.B1063680"
--=_NextPart_001_00D7_01C394E2.B1063680
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
DDL =3D Data Definition Language, which includes the CREATE TABLE, =CREATE INDEX, etc. The query looks busy - 11 tables. I have found =issues with SQL Server 7.0 with respect tot he placement of ON =predicates of JOIN clauses. I have stripped out all of the parentheses =and lined up the code just to make it legible. I then added an extra =filter on ContractSkill_2.SkillId that may look redundant but helps the =optimizer. I have attached the script but will reproduce it here:
SELECT
Contract.ContractId
, Contract.Reference
, Contract.UserId
, Contract.ContactId
, Contract.CompanyId
, Contract.AddressId
, Contract.CandidateId
, Contract.StartDate
, Contract.EndDate
, Contract.DateSigned
, Contract.Contract
, ContractConfigFields.DaysPerWeek
, Contract.HoursPerWeek
, ContractConfigFields.StandardCostRate
, Contract.StandardChargeRate
, Skill_1.Skill AS [Standard Rate Type]
, Skill.Skill AS [Account Manager 2]
, Skill_2.Skill AS [Charge Currency]
, Skill_3.Skill AS [Cost Currency]
FROM ContractSkill AS ContractSkill_1
INNER JOIN Contract ON ContractSkill_1.ContractId =3D =Contract.ContractId
INNER JOIN ContractSkill ON Contract.ContractId =3D =ContractSkill.ContractId
INNER JOIN Skill ON ContractSkill.SkillId =3D Skill.SkillId
INNER JOIN ContractSkill AS ContractSkill_2 ON Contract.ContractId =3D =ContractSkill_2.ContractId
INNER JOIN Skill AS Skill_1 ON ContractSkill_2.SkillId =3D =Skill_1.SkillId
INNER JOIN ContractSkill AS ContractSkill_4 ON Contract.ContractId =3D =ContractSkill_4.ContractId
INNER JOIN Skill AS Skill_2 ON Skill_2.SkillId =3D =ContractSkill_4.SkillId
INNER JOIN ContractSkill AS ContractSkill_5 ON Contract.ContractId =3D =ContractSkill_5.ContractId
INNER JOIN Skill AS Skill_3 ON ContractSkill_5.SkillId =3D =Skill_3.SkillId
INNER JOIN ContractConfigFields ON Contract.ContractId =3D =ContractConfigFields.EntityId
WHERE Skill_1.SkillId >=3D 281 And Skill_1.SkillId <=3D 284
AND ContractSkill_2.SkillId >=3D 281 and ContractSkill_2.SkillId <=3D =284 -- added extra filter
AND Skill.SkillCategoryId =3D 105
AND ContractSkill_1.SkillId >=3D 264 And ContractSkill_1.SkillId <=3D =266
AND Skill_2.SkillCategoryId =3D 42
AND Skill_3.SkillCategoryId =3D 104
That said, this does look very confusing. Without your DDL - plus the =purpose of each table - it will be difficult to help further.
-- Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Paul Welsh" <reply.to.group.please@.microsoft.com> wrote in message =news:e$PZ4UMlDHA.708@.TK2MSFTNGP10.phx.gbl...
Well, here's the query:
SELECT Contract.ContractId, Contract.Reference, Contract.UserId, =Contract.ContactId, Contract.CompanyId, Contract.AddressId, =Contract.CandidateId, Contract.StartDate, Contract.EndDate, =Contract.DateSigned, Contract.Contract, =ContractConfigFields.DaysPerWeek, Contract.HoursPerWeek, =ContractConfigFields.StandardCostRate, Contract.StandardChargeRate, =Skill_1.Skill AS [Standard Rate Type], Skill.Skill AS [Account Manager =2], Skill_2.Skill AS [Charge Currency], Skill_3.Skill AS [Cost Currency]
FROM ((((((ContractSkill AS ContractSkill_1 INNER JOIN (Contract INNER =JOIN ContractSkill ON Contract.ContractId =3D ContractSkill.ContractId) =ON ContractSkill_1.ContractId =3D Contract.ContractId) INNER JOIN Skill =ON ContractSkill.SkillId =3D Skill.SkillId) INNER JOIN ContractSkill AS =ContractSkill_2 ON Contract.ContractId =3D ContractSkill_2.ContractId) =INNER JOIN Skill AS Skill_1 ON ContractSkill_2.SkillId =3D =Skill_1.SkillId) INNER JOIN (Skill AS Skill_2 INNER JOIN ContractSkill =AS ContractSkill_4 ON Skill_2.SkillId =3D ContractSkill_4.SkillId) ON =Contract.ContractId =3D ContractSkill_4.ContractId) INNER JOIN =(ContractSkill AS ContractSkill_5 INNER JOIN Skill AS Skill_3 ON =ContractSkill_5.SkillId =3D Skill_3.SkillId) ON Contract.ContractId =3D =ContractSkill_5.ContractId) INNER JOIN ContractConfigFields ON =Contract.ContractId =3D ContractConfigFields.EntityId
WHERE (((Skill_1.SkillId)>=3D281 And (Skill_1.SkillId)<=3D284) AND =((Skill.SkillCategoryId)=3D105) AND ((ContractSkill_1.SkillId)>=3D264 =And (ContractSkill_1.SkillId)<=3D266) AND =((Skill_2.SkillCategoryId)=3D42) AND ((Skill_3.SkillCategoryId)=3D104));
It was created by Access and, as I say ran in 30 seconds on my old dual =PII-400.
Sorry, what's the DDL?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:Oa8a7lLlDHA.2312@.TK2MSFTNGP12.phx.gbl...
I'd focus on cleaning up this one query. I wouldn't turn off =parallelism for the whole SQL Server instance. Rather, I would use the =OPTION (MAXDOP 1) for this one query. Also, I'd run the query through =the Index Tuning Wizard to see if there are some suggestions there. =Finally, if you could post the DDL and the query, we may be able to give =it a tweak.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Paul Welsh" <reply.to.group.please@.microsoft.com> wrote in message =news:eRqU8aLlDHA.2528@.TK2MSFTNGP10.phx.gbl...
I need some expert help here!
I posted yesterday that a particular query takes 50 minutes on a new, =much
more powerful, server compared to the old one.
The new one is running Windows 2000 as opposed to NT 4.
Both are running SQL Server 7 standard edition, though the new one has =SP4
installed.
I think of real importance here is that the new one has dual Xeon 2.4 =Ghz
processors with hyperthreading. This makes it appear to the OS and to =SQL
Server as if it has 4 processors. The old server had dual PII 400 Mhz
processors.
I have improved things a lot by going into the SQL Server properties =in
Enterprise Manager and, under Processor, changing the Parallelism to =use 1
processor. Note that because it's hyperthreaded, 4 CPUs are shown.
I've run the Profiler on the query in question and the Execution Plan =event
differs substantially between configurations.
Now I need help interpreting them! Here they are below.
I wonder whether I should spend my time rewriting the query, ie, =breaking it
into two, rather than trying to make SQL Server work quicker.
I notice "nested loops" doesn't appear with the new server.
Anyhow, advice v. much appreciated:
(1) Old server, works a treat, executes in tens of seconds
Execution Tree
--
Nested Loops(Inner Join)
|--Bookmark Lookup(BOOKMARK:([Bmk1001]),
OBJECT:([TriSysV5].[dbo].[Contract]) WITH PREFETCH)
| |--Nested Loops(Inner Join)
| |--Hash Match(Inner Join,
HASH:([Skill].[SkillId])=3D([ContractSkill].[SkillId]))
| | |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill]),
SEEK:([Skill].[SkillCategoryId]=3D105) ORDERED)
| | |--Nested Loops(Inner Join)
| | |--Hash Match(Inner Join,
HASH:([Skill_2].[SkillId])=3D([ContractSkill_4].[SkillId]))
| | | |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill] AS =[Skill_2]),
SEEK:([Skill_2].[SkillCategoryId]=3D42) ORDERED)
| | | |--Merge Join(Inner Join, MANY-TO-MANY
=MERGE:([ContractSkill_4].[ContractId])=3D([ContractSkill_5].[ContractId])=,
=RESIDUAL:([ContractSkill_4].[ContractId]=3D[ContractSkill_1].[ContractId]=))
| | | |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20] =AS
[ContractSkill_4]), ORDERED)
| | | |--Sort(ORDER
BY:([ContractSkill_1].[ContractId] ASC))
| | | |--Hash Match(Inner Join,
HASH:([Skill_1].[SkillId])=3D([ContractSkill_2].[SkillId]))
| | | |--Bookmark
Lookup(BOOKMARK:([Bmk1008]), OBJECT:([TriSysV5].[dbo].[Skill] AS =[Skill_1]))
| | | | |--Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[PK_Skill_1__11] AS [Skill_1]),
SEEK:([Skill_1].[SkillId] BETWEEN 281 AND 284) ORDERED)
| | | |--Hash Match(Inner Join,
=HASH:([ContractSkill_5].[ContractId])=3D([ContractSkill_1].[ContractId]))=
| | | |--Merge Join(Inner =Join,
MANY-TO-MANY
=MERGE:([ContractSkill_2].[ContractId])=3D([ContractSkill_5].[ContractId])=,
=RESIDUAL:([ContractSkill_2].[ContractId]=3D[ContractSkill_5].[ContractId]=))
| | | | |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20] =AS
[ContractSkill_2]), ORDERED)
| | | | |--Sort(ORDER
BY:([ContractSkill_5].[ContractId] ASC))
| | | | |--Nested
Loops(Inner Join)
| | | | =|--Clustered
Index Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill] AS
[Skill_3]), SEEK:([Skill_3].[SkillCategoryId]=3D104) ORDERED)
| | | | =|--Clustered
Index Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[CLusteredIndex] =AS
[ContractSkill_5]), =SEEK:([ContractSkill_5].[SkillId]=3D[Skill_3].[SkillId])
ORDERED)
| | | |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[CLusteredIndex] AS
[ContractSkill_1]), SEEK:([ContractSkill_1].[SkillId] BETWEEN 264 AND =266)
ORDERED)
| | |--Index
=Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20]),
SEEK:([ContractSkill].[ContractId]=3D[ContractSkill_5].[ContractId]) =ORDERED)
| |--Index
Seek(OBJECT:([TriSysV5].[dbo].[Contract].[PK_Contract_1__11]),
SEEK:([Contract].[ContractId]=3D[ContractSkill_5].[ContractId]) =ORDERED)
|--Clustered Index
=Seek(OBJECT:([TriSysV5].[dbo].[ContractConfigFields].[PK_ContractConfigFi=eld
s]), =SEEK:([ContractConfigFields].[EntityId]=3D[ContractSkill_5].[ContractId])=
ORDERED)
(2) New server, default settings, ie, all processors used for =parallelism.
Takes tens of minutes to run and uses up 100% cpu.
Execution Tree
--
Parallelism(Gather Streams)
|--Hash Match(Inner Join,
HASH:([Skill_3].[SkillId])=3D([ContractSkill_5].[SkillId]))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([Skill_3].[SkillId]))
| |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill] AS =[Skill_3]),
SEEK:([Skill_3].[SkillCategoryId]=3D104) ORDERED)
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([ContractSkill_5].[SkillId]))
|--Hash Match(Inner Join,
HASH:([Skill_2].[SkillId])=3D([ContractSkill_4].[SkillId]))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([Skill_2].[SkillId]))
| |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill] AS =[Skill_2]),
SEEK:([Skill_2].[SkillCategoryId]=3D42) ORDERED)
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([ContractSkill_4].[SkillId]))
|--Hash Match(Inner Join,
HASH:([Skill].[SkillId])=3D([ContractSkill].[SkillId]))
|--Parallelism(Repartition Streams, =PARTITION
COLUMNS:([Skill].[SkillId]))
| |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill]),
SEEK:([Skill].[SkillCategoryId]=3D105) ORDERED)
|--Parallelism(Repartition Streams, =PARTITION
COLUMNS:([ContractSkill].[SkillId]))
|--Merge Join(Inner Join, MANY-TO-MANY
=MERGE:([ContractConfigFields].[EntityId])=3D([ContractSkill_1].[ContractI=d]),
=RESIDUAL:([ContractSkill_1].[ContractId]=3D[ContractConfigFields].[Entity=Id]))
|--Merge Join(Inner Join, =MANY-TO-MANY
=MERGE:([ContractConfigFields].[EntityId])=3D([ContractSkill].[ContractId]=),
=RESIDUAL:([ContractSkill].[ContractId]=3D[ContractConfigFields].[EntityId=]))
| |--Merge Join(Inner Join,
MANY-TO-MANY
=MERGE:([ContractConfigFields].[EntityId])=3D([ContractSkill_4].[ContractI=d]),
=RESIDUAL:([ContractSkill_4].[ContractId]=3D[ContractConfigFields].[Entity=Id]))
| | |--Merge Join(Inner =Join,
MANY-TO-MANY
=MERGE:([ContractSkill_5].[ContractId])=3D([ContractConfigFields].[EntityI=d]),
=RESIDUAL:([ContractSkill_5].[ContractId]=3D[ContractConfigFields].[Entity=Id]))
| | |
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([ContractSkill_5].[ContractId]), ORDER
BY:([ContractSkill_5].[ContractId] ASC))
| | | | |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20] =AS
[ContractSkill_5]), ORDERED)
| | | |--Merge =Join(Inner
Join, =MERGE:([ContractConfigFields].[EntityId])=3D([Contract].[ContractId]),
=RESIDUAL:([Contract].[ContractId]=3D[ContractConfigFields].[EntityId]))
| | |
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([ContractConfigFields].[EntityId]), ORDER
BY:([ContractConfigFields].[EntityId] ASC))
| | | | =|--Clustered
Index
=Scan(OBJECT:([TriSysV5].[dbo].[ContractConfigFields].[PK_ContractConfigFi=eld
s]), ORDERED)
| | | |--Sort(ORDER
BY:([Contract].[ContractId] ASC))
| | | |--Hash
Match(Inner Join,
HASH:([ContractSkill_2].[ContractId])=3D([Contract].[ContractId]))
| | |
|--Parallelism(Distribute Streams, PARTITION
COLUMNS:([ContractSkill_2].[ContractId]))
| | | |
|--Nested Loops(Inner Join)
| | | |
|--Bookmark Lookup(BOOKMARK:([Bmk1008]), =OBJECT:([TriSysV5].[dbo].[Skill] AS
[Skill_1]))
| | | |
| |--Index Seek(OBJECT:([TriSysV5].[dbo].[Skill].[PK_Skill_1__11] =AS
[Skill_1]), SEEK:([Skill_1].[SkillId] BETWEEN 281 AND 284) ORDERED)
| | | |
|--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[CLusteredIndex] AS
[ContractSkill_2]), =SEEK:([ContractSkill_2].[SkillId]=3D[Skill_1].[SkillId])
ORDERED)
| | |
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([Contract].[ContractId]))
| | |
|--Clustered Index
Scan(OBJECT:([TriSysV5].[dbo].[Contract].[ContractContactId]))
| | =|--Parallelism(Repartition
Streams, PARTITION COLUMNS:([ContractSkill_4].[ContractId]), ORDER
BY:([ContractSkill_4].[ContractId] ASC))
| | |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20] =AS
[ContractSkill_4]), ORDERED)
| |--Parallelism(Repartition
Streams, PARTITION COLUMNS:([ContractSkill].[ContractId]), ORDER
BY:([ContractSkill].[ContractId] ASC))
| |--Index
=Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20]),
ORDERED)
|--Sort(ORDER
BY:([ContractSkill_1].[ContractId] ASC))
|--Parallelism(Repartition
Streams, PARTITION COLUMNS:([ContractSkill_1].[ContractId]))
|--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[CLusteredIndex] AS
[ContractSkill_1]), SEEK:([ContractSkill_1].[SkillId] BETWEEN 264 AND =266)
ORDERED)
(3) New server, forcing only 1 processor to be used under Parallelism, =works
but still much slower than (1) above.
Execution Tree
--
Hash Match(Inner Join,
HASH:([Skill_3].[SkillId])=3D([ContractSkill_5].[SkillId]))
|--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill] AS =[Skill_3]),
SEEK:([Skill_3].[SkillCategoryId]=3D104) ORDERED)
|--Hash Match(Inner Join,
HASH:([Skill_2].[SkillId])=3D([ContractSkill_4].[SkillId]))
|--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill] AS =[Skill_2]),
SEEK:([Skill_2].[SkillCategoryId]=3D42) ORDERED)
|--Hash Match(Inner Join,
HASH:([Skill].[SkillId])=3D([ContractSkill].[SkillId]))
|--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[ClusteredIndex_Skill]),
SEEK:([Skill].[SkillCategoryId]=3D105) ORDERED)
|--Merge Join(Inner Join, MANY-TO-MANY
=MERGE:([ContractConfigFields].[EntityId])=3D([ContractSkill_1].[ContractI=d]),
=RESIDUAL:([ContractSkill_1].[ContractId]=3D[ContractConfigFields].[Entity=Id]))
|--Merge Join(Inner Join, MANY-TO-MANY
=MERGE:([ContractConfigFields].[EntityId])=3D([ContractSkill].[ContractId]=),
=RESIDUAL:([ContractSkill].[ContractId]=3D[ContractConfigFields].[EntityId=]))
| |--Merge Join(Inner Join, MANY-TO-MANY
=MERGE:([ContractConfigFields].[EntityId])=3D([ContractSkill_4].[ContractI=d]),
=RESIDUAL:([ContractSkill_4].[ContractId]=3D[ContractConfigFields].[Entity=Id]))
| | |--Merge Join(Inner Join, MANY-TO-MANY
=MERGE:([ContractSkill_5].[ContractId])=3D([ContractConfigFields].[EntityI=d]),
=RESIDUAL:([ContractSkill_5].[ContractId]=3D[ContractConfigFields].[Entity=Id]))
| | | |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20] =AS
[ContractSkill_5]), ORDERED)
| | | |--Merge Join(Inner Join,
MERGE:([ContractConfigFields].[EntityId])=3D([Contract].[ContractId]),
=RESIDUAL:([Contract].[ContractId]=3D[ContractConfigFields].[EntityId]))
| | | |--Clustered Index
=Scan(OBJECT:([TriSysV5].[dbo].[ContractConfigFields].[PK_ContractConfigFi=eld
s]), ORDERED)
| | | |--Sort(ORDER
BY:([Contract].[ContractId] ASC))
| | | |--Hash Match(Inner Join,
HASH:([ContractSkill_2].[ContractId])=3D([Contract].[ContractId]))
| | | |--Nested Loops(Inner =Join)
| | | | |--Bookmark
Lookup(BOOKMARK:([Bmk1008]), OBJECT:([TriSysV5].[dbo].[Skill] AS =[Skill_1]))
| | | | | |--Index
Seek(OBJECT:([TriSysV5].[dbo].[Skill].[PK_Skill_1__11] AS [Skill_1]),
SEEK:([Skill_1].[SkillId] BETWEEN 281 AND 284) ORDERED)
| | | | |--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[CLusteredIndex] AS
[ContractSkill_2]), =SEEK:([ContractSkill_2].[SkillId]=3D[Skill_1].[SkillId])
ORDERED)
| | | |--Clustered Index
Scan(OBJECT:([TriSysV5].[dbo].[Contract].[ContractContactId]))
| | |--Index
Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20] =AS
[ContractSkill_4]), ORDERED)
| |--Index
=Scan(OBJECT:([TriSysV5].[dbo].[ContractSkill].[PK_ContractSkill_1__20]),
ORDERED)
|--Sort(ORDER BY:([ContractSkill_1].[ContractId] =ASC))
|--Clustered Index
Seek(OBJECT:([TriSysV5].[dbo].[ContractSkill].[CLusteredIndex] AS
[ContractSkill_1]), SEEK:([ContractSkill_1].[SkillId] BETWEEN 264 AND =266)
ORDERED)
--=_NextPart_001_00D7_01C394E2.B1063680
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
DDL =3D Data Definition Language, =which includes the CREATE TABLE, CREATE INDEX, etc. The query looks busy - 11 tables. I have found issues with SQL Server 7.0 with respect tot =he placement of ON predicates of JOIN clauses. I have stripped out =all of the parentheses and lined up the code just to make it legible. I then =added an extra filter on ContractSkill_2.SkillId that may look redundant but =helps the optimizer. I have attached the script but will reproduce it here:
SELECT Contract.ContractId, Contract.Reference=, Contract.UserId, Contract.ContactId, Contrac=t.CompanyId, Contract.AddressId, Contract.CandidateId, Contract.StartDate, Contract.EndDate, Contract=.DateSigned, Contract.Contract, ContractConfigFields.Da=ysPerWeek, Contract.HoursPerWeek, ContractConfigFields.=StandardCostRate, Contract.StandardChargeRate, Skill_1.=Skill AS [Standard Rate Type], Skill.Skill AS [Account Manager 2], Skill_2.Skill AS [Charge Currency], Skill_3.Skill =AS [Cost Currency]FROM ContractSkill AS ContractSkill_1INNER JOIN Contract ON ContractSkill_1.ContractId =3D Contract.ContractIdINNER JOIN ContractSkill ON Contract.ContractId =3D ContractSkill.ContractIdINNER =JOIN Skill ON ContractSkill.SkillId =3D Skill.SkillIdINNER JOIN ContractSkill AS ContractSkill_2 ON Contract.ContractId =3D ContractSkill_2.ContractIdINNER =JOIN Skill AS Skill_1 ON ContractSkill_2.SkillId =3D Skill_1.SkillIdINNER JOIN ContractSkill AS ContractSkill_4 ON Contract.ContractId =3D ContractSkill_4.ContractIdINNER =JOIN Skill AS Skill_2 ON Skill_2.SkillId =3D ContractSkill_4.SkillIdINNER JOIN ContractSkill AS =ContractSkill_5 ON Contract.ContractId =3D ContractSkill_5.ContractIdINNER =JOIN Skill AS Skill_3 ON ContractSkill_5.SkillId =3D Skill_3.SkillIdINNER JOIN ContractConfigFields ON Contract.ContractId =3D =ContractConfigFields.EntityIdWHERE Skill_1.SkillId >=3D 281 And Skill_1.SkillId <=3D 284 AND =ContractSkill_2.SkillId >=3D 281 and ContractSkill_2.SkillId <=3D =284 -- added extra filter AND Skill.SkillCategoryId =3D =105 AND ContractSkill_1.SkillId >=3D 264 And ContractSkill_1.SkillId <=3D 266 AND Skill_2.SkillCategoryId =3D 42 AND Skill_3.SkillCategoryId =3D 104
That said, this does look very =confusing. Without your DDL - plus the purpose of each table - it will be difficult =to help further.
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Paul Welsh"
Well, here's the query:
SELECT Contract.ContractId, =Contract.Reference, Contract.UserId, Contract.ContactId, Contract.CompanyId, =Contract.AddressId, Contract.CandidateId, Contract.StartDate, Contract.EndDate, =Contract.DateSigned, Contract.Contract, ContractConfigFields.DaysPerWeek, =Contract.HoursPerWeek, ContractConfigFields.StandardCostRate, Contract.StandardChargeRate, Skill_1.Skill AS [Standard Rate Type], Skill.Skill AS [Account Manager =2], Skill_2.Skill AS [Charge Currency], Skill_3.Skill AS [Cost =Currency]FROM ((((((ContractSkill AS ContractSkill_1 INNER JOIN (Contract INNER JOIN ContractSkill ON Contract.ContractId =3D ContractSkill.ContractId) ON ContractSkill_1.ContractId =3D Contract.ContractId) INNER JOIN Skill ON ContractSkill.SkillId =3D Skill.SkillId) INNER JOIN ContractSkill AS ContractSkill_2 ON Contract.ContractId =3D ContractSkill_2.ContractId) =INNER JOIN Skill AS Skill_1 ON ContractSkill_2.SkillId =3D Skill_1.SkillId) INNER =JOIN (Skill AS Skill_2 INNER JOIN ContractSkill AS ContractSkill_4 ON =Skill_2.SkillId =3D ContractSkill_4.SkillId) ON Contract.ContractId =3D =ContractSkill_4.ContractId) INNER JOIN (ContractSkill AS ContractSkill_5 INNER JOIN Skill AS Skill_3 =ON ContractSkill_5.SkillId =3D Skill_3.SkillId) ON Contract.ContractId =3D ContractSkill_5.ContractId) INNER JOIN ContractConfigFields ON Contract.ContractId =3D ContractConfigFields.EntityIdWHERE (((Skill_1.SkillId)>=3D281 And (Skill_1.SkillId)=3D264 =And (ContractSkill_1.SkillId)<=3D266) AND =((Skill_2.SkillCategoryId)=3D42) AND ((Skill_3.SkillCategoryId)=3D104));
It was created by =Access and, as I say ran in 30 seconds on my old dual PII-400.
Sorry, what's the DDL?
"Tom Moreau"
I'd focus on cleaning up this one =query. I wouldn't turn off parallelism for the whole SQL Server instance. =Rather, I would use the OPTION (MAXDOP 1) for this one query. Also, I'd =run the query through the Index Tuning Wizard to see if there are some =suggestions there. Finally, if you could post the DDL and the query, we may =be able to give it a tweak.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Paul Welsh"
--=_NextPart_001_00D7_01C394E2.B1063680--
--=_NextPart_000_00D6_01C394E2.B1063680
Content-Type: text/plain;
name="BigJoin.sql"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="BigJoin.sql"
SELECT
Contract.ContractId
, Contract.Reference
, Contract.UserId
, Contract.ContactId
, Contract.CompanyId
, Contract.AddressId
, Contract.CandidateId
, Contract.StartDate
, Contract.EndDate
, Contract.DateSigned
, Contract.Contract
, ContractConfigFields.DaysPerWeek
, Contract.HoursPerWeek
, ContractConfigFields.StandardCostRate
, Contract.StandardChargeRate
, Skill_1.Skill AS [Standard Rate Type]
, Skill.Skill AS [Account Manager 2]
, Skill_2.Skill AS [Charge Currency]
, Skill_3.Skill AS [Cost Currency]
FROM ContractSkill AS ContractSkill_1
INNER JOIN Contract ON ContractSkill_1.ContractId =3D =Contract.ContractId
INNER JOIN ContractSkill ON Contract.ContractId =3D =ContractSkill.ContractId
INNER JOIN Skill ON ContractSkill.SkillId =3D Skill.SkillId
INNER JOIN ContractSkill AS ContractSkill_2 ON Contract.ContractId =3D =ContractSkill_2.ContractId
INNER JOIN Skill AS Skill_1 ON ContractSkill_2.SkillId =3D =Skill_1.SkillId
INNER JOIN ContractSkill AS ContractSkill_4 ON Contract.ContractId =3D =ContractSkill_4.ContractId
INNER JOIN Skill AS Skill_2 ON Skill_2.SkillId =3D =ContractSkill_4.SkillId
INNER JOIN ContractSkill AS ContractSkill_5 ON Contract.ContractId =3D =ContractSkill_5.ContractId
INNER JOIN Skill AS Skill_3 ON ContractSkill_5.SkillId =3D =Skill_3.SkillId
INNER JOIN ContractConfigFields ON Contract.ContractId =3D =ContractConfigFields.EntityId
WHERE Skill_1.SkillId >=3D 281 And Skill_1.SkillId <=3D 284
AND ContractSkill_2.SkillId >=3D 281 and ContractSkill_2.SkillId <=3D =284
AND Skill.SkillCategoryId =3D 105
AND ContractSkill_1.SkillId >=3D 264 And ContractSkill_1.SkillId <=3D =266
AND Skill_2.SkillCategoryId =3D 42
AND Skill_3.SkillCategoryId =3D 104
--=_NextPart_000_00D6_01C394E2.B1063680--|||Well it looks to me like:
It's just four tables -- Contract, ContractConfigFields,
ContractSkill, and Skill.
Apparently they've violated atomicity and thus 1nf by putting
different stuff into the skill.skill field, and the same deal on
contractskill.skill. They then want to find records where the skill
and contractskill match on four parameters as dictated by the where
clause.
Should probably have more tables to be normalized to 3nf, or, well,
maybe it's clever the way it is, I'm not sure.
Let's just hope it's not for a recruiter placing database developers.
As to why it should be slower on the new processor, or if there's a
better way to write the query given the current data model, I dunno.
Maybe simple joins on the four tables and four correlated subqueries?
J.
On Fri, 17 Oct 2003 19:13:04 -0400, "Tom Moreau"
<tom@.dont.spam.me.cips.ca> wrote:
>DDL = Data Definition Language, which includes the CREATE TABLE, CREATE INDEX, etc. The query looks busy - 11 tables. I have found issues with SQL Server 7.0 with respect tot he placement of ON predicates of JOIN clauses. I have stripped out all of the parentheses and lined up the code just to make it legible. I then added an extra filter on ContractSkill_2.SkillId that may look redundant but helps the optimizer. I have attached the script but will reproduce it here:
>
>SELECT
> Contract.ContractId
>, Contract.Reference
>, Contract.UserId
>, Contract.ContactId
>, Contract.CompanyId
>, Contract.AddressId
>, Contract.CandidateId
>, Contract.StartDate
>, Contract.EndDate
>, Contract.DateSigned
>, Contract.Contract
>, ContractConfigFields.DaysPerWeek
>, Contract.HoursPerWeek
>, ContractConfigFields.StandardCostRate
>, Contract.StandardChargeRate
>, Skill_1.Skill AS [Standard Rate Type]
>, Skill.Skill AS [Account Manager 2]
>, Skill_2.Skill AS [Charge Currency]
>, Skill_3.Skill AS [Cost Currency]
>FROM ContractSkill AS ContractSkill_1
>INNER JOIN Contract ON ContractSkill_1.ContractId = Contract.ContractId
>INNER JOIN ContractSkill ON Contract.ContractId = ContractSkill.ContractId
>INNER JOIN Skill ON ContractSkill.SkillId = Skill.SkillId
>INNER JOIN ContractSkill AS ContractSkill_2 ON Contract.ContractId = ContractSkill_2.ContractId
>INNER JOIN Skill AS Skill_1 ON ContractSkill_2.SkillId = Skill_1.SkillId
>INNER JOIN ContractSkill AS ContractSkill_4 ON Contract.ContractId = ContractSkill_4.ContractId
>INNER JOIN Skill AS Skill_2 ON Skill_2.SkillId = ContractSkill_4.SkillId
>INNER JOIN ContractSkill AS ContractSkill_5 ON Contract.ContractId = ContractSkill_5.ContractId
>INNER JOIN Skill AS Skill_3 ON ContractSkill_5.SkillId = Skill_3.SkillId
>INNER JOIN ContractConfigFields ON Contract.ContractId = ContractConfigFields.EntityId
>WHERE Skill_1.SkillId >= 281 And Skill_1.SkillId <= 284
> AND ContractSkill_2.SkillId >= 281 and ContractSkill_2.SkillId <= 284 -- added extra filter
> AND Skill.SkillCategoryId = 105
> AND ContractSkill_1.SkillId >= 264 And ContractSkill_1.SkillId <= 266
> AND Skill_2.SkillCategoryId = 42
> AND Skill_3.SkillCategoryId = 104
>That said, this does look very confusing. Without your DDL - plus the purpose of each table - it will be difficult to help further.|||"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:u713pv03ki4l6t7lqq98d6nukhf3mt57he@.4ax.com...
> Well it looks to me like:
> It's just four tables -- Contract, ContractConfigFields,
> ContractSkill, and Skill.
Yup, that's right.
> Apparently they've violated atomicity and thus 1nf by putting
> different stuff into the skill.skill field, and the same deal on
> contractskill.skill. They then want to find records where the skill
> and contractskill match on four parameters as dictated by the where
> clause.
> Should probably have more tables to be normalized to 3nf, or, well,
> maybe it's clever the way it is, I'm not sure.
>
Yes, it's an "interesting" design and not the way I would have done it, but
then again, I didn't write it. It's a recruitment system database. The
skills table is a general purpose dumping ground for all the lookups used in
the system. I think they designed it like this in order to allow
administrators of the application to add their own fields using a separate
and bespoke form designer application.
> Let's just hope it's not for a recruiter placing database developers.
>
Interesting you should speculate on that; I do work for an IT recruitment
agency and this is a recruitment application!
This particular query is pulling off details of the current contracts we
have out in order to do a billings forecast.
> As to why it should be slower on the new processor, or if there's a
> better way to write the query given the current data model, I dunno.
> Maybe simple joins on the four tables and four correlated subqueries?
>
See my response to Tom Moreau, but the performance thing was solved by
forcing sql server 7 to use only one processor for parallel execution of
queries.
Thanks very much for repling. Much appreciated.|||This is a multi-part message in MIME format.
--=_NextPart_000_0054_01C39730.560C58E0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks so much for your efforts, Tom. I really appreciate the fact that =you helped me out on this.
Would you believe it but on Friday afternoon the query in question =suddenly started to perform really well!
I tried it again this morning and it is still running very quickly. =Comparing the old and new servers, on the old setup it took 83 seconds. =On the new one it was taking 50 minutes (!!) but since Friday is down to =18 seconds!
I am using one processor for parallel queries and I have set this up as =a global option. You see the query I posted isn't quite what I'm =running. I'm running a create table query in Access and then using the =table I create within Access. I'm not sure I can use the OPTION (MAXDOP =1) for that one query from within Access. Also, I'm only working here =temporarily so would rather have the sql server run consistently for all =queries. The SQL Server is running an application with a dozen users on =it so having my queries run in one processor and therefore "in the =background" so to speak, doesn't strike me as a bad thing and 18 seconds =for the most complex query is superb!
I have no idea how come this query suddenly started behaving itself, but =I'm very glad it did! Ran it through the profiler today and the =execution plan is now the same as it used to be on the old server.
I tried using all available processors on the new server for parallel =queries but that caused the query to grind to a halt once more so I =reverted back to the single processor.
All very odd, but very good news!
Anyhow, thanks again for your help, Tom.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OtiZXRQlDHA.988@.TK2MSFTNGP10.phx.gbl...
DDL =3D Data Definition Language, which includes the CREATE TABLE, =CREATE INDEX, etc. The query looks busy - 11 tables. I have found =issues with SQL Server 7.0 with respect tot he placement of ON =predicates of JOIN clauses. I have stripped out all of the parentheses =and lined up the code just to make it legible. I then added an extra =filter on ContractSkill_2.SkillId that may look redundant but helps the =optimizer. I have attached the script but will reproduce it here:
That said, this does look very confusing. Without your DDL - plus the =purpose of each table - it will be difficult to help further.
-- Tom
--=_NextPart_000_0054_01C39730.560C58E0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Thanks so much for your efforts, =Tom. I really appreciate the fact that you helped me out on this.
Would you believe it but on Friday =afternoon the query in question suddenly started to perform really well! =
I tried it again this morning and it is =still running very quickly. Comparing the old and new servers, on the =old setup it took 83 seconds. On the new one it was taking 50 minutes (!!) =but since Friday is down to 18 seconds!
I am using one processor for parallel =queries and I have set this up as a global option. You see the query I posted =isn't quite what I'm running. I'm running a create table query in Access =and then using the table I create within Access. I'm not =sure I can use the OPTION (MAXDOP 1) for that one query from within Access. =Also, I'm only working here temporarily so would rather have the sql server run consistently for all queries. The SQL Server is running an =application with a dozen users on it so having my queries run in one processor and =therefore "in the background" so to speak, doesn't strike me as a bad thing =and 18 seconds for the most complex query is superb!
I have no idea how come this query =suddenly started behaving itself, but I'm very glad it did! Ran it through the =profiler today and the execution plan is now the same as it used to be on the old =server.
I tried using all available processors =on the new server for parallel queries but that caused the query to grind to a halt =once more so I reverted back to the single processor.
All very odd, but very good =news!
Anyhow, thanks again for your help, Tom.
"Tom Moreau"
DDL =3D Data Definition Language, =which includes the CREATE TABLE, CREATE INDEX, etc. The query looks busy =- 11 tables. I have found issues with SQL Server 7.0 with respect tot =he placement of ON predicates of JOIN clauses. I have stripped out =all of the parentheses and lined up the code just to make it legible. I =then added an extra filter on ContractSkill_2.SkillId that may look =redundant but helps the optimizer. I have attached the script but will =reproduce it here:
That said, this does look very =confusing. Without your DDL - plus the purpose of each table - it will be =difficult to help further.
-- Tom
--=_NextPart_000_0054_01C39730.560C58E0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0377_01C39714.08369200
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Well, I am pleased that it is working. I'd check STATS_DATE() on all of =the indexes on the involved tables. It could be that statistics were =auto-updated on Friday. From Access, you can invoke stored procs on SQL =Server. You query should be in the form of a stored proc for best =performance.
Take heed on my remarks about parentheses and the ON clauses. Also, =consider moving up to SQL Server 2000. I was impressed by the =performance enhancements - we got a 30% increase in average query speed =in our app.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Paul Welsh" <reply.to.group.please@.microsoft.com> wrote in message =news:e4OR0cylDHA.2444@.TK2MSFTNGP09.phx.gbl...
Thanks so much for your efforts, Tom. I really appreciate the fact that =you helped me out on this.
Would you believe it but on Friday afternoon the query in question =suddenly started to perform really well!
I tried it again this morning and it is still running very quickly. =Comparing the old and new servers, on the old setup it took 83 seconds. =On the new one it was taking 50 minutes (!!) but since Friday is down to =18 seconds!
I am using one processor for parallel queries and I have set this up as =a global option. You see the query I posted isn't quite what I'm =running. I'm running a create table query in Access and then using the =table I create within Access. I'm not sure I can use the OPTION (MAXDOP =1) for that one query from within Access. Also, I'm only working here =temporarily so would rather have the sql server run consistently for all =queries. The SQL Server is running an application with a dozen users on =it so having my queries run in one processor and therefore "in the =background" so to speak, doesn't strike me as a bad thing and 18 seconds =for the most complex query is superb!
I have no idea how come this query suddenly started behaving itself, but =I'm very glad it did! Ran it through the profiler today and the =execution plan is now the same as it used to be on the old server.
I tried using all available processors on the new server for parallel =queries but that caused the query to grind to a halt once more so I =reverted back to the single processor.
All very odd, but very good news!
Anyhow, thanks again for your help, Tom.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OtiZXRQlDHA.988@.TK2MSFTNGP10.phx.gbl...
DDL =3D Data Definition Language, which includes the CREATE TABLE, =CREATE INDEX, etc. The query looks busy - 11 tables. I have found =issues with SQL Server 7.0 with respect tot he placement of ON =predicates of JOIN clauses. I have stripped out all of the parentheses =and lined up the code just to make it legible. I then added an extra =filter on ContractSkill_2.SkillId that may look redundant but helps the =optimizer. I have attached the script but will reproduce it here:
That said, this does look very confusing. Without your DDL - plus the =purpose of each table - it will be difficult to help further.
-- Tom
--=_NextPart_000_0377_01C39714.08369200
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Well, I am pleased that it is =working. I'd check STATS_DATE() on all of the indexes on the involved tables. =It could be that statistics were auto-updated on Friday. From Access, you =can invoke stored procs on SQL Server. You query should be in the form =of a stored proc for best performance.
Take heed on my remarks about =parentheses and the ON clauses. Also, consider moving up to SQL Server 2000. I =was impressed by the performance enhancements - we got a 30% increase in =average query speed in our app.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Paul Welsh"
Thanks so much for your efforts, =Tom. I really appreciate the fact that you helped me out on this.
Would you believe it but on Friday =afternoon the query in question suddenly started to perform really well! =
I tried it again this morning and it is =still running very quickly. Comparing the old and new servers, on the =old setup it took 83 seconds. On the new one it was taking 50 minutes (!!) =but since Friday is down to 18 seconds!
I am using one processor for parallel =queries and I have set this up as a global option. You see the query I posted =isn't quite what I'm running. I'm running a create table query in Access =and then using the table I create within Access. I'm not =sure I can use the OPTION (MAXDOP 1) for that one query from within Access. =Also, I'm only working here temporarily so would rather have the sql server run consistently for all queries. The SQL Server is running an =application with a dozen users on it so having my queries run in one processor and =therefore "in the background" so to speak, doesn't strike me as a bad thing =and 18 seconds for the most complex query is superb!
I have no idea how come this query =suddenly started behaving itself, but I'm very glad it did! Ran it through the =profiler today and the execution plan is now the same as it used to be on the old =server.
I tried using all available processors =on the new server for parallel queries but that caused the query to grind to a halt =once more so I reverted back to the single processor.
All very odd, but very good =news!
Anyhow, thanks again for your help, Tom.
"Tom Moreau"
DDL =3D Data Definition Language, =which includes the CREATE TABLE, CREATE INDEX, etc. The query looks busy =- 11 tables. I have found issues with SQL Server 7.0 with respect tot =he placement of ON predicates of JOIN clauses. I have stripped out =all of the parentheses and lined up the code just to make it legible. I =then added an extra filter on ContractSkill_2.SkillId that may look =redundant but helps the optimizer. I have attached the script but will =reproduce it here:
That said, this does look very =confusing. Without your DDL - plus the purpose of each table - it will be =difficult to help further.
-- Tom
--=_NextPart_000_0377_01C39714.08369200--|||On Mon, 20 Oct 2003 17:08:14 +0100, "Paul Welsh"
<reply.to.group.please@.microsoft.com> wrote:
>Yes, it's an "interesting" design and not the way I would have done it, but
>then again, I didn't write it. It's a recruitment system database. The
>skills table is a general purpose dumping ground for all the lookups used in
>the system. I think they designed it like this in order to allow
>administrators of the application to add their own fields using a separate
>and bespoke form designer application.
Yes, a design as often rediscovered as perpetual motion, ...
>> Let's just hope it's not for a recruiter placing database developers.
>Interesting you should speculate on that; I do work for an IT recruitment
>agency and this is a recruitment application!
Shoemaker's children ...
>See my response to Tom Moreau, but the performance thing was solved by
>forcing sql server 7 to use only one processor for parallel execution of
>queries.
I'd seen queries run from the Query Analyzer in SQL7 that would never
terminate because of some multiprocessor foolishness, but never one
that ran 100x slower for that reason, ... but I can believe it could
happen.
Glad you've got it running well now.
J.|||This is a multi-part message in MIME format.
--=_NextPart_000_003F_01C3973B.27320500
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
One more thing - you may want to run DBCC FREEPROCCACHE when/if you see =the ugly behaviour recur. I have seen some bad plans get into the proc =cache and this is how you can get rid of them.
-- Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eFTcoWzlDHA.372@.TK2MSFTNGP11.phx.gbl...
Well, I am pleased that it is working. I'd check STATS_DATE() on all of =the indexes on the involved tables. It could be that statistics were =auto-updated on Friday. From Access, you can invoke stored procs on SQL =Server. You query should be in the form of a stored proc for best =performance.
Take heed on my remarks about parentheses and the ON clauses. Also, =consider moving up to SQL Server 2000. I was impressed by the =performance enhancements - we got a 30% increase in average query speed =in our app.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Paul Welsh" <reply.to.group.please@.microsoft.com> wrote in message =news:e4OR0cylDHA.2444@.TK2MSFTNGP09.phx.gbl...
Thanks so much for your efforts, Tom. I really appreciate the fact that =you helped me out on this.
Would you believe it but on Friday afternoon the query in question =suddenly started to perform really well!
I tried it again this morning and it is still running very quickly. =Comparing the old and new servers, on the old setup it took 83 seconds. =On the new one it was taking 50 minutes (!!) but since Friday is down to =18 seconds!
I am using one processor for parallel queries and I have set this up as =a global option. You see the query I posted isn't quite what I'm =running. I'm running a create table query in Access and then using the =table I create within Access. I'm not sure I can use the OPTION (MAXDOP =1) for that one query from within Access. Also, I'm only working here =temporarily so would rather have the sql server run consistently for all =queries. The SQL Server is running an application with a dozen users on =it so having my queries run in one processor and therefore "in the =background" so to speak, doesn't strike me as a bad thing and 18 seconds =for the most complex query is superb!
I have no idea how come this query suddenly started behaving itself, but =I'm very glad it did! Ran it through the profiler today and the =execution plan is now the same as it used to be on the old server.
I tried using all available processors on the new server for parallel =queries but that caused the query to grind to a halt once more so I =reverted back to the single processor.
All very odd, but very good news!
Anyhow, thanks again for your help, Tom.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OtiZXRQlDHA.988@.TK2MSFTNGP10.phx.gbl...
DDL =3D Data Definition Language, which includes the CREATE TABLE, =CREATE INDEX, etc. The query looks busy - 11 tables. I have found =issues with SQL Server 7.0 with respect tot he placement of ON =predicates of JOIN clauses. I have stripped out all of the parentheses =and lined up the code just to make it legible. I then added an extra =filter on ContractSkill_2.SkillId that may look redundant but helps the =optimizer. I have attached the script but will reproduce it here:
That said, this does look very confusing. Without your DDL - plus the =purpose of each table - it will be difficult to help further.
-- Tom
--=_NextPart_000_003F_01C3973B.27320500
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
One more thing - you may want to run =DBCC FREEPROCCACHE when/if you see the ugly behaviour recur. I have =seen some bad plans get into the proc cache and this is how you can get rid of them.
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Tom Moreau"
Well, I am pleased that it is =working. I'd check STATS_DATE() on all of the indexes on the involved tables. =It could be that statistics were auto-updated on Friday. From Access, you =can invoke stored procs on SQL Server. You query should be in the form =of a stored proc for best performance.
Take heed on my remarks about =parentheses and the ON clauses. Also, consider moving up to SQL Server 2000. I =was impressed by the performance enhancements - we got a 30% increase in =average query speed in our app.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Paul Welsh"
Thanks so much for your efforts, =Tom. I really appreciate the fact that you helped me out on this.
Would you believe it but on Friday =afternoon the query in question suddenly started to perform really well! =
I tried it again this morning and it is =still running very quickly. Comparing the old and new servers, on the =old setup it took 83 seconds. On the new one it was taking 50 minutes (!!) =but since Friday is down to 18 seconds!
I am using one processor for parallel =queries and I have set this up as a global option. You see the query I posted =isn't quite what I'm running. I'm running a create table query in Access =and then using the table I create within Access. I'm not =sure I can use the OPTION (MAXDOP 1) for that one query from within Access. =Also, I'm only working here temporarily so would rather have the sql server run consistently for all queries. The SQL Server is running an =application with a dozen users on it so having my queries run in one processor and =therefore "in the background" so to speak, doesn't strike me as a bad thing =and 18 seconds for the most complex query is superb!
I have no idea how come this query =suddenly started behaving itself, but I'm very glad it did! Ran it through the =profiler today and the execution plan is now the same as it used to be on the old =server.
I tried using all available processors =on the new server for parallel queries but that caused the query to grind to a halt =once more so I reverted back to the single processor.
All very odd, but very good =news!
Anyhow, thanks again for your help, Tom.
"Tom Moreau"
DDL =3D Data Definition Language, =which includes the CREATE TABLE, CREATE INDEX, etc. The query looks busy =- 11 tables. I have found issues with SQL Server 7.0 with respect tot =he placement of ON predicates of JOIN clauses. I have stripped out =all of the parentheses and lined up the code just to make it legible. I =then added an extra filter on ContractSkill_2.SkillId that may look =redundant but helps the optimizer. I have attached the script but will =reproduce it here:
That said, this does look very =confusing. Without your DDL - plus the purpose of each table - it will be =difficult to help further.
-- Tom
--=_NextPart_000_003F_01C3973B.27320500--
No comments:
Post a Comment