Tag Info

Hot answers tagged

7

how can I utilise SQL Server tools to identify query optimisations, missing indexes etc? No tool is perfect and you should not rely implementing the suggestions without proper testing your entire business life cycle. Native tools like DTA are OK, but I have seen many problems with using it. I would not rely on it. That being said, SQL Server exposes a ...


6

The indexes that were created and dropped by DTA were most likely hypothetical indexes - ones that DTA creates while running to perform its analysis and should (but doesn't always) delete once done. These hypothetical indexes can be created even if you weren't looking at tuning that specific table! I'd recommend you query some of the system DMVs directly ...


5

SORT_IN_TEMPDB means that SQL server will use tempdb to allocate the temporary space as opposed to allocating space in the user database whose index is being rebuild. This means you will need less free space in your user database during an index rebuild operation and more free space in tempdb. It gives you better advantage when tempdb is on a different set ...


5

Neither MySQL nor the siblings (MariaDB, Drizzle, etc) have implemented partial indexes. What you can do, with this restriction in mind: a) make a simple (not partial) index on (is_active, measurement_id). It will be used in queries where the partial index would. Of course if the is_active column is 3% True and 97% false, this index will be much bigger ...


4

For the specific query: select sum(diff_ms) from writetest_table where time_on > '2015-07-13 15:11:56' ; -- use single quotes, not double an index on (time_on, diff_ms) would be the best option. So, if the query runs often enough or its efficiency is crucial to your application, add this index: ALTER TABLE writetest_table ADD INDEX ...


4

I have a broad question regarding SQL Server... Depending on which server version you have access to, the tools vary. Microsoft has an entire documentation section on optimization tools for each of the different server versions. Despite the differences, the fundamentals are the same across most versions. Missing indexes, for example, are addressed in ...


4

As @Chris commented correctly on the referenced question: a little investigation seems to indicate that the recheck condition is always printed in the EXPLAIN, but is actually only performed when work_mem is small enough that the bitmap becomes lossy. Thoughts? http://www.postgresql.org/message-id/464F3C5D.2000700@enterprisedb.com While this is ...


3

Erwin's answer does a good job of answering the question as originally stated, however you added the comment: I want to check proactively if there is any corrupted index in the database, using the below query SELECT index_name, status from user_indexes where status='INVALID' Assuming you are talking about the indisvalid column in pg_index, or some ...


3

I think @Craig's comments are more important, addressing the intentions behind your question. In any case, to answer the question asked: One simple way (among many others) would be to fake an IMMUTABLE function and base an index on it like outlined in this answer from yesterday: CREATE OR REPLACE FUNCTION f_fake_immutable_ts() RETURNS timestamp LANGUAGE ...


3

Basic answers Since you select a couple of big columns (info in comment) an index-only scan are probably not a viable option. This code works (if no NULL values in data!) Add NULLS LAST to make it work in any case, even with NULL values. The added clause won't hurt either way. Ideally, use the clause in the accompanying index as well: SELECT <some ...


3

Erwin describes a method of creating an index that violates PostgreSQL's assumptions about the index, producing results that are likely incorrect and do not match the underlying table. That's one kind of corruption. Another kind of corruption is where an index has blocks that are simply invalid - zeroed out, replaced with random values, etc. Most likely ...


3

Changing your table from a heap to having a clustered index should significantly improve your performance on both queries and perhaps even on inserts. Generally speaking, your clustered index should be narrow, unique, and ever increasing. Using a datetime that you can't guarantee to be unique is not ideal because it's 8 bytes and, since it isn't unique, sql ...


3

This is quite a common query (pardon the pun! :-) ) from people running queries which perform full table scans (FTS), when the poster feels that the system should make use of the index(es). Basically, it boils down to the explanation given here. If the tables are so small, the optimiser will say that "it's not worth the bother of going to the index, doing a ...


3

The index that will give you the most benefit is one on fldB, fldC, fldA desc or on fldC, fldB, fldA desc depending on your data. You'll need to check your data and keys to determine which column should go first. The last column of the index is the column for which the maximum value is being computed. By including it in the index you will avoid table ...


3

It doesn't need a separate index. It's only done that way because you've actually told it to be in the script. You could do this with the same functionality: CREATE TABLE [dbo].[JobItems] ( [ItemId] UNIQUEIDENTIFIER NOT NULL, -- lots of other columns CONSTRAINT [JobItemsIndex] PRIMARY KEY CLUSTERED ([ItemId] ASC) );


