Browse DevX
Sign up for e-mail newsletters from DevX


Create and Modify SQL Server Indexes Properly : Page 4

Learn how to determine the state of your indexes and understand whether that state should be changed. See how evaluating the construction and location of your system's indexes and deciding whether to adjust their fill factors and padding can improve the system's performance.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Make Wise Index Decisions
With SHOWPLAN, SHOWCONTIG, and the other DBCC commands, SQL Server provides great tools for evaluating and managing indexes. Next to database design, no other factor is as potent for getting the best performance out of your system. Databases use indexes to speed processing, both data modification and retrieval. Although they are not part of the relational model, indexes are a necessary physical manifestation of primary and foreign keys as well as commonly queried columns.

The databases system tables hold statistics that the optimizer uses to decide how to most quickly resolve your queries. These statistics convey how skewed the distribution of values in the index are by providing the number of values associated with each key and its distribution, both of which indicate how selective or useful the index is. Based on the statistics, the system will use the index to satisfy a query or, if the index is not selective enough, will disregard the statistics and scan the whole table—something you want to avoid.

I hope you use this information to make informed decisions about what to include in your indexes, how to maintain them, and how to get the best performance from your system.

Drew Georgopulos is the Database Architect for GoAmerica Communications. He moonlights as an Adjunct Professor of Computer Science at Marymount College of Fordham University in Tarrytown, NY teaching systems analysis and relational database design. He holds a certification in systems analysis and relational design from Columbia University, has presented at SQL Connections on SQLDMO, and is a member of the Worldwide Institute of Software Architects. You can reach Drew with your comments and observations at drewg1010101@aol.com.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date