October 20, 2020 at 2:22 pm
I ran the update stats on a table by creating the indexes, when i checked after executing the update stats the query performance is degraded , may i know what are possible reasons for the peformance degradation ?
October 20, 2020 at 2:53 pm
It depends on the query. If the query is an INSERT or an UPDATE query, adding an index may slow it down.
You can update statistics though without creating indexes. Those 2 things are completely different concepts. Statistics are used for estimates whereas an index is to help with scanning the data.
But the first thing to check when looking at performance issues is the execution plan. If you have the plan from before the index was created and after, compare them and you may see why it is slower.
I am hoping that this is on a test system as adding indexes should ALWAYS be done in test before pushing out to live so you can compare performance and check to make sure it isn't hurting other queries too badly.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 20, 2020 at 3:47 pm
I ran the update stats on a table by creating the indexes, when i checked after executing the update stats the query performance is degraded , may i know what are possible reasons for the peformance degradation ?
The most probable reason is (and I assume that you meant you rebuilt the indexes) that most of your indexes are probably set to a default Fill Factor of "0"... which is the same as a Fill Factor of "100".
While not true of all indexes, rebuilding and index that has a Fill Factor of 0/100 means that you've just removed ALL usable free space from those indexes. That means than any and all out-of-order inserts and any and all "ExpAnsive" updates are absolutely guaranteed to cause massive numbers of page splits.
As Brent Ozar has been know to say, rebuilding indexes is a really expensive way to rebuild statistics. As I would add, it's usually better to do no index maintenance than it is to do it wrong. I'll also add that if you are following what most people believe to be "Best Practice" index maintenance, then you're doing it wrong. Everyone seems to have read the "Reorganize between 5 and 30% fragmentation and Rebuild above 30%" and the example code that supports that an has jumped to the conclusion that it's a "Best Practice" because of the unfortunate wording in the MS documentation that suggests it is. Almost no one has paid attention to the subnote under that very recommendation that says it's just a starting point and that you should experiment which each index to determine what is best.
So, my starting recommendation for folks is ... rebuild statistics often for those indexes that need it and stop doing regular index maintenance with the occasional exception of rebuilding to recover large amounts of disk space for individual indexes or indexes that require a lot of "range scan" activity. Even then, you want to study the index and find out if the fragmentation is occurring throughout the index or only at the logical end of the index. You also need to figure out if rebuilding at 0/100% is going to result in massive page splits just as soon as the rebuild is done and if a lower Fill Factor will actually help.
Just to emphasize, rebuilding stats is absolutely essential, especially for "ever increasing keyed" indexes and doing index maintenance the wrong way includes the supposed common "Best Practices". It's better to do no index maintenance than to do it wrong.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 21, 2020 at 7:42 am
Also keep in mind, you can just update the statistics.
Have a look at Erin Stellato's advices: https://sqlperformance.com/2017/10/sql-statistics/updates-to-statistics
or even the one on how you can use Ola's solution to perform this maintenance: https://www.sqlskills.com/blogs/erin/updating-statistics-with-ola-hallengrens-script/
here is a nice discussion about it all "Mixed Extents: 02 - Indexing and Stats Maintenance"
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 21, 2020 at 12:48 pm
And to understand the performance degradation, capture the execution plan of the query, once when it's running well, and once when it's running badly. Compare those to understand the changes. That should give you a lot of understanding of what's going on.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 21, 2020 at 6:43 pm
here is a nice discussion about it all "Mixed Extents: 02 - Indexing and Stats Maintenance"
I saw that. It's a shame that they didn't get into the Index Maintenance area more but they are spot on about the current supposed "Best Practices" of Reorganize between 5 and 30% and Rebuild above 30% not actually being a "Best Practice". They did start to talk about how Page Density is also important but not to any great extent (pun intended) 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
October 22, 2020 at 8:50 am
... They did start to talk about how Page Density is also important but not to any great extent (pun intended) 😀
I think the podcast should fit 1 hour 😉
I totally like it !
Keypoint is always: "YMMV" -> The engine is becoming better over time, so test your cases to see how it performs with your own load.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 23, 2020 at 2:29 pm
Jeff Moden wrote:... They did start to talk about how Page Density is also important but not to any great extent (pun intended) 😀
I think the podcast should fit 1 hour 😉
I totally like it !
Keypoint is always: "YMMV" -> The engine is becoming better over time, so test your cases to see how it performs with your own load.
If you compare the title of the session to the questions that were asked, there was a whole lot of deviation from the expected subject. They would have had more time to talk about the advertised subject, including page density, if the person running the show had stuck to the subject.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply