Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

-1
votes
0answers
14 views

non cluster index store the cluster key instead of RID [duplicate]

I was reading a textbook which says: "Remember that the clustered index defines the actual physical ordering of the data rows on the disk. If a row is updated such that its cluster key changes, that ...
-1
votes
0answers
15 views

What will this nonclustered index's root and non-leaf level look like? [duplicate]

Let's say we have an Employee table with columns EmpID, FirstName...etc. and EmpID is the primary key. Say there is no clustered index, and we create a nonclustered index on FirstName. I know leaf ...
2
votes
2answers
78 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/...
2
votes
3answers
139 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
56 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 ...
22
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
387 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
56 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
151 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
58 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-...
10
votes
3answers
260 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
73 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
66 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?
6
votes
1answer
1k 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
53 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
35 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
144 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 ...
0
votes
0answers
19 views

Does rebuilding and reorganizing indexes yield less fruitful results on tables that don't have clustered indexes?

I understand the operations of rebuilding and reorganizing indexes at a high level, but was wondering what they are actually doing on a table that doesn't have a clustered index (i.e. a table stored ...
2
votes
2answers
1k 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 ...
3
votes
2answers
728 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 ...
1
vote
1answer
1k 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
275 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
375 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
47 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
100 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
51 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
46 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
518 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 ...
2
votes
2answers
521 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
282 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
103 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
2k 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
55 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
496 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
800 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
3k 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 ...
7
votes
1answer
804 views

Any reason to have both an index with included columns and one without?

I am looking through a few slow running queries on a database that has under gone significant development (under a number of different developers) over the last couple years. One of the most commonly ...
1
vote
0answers
280 views

Using a non clustered datetime index to partition large transaction table

I have a table "usage" that records calls. The table already has > 250 million records and is growing fast so I am moving the table into a new partitioned table in SQL Server to improve query ...
0
votes
2answers
99 views

SQL server 2012 reports “Missing Index” on column which is covered by “Clustered Index”

My table creation script is as follows SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[quotation_section]( [quotation_id] [varchar](24) NOT NULL, ...
5
votes
1answer
61 views

Drawbacks to add clustered keys

I have a database that has multiple tables without any clustered index. All that I have found are small (< 500 rows). Specifically, PRIMARY KEY NONCLUSTERED with no other indexes at all. At a very ...
3
votes
1answer
359 views

Online index operation blocking inserts

I am trying to create a non clustered index on a table which is around 500GB. The table already has a clustered index on it. Since we do not want other transaction to be blocked we tried using ONLINE=...
0
votes
2answers
330 views

Does a unique non-clustered index help with locks?

I was wondering if SQL Server behaves differently with a unique non-clustered index versus a non-unique clustered index when it comes to KEY locks.
6
votes
1answer
810 views

Temporary Tables With Nonclustered Indexes Including All Columns

Background: Once per day several temporary tables that calculate a variety of different aggregated values are created. They all contain the same unique identifier (the field I would make a PRIMARY KEY ...
2
votes
1answer
297 views

Creating additional indexes on a table with a columnstore clustered index

According to this article, a table with a clustered columnstore index can have one or more additional non-clustered indexes. However, when I try to add a non-clustered index on a table with a CCI, I ...
0
votes
1answer
448 views

Index and Heap table storage on disk

I have a few questions on tables and Indexed tables and its storage, Here is my first image where i have taken an example with Northwind database and Table name is Orders. Orders Table is with 9 ...
0
votes
1answer
387 views

Aggressive index tuning

Background info - I have a table that stores Job information with a unique JobID (PK) and a ClientID (one client can have many jobs), as well as bunch of columns that are heaped data. I have ran ...
0
votes
0answers
65 views

Helpful nonclustered index improved the query but raised logical reads

I attempted to improve the following query that is getting executed continuously: SELECT TOP(?) Schema1.Object1.Column1, Schema1.Object1.Column2, Schema1.Object1.Column3 FROM Schema1.Object1 ...
14
votes
2answers
1k views

Why is my index not being used in a SELECT TOP?

Here's the run-down: I'm doing a select query. Every column in the WHERE and ORDER BY clauses are in a single non-clustered index IX_MachineryId_DateRecorded, either as part of the key, or as INCLUDE ...