May 20, 2014 at 5:51 pm
Comments posted to this topic are about the item Reindex Procedure with a Twist (or two) the SQL
September 15, 2015 at 6:34 am
Looks to be a very interesting script, thanks.
September 15, 2015 at 8:43 am
I'd used a private "DBA" database for years until my latest position which involves a large farm of database servers dedicated to a prominent third party ERP software product. Man, I had used that database for maintenance procedures, backup jobs, growth tracking, the works. When I suggested creating one on each of the servers for this purpose I suddenly had a very quiet room and the undivided attention of an angry mob. To paraphrase and summarize the answer: "No."
Actually, the long and complete answer: "No." <LOL>
Nice solution, though; good work!
September 16, 2015 at 7:48 am
I'm sorry but this looks like a (maybe not so current) re-write of something available at https://ola.hallengren.com/ for a very long time.
I think the original article you reference has comments from her and Steve in it. 😉
September 17, 2015 at 3:24 am
PHYData DBA (9/16/2015)
I'm sorry but this looks like a (maybe not so current) re-write of something available at https://ola.hallengren.com/ for a very long time.I think the original article you reference has comments from her and Steve in it. 😉
Having had some conversations with Ola about getting some these features put into his scripts, I don't think it is, but whomever helped put out the original code, I have been very grateful for a number years.
September 17, 2015 at 3:27 am
PHYData DBA (9/16/2015)
I'm sorry but this looks like a (maybe not so current) re-write of something available at https://ola.hallengren.com/ for a very long time.I think the original article you reference has comments from her and Steve in it. 😉
Having had some conversations with Ola about getting some these features put into his scripts, I don't think it is, but whomever helped put out the original code, I have been very grateful for a number years.
September 17, 2015 at 3:39 am
I don't seem to be able to reply to more than one of you. The last reply shows up and the previous one goes away. I will wait a little while and see if that fixes itself. In the mean time, I will just post un-quoted replies and see if that works.
September 17, 2015 at 3:54 am
Your Name Here (9/15/2015)
I'd used a private "DBA" database for years until my latest position which involves a large farm of database servers dedicated to a prominent third party ERP software product. Man, I had used that database for maintenance procedures, backup jobs, growth tracking, the works. When I suggested creating one on each of the servers for this purpose I suddenly had a very quiet room and the undivided attention of an angry mob. To paraphrase and summarize the answer: "No."Actually, the long and complete answer: "No." <LOL>
Nice solution, though; good work!
Thank you. This is about my third attempt to reply, we shall see what happens.
Should you ever end up in an environment or situation where there are are no mobs, I would be happy to hear the results. I have used this in environments up to about 2000 servers without much overhead. It is largely set it and forget it. Even on servers with a couple hundred descent sized databases (by number of objects), the DBA database remains pretty small. And like you said it can also be used to handle other DBA related tasks.
September 17, 2015 at 3:59 am
Replies seem to be working now. I started hold my head to the left and that seems to have fixed it. 😀
September 17, 2015 at 6:35 am
LOL
I've had "those days" as well & can relate. I typically blame a lack of enough coffee, conveniently omitting the definition of "enough"...
When I get the opportunity I'll certainly give it a whirl. I can appreciate the time and effort it takes to code it, test and debug, then code it again right <grin> and finally get it to a point where you're comfortable posting it to the community. Having someone like Ola in the discussion is awesome as well. We all benefit from recognized experts having input in stuff like this. Hopefully it will become a part of future tool sets.
Best of luck and I hope to see more code posted by you in the future.
Rock on, man.
September 17, 2015 at 7:19 am
Keith Hays (9/17/2015)
PHYData DBA (9/16/2015)
I'm sorry but this looks like a (maybe not so current) re-write of something available at https://ola.hallengren.com/ for a very long time.I think the original article you reference has comments from her and Steve in it. 😉
Having had some conversations with Ola about getting some these features put into his scripts, I don't think it is, but whomever helped put out the original code, I have been very grateful for a number years.
What are some of the things that you have in yours that are left out of Ola's? I like what you are saying, but would like to know what it means.
Since I also have been using a hybrid of these same things for over 15 years now you have my interest.
September 28, 2015 at 6:02 am
PHYData DBA (9/17/2015)
Keith Hays (9/17/2015)
PHYData DBA (9/16/2015)
I'm sorry but this looks like a (maybe not so current) re-write of something available at https://ola.hallengren.com/ for a very long time.I think the original article you reference has comments from her and Steve in it. 😉
Having had some conversations with Ola about getting some these features put into his scripts, I don't think it is, but whomever helped put out the original code, I have been very grateful for a number years.
What are some of the things that you have in yours that are left out of Ola's? I like what you are saying, but would like to know what it means.
Since I also have been using a hybrid of these same things for over 15 years now you have my interest.
Well, one of the issues I ran into with OLA's script was related to how partitions are re-indexed. Keep in mind this is from memory, At least until now, online rebuilding of an individual partition was not possible and in the environments I generally work in, offline indexing is just not possible short of a downtime which rarely happens which means I would have had to rely on Reorganization which I do not like to rely on in my environments. Reorganization a mechanism put out my Microsoft that is more of a feel good thing as opposed to a useful thing IMHO. And, it forces me to update statistics as a separate process. It is possible to rebuild all of the partitions online which was not in OLA's script at the time. It could be in there now, I have not check back recently.
The primary mechanism I rely on in the script to minimize the amount of reindexing that has to happen is the dynamically adjusted fill factor. This makes it possible, especially for indexes based on GUIDS, to constantly keep the fill factor adjusted to minimize fragmentation to begin with while not wasting any more space than is necessary. This has the effect of spreading the likelihood of rebuilding a particular index over a very long time thus radically cutting down both number of indexes being rebuilt as well as the time to rebuild them on any given night. At some point, I to need to develop a mechanism in the script to bring the fill factor level back up. I have seen that over the very long time, years usually, the usage of a particular table will change enough to warrant recovering the space. Of course with the advent of ExtremeIO, that isn't really an issue, but I suspect most folks are not using that yet.
September 28, 2015 at 6:03 am
PHYData DBA (9/17/2015)
Keith Hays (9/17/2015)
PHYData DBA (9/16/2015)
I'm sorry but this looks like a (maybe not so current) re-write of something available at https://ola.hallengren.com/ for a very long time.I think the original article you reference has comments from her and Steve in it. 😉
Having had some conversations with Ola about getting some these features put into his scripts, I don't think it is, but whomever helped put out the original code, I have been very grateful for a number years.
What are some of the things that you have in yours that are left out of Ola's? I like what you are saying, but would like to know what it means.
Since I also have been using a hybrid of these same things for over 15 years now you have my interest.
Well, one of the issues I ran into with OLA's script was related to how partitions are re-indexed. Keep in mind this is from memory, At least until now, online rebuilding of an individual partition was not possible and in the environments I generally work in, offline indexing is just not possible short of a downtime which rarely happens which means I would have had to rely on Reorganization which I do not like to rely on in my environments. Reorganization a mechanism put out my Microsoft that is more of a feel good thing as opposed to a useful thing IMHO. And, it forces me to update statistics as a separate process. It is possible to rebuild all of the partitions online which was not in OLA's script at the time. It could be in there now, I have not check back recently.
The primary mechanism I rely on in the script to minimize the amount of reindexing that has to happen is the dynamically adjusted fill factor. This makes it possible, especially for indexes based on GUIDS, to constantly keep the fill factor adjusted to minimize fragmentation to begin with while not wasting any more space than is necessary. This has the effect of spreading the likelihood of rebuilding a particular index over a very long time thus radically cutting down both number of indexes being rebuilt as well as the time to rebuild them on any given night. At some point, I to need to develop a mechanism in the script to bring the fill factor level back up. I have seen that over the very long time, years usually, the usage of a particular table will change enough to warrant recovering the space. Of course with the advent of ExtremeIO, that isn't really an issue, but I suspect most folks are not using that yet.
September 28, 2015 at 6:03 am
Well, one of the issues I ran into with OLA's script was related to how partitions are re-indexed. Keep in mind this is from memory, At least until now, online rebuilding of an individual partition was not possible and in the environments I generally work in, offline indexing is just not possible short of a downtime which rarely happens which means I would have had to rely on Reorganization which I do not like to rely on in my environments. Reorganization a mechanism put out my Microsoft that is more of a feel good thing as opposed to a useful thing IMHO. And, it forces me to update statistics as a separate process. It is possible to rebuild all of the partitions online which was not in OLA's script at the time. It could be in there now, I have not check back recently.
The primary mechanism I rely on in the script to minimize the amount of reindexing that has to happen is the dynamically adjusted fill factor. This makes it possible, especially for indexes based on GUIDS, to constantly keep the fill factor adjusted to minimize fragmentation to begin with while not wasting any more space than is necessary. This has the effect of spreading the likelihood of rebuilding a particular index over a very long time thus radically cutting down both number of indexes being rebuilt as well as the time to rebuild them on any given night. At some point, I to need to develop a mechanism in the script to bring the fill factor level back up. I have seen that over the very long time, years usually, the usage of a particular table will change enough to warrant recovering the space. Of course with the advent of ExtremeIO, that isn't really an issue, but I suspect most folks are not using that yet.
September 28, 2015 at 9:38 am
Keith Hays (9/28/2015)
Well, one of the issues I ran into with OLA's script was related to how partitions are re-indexed. Keep in mind this is from memory, At least until now, online rebuilding of an individual partition was not possible and in the environments I generally work in, offline indexing is just not possible short of a downtime which rarely happens which means I would have had to rely on Reorganization which I do not like to rely on in my environments. Reorganization a mechanism put out my Microsoft that is more of a feel good thing as opposed to a useful thing IMHO. And, it forces me to update statistics as a separate process. It is possible to rebuild all of the partitions online which was not in OLA's script at the time. It could be in there now, I have not check back recently.The primary mechanism I rely on in the script to minimize the amount of reindexing that has to happen is the dynamically adjusted fill factor. This makes it possible, especially for indexes based on GUIDS, to constantly keep the fill factor adjusted to minimize fragmentation to begin with while not wasting any more space than is necessary. This has the effect of spreading the likelihood of rebuilding a particular index over a very long time thus radically cutting down both number of indexes being rebuilt as well as the time to rebuild them on any given night. At some point, I to need to develop a mechanism in the script to bring the fill factor level back up. I have seen that over the very long time, years usually, the usage of a particular table will change enough to warrant recovering the space. Of course with the advent of ExtremeIO, that isn't really an issue, but I suspect most folks are not using that yet.
I had never heard of dynamically adjusting Index Fill factor based on the values populated in static length datatype.
Unless you are storing the GUID in a variable length not UID data type and allowing values that are not a GUID.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply