August 6, 2003 at 7:07 am
Hi Folks,
I have a query that runs in under 1 sec on a single processor machine (our dev environment), yet when I run the same query on a cluster with 2 multiprocessor machines (and tonnes more ram) it takes 17 secs. I have compared the execution plans, and they are very different.
How do I go about troubleshooting this?
Thanks,
R.
August 6, 2003 at 7:28 am
It sounds like your query runs in parallel. You should see whether it runs in parallel from the execution plan. You could also set "max degree of parallelism" to 1 to disable parallelism execution to see any improvement.
August 6, 2003 at 10:39 am
Thanks Allen ... what exactly do you mean when you say that the query runs in parallel? How do I figure that out by looking at the execution plan?
Thanks,
R.
August 6, 2003 at 11:27 am
Quoted from BOL.
"Parallel Query Processing
Microsoft® SQL Server™ 2000 provides parallel queries to optimize query execution for computers having more than one microprocessor. By allowing SQL Server to perform a query in parallel by using several operating system threads, SQL Server completes complex queries with large amounts of data quickly and efficiently.
During query optimization, SQL Server looks for queries that might benefit from parallel execution. For these queries, SQL Server inserts exchange operators into the query execution plan to prepare the query for parallel execution. An exchange operator is an operator in a query execution plan that provides process management, data redistribution, and flow control. After exchange operators are inserted, the result is a parallel query execution plan. A parallel query execution plan can use more than one thread, whereas a serial execution plan, used by a nonparallel query, uses only a single thread for its execution. The actual number of threads used by a parallel query is determined at query plan execution initialization and is called the degree of parallelism."
You may post your execution plan here.
August 6, 2003 at 12:03 pm
Thanks again Allen. It does not appear to be an issue with parallelism. Here are the plans for each:
SQL QUery:
SELECT distinct r.*
FROM vw_RefVersionOwner Orig
INNER JOIN vw_RefVersionowner vo ON vo.ref_id = Orig.versionowner
INNER JOIN tlnreferences r ON r.ref_id = vo.devid
WHERE languages <> 'E'and ((start_date <= Getdate() and end_Date is null and site_id = 5)
or (getdate() between start_Date and end_Date and site_id = 5)) AND hot_topic_date is not null
ORDER BY r.Ref_ID
On the single processor:
|--Sort(ORDER BY:([TLNReferences].[devid] ASC))
|--Nested Loops(Inner Join, OUTER REFERENCES:([TLNReferences].[devid]) WITH PREFETCH)
|--Hash Match(Aggregate, HASH:([TLNReferences].[devid]), RESIDUAL:([TLNReferences].[devid]=[TLNReferences].[devid]))
| |--Hash Match(Inner Join, HASH:([Expr1015])=([TLNReferences].[Ref_ID]), RESIDUAL:([TLNReferences].[Ref_ID]=[Expr1015]))
| |--Compute Scalar(DEFINE:([Expr1015]=If ([TLNReferences].[VersionOwner]=0) then [TLNReferences].[Ref_ID] else [TLNReferences].[VersionOwner]))
| | |--Clustered Index Scan(OBJECT:([leadership].[leadership].[TLNReferences].[PK_TLNReferences]))
| |--Clustered Index Scan(OBJECT:([leadership].[leadership].[TLNReferences].[PK_TLNReferences]))
|--Clustered Index Seek(OBJECT:([leadership].[leadership].[TLNReferences].[PK_TLNReferences] AS [r]), SEEK:([r].[Ref_ID]=[TLNReferences].[devid]), WHERE:((([r].[site_id]=5 AND [r].[hot_topic_date]<>NULL) AND [r].[Languages]<>'E') AND ((Convert([r].[Start_Date])<=getdate() AND [r].[End_date]=NULL) OR (getdate()>=Convert([r].[Start_Date]) AND getdate()<=Convert([r].[End_date])))) ORDERED FORWARD)
On the multi processor:
|--Nested Loops(Left Semi Join, OUTER REFERENCES:([r].[Ref_ID]))
|--Clustered Index Scan(OBJECT:([Leadership].[leadership].[TLNReferences].[PK_TLNReferences] AS [r]), WHERE:((([r].[site_id]=5 AND [r].[hot_topic_date]<>NULL) AND [r].[Languages]<>'E') AND ((Convert([r].[Start_Date])<=getdate() AND [r].[End_date]=NULL) OR (getdate()>=Convert([r].[Start_Date]) AND getdate()<=Convert([r].[End_date])))) ORDERED FORWARD)
|--Nested Loops(Inner Join, OUTER REFERENCES:([TLNReferences].[VersionOwner], [TLNReferences].[Ref_ID]) WITH PREFETCH)
|--Clustered Index Scan(OBJECT:([Leadership].[leadership].[TLNReferences].[PK_TLNReferences]))
|--Clustered Index Seek(OBJECT:([Leadership].[leadership].[TLNReferences].[PK_TLNReferences]), SEEK:([TLNReferences].[Ref_ID]=If ([TLNReferences].[VersionOwner]=0) then [TLNReferences].[Ref_ID] else [TLNReferences].[VersionOwner]), WHERE:([r].[Ref_ID]=[TLNReferences].[devid]) ORDERED FORWARD)
Edited by - robby_disco on 08/06/2003 12:06:02 PM
August 7, 2003 at 1:10 am
Have you taken a look at your indices? Are they consistent between your development and live environments? Another thing is to be sure that statistics are up-to-date. Bad stats can cause the optimizer to choose a very bad query plan.
August 7, 2003 at 3:54 am
quote:
Have you taken a look at your indices? Are they consistent between your development and live environments? Another thing is to be sure that statistics are up-to-date. Bad stats can cause the optimizer to choose a very bad query plan.
Nikki Pratt
Development DBA
August 7, 2003 at 7:03 am
Do you have the same amount of data on your test and prod machines?
The query plan shows a lot of table scans of the table TLNReferences (a clusterd index scan means table scan)
Anyway if you have a lot of records in your database then THIS quer CANNOT run fast
How many rows do you have in that table? (on both machines).
Because the execution plan will be different on a DB where a number of records are small compared to a table where you have a lot of records.
You have to define an index on versionowner, eventually on ref_id.
Also I do not see the necessity of your view vw_RefVersionOwner in this query because it seams to access only your table TLNReferences
Gabor
Bye
Gabor
August 7, 2003 at 7:33 am
Thanks for the input Paul and Gabor.
To answer some of your questions:
1) There are about 12000 rows each in tlnreferences. I restore a back-up of production to the dev environ everyday. Am I correct in assuming that the indices and stats are the same on both machines? If so ... why do I get 2 different query plans?
2) It may help if you understand what I need the view for. The view is as follows:
Create View vw_RefVersionOwner
AS
SELECT Ref_ID, Case VersionOwner
When 0 then ref_id
else versionowner
end as versionowner, prodid,devid
FROM Tlnreferences
Unfortunately I have inherited a very poor table design. The tlnreferneces table lists a number of recursively versioned references. Essentialy Ref_ID, VersionOwner, prodid, devid are all ref_id. The first version is the versionowner and has a value of 0 (it should have been the same as the ref_id so I could key off it but anyways), all subsequent versions have a versionowner = ref_id of the first version. Prodid and Devid are stored in the version owner's row. Prodid is the reference that appears in production, devid is the upcoming version that appears in development (not to be confused with our prod and dev database environs ... poorly named keys). This view functions as a lookup table so that I can recursively figure out what the most recent prod or dev id is. Make sense?
In this query I am:
SELECT distinct r.*
-- get list of all refs
FROM vw_RefVersionOwner Orig
-- filter on versionowners
INNER JOIN vw_RefVersionowner vo ON vo.ref_id = Orig.versionowner
--return only dev refs for the version owners
INNER JOIN tlnreferences r ON r.ref_id = vo.devid
I seem to remember reading somewhere that a view will not use a clustered index? Is that why I am getting such poor performance when I join on the view?
Edited by - robby_disco on 08/07/2003 07:34:13 AM
August 7, 2003 at 11:45 am
Have you run a profiler to check for missing column statistics? I have seen query times jump up when column statistics go missing.
Tom
August 7, 2003 at 12:46 pm
quote:
Have you run a profiler to check for missing column statistics? I have seen query times jump up when column statistics go missing.Tom
What do I look for in the profiler?
August 7, 2003 at 1:14 pm
In profiler, under events and warning, missing column statistics.
Tom
August 12, 2003 at 1:34 pm
quote:
In profiler, under events and warning, missing column statistics.Tom
doesn't appear to have any missing statistics
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply