Post reply

My query runs very slowly whith special where clause!

  • September 6, 2011 at 12:10 am

    #256621

    Hi

    I have a Query that is mixed of sql query and MDX query . i run it everyday ,but it takes long time some day . when i run just the MDX or SQL Query seperate ,i get result quickly. it takes 29 minutes for the result set with 5000 rows for specific day but i get the result in 10 seconds for the other day !

    I checked the sysproccesses and found that Physical IO value is so huge like 11099829. also Lastwaitetype value was PAGEIOLATCH_SH .

    I checked performance monitoring too and got sql server used all it's own memory .then i grew the SQL Server memory size .

    also The disk read time is too high in performance monitoring .

    i'm so confused ! help me if you can PLZ 🙁

  • September 6, 2011 at 12:26 am

    #1377178

    Not enough details im afraid,

    try this article to help us to help you....

    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/


    Clear Sky SQL
    My Blog[/url]

  • September 6, 2011 at 4:32 am

    #1377271

    roja.hafezi (9/6/2011)

    Hi

    I have a Query that is mixed of sql query and MDX query . i run it everyday ,but it takes long time some day . when i run just the MDX or SQL Query seperate ,i get result quickly. it takes 29 minutes for the result set with 5000 rows for specific day but i get the result in 10 seconds for the other day !

    I checked the sysproccesses and found that Physical IO value is so huge like 11099829. also Lastwaitetype value was PAGEIOLATCH_SH .

    I checked performance monitoring too and got sql server used all it's own memory .then i grew the SQL Server memory size .

    also The disk read time is too high in performance monitoring .

    i'm so confused ! help me if you can PLZ 🙁

    Check whether there is any blocking...

  • September 6, 2011 at 7:41 am

    #1377407

    From what you have stated, I can only guess that you have high IO bottlenecks. When the query is running slowly, is there any other activity going on on the server? For example, back up, Virus scan etc.

    -Roy

  • September 11, 2011 at 7:26 pm

    #1379767

    What other processes are running on that day along with your query ?

    Run a perfmon counter log along with profiler trace to identify the actual culprit.

    Thank You,

    Best Regards,

    SQLBuddy

  • September 11, 2011 at 9:20 pm

    #1379774

    I agree with Dave... simply not enough information to help. Please read the article at the link that Dave posted.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 11, 2011 at 10:58 pm

    #1379785

    Hi

    Thank you everybody . we used execution plan and found out an index offer then created this index and the problem solved.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply