Folder SQL Server Database Index
Last updated 02 March 2021.
SQL Server Database Index - Performance troubleshooting and profiling for Dynamics NAV | BC and SQL Server
Checks SQL Server in scope for some of most common skewed Best Practices and performance issues.
Valid from SQL Server 2012 onwards. By default all databases in the SQL Server instance are eligible for the several database specific checks, and you may use the optional parameter to narrow these checks to specific databases.
Only a system administrator/local host administrator is able to perform all checks or functions.
This code and information are provided "AS IS" without warranty of any kind, either expressed or implied. Furthermore, the author or Olav Treffurt IT Consulting shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.
Before you start a query from the sql server area, it is important that you first select the database you want to process.
Open the "options" section.
Then select the desired database from the dropdown box.
All queries for databases are now queried for this database. You can change the data at any time.
Contains the following informational sections:
Under the menu item "SQL Database" -> "SQL Server Database Index" the function "Index breakdown" has been changed. The function no longer analyzes the entire database but only a specially specified table.For the parameter "ReportStyle" please enter the value 0 or 1.
In the SQL database section there are now some queries that you can filter on special table names. When you start the query and a dialog box with the "SpecifiedTable" parameters appears, you can enter the table name or leave the parameter value empty. If you confirm with okay and have inserted a table name, the SQL query will be executed specifically on this table. If you leave the parameter value blank, the SQL query will be executed for the entire database.
The filtering applies to the following SQL queries:
- SQL Server Database Index -> Simple Redundant Index Query
- SQL Server Database Index -> Volatile Indexes
- SQL Server Database Index -> Which Index Is Never Used
- SQL Server Database Index -> Overall Index Usage Reads
- SQL Server Database Index -> Index Fragmentation
- SQL Server Database Index -> Get Fill Factor for all Indexes on Database
- SQL Server Database Index -> Find Statistics Information
- SQL Server Database Index -> Fixing SQL Server Fragmentation
- SQL Server Database Index -> Indexes Script Out
|Simple Redundant Index Query||The query starts with a simple interrogation of sys.index_columns to list the columns participating in different components of the index (for example, the key columns, the included columns, the cluster key.) This example is for the key columns for every user table index.||2.0|
|Volatile Indexes||This query shows you which indexes and statistics have had the most updates, which helps you understand your workload in more detail. Understanding which tables, indexes and statistics are most volatile is useful when you are thinking about how to layout your database files, how to design and configure your storage subsystem, and how to handle your index tuning and maintenance.||2.0|
|Which Index Is Never Used||
Whenever we have discussing index maintenance, and specifically fragmentation, we always make a point of saying ‘Make sure the index is being used before doing anything about fragmentation’.
If an index isn’t being used very much, but has very low page density (lots of free space in the index pages), then it will be occupying a lot more disk space than it could do and it may be worth compacting (with a rebuild or a defrag) to get that disk space back. However, usually there’s not much point spending resources to remove any kind of fragmentation when an index isn’t being used. This is especially true of those people who rebuild all indexes every night or every week.
|Overall Index Usage Reads||Show which indexes in the current database are most active for Reads.||2.0|
To understand which index has potential or actual duplicates, look at the index_definition column. Results in this section are sorted by alphabetical order of the list of keys and includes. This sorting means that potential duplicates will be next to each other.
There are other important things you need to know about the indexes. Because of this the index_definition is prefixed with codes. Here are the meaning of the codes:
NonClustered indexes are very powerful in SQL Server. You want to have the right nonclustered indexes in place to help support queries reading table and make them faster– that has a lot of benefits, like reducing IO, CPU, and memory usage.
But on the flip side, you can easily have too much of a good thing. Duplicate indexes take up double the room in SQL Server– and even if indexes are COMPLETELY identical, SQL Server may choose to use both of them.
Duplicate indexes essentially cost you extra IO, CPU, and Memory, just the things you were trying to SAVE by adding nonclustered indexes! And that’s a little bit crazy.
Indexes diagnosed with duplicate keys have exactly that: completely duplicate keys.
Duplicate keys are a red flag, but don’t jump into action too soon. Although indexes may have duplicate keys, there are important things to be aware about.
Are the duplicates both nonclustered?You may have a case where one of your duplicates is the clustered index— and that means that index is the data in the table itself. You clearly wouldn’t want to go dropping that index unless you want to lose the whole table. You can identify a clustered index easily– it is always index id 1, and the index_definition column will contain a “[CX]”. For example this index is a clustered index and a primary key: [CX] [PK] [KEYS] BusinessEntityID There are rare cases where it is useful to have a duplicate nonclustered index on the same column as the clustering key, but this is the exception rather than the rule.) Is one of the duplicates serving as a primary key? One of your duplicates may be a primary key, in which case it is also likely worthy of special treatment. If an index is a primary key, the index_definition column will contain a “[PK]” (check out the example above). Do the indexes have different included columns? Included columns are listed in the index_definition column. They are prefixed by the term [INCLUDES] How much is each index being used? Find this from the index_usage_summary column. Even if the indexes are completely identical, SQL Server may choose to use them both and you may see usage on them. If they have different included columns, you may see a very big variation in index usage, however.
Remember, index tuning is complicated! we gives you a lot of information to help you see everything at once, but it’s up to you to decide the right thing to do.
If you’re not sure about the right course of action, step back and learn more. Don’t put together a change plan to move forward until you’re certain about the choices you’re making.
Borderline duplicate keys
Borderline duplicate keys start with the same key column, but do not have completely identical keys.
In many cases, indexes with borderline duplicate keys are very easy to combine into a single index. However, before you assume this is the right course of action, look closely at the index types and usage as we describe above. It may change your decisions.
Without going into a lot of detail, SQL Server stores data on 8KB pages. When we insert data into a table, SQL Server will allocate one page to store that data unless the data inserted is more than 8KB in which it would span multiple pages. Each page is assigned to one table. If we create 10 tables then we'll have 10 different pages.
As you insert data into a table, the data will go to the transaction log file first. The transaction log file is a sequential record meaning as you insert, update, and delete records the log will record these transactions from start to finish. The data file on the other hand is not sequential. The log file will flush the data to the data file creating pages all over the place.
Now that we have an idea of how data is stored, what does this have to do with fragmentation?
There are two types of fragmentation: Internal Fragmentation and External Fragmentation.
SQL Server Internal Fragmentation
SQL Server Internal Fragmentation is caused by pages that have too much free space. Let's pretend at the beginning of the day we have a table with 40 pages that are 100% full, but by the end of the day we have a table with 50 pages that are only 80% full because of various delete and insert statements throughout the day. This causes an issue because now when we need to read from this table we have to scan 50 pages instead of 40 which should may result in a decrease in performance.
SQL Server External Fragmentation
External Fragmentation is caused by pages that are out of order. Let's pretend at the beginning of the day we have a perfectly ordered table. During the day we issue hundreds of update statements possibly leaving some empty space on one page and trying to fit space into other pages. This means our storage has to jump around to obtain the data needed instead of reading in one direction.
Analyzing SQL Server Fragmentation
So is fragmentation an issue? We believe it is. If you can store your entire database in memory or if your database is read only then I wouldn't worry about it, but most of us don't have that luxury. we worked on lot of servers and analyzing fragmentation levels are one of the first things we look at. In fact, just by fixing fragmentation, we have saw up to 200% improvements in query performance.
|Get Fill Factor for all Indexes on Database||
The fill-factor option is provided for fine-tuning index data storage and performance. When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth. For example, specifying a fill-factor value of 80 means that 20 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying table. The empty space is reserved between the index rows rather than at the end of the index.
The fill-factor value is a percentage from 1 to 100, and the server-wide default is 0 which means that the leaf-level pages are filled to capacity.
|Most Important Miss Indexes||Another view - see "Number Miss Index DB"||2.0|
|Script out all Indexes in a Database||
Get fragmentation info for all indexes above a certain size in the current database (Index Fragmentation)
- Note: This query could take some time on a very large database
Helps determine whether you have framentation in your relational indexes and how effective your index maintenance strategy is.
|Missing Indexes||Another view - see "Number Miss Index DB"||2.0|
|Find Statistics Information||SQL Server statistics are essential for the query optimizer to prepare an optimized and cost-effective execution plan. These statistics provide distribution of column values to the query optimizer, and it helps SQL Server to estimate the number of rows (also known as cardinality). The query optimizer should be updated regularly. Improper statistics might mislead query optimizer to choose costly operators such as index scan over index seek and it might cause high CPU, memory and IO issues in SQL Server. We might also face blocking, deadlocks that eventually causes trouble to the underlying queries, resources.||2.0|
|Fixing SQL Server Fragmentation||
Understanding SQL Server Index Fragmentation
Indexes play a critical role in the performance of SQL Server applications. Indexes are created on columns in tables or views and provide a fast way to look up data based on the values of those columns. An index is an on-disk structure that consists of keys that point to one or more columns in the table or view. Indexes are stored on disk using a B-tree structure. When you create an index on a column and then search for a row based on the values in the index, SQL Server will first find that value in the index and then use the index to quickly locate the row data in the database. If you didn’t have the index, SQL Server would have to perform a table scan to locate the row data, which would require significantly more I/O and would be much slower.
Index Fragmentation Can Hinder Performance
Indexes are a great thing for relational database performance. Fragmentation happens when the order of pages in an index doesn't match the physical order in the data file. When an index is first created, there’s no fragmentation. However, as the database is used and data is inserted, updated, and deleted, the indexes become fragmented, which can degrade performance. There are two types of index fragmentation:
1. Internal Fragmentation
Internal fragmentation occurs when data pages have excessive free space. As you insert data into a table, if the data is under the SQL Server’s data page size, then SQL Server will allocate one page to store that data. Otherwise, SQL Server will allocate multiple pages to store the data, and these data pages are often not full. Deletes cause internal fragmentation by leaving empty space in the data page. For example, if a table starts off with 1,000 data pages that are 100% full, there’s no index fragmentation. But after that table is used extensively, it might have 1,200 data pages that are only 80% full. As SQL Server scans the index, it needs to do 20% more work by processing 1,200 pages instead of the original 1,000. Sparsely populated data pages also increase SQL Server’s cache size, as caching happens at the page level.
2. External Fragmentation
External fragmentation occurs when data pages are out of order. It can occur from an insert operation or an update that makes an existing record longer and an index leaf page is full and more space is required. SQL Server then performs a page split operation where it creates a new page and moves 50% of the rows from the full page to the new page. This causes logical fragmentation because the new page typically isn’t physically contiguous to the original page. External fragmentation results in increased random I/O, where SQL Server needs to read index data from many places rather than just reading the data sequentially.
List of all index & index columns in SQL Server DB.
This query can take a very long time if the database is large. Please do not perform daily business, because the performance of the database can suffer.
|Indexes Script Out||See description above.||2.0|
|Indexes Information current DB||no further description.||2.0|
|Indexes Information do not check Primary Keys||no further description.||2.0|
|Indexes Information Non Custered Indexes||no further description.||2.0|
|Indexes Information Specified Index||no further description.||2.0|
|Indexes Information Specified Table||no further description.||2.0|
|User Table Heap||Heap tables are tables without a Clustered Index. A table in SQL Server can have a Clustered Index, then it's called a Clustered Table, and without a Clustered Index, it's called a Heap Table. In a heap table, the data is not sorted in any way, it's just a pile of unordered, unstructured records.||2.0|