Tagged Questions

The process of determining which indexes are useful and which are not.

learn more… | top users | synonyms

6
votes
2answers
658 views

Adding a Clustered index to a HEAP table on someone else's app

We're using a proprietary application based on SQL Server 2005, which has many HEAP based tables (that is, no Clustered index). Over the years, these tables have grown badly fragmented (e.g. 99% ...
4
votes
2answers
535 views

Execution plan results for sp_executesql suggests adding missing index for already existing index

I'm analyzing a query being run inside our system via sp_executesql. I am able to execute the query on the production database and include the actual execution plan. In the execution plan results ...
18
votes
2answers
4k views

Is a composite index also good for queries on the first field?

Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index? ...
7
votes
1answer
1k views

What factors go into an Indexed View's Clustered Index being selected?

Breifly What factors go into they query optimizer's selection of an indexed view's index? For me, indexed views seem to defy what I understand about how the Optimizer picks indexes. I've seen this ...
2
votes
1answer
116 views

Does updating a partially indexed field add or remove it from an index in Postgres

We can create a partial index for a field in Postgres. I am looking at using this to stop rows which are archived from being in an index on a created_at field: First we create the index: test=# ...
3
votes
2answers
133 views

index maintenance strategy if few insertion points relative to # of rows

In SQL Server 2008 R2, I have a nonclustered covering index on multiple tables with 100M+ rows. The table has a few thousand "insertion points" where all new inserts happen. This means that regardless ...
1
vote
1answer
186 views

What would be a optimal index for this query in mysql

I'm trying to optimize this query. SELECT * FROM sales_reps WHERE account_id = 1 AND (state = 'enabled') AND (clients >= 5 OR revenue >= 10000) ORDER BY name, platform, client_group, ...
8
votes
2answers
1k views

PostgreSQL Index Caching

I'm having difficulty finding 'lay' explanations of how indexes are cached in PostgreSQL, so I'd like a reality check on any or all of these assumptions: PostgreSQL indexes, like rows, live on disk ...
1
vote
0answers
548 views

SQL Server Database Engine Tuning Advisor vs DMV queries

What is the best way to tune the indexes on my production database? I normally use the following query : SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage], ...
3
votes
3answers
168 views

What is a good guideline for sampling for statistics updates

Scope: A billion record table that is indexed in 10 ways. Rate of change per day is 1/2 a percent. Some of the indexes are monotonously increasing (datetime/timestamp), yet the common queries most ...
5
votes
2answers
1k views

Viable strategies for rebuilding indexes online in SQL Server 2008 R2 Standard edition?

