Tag Info

New answers tagged

4

There might be hardware issues, too - how should we know? But there are certainly issues with the query. First of all, remove DISTINCT from your VIEW definition. It's doing nothing at all (but complicating and slowing things down). Related answer on SO with explanation: PostgreSQL - Slow query joining on a VIEW Arriving at this (cleaned up) query: ...


1

I'm guessing your << is not utilizing an index, because your primary key would create a btree index, and what you need to make that particular operation indexable is a gist index. (use explain analyze to confirm). if that is the case that index not being used, Try doing something like CREATE INDEX idx_network_info_network_gist ON ...


1

If you ONLY want to optimise for this query. This is the best index: ALTER TABLE items ADD INDEX (category, created_at, user_id) This optimises the value of the filters, which reduces the total amount of data you touch. By adding user_id, item_id at the end of the query, you make the index covering and it saves you a lookup into the primary index. We can ...


3

The index IS covering for the second query. However, it is not USEFUL to support the seek. Because neither bar_id nor date_sent is leading the index, the optimiser cannot seek for it. What you have achieved with the covering index is to make table scan faster for the second query. But you have not supported a better seek strategy. This covering index would ...


0

Index usage depends on the singular query that is being executed. The only way to know their usage would be to do an EXPLAIN PLAN on each singular query that can be sent to the database. Moreover, a single query could use different indexes (or no index at all) by just changing a condition in the WHERE clause. Some RDBMS allow the logging of queries, that ...


0

If all your filters are equality matches (such as =, rather than >, LIKE, !=, etc), then it's going to useful to list them first. Then the data that matches those three conditions will be easily locatable, and in CustomerID order, which gives good options for being used in a join. Remember that your matching on CustomerID needs to join on several things. ...


1

I have now moved from dev to production. the table FactBackOrder has 4,183,289 rows. We're in sql server 2012. Microsoft SQL Server 2012 (SP1) - 11.0.3412.0 (X64) Mar 2 2014 01:25:09 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) But I don't want to use Columnstore indexes - these tables are ...


8

when does SQL Server notice the new indexes and determines whether they actually help the query? Immediately. Creating an index causes a recompile because of a schema change. Here's an illustration of this. First to setup the objects in the test database: use TestDatabase; go if exists (select 1 from sys.tables where object_id = ...


1

My experience is that a useful index gets used almost immediately. It does change the information available to the optimizer after all. However, a new index may never get used if the optimizer rejects its value. You should note that a stored procedure may hold onto an existing plan for some time. If so, you can use exec sp_recompile procedurename to ...


4

First and foremost thing, SQL Server in picture is SQL Server 2008 R2 RTM which is not supported by Microsoft in any way please apply SQL Server 2008 R2 SP3 to at least get extended support I analyzed and found that one of the User DB A was using 8 GB RAM I don't think there is any issue because of this, this is totally normal looking at this you ...


4

One of the most important considerations for a clustered index in terms of performance is that it be ever increasing (or decreasing) and not something that will be changed (except possibly very rarely). The clustered index represents the physical order of your table. If you are constantly inserting into the middle of the index, or modifying the values of ...


2

Is the proposed index meant to enforce the data constraint, or improve performance? If you want the index to prevent duplicate records, you're obliged into include all three fields. However, if you're trying to improve performance, and your queries typically filter on ID1, you have another option: create a non-unique index on ID alone. This would be ...


0

Not sure if this answers your question, but I would not bother so much about the individual cardinality of X and Y. For index design I would start with the predicates that the index should assist with. If you have queries like: where X = ? where Y = ? and X = ? go with (X, Y). If you have queries like: where Y = ? where Y = ? and X = ? go with (Y, ...


3

No, there is no knob for clearing just missing_index DMVs. You'll have to restart the service or, if you want to exclude specific tables, you could create a view against the DMVs with a filter.


1

There are basically three situations where you would want to index the child in addition to the parent in an FK/PK relationship: If you ever select from ITEM based on order_id (obviously!) If you ever run queries on from the JOIN of ITEM and ORDERS with a filter on ORDERS As with 2, but with a filter on ITEM that is less selective than that on ORDERS In ...



Top 50 recent answers are included