Skip to main content
102 votes
Accepted

SQL Server: How to track progress of CREATE INDEX command?

I think the following query will at least get you quite close. It makes use of a DMV that was introduced in SQL Server 2014: sys.dm_exec_query_profiles (and thanks to Martin Smith for introducing it ...
Solomon Rutzky's user avatar
17 votes

SQL Server: How to track progress of CREATE INDEX command?

I think we can remove the @SPID variable with a reference to sys.dm_exec_requests: ;WITH agg AS ( SELECT SUM(qp.[row_count]) AS [RowsProcessed], SUM(qp.[estimate_row_count]) AS [...
Bruce Pratt's user avatar
15 votes
Accepted

Index fragmentation problem after disabling page-level locking for an Index

The answer to your query is buried in some documentation: Online index rebuilding may increase fragmentation when it is allowed to run with MAX DOP > 1 and ALLOW_PAGE_LOCKS = OFF options. The ...
Josh Darnell's user avatar
  • 29.3k
14 votes

When should I rebuild indexes?

As noted in the accepted answer from Matt M, a common rule of thumb is that indexes that are over 30% fragmented should be rebuilt. This query will help you find how many indexes you have that are ...
amandamaddox3's user avatar
14 votes

Rebuilding indexes for a partitioned table having 300 partitions

While examining the query plan of rebuilt indexes, I can see the 'Estimated number of rows' is 180k but 'Actual number of rows' is 300 partitions * 180,000 rows = 54 Million rows, even though I am ...
Paul White's user avatar
  • 84.3k
11 votes
Accepted

Rebuild Index not freeing up space

If you are expecting a rebuild to make the data files in the file system smaller, this is not how it works. In fact, rebuilding can even lead to larger data files on disk, as new pages and extents ...
Aaron Bertrand's user avatar
11 votes

Why does an index rebuild requires a Sch-M lock?

The documentation for How Online Index Operations Work says: SCH-M (Schema Modification) if any source structure (index or table) is dropped.* * The index operation waits for any uncommitted update ...
Paul White's user avatar
  • 84.3k
11 votes
Accepted

How likely is fragmentation on a table with 40000 products likely to affect performance

Based on the data that you have provided - there are 67 total pages. Index fragmentation for such a small table would not affect the performance. I would not worry about index fragmentation for the ...
Kin Shah's user avatar
  • 62.3k
11 votes
Accepted

Queries and updates extremely slow after IndexOptimize

I suspect you've got a different sample rate defined between your two maintenance approaches. I believe Ola's scripts use default sampling unless you specify the @StatisticsSample parameter, which it ...
John Eisbrener's user avatar
10 votes
Accepted

Scheduling index rebuild jobs

You should use smart index maintenance scripts as opposed to maintenance task. Should I run the job in primary or in replica? Yes, as they get replicated on secondary replicas. If I run it ...
Kin Shah's user avatar
  • 62.3k
10 votes
Accepted

ALTER INDEX REORGANIZE questions

If run multiple REORGANIZE operations on separate indexes at the same time (say 10+, in separate SSMS windows/sessions) - can it cause data corruption or any other known issues ? No it would not. ...
Shanky's user avatar
  • 19k
9 votes

SQL Server: How to track progress of CREATE INDEX command?

Since this topic seems to still be active, I thought it would be worthwhile to note that using the new resumable index operations in SQL Server 2019 and Azure SQL DB (in 150 compatibility mode) ...
Pam Lahoud's user avatar
9 votes
Accepted

REORGANIZE INDEX blocks other queries

Unfortunately, your understanding is a misunderstanding, and REORGANIZE requires locks for its operation. It just doesn't have as severe locking as an offline REBUILD. Since REORGANIZE takes X locks, ...
Forrest's user avatar
  • 4,169
9 votes
Accepted

Stats update during Index rebuild

Yes, statistics are updated when the index is rebuilt. No, statistics are usually gathered as a side effect of populating the index*. The data stream is already sorted as needed so it makes sense to ...
Paul White's user avatar
  • 84.3k
8 votes

Does index rebuild time depend on the fragmentation level?

For everyone interested, I have created a chart showing the index REBUILD duration of about 2500 index rebuilds within couple of weeks in relation to the fragmentation of the index and it's size in ...
Magier's user avatar
  • 4,787
8 votes
Accepted

RCSI and index maintenance

However as long as we continue to rebuild online or reorganize we will retain the 14 bytes per row. Is that correct? This is true. REBUILD WITH (ONLINE = ON) and REORGANIZE will leave the 14 bytes ...
Josh Darnell's user avatar
  • 29.3k
8 votes
Accepted

Does sp_BlitzIndex uses Query Store data?

backwards If anything, you should use sp_BlitzIndex to further validate missing index requests you find while looking for queries to tune to find overall potential uses for the index. The real value ...
Erik Darling's user avatar
  • 40.8k
7 votes
Accepted

Database maintenance rebuilding indexes daily

Firstly you should take into account the page_count of the index. If the page_count is less than 1000 (or whatever you decide) then you should ignore the index. We have a maintenance script which ...
Porkster's user avatar
  • 132
7 votes

How to prevent transaction log getting full during index reorganize?

I've had this issue before. You have a big database and a small log drive. You want to reorganize (for a variety of reasons). When you attempt this on a large fragmented table the log fills until ...
Cody Konior's user avatar
  • 3,508
7 votes

Filter Schema In Index Optimize Script

Yes, if you look at the documentation, you see that you can use the @indexes parameter to specify which indexes to defragment. You can use % wildcards in your object names and you can prefix them with ...
Tom V's user avatar
  • 15.7k
7 votes
Accepted

SQL Server Index and Statistics Maintenance performance question

Can somebody share with me his experience in using IndexOptimize for a database with a very high number of indexes ? You are right, Ola's script first gets data from all the index related DMV's and ...
Randi Vertongen's user avatar
6 votes

Heap having high fragmentation

I've seen this question pop up enough times over the years that I figured it finally warranted a real answer. Yes, this answer is years after the fact, but facts remain the same now as they did in ...
John Eisbrener's user avatar
6 votes

Rebuild index online without risk to other requests

There are several options available to help reduce the problems you're seeing: /* set the LOCK_TIMEOUT value to the maximum number of milliseconds you want to wait for the ALTER INDEX REBUILD to ...
Hannah Vernon's user avatar
  • 70.1k
6 votes
Accepted

Ola Hallengren Index Maintenance with compressed indexes

Yes - I don't see how Ola's script could possibly alter the existing compression level of an index. If you look at the source for Ola's Index Maintenance, you will not see the word COMPRESSION ...
Scott Hodgin - Retired's user avatar
6 votes

Indexes getting highly fragmented during normal usage of system

If you can properly confirm that fragmentation really is a problem, for example because read performance drops measurably when read-ahead becomes less effective, or because the system is struggling to ...
Paul White's user avatar
  • 84.3k
6 votes
Accepted

Best way to configure and maintain a table with many insertions and deletions that's empy on average?

In my experience with SQL Server 2016 Enterprise Edition in a similar process where a permanent table is used as a message queue - so it has a very high rate of inserts and deletes - we found ...
Scott Hoffman's user avatar
6 votes
Accepted

How do I use the smallest amount database file space to rebuild an index on Mircosoft SQL Server Express due to the 10GB limit

The reason is that index rebuild means creating a new index under covers and then dropping the old index. I.e., while the operation is running you need twice the amount of storage available. If that ...
Tibor Karaszi's user avatar
6 votes

ALTER INDEX ALL did not fix the problem, but deleting and recreating a non-clustered index does the trick. Why?

REORGANIZE does a lot less than recreating the index. It does not change the data that is on each page, or update statistics, it just rearranges where the pages are stored. What you are looking for is ...
David Spillett's user avatar
5 votes
Accepted

Do we have to run auto update statistics after index rebuild?

You should not update statistics after you rebuilt the index as rebuilding it gives you up-to-date statistics. See Does rebuild index update statistics? by Jack Li for more info. You get updated ...
Tara Kizer's user avatar
  • 5,966
5 votes
Accepted

Index Maintenance for varbinary

If you have a table with 1.9 billion rows, I'm guessing it has a fairly low change rate as a percentage. Check how many rows you're actually inserting/updating on a daily basis - it's likely less than ...
Brent Ozar's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible