Skip to Content
author's profile photo Former Member
Former Member

update Index Stats using Sampling

Hi Team Good afternoon We currently have a JOB that runs UIS using sampling = 30 and is failing on a table that has 293412179 rows and one CI Error - the tempdb is full for the DB maintenance a.c which is mapped to the DBA tempdb Can't allocate space for object 'temp worktable' in database 'DBA_tempdb' because 'system' segment is full/has no free extents. If you ran out of space in syslogs, dump the transaction log. Otherwise, use ALTER DATABASE to increase the size of the segment. When we run the same job using sampling = 10 it works fine. Question What are the risk associated with changing the sampling ? Will that impact the query plans for the app using that table ? -Sid

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Posted on Dec 04, 2015 at 03:13 PM

    If you are on 15.7sp130+, use update statistics with hashing instead of update statistics with sampling. My opinion is that it is more accurate than sampling and definitely would eliminate the issue with tempdb as it uses next to nothing for tempdb space - result is it often is much faster was well due to not having to wait for IOs.

    Add a comment
    10|10000 characters needed characters exceeded

    • Sorry - this wacky editor on SCN is giving me fits on cut & pasting....will try again...

      What you might want to do is look at monProcessWaits before/after the update stats - if you see most of the time is on WaitEvent=29 or 144, then the time is all spent on IO and not a lot can be done about it other than speeding up the IO subsystem (compare WaitTime to Waits to get ms/IO)...

      It is also possible that the particular table only has low domain values which doesn't require a lot of tempdb space necessarily. The real gains I noted was when the tempdb space was reduced - e.g. if you set statistics on, check out the drop in writes.....if not that much, then that is why no real gain - and likely low cardinality attributes....(or else someone did stats hashing by default - make sure you test with no_hashing and not just defaults as it could be inherited as well as set via sp_configure)....

  • Posted on Dec 03, 2015 at 07:55 PM

    For me I don't think it will make a huge amount of difference to plan choice unless you don't have in place non-sampled densities.

    When you update statistics with sampling the pages and values it reads it considers as the 'full' table from the density point of view. If you have 100 unique values and you do sampling of 10% it'll calculate that you have 10 unique values and give you a total density of 0.1 instead of 0.01. This can make a big difference to costings in some areas (unknowns/subqueries). You might also end up with slightly less granular histograms.

    For this reason the any densities that have been calculated without sampling will not be replaced with sampled densities by default. If you've never gathered a non-sampled density then you may end up reducing your density further ( I wouldn't know without checking whether aa density calculated with 30% I'd replaced when sampled with 10%)

    If you don't have a non-sampled density and sont have the resources to generate them you should consider adjusting the density by a factor equal to 1/sampling percent - see sp_modifystats.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Simon We recently started using the UIS with sampling, before that we were simply doing US not even UIS on this DB Have requested the app team to see if the can provide any details on the Queries hitting that table, as it currently has only one view dependent on the table -Sid

  • Posted on Dec 04, 2015 at 03:03 PM

    Siddharth

    May be you can take a different approach šŸ˜Š

    Some of the places I worked, I have seen tempdb configured with log and data mixed just like master database. So you have full tempdb available for growing data and log. This is done because unlike normal user database we do not need to worry about tempdb recovery.


    Risk of runaway process exists in all set-ups.

    This architecture has a danger of runaway transaction filling up whole tempdb.

    But it also has the benefit of space for the DBA maintenance work where whole tempdb is available for you to use for worktables and/or transaction logs as needed.

    Think about it, test it and if found suitable implement it

    HTH

    Avinash

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.