Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

Does offline index rebuild of clustered or non-clustered index block user's SELECT/UPDATE/INSERT/DELETE queries?

D
derk last edited by

I understand that index rebuild requires schema stability (Sch-S) lock which means it will block any query that tries to do the schema modification (Sch-M).

Does an offline index rebuild of clustered or non-clustered index also block user's SELECT/UPDATE/INSERT/DELETE queries?
Reply Quote 0
1 Reply Last reply
C
charlenef last edited by

I understand that index rebuild requires schema stability (Sch-S) lock which means it will block any query that tries to do the schema modification (Sch-M).

You seem to have this backwards. Any operation that changes system metadata requires a schema modification (sch-m) lock at some stage. Regular user queries require at least schema stability (sch-s) even when running under read uncommitted isolation. See https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide#schema in the documentation.

Does an offline index rebuild of clustered or non-clustered index also block user's SELECT/UPDATE/INSERT/DELETE queries?

The documentation is not as clear as it could be about this in all places, but https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql#online---on--off--as-applies-to-rebuild_index_option is relatively clear (emphasis added):

An offline index operation that creates, rebuilds, or drops a clustered, spatial, or XML index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table during the operation.
Reply Quote 0
1 Reply Last reply

2
Posts

0
Views

Suggested Topics

G

Does composite cluster index affect performance of seek/scan non-cluster index which is part of the composite?
SQL, Database Testing • sql server database design sql server 2008 index • • gionna

2

0
Votes

2
Posts

0
Views

J

First off, don't mix "primary key" and "clustered index". One is "how to identify a row", the other is "on-disk structure" Then, you don't need an index on Division by itself because it is the left hand column of the clustered index. However, it won't be covering (see links below) And for each point in turn: this table doesn't know that "Brcode is unique for all division". If it is, then brCode should be first and unique and the clustered index by itself a search on TrDate won't benefit from the clustered index it depends on selectivity, statistics, how many columns are required. It may seek with a key lookup or it may scan the clustered index (= a table scan) or scan/seek the index on TrDate Finally, I'd suggest some background reading: SQL Server Index Basics Using Covering Indexes to Improve Query Performance
performance of Replace vs If exists update else insert vs update if row_count()=0 insert
SQL, Database Testing • mysql update insert replac languagee • • emmalee

2

0
Votes

2
Posts

0
Views

C

Usually the answer is simply "which one sends more requests to the server". IODKU - 1 REPLACE - 1 EXISTS - 2 (SELECT, plus either UPDATE or INSERT) ROW_COUNT - 1 or 2 (ROW_COUNT does not count) But we have a tie with that metric. So, let's dissect the winners: IODKU - Find the row, decide what do, then do it. REPLACE - DELETE all rows that match on any UNIQUE (or PRIMARY) KEY, then INSERT. It feels like IODKU will be less work.
R

Insert results from query, update old existing row if query doesn't still return same value
SQL, Database Testing • postgresql upsert surrogate key • • Raziyah00

2

0
Votes

2
Posts

0
Views

Z

Something like this maybe? with daily_query as ( .... this is your current query ... ), upsert as ( -- covers first and third requirement insert into the_table (....) select ... from daily_query on conflict (pk_column) do nothing ) -- this sets those rows to in active that exist in the target table -- but are not in the initial query update the_table t set active = false where not exists (select * from daily_query q where q.pk_column = t.pk_column);
Identify File Growth Events
SQL, Database Testing • sql server datafile • • carriann

2

0
Votes

2
Posts

0
Views

T

You can get autogrowth events information from the default trace if it is enabled: select distinct ei.eventid, e.name from sys.fn_trace_geteventinfo(1) ei inner join sys.trace_events e on e.trace_event_id = ei.eventid where name like '%grow%'; You can see from this that the default trace does have the Data File Auto Grow and Log File Auto Grow events capture. To see if you have the default trace enabled on that instance, you can do the following: exec sp_configure 'default trace enabled'; go Note: this is an advanced configuration option, so show advanced options would have to get set to 1 to view this configuration option through sp_configure. Also, these two events will not be triggered if the file grew manually. Here is a quick sample query to get these events: select te.name as event_name, tr.DatabaseName, tr.FileName, tr.StartTime, tr.EndTime from sys.fn_trace_gettable('', 0) tr inner join sys.trace_events te on tr.EventClass = te.trace_event_id where tr.EventClass in (92, 93) order by EndTime; And you can get from the system function sys.fn_trace_getinfo: select * from sys.fn_trace_getinfo(1);
S

Optimize my code SQL Server (temporary cover)
Software Programming • sql server sql • • shizuka

2

0
Votes

2
Posts

0
Views

A

If you do not want to use temporary tables you can use a table type variable to insert data https://docs.microsoft.com/en-us/sql/t-sql/data-types/table-transact-sql?view=sql-server-2017 an example with variable table:declare @variableTabla as table ( tipo_doc varchar(25), fecha_rep datetime, region varchar(25), sy_id int, Fecha_A datetime ) declare @minId int select @minid = min(id) from Tabla1 where fecha_rep = '2019-01-01' group by sy_id, fecha_rep select 'Reporte' tipo_doc, fecha_rep as Fecha_R, region, sy_id, fecha_rep as Fecha_A into @variableTabla from Tabla1 where id id = @minid -- elimino el in ya que min obtiene un dato and sy_id is not null select * from @variableTabla Another alternative is to use https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017 that creates these data in memory and only exist in time of execution of the query (like a table type variable)an example with WITH Serious: ;with table_ as (select 1 as algo) select * from table _for a piece of your consultation, with:declare @minId int select @minid = min(id) from Tabla1 where fecha_rep = '2019-01-01' group by sy_id, fecha_rep ;with tusdatos as ( select 'Reporte' tipo_doc, fecha_rep as Fecha_R, region, sy_id, fecha_rep as Fecha_A from Tabla1 where id = @minid -- elimino el in ya que min obtiene un dato and sy_id is not null ) select * from tusDatos
M

How do I create a table with the field date format dd/MM/yyy?
Software Programming • sql server sql • • meriah

2

0
Votes

2
Posts

0
Views

Where you mean default format I understand that you refer to the format in which you are receiving the date in the queries. Internally SQL Server is responsible for making the encoding necessary to store the date, trying to modify these parameters personally consider it an error.What I think you should do is use the functions it provides to get the date in the correct format. These depend on the version you are using, then you have all the features available for each version: (Don't forget to select your version so you don't go crazy) https://msdn.microsoft.com/es-es/library/ms187928(v=sql.120).aspx In your case if you have the latest version of SQL Server you could use:SELECT CONVERT(char(10), GetDate(), 101) in this way you would get the date in the format you tell us.
Select over 2 select queries
SQL, Database Testing • select • • morde

2

0
Votes

2
Posts

0
Views

This is a simple UNION query: SELECT id, date FROM work WHERE id = 1 UNION ALL SELECT id, date FROM home WHERE id = 1 ORDER BY date; I've used the UNION ALL syntax which return all rows from both source tables. Leaving the ALL keyword out ensures rows that are duplicated in both tables are only returned a single time. I've added an ORDER BY clause at the end of the query to return the list in date ascending order, since your sample data seems to indicate that. See the Wikipedia entry for further details.
B

Join one-to-many field in a single result without concatenation
SQL, Database Testing • sql server query pivot • • baileigh

2

0
Votes

2
Posts

0
Views

A

One way you can do this is with a PIVOT. Here is the setup code: create table grades ( ID int, Mapped_Heading varchar(10), Source_Value varchar(10) ); create table students ( ID int, Name varchar(10), Age int ); insert grades values (1,'English', 'pass'), (1,'Math', 'pass'), (1,'History', 'fail'); insert students values (1, 'Bob', 12), (2, 'Harry', 12), (3, 'Emily', 14); Here is the PIVOT code: with temp as ( select s.*, g.Mapped_Heading, g.Source_Value from students s join grades g on g.ID = s.ID ) select * from ( select * from temp ) a pivot ( max(Source_Value) for Mapped_Heading in ([English],[Math],[History]) ) as b The output looks like this: +----+------+-----+---------+------+---------+ | ID | Name | Age | English | Math | History | +----+------+-----+---------+------+---------+ | 1 | Bob | 12 | pass | pass | fail | +----+------+-----+---------+------+---------+ db<>fiddle demo
T

select for update gives error on indexed column
SQL, Database Testing • mysql index select transac languagetion • • Trenton

2

0
Votes

2
Posts

0
Views

A

SELECT FOR UPDATE tends to do row level locking in a deep way we tend to take for granted: Locking the corresponding index entries as well. SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index. Here are three DBA Stack Exchanges questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions. Reasons for occasionally slow queries? Trouble deciphering a deadlock in an innodb status log Will these two queries result in a deadlock if executed in sequence? Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that SELECT queries can lock data at the index level. You also have special cases of SELECT where you can lock specific rows on demand. Look at this issue from another angle, "Are InnoDB deadlock exceptions possibly going to be thrown by SELECT?" The answer to that can be Yes under a certain condition: If just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.
S

