Skip to main content
Fachgebiete
Automobil + Motoren Bauwesen + Immobilien Business IT + Informatik Elektrotechnik + Elektronik Energie + Nachhaltigkeit Finance + Banking Management + Führung Marketing + Vertrieb Maschinenbau + Werkstoffe Versicherung + Risiko
DE
EN
Themenseiten
Organisationspsychologie Projektmanagement Marketing Smart Manufacturing
Bücher
Zeitschriften
Weitere Formate
Podcasts Webinare Technik Webinare Wirtschaft Kongresse Veranstaltungskalender Awards
Jetzt Einzelzugang starten
Zugang für Unternehmen
Referenzkunden
MTZ-Fachkongress

Antriebe und Energiesysteme von morgen


Austausch von Automobil- und Energiewirtschaft

Am 14./15. Mai geht es in Chemnitz um die Mobilitätswende durch Elektro- und Wasserstofffahrzeuge.
Erweiterte Suche
Anmelden
nach oben

2019 | Buch

Index Fundamentals Kapitel lesen Erstes Kapitel lesen

Expert Performance Indexing in SQL Server 2019

Toward Faster Results and Lower Maintenance

verfasst von: Jason Strate

Verlag: Apress

Enthalten in: Springer Professional "Wirtschaft+Technik" , Springer Professional "Technik" , Springer Professional "Wirtschaft"

Einloggen, um Zugang zu erhalten
insite
SUCHEN

Über dieses Buch

Take a deep dive into perhaps the single most important facet of good performance: indexes, and how to best use them. Recent updates to SQL Server have made it possible to create indexes in situations that in the past would have prevented their use.
Other improvements covered in this book include new dynamic management views, the ability to pause and resume index maintenance, and the ability to more easily recover from failures during index creation and maintenance operations. This new edition also brings new content around the indexing of columnstore and in-memory tables, showing how these new types of tables and the queries that execute against them can also benefit from good indexing practices.
The book begins with explanations of the types of indexes and how they are stored in databases. Moving deeper into the topic, and further into the book, you will look at the statistics that are accumulated both by indexes and on indexes. You will better understand what indexes are doing in the database and what can be done to mitigate and improve their effect on performance. You will get a look at the Index Advisor now available in Azure SQL Database, and learn how to review and maintain the health of your indexes. The final chapters present a guided tour through a number of scenarios showing approaches you can take to investigate, mitigate, and improve the performance of your database.

What You Will Learn
Properly index row store, columnstore, and in-memory tablesReview statistics to understand indexing choices made by the optimizerApply indexing strategies such as covering indexes, included columns, and index intersectionsRecognize and remove unnecessary indexesDesign effective indexes for full-text, spatial, and XML data typesManage the big picture: Encompass all indexes in a database, and all database instances on a server

Who This Book Is For
Database administrators and developers who are ready to lift the performance of their database environment by thoughtfully building indexes to speed up queries that matter the most and make a difference to the business

Inhaltsverzeichnis

