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.
29
questions with no upvoted or accepted answers
3
votes
0
answers
242
views
Why does removing a RID by creating a clustered index lose parallelism and slow down execution by 2X
If I remove a RID lookup by replacing 2 non-clustered indices (non unique) with 2 identical clustered indicies (non-unique) where there weren't any before, why does this query slow down by a factor ...
- 2,120
2
votes
0
answers
73
views
Clustered Index being used over non-clustered with same key
I have a table that has a clustered index (CI) on the field filename.
It also has a non-clustered index (NCI) - With the key column of filename and 5 other columns in the includes. (There are ...
- 29
2
votes
0
answers
691
views
How to estimate the nonclustered index size before its creation?
I created two scenarios and expected to see the same result but the results were noticeably different. I would like to know where I did wrong.
Scenario 1
Create table mytable1 with clustered index and ...
- 1,263
2
votes
0
answers
242
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
...
- 21
2
votes
0
answers
1k
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 ...
- 21
2
votes
0
answers
39
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 ...
- 133
2
votes
0
answers
59
views
SQL Server 2005 Datawarehouse DB - Integrity failure caused by NonClustered indexes
Last year I've been working with a data warehouse database (SQL Server 2005) loaded on a daily basis during the nights. Overall database performance is acceptable. However, there is a "known error" ...
- 121
2
votes
0
answers
316
views
Covering Index - Insert Performance
We are running some performance tuning on our database in SQL Server. The database in itself is pretty small with around 13 tables. But there are multi-level relationships between tables.
When I try ...
1
vote
0
answers
30
views
How to create Non-clustered Covering index on PK while creating a table
I am trying to create a table where PK will not be a clustered index. PK will be non clustered with INCLUDE clause to add cover.
CREATE TABLE [Ref].[User](
[UserRegisteredId] [varchar](100) NOT ...
- 21
1
vote
0
answers
58
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, ...
1
vote
0
answers
74
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
...
- 3,142
1
vote
0
answers
77
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 ...
- 56
1
vote
0
answers
432
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 ...
- 11
1
vote
0
answers
1k
views
Resolve "stuck" system process of dropping indexes
I am re-vamping some indexes, and have decided to drop 3 on a certain table. The process has been running for almost 2 hours now. The table does have quite a few million rows, but I still feel that is ...
- 607
1
vote
0
answers
386
views
Best Strategy to remove the unused or duplicate indexes
I have searched the Internet a lot about this but I'm still not comfortable making a decision.
One of our reporting databases is 350 GB (it acts as a subscriber for transaction replication where ...
- 2,169
0
votes
0
answers
112
views
why the index can take a long time to create on small table?
SQL Server 2019
Index was created with online = on.
Table has about 35000 rows with 7 columns with int, uniqueidentifier and datetime data types.
Table has only clustered index.
sp_whoisactive doesn't ...
- 1
0
votes
0
answers
82
views
Setting up an HTAP Environment
I've had success in the past with setting up a CIX/PK on an identity column and then setting up supporting NCIX and NCCsIX on tables to help support an HTAP environment.
I'm curious, going forward ...
0
votes
0
answers
273
views
Creating non clustered index in sql server in large table
Table is having 180 million records. Has a primary key(WExtractGrouping, Id, PEventId, CAId, TEventId, EventDate). No other indexes except clustered index on pk.
CREATE NONCLUSTERED INDEX [...
- 1
0
votes
2
answers
658
views
What is the best way to measure INDEX impact on production?
We are in process of creating nonclustered indexes on tables on QA environment on SQL Server 2016. Tables are the same in terms of structure (columns, clustered indexes) but tables on production have ...
- 45
0
votes
0
answers
60
views
Working with index-key sizes which are close to the DBMS's index-key size limit
I might need to work with relatively large (non-clustered) index keys (between 1KB to 4KB). I found that Spanner and DB2 support 8KB index-key size, MSSQL allows 1.7KB and Postgress around 2.6KB (1/3 ...
- 101
0
votes
0
answers
38
views
Searching text using LIKE, and Included Column
SELECT * FROM Table WHERE (Column1 = 'ED69K')
SELECT * FROM Table WHERE (Column1 LIKE '% ED69K%')
I have a wide table with 25 columns on the table. The above queries do need to do key lookup ...
- 1,049
0
votes
0
answers
245
views
Rebuild indexes makes switch partitions useless (SQL Server)
I have a big table containing multiple indicators per ticker. The table is partitioned by indicator. Each indicator has nearly 50 million rows and currently there are about 30 indicators and they will ...
- 101
0
votes
0
answers
185
views
SQL2005 Index exists in sys.indexes but not on table
I have a routine that runs through all the non-clustered indexes in a given database on SQL2005 (SP3), and reorganizes and rebuilds them (if fragmentation > 20%). This process is erroring out on ...
- 55
0
votes
0
answers
60
views
Non cluster indexing with combination or on single entity for multiple columns
I am new to db design I also read concepts of non cluster indexing and know about the combined non cluster indexing but in my scenario I am having user table with multiple search on column
table ...
- 133
0
votes
0
answers
212
views
Update query performance improvement (MS-SQL)
UPDATE M SET ARCHIVE_FLAG = 'N' FROM MARKPOSTED_TMP M WHERE
EXISTS (SELECT 1 FROM SHFMKPSTMM S INNER JOIN SHIFT_TMP S1
ON M.MARKID = S.MARKID AND
S.SHIFTID = S1.SHIFTID AND S1.ARCHIVE_FLAG <> '...
0
votes
0
answers
234
views
How to avoid duplicate in mysql INNODB transaction for non index key?
I have a INNODB table with txnno column without unique index but I have primary key on another column. Now I am inserting transaction from multiple places. And there are some concurrent insertion ...
- 41
0
votes
0
answers
60
views
MySQL InnoDB Weird Query Performance
I designed two tables, both using InnoDB.
The first one has columns "Offset, Size, and ColumnToBeIndexed" with BIGINT "Offset" being the primary key, and the second one has columns "OffsetAndSize, ...
- 121
0
votes
0
answers
75
views
Explaining about SQL Server query Execution Plans and understanding Clustered and Nonclustered Indexes
I'm Christine and I'm a newbie. I would like to know if someone could help me clarify what I'm drilling down this execution plan as below :
Here's query generated from SQL server that is accompanied ...
-2
votes
1
answer
122
views
Will index on a partition that doesn't include partition column work fast?
Let's say I have a huge table with columns [CloseOfBusinessDate], [DataSourceID], etc.
[CloseOfBusinessDate] is a partition column and I have a clustered index on that column (on ...
- 97