locked
Are statistics important on a table that is almost exclusively INSERT RRS feed

  • Question

  • We're having an issue running stats on a large table. This table is primarily for audit purposes, and very rarely used to fetch data. Running stats is no taking greater than a day. Is it advisable to maintain stats on an almost exclusively INSERT table?
    Friday, March 6, 2020 8:49 PM

Answers

  • the thinking right now with the vendor is to TRUNCATE the table.

    Of course, it is the vendor who is insisting that we run the STATS job daily; they will not even look at database issues unless the STATS are up-to-date. The fundamental problem is that running that job is sucking resources from our database that are sorely needed during our busiest time of the year.

    Your vendor appears to be somewhat squared...

    You say that this is an auditing table, but not an auditing table for what. All depending on the business, audit records must have to be kept for several years, or they can be thrown away after a week. TRUNCATE TABLE sounds to me a little heavy-handed though. Then again, auditing records could be moved to a different database on a different server.

    If there are no application queries running against this table, only occasional investigation queries, I don't see as statistics as critical. The queries may always be of the slow type, since there may not be good index for what they are looking for.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, March 8, 2020 11:51 AM

All replies

  • You should be able to skip updating statistics on that table. Then again, it sounds like there are some queries run against it. Well, you will probably find out the hard way if these queries suffer from statistics being out of date.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, March 6, 2020 10:24 PM
  • To say that you dont need to update stats at all would be wrong, the correct question should be what should be the correct frequency. You should find out when stats are getting outdated and should take time to update it. May be once in a month on Sundays and let it run whole night

    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

    Saturday, March 7, 2020 6:08 AM
    Answerer
  • Can you show an example of SELECT statement you use against this table and an execution plan?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, March 8, 2020 5:15 AM
  • Thanks for your response. I understand that queries against that table would suffer. These queries are most often run by systems techs, and the records exist principally for audit purposes. If it gives you any indication how unnecessary the table is (from a query perspective) for day-to-day operations, the thinking right now with the vendor is to TRUNCATE the table.

    Sunday, March 8, 2020 10:55 AM
  • Thanks for responding, and tend to agree with you regarding the question being the frequency. But let me add some more information. Any queries against this table are most often run by systems techs, and even then quite rarely. The records exist principally for audit purposes. If it gives you any indication how unnecessary the table is (from a query perspective) for day-to-day operations, the thinking right now with the vendor is to TRUNCATE the table.

    Of course, it is the vendor who is insisting that we run the STATS job daily; they will not even look at database issues unless the STATS are up-to-date. The fundamental problem is that running that job is sucking resources from our database that are sorely needed during our busiest time of the year.

    Sunday, March 8, 2020 11:00 AM
  • I mignt be able to get a hold of those; let me check. FYI, I'm not the database guy here; I'm an application developer. I know just enough database stuff to get me into trouble. But I am curious what would be the point of having statistics for a table that is very, very rarely queried (SELECT or even UPDATE or DELETE), so much so that the vendor is willing to allow us to TRUNCATE the table as a solution.

    Thanks for responding!

    Sunday, March 8, 2020 11:05 AM
  • Statistics are usually used to retrieve the data ( they are changed/updated depends on amount of insert/update data). In your case specifically you can  change from Automatically y update statistic to asynchronous if you see potential impact... It would be also good to delete from that table the data which is not going to be used by users, 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, March 8, 2020 11:32 AM
  • the thinking right now with the vendor is to TRUNCATE the table.

    Of course, it is the vendor who is insisting that we run the STATS job daily; they will not even look at database issues unless the STATS are up-to-date. The fundamental problem is that running that job is sucking resources from our database that are sorely needed during our busiest time of the year.

    Your vendor appears to be somewhat squared...

    You say that this is an auditing table, but not an auditing table for what. All depending on the business, audit records must have to be kept for several years, or they can be thrown away after a week. TRUNCATE TABLE sounds to me a little heavy-handed though. Then again, auditing records could be moved to a different database on a different server.

    If there are no application queries running against this table, only occasional investigation queries, I don't see as statistics as critical. The queries may always be of the slow type, since there may not be good index for what they are looking for.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, March 8, 2020 11:51 AM
  • Thanks again for responding.

    The table provides the history of changes to a document to a "Version History" facility within the application. The thing is, that facility is very rarely used by end users. In fact, I daresay maybe one percent even know it is available. We (the techs) use it more often, but generally only for documents less than a month old. The table is storing document history for eight years. We will need a few years in case we get audited, but, as you say, we can move the table to another location for that purpose.

    But the conundrum seems to be our vendor insists that we run STATS against that table or they won't support it. That got me curious: why would you run STATS against a table used almost exclusively for INSERTS? Have I got a point here, or am I just ignorant?

    The vendor states there aren't any key relationships on the table, and, again, they are allowing us to truncate the table. We could live with the table, at least through the immediate crisis, if we weren't running STATS against it.

    I would be interested in your thoughts. 

    Sunday, March 8, 2020 12:02 PM
  • But the conundrum seems to be our vendor insists that we run STATS against that table or they won't support it. That got me curious: why would you run STATS against a table used almost exclusively for INSERTS? Have I got a point here, or am I just ignorant?

    You do have a good point but it seems academic if the vendor won't provide support without the daily stats job. I wouldn't expect sampled stats to be a resource hog so maybe that's an option. Otherwise, it seems TRUNCATE is your only option.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, March 8, 2020 1:54 PM
    Answerer
  • It is somewhat academic, but first I would really like to eliminate unnecessary strain on our database, particularly during our busiest time. I should have an opportunity to speak with the vendor's database guy this week and would like to be armed for this question. Our primary support vendor, who is between us and the developers of the product, is spouting the manual as if it is gospel. I want to know if it is the truth, or if it is something they have just never thought of.

    Thanks very much for your response!

     
    Monday, March 9, 2020 12:02 AM
  • Monday, March 9, 2020 4:52 AM
  • I see no reason why you should not stand by your opinion.

    And it makes no sense to say "If you have that audit table with data but don't run update stats on it, you are not supported. But we will support if you truncate the whole thing".


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, March 9, 2020 10:52 PM
  • I think the vendor is finally starting to see things my way 8)

    Thanks for the help!

    Tuesday, March 10, 2020 6:34 PM
  • Hello friend,
    Have you solved the question?
    If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members finding and reading the thread easily. 
    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 19, 2020 6:56 AM