Post reply

DBCC CHECKDB and TEMPDB

  • January 23, 2017 at 8:41 am

    #401463

    Hello.

    I am reading all that I can on the topic of speeding up DBCC CHECKDB and hearing that TEMPDB size is a factor.

    Here is the result of a query against TEMPDB :

    FileName FileSizeinMB (No column name) GrowthValue GrowthIncrement
    tempdev 22278.375000 Autogrowth is on. 10 Growth value is a percentage.
    templog 379.437500 Autogrowth is on. 10 Growth value is a percentage.

    and here is the result of EstimateOnly against the DB (it is around 600GB in size) :

    DBCC CHECKDB with NO_INFOMSGS, ALL_ERRORMSGS, ESTIMATEONLY

    Estimated TEMPDB space (in KB) needed for CHECKDB on database RSSQLDB = 93761605.


    If I am interpreting this correctly, TEMPDB is currently around 20GB and DBCC CHECKDB indicates it would like it to be more like 90GB.

    CHECKDB runs with no errors (I will be monitoring TEMPDB tonight while it runs to see how much gets used).

    What does DBCC CHECKDB do when it has a TEMPDB that is so much smaller than it would like?

    Why doesn't TEMPDB grow (with AUTOGROWTH ON) whilst DBCC CHECKDB is running to be closer to 90GB?

    I am assuming here that the drive that houses TEMPDB is not full (I have no access to check myself but will confirm)
    on the basis that I would expect an out of space condition to be thrown if that were the case and TEMPDB tried to extend.

    Aside from the fact that TEMPDB is a single file, I guess what I am trying to determine is whether requesting an increase in
    the size of TEMPDB will affect the execution time of DBCC CHECKDB or whether I am misunderstanding something here?

    TIA and Regards
    Steve O.

  • January 24, 2017 at 1:34 pm

    #1924728

    SteveOC - Monday, January 23, 2017 8:41 AM

    If I am interpreting this correctly, TEMPDB is currently around 20GB and DBCC CHECKDB indicates it would like it to be more like 90GB.

    CHECKDB runs with no errors (I will be monitoring TEMPDB tonight while it runs to see how much gets used).

    What does DBCC CHECKDB do when it has a TEMPDB that is so much smaller than it would like?

    Why doesn't TEMPDB grow (with AUTOGROWTH ON) whilst DBCC CHECKDB is running to be closer to 90GB?

    I am assuming here that the drive that houses TEMPDB is not full (I have no access to check myself but will confirm)
    on the basis that I would expect an out of space condition to be thrown if that were the case and TEMPDB tried to extend.

    Aside from the fact that TEMPDB is a single file, I guess what I am trying to determine is whether requesting an increase in
    the size of TEMPDB will affect the execution time of DBCC CHECKDB or whether I am misunderstanding something here?

    Your interpretation is correct. But it's not guaranteed to use that space, it's just an estimate of how much it could end up being. If you have autogrowth in case its needed for an emergency, it will grow. If a process needs tempdb and there is no more space left, the process aborts and you get an error about tempdb being out of space.
    20 GB is relatively small for a tempdb when the largest database is 600 GB. Getting more space generally won't affect the execution time but things are a bit more complicated than that.
    First, if you want to understand more about dbccc estimate only refer to this article:
    How does DBCC CHECKDB WITH ESTIMATEONLY work?
    But then you get to the part where you mention that tempdb is a single file...it probably shouldn't be. But that is always a can of worms when you talk about files needed, settings, growth, etc. You might want to start by reading this one:
    A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core

    And then to make it even more fun, Microsoft has more than one set of recommendations on the number of files but the one per core is the one you will see most often. However, that can also be a problem. Bob Ward who used to be the head guru of MS PSS  came up with a different set of recommendations based on testing that showed that often more than 8 files causing performance issues. Here is what he recommended:
    Recommendations to reduce allocation contention in SQL Server tempdb database

    Probably one of the most important parts and the one not mentioned too often in the debates on number of files is the importance of monitoring for contention. Both of the above links give more information on monitoring tempdb for contention. My guess from reading posts on various forums is that in general most people create 4 - 8 evenly sized files to start - I think it really depends on how tempdb is used on that server. But you will see conflicting arguments about the trace flags. You do want to be careful as 1117 is a system setting, not just tempdb so the impacts are on the server. I usually just turn on 1118. These trace flags are explained more in these articles:
    Trace Flags 1117, 1118, and Tempdb Configuration
    It Just Runs Faster: -T1117 and -T1118 changes for TEMPDB and user databases
    There are other links in the above posted articles that you will want to read to get a better idea of how you want to manage tempdb. 

    Sue

  • January 24, 2017 at 3:40 pm

    #1924764

    Sue_H - Tuesday, January 24, 2017 1:34 PM

    SteveOC - Monday, January 23, 2017 8:41 AM

    If I am interpreting this correctly, TEMPDB is currently around 20GB and DBCC CHECKDB indicates it would like it to be more like 90GB.

    CHECKDB runs with no errors (I will be monitoring TEMPDB tonight while it runs to see how much gets used).

    What does DBCC CHECKDB do when it has a TEMPDB that is so much smaller than it would like?

    Why doesn't TEMPDB grow (with AUTOGROWTH ON) whilst DBCC CHECKDB is running to be closer to 90GB?

    I am assuming here that the drive that houses TEMPDB is not full (I have no access to check myself but will confirm)
    on the basis that I would expect an out of space condition to be thrown if that were the case and TEMPDB tried to extend.

    Aside from the fact that TEMPDB is a single file, I guess what I am trying to determine is whether requesting an increase in
    the size of TEMPDB will affect the execution time of DBCC CHECKDB or whether I am misunderstanding something here?

    Your interpretation is correct. But it's not guaranteed to use that space, it's just an estimate of how much it could end up being. If you have autogrowth in case its needed for an emergency, it will grow. If a process needs tempdb and there is no more space left, the process aborts and you get an error about tempdb being out of space.
    20 GB is relatively small for a tempdb when the largest database is 600 GB. Getting more space generally won't affect the execution time but things are a bit more complicated than that.
    First, if you want to understand more about dbccc estimate only refer to this article:
    How does DBCC CHECKDB WITH ESTIMATEONLY work?
    But then you get to the part where you mention that tempdb is a single file...it probably shouldn't be. But that is always a can of worms when you talk about files needed, settings, growth, etc. You might want to start by reading this one:
    A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core

    And then to make it even more fun, Microsoft has more than one set of recommendations on the number of files but the one per core is the one you will see most often. However, that can also be a problem. Bob Ward who used to be the head guru of MS PSS  came up with a different set of recommendations based on testing that showed that often more than 8 files causing performance issues. Here is what he recommended:
    Recommendations to reduce allocation contention in SQL Server tempdb database

    Probably one of the most important parts and the one not mentioned too often in the debates on number of files is the importance of monitoring for contention. Both of the above links give more information on monitoring tempdb for contention. My guess from reading posts on various forums is that in general most people create 4 - 8 evenly sized files to start - I think it really depends on how tempdb is used on that server. But you will see conflicting arguments about the trace flags. You do want to be careful as 1117 is a system setting, not just tempdb so the impacts are on the server. I usually just turn on 1118. These trace flags are explained more in these articles:
    Trace Flags 1117, 1118, and Tempdb Configuration
    It Just Runs Faster: -T1117 and -T1118 changes for TEMPDB and user databases
    There are other links in the above posted articles that you will want to read to get a better idea of how you want to manage tempdb. 

    Sue

    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

  • January 24, 2017 at 4:23 pm

    #1924767

    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

  • January 25, 2017 at 7:04 am

    #1924838

    Sue_H - Tuesday, January 24, 2017 4:23 PM

    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

    http://www.sqlserverblogforum.com/tag/tempdb-monitoring/

    http://www.sqlserverblogforum.com/2016/12/vldb-very-large-database-dbcc-checkdb/

    I had same issue, you can find-out why it is growing large. Monitor it by using whoisactive and use different methods of checkDB and test the tempDB file creation etc.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply