Tag Info

New answers tagged

0

Primary Key and Clustered Index are really separate concepts and, although many tables put both of these attributes on the same constraint or index, this is not a requirement. I do not see how changing the Clustered Index to the make it also the Primary Key will particularly help your performance. It sounds like you have a Primary Key (BIGINT) already and ...


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


2

There is a situation in your current setup that would/could cause some slowdown related to an auto-incrementing key (IDENTITY, GETDATE(), NEWSEQUENTIALID()): under high-concurrency INSERT operations, there can be contention related to placing rows on the same page. This is called a "hotspot" and is one of the few drawbacks to auto-incrementing values as ...


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


4

While this looks like more code, I think it's more straightforward because it mimics what is asked in English more closely (find the tables without a clustered index, not the tables with an index_id of 0) it doesn't need to apply distinct it avoids pesky metadata functions and awkward functions like objectproperty/ex it includes the schema SELECT [table] ...


2

Query sys.indexes using: select distinct [table] = object_name(object_id) from sys.indexes where index_id=0 and objectproperty(object_id, 'IsuserTable') = 1 or: SELECT SCHEMA_NAME([schema_id]), name FROM sys.tables WHERE OBJECTPROPERTYEX([object_id], 'TableHasClustIndex') = 0; If you are looking for a complete index analysis, you should take a look ...



Top 50 recent answers are included