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.

Filter by
Sorted by
Tagged with
0
votes
1answer
26 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
52 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
35 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
86 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
159 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
66 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
391 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
61 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
198 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
60 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
292 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
94 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
76 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?
7
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
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
164 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
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 ...
3
votes
2answers
822 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
326 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
450 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
48 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
108 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
551 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
631 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
302 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
114 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
57 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
545 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
836 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
847 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
300 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
107 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
383 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
358 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
876 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
325 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
473 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
390 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
66 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 ...