Some performance improvements tips required at server level

  • sayedkhalid99

    Ten Centuries

    Points: 1394

    My db is currently around 650mb but is is increasing with around 500 concurrent users doing transactions and reporting at the same server. Please let me know whether the following changes can improve the performance or not.

    1. Moving mdf,ldf,tempdb to seperate drives.

    2. Create 2 server one for transactions and one for reporting ,replicating data to second server through always on or replication mechanism.

    3- changing the current raid 5 to raid 10 in first server or both.

    What other changes i need to bring to improve performance , becouse some of the query is performing very slowin the server . can i expect performance gain by doing the above tasks.

  • Alan Burstein

    SSC Guru

    Points: 61019

    [Quote]1. Moving mdf,ldf,tempdb to seperate drives.[/quote]

    Yes, you want to do that anyhow but yes, do that.

    2. Create 2 server one for transactions and one for reporting ,replicating data to second server through always on or replication mechanism.

    Yes, this will help tremendously. As a general rule it's best to separate your OLTP and reporting. Plus, with a reporting server you can index the heck out of it and do a lot of other things that you don't want to do on your OLTP system.

    -- Alan Burstein


    Helpful links:Best practices for getting help on SQLServerCentral -- Jeff ModenHow to Post Performance Problems -- Gail ShawNasty fast set-based string manipulation functions:For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)To split strings based on patterns try PatternSplitCMNeed to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8KI cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code.  -- Itzik Ben-Gan 2001

  • Gail Shaw

    SSC Guru

    Points: 1004424

    sayedkhalid99 (4/17/2016)


    What other changes i need to bring to improve performance ,

    The usual process of identify the slow queries, tune them (indexes and/or queries). Repeat until performance is acceptable.

    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
  • sayedkhalid99

    Ten Centuries

    Points: 1394

    thanks Alan Burstein,Gail Shaw for you feedback , my point was the same query will perform differently when i execute that in local pc and in server , i will move for separating the server due to load of users on server hard drives,processor,memory and heavy report generations crystal report and etc.. and see how it works.

Viewing 4 posts - 1 through 4 (of 4 total)

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