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 ...
