You can optimize the query 1 by putting data, logs and indexes on different I/O devices to improve the reading speed. Tempdb should have been put on RAID0 before, but SQL2000 was not supported. The greater the amount of data (size), the more important it is to improve I/O. Divide the table vertically and horizontally to reduce the table size (sp_spaceuse) 3. Upgrade the hardware. Establish indexes according to query conditions, optimize indexes, optimize access methods, and limit the data volume of result sets. Pay attention to the appropriate fill factor (it is best to use the default value of 0). The index should be as small as possible. Use small byte columns to build indexes (refer to index creation). Do not create a single index for fields with limited values, such as gender field 5. Increase the network speed; 6. Expand the memory of the server. Windows 2000 and SQL server 2000 can support 4-8G of memory. Configure virtual memory: The size of virtual memory should be configured according to the services running concurrently on the computer. Running Microsoft SQL Server? In 2000, consider setting the virtual memory size to 0.5 times of the physical memory installed in the 65438+ computer. If the full-text retrieval function is additionally installed, and you plan to run the Microsoft search service to perform full-text indexing and queries, consider configuring the virtual memory size to be at least 3 times the physical memory installed in your computer. Configure the SQL Server maximum server memory server configuration option to 1.5 times physical memory (half of the virtual memory size setting). 7. increase the number of server CPUs; But it must be understood that parallel processing and serial processing need more resources, such as memory. Whether to use parallel or serial operation is automatically evaluated and selected by MsSQL. A single task can be broken down into multiple tasks, so it can be run on the processor. For example, delaying the simultaneous execution of query sorting, linking, scanning and grouping by statements, SQL SERVER determines the optimal parallel level according to the system load, and complex queries that consume a lot of CPU are most suitable for parallel processing. However, the update operations Update, INSERT and DELETE cannot be processed in parallel. 8. If you use like to query, it is not enough to simply use index, while full-text indexing consumes space. Like 'a%' uses an index similar to' %a' without an index. When querying with like "%a%", the query time is proportional to the total length of field values, so you can't use CHAR type, but use VARCHAR. Build a full-text index for long field values. 9. Separation of database server and application server; OLTP and OLAP are separated by 10, and the database server consortium can be realized by distributed partition view. A consortium is a group of servers managed separately, but they cooperate with each other to share the processing load of the system. This mechanism of forming a database server alliance by partitioning data can expand a group of servers to support the processing needs of large multi-tier websites. For more information, see Designing a Federated Database Server. (refer to SQL help file' partitioned view') A. Before implementing partitioned view, table B must be partitioned horizontally. After creating the member table, define a distributed partitioned view on each member server, and each view has the same name. In this way, queries referencing the names of distributed partitioned views can be run on any member server. The system operates as if there is a copy of the original table on each member server, but in fact there is only one member table and one distributed partitioned view on each server. The location of the data is transparent to the application. 1 1, rebuild index DBCC REINDEX, DBCC INDEXDEFRAG, shrink data and log DBCC SHRINKDB, DBCC SHRINKFILE. Set the automatic shrink log. For large databases, do not set the database to grow automatically, which will reduce the performance of the server. The writing of T-sql is very particular, and the common points are listed as follows: 1. The process of DBMS processing query plan is as follows:

1. Lexical and grammatical check of query statement 2. Submit the statement to the query optimizer of DBMS 3. The optimizer performs algebraic optimization and access path optimization 4. The query plan is generated by the precompiling module 5. Then submit it to the system for processing and execution at an appropriate time. 6. Finally, the execution result is returned to the user. Secondly, look at the data storage structure of SQL SERVER: the size of one page is 8K(8060) bytes, and eight pages are a panel, which is stored according to B-tree. 12, the difference between commit and rollback: rollback everything. Submit: Submit the current affairs. You don't need to write in dynamic SQL. If you want to write something, please write it outside, such as begin tran exec(@s) commit trans or write dynamic SQL as a function or stored procedure. 13. In the query Select statement, the number of rows returned is limited by the Where clause to avoid table scanning. If unnecessary data is returned, I/O resources of the server will be wasted, network burden will be increased and performance will be reduced. If the table is very large, it will be locked in the process of table scanning, and other connections are prohibited from accessing the table, which will have serious consequences. 14, SQL comment statement has no effect on execution 15, try not to use cursor, it takes up a lot of resources. If you need to execute line by line, try to use non-cursor technology, such as looping on the client, using temporary tables, table variables, using subqueries, using Case statements and so on. Cursors can be classified according to the extraction options supported by cursors: only forward must extract rows from the first row to the last row. FETCH NEXT is the only allowed fetch operation and the default mode. Scrollability can randomly select any line at any position of the cursor. Cursor technology has become very powerful under SQL2000, and its purpose is to support loops.

