SQL Server Optimization data collation (ii)

stored procedure authoring experience and optimization measures

One, suitable for the reader object: Database development programmer, database of a lot of data, involving the SP (stored procedure) optimization of project developers, the database has a strong interest in people.

Second, the introduction: In the database development process, often encounter complex business logic and database operations, this time will use the SP to encapsulate the database operations. If the project SP more, writing without certain specifications, will affect the future of the system maintenance difficulties and the large SP logic is difficult to understand, in addition, if the data volume of the database is large or the performance requirements of the SP is very high, you will encounter the problem of optimization, otherwise the speed may be very slow, through personal experience, An optimized SP is hundreds of times times more efficient than a poorly performing sp.

Third, the content:

1, if the developer uses the other library table or view, be sure to create a view in the current library to achieve cross-library operations, it is best not to directly use "Databse.dbo.table_name", because sp_ Depends cannot display the cross-Library table or view used by the SP, and is not easy to verify.

2, the developer before submitting the SP, must already use set SHOWPLAN on to analyze the query plan, has done its own query optimization check.

3, high program operation efficiency, optimize the application, in the SP writing process should pay attention to the following points:

A) Usage specification for SQL:

I. Try to avoid large business operations, and use HOLDLOCK clauses with caution to improve the concurrency capability of the system.

II. Try to avoid repeated access to the same or several tables, especially those with a large amount of data, you might consider extracting the data into a temporary table based on conditions before making a connection.

III. Avoid using cursors as much as possible, because cursors are inefficient and should be overwritten if the cursor is operating with more than 10,000 rows of data, and if a cursor is used, try to avoid the operation of table joins in the cursor loop.

Iv. Note the where sentence is written, you must consider the order of the statements, should be based on the index order, the range size to determine the order of the condition clauses, as far as possible to make the field order and index order consistent, ranging from large to small.

V. Do not perform functions, arithmetic operations, or other expression operations on the left side of the "=" in the WHERE clause, or the index may not be used correctly by the system.

VI. Try to use exists instead of select COUNT (1) to determine if a record exists, the Count function is used only for all rows in the statistics table, and COUNT (1) is more efficient than count (*).

VII. Use ">=" as much as possible and do not use ">".

Viii. note the substitution between some or clauses and the Union clause

IX. Note the data types that are connected between tables to avoid connections between different types of data.

X. Note the relationship of parameters and data types in the stored procedure.

XI. Note the amount of data in the insert and update operations to prevent conflicts with other applications. If the amount of data exceeds 200 data pages (400k), the system will be locked and the page-level lock will be upgraded to a table-level lock.

b) Index usage specification:

I. The creation of an index to be considered in conjunction with an application, it is recommended that a large OLTP table not exceed 6 indexes.

II. Use indexed fields as much as possible as query criteria, especially clustered indexes, and, if necessary, by using the index index_name to force the index to be specified

III. Avoid table scan when querying large tables, and consider creating new indexes if necessary.

Iv. when using an indexed field as a condition, if the index is a federated index, you must use the first field in the index as a condition to guarantee that the system uses the index, otherwise the index will not be used.

V. To be aware of the maintenance of the index, periodically rebuild the index and recompile the stored procedure.

c) Usage specification for tempdb:

I. Try to avoid using distinct, order BY, group BY, have, join, Cumpute, because these statements aggravate the burden of tempdb.

II. Avoid frequent creation and deletion of temporary tables, reducing the consumption of system table resources.

III. When creating a temporary table, if you insert a large amount of data at one time, you can use SELECT INTO instead of CREATE table, avoid log, increase the speed, and if the amount of data is small, it is recommended to create table and insert in order to mitigate the resources of the system tables.

Iv. if the data volume of the staging table needs to be indexed, then the process of creating the staging table and indexing should be placed in a single sub-stored procedure, in order to ensure that the system can use the index of the temporary table well.

V. If a temporary table is used, be sure to explicitly delete all temporary tables at the end of the stored procedure, TRUNCATE table first, and then drop table, which avoids longer locking of the system tables.

VI. Careful use of large temporary tables and other large table connection query and modification, reduce the system table burden, because this operation will be in one statement multiple times the system table of tempdb.

d) Rational use of the algorithm:

Based on the SQL optimization techniques mentioned above and the SQL optimization content in the ASE Tuning manual, a variety of algorithms are used to compare with each other in order to obtain the least cost and most efficient method. Specific ASE tuning commands are available: SET STATISTICS IO on, SET statistics time on, set Showplan on, and so on.

Parsing: Lock mode in Microsoft SQL Server
When locking in a SQL Server database, in addition to the different resources can be locked, you can also use different degrees of lock mode, that is, there are several modes of lock, SQL Server lock mode includes:

1. Shared locks are used for all read-only data operations in SQL Server. Shared locks are non-exclusive and allow multiple concurrent transactions to read their locked resources. By default, when data is read, SQL Server immediately releases the shared lock. For example, when you execute a query "select * from AUTHORS", first lock the first page, after reading, release the lock on the first page, and then lock the second page. This allows you to modify the first page that is not locked during a read operation. However, the Transaction Isolation level connection option settings and the lock settings in the SELECT statement can change this default setting for SQL Server. For example, "SELECT * from AUTHORS HOLDLOCK" requires that the table be locked during the entire query process until the query is complete before releasing the lock.

2. The update lock update lock is used during the initialization phase of the modify operation to lock resources that might be modified, which avoids the deadlock caused by the use of shared locks. Because a shared lock is used, the operation to modify the data is divided into two steps, first obtaining a shared lock, reading the data, and then upgrading the shared lock to an exclusive lock before performing the modify operation. This way, if there are two or more transactions concurrently requesting a shared lock on a transaction, these transactions will be upgraded to exclusive locks when the data is modified. At this point, these transactions do not release the shared lock but wait for the other party to release, which creates a deadlock. If a data is directly requested to update the lock before modification, it can avoid deadlock if it is upgraded to exclusive lock when the data is modified.

3. Exclusive lock locks are reserved for modifying data. The resources it locks, other transactions cannot be read, and cannot be modified.

4. A schema modification (SCH-M) lock is used when a structure lock performs a data definition language (DDL) operation on a table, such as adding a column or dropping a table. The schema stability (sch-s) lock is used when compiling the query. The schema stability (sch-s) lock does not block any transaction locks, including exclusive locks. As a result, other transactions, including those that have an exclusive lock on the table, can continue to run when the query is compiled. However, DDL operations cannot be performed on the table.

5. Intent Lock Intent Lock indicates that SQL Server has the intention of acquiring a shared or exclusive lock on the lower level of the resource. For example, a table-level shared intent lock describes a transaction intent to release an exclusive lock to a page or row in a table. The intent lock can also be divided into shared intent lock, exclusive intent lock and shared exclusive intent lock. A shared intent lock describes the transaction intent to place a shared lock on a low-level resource locked by a shared intent lock to read data. An exclusive intent lock indicates that the transaction intends to modify the data by placing an exclusive lock on the low-level resource locked by the shared intent lock. Shared exclusive lock description transactions allow other transactions to use shared locks to read top-level resources and intend to place an exclusive lock on the lower layer of the resource.

6. Bulk update lock A bulk update lock is used when you bulk copy data to a table and specify the TABLOCK hint or use sp_tableoption to set the table lock on bulk option. A bulk update lock allows a process to concurrently bulk replicate data to the same table, while preventing other processes that do not bulk copy data from accessing the table.

Detailed introduction to Optimizing Settings for SQL Server 2000
SQL Server has been well-configured to optimize its performance, much better than other relational databases in today's market. However, you still have several settings that need to be modified so that your database can handle more transactions per minute (TPM). The purpose of this article is to discuss these settings. We ignore the performance improvements that can be achieved through hardware configuration or table or index design, as these are outside the scope of this article.

