Hot answers tagged index
12
Short version: seek is much better
Less short version: seek is generally much better, but a great many seeks (caused by bad query design with nasty correlated sub-queries for instance, or because you are making many queries in a cursor operation or other loop) can be worse than a scan, especially if your query may end up returning data from most of the rows ...
7
In this case I consider the first index redundant (and less useful). Note that dropping the index will invalidate any plan that references it, so the next time you run any of those queries, you may see a blip in performance due to the required recompile to access the second index instead. While both indexes existed, any query that only needed to act on ...
5
Your comments note that you're specifically talking about full-text indexing.
You can indeed query the database while a full-text index is being created. Full text indexes are created in the background on SQL Server 2005, 2008, and 2008R2.
You can continue to query the database using the LIKE operator, although of course the queries won't be as fast as a ...
4
My suggestion would be:
(a) talk to Azure support. This is not how it should be working AFAIK.
(b) when building your list of indexes to rebuild/reorganize, add a NOT EXISTS clause to the criteria to eliminate any indexes with GUIDs as the leading key column:
SELECT name, etc.
FROM sys.indexes AS i
INNER JOIN sys.dm_db_index_physical_stats AS s
ON ...
...
4
FILLFACTOR only applies when you build or rebuild an index, not during normal operation. Normal operations always try to fill the pages to 100%.
If you insert a row that has a variable width, then update the row to be longer, that row will no longer fit on the page if there isn't enough extra space to store the after-image on the same page. If there isn't ...
3
If you wish to dig the subject, a very helpful book (at least for me) is SQL Server Execution Plans by Grant Fritchey, freely available at RedGate here.
If you have a query such as
SELECT *
FROM myTable
SQL Server will likely use an Index scan, as it needs to go through all the rows to display the required results.
On the contrary,
SELECT *
FROM ...
3
Generally, seeks are good, scans are bad.
Seeks are where the query is able to make effective use of the index, and use it to find the rows it needs.
Scans are where the query is looking through the whole index trying to find what it needs.
How does SQL choose? Deep in the internals of the query optimiser, the decision is made based on your query and the ...
3
Conditions with OR are harder for the optimizer than conditions with AND only.
Two or more range conditions (>, >=, <, <=, BETWEEN, LIKE 'search%') are harder than conditions with equality only or with only one range.
Your query has both the above difficulties. Noticing that it is equivalent to this rewriting:
WHERE ( languageId = 3 AND
...
3
why isn't it possible to access the data directly from the table discarding the B-tree?
(most likely by scanning the table row by row)
wouldn't that be more appropriate than inaccessible data at all?
To answer your question, Indexing basics comes more handy -- An index is made up of a set of pages (index nodes) that are organized in a B-tree structure. This ...
3
Using SELECT * is bad practice especially in a stored procedure. Even though you have a WHERE clause to filter the rows returned, I would explicitly state the columns.
As for indexes you will probably have to do the tuning yourself by looking at the execution plan for each type of index applied. However I would have a clustered index on ID and 1 non ...
2
They are not duplicate. However, depending on the nature of your queries, they may not both be needed.
Whenever I need to review the definition of a duplicate index I refer to Kimberly Tripp's posts:
http://www.sqlskills.com/blogs/kimberly/how-can-you-tell-if-an-index-is-really-a-duplicate/
...
2
Right now, you are in a very fortunate position. I noticed you have big-tables defined. This is preventing you from experiencing "Table is Full" errors. Why is this good?
Whenever you get "Repair With Keycache" as a status, you have no free space to do file sorting. Making sort_buffer_size bigger isn't necessarily the answer since temp tables become disk ...
2
Others have defined well enough the differences between seek and scan. In this instance, your query itself and the execution planner should give you the information you need to see which values are used as predicates (filters) for the query in each part. Typically it's a good practice to always add non clustered indexes on foreign keys, and depending on the ...
2
Seems like you have media corruption. I would consult Automatic Diagnostic Repository (ADR) contents if there are open failures in your database. You can do that via Enterprise Manager or using RMAN command-line utility:
[oracle@oca ~]$ rman target=/
RMAN> list faliure;
If there are failures with status OPEN listed, you can ask Data Recovery Advisor ...
2
The reason that your index rebuild isn't completing is because of the LCK_M_SCH_M wait type. What happens when you try to rebuild an index, a Sch-M lock is requested on the object that you're trying to rebuild.
Please see this below chart for lock compatibility:
As you can see here, a Sch-M lock has a conflict with almost every locking scenario (shared, ...
2
I have recently discovered a fantastic free script from the people at BrentOzar Unltd
http://www.brentozar.com/blitzindex/
This does some good analysis of which indexes exist, how often they are used and how often the query engine is looking for an index that doesn't exist.
It's guidance is generally good. Sometimes it gets a bit over-suggestive of ideas. ...
2
Try switching the index to be:
KEY `new_places_on_wishlist` (`wishlist_id`, `id`, `place_id`)
You are effectively doing the part of order by optimization manual that says it can't use the index because of ordering by non-consecutive parts of the key, although their example is different than your situation.
Basically you are currently doing
SELECT ...
2
You can use opensource tool like sql-dbdiff or OpenDBDiff. Both are commandline, so can be used in automating scripts.
Also, if you want 3rd party licensed tool then Redgate's SQL Compare (if u want for data compare -- there is data compare as well) is very useful and I have used it extensively for automation.
Out of curiosity, why do you need Indexes on ...
2
Once the database is detached you will probably not be able to attach the data files. Since the file operation that occurred is an almost guaranteed data corruption I think your best option here is to perform a restore from a native SQL server backup or export of some kind, or recover any snapshots you might have taken of the environment.
1
Finally I've been able to figure out the problem, yet I still don't know why it happenned!
I've found this answer.
Doing ALTER TABLE acs_spectotechnologies_com_enerconcept_0005.measuresHistory ENABLE KEYS; fixed the usage of keys for this table, now every requests are lightning fast!
1
First point: a table is either a heap (has no clustered index) or a clustered table (has a clustered index). One table can have at most on clustered index, since there is only one way to sort a table at one point in time.
When creating a clustered index, SQL Server sorts the data pages and creates a b-tree indexing the pages (not the rows - non-leaf pages ...
1
What you are asking is a little daunting. Here is why:
Would it be faster to store a hash of the value as well and instead index and search on that?
Creating a hash column and indexing sounds like a great idea. I have suggested that back on March 03, 2013 : Possible INDEX on a VARCHAR field in MySql (See Suggestion #3)
Does that even make sense if ...
1
You can use Powershell and SMO to do this - simple, efficient and customizable. Such script to script out database objects can be found here and here
1
SQL Server Management Objects SMO is your answer. You can use it to accomplish this task. Here is an example to generate Create Table Scripts.
public string GetTableDescription(string pDatabaseName, string pSchemaName, string pTableName, string connectionString)
{
connectionString = ...
1
As I understand your question you are asking why highly selective index scans might be much slower after a certain number of records are returned or after the table reaches a certain size. As it turns out your query plans provide most of the information needed. Understanding of course is the first step in trying to figure out how to solve the problem. It ...
Only top voted, non community-wiki answers of a minimum length are eligible
