July 27, 2018 at 10:53 am
I'm having some performance problems I'm attempting to run down. One of the major things is it's requesting 15GB of ram to run a query, that then only uses 32kb of RAM. One of the suggestions I've found is to look for and deal with stale stats. I've run an sp_update stats on the DB, and still having issues. I'm suspecting fullscan is needed, but trying to find culprits. Any tips on that?
One thing I've been looking at is the execution plan. I've read that when the estimated rows doesn't match the actual rows, at least reasonably close, that's a stats problem. I've seen examples of that, but sometimes, even refreshing the stats on the tables doesn't help. What else can cause this issue?
For example, I'll look at the actual execution plan. I'll see at the start, it does a seek. It estimates 395 rows read. Actual rows read is 141,000. So I'll go and update stats on that table with fullscan with:
update statistics tableName with fullscan
Run run the procedure, and see the exact same thing. Even with a DBCC FREEPROCCACHE to rule out any caching shenanigans.
Any ideas or tips here?
July 30, 2018 at 2:25 pm
llevity - Friday, July 27, 2018 10:53 AMI'm having some performance problems I'm attempting to run down. One of the major things is it's requesting 15GB of ram to run a query, that then only uses 32kb of RAM. One of the suggestions I've found is to look for and deal with stale stats. I've run an sp_update stats on the DB, and still having issues. I'm suspecting fullscan is needed, but trying to find culprits. Any tips on that?One thing I've been looking at is the execution plan. I've read that when the estimated rows doesn't match the actual rows, at least reasonably close, that's a stats problem. I've seen examples of that, but sometimes, even refreshing the stats on the tables doesn't help. What else can cause this issue?
For example, I'll look at the actual execution plan. I'll see at the start, it does a seek. It estimates 395 rows read. Actual rows read is 141,000. So I'll go and update stats on that table with fullscan with:
update statistics tableName with fullscan
Run run the procedure, and see the exact same thing. Even with a DBCC FREEPROCCACHE to rule out any caching shenanigans.
Any ideas or tips here?
Some other things that can throw it off would be using local variables, parameter sniffing, data skew and on the opposite end would be incorrect filtered statistics, You can have issues even with a bad database design and/or poorly written query.
Sue
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply