none
IBM x3650 M5 VS DELL Latitude E5540 performance

    Question

  • Hi

    I have performance problem with my server. IBM x3650 have a two Virtual Machines. One of them is SQL server with 40GB RAM (30GB set for SQL). IBM x3650 have RAID 10. On SQL server i have database (21GB) and tempdb file split to 8 files (server have a 8 Cores). The problem is when i make a simple SELECT query with 33000 rows, SQL server message from statistics is

     SQL Server Execution Times:
       CPU time = 8500 ms,  elapsed time = 9375 ms.

    When i make a same query (sam database )but on my laptop DELL Latitude E5540 (4 cores , 8 gb RAM) statistics looks like

     SQL Server Execution Times:
       CPU time = 65 ms,  elapsed time = 101 ms.

    Why? Why server is slover than laptop? (today when nobody works on server (only my) statistics looks same (even if i do restart)
    Saturday, January 28, 2017 2:43 PM

Answers

  • Hi,

    Are the execution plans on the laptop and server the same or not ?

    Did you actually compared them or just assuming what they should be like ?

    Don't assume anything, retrieve both and compare them.

    • Marked as answer by jakub.magiera Wednesday, February 1, 2017 7:24 AM
    Sunday, January 29, 2017 3:16 PM

All replies

  • As you are using so much more cpu time on the server than on our laptop I suspect that the execution plans are different - even with the same query and database - or there is a workload currently executing on your sever which is interfering on your server.

    you say that there is nobody on the server, but perhaps there is a job running which is causing you to see this.

    Can you post the two execution plans here?

    Are they both using the same indexes?

    Saturday, January 28, 2017 4:10 PM
  • Also check the hypervisor settings. If the hypervisor is restricting the VMs access to CPU time (possibly because of other VMs running), that can look like high CPU usage on the VM. 

    David


    Microsoft Technology Center - Dallas

    My Blog

    Saturday, January 28, 2017 5:39 PM
  • Another thing to consider. Is your Latitude on an SSD? If so you are giving it a much bigger performance boost than your spinning drives, even if they are on RAID 10.

    Is SQL on your laptop on a VM as well? If it is on the host it has another advantage. The IBM system has the host and the two VMs it is managing. What is the time like when the other VM is not running?


    Joie Andrew "Since 1982"

    Saturday, January 28, 2017 6:41 PM
  • Hi,

    1. What's the current load on the server ? One thing is running your query alone on your laptop, another very different is on a server with 2500 users hitting the database (some of them might be locking your table or putting too much

    pressure on the I/O)

    2. Have you compared the execution plan on your server against your laptop ?

    3. Are statistics and indexes refreshed on the server ?

    Sunday, January 29, 2017 1:09 AM
  • Put Virtual Machine on laptop with SQL server. And second virtual machine with whatever other virtual machine on server has running which I don't see you mentioning what other virtual machine is for. Then perform queries on laptop and see results.

    La vida loca

    Sunday, January 29, 2017 1:37 AM
  • Hi I'll try to answer all of your questions.

    1. Laptop is with SATA drive not SSD

    2. Execution plan should be same because query is only " SELECT * FROM articles" so it only read rows (33000 rows)

    3. I did a rebuild and reorganize

    4. When i started query, CPU utylization was 14%

    5. now there is nobody logged in (only me ) and statistics looks same

    6. When second VM is off, there is no differences

    7. laptop is without VM

    Main problem is not CPU use but elapsed time for same uqery on laptop and server.

    Sunday, January 29, 2017 8:36 AM
  • SATA is a backplane/connection type. It has nothing to do with if the drive is a spinning platter or SSD drive. What do you get when you run the following on your IBM and Dell systems?

    get-wmiobject -class win32_diskdrive


    Joie Andrew "Since 1982"

    Sunday, January 29, 2017 8:47 AM
  • Ok. I know, sata is a type of conection. But you have right. I was check and DELL have Seagate ST500LM000 Solid State Hybrid Drive, 500 GB  , 5400 obr/min with 8GB FLASH memory. So maybe this is reason why laptops is faster. What you think if I will use a 120GB SSD drive as a buffor for SQL?
    Sunday, January 29, 2017 11:54 AM
  • If your memory is adequate enough the buffer pool for SQL should already be using memory, which will be faster than your SSD anyway. What would probably be best is if you want faster read performance put your data files on the SSD. If you want faster write performance, put your log files on the SSD.

    Another thing to check is look at your SQL Server Error Log. Make sure there are no messages that memory is being paged out. If so that could be a huge performance impact as well. If that is the case ensure that SQL has the "Lock pages in memory" right on the OS.


    Joie Andrew "Since 1982"

    Sunday, January 29, 2017 12:10 PM
  • There is no any errors/messages about paget out. I'm only put user that SQL service use to "Lock Pages In memory" but it nothing changed. Query sql few times with select and answer is still aobut 9 seconds.
    I can understand everything but not that big differences between Laptop and much more faster server.

    Sunday, January 29, 2017 2:15 PM
  • >Main problem is not CPU use but elapsed time

    The elapsed time is caused by the CPU use.

    If everything you have said in this thread is accurate, we are running out of ideas.  So time to double check everything.

    Please post the table DDL and execution plans and row counts for both databases.

    David


    Microsoft Technology Center - Dallas

    My Blog

    Sunday, January 29, 2017 2:34 PM
  • Hi,

    Are the execution plans on the laptop and server the same or not ?

    Did you actually compared them or just assuming what they should be like ?

    Don't assume anything, retrieve both and compare them.

    • Marked as answer by jakub.magiera Wednesday, February 1, 2017 7:24 AM
    Sunday, January 29, 2017 3:16 PM
  • Hi,

    Please, check power options on both laptop and server (Control Panel)

    Putting the server in "power saving" mode may slow CPU in quite a noticeable way.



    Sunday, January 29, 2017 3:18 PM
  • laptop and server have a set High Performance
    Monday, January 30, 2017 10:08 AM
  • Actually there was a small differences in execution plan and it was a reason why server i slower in SELECT then laptop. One of column was updated few days ago and this column does not exist on laptop. In execution plan it looks like "compute scalar". When i was deleted this column, server query is 65ms. Thank you all for help.
    Monday, January 30, 2017 2:31 PM
  • Hi jakub,

    Glad to hear that you have identified the issue, please mark the corresponding reply as answer so it would benefit others when they are reading this thread.

    If you have any other questions, please let me know.

    Regards,
    Lin

    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.

    Tuesday, January 31, 2017 5:46 AM
    Moderator