Creating Non-Clustered Index on Non-Persisted Computed Column SQL Server
SQL, Database Testing • sql server sql server 2012 index c languageomputed c langu • • shizuka

2

0
Votes

2
Posts

0
Views

G

When SQL Server creates the index on the computed field, the computed field is written to disk at that time - but only on the 8K pages of that index. SQL Server can compute the InvoiceStatusID as it reads through the clustered index - there's no need to write that data to the clustered index. As you delete/update/insert rows in dbo.Invoice, the data in the indexes is kept up to date. (When InvoiceStatus changes, SQL Server knows to also update IX_Invoice.) The best way you can see this for yourself is to actually do it: create these objects, and execute updates that touch the InvoiceStatusID field. Post the execution plan (PasteThePlan.com is helpful for this) if you want help seeing where the index updates are happening.
C

Does it ever make sense to have a clustered and non clustered index for the same column
SQL, Database Testing • index index tuning • • courtlanda

2

0
Votes

2
Posts

0
Views

J

Before addressing the two questions, let me briefly define the difference between clustered & non-clustered indexes. The clustered index is organized by the key columns. It also includes every other column as part of the row structure (ie, it has the entire row). The nonclustered index is also organized by the key columns. It implicitly includes the clustering key columns (if the table is clustered), or a pointer to the row (if the table's a heap). If any INCLUDE columns are explicitly specified, they will also be included in the index structure. Does it ever make sense to keep both of these? There is a corner case where it makes sense to have a non-clustered index "duplicating" the clustered index. If you have a query that frequently scans the table, and ONLY makes use of the clustering key column, the query optimizer will prefer to use the non-clustered index. The non-clustered index does not contain the full row data, and thus it will take up less physical space. Because it takes up less physical space, SQL Server can scan the table with fewer IOs, and will make use of it for performance reasons. You can query sys.dm_db_index_usage_stats, you can see the number of user_seeks and user_scans on the two indexes. This will help you see how the two indexes are being used, and determine the usefulness of the two indexes. If I delete one which one should I delete? As a general rule, most tables should have a clustered index. (That's a whole other topic). Not knowing any details about your index usage or data access, I'd guess that if you were to drop one, you would drop the nonclustered index (but, that really is a guess based on what I know). Depending on the clustering key, the data, the data access patterns, etc., the most correct answer might be to drop the clustered index and create a different clustered index. You may want to read more on Effective Clustered Indexes.
C

How to identify the top disk space consuming tables by file?
SQL, Database Testing • sql server sql server 2014 filegroups • • chanisef

2

0
Votes

2
Posts

0
Views

N

You may go to the report --> standard report and select Disk Usage by Top Table. This will be give you the table name in descending order which is used from maximum on disk to minimum on disk. Alternatively, you may use the SQL below to get details: SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024 as Size FROM sys.dm_db_partition_stats, sys.objects WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id GROUP BY sys.objects.name ORDER BY Size DESC; GO Or this query: SELECT TOP 1000 a3.name AS SchemaName, a2.name AS TableName, a1.rows as Row_Count, (a1.reserved )* 8.0 / 1024 AS reserved_mb, a1.data * 8.0 / 1024 AS data_mb, (CASE WHEN (a1.used ) > a1.data THEN (a1.used ) - a1.data ELSE 0 END) * 8.0 / 1024 AS index_size_mb, (CASE WHEN (a1.reserved ) > a1.used THEN (a1.reserved ) - a1.used ELSE 0 END) * 8.0 / 1024 AS unused_mb FROM ( SELECT ps.object_id, SUM ( CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END ) AS [rows], SUM (ps.reserved_page_count) AS reserved, SUM (CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END ) AS data, SUM (ps.used_page_count) AS used FROM sys.dm_db_partition_stats ps GROUP BY ps.object_id ) AS a1 INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id ) INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id) WHERE a2.type <> N'S' and a2.type <> N'IT' order by a1.data desc Note: I have not tested these queries myself, confirming back whether it works or not would be appreciated. This has been asked on multiple forums and I have used answers from here. As far as files are concerned, you may need to list filegroups, if none are defined, all the allocations will take place in primary filegroup. Below query could help you finding physical location: SELECT OBJECT_NAME(i.id) AS [Table_Name] , i.indid , i.[name] AS [Index_Name] , i.groupid , f.name AS [File_Group] , d.physical_name AS [File_Name] , s.name AS [Data_Space] FROM sys.sysindexes i INNER JOIN sys.filegroups f ON f.data_space_id = i.groupid AND f.data_space_id = i.groupid INNER JOIN sys.database_files d ON f.data_space_id = d.data_space_id INNER JOIN sys.data_spaces s ON f.data_space_id = s.data_space_id WHERE OBJECTPROPERTY(i.id, 'IsUserTable') = 1 ORDER BY f.name, OBJECT_NAME(i.id), groupid Hope this helps.
Replication fails because of a non-existent foreign key
SQL, Database Testing • sql server sql server 2012 replication foreign key • • Bogopo

2

0
Votes

2
Posts

0
Views

A

Sorry for waking up this dead thread, but I hate it when questions remains inconclusive. Anyway, I had the same problem. Apparently SQL server replication saves foreign keys in "dbo.MSsavedforeignkeys", which is where I found my blocker FK. A quick delete dbo.MSsavedforeignkeys where constraint_name = N'FK_TableOnlyInDestDB_MyReplicatedTable' solved my problem. Of course, there may be more "best practice" ways of cleaning up after failed replications.
R

What if all the non clustered indexes on my table were filtered indexes?
SQL, Database Testing • sql server performance index sql server 2005 filtered index • • Raziyah00

2

0
Votes

2
Posts

0
Views

O

Significant gains? No, I shouldn't think so. SQL Server's indexes are B-Trees. These have a hierarchical nature. To read a row the server starts at the root node of the index then steps through one or more intermediate nodes before reading the row's values via the leaf node. (Details vary between clustered & non-clustered indexes, and heaps.) The speed-limiting factor is often the number of levels there are in the index and, hence, the number of pages (each index node is its own page) that have to be read. This, in turn is controlled by the fan-out, the number of lower-level pages a higher-level page can reference, which is itself determined by the total size of the key. Let's do some hand-wavy maths. Index IDX_SeasonItemID_Tier1_Tier2_ImageSizeBodenImageURL_INC_BodenDomainURL is defined as Column Definition Effective bytes Note ------------ ------------- --------------- ----------------------------------- SeasonItemID VARCHAR(5) 5 Use the full size Tier1 VARCHAR(10) 5 Assume 50% of defined size Tier2 VARCHAR(10) 5 Assume 50% of defined size ImageSize VARCHAR(10) 7 Minimum from given data ImageFormat VARCHAR(20) 10 Assume 50% of defined size BodenImageURL NVARCHAR(210) 210 Assume 50% of defined size, 2 bytes per character. ==== Total 242 bytes I'll ignore the included column for fan-out since it's only present in the leaf nodes. Since there's about 8060 usable bytes per page that gives roughly 8060 / 242 = 33 rows per page. So a one-level index can reference up to 33 rows, a two-level index can reference 33 * 33 = 1,089 rows, a three-level 35,937 rows, and a four-level 1,185,921 rows. Given your counts of 74,000 rows per picture size and 370,000 rows in total you'll need a four-level index1 for both filtered and non-filtered indexes. There will be no reduction in the number of pages referenced and so no improvement in run-time. These numbers are a lower limit as the included columns and the column of the clustered index will take further space in the leaf pages of the non-clustered index. Adding these indexes will introduce risks, however. One is that the optimizer can be finicky about when it uses them. Another is that bringing on a new size (a data change) will require five more indexes to be defined (a DDL change). This is unwelcome maintenance overhead but without performing it the new-sized images will, in effect, be un-indexed. Schema changes will be complicated by the additional objects. Index maintenance will be slightly more complicated, if scripted manually. The overhead of the upper level of the indexes will increase the DB size fractionally, and with it backup and restore times. 1For the value used by an actual index see sys.dm_db_index_physical_stats.index_depth (docs).
C

What is the benefit of two FK, when one of them can be deduced from another table?
SQL, Database Testing • sql server foreign key • • charlenef

2

0
Votes

2
Posts

0
Views

D