Broken page Detection

Before we start talking about server configuration switches, let's take a quick look at your model database-or the template that is used as the basis for building a new database. By default, you can create stored procedures, functions, and so on in the database, and then they will be added to the newly created database.

To optimize performance, you might want to turn off broken page detection in the model database. When a page is successfully written to disk, the broken page detection is identified. If activated, you can see each write operation has a small effect on performance. Most modern disk arrays have onboard batteries that allow the array to complete all writes in the event of a sudden power outage-the most frequent cause of broken pages.

The following steps can accept how to turn off broken page detection:

exec sp_dboption ‘model‘, ‘torn page detection‘, ‘false‘


  

	
   


  

This basic knowledge resource can provide you with more information about this setting.
Most of the configuration is done through the system stored procedure sp_configure. To display a list of all the server's settings for customization, you can enter the following command:

 
 sp_configure ‘show advanced options‘, 1

  GO

  RECONFIGURE WITH OVERRIDE

The number of options you can configure depends on your version of SQL Server, the service pack, and the number of bits (64-bit SQL Server is more than 32-bit options). I'll talk directly about the options that best affect SQL Server performance tuning.

Affinity mask:affinity Mask allows you to control which processor SQL Server uses. In most cases, you should not touch this setting and let the operating system control the processor relationship. However, you might want to use this option to dedicate a processor to another process (for example, MSSearch or SQL Server disk IO, as well as the balance of SQL Server). Refer to the basic knowledge resource for more information about this setting.

The launch of Awe Enabled:awe can allow SQL Server Enterprise Edition to run on Windows 2000 and above advanced servers, or Windows 2003 Enterprise and above, with more than 4GB of memory. If your server meets these criteria, activate this setting.

Parallel cost limits: Parallel cost limits are set when queries need to be processed in parallel. The default is five seconds. Changing this value to a slightly lower value allows more queries to be processed in parallel, but this can also cause CPU bottlenecks. This setting will only work on machines with multiple processors.

Fill factor: The fill factor sets the factor that is used to auto-populate when the clustered index is created. In the frequently inserted table, set the value from the default of 90% to a lower value, and you get the benefit.

Lightweight buffer pool: This setting starts the fibre mode. Use this option on 8-way and above servers with high CPU utilization. This allows the fiber to serve each thread at the same time, while running on each processor by default. Some tasks can benefit from these fibers.

Maximum parallelism: This setting is determined when the server can use parallelism or cannot use parallelism, or when a number of processors can be used for parallel operations. Parallelism is where multiple processing occurs on multiple processors. For example, a parallel operation of a query can be processed concurrently on a different processor.

Server maximum Memory (MB): If you run other processing on SQL Server and have enough memory, then you may want to set aside 512MB of memory for the operating system and these processes. For example, you can set it to 512 in MSSearch or by running a large number of agents locally.
Maximum worker thread: The maximum worker thread setting is somewhat similar to the connection pool in ADO. With this setting, any user connection that exceeds the limit (255 users) can wait in the thread pool until the threads for a connection service are freed, as if the connection in ADO is shared with the connection pool. If you have a lot of connections, and a lot of memory, then you can increase this value.

Network packet Size (B): This setting controls the size of the package that is transferred to your client in the network. In a lossy network (such as a phone line), you might want to set this parameter to a lower value, with the ink value being 4096. In a well-connected network, you can improve this setting, especially for large batch operations involving blobs.

Priority advance: This setting provides the processor push for SQL Server. In Task Manager, click the Process tab, locate the location of SQL Server, and then right-click it. Select Set priority level. Note that SQL Server should run at a normal priority level. Enter the following command:

1 sp_configure ' priorityboost', 1 2 3 Reconfigure with override

