none
SQL Server instance on VM Slow. How to improve performance?

    Question

  • I am using a VM to set up multiple instances of SQL Server for dev purposes.

    I noticed my SSIS jobs were running much slower and the SSAS was not completing.

    Closer inspection indicated the queries were all running without parrallelism.

    Windows explorer shows me 6 CPUs and there is 64GB of memory assigned, and its using a fraction of it.

    I tried setting MDOP to 4 but it made no difference to the query plans.

    Can anybody advise on how I can fix this.

    Thanks

    Regards

    Jon

    Thursday, April 13, 2017 12:04 PM

Answers

  • Any index fragmentation - please run below query, if you found any rows then i would suggest you to run re-build indexes those tables

    ---- Query to check fragmentations

    SELECT ltrim(rtrim(db_Name())) 
    ,ltrim(rtrim(object_name(a.object_id)))
    ,Ltrim(rtrim(b.name))
    ,left(a.avg_fragmentation_in_percent,5)
    FROM sys.dm_db_index_physical_stats (db_ID(), null,
    NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b 
    ON a.object_id = b.object_id 
    AND a.index_id = b.index_id
    where a.database_id = db_id()
    and a.avg_fragmentation_in_percent > 35
    and b.name is not null
    order by a.object_id

    ---- re-build indexes please check this url

    https://www.mssqltips.com/sqlservertip/1367/sql-server-script-to-rebuild-all-indexes-for-all-tables-and-all-databases/

    Hope it helps

     

    http://uk.linkedin.com/in/ramjaddu

    • Marked as answer by JLJ1976 Thursday, April 13, 2017 4:49 PM
    Thursday, April 13, 2017 12:15 PM

All replies

  • Any index fragmentation - please run below query, if you found any rows then i would suggest you to run re-build indexes those tables

    ---- Query to check fragmentations

    SELECT ltrim(rtrim(db_Name())) 
    ,ltrim(rtrim(object_name(a.object_id)))
    ,Ltrim(rtrim(b.name))
    ,left(a.avg_fragmentation_in_percent,5)
    FROM sys.dm_db_index_physical_stats (db_ID(), null,
    NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b 
    ON a.object_id = b.object_id 
    AND a.index_id = b.index_id
    where a.database_id = db_id()
    and a.avg_fragmentation_in_percent > 35
    and b.name is not null
    order by a.object_id

    ---- re-build indexes please check this url

    https://www.mssqltips.com/sqlservertip/1367/sql-server-script-to-rebuild-all-indexes-for-all-tables-and-all-databases/

    Hope it helps

     

    http://uk.linkedin.com/in/ramjaddu

    • Marked as answer by JLJ1976 Thursday, April 13, 2017 4:49 PM
    Thursday, April 13, 2017 12:15 PM
  • Hello,


    Update table statistics with full scan.


    Make sure the VM is not configured with dynamic memory management. This may be related with poor performance.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com


    Thursday, April 13, 2017 1:00 PM
    Moderator
  • I rebuilt the indexes and that sort of improved the plan to some extent, ie there was parallelism.

    However the query still takes about 5 mins vs 30 secs on a standalone server.

    The VM also has only a third of the data.

    There should be more then enough CPU and memory to handle this query.

    Its not even recommending any new indices.

    Thursday, April 13, 2017 4:09 PM
  • I rebuilt all the indices that are involved.

    I can see that SQLServer has taken its maximum share of the memory I allocated to it.  

    Would dynamic memory management still affect this?

    Thursday, April 13, 2017 4:12 PM
  • Are you running Developer or Enterprise Edition?

    Not sure if you can run parallel queries on Standard Edition

    • Edited by sbogar Thursday, April 13, 2017 4:28 PM
    Thursday, April 13, 2017 4:28 PM
  • Developer. Actually looking at the memory being used, its not using the 12GB max I set it, instead its stopped at 1.5GB.

    I'm going to trying rebooting the box and see if this behavior goes away

    Thursday, April 13, 2017 4:30 PM
  • Hmm, reboot did not help.

    Even though I set a min of 4gb and max 12GB.

    Its only using 1.5GB. Looking at the plan, I can see a lot of spillover into the tempdb.

    Is this an indication that the memory I think is allocated to my server is not set, and depends on the other VMs?

    Will need to speak to the Network Admins if this is the case.

    Thursday, April 13, 2017 4:39 PM
  • Hmm, reboot did not help.

    Even though I set a min of 4gb and max 12GB.

    Its only using 1.5GB. Looking at the plan, I can see a lot of spillover into the tempdb.

    Is this an indication that the memory I think is allocated to my server is not set, and depends on the other VMs?

    Will need to speak to the Network Admins if this is the case.

    What is output of select @@version

    and

    Select * from sys.dm_os_process_memory.

    So you have SSIS and SSRS running on system how much you have left for it. I mean you have assigned 12 GB for SQL Server and what is total RAM on the VM ?

    Is VM configured correctly to use all resources


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, April 13, 2017 4:47 PM
    Moderator
  • Maybe my understanding of how the memory is used is incorrect.

    My SSAS cube is now completing in a reasonable time.

    Maybe it was the fragmentation after all.

    Thanks for the help guys.

    Thursday, April 13, 2017 4:49 PM
    • Edited by philfactor Thursday, April 13, 2017 5:01 PM
    Thursday, April 13, 2017 5:00 PM
  • Hello,


    Dynamic memory definitely affect the performance of database servers.

    VMware says Microsoft recommend to disable Dynamic Memory on SQL Server depployments. Source: http://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/solutions/sql-server-on-vmware-best-practices-guide.pdf


    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Thursday, April 13, 2017 5:05 PM
    Moderator
  • The version is Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) 
    Oct 20 2015 15:36:27 
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

    The query came back with

    physical_memory_in_use_kb large_page_allocations_kb locked_page_allocations_kb total_virtual_address_space_kb virtual_address_space_reserved_kb virtual_address_space_committed_kb virtual_address_space_available_kb page_fault_count memory_utilization_percentage available_commit_limit_kb process_physical_memory_low process_virtual_memory_low
    2580516 0 0 8589934464 40492576 2811048 8549441888 1114747 100 121541980 0 0

    It says memory utilization percentage 100% yet its only at 2.58GB on the task manager despite this instance being set up to max out at 9GB.

    I did not configure the VM, just was allocated a VM that I could use.

    The VM states it has 64GB memory, but the SQL Server instances are not taking up their max allocation.

    Tuesday, April 18, 2017 10:12 AM
  • It seems like VM pulling out memory from host. For time being you must give Locked pages in memory priv to SQL Server service account and you would see memory rising. But be careful this is not always what is recommended you must meet your VMware admin

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, April 18, 2017 6:17 PM
    Moderator