Questions tagged [nonclustered-index]
The nonclustered-index tag should be used for questions regarding how to implement and troubleshoot supplemental indexes used primarily to enhance performance. The term nonclustered is typically a SQL Server-specific term.
132
questions
0
votes
1answer
49 views
Should a filtered index theoretically perform faster in this example?
I have a SuperheroMovies table which has all superhero movies, and has a column called "UniverseName" which has either the values "Marvel", "DC", "Capcom", "Unknown".
It also has columns called "...
1
vote
2answers
97 views
How to compare write speed before and after adding index?
I am planning to add a non-clustered index (composite) to my table with around 5 million records and 7 columns. Table has only clustered index on id column. The first thing I am going to do is test on ...
0
votes
1answer
43 views
Offline index rebuild - does it also create a whole new copy of the index?
As I know, while online index is rebuilding, SQL Server virtually creates an index and then at the end it swaps the existing old index with the new created one and then finally drops the old index.
...
-1
votes
1answer
42 views
Index Update with Eager Spool and Sort operators in Execution Plan
After an UPDATE statement on a large table, the execution plan shows updates of indexes (all non-clustered) that include the updated columns.
Before each index update, there is an Eager Spool ...
3
votes
1answer
76 views
Do I need to rebuild heap table after rebuilding a non-clustered index?
Assume that I have a heap table with a higher fragmentation percentage and that table also has a non-clustered index with higher fragmentation too. I can't reproduce this case right now but maybe ...
0
votes
2answers
57 views
non clustered index coverage with INCLUDED columns
If I have two indexes:
IDX_1 = (COL1), (COL2) DESC
IDX_2 = (COL1) INCLUDE (COL3)
will a single defined below cover both queries? does the Descending keyword have any bearing on the usage of INCLUDE ...
4
votes
1answer
75 views
Maximizing throughput on bulk data processing
I'm using SQL Serverversion 14.0.3035.2
I have a pile of information that I need to process from table A and insert the modified data into table B. I have written an application to query the data, do ...
2
votes
1answer
47 views
Automatic seek on an index on a calculated column
I have a table with a varchar field than I need to be unique, but it is too long for the nonclustered index that the uniqueness constraint requires, so I have added a computed column (either using ...
0
votes
1answer
51 views
Column as an Included column or as a key column
I have a query which has an optional where condition on column classID(not unique).
This same classID is being displayed as a select column in various other queries.
Currently i have classID as an ...
1
vote
1answer
85 views
Do I need to update an index include columns every time I add a new column
I work on a team where we have millions of records of data, but not 100s of millions anything crazy.
We have had recent DB issues where we have hung connections due to Page Latch IOs, where the DB is ...
4
votes
1answer
81 views
How does SQL Server know what index to use?
I have this non clustered index
CREATE NONCLUSTERED INDEX [nci_wi_StoreVariantInventory_F09B4BE817BC953FAC6861B7E46F6F51] ON [dbo].[StoreVariantInventory]
(
[ProductId] ASC,
[StoreId] ASC,
...
1
vote
1answer
106 views
Adding a clustered index to a heap table
I've noticed that we have a few heap tables on our database, they all have a non clustered index on the primary key (which is an Int). The biggest table has over 2 million rows, a forwarded record ...
1
vote
1answer
39 views
Why is DTA recommending non-clustered indexes that match the clustered Primary Key index?
I'm only vaguely familiar with SQL Server's Database Engine Tuning Advisor (DTA) but I'm using it to suggest recommendations for indexes on my database.
Most of the tables in the database at present ...
2
votes
0answers
67 views
View with where clause slower than query with same where clause
This query
with periods (PeriodStart,PeriodEnd,ticker,FundamentalId,dimension) as
(select b.reportdate PeriodStart, MIN(a.reportdate) PeriodEnd,b.ticker, b.FundamentalId,b.dimension
from Base a
...
1
vote
1answer
52 views
Understanding the recreation of index in Table partitioning
I need your help. I have[AdventureWorks2014] database and I wanted to perform partitioning on [Production].[TransactionHistoryArchive] table based on [TransactionDate] column. [Production].[...
2
votes
2answers
218 views
Moving all Heap Tables to another Filegroup in SQL Server
In SQL Server 2017, I want to move ALL Heaps in a DB to a secondary filegroup. What's the easiest way to do this?
This question is only for Nonclustered Indexes.
https://stackoverflow.com/questions/...
3
votes
3answers
300 views
Why does sql server prefer the nonclustered index over the clustered index?
I am trying to speed up a table and as I was experimenting I ran into this (what I think is) odd occurrence. I created a clustered index and a nonclustered index that should be the same thing. However,...
1
vote
1answer
95 views
SQL Server: Index and Include Columns
Assume that I have a table Foo with columns A, B, C, D, and E. All are integers. Let's assume a million records, and that we're on SQL Server 2017.
The following index exists:
create Nonclustered ...
23
votes
1answer
1k views
Why Do My Nonclustered Indexes Use More Space When I Delete Rows?
I have a large table with 7.5 billion rows and 5 indexes.
When I delete roughly 10 million rows, I notice that the nonclustered indexes seem to increase the number of pages they're stored on.
I ...
7
votes
1answer
402 views
Index help for bad Query on fairly big table (2m)
I am looking for some assistance please. I have a query that is running against a fairly big table (2 million records).
I have been trying to get the indexes working efficiently. There are a few ...
0
votes
1answer
81 views
SQL Server indexes Work Sometimes [duplicate]
So I have the following indexes on my table:
CREATE NONCLUSTERED INDEX [$5] ON [dbo].[Record Link]
(
[Company] ASC,
[Record ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ...
2
votes
0answers
330 views
Default filegroup, specified in the SQL Server CREATE INDEX statement, is ignored (ON [DEFAULT] storage clause)
Am I missing something?
Documentation clearly states, that:
ON "default" Applies to: SQL Server 2008 through SQL Server 2017 and
Azure SQL Database.
Creates the specified index on the ...
-2
votes
2answers
64 views
Can a non-clustered index be transformed to a covering index in SQL Server?
I have a non-clustered index which includes one column. Now I want to alter the index and add additional two columns as covering columns. Is it possible? If yes, then can you, please, provide the T-...
11
votes
3answers
362 views
Why would optimiser choose Clustered Index + Sort instead of Non-Clustered Index?
Given the next example:
IF OBJECT_ID('dbo.my_table') IS NOT NULL
DROP TABLE [dbo].[my_table];
GO
CREATE TABLE [dbo].[my_table]
(
[id] int IDENTITY (1,1) NOT NULL PRIMARY KEY,
[foo] ...
1
vote
1answer
198 views
Clustered Index Scan on 5+ billion rows instead of using non clusterd index for <1000 rows
We have a huge SQL 2017 database containing more than 5 billion internetpages. One of the table contains the info about the page: PageID is the primary clustered key and UriKey is the non clustered ...
0
votes
1answer
93 views
use DBCC SHRINKDATABASE after rebuild / reindex [duplicate]
I have little free disk space.
Why shouldn't you use DBCC SHRINKDATABASE (DBName, TRUNCATEONLY) after every rebuild/reindex operation?
9
votes
1answer
2k views
“Warnings: Operation caused residual I/O” versus key lookups
I've seen this warning in SQL Server 2017 execution plans:
Warnings: Operation caused residual IO [sic]. The actual number of rows read was (3,321,318), but the number of rows returned was 40.
...
1
vote
0answers
55 views
SQL Server Execution Plan
In the following queries the Student table has Clustered Index on StudentID column, and the Enrollment Table also has a Clustered Index on EnrollmentID as well as Non-Clustered index on (SessionID, ...
2
votes
0answers
36 views
To cluster or not to cluster? That is the question!
I have a table with just one (nvarchar) column for tracking IDs that have been used, to ensure they aren’t duplicated. Should I use Clustered or Non-clustered Index for this? The only two operations ...
0
votes
1answer
216 views
Does the Fill Factor setting affect heaps / heap tables with non-clustered indexes?
I came across this old article on Brent Ozar's blog (written by Kendra Little) regarding Fill Factor: https://www.brentozar.com/archive/2013/04/five-things-about-fillfactor/
In it, she briefly ...
2
votes
2answers
3k views
Performance differences between RID Lookup vs Key Lookup?
Are there any performance differences between when a non-clustered index uses the clustered index's key to locate the row vs when that table doesn't have a clustered index and the non-clustered index ...
4
votes
2answers
1k views
Clustered vs Non Clustered Indexes for a Primary Key
I have a large table with around 19 million records in it.
The primary key is a uniqueidentifier column called ID and there is a nonclustered index on this column. An application generates these ...
2
votes
1answer
3k views
Non-Clustered Index takes much longer to create than clustered Index
I have a procedure that creates a clustered and a non-clustered index on a table in order to do a partition swap. The problem I have is the clustered index takes about 2 minutes to create:
CREATE ...
1
vote
1answer
423 views
Non clustered index leaf level
When creating a non-clustered index, the leaf level will contain a reference to rows in the original table:
if the table has clustered index, the leaf level will contain the clustered index key;
if ...
2
votes
2answers
545 views
How to Limit DTU Percentage Consumption for a Given Session in Azure SQL Database?
We have an Azure SQL Database tier S4 (200 DTU), and there is one big table inside
When I run a "alter index rebuild..." statement, it takes about 18 mins to complete
This particular nonclustered ...
0
votes
2answers
51 views
Change of index key columns order affects performance
I have the following table in my database:
create table [dbo].[tb_StatusLog](
[RequestID] [int] not null,
[statusID] [int] not null,
[startTime] [datetime] not null,
[endTime] [datetime] null,
)
...
0
votes
3answers
118 views
what columns to include in the index to improve the performance of the following update?
I have the following update:
UPDATE dbo.fees
SET amount = @amount,
currencyCode = @currencyCode,
feeType = @feeType,
countryCode = @countryCode,
gatewayCode = @gatewayCode, ...
9
votes
3answers
1k views
Does a non-clustered index make any guarantees about row order?
I have a developer that would like, when doing a select statement with no order by, the rows in a table to be in the order they were inserted. The developer suggested changing from a clustered to non-...
1
vote
0answers
55 views
What queries read my indexes and what queries apply inserts / updates to my indexes?
I'm looking indexes with 0 reads with SP_blitzindex, from there I am able to grab the indexes names that are probably not helping my system.
Using the top query posted by Kendra:
SELECT
...
1
vote
2answers
51 views
Does an index continue to be used after it is dropped (Database Polling investigation)
The folly of developers who use database polling is shown below.
I have identified a query below which did not have an index in place on a particular column
In addition to polling the database every ...
0
votes
1answer
26 views
Private access for a SP working on a table
I have a Stored Procedure that is dropping, recreating and then applying keys and indexes to a table, but the process gets interrupted by other database tasks and then fails to apply the require keys ...
2
votes
2answers
613 views
Would a nonclustered index on the primary key speed up deletes and prevent deadlocks?
I have a very high traffic database. In the application they will issue deletes, and frequently these deletes will deadlock with other deletes on the same table. I am researching on ways to remedy ...
3
votes
2answers
909 views
NonClustered Index not being Used
I have a table (created below) that contains 321 rows.
I expect the last query below to use the NonClustered Index and then a Key Lookup. However, instead it uses a Clustered Index scan. Only a ...
0
votes
1answer
373 views
Non clustered index on partitioned table
Using SQL Server 2012. I have a huge table that is partitioned. I need to create a non clustered index on it.
After I created the index, a job that somewhere switches partition fails because the ...
2
votes
1answer
157 views
Covering index in a partitioned table
I have a table with 20 millions of rows called [transaction].
That table is partitioned by transaction_date.
Can I create a non clustered index in that table even if it is partitioned?
Example:
...
2
votes
2answers
3k views
Creating non-clustered index on large table fills transaction logs
Using SQL Server 2016, I'm trying to create a non-clustered covering index on a table in my database that is of the structure int, bigint, int, varchar(20), varchar(4000). The size is ~13,241,928 rows....
1
vote
0answers
60 views
Any performance gains from having identical clustered and nonclustered indexes on table on different filegroups?
I am attempting to rearchitect our MSSQL database such that we have two filegroups, one for data, on a 15K HDD, primary, and one for indexes on a separate SSD, indexes, in order to realize some ...
5
votes
3answers
758 views
SQL Server 2016 not using index used in 2012
2016 plan here
2012 plan here
I have almost identical tables with very small difference in row count. One on 2012, and the other on 2016. Indexing is identical. These VMs are in exact same ...
7
votes
3answers
893 views
Reasonable way to predict query performance over time
I have a SQL Server 2012 database that I am using to save data from processed files. We read data from a folder, process it with python and save the results into the database.
One of the first things ...
5
votes
3answers
4k views
Stop SQL Server from ordering results? [duplicate]
I'm teaching a class in which I want to demonstrate to my students that queries are not guaranteed to return results in a specific order without an explicit ORDER BY. I've observed that SQL Server ...