Then restart your SQL Server. In Task Manager, look at what priority SQL Server is now running on. It should be on a high priority level. SQL Server should have a higher priority than other user processes. Use this setting on servers that are dedicated to SQL Server.

Summarize

This article discusses the most common SQL Server optimization settings. It is important to have baseline determination in the test environment before and after making the change, so that you can assess the impact on your system under a typical load.
Analysis of deadlocks in SQL Server database

A deadlock in a SQL Server database does not automatically generate a trace file as Oracle does. Blocking information can sometimes be seen in [managing]->[Current Activity] (sometimes SQL Server Enterprise Manager does not respond because of too many locks).

Set Trace 1204:

1 use MASTER 2 3 DBCC TRACEON (1204,-1) /c3>

Displays the status of all trace flags that are currently enabled:

DBCC Tracestatus (-1)

To cancel Trace 1204:

DBCC Traceoff (1204,-1)

After you set trace 1204, some information about the SQL Server database deadlock is displayed in the log file of the database. But that information is hard to read, and needs to be looked at with SQL Server Books Online. Depending on the PAG lock to find the relevant database table method:

DBCC TRACEON (3604)
DBCC PAGE (Db_id,file_id,page_no)
DBCC Traceoff (3604)

Please refer to the sqlservercentral.com for more detailed explanation. But from Csdn learned a way to find the cause of the deadlock. I slightly modified, removed the cursor operation and added some hint information, wrote a system stored procedure sp_who_lock.sql. The code is as follows:

 
 
1 if exists (select * from dbo.sysobjects
2 where id = object_id(N‘[dbo].[sp_who_lock]‘)
3 and OBJECTPROPERTY(id, N‘IsProcedure‘) = 1)
4 drop procedure [dbo].[sp_who_lock]

Call directly when needed:

Sp_who_lock

You can isolate the process that caused the deadlock and the SQL statement.

SQL Server comes with system stored procedures sp_who and sp_lock can also be used to find blocking and deadlocks, but not the methods described here are useful. If you want to know what other tracenum parameters mean, see the www.sqlservercentral.com article

We can also set the time-out (in milliseconds) of a lock to shorten the timeframe that the deadlock might affect:

For example:

 
 
1 use master
2 seelct @@lock_timeout
3 set lock_timeout 900000
4 -- 15分钟
5 seelct @@lock_timeout

Tips for optimizing SQL Server indexing
There are several tools in SQL Server that allow you to detect, tune, and optimize SQL Server performance. In this article, I'll show you how to use the tools of SQL Server to optimize the usage of database indexes, and this article also covers general knowledge about indexes.

Common Sense About indexes

The biggest factor affecting database performance is the index. Because of the complexity of the problem, I can only talk about it briefly, but there are several good books available for you to refer to in this regard. I'm here to discuss only two types of SQL Server indexes, the clustered index and the nonclustered index. When examining what type of index to build, you should consider the data type and the column that holds the data. Similarly, you must also consider the types of queries the database may use and the most frequently used query types.

Type of index

If column holds highly relevant data and is often accessed sequentially, it is best to use the clustered index, because if you use the clustered index, SQL Server will physically rearrange the data columns in ascending (default) or descending order. This makes it possible to quickly find the data being queried. Similarly, the clustered index is best used for these column cases where the search control is within a certain range. This is because, because of the physical rearrangement of data, there is only one clustered index on each table.

In contrast to the above, if columns contains poor data correlation, you can use the nonculstered index. You can use up to 249 nonclustered indexes in a single table--although I can't imagine how many indexes can be used in real-world applications.

When the table uses the primary key (primary keys), by default SQL Server automatically establishes a unique cluster index on column (s) that contains the keyword. Obviously, establishing a unique index on these column (s) means that the primary key is unique. If you intend to use it frequently when creating a foreign keyword (foreign key) relationship, it is a good idea to build a nonclustered index on the external keyword cloumn. If the table has a clustered index, it uses a linked list to maintain the relationship between the data pages. Conversely, if the table does not have a clustered index, SQL Server saves the data page in a stack.

