New answers tagged

8

I believe your estimates are wrong because of an estimator bug that swaps two of the DATEDIFF arguments. I talk about this here: Performance Surprises and Assumptions : DATEDIFF A workaround is to calculate the first day of 13 months ago without using DATEDIFF (2008+): DATEADD(MONTH, -13, DATEADD(DAY, 1-DATEPART(DAY,GETDATE()), CONVERT(DATE, ...


0

If you are using the InnoDB engine for your tables #1 is relevant. If you are using MyISAM, #3 is relevant. But... You should "never" have to run ANALYZE TABLE. (There are exceptions, but they are so rare, that I will assume that the problem is elsewhere.) Possible cause #1: Your data has grown to be bigger than the cache (buffer_pool). How big is the ...


6

The first thing I would try is updating the SQL Server instance from the SP1 CU16 with QFE that you have right now, to SP3 CU1 (current 2012 build) then retesting to see if the behaviour is the same. For example: FIX: UPDATE STATISTICS performs incorrect sampling and processing for a table with columnstore index in SQL Server ...first released in SP2 CU2 ...


8

Couple of things .. Stop using the undocumented sp_MSforeachtable From Erin's blog - Understanding What sp_updatestats Really Updates sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows Note that ...



Top 50 recent answers are included