I have a very large table with 500m rows and 150 columns (I did not design the table, im simply trying to figure out what is going on here). I try to update a single statistic for this table.

I run like so

update statistics A (stat1) with sample 5000000 rows

It takes me 35 minutes for this to complete. Subsequent runs of the same task finish in less than 1 minute. I don't know what is happening here. I tried to monitor sys.dm_exec_requests and there is no other load, I did notice on the initial run that cpu_time to elapsed_time ratio was pretty low (like 1:5 vs 1:1 on subsequent runs).

  • If the subsequent runs are tested within a short time of the original run, then the likely difference is memory caching. From a cold start, all 5m rows need to be loaded from disk, whereas on subsequent runs, some percentage of disk pages are already cached. – Laughing Vergil Oct 30 '18 at 16:02
  • @LaughingVergil is dropcleanbuffers with a checkpoint (dba.stackexchange.com/questions/10818/…) sufficient to clear the memory cache? I wasn't able to reproduce the 35 minute wait time doing this – Steven Hsu Oct 30 '18 at 16:20
  • Well, it should be sufficient to clear the cache provided that you run the CHECKPOINT before the DropCleanBuffers. Unless your drive(s) or the system itself (outside of SQL Server) is caching the data, that would reduce your memory-loaded data to nothing. However, if your disk(s) have cache, or the system itself outside of SQL Server has sufficient memory and is caching, then your experience would vary as well. The quantity of cached data compared to the overall size of the data would also affect the experience. – Laughing Vergil Oct 30 '18 at 16:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.