• SteveOC - Tuesday, January 24, 2017 3:40 PM

    Thanks Sue.

    I mentioned that Tempdb was a single file as I flagged this some time ago when I started looking at  this SQL server, but to be honest having done that I have not been able to demonstrate any contention for Tempdb and other changes have taken priority.

    Oddly that CheckDB is running now and not using much if any of TempDB it seems.
    PLE drops to nearly nothing as usual so assume it is doing everything in memory?

    SteveO. l

    Checkdb (and other maintenance types of tasks) almost always cause drops in PLE just due to those procedures reading a lot of pages into the buffer pool and then they get dumped back out to accommodate for the next batch. So a page doesn't stay in the buffer pool that long while those are going on for most databases.

    You very well could have a system where tempdb just doesn't get hit too hard or too often. I've had some of those but I still create at least 4 files for tempdb. If nothing else, it kind of forces us to have to get a better handle on the storage available, in use, etc and get a bigger picture view of that side.
    And someone will eventually do something like a union with the largest tables on the server. And want it ordered. Or import a bunch of large files into a temp table with several LOB columns. And then add an index on the largest columns of the table.
    But enjoy it while it's quiet. For now. 🙂

    Sue