New answers tagged index
0
Books online includes several reasons you might not be able to drop an index.
The index is associated with a primary key constraint.
Filegroup is offline or read-only.
"You cannot drop a clustered index online if the index is disabled on a view, or contains text, ntext, image, varchar(max), nvarchar(max), varbinary(max), or xml columns in the leaf-level ...
0
Another option would be a DDL trigger, which can capture index operations.
USE your_database;
GO
CREATE TRIGGER [IndexEventAudit]
ON DATABASE
FOR ALTER_INDEX, CREATE_INDEX, DROP_INDEX
AS
BEGIN
SET NOCOUNT ON;
DECLARE @e XML = EVENTDATA();
DECLARE @sql NVARCHAR(MAX)
= @e.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(MAX)');
...
0
Try this one -
CREATE TABLE dbo.Medicines
(
ID BIGINT IDENTITY(1,1) PRIMARY KEY
, MedicineName nvarchar(50)
, BrandName nvarchar(50)
, MedicineCode nvarchar(20)
, Price DECIMAL(10,2)
, Quantity INT
)
CREATE NONCLUSTERED INDEX [ind_Medicines] ON [dbo].[Medicines]
(
[MedicineName] ASC,
[BrandName] ASC,
[MedicineCode] ASC
...
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 ...
11
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 ...
1
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
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 ...
2
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 ...
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 ...
0
If the clients are filtering in almost the same way over and over again you can create an index for those queries.
E.g. the client is filtering on SiteId and StatusId you can create an additional index:
CREATE NONCLUSTERED INDEX IX_Ticket_InsertDateTime_SiteId_StatusId ON Ticket
(InsertDateTime DESC,
SiteId [ASC/DESC],
StatusId [ASC/DESC] )
...
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
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 ...
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, ...
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
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. ...
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 ...
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 ...
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 ...
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 = ...
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 ...
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 ...
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.
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 ...
...
0
Ok, the "RMAN> list failure;" thing didn't work, but it did lead me in the right direction. To get things going again I did this:
$ rman target sys/myuser@mytargetsrvr
RMAN> recover datafile 8
// Which gave this
ORA-00283: recovery session canceled due to errors
ORA-01124: cannot recover data file 8 - file is in use or recovery
// instead I did ...
0
Since it is a MyISAM table you are loading, please be aware of three(3) aspects of bulk loading:
ASPECT #1 : Bulk Insert Buffer
What is the bulk insert buffer? According to the MySQL Documentation
MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when ...
0
There is a command line utility dbv which can scan a datafile a will report and mark corrupted blocks. If you want for "fix" it you can either use RMAN block recover option - if you run Ent. Edition and if you have RMAN backup.
Otherwise search for document named "How to format corrupted block" on Metalink. This describes a situation when a table block is ...
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 ...
0
I guess the selectivity of column web_city.name is not very good. So the optimizer doesn't use index scan; You can see the distinct values of the column : as following:
--get the distinct valus
select tablename,attname,n_distinct from pg_stats where tablename='web_city' and attname='name';
If the output n_distinct is very low , then it imply that the ...
1
PostgreSQL only supports index-only scans since 9.2 and only if the visibility map allows that.
If there is but a single concurrent transaction which has updated a record in a page (and hence there exists a record not visible to all transaction), the visibility map record for this page is invalidated and its records need to be checked for visibility ...
0
I haven't tested this (as my tables usually have at least a primary key) but I expect the difference to depend on the choice of the primary key.
Based on the information in MySQL documentation about InnoDB engine, all InnoDB tables have a clustered index. This is the PRIMARY key of the table and in lack of one, the first UNIQUE index. And in lack of unique ...
0
On the contrary, a table with no indexes would be faster to run INSERTs on than a table with indexes.
However, in the case of InnoDB, there is already an internal index in use called the gen_clust_index. It is also better known as the Clustered Index. It exists for all InnoDB tables, even those with no PRIMARY KEY.
I have written about the gen_clust_index ...
Top 50 recent answers are included
