Home Forums SQL Server 2014 Administration - SQL Server 2014 Performance improves after reboot RE: Performance improves after reboot

  • February 10, 2017 at 1:13 pm

    #1927881

    jonathan.ellison - Friday, February 10, 2017 5:21 AM

    Here are some basic things I would check.  TempDB files - both data and logs.  When you reboot a server these get reset.  Do you have enought space for these files?  How many tempdb data files are you using.  If you are using only 1 tempdb data file, you may need to increase that number.  You can also try running DBCC FREEPROCCACHE, DBCC DROPCLEANBUFFERS, DBCC FREESYSTEMCACHE ('ALL') and DBCC FREESESSIONCACHE. 

    Hope these suggestions help.  Bottom line, don't give up.  You can and will figure out what is causing your issue and you become the better DBA for what you learn.

    Jon

    Jon,
    I am not DBA. I am an ETL Developer (consultant) who was asked to help.
    DBA were not able so far to fix performance issue.
    In my opinion they never really tried.

    I don't understand why it's called "Initial Size (MB)".
    It should be "Current File Size". Period.
    In 2014 it's not being reset to anything other than the size it was before reboot. I did a test on my local SQL Server.
    I manually grew tempdb.mdf from 8 to 50 MB. Rebooted and it's 50 MB.

    When our SQL Server was first installed in April 2016, our DBA created 8 tempdb files (we have 16 processors).
    Each was 2GB. Autogrowth = 200MB
    Today the files are:

    tempdb.mdf ---------------- 30.2GB
    tempdb_db2.mdf -------- 2GB
    tempdb_db3.mdf -------- 32.6GB
    tempdb_db4.mdf -------- 32.3GB
    tempdb_db5.mdf -------- 29.8GB
    tempdb_db6.mdf -------- 32.6GB
    tempdb_db7.mdf -------- 32.7GB
    tempdb_db8.mdf -------- 32.7GB

    During day we have from 600 to 2,000 objects in tempdb.
    Can I try to shrink tempdb and then watch when and how much file sizes are changing?
    Although probably not a good idea. If it needs to grow everything will be much slower. Huge performance imact.

    We had a major slowdown on January 5/6.
    Instead of 1.5 hours ETL took 9 and 7 hours.
    I wonder, what if on that day tempdb Autogrowth by 200MB was kicked off?