none
Finding Cause of Performance Issue RRS feed

  • Question

  • Hi All,

    Running SQL Server 2008 R2 SP3 on two different servers, with (at a glance) identical configurations.

    Both servers are virtual and have the same amount of resources assigned to them.  The data drives are on the same SAN and use the same performance tier.  Server A has a production database of 40+ GB, while Server B has a production database of 5+ GB.

    Both databases have the same schema and indexing, and the activity levels on Server A are higher than Server B.  When running queries against Server B they are noticeably slower than executing the same queries against Server A.  I can confirm this isn't just activity-based because even in off-hours the issue still presents itself.

    Is there anything in terms of configuration at either the server or database level I should be checking?  If so, what?

    Thanks!
    Friday, January 24, 2020 12:18 AM

All replies

  • Hi - First make sure there is no congestion on temp DB.    
    Friday, January 24, 2020 12:49 AM
  • How would I do this?  I'm not sure what you mean by congestion.
    Friday, January 24, 2020 12:52 AM
  • How about memory and CPU utilization on both servers? Are those high?
    Friday, January 24, 2020 1:26 AM
  • Both servers have 8GB RAM, when we check CPU utilization it is very low.
    Friday, January 24, 2020 1:51 AM
  • If you are certain that both VMs have the same priorities and that is not the problem … then here are a couple of ideas.

    First, I presume that the data is rather different between the two, and it could be different enough that Server B needs different indexing - or at least statistics updating.

    Second, if Server B is very low activity it might not even have grabbed all available RAM, what do your disk statistics look like?  Have you checked for blocking?  Differences in the data could produce differences in behaviors.

    Bonus: you mention data drives, are the logs on the same drives, any difference there?

    Basically, just treat Server B as a performance issue independently, and diagnose it.

    Josh

    Friday, January 24, 2020 3:20 AM
  • An obvious start is to run the the same query on both and compare executions plans. Are they identical? And are actually execution times for that particular query slower on server B?

    If plans are the same and it is slower on B, run against, now with SET STATISTICS IO enabled. Are there any physical reads? If there are not, the disks does not matter (unless the plan has sort/hash spills).


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, January 24, 2020 10:26 PM
  • How would I do this?  I'm not sure what you mean by congestion.

    You need to set tempdb files with equal size. Files must grow equally. Make sure Index's are properly placed and not much fragmented. secondly check wait types on your server B. execute queries and check which disk I/O. 

    Saturday, January 25, 2020 12:51 PM
  • Hi 2012S4,

    Please check below links to see if they could help you.

    Troubleshoot Slow-Running Queries In SQL Server
    SQL SERVER – 5 Important Steps When Query Runs Slow Occasionally
    A DBA guide to SQL Server performance troubleshooting – Part 1 – Problems and performance metrics

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, January 27, 2020 8:24 AM
  • Hi,

    I think your RAM has been overclocked to wrong speed.

    May be that's the hidden reason for this issue. You can simply set your RAM to a specific speed. And then check if it works or not

    Thursday, January 30, 2020 5:08 PM