Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.5K Microsoft Dynamics NAV
- 18.5K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 264 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 992 SQL General
- 384 SQL Performance
- 33 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Options
sp_updatestats and indexed views
Phenno
Member Posts: 630
Ok, I know there is several more threads with similar topic but something bothers me regarding advice to disable autocreate and autoupdate of stats while maintaining them by scheduled jobs (at least for pre-NAV2013 clients).
Advice was to use sp_createstats indexonly and sp_updatestats procedures at some schedule (daily). But, I found here on mibuso (and confirmed on my sql server) that those procedures will not update stats for indexed views (VSIFTs). Some of those views have stats updated upon index rebuild but if that occurs rarely those stats will be updated rarely too.
So, what are you doing to update stats for indexed view too? Use Olla's script (as far as I know it does cover stats on vsifts)? Use maintenance plan for update of stats? Something else?
Also, I had a small discussion regarding advice to keep enabled sp_createstats and sp_updatestats (async) on nav2013+ version. I must confess that I only partially understand pros and cons for this decision so I tried to read a little bit more on this topic and found some earlier conversations on mibuso and some other blogs/forums. What I can see is that for previous versions if NAV the major two arguments to disable autostats were (mostly from Jorg Stryk):
- too many stats generated due to way how NAV formed queries (cursors?)
- some issues when changing tables with a lot of autostats (limits of number of objects, etc).
On NAV2013+ the large difference is that pages are now scripted as outer join queries. But they still use a whole list of fields and user can still make filter on any available column. Wouldn't we get a lot of autostats in NAV2013+ too? Or there will be less autostats created cause cursors are not in use anymore?
Advice was to use sp_createstats indexonly and sp_updatestats procedures at some schedule (daily). But, I found here on mibuso (and confirmed on my sql server) that those procedures will not update stats for indexed views (VSIFTs). Some of those views have stats updated upon index rebuild but if that occurs rarely those stats will be updated rarely too.
So, what are you doing to update stats for indexed view too? Use Olla's script (as far as I know it does cover stats on vsifts)? Use maintenance plan for update of stats? Something else?
Also, I had a small discussion regarding advice to keep enabled sp_createstats and sp_updatestats (async) on nav2013+ version. I must confess that I only partially understand pros and cons for this decision so I tried to read a little bit more on this topic and found some earlier conversations on mibuso and some other blogs/forums. What I can see is that for previous versions if NAV the major two arguments to disable autostats were (mostly from Jorg Stryk):
- too many stats generated due to way how NAV formed queries (cursors?)
- some issues when changing tables with a lot of autostats (limits of number of objects, etc).
On NAV2013+ the large difference is that pages are now scripted as outer join queries. But they still use a whole list of fields and user can still make filter on any available column. Wouldn't we get a lot of autostats in NAV2013+ too? Or there will be less autostats created cause cursors are not in use anymore?
0
Comments
Haven't had an issue with that yet.
I'm doing some digging into the EXEC sp_createstats 'indexonly'.
Id it refreshes on index rebuilds just run a rebuild every weekend.