Data page

When the index is set up, SQL Server creates a data page (DataPage), which is a pointer to speed up the search. When the index is set up, its corresponding fill factor is set. The fill factor is set to indicate the percentage of the data page in the index. Over time, updates to the database consume existing free space, which causes the page to be split. The consequence of page splitting is that the performance of the index is reduced, so queries using that index can result in fragmentation of the data store. When an index is established, the fill factor for the index is set so that the fill factor cannot be maintained dynamically.

In order to update the fill factor in the data page, we can stop the old index and rebuild the index, and reset the fill factor (note: This will affect the current database operation, please use caution in important situations). DBCC INDEXDEFRAG and DBCC DBREINDEX are two commands to clear clustered and nonculstered index fragmentation. Indexdefrag is an online operation (that is, it does not block other table actions, such as queries), while Dbreindex rebuilds the index physically. In the vast majority of cases, rebuilding an index can better eliminate fragmentation, but this advantage is in exchange for blocking the other actions that currently occur on the table where the index is located. When a large fragmented index occurs, Indexdefrag takes a long time because the command is run based on a small interaction block (transactional block).

Fill factor

When you perform any of the above measures, the database engine can return the indexed data more efficiently. The topic of fill factor (FILLFACTOR) is beyond the scope of this article, but I would like to remind you that you need to be aware of the tables that you intend to use to index the fill factor.

SQL Server dynamically chooses which index to use when executing the query. To do this, SQL Server determines which index to use based on the statistics that are distributed on that keyword on each index. It is important to note that, after daily database activity such as inserting, deleting, and updating tables, these statistics that SQL Server uses may have been "out of date" and need to be updated. You can view the status of the statistics by executing DBCC SHOWCONTIG. When you think that the statistic has "expired", you can execute the table's UPDATE STATISTICS command so that SQL Server refreshes the information about the index.

Establish a Database maintenance plan

SQL Server provides a tool for simplifying and automating database maintenance. This tool, called the Database Maintenance Plan Wizard, DMPW, also includes optimizations for indexes. If you run this wizard, you will see statistics about the indexes in the database, which work as logs and are updated periodically, thus reducing the amount of manual rebuilding of the index. If you don't want to automatically refresh index statistics periodically, you can also select reorganize data and data pages in DMPW, which stops the old index and rebuilds the index by a specific fill factor.
use and optimization of Sybase SQL Server Indexes
In the application system, especially in the online transaction processing system, the data query and processing speed has become the standard to measure the success or failure of the application system. The use of index to speed up the data processing speed has become the majority of database users to accept the optimization method.

On the basis of good database design, the efficient use of indexes is the foundation of SQL Server's high performance, and SQL Server uses a cost-based optimization model, which determines whether indexes or indexes are used for each submitted query about the table. Because most of the overhead of query execution is disk I/O, one of the primary goals of using indexes to perform high performance is to avoid full table scans, because full table scans require reading every data page of a table from disk, and if an index points to a data value, the query needs to read the disk several times. So if a reasonable index is established, the optimizer can use the index to speed up the query process of the data. However, indexes do not always improve the performance of the system, and the presence of indexes in the increment, delete, and change operations increases a certain amount of work, so it will help to optimize those poorly performing SQL Server applications by increasing the appropriate indexes and removing suboptimal indexes from places where appropriate. The practice shows that the reasonable index design is based on the analysis and prediction of various queries, and only when the index is correctly combined with the program can the optimal scheme be produced. This article has carried on some analysis and the practice to the SQL Server index performance question.

First, the use of clustered index (clustered indexes)

