Home Forums SQL Server 2017 SQL Server 2017 - Development SQL Server 2017, 2012 Express Edition Insert Select Performance Issue with very less data. RE: SQL Server 2017, 2012 Express Edition Insert Select Performance Issue with very less data.

  • Sharat Gupta-482974 - Friday, October 12, 2018 3:52 AM

    Real table has to written to disk, is it not required for temp table/table variable to be written to disk? All three have physical storage. Real table in user database and temp table/table variable in tempdb.

    No, it isn't required. SQL Server doesn't necessarily persist temporary objects to disk (and doesn't guarantee not to either). On a box with nothing else running and plenty of free RAM, it's incredibly likely it won't bother as it doesn't gain anything from doing so.

    This is why all your problems point to a disk contention issue. You've not configured the max RAM available to SQL Server so it's probably fighting the OS disk cache for free memory. Your query is probably causing a lot of spillage into tempdb and causing a lot of unnecessary writes to disk already (hence the poor performance for the select alone). Adding the insert is quite possibly causing the machine to thrash, constantly swapping data in and out of RAM and killing the performance of your system overall. 

    All of these are things people could have helped you with if you just shared your view code. It's not going to be worth the vast sums of money you imagine. It really depends on whether you want people to help solve your actual problem or not.