All Questions
Tagged with sql-server-2017 index
20 questions
-1
votes
1
answer
207
views
Identify which index's rebuild operation is causing the log file to grow
We have a database, in full recovery model, which goes under index rebuild maintenance once a week. There is an index which is causing the log file to grow. I have log file growth event alerts enabled,...
- 1,550
11
votes
1
answer
2k
views
If you disable a non-clustered index, are the statistics still used?
tl;dr - As the title states - If I disable a non-clustered index on a table, are the linked statistics for that index still used?
I'm aware there are many questions about "dropping vs disabling ...
- 213
3
votes
1
answer
3k
views
How can I improve query performance with filter on datetime column using an indexed view?
I have this table:
CREATE TABLE transactions
(
id NUMERIC(20, 0) NOT NULL PRIMARY KEY,
amount NUMERIC(18, 2) ...
- 179
1
vote
1
answer
737
views
Why is Index causing bad estimation and execution plan
SELECT ISNULL(SUM(CASE WHEN T4.f_in =12 THEN T4.f_money END),0) -
ISNULL(SUM(CASE WHEN T4.f_out=12 THEN T4.f_money END),0)
FROM [Credit] M WITH(NOLOCK)
INNER JOIN [Transaction] T4 WITH(...
- 195
0
votes
0
answers
40
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,063
0
votes
1
answer
75
views
Is there a way to tell optimizer not to use index for reads?
Of course I can just drop and recreate or disable and rebuild it.
But for large tables it can take time, and for critical tables it can cause issues.
Is there any "more sophisticated" way to ...
- 742
0
votes
1
answer
242
views
Why is this query on JSON values in SQL Server using an index scan instead of an index seek?
I have a table with the following schema:
CREATE TABLE [dbo].[Obj1Json](
[ObjectID] [int] IDENTITY(1,1) NOT NULL,
[PointerToSource] [nvarchar](255) NULL,
[CreateDate] [datetime2](7) NOT ...
- 113
1
vote
1
answer
163
views
Could database with compat level 100 support columnstore index?
I have an instance which runs on SQL Server 2017 Developer Edition instance. I restored database ContosoRetailDW which had compatibility level 100. After restore operation, I run the following query:
...
- 1,303
3
votes
1
answer
220
views
VARCHAR missing index not showing up in missing index dmv or query plan suggestions
I have a table with 307200 records, with f_column being VARCHAR(30). No missing index are shown in the missing indexes dmv or suggested in the execution plan.
SELECT f_column, f_column2 FROM t_table ...
- 195
3
votes
1
answer
204
views
Can you create a primary xml index in the CREATE TABLE statement (SQL Server)?
I'm creating a temporary table and would like to create a primary and secondary XML index on a contained xml column within the CREATE TABLE statement since that is the only way I can create an index ...
- 263
1
vote
1
answer
1k
views
Composite index performance with varchar columns
I have large fact tables with composite non-clustered indexes, something like this:
NONCLSUTERED INDEX (OrderDate,OrderType,ClientKey,ItemKey,CustomerKey)
The columns ending with "Key" are varchar(...
- 265
4
votes
1
answer
170
views
How to recreate an Index with the same settings?
I'm in a situation where I want to add another included column to an existing index, but keep the rest of the settings (eg. FILLFACTOR, ONLINE, SORT_IN_TEMPDB, etc.) the same.
I do not have the ...
- 5,512
7
votes
2
answers
554
views
Force SQL Server to use fragmented indexes?
I have a quite large table (>10M rows) with frequent crud operations. It has proper indexes, but they get fragmented quickly. Without a periodic index reorganize/rebuild maintenance plan, the index ...
- 173
16
votes
5
answers
3k
views
Attempts to reclaim unused space causes the used space to increase significantly in SQL Server
I have a table in a production database that has a size of 525 GB, of which 383 GB is unused:
I'd like to reclaim some of this space, but, before messing with the production DB, I'm testing some ...
- 193
4
votes
2
answers
1k
views
SQL Server Index and Statistics Maintenance performance question
I tried to use IndexOptimize stored procedure on a database with a very high number of indexes (> 250.000). The initial step where stored procedure collects data what needs to be processed takes hours,...
1
vote
1
answer
170
views
Indexing for a table with many large subsets
In SqlServer 2017 we have a table with millions of rows. The table has a few dozen columns. One of the columns is a varchar(50) with a well defined range of allowed values, the value of this column is ...
- 167
7
votes
1
answer
700
views
Why does adding a computed column prevent predicate pushdown?
I have a weird situation that I don't quite understand.
I have a table like this kinda:
CREATE TABLE dbo.cc_demo
(
id INT IDENTITY PRIMARY KEY,
up_action INT,
...
- 217
2
votes
2
answers
1k
views
Cancelling the rebuilding of a partition with compression
Trying to enable compression on a large table in production but we are now out of time (been running for 6.5 hours already).
What is the impact of cancelling the operation and letting it roll back?
...
- 574
2
votes
2
answers
2k
views
Is it bad to shrink + reindex after removing a significant part of data?
I have a test environment running on SQL Server 2017 Express that uses only recent data (a job removes data older than three months once per month).
The job does the following:
Using a while loop, ...
- 1,171
0
votes
2
answers
1k
views
Best option to index an application log table for quick insertion and retrieval in date order?
Suppose I have an application trace log table with three columns: (id uniqueidentifier, message nvarchar(max), and started datetime2).
The primary key (clustered) is on [id]. I need to be able to ...
- 615