A clustered index is a sort of re-organization of the actual data on disk to the value of one or more of the specified columns. Because the index page pointer to the clustered index points to the data page, finding data using a clustered index is almost always faster than using a nonclustered index. Only one clustered index can be built per table, and a clustered index requires at least the additional space of the table 120% to hold a copy of the table and an intermediate page of the index. The idea of building a clustered index is:

1, most tables should have clustered index or use partition to reduce the competition for the end of the table, in a high transaction environment, the last page of the blockade severely affect the throughput of the system.

2, in the clustered index, the data is physically sorted on the data page in order, and duplicate values are also grouped together, so that when you include the scope check (between, <, <=, & gt;, > =) or use a group by or order by query, Once a row with the first key value in the range is found, rows with subsequent index values are guaranteed to be physically contiguous together without further searching, avoiding a wide range of scans, which can greatly improve query speed.

3. When a clustered index is established on a table with frequent insert operations, do not build on a column with a monotonically rising value (such as identity), or it will often cause a blocking conflict.

4. Do not include frequently modified columns in the clustered index, because the data rows must be moved to a new location after the code value has been modified.

5. Select the clustered index should be based on the WHERE clause and the type of the join operation. The candidate columns for the clustered index are:

Primary key column, which is used in the WHERE clause and inserted randomly.

Columns accessed by range, such as Pri_order > Pri_order < 200.

The column used in the group by or order by.

Columns that are not frequently modified.

The columns used in the join operation.

Second, the use of non-clustered index (nonclustered indexes)

SQL Server creates an index that is not clustered by default, because the nonclustered index does not reorganize the data in the table, but instead stores the indexed column values on each row and points to the page where the data resides. In other words, non-clustered indexes have an extra level between the index structure and the data itself. A table can have 250 nonclustered indexes if there is no clustered index. Each non-clustered index provides a different sort order for accessing data. When building a nonclustered index, weigh the pros and cons of the index's speed to query and reduce the rate of modification. Also, consider these issues:

How much space the index needs to use.

The appropriate column is stable.

How the index key is selected, and whether the scan effect is better.

Whether there are many duplicate values.

Non-clustered indexes on tables require more overhead than clustered indexes and no indexes at all for frequently updated tables. For each row that is moved to a new page, the page-level rows for each nonclustered index that points to the data must also be updated, and sometimes the indexing page may need to be split. The process of deleting data from one page also has a similar overhead, and the removal process must also move the data to the top of the page to ensure the continuity of the data. Therefore, it is very prudent to build non-clustered indexes. Non-clustered indexes are often used in the following situations:

A column is commonly used for aggregate functions, such as Sum,.....

A column is commonly used in Join,order by,group by.

The data found does not exceed 20% of the amount of data in the table.

Third, the use of coverage index (covering indexes)

An overlay index is a non-clustered index that contains all the information needed for a search in an index entry, which is faster because the index page contains the data necessary for the lookup and does not require access to the data page. If the nonclustered index contains the result data, its query speed will be faster than the clustered index.

However, because the index entries of the overlay index are much larger, it takes up more space. And the update operation causes the index value to change. Therefore, if a potential overwrite query is not commonly used or is not critical, the increase in the overlay index will degrade performance.

Iv. Selection Techniques of indexes

P_detail is the housing Accumulation fund Management system records personal details of the table, there are 890000 rows, observe the results of the query run under different indexes, the test in the C/s environment, the client is IBM PII350 (memory 64M), the server is Dec alpha1000a (Memory 128M ), the database is SYBASE11.0.3.

1. Select COUNT (*) from P_detail where op_date> ' 19990101 ' and op_date< ' 19991231 ' and pri_surplus1>300

2. Select COUNT (*), SUM (PRI_SURPLUS1) from P_detail where op_date> ' 19990101 ' and pay_month between ' 199908 ' and ' 199912 '

Do not build any index query 1 1 minutes 15 seconds

Enquiry 2 1 minutes 7 seconds

Non-clustered index query on Op_date 1 57 seconds

Query 2 57 seconds

