Post reply

SQL Performance

  • March 11, 2009 at 2:07 am

    #202138

    hi there ...

    i have a procedure that need about 30millisecond to execute ... when executed alone

    but when we have concurrent user ... around 800 user try to execute it concurrently it needs about 3second !!!

    how can i make it take 30second ... regardless the number of concurrent user !!! can i ?

    many thanks

  • March 11, 2009 at 2:48 am

    #957315

    It depends! What does the procedure do? Does it select or modify data?

    Gianluca

    -- Gianluca Sartori

  • March 11, 2009 at 2:58 am

    #957318

    Select ,Modify and also some of arithmatic operation ...

    but every user has his own data, so lock on table will be only for his data and will not affect other users

    i think that what i should do is related to SQL configuration ... Right 🙂

  • March 11, 2009 at 3:01 am

    #957320

    If you're sure every user works only on his data, try putting some NOLOCK in the select satements to avoid table and page locking during reads.

    With this little information this is all I can suggest.

    -- Gianluca Sartori

  • March 11, 2009 at 3:12 am

    #957324

    If you add the code we might be able to help you 🙂

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • March 11, 2009 at 3:16 am

    #957328

    thanks alot Gianluca Sartori ...

    and about the code, its very long and related so much to our system.

    but im saying that its not related to code ...

    its related to number of concurrent user ... since server I\O ,CPU and Memory goes high

    and the response for the new request become slow ...

    all what i need is to know if there is a way to isolate these factor and run every query as its run alone 🙂 can i ?

  • March 11, 2009 at 10:43 am

    #957670

    Yeap you can still post the query here as an attachment though.

    If the performance is being affected by system and you think you can't gain any thing from query optimization then you might need to look at hardware bottlenecks.

    Example:

    Process Queue Length

    Avg. Disk Read/Write Queue Length

    Page Life Exp.

    Page Hit ratio..

    etc..

    Mohit.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • March 11, 2009 at 11:28 am

    #957733

    Gianluca Sartori (3/11/2009)

    If you're sure every user works only on his data, try putting some NOLOCK in the select satements to avoid table and page locking during reads.

    Provided you don't mind a small chance of inaccurate data. I don't mean from half-done transactions. NOLOCK allows for a possibility of missing rows or reading rows twice.

    mhasan: Can you post the query, the table defintions and the indexes? Good code and good indexes allow for very short transactions and minimal blocking.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 11, 2009 at 11:38 am

    #957745

    GilaMonster (3/11/2009)

    NOLOCK allows for a possibility of missing rows or reading rows twice.

    Gail, what do you mean "reading rows twice"? I know I could miss some rows with NOLOCK, but I know nothing about duplicated reads. I use NOLOCK in some stored procedures reading static data to avoid locking in multi-user environment and I would be interested on this topic.

    -- Gianluca Sartori

  • March 11, 2009 at 12:19 pm

    #957788

    Nolock allows for what's called an allocation order scan, where the storage engine reads the index leaf pages in the order they are in the file, not the logical order specified by the index key. The latter's called an index-order scan and it's the only scan allowed in the other isolation levels 1

    With an allocation order scan, it is possible for a page split to occur during the scan moving half a page of rows from behind the scan's current position to in front of the scan's current possition, resulting in the rows being read twice.

    Similarly it's possible for a page ahead of the scan's current position to be split and half of the rows placed in a page behind the scan's current possition, resulting in them being completely missed.

    This is not a bug. Nolock essentially means 'I want the data without delay, I don't mind if it's slightly inaccurate'

    You may say this won't happen often, but in truth it depends on the table schema, the index keys and the type of activity. I've seen a demo where there was one query window just doing single row inserts into a table and a second doing a read with nolock. It took less than 10 seconds for one of the reads to return duplicate values for a column that was INT IDENTITY UNIQUE

    If your data is static, there's no chance of getting this. But then, if your data is static and hence won't be locked, why do you need nolock at all?

    (1) Allocation order scans are also allowed by a scan that has a table lock, because when the entire table is locked, there's no chance of data pages moving.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 12, 2009 at 2:26 am

    #958121

    Thank you very much Gail, I didn't know about page splits!

    I use sometimes NOLOCK when my data is partially static and I need to read only the static part, avoiding locks brought by the updates on the nonstatic part. Do page splits occour in this scenario as well?

    My users never complained about duplicated rows, but this is no guarantee.

    Thanks again

    Gianluca

    -- Gianluca Sartori

  • March 12, 2009 at 2:34 am

    #958124

    Depends. If the static and non-static can be on the same page, yes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 12, 2009 at 2:40 am

    #958126

    Thanks Gail,

    I have a new top ranked item for my to-do list today...

    -- Gianluca Sartori

  • March 13, 2009 at 8:22 am

    #959136

    Hi Gail

    Quick question about the splits.

    If the index is on the identify field and you use 100% fill factor (with identity, you can go only one way so 100% should not hurt ?) - would you have the same problem?

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • March 14, 2009 at 2:22 pm

    #959789

    if that index is using for read (I mean select statement) then by defining the filfactor to 100% would be he best in terms of performance as far as I know....but if the index undergoes lot of updates then you need to specify the fillfactor.. I would say set fillfactor to 80 should be ok..

    please refer this link:-

    http://www.sqlservercentral.com/articles/Performance+Tuning/whocaresaboutfillfactor/1196/

Viewing 15 posts - 1 through 15 (of 19 total)

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