SQL Server Performance

How to Optimize SQL server performance with SAN

Discussion in 'ALL SQL SERVER QUESTIONS' started by sachinonnet, Oct 8, 2012.

  1. sachinonnet New Member


    We are running SQL Server 2008 R2 64Bit , enterprise with SP1, for Storage we are using SAN.

    We are facing performance issue with IO below is the configuration:
    1. Two LUN's created for DB Logs and DB Data
    2. Major 5 tables hold 600million rows and partitioned monthly
    3. Read and Write happening 50-50
    4. Partitioned tables don't have cluster index to improve write performance.
    5. 32 CPU coarse where as utilisation shows 1-5%Max
    6. 'max degree of parallelism' currently set to 4

    below are the some changes we suggest and like to know your inputs to improve performance

    1. Create More LUN's for Logs and Data
    2. 'max degree of parallelism' set to 32
    3. Move partitioned index to seperate LUN
    I started with good article on SAN @ http://www.brentozar.com/sql/sql-server-san-best-practices/

  2. Shehap MVP, MCTS, MCITP SQL Server

    Usually for IO reflow issues , we have 2 main probable assumptions :

    1. Intensive IO overload on your DB server due to some activities either T-SQL Queries (Mostly Probable ) or backup activities

    2. IO subsystem performance issues or CRC (cyclic redundancy check) issues relevant to your storage either local or SAN

    So to compromise Cleary between them, we have to open a troubleshooting case as follows:

    1. Capture all expensive queries taking place by the same time interval of these errors , you can do this using the attached script which will capture continuously all expensive queries in a table called Exp_Detailed under msdb DB

    2. Conduct stress test on these expensive queries happening by the same slot of IO reflow using any tool like SQLQueryTest

    3. Monitor all transactions while this stress test using Activity monitor and keep more insight on page Latch waits and page IO latch waits.. .

    4. In case of failure of stress test and much Page latch waits , then we can come with a solid conclusion we have a problem of T-SQL queries impacting negatively on IO performance

    5. In case of success, we have to check out all possible SQL Server activities undertaken by the same time of IO reflow like Backup and check integrity in particular more

    6. If nothing to do with the above assumptions , then we have to go down to the final assumption that we have really IO subsystem issues that need to be checked with SAN Administrator

    Therefore, the following actions might be helpful for you :

    · Creating more LUNS will be helpful in your case if you could distribute separately temp DB files, Log files , .mdf files , .ndf file and File stream files (If exists) along with these SAN LUNs.

    · Creating the appropriate index (clustered or non clustered) to optimize IO cost consumed by Expensive queries , you can read more at my blog http://www.sqlserver-performance-tuning.com/apps/blog/show/12927042-t-sql-performance-optimization-6-6-

    · Keep CPU Parallelism at 4 or 8 or 16 according to your workload but nit 32 CPU Tat all

    You can share with me your findings to be able to help you addressing that case
  3. David_W New Member

    How much RAM does the server have?

Share This Page