Build clustered index query on Op_date 1 <1 sec

Query 2 52 seconds

Indexed queries on Pay_month, Op_date, Pri_surplus1 1 34 seconds

Query 2 <1 sec

Indexed queries on Op_date, Pay_month, Pri_surplus1 1 <1 seconds

Query 2 <1 sec

From the above query effect analysis, the index is not, the establishment of different ways will lead to different query results, choose what kind of index based on the user's query criteria for the data, these conditions are reflected in the WHERE clause and the join expression. In general, the idea of indexing is:

(1), the primary key is often used as the condition of the WHERE clause, the clustered index should be established on the table's primary key column, especially if it is used as a connection.

(2), there are a large number of duplicate values and often have a range of queries and sorting, grouping occurrences of columns, or very frequently accessed columns, you can consider establishing a clustered index.

(3), frequent simultaneous access to multiple columns, and each column contains duplicate values consider building a composite index to cover one or a set of queries, and the most frequently referenced columns as leading columns, if possible to make the critical query form an overlay query.

(4), if you know that all the values of the index key are unique, be sure to define the index as a unique index.

(5) When an index is built on a table that is often inserted, the FILLFACTOR (fill factor) is used to reduce page splitting, while increasing the concurrency decreases the deadlock. If you build an index on a read-only table, you can set FILLFACTOR to 100.

(6), when selecting the index key, try selecting those columns that take small data types as keys to make each cable

The page can accommodate as many index keys and pointers as possible, which minimizes the index pages that a query must traverse. Also, use as many as possible the integer as the key value, as it can provide faster access than any data type.

V. Maintenance of the Index

As mentioned above, some unsuitable indexes affect the performance of SQL Server, and as the application runs, the data changes constantly, which affects the use of the index when the data changes to a certain extent. This requires the user to maintain the index themselves. The maintenance of the index includes:

1. Rebuilding the Index

As data rows are inserted, deleted, and data pages split, some index pages may contain only a few pages of data, and when applied to large chunks of I/O, rebuilding a nonclustered index can reduce fragmentation and maintain the efficiency of large chunks of I/O. Rebuilding an index is actually a re-organization of B-tree space. The index needs to be rebuilt under the following conditions:

(1), data and usage patterns vary significantly.

(2), the order of sequencing changes.

(3), for a large number of inserts or has been completed.

(4), queries with large chunks of I/O have more disk reads than expected.

(5), because of a large number of data modifications, so that the data pages and index pages are not fully used, resulting in the use of space beyond estimation.

(6), DBCC check out the index has a problem.

When the clustered index is rebuilt, all nonclustered indexes on this table will be

Built.

2. Update of index statistic information

When an index is created on a table that contains data, SQL Server creates a distributed data page to hold two statistics about the index: the distribution table and the density table. The optimizer uses this page to determine whether the index is useful for a particular query. However, this statistic is not dynamically recalculated. This means that when the table's data changes, the statistics may be outdated, affecting the optimizer's quest for the most working target. Therefore, you should run the UPDATE STATISTICS command under the following circumstances:

(1), inserting and deleting data rows modifies the distribution of the data.

(2) Add data rows to the table where data is deleted with TRUNCATE table.

(3), modify the value of the indexed column.

Vi. concluding remarks

The practice shows that inappropriate indexes not only help but reduce the execution performance of the system. Because a large number of indexes cost more system time than no indexes when inserting, modifying, and deleting operations. For example, an index created under the following circumstances is inappropriate:

Columns that are rarely or never referenced in a query do not benefit from the index because the indexes are few or never necessary to search for rows based on those columns.

Columns with only two or three values, such as men and women (yes or no), will never benefit from the index.

In addition, because the index speeds up the query, it slows down the speed of data updates. By building a table on one segment, and building its nonclustered index on another segment, these two sections improve operational performance on separate physical devices.

SQL Server Optimization data collation (ii)