Frontmatter
Chapter 1. Index Fundamentals
Abstract
The goal of this book is to help you improve the performance of your databases through the use of indexes. In order to accomplish this, you must first understand what indexes are and why you need them. You need to understand the differences between how data in a clustered index, columnstore index, and heap table is stored. You also will look at how nonclustered and other index types are built and how indexes interact with other indexes. This chapter will provide the building blocks for understanding the logical design of indexes.
Jason Strate
Chapter 2. Index Storage Fundamentals
Abstract
Where the previous chapter discussed the logical design and syntax of indexes, this chapter will focus on the physical implementation of indexes. An understanding of the way in which indexes are laid out and interact with each other at the implementation and storage level will help you become better acquainted with the benefits that indexes provide and why they behave in certain ways.
Jason Strate
Chapter 3. Index Metadata and Statistics
Abstract
Now that you understand the logical and physical fundamentals of indexes, you should look at the way in which statistics are stored for indexes. These statistics provide insight into how SQL Server can utilize and is utilizing indexes. It also provides the information needed to decipher why an index may not be selected and how it is behaving. This chapter will provide you with a deeper understanding about where and how this information is collected. You’ll investigate some additional DBCC commands and dynamic management objects (DMOs) that are available and see how that information comes to be.
Jason Strate
Chapter 4. XML Indexes
Abstract
The past couple chapters focused on indexing what is commonly referred to as structured data, where there is a common schema and organization around the data and its storage. In this chapter and the next few chapters, the indexing focus shifts to unstructured and semistructured data. With both structured and unstructured data, the task of indexing is to gain optimal efficiency for retrieving and manipulating data, but the data types that represent these types of data have differences in how they are stored in the database. These differences dictate how and why indexing is implemented as well as how the indexes are used by the query optimizer.
Jason Strate
Chapter 5. Spatial Indexing
Abstract
The next type of indexing we need to look at is spatial indexing, which relates to the spatial data types. Introduced in SQL Server 2008, spatial data types advance the storage capabilities of SQL Server allowing data that defines shape and location information. Before these enhancements, spatial data was often stored as string or numeric values without meaning within the database and required cumbersome conversions and calculations to resolve the information into something meaningful.
Jason Strate
Chapter 6. Indexing Memory-Optimized Tables
Abstract
The past few chapters have focused on specialized indexing-related data types in SQL Server. SQL Server also offers specialized tables that reside in memory called memory-optimized, or in-memory, tables. Introduced in SQL Server 2014, these tables reside fully within memory while SQL Server is running relying on disk-based structures only for ensuring the ability to recover from service restarts.
Jason Strate
Chapter 7. Full-Text Indexing
Abstract
SQL Server supports mechanisms that allow you to store large amounts of unstructured text information. Since SQL Server 2008, one of those mechanisms has been the MAX lengthused with the variable-length character data types VARCHAR and NVARCHAR. This means you can store up to 2 GB worth of character information within a single column. While SQL Server can store this information, the 1,700-byte limit for nonclustered indexes and 900-byte limit on clustered indexes make indexing through traditional means a challenge. Fortunately, SQL Server offers another indexing mechanism for searching within these large data types, where full-text indexing comes into play.
Jason Strate
Chapter 8. Indexing Myths and Best Practices
Abstract
In the past few chapters, we’ve defined indexes and showed how they are structured. In the upcoming chapters, you’ll be looking at strategies to build indexes and ensure that they behave as expected. In this chapter, we’ll dispel some common myths and show how to build the foundation for creating indexes.
Jason Strate
Chapter 9. Index Maintenance
Abstract
Like anything in life, indexes require maintenance. Over time, the performance benefits of indexes can wane or, through data modifications, their sizes and the underlying statistics can drift and bloat. To prevent these issues, indexes must be maintained. If you do so, your database will remain a lean, mean query-running machine.
Jason Strate
Chapter 10. Indexing Tools
Abstract
When it comes to indexing, Microsoft currently has two tools built into SQL Server that can be used to help identify indexes that can improve database performance. These are the missing index dynamic management objects (DMOs) and the Database Engine Tuning Advisor (DTA). Both tools are useful to assist with indexing databases and can provide valuable input when working on tuning a database. Microsoft is developing a new Automatic Index Management tool, which is currently available in Azure SQL Database. This is not included in SQL Server 2019 at the time of this writing.
Jason Strate
Chapter 11. Indexing Strategies
Abstract
Indexing databases is often thought of as an art where the database is the canvas and the indexes are the paints that come together to form a beautiful tapestry of storage and performance. A little color here, a little color there, and paintings will take shape. In much the same way, a clustered index on a table and then a few nonclustered indexes can result in screaming performance as beautiful as any masterpiece. Going a little too abstract or minimalist with your indexing might make you feel good, but the performance will let you know it isn’t too useful.
Jason Strate
Chapter 12. Query Strategies
Abstract
In the previous chapter, we looked at strategies to identify potential indexes for your databases. That, though, is often only half the story. Once the indexes have been created, you would expect performance within the database to improve, leading you then to the next bottleneck. Unfortunately, coding practices and selectivity can sometimes negatively influence the application of indexes to queries. And sometimes how the database and tables are being accessed will prevent the use of some of the most beneficial indexes in your databases.
Jason Strate
Chapter 13. Monitoring Indexes
Abstract
Throughout this book, we’ve discussed what indexes are, what they do, patterns for building them, and many other aspects for determining how a SQL Server database should be indexed. All of that information is necessary for the final piece in indexing your databases, analyzing your databases to determine which indexes are required. For this, this chapter and the two following will pull together the information we need to implement an indexing methodology.
Jason Strate
Chapter 14. Index Analysis
Abstract
In the previous chapter, we discussed what information should be collected when monitoring indexes. All of that information is necessary for the next piece of indexing your databases, which is determining which indexes to apply. In this chapter, we will take all the information gathered while monitoring and use it to analyze the state of performance and the value of the existing indexes. The end goal of the index analysis is to build a list of indexes to create, modify, and, potentially, drop from the databases. In many cases, the analysis of the indexes will appear to border on art. There are many decisions in which you will use previous performance to anticipate future indexing needs. In the end, though, with every change proposed, there will be supporting evidence before and after the indexing solutions to statistically support or disprove the value of the index which makes index analysis more science than art.
Jason Strate
Chapter 15. Indexing Methodology
Abstract
Throughout this book, we’ve discussed what indexes are, what they do, patterns for building them, and many other aspects for determining how a SQL Server database should be indexed. All that information is necessary for the last piece in indexing your databases, which is a methodology for managing indexes. To do this, you need a process for applying that knowledge to determine the indexes that are best for your environment and provide the greatest gain to performance.
Jason Strate
Backmatter
Metadaten
Titel
Expert Performance Indexing in SQL Server 2019
verfasst von
Jason Strate
Copyright-Jahr
2019
Verlag
Apress
Electronic ISBN
978-1-4842-5464-6
Print ISBN
978-1-4842-5463-9
DOI
https://doi.org/10.1007/978-1-4842-5464-6

Premium Partner

    Bildnachweise