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.
125
questions
-1
votes
0answers
21 views
Indexing strategy for optional predicates
Let me ask a similar question i asked before.
I believe there is more scope of explanation on this one.
CREATE TABLE [dbo].[SalesOrder](
[Id] [uniqueidentifier] NOT NULL,
[PageId] [...
0
votes
1answer
47 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
80 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
76 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
102 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 ...
0
votes
1answer
29 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
60 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
40 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
103 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
179 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
78 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
396 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
66 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
247 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
63 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
325 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
116 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
81 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?
8
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
188 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
2k 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
921 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
2k 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
366 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
499 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
49 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
116 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
54 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
580 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
752 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
333 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
126 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
59 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
616 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
865 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 ...
7
votes
1answer
873 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
317 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
122 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
63 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
420 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
383 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
931 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 ...
