Re: Inconsistent performance on a query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Steve Kass (skass_at_drew.edu)
Date: 07/21/04 

Date: Wed, 21 Jul 2004 14:38:09 -0400

Are you saying that if you leave out "where se.file_key" is null, it
runs much slower? If so, it sounds like it might be a bad query plan.

If the new server is multi-processor and the old one is not, you could
try it with OPTION (MAXDOP 1),
to see if a parallel plan is causing trouble.

If the number of rows involved is small, a workaround (until you figure
out something better) is

select ...
INTO #QuickResults
from ...
-- your query /without/ the IS NULL condition

select *
from #QuickResults
where file_key is null

If none of this helps, can you post the table definitions and the four
query plans (from SET SHOWPLAN_TEXT ON):
Plan for Server A, with IS NULL which runs fine
Plan for Server B, with IS NULL, the problem query
Plan for Server A, without IS NULL, which runs fine
Plan for Server B, without IS NULL, which runs fine

Steve Kass
Drew University

spoons wrote:

>The problem is with the 'is null' clause. The new server is more powerful than the old server and is dedicated to SQL Server.
>
>"Kenny" wrote:
>
>
>
>>If you have a lot of applications or schedule task running on that machine,
>>it could be your cpu or memory. check if your cpu or memory usage is high.
>>if you have multiple cpus, you can disable cpu 0 and use cpu 1, 2 and
>>other. READ about affinity mask advance option in book online.
>>
>>
>>
>>
>>"Bri Gipson" <nospam@aol.com> wrote in message
>>news:sp9tf0hc9v5r6rfbvf1naiuo7r2oj08vmt@4ax.com...
>>
>>
>>>I'm not sure how much this pertains to your issue, but where I work some
>>>
>>>
>>machines are faster than others... more CPU power, more memory, faster hard
>>drives. Particularly with differing CPUs and hard drive speeds you'll find a
>>great amount of difference on
>>
>>
>>>running a query on identical installs (database and SQL server settings).
>>>
>>>
>>Also check to see that your slower machine isn't running CPU intensive apps
>>or services.
>>
>>
>>>Cheers,
>>>Bri
>>>
>>>--
>>>
>>>On Wed, 21 Jul 2004 10:23:01 -0700, spoons
>>>
>>>
>><spoons@discussions.microsoft.com> wrote:
>>
>>
>>>>I have a select statement that runs in two seconds on one server and does
>>>>
>>>>
>>not complete running after twenty minutes on another server. I copied the
>>database from the first server and restored to a new server. The two
>>servers have the same SQL 2000 sp3, and I ran DBCC USEROPTIONS and received
>>the same result set from the two servers. The connections properties are
>>the same. The problem comes into play when the where clause at the end of
>>the query with the 'is null' clause is included. I would appreciate any
>>help in resolving this problem. Does this have to do with configuration
>>settings? Please view the query:
>>
>>
>>>>select sm.client_key, sm.location_key, sm.program_key, sm.file_key
>>>> , sm.form_type, sm.background_type, ris.index_type_code
>>>> , ris.index_code, ris.results_record_type, ris.dispatched_index_date
>>>> , ris.research_date,se.file_key as IsUpdate,rit.name
>>>> , rit.catagory_name, rit.jurisdiction_code,
>>>>
>>>>
>>ris.jurisdiction_state_code
>>
>>
>>>> , ris.jurisdiction_federal_code, ris.jurisdiction_county_code,
>>>>
>>>>
>>ris.method_code
>>
>>
>>>> , se.Date_Completed,rco.Completion_Indicator
>>>> from n_subject_master sm
>>>> join rome_index_subject ris on sm.file_key=ris.file_key
>>>> join wnrcadmin.rome_index_type rit on
>>>>
>>>>
>>ris.index_type_code=rit.index_type_code
>>
>>
>>>> join wnrcadmin.research_codes_overall rco on
>>>>
>>>>
>>ris.results_record_type=rco.code
>>
>>
>>>> left join summary_element se on ris.file_key=se.file_key
>>>> and ris.index_type_code=se.element_type_key
>>>> and ris.index_code=se.element_key
>>>>where se.file_key is null
>>>>
>>>>
>>
>>
>>

Relevant Pages

  • Re: Strange performance setting Execution Location=3
    ... understanding many aspects of Analysis Server. ... merge time, and query time. ... > a faster CPU, this isn't the only factor at work here. ...
    (microsoft.public.sqlserver.olap)
  • Re: Optimizing this very slow query
    ... executing the query at the server side will use 100% of the server CPU ... Because, by default, the client evaluate the formula, so 100% of the ...
    (microsoft.public.sqlserver.olap)
  • Re: [9fans] 9vx and local file systems
    ... For about seven years I had the luxury of running Plan 9 ... I have a second Plan 9 server with a bigger, ... I've used drawterm to connect to it, edit and compile venti, ... doesn't require a cpu server. ...
    (comp.os.plan9)
  • Re: Strange performance setting Execution Location=3
    ... I think the point to make here is that remoting a query doesn't ... necessarily mean improved performance - although the server might have ... a faster CPU, this isn't the only factor at work here. ... > twice as long even though the server has a much faster cpu. ...
    (microsoft.public.sqlserver.olap)
  • Re: Denial of Service: BINARY_CHECKSUM of TEXT column hangs CPU: SQL 2K SP4
    ... hangs hold of the CPU until the server is restarted. ... I'm assuming that you verified that the query hangs the CPU on your SQL 2K ... > Jasper Smith (SQL Server MVP) ...
    (microsoft.public.sqlserver.security)