SQL Server Performance

[SQL2K] Query abnormal performance problem

Discussion in 'General DBA Questions' started by greg00, Mar 21, 2006.

  1. greg00 New Member

    Hi,

    PROBLEM :

    it's a query performance problem.
    There is several inner join in my query (on 5 table and 1 view and 1 user function)
    return 23000 lines.
    -> Select with 30 columns (60 with select *)

    CONFIG :

    Tests have been made on 2 pc with same hardware :
    -> PIV Dual Core D920
    -> Same harddrive
    -> SQL Serveur 2K SP4 version : 8.00.2039
    -> Same database

    WHAT:

    -> On pc without problem :
    execution time 1850ms
    -> On pc with problem :
    Select "columns_list" : 17854ms
    Select * : 3753ms
    Select "columns_list" without join on view and user funtion : 8000ms
    Select * without join on view and user funtion : 3800ms

    More test on third pc running query on the 2 other:
    3rd pc on pc without problem : 8500ms
    3rd pc on pc with problem : 8500ms
    so problem seems disapear when run remotely.

    TRY TO RESOLVE :

    Check database (restore same on 2 pc)
    Reinstall SQL Server et SP4


    If you have any idea, she is welcome.


    Thanks in advance.

  2. Adriaan New Member

    quote:There is several inner join in my query (on 5 table and 1 view and 1 user function)
    The UDF is the #1 candidate for troubleshooting.

    If the UDF is part of the ON clause of the JOIN, then you need to understand that you're forcing SQL Server to NOT use any indexes for that expression, and it will have to do the JOIN on all rows from the underlying table/view.

    What does the UDF do?

    Other issues would be if the UDF returns a table ... does it take column(s) of other tables as parameter(s)? ... are you filtering on the results from the UDF?
  3. Twan New Member

    Hi ya,

    same memory config?
    same applications installed?
    same version of MDAC?
    same network cards/settings, no errors on the cards or switch ports?
    same execution plan if run with QA on each box?
    what does Profiler say if you run it while executing these statements?
    what does perfmon say if you run it while executing these statements?


    Cheers
    Twan
  4. greg00 New Member

    quote:Originally posted by Adriaan


    quote:There is several inner join in my query (on 5 table and 1 view and 1 user function)
    The UDF is the #1 candidate for troubleshooting.

    If the UDF is part of the ON clause of the JOIN, then you need to understand that you're forcing SQL Server to NOT use any indexes for that expression, and it will have to do the JOIN on all rows from the underlying table/view.

    What does the UDF do?

    Other issues would be if the UDF returns a table ... does it take column(s) of other tables as parameter(s)? ... are you filtering on the results from the UDF?

    Yes 1 UDF is part of ON Clause of the JOIN. She takes only one fixed parameter (@language) and return just 200 lines there is not filtering on result just link value.

    if i replace UDF call directly by a sub query the problem is the same.

    Anyway, the query is the same on 2 pc's and 10x slower on one.
    And it's amazing that when i ask select * (60col) it's faster than selec col1,col2 (30col).

    I have made more test on 3rd pc with slower cpu simgle core and with same memory
    and it's take 4334ms
  5. greg00 New Member

    quote:Originally posted by Twan

    Hi ya,

    same memory config?
    same applications installed?
    same version of MDAC?
    same network cards/settings, no errors on the cards or switch ports?
    same execution plan if run with QA on each box?
    what does Profiler say if you run it while executing these statements?
    what does perfmon say if you run it while executing these statements?


    Cheers
    Twan

    I have made more test on 3rd pc with slower cpu simgle core and with same memory
    and it's take 4334ms

    --> memory config is the same

    --> application install looks same

    --> same version of mdac (2.81.1117.6)

    --> network : i think it's good because when i execute remotly from 3rd part there is no problem, exectuion time is the same on 2 test pc.

    --> execution plan exactly the same

    --> when i compare profile it's look very near

    But i don't understand why when i set stastics time on and i look result

    the cpu time is the same on 2 pc's
    850ms and total time is 3400ms for the good one and 17443ms for the bad one.








  6. greg00 New Member


    MORE TEST :


    even if i execute my query on old server PIII 1Ghz with poor sdram memory and old drive
    it take 6442ms instead of 17454ms on my bug pc and 3000ms on the good one.


    I don't think it's hardware probleme, because when i do
    a simple query like "select * from mytable" where mytable have
    24000 lines it's very fast on 2 test pc's.


    I really don't understand.
  7. Twan New Member

    if cpu is the same on both by duration isn't and both queries are run locally on the machine, then it has to be a problem with io. I'm assuming that the amount of data being searched through is more than the amount of RAM in each PC?

    COuld you run perfmon while running the query to get things like
    memory pages/sec
    processor utilisation
    process: SQLserver.exe processor utilisation
    SQL Server buffer stuff
    SQL Server memory stuff
    page life expectancy


    Cheers
    Twan
  8. greg00 New Member

    quote:Originally posted by Twan

    if cpu is the same on both by duration isn't and both queries are run locally on the machine, then it has to be a problem with io. I'm assuming that the amount of data being searched through is more than the amount of RAM in each PC?

    COuld you run perfmon while running the query to get things like
    memory pages/sec
    processor utilisation
    process: SQLserver.exe processor utilisation
    SQL Server buffer stuff
    SQL Server memory stuff
    page life expectancy


    Cheers
    Twan

    i run perfmon on 2 pc's

    SQL Server : Buffer Manager : read pages /s

    WITH : Select "column_list":
    on pc without problem : max value : 450
    on pc with problem : max value : 42

    WITH : Select *
    on pc without problem : max value : 450
    on pc with problem : maw value : 450

    So, it seems to be a buffer read problem when i
    use a select with column list, but why?
  9. Adriaan New Member

    quote:Originally posted by greg00


    quote:Originally posted by Adriaan


    quote:There is several inner join in my query (on 5 table and 1 view and 1 user function)
    The UDF is the #1 candidate for troubleshooting.

    If the UDF is part of the ON clause of the JOIN, then you need to understand that you're forcing SQL Server to NOT use any indexes for that expression, and it will have to do the JOIN on all rows from the underlying table/view.

    What does the UDF do?

    Other issues would be if the UDF returns a table ... does it take column(s) of other tables as parameter(s)? ... are you filtering on the results from the UDF?

    Yes 1 UDF is part of ON Clause of the JOIN. She takes only one fixed parameter (@language) and return just 200 lines there is not filtering on result just link value.

    if i replace UDF call directly by a sub query the problem is the same.

    Anyway, the query is the same on 2 pc's and 10x slower on one.
    And it's amazing that when i ask select * (60col) it's faster than selec col1,col2 (30col).

    I have made more test on 3rd pc with slower cpu simgle core and with same memory
    and it's take 4334ms

    I know you;re looking at other issues, but ...

    Does the table that you're returning from the UDF have a primary key defined on the JOIN column?
  10. greg00 New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Does the table that you're returning from the UDF have a primary key defined on the JOIN column?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Yes, there is a primary key.<br /><br />MORE TEST : <br />the problem is mainly in Quary Analyzer and sometimes in my ASP.NET code.<br />And when i put result directly in a file with Query analyzer, the execution time is good.<br /><br />Maybe i must change my screen [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] ?

Share This Page