Ygthkb

Why is working on the same position for more than 15 years not a red flag?

I am on the US no-fly list. What can I do in order to be allowed on flights which go through US airspace?

What does "at rest" mean involving data encryption?

Is it a fallacy if someone claims they need an explanation for every word of your argument to the point where they don't understand common terms?

How to remove trailing forward slash

What are the advantages of using `make` for small projects?

Why do neural networks need so many training examples to perform?

Why don't I see the difference between two different files in insert mode in vim?

Why zero tolerance on nudity in space?

Are there any monsters that consume a player character?

How to acknowledge an embarrassing job interview, now that I work directly with the interviewer?

Can a hotel cancel a confirmed reservation?

What formula could mimic the following curve?

Is there hidden data in this .blend file? Trying to minimize the file size

When teaching someone how to prove a function is uniformly continuous, using epsilon/delta, which example would be among the simplest?

Strange Sign on Lab Door

What is the wife of a henpecked husband called?

Word or phrase for showing great skill at something without formal training in it

Why can a 352GB NumPy ndarray be used on an 8GB memory macOS computer?

A donkey does not know what kind of fruit persimmon is

Can a person refuse a presidential pardon?

Approaches to criticizing short fiction

What's the most convenient time of year in the USA to end the world?

Can we use the stored gravitational potential energy of a building to produce power?

ola-hallengren index rebuild with multi thread and multiple days

Is a weekly rebuild of indexes a good idea?Ola Hallengren with database mirroringOla Hallengren index maintenance - long periods of time between commands?ola hallengren Index Maintenance script - GUID IndexModifying Ola Hallengrens index maintenance script for deadlocksShrinking T-Log file every weekend basisOla Hallengren Index maintenance on servers with Availability GroupsOla Hallengren Parameters for Index Rebuild/Reorganize on SharePointWarning in Ola Hallengren Index Maintenance JobOla Hallengren Index Optimize - Skip “unused” IndexesOla Hallengren Index Maintenance with compressed indexes

0

Ola scripts for index maintenance work really well for smaller databases. For multi terabyte databases with big indices, many partitions, and a small interval of schedule times I observed that the script always tends to rebuild same indices and never finishes.

How can we implement the solution to capture and then run the index optimization for certain duration of days and then reset. Can we also have it run in multiple threads?

We did develop a solution to achieve it, but the only problem we have is updating it on regular basis.

sql-server maintenance ola-hallengren

share|improve this question

edited May 5 '18 at 19:02

LowlyDBA

7,07252542

asked May 5 '18 at 18:09

Srikar Reddy KasireddySrikar Reddy Kasireddy

1

bumped to the homepage by Community♦ 6 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Can you include what you have worked on so far? What do you mean by updating it is a problem?
  
  – LowlyDBA
  May 5 '18 at 19:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Keeping up to date with the latest versions.
  
  – Srikar Reddy Kasireddy
  May 7 '18 at 3:49
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  We developed a solution where it captured the index stats in a table and loop the table for the duration of days we setup and rebuild or reorg the indices on everyday schedule. Once the max days is reached, the next run will start capturing the fresh stats of indices and run through same. We achieved parallel threads by running all this with sql agent jobs controleld by a controller job.
  
  – Srikar Reddy Kasireddy
  May 7 '18 at 3:53
  

  
  
  
  

add a comment | 

0

Ola scripts for index maintenance work really well for smaller databases. For multi terabyte databases with big indices, many partitions, and a small interval of schedule times I observed that the script always tends to rebuild same indices and never finishes.

How can we implement the solution to capture and then run the index optimization for certain duration of days and then reset. Can we also have it run in multiple threads?

We did develop a solution to achieve it, but the only problem we have is updating it on regular basis.

sql-server maintenance ola-hallengren

share|improve this question

edited May 5 '18 at 19:02

LowlyDBA

7,07252542

asked May 5 '18 at 18:09

Srikar Reddy KasireddySrikar Reddy Kasireddy

1

bumped to the homepage by Community♦ 6 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Can you include what you have worked on so far? What do you mean by updating it is a problem?
  
  – LowlyDBA
  May 5 '18 at 19:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Keeping up to date with the latest versions.
  
  – Srikar Reddy Kasireddy
  May 7 '18 at 3:49
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  We developed a solution where it captured the index stats in a table and loop the table for the duration of days we setup and rebuild or reorg the indices on everyday schedule. Once the max days is reached, the next run will start capturing the fresh stats of indices and run through same. We achieved parallel threads by running all this with sql agent jobs controleld by a controller job.
  
  – Srikar Reddy Kasireddy
  May 7 '18 at 3:53
  

  
  
  
  

add a comment | 

Ola scripts for index maintenance work really well for smaller databases. For multi terabyte databases with big indices, many partitions, and a small interval of schedule times I observed that the script always tends to rebuild same indices and never finishes.

How can we implement the solution to capture and then run the index optimization for certain duration of days and then reset. Can we also have it run in multiple threads?

We did develop a solution to achieve it, but the only problem we have is updating it on regular basis.

sql-server maintenance ola-hallengren