2

I wouldn't say that index 2 is a subset of index 1. An index that had the columns UserId, InstanceId would be. This could still be useful to queries that do not require the created column as they would be accessing a more narrow index which would require less IO and memory. Your best bet is to check if your indexes are actually being used by queries. ...


2

Based on: WHERE [Entity_Identifier] = @EntityIdentifier AND [Status] = 'In_Progress' AND [Process_Id] = @ProcessId ORDER BY [Message_Sequence] ASC ...the best thing is to have an index on ([Entity_Identifier], [Process_Id], [Message_Sequence]) WHERE [Status] = 'In_Progress' As Status is filtered with a constant, a WHERE clause is going to be useful ...


2

I think you already more or less answered your own question. While a GIN index can speed up searching inside an array quite significantly, you have to be aware that relational database engines are designed to do joins very efficiently. Also, when you need the actual tags, you will have to join the tag table regardless of you use the array approach or ...


2

If I get rid of the data representing 95 % of the table, I should have no further trouble filtering the rest of the data. In that case, separating that 95% of the data is way to go. If you want to stay with SSIS, the obvious solution is to redirect those records to a another flat file (can also be a separate db) during import. Specifying this flat file ...


2

In simple cases, it will be the same. However, I have seen very complex queries with several joins have significantly different plans. A recent one I was working on started with a table that has close to 6 million rows joined to about 20 different tables. Only the first join to this table was an inner join, all others were left outer joins. The filter in the ...


2

A query use the index in the column order (UserId first then RoleId). Without an index on RoleId, it will scan the clustered index. Unless there is a where clause with a userId, the engine does not know how to get inside the index whitout scanning all the userId. Because roleId is a FK, best practice suggest to have an index on it. You definetly need it if ...


1

I know the reason now. I need to vacuum the tables before using index-only-scan, otherwise, if a sufficiently high number of heap pages where modified since the last vacuum, the planner will not choose to use index-only scan. When only a small amount of pages have been changed, an index-only scan may happen, which then involves heap fetches. If I force it ...


1

In general, where you put the filters makes a difference. While Tom V says the Optimizer will recognize that the queries are the same and come up with the same plan, that is not always true. It depends on what version of SQL you are on, how complex your query is, and how important to the overall batch the Optimizer determines the query is. The Optimizer ...


1

The query will be slow because cardinality of category index is low. There are 12 categories, so in average the query will read 1/12 part of the index. You can't improve this query. Your original approach can improve overall performance. Just instead of manually updating book_count create a trigger on INSERT and DELETE event. UPDATE: To prove the query ...


1

I wasn't able to find any good resources online, so I did some more hands-on research and thought it would be useful to post the resulting full-text maintenance plan we are implementing based on that research. Our heuristic to determine when maintenance is needed Our primary goal is to retain consistent full-text query performance as data evolves in the ...


1

Given the structure of multicolumn B-tree index it is not viable to do a sort on position when using IN on category_id. But as the test data suggest the position is not "global" but seems to have a meaning only for given category_id. So as I suggested in comments, it is instead possible to ORDER BY (category_id, position) - that can use two-column index on ...


1

Index Scan scans each and every record in the index. Table Scan is where the table is processed row by row from beginning to end. If the index is a clustered index then an index scan is really a table scan. Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Index Seek: ...


1

The only situation where an index with full key cardinality = 1 will help is when you are looking for something that does not exists. By using such index you can quickly conclude that nothing will match your predicate, whereas without the index you will have to scan the table. If you have a large table and you frequently look for things that does not ...


1

Here's some explanation of which index you could use and when: First, indexes speed up retrievals but slow down inserts and deletes, as well as updates of values in indexed columns. That is, indexes slow down most operations that involve writing. This occurs because writing a record requires writing not only the data row, it requires changes to any indexes ...


1

It's a foreign key error (Error: 1025 | errno: 150) you can find out using perror: root@onare:/home/onare# perror 1025 MySQL error code 1025 (ER_ERROR_ON_RENAME): Error on rename of '%-.210s' to '%-.210s' (errno: %d) root@onare:/home/onare# perror 150 MySQL error code 150: Foreign key constraint is incorrectly formed You can get more details about what ...



Only top voted, non community-wiki answers of a minimum length are eligible