Tag Info

Hot answers tagged

13

Your index is seemingly fine and good (i.e. covering) for the query and it should be used. The real problem is the query itself and specifically this condition which hides an implicit conversion: WHERE [serialNumber] = 137802 According to SQL Server's datatype precedence, when two values of different datatypes are compared, the value with the datatype of ...


10

In SQL Server the clustered index key column(s) are always added in to the non clustered index to act as a row locator (Ref: More About Nonclustered Index Keys). For an NCI declared as unique they are added as an included column otherwise they are added to the end of the key. You might want to add the columns in explicitly if the default placement is not ...


10

I wrote a post about this kind of situation here, which goes into more depth than this answer. is there any way to say in index definition to include only first 800 characters from the column? This can be done by creating a computed column as LEFT(MyColumn, 800), and then indexing that column. Note that the column doesn't have to be materialized in the ...


8

A non-clustered index scan may be chosen in this scenario: the optimizer determines that it is cheaper to scan all rows rather than perform seeks/range scans the non-clustered index is "skinnier" than the clustered index the non-clustered still covers the columns needed by the query (or it covers enough of them and a lookup for the remainder is still ...


7

You haven't really given enough information about the original execution plan, what it changed to or what columns you put in the new NCI. It is possible that the query performance can be improved even further. The cost of the new index is the initial build, the additional processing of entries on INSERT and UPDATE and potentially the fragmentation ...


7

No, _idx_TableA will not be affected for this operation. I have modified your example and added another index (NCI) that actually includes the key column Col3. Here's my example code: use testdb; go CREATE TABLE [DBO].[TableA]( [Col1] [nchar](5) NOT NULL, [Col2] [nchar](2) NULL, [Col3] [int] NULL CONSTRAINT [TableA_PK] PRIMARY KEY CLUSTERED ...


7

The reason for this is that the "fixed" physical location of your row - the RID (or row identifier) might (and will!) change over time - think page splits that occur when a row needs to be inserted into a table on a page that's already full. Updating those RIDs in all the nonclustered indices that exist on a given table is quickly becoming both a hassle, ...


7

In simple terms, it involves less processing and movement of NC index entries when data in the clustered index physically moves (row forwarding, page splits, INSERTs etc). Mostly the clustered index entries only need changed: not the NC index pointers. By using RIDs, you'd need to do a lot more work on the NC indexes. To minimize this lookup in a query, ...


7

This is the name of the filegroup or partition scheme that the index is created on. This can be specified when creating an index with a second ON clause. The sp_help procedure calls sp_helpindex which retrieves the name from sys.data_spaces The primary filegroup contains the primary data file and any other files not specifically assigned to another ...


7

SQL Server does not "rebalance the tree" as a periodic event. I have last heard this term in the context of Oracle. All that SQL Server does it increase the tree height when necessary. This is an event that happens only a few times in the entire existence of a B-tree. In a DML heavy workload there can be many small tree adjustments called page splits. These ...


7

Are there any reasons for periodic/cyclic slow-down of insert performance? Yes. check point events. With a write intensive workload, big RAM server, as you describe, a large number of 'dirty' pages accumulate in memory. At the predetermined checkpoint interval all these dirty pages get written to disk, causing a spike of IO requests. This in turn slows ...


6

I assumed it would follow my hint, and maybe error out at execution time if I wound up with some bad data and the index was missing some needed values. The query optimizer will only use a filtered index in a query plan if it can guarantee (within its reasoning framework) that all possible matches can be served from the index. This is by design, to avoid ...


6

I think the script in question is sp_BlitzIndex. The script lists tables that have more than 3 columns, and the number of non-nullable columns is 1 or 0. It doesn't mean that those tables are bad-- but if you see a lot of rows for this, it's just there to raise the question: Is it valid for those columns to all really contain nulls? When tables are ...


5

If you need columns in the output that aren't covered by the index, the optimizer has to make a choice: Perform a table / clustered index scan (therefore all columns are there) Perform a seek, then perform lookups to retrieve the columns not covered Which way it will choose depends on a variety of things, including how narrow the index is, how many rows ...


5

SQL Server will show you an Index it would like to use, since it estimates that index would make life easier. There is certainly no need to create an index on every searchable field, in fact doing so will make write performance substantially worse. If you have less than say 5 to 10 indexes on the given table, and you run that query all the time, you ...


5

They don't tend to be shown in ERDs. An ERD focuses on the Entities and their Relationships, but an index is a copy of the data from one (or potentially more) of the entities, created to assist in the execution of queries. While it's possible that an index could be unique and therefore contribute to the database design, they are not typically shown on ERDs. ...


5

And it looks like curiosity didn't actually kill the cat :-). So I found this book Database System Concepts (Sixth Edition) online and it's freely available to be read. I've scratched it a bit and it turns out that it has the needed definition inside: Primary index: in a sequentially ordered file, the index whose search key specifies the sequential ...


5

I think the problem here is a difference in terminology. The "number of writes" that's usually referred to is the number of object accesses, rather than the number of pages that get touched by the physical operation. The reason why that's usually used as a metric in discussion is because it's a more "stable" and meaningful number to talk about. As we're ...


5

Since we are talking about the clustered index, just because you defined the CI key column as ID, you still have the DeletedDate data in the leaf data pages of the index. That's the nature of the clustered index: It is the table data. Because you are typically having queries that look like: select * from YourTable where DeletedDate is null; You will ...


4

(I'm assuming SQL Server in this answer, please clarify which RDBMS you're using if it's not SQL Server) The index you want is: CREATE INDEX [IX_Parent_Id] ON [sch].[Parent] (Id) INCLUDE (Name) You use INCLUDE when a column isn't being filtered on (isn't in the WHERE clause) but is being selected. INCLUDE means that the column will only be included at ...


4

Go ahead and drop the clustering key while you're importing data. When you've finished your INSERTs, create the clustering key first, then the PK if it's non-clustered, then any remaining indices. I'm running such scripts at this very moment, and it takes about half as long as inserting into a table which is fully indexed. There's no problem in going ...


4

Yes, as ypercube already said. The only thing to look out is a wild discrepancy in the column actual data size. For instance if NC_Index1 is some 1Mb, and NC_Index2 is 200GB then you can introduce some potential scan perf problems. But, frankly, the chances of this being the case are, basically, 0. Or NULL, depending on your prefs.


3

No. Since the update does not update data in neither col 2 nor the clustered key (col 1), the index _idx_TableA with only col 2 does not get updated. @professionalAmateur, click on the execution plan button on the top. Run you query. Execution plan tab should show as below.


3

To use a filtered index it wants to see the predicate there, or one that matches very closely. So explicitly saying AND ins.ParentID IS NULL is going to be useful. Now, you should generally include the columns you're filtering on in the index itself because of a QO quirk. If the predicate in the query matches exactly the one in the index, and you're not ...


3

Clustered index key performs best to be unique, narrow, static and ever-increasing by itself. So in this case, the inclusion of DeletedDate actually result the clustered key becomes non-unique non-static (presuming the DeletedDate value could be changed). The INCLUDE in the non-clustered index is useful to cover the query without having to perform a key ...


3

Your first column in the index is cl_totalcrawltimes. In the WHERE clause you're using this column to define a lower range (cl_totalcrawltimes > 0). Depending on the data distribution this most probably will lead to an index scan instead of an index seek. Furthermore, cl_crawlsource is not part of the index (the include section, preferrably), so this would ...


2

The reason it is telling you to create a new index is because the one you have is not the most optimal one possible. The query optimizer is quite selective in the way that it chooses which index to use, the following link should help you understand selectivity: http://blogs.msdn.com/b/bartd/archive/2011/01/25/query_5f00_tuning_5f00_key_5f00_terms.aspx You ...


2

Based on your note it sounds like adding a non-clustered index to that table is the right way to go. Play with the order of the columns in the development system, and add any needed columns as included columns and see if the query gets any faster. The general rule of thumb is that is a query is a part of the application and the query is being used ...


2

You cannot have an unique constraints backed by an aligned index (or plain unique non-clustered indexes) unless you add the partitioning column to the the unique expression. So if you have partitioned your table on column [datetime] then your unique constraint (or the unique index) must be ([datetime], [xyz]). Since more often than not this is not ...


2

Try generating an actual plan in SQL Sentry Plan Explorer. For a narrow plan the actual costs are not included in the XML, so we can't show those, but we at least have the ability to let you know that non-clustered indexes are affected. Disclaimer: I work for SQL Sentry. A lot more information found in the duplicate question: Nonclustered Index Insert ...



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