share|improve this question

edited May 5 '18 at 19:02

LowlyDBA

7,07252542

asked May 5 '18 at 18:09

Srikar Reddy KasireddySrikar Reddy Kasireddy

1

share|improve this question

edited May 5 '18 at 19:02

LowlyDBA

7,07252542

asked May 5 '18 at 18:09

Srikar Reddy KasireddySrikar Reddy Kasireddy

1

share|improve this question

edited May 5 '18 at 19:02

LowlyDBA

7,07252542

asked May 5 '18 at 18:09

Srikar Reddy KasireddySrikar Reddy Kasireddy

1

edited May 5 '18 at 19:02

LowlyDBA

7,07252542

edited May 5 '18 at 19:02

LowlyDBA

7,07252542

asked May 5 '18 at 18:09

Srikar Reddy KasireddySrikar Reddy Kasireddy

1

asked May 5 '18 at 18:09

Srikar Reddy KasireddySrikar Reddy Kasireddy

1

1 Answer
1

active

oldest

votes

0

For multi terabyte databases with big indices, many partitions, and a small interval of schedule times I observed that the script always tends to rebuild same indices and never finishes.

This is very common for VLDBs. You can be a bit smatter and follow Paul Randal's Quick list of VLDB maintenance best practices. You can rebuild indexes ONLINE in Enterprise edition.

In your case since you are using Ola's Index Optimization scripts, you are running into endless index defragmentation which you can solve by being analyzing CommandLog table

I have given few more thoughts on Is a weekly rebuild of indexes a good idea? covering maxdop option.

I would say, if your databases are extremely big (TB size), atleast do a reorg instead of rebuild and manually update stats. This will put you in a good reasonable state since a reorg will start from where it got killed.

For running in parallel threads, you can drive of commandLog table and use powershell runspace (be careful of not spawing many threads - see Notes on usage of using runspace).

share|improve this answer

answered May 5 '18 at 20:16

KinKin

53.8k481191

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f205937%2fola-hallengren-index-rebuild-with-multi-thread-and-multiple-days%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

For multi terabyte databases with big indices, many partitions, and a small interval of schedule times I observed that the script always tends to rebuild same indices and never finishes.

This is very common for VLDBs. You can be a bit smatter and follow Paul Randal's Quick list of VLDB maintenance best practices. You can rebuild indexes ONLINE in Enterprise edition.

In your case since you are using Ola's Index Optimization scripts, you are running into endless index defragmentation which you can solve by being analyzing CommandLog table

I have given few more thoughts on Is a weekly rebuild of indexes a good idea? covering maxdop option.

I would say, if your databases are extremely big (TB size), atleast do a reorg instead of rebuild and manually update stats. This will put you in a good reasonable state since a reorg will start from where it got killed.

For running in parallel threads, you can drive of commandLog table and use powershell runspace (be careful of not spawing many threads - see Notes on usage of using runspace).

share|improve this answer

answered May 5 '18 at 20:16

KinKin

53.8k481191

add a comment | 

0

For multi terabyte databases with big indices, many partitions, and a small interval of schedule times I observed that the script always tends to rebuild same indices and never finishes.

This is very common for VLDBs. You can be a bit smatter and follow Paul Randal's Quick list of VLDB maintenance best practices. You can rebuild indexes ONLINE in Enterprise edition.

In your case since you are using Ola's Index Optimization scripts, you are running into endless index defragmentation which you can solve by being analyzing CommandLog table

I have given few more thoughts on Is a weekly rebuild of indexes a good idea? covering maxdop option.

I would say, if your databases are extremely big (TB size), atleast do a reorg instead of rebuild and manually update stats. This will put you in a good reasonable state since a reorg will start from where it got killed.

For running in parallel threads, you can drive of commandLog table and use powershell runspace (be careful of not spawing many threads - see Notes on usage of using runspace).

share|improve this answer

answered May 5 '18 at 20:16

KinKin

53.8k481191

add a comment | 

For multi terabyte databases with big indices, many partitions, and a small interval of schedule times I observed that the script always tends to rebuild same indices and never finishes.

This is very common for VLDBs. You can be a bit smatter and follow Paul Randal's Quick list of VLDB maintenance best practices. You can rebuild indexes ONLINE in Enterprise edition.

In your case since you are using Ola's Index Optimization scripts, you are running into endless index defragmentation which you can solve by being analyzing CommandLog table

I have given few more thoughts on Is a weekly rebuild of indexes a good idea? covering maxdop option.

I would say, if your databases are extremely big (TB size), atleast do a reorg instead of rebuild and manually update stats. This will put you in a good reasonable state since a reorg will start from where it got killed.

For running in parallel threads, you can drive of commandLog table and use powershell runspace (be careful of not spawing many threads - see Notes on usage of using runspace).

share|improve this answer

answered May 5 '18 at 20:16

KinKin

53.8k481191

share|improve this answer

answered May 5 '18 at 20:16

KinKin

53.8k481191

answered May 5 '18 at 20:16

KinKin

53.8k481191

answered May 5 '18 at 20:16

KinKin

53.8k481191