New answers tagged

0

Usage statistics come from sys.dm_db_index_usage_stats, which tracks the number of execution plans that include an operator touching that index. It's reset on SQL Server service restart, or when the index is modified. Operational statistics come from sys.dm_db_index_operational_stats, which track the number of times the index has actually been touched. It's ...


16

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 ...


6

Yes, given the constraints in the question, particularly that the primary key column is the leading column in the indexes. Also assuming the primary key never changes. Not necessarily. The optimizer can indeed infer uniqueness without marking the nonclustered index unique. Marking the index unique may introduce a Split-Sort-Collapse combination in ...


-3

The FILLFACTOR on the table is 100, thus there are no free pages in the cluster index. If you're doing lots of inserts try setting FILLFACTOR to something like 80. And read the SQL Server books online regarding FILLFACTOR. ;-)


5

This started as a comment/questions but it got to long so I moved it here: I'm really thrown by this question. 1.5mil rows isn't really all that big. And the point behind an identity is that it's ever increasing. If that's your CL you shouldn't be doing inserts into the middle of a page, certainly not often enough to cause the level of fragmentation ...


1

You might find it interesting to check Thomas Kejser's take on indexes in SQL Server. Although clustered indexes are very useful, there can be reasons to keep a heap. For example, read this post: http://kejser.org/clustered-indexes-vs-heaps/ Particularly look at the topic: Fragmentation Prone tables with lots of INSERT activity This topic seems to ...


3

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 ...


15

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 ...


1

Seems like a decent approach. Of course, one should apply some human verification to this before automatically dropping everything that seems unused. For example, it's conceivable that the statistics were recently reset and/or an index is only used for some occasional batch tasks.



Top 50 recent answers are included