Description
The Indexing Strategy and Maintenance is extremely important for the performance and scalability. Having the correct number of appropriate indexes for all the workloads is critical for performance and scalability. It is one of the most important thing in DBA and contain much more control over the indexing strategy as a DBA and even with the third party applications.
Index maintenance considerations are also very important and has control over the amount of index fragments, but also want to avoid excessive index maintenance and putting a lot of extra load on the system.Index maintenance creates more load on the system in terms of memory pressure, I/O pressure, CPU pressure and transaction log generations. And this is especially important while performing database mirroring on the available groups or even with the transaction log shipping.
Description
Some of the common indexing issues in the SQL Server are:
- In SQL Server it contains too many indexes.
- It is having high impact on missing indexes.
- It contain database tuning adviser issues.
- SQL Server is having poorly maintained indexes.
- Doing brute force,unnecessary index maintenance.
- SQL Server is using the SQL maintenance plan wizard.
- SQL Server has a common XML index issues.
- SQL Server has a common spatial index issues.
- SQL Server is not using data compression where it is appropriate.
- It is not using column store index where they are appropriate.
Description
There are a number of issues with the database tuning adviser. The Database tuning adviser can actually be dangerous and never point it at alive production database directly from the DTA GUI. And can creates an extra workload and creates hypothetical indexes during the tuning process that could affected the workload.
Database tuning adviser issues is much better and safer to use the data commands line utility and use a test server and XML input file to read hardware information along with the metadata and statistics from the production server. And database tuning adviser issues can also capture a trace and replay it on a test server. And can also use a copy of the production data on a test server that ensure a similar hardware if possible. And can able to restore a database backup rather than copying data.
Description
It is very common to do unnecessary index maintenance,especially if some one has used the SQL maintenance plan wizards or if they have written their own index maintenance scripts.Indexes may be blindly rebuilt or re-organised regardless of the need for creating extra I/O, memory and CPU pressure.
Indexes creates extra transaction log activity and at the same time it can create extra load for most HA/DR technologies, such as database mirroring, always on the availability groups,replications and log shipping. These indexes will make sure of checking the fragmentation level before starting any index maintenance operations, such as using some logic for determining what type of index maintenance is needed and check the page count before doing the index maintenance by lowering the fill factors on volatile indexes to reduce the frequency and need for index maintenance.
Description
While executing XML and SQL Server it almost common to run in some XML index issues.XML index are special indexes on XML columns in a table.They can help in optimize XQuery operation on those columns.In order to use an XML index,the base table must have a clustered index and it doesn't have any heap table.
The XML column cannot be a part of the clustered index for the base table.The primary XML index is a clustered index on the XML nodes table that provides carnality estimates for the query optimizer.The XML will decomposed to build new index rows during an insert operation so insert operation performance can definitely defined in XML indexes in place on a table.
Key Points
- Indexing Strategy and Maintenance - Useful for executing the performance in SQL Server and Scalability.
- Common indexing issues - Are the issues related to the SQL Server.
- Database tuning adviser issues - Used directly in the graphical user interface of server.