And 68 indexes. We run a bespoke ORM kind of business layer in our application where we have many objects that represent a row in a table, but they might be retrieved but other columns than the identity, so we have a small number of indexes one per predicate but each index will include the whole table, basically. Getting the exact right number of indexes in your environment involves rolling up your sleeves and doing some work.
Our application performance is pretty good currently but I always look for things that can be done better. It could have been the standard, and it would have made a whole lot of things easier in the modern era. On the other hand, we have learned a lot from the struggle with the incompatibility of base-ten with powers of two. In other news, you may want to investigate Saw Stop table saws. The videos are pretty cool. The TL:DR for this article is, it depends.
I work on an ERP system that has 10 or 15 indexes on most tables out of the box, and still experience poor in performance on some queries or get hints for missing indexes.
Its something we have to live with and need to be creative to gain better performance. Start by staying on top of what existing indexes are not used or indexes where the write to read ratio is high. The use cases and data access patterns for an application can drift over time, making once useful indexes obsolete. Still, what can be potentially worse than an update with too many indexes — is an update without a covering index, which results in frequent and extended periods of blocking. What about a scenario where you have a date table.
Something super static that has zero inserts. We insert like years worth of dates in it and there it sits. Sure, many of them are rarely used, but is there really any harm in a table with 36K rows in it with no updates having so many indexes?
I figure, small table, no updates, no harm. I have recently created 40 indices on a table, some with up to 15 columns, and i am content. It usually only updates over-night once a month, but it is constantly used for ad hoc queries.
I am very happy with its performance and only now becoming grateful that had I those extra hands grafted on. NB This is a fatuous comment and is not intended to encourage reckless cosmetic surgery, merely to demonstrate that the rules are more like guidelines — always think about your precise requirements when applying them. Your email address will not be published. Don't subscribe All Replies to my comments Notify me of followup comments via e-mail. Unused indexes add overhead to insert, delete, and update operations as well as index maintenance operations.
Index usage statistics are initialized to empty when the SQL Server service restarts. Save my name, email, and website in this browser for the next time I comment. Toggle navigation. Some of these DMVs include: sys. Name TableName, SI. It only takes a minute to sign up. Connect and share knowledge within a single location that is structured and easy to search. I have a table with about 37 indexes. Would that be considered too many? What are some best practices regarding indexing?
EDIT: It is warehousing data, updated daily with new transactions. There are 47 million rows. I've tried searching for a case where having too many indexes was a problem and couldn't really find anything. You KNOW you have too many if your inserts are too slow, and the index used for reading are not speeding things up enough to make up for it.
For example, usually if you have an index on columns A, B you don't need a separate index on A. And you probably don't gain much by having another index on A, B, C, E. Creating an index is a trade-off. You have to determine whether the overhead of maintaining the index and thus slowing down DML operations and the additional space consumed by the index is worth the performance gain you get from the queries that use the index.
That, in turn, depends on how much the queries benefit and how critical those queries are. The number of indexes on a table is irrelevent in this cost-benefit equation-- each index ought to be considered on its own though the performance benefit of a particular index may be dependent on whether a different index is available for the query to use instead.
You need to consider using a Star Schema to start with and you may based on the features in your choosen database to build static aggregate tables connected to dimension tables to provide summaries using batch processing.
The above will provide you better performance and possibly less indexes. You can gain further performance by shutting off some of your indexes during load and re-building them after load is complete. Building an index over 47 million rows is very fast. Above all, you need to justify the need of every index as a general database design practice. This can only be done by examining, in details, user reporting needs. Remember that even if you define indexes, your query may not use one or more of them, so your user needs, query analysis and good database design should help you determine exactly whether to use an index or not and the type of index required.
I have designed several database and data marts, but honestly, never seen that number of indexes per table. Most things that I have seen say that having more than 5 indexes will begin to slow down your insert speed. Consider this, every index you have is like having a new table in your database with the same information just sorted a different way. Having 37 indexes is like having to do an insert 38 times!
0コメント