Category has one foreign key to Shop. The Product has two foreign keys: one to Category, another to Shop. There should be a single foreign key referencing two columns in categories. create table products ( ... primary key (shop_id, category_id, product_id), foreign key (shop_id, category_id) references categories (shop_id, category_id) ); Think about it this way. Conceptually, normalization starts with a single relation that contains all the attributes. shop_name category_name product_name Wibble Chain saws Stihl 350 Wibble Chain saws Poulan 3X Wibble Pole saws Black & Decker 14 foot electric Wibble Pole saws Corona 15 foot Thursby Pole saws Hitachi electric Thursby Pole saws Remington electric Thursby Chain saws Husqvarna 460 Thursby Chain saws Poulan 3x It should be clear that the only candidate key is {shop_name, category_name, product_name}. It's in 5NF. There's no redundant data. Replacing text with ID numbers won't improve that. A design that requires you to chase ID numbers through a hierarchy of tables is an anti-pattern. It's like building an IMS database in SQL. (IMS was a problem the relational model intended to solve.)

Does offline index rebuild of clustered or non-clustered index block user's SELECT/UPDATE/INSERT/DELETE queries?

Suggested Topics

Does composite cluster index affect performance of seek/scan non-cluster index which is part of the composite? SQL, Database Testing • sql server database design sql server 2008 index • • gionna

performance of Replace vs If exists update else insert vs update if row_count()=0 insert SQL, Database Testing • mysql update insert replac languagee • • emmalee

Insert results from query, update old existing row if query doesn't still return same value SQL, Database Testing • postgresql upsert surrogate key • • Raziyah00

Identify File Growth Events SQL, Database Testing • sql server datafile • • carriann

Optimize my code SQL Server (temporary cover) Software Programming • sql server sql • • shizuka

How do I create a table with the field date format dd/MM/yyy? Software Programming • sql server sql • • meriah

Select over 2 select queries SQL, Database Testing • select • • morde

Join one-to-many field in a single result without concatenation SQL, Database Testing • sql server query pivot • • baileigh

select for update gives error on indexed column SQL, Database Testing • mysql index select transac languagetion • • Trenton

Creating Non-Clustered Index on Non-Persisted Computed Column SQL Server SQL, Database Testing • sql server sql server 2012 index c languageomputed c langu • • shizuka

Does it ever make sense to have a clustered and non clustered index for the same column SQL, Database Testing • index index tuning • • courtlanda

How to identify the top disk space consuming tables by file? SQL, Database Testing • sql server sql server 2014 filegroups • • chanisef

Replication fails because of a non-existent foreign key SQL, Database Testing • sql server sql server 2012 replication foreign key • • Bogopo

What if all the non clustered indexes on my table were filtered indexes? SQL, Database Testing • sql server performance index sql server 2005 filtered index • • Raziyah00

What is the benefit of two FK, when one of them can be deduced from another table? SQL, Database Testing • sql server foreign key • • charlenef

Does composite cluster index affect performance of seek/scan non-cluster index which is part of the composite?
SQL, Database Testing • sql server database design sql server 2008 index • • gionna

performance of Replace vs If exists update else insert vs update if row_count()=0 insert
SQL, Database Testing • mysql update insert replac languagee • • emmalee

Insert results from query, update old existing row if query doesn't still return same value
SQL, Database Testing • postgresql upsert surrogate key • • Raziyah00

Identify File Growth Events
SQL, Database Testing • sql server datafile • • carriann

Optimize my code SQL Server (temporary cover)
Software Programming • sql server sql • • shizuka

How do I create a table with the field date format dd/MM/yyy?
Software Programming • sql server sql • • meriah

Select over 2 select queries
SQL, Database Testing • select • • morde

Join one-to-many field in a single result without concatenation
SQL, Database Testing • sql server query pivot • • baileigh

select for update gives error on indexed column
SQL, Database Testing • mysql index select transac languagetion • • Trenton

Creating Non-Clustered Index on Non-Persisted Computed Column SQL Server
SQL, Database Testing • sql server sql server 2012 index c languageomputed c langu • • shizuka

Does it ever make sense to have a clustered and non clustered index for the same column
SQL, Database Testing • index index tuning • • courtlanda

How to identify the top disk space consuming tables by file?
SQL, Database Testing • sql server sql server 2014 filegroups • • chanisef

Replication fails because of a non-existent foreign key
SQL, Database Testing • sql server sql server 2012 replication foreign key • • Bogopo

What if all the non clustered indexes on my table were filtered indexes?
SQL, Database Testing • sql server performance index sql server 2005 filtered index • • Raziyah00

What is the benefit of two FK, when one of them can be deduced from another table?
SQL, Database Testing • sql server foreign key • • charlenef