We use SQL Server 2008 R2 Standard edition (we can't afford Enterprise edition on all our production database servers) and have to periodically maintain the critical indexes (rebuilding/reorganizing) ...
4
votes
3answers
1k views

Lookup table for a one-to-many relationship

I have a tickets table with an id that I need to associate to a lookup table where the counterpart of that data is another id that is controlled from an outside source. tickets - id - sutff lookup - ...
1
vote
1answer
94 views

What is the best relationship model for high performance?

I want to model a relationship between Customer and Transaction. However, because Customer is only described by a unique string and no extra info, I have two possibilities to implement it in the ...
1
vote
1answer
191 views

INDEX Creation Advice Oracle

I have a table that has time data of 2 billion records obtained from the clients. The data has two main columns, date and entity_id. The attributes date, and entity_id would make a unique pair. ...
6
votes
2answers
3k views

How can I improve the speed of a query on a 20 million+ row table?

I have a query that is used for getting internet traffic statistics of certain IP addresses. There are separate IP address fields for hosts and blocks of IPs called assignments. The data is stored ...
7
votes
2answers
870 views

Filtering data ordered by rowversion

I have a SQL table of data with the following structure : CREATE TABLE Data( Id uniqueidentifier NOT NULL, Date datetime NOT NULL, Value decimal(20, 10) NULL, RV timestamp NOT NULL, ...
2
votes
1answer
251 views

Oracle function based index is not used in query

I cannot figure out why the following is not working as I expect it : CREATE TABLE table1(table1_id INT NOT NULL PRIMARY KEY, col1 INT NOT NULL, val1 VARCHAR(50)); CREATE INDEX ...
3
votes
1answer
124 views

Where to place non clustered index

Execution plan clearly showing that one of my table used in query is useing Clustered Index Scan. From this node, how can i guess that which columns should be part of my non clustered index key and ...
4
votes
1answer
370 views

Using indexes to create multiple relationships to a table in order to enforce data integrity and add meaning

I'm working on an inventory database that uses supertype/subtype model. My question regards creating multiple relationships with the same tables using the primary key and composite indexes that also ...
4
votes
2answers
2k views

How I should fix the following update statement to make it run faster?

I have the following DML: UPDATE rc_usutb011 u SET cod_ant=( SELECT cod_hst FROM rc_pdptb101 p WHERE ocip_hst=ocip_ant AND p.sector=u.sector ); The explain plan ...
2
votes
2answers
515 views

Reorganize Indexes T-SQL

I'm moving a database to SQL Azure and with that I lose the maintenance plans so I am going to use a Worker Role to schedule them. When I open the properties of the Rebuild Index Task and click on ...
5
votes
3answers
2k views

SQL server indexing foreign keys, covering indexes included columns

I have pulled a list of FK's that do not have indexes on them. If a FK does not have a dedicated index on them but are part of wider indexes used for covering queries, Should they have a dedicated ...
4
votes
2answers
4k views

Primary key vs. unique index: performance difference with foreign keys?

Will there be any difference in performance if you compare a query fetching data via a join between two tables where there's a relationship against either of the following: A primary key A unique ...
2
votes
1answer
184 views

Indexing or changing one-to-many relationship

I have these two Microsoft SQL Server 2005 tables: ObjectLicenses (ObjectLicenseID [PK]; ObjectID; LicenseType; LicenseNumber) 1|1|A|000001 2|1|A|000002 3|1|B|000003 4|2|C|000004 Objects (ObjectID ...
2
votes
1answer
101 views

Are two indexes needed?

Our MySQL database will contain an encyclopedia. The encyclopedia will be shown in pages where every page contains entries starting from a letter. Which indexes should I use? Should I have two ...
3
votes
2answers
102 views

MySQL using a index referencing a different column

I have a large table which uses a versioning system in the table. When a record is updated a new record is inserted with the old data and the original record is updated ( so to maintain the same ...
0
votes
1answer
81 views

Can you search using an index while indexes are being updated in oracle11g?

I am using oracle 11g. I noticed some performance problems that causes hanging on some search operations. Can I still search based on an index while that index is being updated in the database?
0
votes
3answers
3k views

Will more indexes on a table affect performance? [duplicate]

Possible Duplicate: How do indices impact query performance? How to know when/if I have too many indexes? Say ‘X’ report using some "where" condition in the statement so we are creating ...
1
vote
1answer
1k views

Index Strategies on Text or NVARCHAR(MAX) Fields

I have the following query (simplified for question) I'm trying to speed up for a read only DB ... SELECT [sysid] ,[Date]=CONVERT(CHAR, DATEADD(D, [date], '1800-12-28'),101) ,[From]=[from_addr] ...
0
votes
1answer
150 views

Creating Indexes to optimize query

I have this query and I want to increase its performance by adding appropriate Indexes. DELETE FROM MYTAB1 WHERE MYID1 IN ( SELECT MYID2 FROM MYTAB2 ); I am not familiar with the ...
4
votes
2answers
499 views

Why is a secondary index chosen over a clustered index for SELECT COUNT(*) …?

In this query: select count(*) from largetable; a secondary index is chosen: mysql> explain select count(*) from largetable; ...
1
vote
1answer
311 views

Analyzing TCB Index Status for DB2

The Following is the TCB index that I could get using a command for my DB2 database. I don't know how to analyze it. Which columns are important? What do they mean? TCB Index Stats: Address ...
4
votes
3answers
222 views

Most efficient (or meaningful) way to index grid-based data

I have a MySQL table which I expect to be in surplus of a few million rows and 99% select statements. The problem I am having is coming up with a meaningful way to determine the Primary Key. (I have ...
6
votes
4answers
1k views

How will large index INCLUDE fields effect system performance?

This question is about SQL Server index performance with a varchar(2000) as an INCLUDE in a covering index. I’m trying to improve performance in an slow and unstable database application. In some ...
11
votes
1answer
4k views

Should I use many single field indexes, instead of specific multi column indexes?

This question is about the effectiveness of a SQL Server indexing technique. I think it is known as "index intersection". I'm working with an existing SQL Server (2008) application that has a number ...
5
votes
2answers
4k views

What is the difference between ALTER INDEX and DBCC DBREINDEX?

Is the only difference between ALTER INDEX [index_name] on [object_name] REBUILD with (ONLINE=OFF, FILLFACTOR=90) and DBCC DBREINDEX([dbname], 90) just that the DBCC command will reindex all ...
3
votes
1answer
182 views

Filtered index statistics refresh threshold

We have filtered indexes in our production environment. While doing some research about them, I came across this article "Filtered indexes and filtered stats might become seriously out-of-date" ...
2
votes
1answer
339 views

Is staggering reindexing jobs a good strategy?

I have inherited a server with 35 databases and a job that reindexes all tables on all databases (using sp_MSForEachTable). It's taking over 14 hours to complete and is now blocking other processes. ...
3
votes
1answer
2k views

Oracle not using index when > used in where clause

We have a relatively classic scenario where the date clause of the query generated by the application uses a "greater than" for a given date. Because there is no end clause to the date range, oracle ...
2
votes
1answer
295 views

In DB2, what are the alternative ways of evaluating an index's (or several indexes) benefit?

I'm trying to evaluate different approaches to analyzing the benefit of changing the set of indexes that are available. If I pick some representative queries from my workload, I can run EXPLAIN and ...
2
votes
2answers
914 views

How can an identity primary key index become fragmented?

From what I understand about index fragmentation, this should not be possible. The cases I have found in my databases are non-clustered. Example: ALTER TABLE [dbo].[ClaimLineInstitutional] ADD ...
8
votes
4answers
372 views

What are the performance considerations between using a broad PK vs a separate synthetic key and UQ?

I have several tables where records can be uniquely identified with several broad business fields. In the past, I've used these fields as a PK, with these benefits in mind: Simplicity; there are no ...
5
votes
1answer
530 views

SQL Server 2008 Maintenance Plan Failure

Instead of using my old school index rebuild script I had decided to use 2008's built-in Maintenance Plan to do a simple Rebuild Index task. The details are: Connection = Local server connection ...
0
votes
1answer
2k views

Reindexing Partitioned Table Indexes

Historic Reporting Server running SQL 2008 R2. I'm trying to tweak the fill factor. I have a job that records the fragmentation when the jobs runs in the early hours. For every table that had high ...
11
votes
2answers
4k views

How to know when/if I have too many indexes?

Running Microsoft SQL Server Profiler every now and then, it suggests me with a bunch of new indexes and statistics to create ("...97% estimated improvement..."). From my understanding every added ...
4
votes
3answers
352 views

Using DTA vs. evaluating DMVs?

Currently I am confronted with a production SQL Server database where someone had added almost all missing index proposals from DTA. Form How to determine if an Index is required or necessary I have ...