PSA: Update your table statistics 😠

josecgomez · August 11, 2020, 4:49pm

This is something that should be part of your SQL maintenance already, but if it isn’t please do yourself a favor and update statistics on your Db at least once or twice a month (more often if you have a really heavy usage)

We just spent 2 hours chasing an incredibly slow BAQ which was returning < 300 records and taking an inordinate amount of time to run.

We were using a new UD table for the first time in this BAQ and after much todo, we neded up generating an execution plan for our BAQ using SQL Server Profiler

This yielded a clean execution plan however a “few” warnings we noted on there said “Warning Columns with No Statistics”

Ran Update statistics on those tables with a full table scan, and the BAQ that was taking forever returned in 600ms…

Don’t mind me, I’m gonna go sit and rock in the corner… cause that’s several hours of me and @Banderson’s life we won’t get back… SIGH

kylepbsps · August 12, 2020, 1:22pm

Do you have a tutorial on this? I’m doing some googling but I’m not sure I really understand what im doing.

jgiese.wci · August 12, 2020, 2:54pm

Also if you are using the SQL Server Index and Statistics Maintenance scripts it may not be doing a full scan. I assumed these are updating my statistics, but i’m having the slow BAQ issues too. Still need to determine if running it manually fixes that.

Banderson · August 12, 2020, 9:28pm

Hey @jgiese.wci

We’re all still waiting to see if it actually fixed your issue.

Tick Tock GIF by memecandy - Find & Share on GIPHY

Arul · August 12, 2020, 11:39pm

@josecgomez

If you run PDT, it shows the required SQL setting. This is part of that.

josecgomez · August 12, 2020, 11:40pm

Interesting, however in my experience

The Table stats are not covered by pdt at least not in any reasonable way

You have to have a job to update it’s maybe it’s not frequent enough

I had the issue until I manually updated the table stats and I have 100% pass on pdt

So something is a miss somewhere

Arul · August 12, 2020, 11:45pm

Cool. Will check the SQL jobs.

enbw · August 13, 2020, 6:03am

Hi, out of interest what sql version are you running and how many rows are in the ud table. Did you have chance to run the sql query direct?

josecgomez · August 13, 2020, 10:12am

There were only 300 rows in the UD table
And I did run the query manually and it did take forever until we updated statistics
( I ran the same exact query as Epicor by capturing the query run using sql profiler)

When I say forever I mean 25 seconds for a 300 row table

After stats update it ran in less than 1 second (registered as 0 in Mssql studio)