There are four concurrency options READ_ONLY: update by cursor positioning is not allowed, and there are no locks in the rows that make up the result set. Optimal value: optimistic concurrency control is a standard part of transaction control theory. Open concurrency control is used when the second user has little chance to update the row between opening the cursor and updating the row. When using this option to open a cursor, there is no lock to control the rows in it, which will help to maximize its processing power. If the user tries to modify a row, the current value of the row will be compared with the value obtained when the row was last extracted. If any value changes, the server will know that someone else has updated the row and will return an error. If the values are the same, the server will perform the modification. Select this concurrency option? Best row versioning: This optimistic concurrency control option is based on row versioning. For row versioning, the table must have a version identifier that the server can use to determine whether the row has changed since the cursor was read. In SQL Server, this performance is provided by the timestamp data type, which is a binary number indicating the relative order of changes in the database. Each database has a global current timestamp value: @@DBTS. Whenever a row with a timestamp column changes in any way, SQL Server first stores the current @@DBTS value in the timestamp column, and then increases the value of @@DBTS. If a table has a timestamp column, the timestamp is recorded at the row level. The server can compare the current timestamp value of the row with the timestamp value stored during the last extraction to determine whether the row has been updated. The server does not have to compare the values of all columns, only the timestamp columns. If the application requires open concurrency based on row versioning for tables without timestamp columns, the cursor defaults to value-based open concurrency control. Scroll LockThis option enables pessimistic concurrency control. In pessimistic concurrency control, when a database row is read into the cursor result set, the application will try to lock the database row. When a server cursor is used, an update lock is placed on the row when it is read into the cursor. If the cursor is opened in a transaction, the transaction update lock will remain until the transaction is committed or rolled back. When you select the next line, the cursor lock will be removed. If the cursor is opened outside the transaction, the lock will be discarded when the next row is fetched. Therefore, whenever users need completely pessimistic concurrency control, they should open cursors within transactions. Updating the lock will prevent any other task from acquiring the update lock or exclusive locks, thus preventing other tasks from updating the row. However, updating the lock does not prevent * * * from enjoying the lock, so it will not prevent other tasks from reading the row unless the second task also requests to use the update lock for reading. Rolling LockAccording to the lock hints specified in the SELECT statement of cursor definition, these cursor concurrency options can generate rolling locks. When extracting, the scroll lock will be acquired on each line, and it will remain until the next extraction or cursor closing, whichever occurs first. In the next extraction, the server acquires the scroll lock of the newly extracted row and releases the scroll lock of the last extracted row. Rolling locks are independent of transaction locks and can be maintained after commit or rollback operations. If the option to close the cursor at COMMIT time is off, the commit statement will not close any open cursors, and the scroll lock will be retained after commit to keep the extracted data isolated. The type of scroll lock obtained depends on the cursor concurrency option and the lock hint in the cursor SELECT statement. Lock prompt read-only optimistic numerical optimistic line version control lock silent unlock unlock unlock update NOLOCK unlock unlock hold lock * * * Enjoy * * Enjoy update UPDLOCK error update TABLOCKX error unlock unlock update other unlock unlock unlock unlock unlock update * Specifying NOLOCK prompt will make the table with specified prompt read-only in the cursor.

16, use Profiler to track the query, get the time needed for the query, and find out the problems of SQL; Optimize the index 17 with the index optimizer, and pay attention to the difference between UNion and UNion all. The combination is good 18. Pay attention to using DISTINCT, and don't use it when it is unnecessary. It will slow down the query like UNION. There are no duplicate records in the query. 19. Don't return unnecessary rows and columns when querying. Please use sp _ configure' query governor cost limit' or SET QUERY_GOVERNOR_COST_LIMIT to limit the resources consumed by the query. When the resource consumed by the evaluation query exceeds the limit, the server automatically cancels the query and kills it before the query. Set the lock time To set the lock time to 2 1, use select top 100/ 10% to limit the number of rows returned by the user, or set ROWCOUNT to limit the number of rows operated. 22. Before SQL2000, the following words were generally not used: "is null"