5
votes
2answers
105 views

SQL Server: Create New GUID Values for Existing Index

I have an existing table with about 6 million rows. The relevant columns are: ID int not null PK Key uniqueidentifier not null Read/write on this table by Key lookup is probably something like 100 ...
2
votes
1answer
129 views

How to get rid of clustered index scan?

I have a query that was generated by C#/Linq-To-Entities code. As with this kind of query, it looks seriously ugly and I doubt it would help to include it here. But I have run it through a query ...
2
votes
1answer
92 views

Is it advisable to add indexes to a data warehouse's staging area?

We have a data warehouse built in MSSQL 2012, Staging area, EDW, cubes and reports. In the staging area, the tables are a 1:1 copy of those supporting the systems used to fill the DWH with. The ...
5
votes
2answers
141 views

Any advantage of having a second index with included columns on a primary key?

I'm reviewing our database and noticed that on a particular table we have the following indexes: Table: Col1 INT IDENTITY(1,1) Primary Key Col2 INT ... about 15 more columns .... ColN ...
1
vote
0answers
61 views

How to take advantage of multiple spatial indices?

All, I'm building a view which uses a spatial intersect (STIntersect in MSSQL 2012) between points (student addresses with address information) and polygons (school boundary polygons with school ...
1
vote
1answer
113 views

Is indexing binary?

The scenario: When a user updates some information - the correct row must be found and updated. I'm debating between two options: Let the server search through all of the rows (-slow). But be ...
3
votes
4answers
569 views

indexing on multiple nvarchar(max) columns

I have multiple nvarchar(max) columns in my DB named ShipperName,ConsigneeName, ProdDesc,BillOFLading. What I need to do is to have UNIQUE INDEX for the combination of these columns. As I am failed ...
4
votes
0answers
98 views

Rebuild Index Task removed WHERE clause from filtered index [closed]

I have a filter index setup on a table as follows: CREATE UNIQUE NONCLUSTERED INDEX [IX_TableName_Column_Filtered] ON [Schema].[TableName] ( [Column] ASC ) WHERE ([Column] IS NOT NULL) WITH ...
3
votes
0answers
135 views

SQL Server 2012 Error when creating Selective XML Index on XML Query Plan

I'm trying to create a database solution that will enable me to optimise high CPU/IO/Duration queries to improve overall performance of the database. Part of the optimisation requires persisting ...
2
votes
2answers
95 views

fragmentation for non cluster index growth very fast to 30

i have table with about 7 milion records.index fragmentation was about 90 . i try rebuild index for all index. and it works fine but after nightly job fragmentation for one of none cluster index ...
0
votes
1answer
79 views

Which index types do not support online index rebuilds?

In SQL Server 2012 Enterprise Ed., which index types do not support online index rebuilds? and why? and which activity-operations can prevent online index rebuilds?
1
vote
0answers
19 views

Why is SQL Server Recommending I Create this Index? [duplicate]

I have the following query: Select SubId, Max(ReadTimeLocal) From dbo.PanelWorkflow Where ProcessNumber IN ...
2
votes
1answer
234 views

Error when creating an index in SQL Server 2012

I'm trying to create the following index on our test environment (SQL Server 2012): CREATE NONCLUSTERED INDEX [iTransaction-OriginatingTransaction] ON [dbo].[Transaction] ( ...
8
votes
2answers
2k views

Does a re-index update statistics?

I've been doing the MS10775A course this past week and one question that came up that the trainer couldn't answer reliably is: Does a re-index update the statistics? We found discussions online ...
5
votes
2answers
150 views

Indexing strategy for dynamic predicate

Assume SQL Server 2012 Standard edition. My database has a table with 500 million rows. The table has about a dozen columns, none of which are very wide (some varchar(100)'s and some ints). The ...
3
votes
0answers
118 views

custom population of full text index

Is there any way to populate a full text search index without storing the backing information in the database? I don't mean using remote-blob-storage or FILESTREAM types. The backing information is a ...
1
vote
2answers
1k views

SQL Server clustered index high fragmentation

I've got a Table with an Integer (4bytes) as primary key. it's defined as an identity. it is also the clustered index. Inserts are working perfectly fine. After inserting 2000 rows the fragmentation ...
3
votes
1answer
1k views

Why does CREATE INDEX … WITH ONLINE=ON block access to the table over a period of minutes?

I have an existing table: CREATE TABLE dbo.ProofDetails ( ProofDetailsID int NOT NULL CONSTRAINT PK_ProofDetails PRIMARY KEY CLUSTERED IDENTITY(1,1), ProofID int NULL, ...
3
votes
2answers
1k views

Suspended in index rebuild

In SQL Server 2012, an Index Rebuild job is taking a very long time (up to 8 hours). However, not even one Index rebuild completed, so I stopped the index job. In monitoring SQL task: state = ...
0
votes
3answers
2k views

SQL Server 2012, rebuild not lowering avg fragmentation

I have a script identifying what indices to rebuild. select 'alter index ' + name + ' on ' + @dbname + '.dbo.' + OBJECT_NAME(a.object_id) + ' rebuild;' from sys.dm_db_index_physical_stats ...
1
vote
0answers
200 views

Best Practice to update columnstore indexed tables

I have a table with aprox. 22 Bn rows, for each month I have made one partition that and each partition has two data files. I created the columnstore index on this table and the performance ...
6
votes
1answer
132 views

Ok to drop indexes on FK's if they have no stats in the DMVS

I have used the well known query below from Kevin Kline to check for unused indexes. Several indexes created on Foreign keys returns no read stats, only writes. Are you 100% safe to drop these ...
1
vote
1answer
972 views

SQL Server Full-Text Indexer with stoplists/stopwords

As I'm working on a graduate project (Textmining with SQL Server 2012 Semantic Search) I run into a situation where I need to post a question on this website, hoping someone can help me. This ...
2
votes
1answer
247 views

Why 2 allocation units during online index rebuild to different file group?

I'm in the process of rebuilding the clustered index of a big table to a different file group. I'm monitoring the rebuild process using sys.system_internals_allocation_units. I'm 3h in but not done ...
3
votes
1answer
1k views

Move Primary Key to Filegroup (SQL Server 2012)

How can I move a clustered primary key to a new filegroup? I already have found a possible "algorithm" but it is horribly inefficient: Drop non-clustered indexed (requires them to be resorted and ...
2
votes
2answers
244 views

SQL Server and Over-Indexing Tables [duplicate]

Possible Duplicate: Where can I find some guidance on index strategies? I remember taking a course on SQL Server 2000, and the instructor said that you have to be careful of adding too many ...
3
votes
2answers
692 views

SQL Server 2012 - Frequent SELECT and UPDATE on bit column. Index doesn't help

I have a SQL Server table with about 2 million rows in, this table stores short textual documents. Here's my schema: CREATE TABLE Documents ( documentId bigint IDENTITY NOT NULL, content ...
11
votes
1answer
1k views

What's the anatomy of a columnstore index?

One of the new features in SQL Server 2012 codenamed Denali is the Columnstore index. I know a good bit about regular old row-store indexes, like the b-tree structure, differences in storage between ...