61

You can keep the following in mind when caring about updating statistics (copied from Rebuilding Indexes vs. Updating Statistics (Benjamin Nevarez) By default, the UPDATE STATISTICS statement uses only a sample of records of the table. Using UPDATE STATISTICS WITH FULLSCAN will scan the entire table. By default, the UPDATE STATISTICS statement updates both ...


45

Short answer: integer is faster than varchar or text in every aspect. Won't matter much for small tables and / or short keys. The difference grows with the length of the keys and the number of rows. string ... 20 characters long, which in memory is roughly 5x that of the integer (if an integer is 4 bytes, and the strings are pure ASCII at 1 byte per ...


45

When SQL Server creates a missing index recommendation for a particular query plan, it separates possible key columns into 2 groups. The first set contains all of the recommended columns that are part of an EQUALITY predicate. The second set contains all of the recommended columns that are part of an INEQUALITY predicate. Within each set, the columns are ...


36

Yes, it will influence initial plan compile time as the optimizer will have many extra access paths to the data to consider. Since you're on SQL Server 2017, loading once, and running reports, why not just use a clustered column store index instead? That seems to be the ideal solution to your need to index every possible column combination. Columnstore ...


27

A nonclustered index that has the same key(s)* as the clustered index, may still be useful, because the nonclustered index will usually be smaller and denser. Remember, a clustered index includes all in-row data, so it is normally the widest (least dense) index possible. * The same key columns, in the same sequence, sorted the same way (asc/desc). For a ...


27

If you have N columns in a table, every possible column combination is 2^N-1 (removing the empty set). For 10 columns that would mean 1023 indexes, for 20 columns we end up with a whopping 1048575 indexes. Most of the indexes will never be used but will have to be taken into consideration by the optimizer. It is possible that the optimizer will choose a sub-...


27

Why this index is not "covering" for this query: No good reason. That is a covering index for that query. Please vote for the feeback item here: https://feedback.azure.com/forums/908035-sql-server/suggestions/32896348-filtered-index-not-used-when-is-null-and-key-looku And as a workaround include the WhereColumn in the filtered index: CREATE NONCLUSTERED ...


21

The primary problems with GUIDs, especially non-sequential ones, are: Size of the key (16 bytes vs. 4 bytes for an INT): This means you're storing 4 times the amount of data in your key along with that additional space for any indexes if this is your clustered index. Index fragmentation: It is virtually impossible to keep a non-sequential GUID column ...


19

Is the WHERE-JOIN-ORDER-(SELECT) rule for index column order wrong? At the least it is incomplete and potentially misleading advice (I didn't bother to read the whole article). If you're going to read stuff on the Internet (including this), you should adjust your amount of trust according to how well you already know and trust the author, but always then ...


18

Typically indexes will be used by SQL Server if it deems it more expedient to use the index than to directly use the underlying table. It would seem likely the cost-based optimizer thinks it would be more expensive to actually use the index in question. You may see it use the index if instead of doing SELECT *, you simply SELECT T1Col1. When you SELECT * ...


18

Index A is better for this query. When all the conditions in the WHERE are equality checks except one that is using a range condition or IN operator on a column, then that last column should be last in the index, after all the columns that have an equality check. This allows the optimizer to use an index seek to the first row that matches the conditions and ...


17

The activity of altering big tables are done in phases: Create a new table with required fields and indexes say in test DB (just structure) Dump the data from the existing table and load the same to the newly created table in test DB Now announce your downtime :) Swap the tables by renaming - RENAME table ur_db.table_name to test.temp, test.table_name to ...


17

Very short version: Yes, sometimes. PostgreSQL can use bitmap index scans to combine multiple indexes. A predicate like WHERE a > 50 AND a < 50000 is a specialisation of the more general form: wHERE a > 50 and b < 50000 for a = b. PostgreSQL can use two indexes here, one for each part of the predicate, and then bitmap AND them. It doesn't ...


16

It is legacy from SQL Server 2000 0 and 100 were different back then 100 meant "fill all pages including all b-tree index levels" 0 meant "leave some space at higher levels in the b-tree index" Since SQL Server 2005, both mean "fill all pages including all b-tree index levels" Quotes from BOL (My bold) SQL Server 2000: A fill factor value of 0 does ...


16

Your index only has 679 pages. Ola's solution is set to ignore indexes with less than 1000 pages (see the @PageCountLevel parameter). You can override that so that it cares about indexes with fewer than 1000 pages, but why? Wasted effort IMHO. I would stop worrying about small tables like this - let Ola's solution do its job, and worry about fragmentation ...


16

By default the PK is clustered and in most cases, this is fine. However, which question should be asked: should my PK be clustered? which column(s) will be the best key for my clustered index? PK and Clustered index are 2 differences things: PK is a constraint. PK is used to uniquely identify rows, but there is no notion of storage. However by default (...


16

If you want good results from the query optimizer, it pays to be careful about data types. Your variables are typed as datetime2: DECLARE @OrderStartDate datetime2 = '27 feb 2016'; DECLARE @OrderEndDate datetime2 = '28 feb 2016'; But the column these are compared to is typed smalldatetime (as the sdtm prefix suggests!): [sdtmOrdCreated] SMALLDATETIME ...


16

I'm frequently involved in code reviews for the dev team, and I need to be able to give general guidelines for them to follow. The environment I'm currently involved in has 250 servers with 2500 databases. I've worked on systems with 30,000 databases. Guidelines for indexing should revolve around the naming convention, etc, not be "rules" for what columns ...


15

As far I understand this, I am looking at a KEYLOCK deadlock basically caused by an uncovered index query that uses a nonclustered and a clustered index in order to collect the required values, right? Essentially, yes. The read operation (select) accesses the nonclustered index first, then the clustered index (lookup). The write operation (insert) accesses ...


15

This DMV only maintains statistics since the last SQL Server restart; the view gets wiped out completely and everything starts from scratch. More importantly, the rows in this view for any specific index are removed when that index is rebuilt (but not when it is reorganized). If you are performing regular index maintenance, it might be useful to look at the ...


15

If the subquery in that update consistently uses those two predicate values, a filtered index should help a lot. Something like this (which Erik Darling kindly provided as a comment): CREATE INDEX IX_ntID_ResponseWindow_Includes ON dbo.Notifications (ntID, ResponseWindow) INCLUDE (NotificationType, Status) WHERE (Status = 'Done' AND NotificationType = '...


14

Timestamps with B-tree index I suggest a third option: as long as your table holds two timestamp columns (which seem to be defined NOT NULL) I would use a single multicolumn index with opposed sort order (if no other considerations apply): CREATE INDEX reservations_range_idx ON reservations using gist(starts_at, ends_at DESC); More in these related answer: ...


14

Index Hint You can use an index hint to do that: SELECT * FROM dbo.Users AS u WITH (INDEX = ix_definitely_an_index) WHERE u.Reputation = 2; The downsides are: Potentially changing a lot of code If you rename an index, this breaks If you change an index definition, it might not be the best index to use anymore Plan Guides You can also use a Plan Guide, ...


13

There is nothing wrong with GUID as keys and clusters in an OLTP system (unless you have a LOT of indexes on the table that suffer from the increased size of the cluster). As a matter of fact, they are much more scalable than IDENTITY columns. There is a widespread belief that GUID are a great problem in SQL Server - largely, this is quite simply wrong. As ...


13

Clustered Index This index is the table. The index information is stored in the b-tree pages while all of the other columns (those not in the index) are stored at the leaf level of the index. All other indexes will have a link back to this one and will always include the indexed columns of the clustered index at their leaf level. This is because if SQL ...


13

Yes, there can be downsides. If another query looks at a different data segment not determined by the date, it might take a performance hit if rows are spread out over more data pages now. Just the same way as your first query profits. That completely depends on information not in your question. other queries using a PK of table (let say id_foo) That ...


13

Yes it could, when SQL Server decides that the statistics from that index is more accurate/useful and uses that stats to do the estimates and come up with a plan. I have come across situations when SQL Server has decided to use stats from one index and scan/seek another index. Edit - This might not be applicable cause I just realized that you have ...


12

And so enters the art of performance tuning and indexing strategies... It seems logical to me to amend the existing index definition to include the suggested columns I'm going to take your quote and write a third index definition: create index [idx_index3] on [table1] (col1, col2, col3) include (col4, col5, col6....); That should be the CREATE INDEX ...


12

In addition to the T-SQL techniques discussed in Aaron Bertrands article, "Best approaches for grouped running totals", you might want to look at a SQLCLR procedure (if you are running less than SQL Server 2012). The main advantages are that a SQLCLR procedure requires only a single scan of the source records, and benefits from the increased execution speed ...


12

It seems to ignore any index I put on it Unless you're using SQL Server Enterprise Edition (or equivalently, Trial and Developer), you will need to use WITH (NOEXPAND) on the view reference in order to use it. In fact, even if you are using Enterprise, there are good reasons to use that hint. Without the hint, the query optimizer (in Enterprise Edition) may ...


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