50 methods to skillfully optimize your sqlserver Database

There are many reasons for slow query speed. The following are common causes:
　　
1. No index or no index is used (this is the most common problem of slow query and is a defect in programming)
　　
2. Low I/O throughput, resulting in a bottleneck effect.
　　
3. the query is not optimized because no computing column is created.
　　
4. Insufficient memory
　　
5. slow network speed
　　
6. The queried data volume is too large (you can use multiple queries to reduce the data volume in other ways)
　　
7. Lock or deadlock (this is also the most common problem of slow query and is a defect in programming)
　　
8. sp_lock and sp_who are active users. The reason is that they read and write competing resources.
　　
9. Unnecessary rows and columns are returned.
　　
10. The query statement is not good and is not optimized.
You can optimize the query by using the following methods:
　　
1. Place data, logs, and indexes on different I/O devices to increase the reading speed. In the past, tempdb can be placed on raid0, which is not supported by SQL2000. The larger the data size (size), the more important it is to increase I/O.
　　
2. vertically and horizontally split the table to reduce the table size (sp_spaceuse)
　　
3. upgrade hardware
　　
4. Create an index based on the query conditions, optimize the index, optimize the access mode, and limit the data volume of the result set. Pay attention to fill factor should be appropriate (preferably use the default value 0 ). The index should be as small as possible. Use a column with a small number of bytes to create an index (refer to the index creation). Do not create a single index for fields with a limited number of values, such as gender fields.
　　
5. Improve network speed;
　　
6. Expand the server memory. Windows2000 and sqlserver2000 support 4-8 GB memory. Configure virtual memory: the virtual memory size should be configured based on services running concurrently on the computer. Run Microsoft sqlserver? 2000, you can consider setting the virtual memory size to 1.5 times the physical memory installed on your computer. If you have installed the full-text search function and intend to run the Microsoft Search Service for full-text indexing and query, consider: set the virtual memory size to at least three times the physical memory installed on the computer. Set the sqlservermaxservermemory server configuration option to 1.5 times the physical memory (half the virtual memory size ).
　　
7. Increase the number of server CPUs. However, you must understand that resources such as memory are more required for concurrent processing of serial processing. Whether to use parallelism or serial travel is automatically evaluated and selected by MSSQL. A single task is divided into multiple tasks and can be run on the processor. For example, if the sort, connection, scan, and groupby statements of delayed queries are executed simultaneously, sqlserver determines the optimal parallel level based on the system load, complex queries that consume a large amount of CPU are most suitable for parallel processing. However, update, insert, and delete operations cannot be processed in parallel.
　　
8. If like is used for query, you cannot simply use index, but full-text index consumes space. When like 'a uses the index like 'a without using the index like 'a, the query time is proportional to the total length of the field value. Therefore, the char type cannot be used, but the varchar type is not used. Create a full-text index for a long field value.
　　
9. dbserver and applicationserver are separated; OLTP and OLAP are separated.
　　
10. Distributed partition view can be used to implement Database Server consortium. A consortium is a group of servers managed separately, but they collaborate to share the processing load of the system. This mechanism of forming Database Server consortium through partition data can expand a group of servers to support the processing needs of large multi-layer Web sites. For more information, see designing a database federation server. (Refer to the SQL Help File 'partition view ')
　　
A. before implementing the partition view, a horizontal partition table must be created.
　　
B. After creating a member table, define a distributed partition view on each Member Server, and each view has the same name. In this way, queries that reference the view name of a distributed partition can run on any Member Server. System operations are the same as if each member server has a copy of the original table, but in fact each server has only one member table and a distributed partition view. The data location is transparent to the application.
　　
11. Rebuild the index dbccreindex, dbccindexdefrag, shrink data and logs dbccshrinkdb, dbccshrinkfile. Set automatic log shrinking. Do not set Automatic database growth for large databases, which will reduce server performance. There is a lot of emphasis on the writing of T-SQL, the following lists the common points: first of all, DBMS processing query plan process is like this:
　　
1. query statement lexical and syntax check
　　
2. submit the statement to the query optimizer of the DBMS.
　　
3. optimizer performs algebra optimization and access path optimization
　　
4. A query plan is generated by the Pre-compilation module.
　　
5. Then, submit it to the system for processing and execution at the appropriate time.
　　
6. Finally, return the execution result to the user. Next, let's take a look at the data storage structure of sqlserver: the size of a page is 8 K (8060) bytes, and 8 pages are a disk area, store data in the Tree B.
　　
12. Difference Between commit and rollback: Roll back all things. Commit: Submit the current transaction. There is no need to write the transaction in the dynamic SQL statement. If you want to write it, please write it out, for example, begintranexec (@ s) committrans, or write the dynamic SQL statement as a function or a stored procedure.
　　
13. Use the WHERE clause in the SELECT statement to limit the number of returned rows to avoid table scanning. If unnecessary data is returned, the server's I/O resources are wasted, this increases the burden on the network and reduces performance. If the table is large, the table is locked during the table scan and other connections are prohibited from accessing the table, resulting in serious consequences.
　　
14. SQL statement comments have no impact on execution
15. Try not to use the cursor. It occupies a large amount of resources. If row-by-row execution is required, try to use non-cursor technology, such as loop on the client, using temporary tables, table variables, subqueries, and case statements. The cursor can be classified according to the extraction options it supports: only the rows must be extracted from the first row to the last row. Fetchnext is the only promised extraction operation, which is also the default method. You can extract arbitrary rows randomly anywhere in the cursor. The cursor technology becomes very powerful in SQL2000, and its purpose is to support loops. There are four concurrent options read_only: do not promise to update through the cursor, and there is no lock in the rows that make up the result set. Optimisticwithvalues: Optimistic Concurrency Control is a standard part of transaction control theory. Optimistic Concurrency control is used in this case. In the interval between opening the cursor and updating the row, there is only a small chance for the second user to update a row. When a cursor is opened with this option, there is no lock to control the rows, which will help maximize its processing capability. If you try to modify a row, the current value of the row is compared with the value obtained from the last row extraction. If any value changes, the server will know that the other person has updated the row and will return an error. If the value is the same, the server executes the modification. Select this concurrency option optimisticwithrowversioning: this optimistic concurrency control option is based on Row version control. Use row version control. The table must have a version identifier, which can be used by the server to determine whether the row is changed after the cursor is read. In sqlserver, this performance is provided by the timestamp data type. It is a binary number that indicates the relative sequence of changes in the database. Each database has a global current timestamp value: @ dbts. Every time you change a row with a timestamp column in any way, sqlserver first stores the current @ dbts value in the timestamp column, and then adds the value of @ dbts. If a table has a timestamp column, the timestamp is recorded as a row. The server can compare the current timestamp value of a row with the timestamp value stored during the last extraction to determine whether the row has been updated. The server does not need to compare the values of all columns. You only need to compare the timestamp column. If the application requires Optimistic Concurrency Based on Row Version Control for tables without a timestamp column, the cursor is optimistic concurrency control based on the value by default. Scrolllocks implements pessimistic concurrency control. In pessimistic concurrency control, when the row of the database is read into the cursor result set, the application attempts to lock the row of the database. 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 the transaction, the update lock of the transaction will be kept until the transaction is committed or rolled back. When the next row is extracted, the cursor lock will be removed. If a cursor is opened outside the transaction, the lock is discarded when the next row is extracted. Therefore, whenever you need full pessimistic concurrency control, the cursor should be opened in the transaction. The update lock prevents any other task from obtaining the update lock or exclusive lock, thus preventing other tasks from updating the row. However, the update lock does not prevent the shared lock, so it does not prevent other tasks from reading rows, unless the second task also requires reading with the update lock. Based on the lock prompts specified in the SELECT statement defined by the cursor, these cursor concurrency options can generate a scroll lock. The scroll lock is obtained on each row during extraction and is kept until the next extraction or cursor is closed. The first occurrence prevails. During the next extraction, the server obtains the scroll lock for the Newly Extracted row and releases the scroll lock of the last extracted row. The rolling lock is independent of the transaction lock and can be kept after a commit or rollback operation. If the option to close the cursor when submitting is off, the commit statement does not close any opened cursor, and the scroll lock is retained until it is committed to maintain isolation of the extracted data. The type of the obtained scroll lock depends on the cursor concurrency option and the lock prompt in the SELECT statement of the cursor. Lock prompt read-only optimistic value optimistic row version control lock No prompt not locked update nolock not locked holdlock sharing update updlock error Update tablockx Error unlocked unlocked update other unlocked update * The specified nolock prompt will make the table specified with this prompt read-only in the cursor.
　　
16. Use profiler to track the query, obtain the time required for the query, and locate the SQL problem. Use the index optimizer to optimize the index.
　　
17. Pay attention to the difference between Union and unionall. Good unionall
　　
18. Pay attention to the use of distinct. Do not use distinct unless necessary. Similar to union, it slows down the query. Duplicate records are no problem in the query.
　　
19. Do not return unwanted rows or columns during query.
　　
20. Use sp_configure 'querygovernorcostlimit 'or setquery_governor_cost_limit to limit the resources consumed by queries. When the resource consumed by the evaluation query exceeds the limit, the server automatically cancels the query and kills the query before the query. Setlocktime: Set the lock time.
　　
21. selecttop100/10percent is used to limit the number of rows returned by the user or setrowcount.
　　
22. Before SQL2000, do not use the following words: "isnull", "<> ","! = ","!> ","! <"," Not "," notexists "," notin "," notlike ", and" like 'p0 '", because they do not leave the index and are all table scans. Do not add a function to the column name in the WHERE clause, such as convert and substring. If a function is required, create a computed column and then create an index. you can also change the wheresubstring (firstname,) = 'M' to wherefirstnamelike 'M' (index scan). You must separate the function from the column name. In addition, the index cannot be too large or too large. Notin scans the table multiple times and replaces it with exists, notexists, In, and leftouterjoin. It is especially a left join, while exists is faster than in, and the slowest is not. if the column value is null, its index does not work in the past. Now the 2000 optimizer can process it. The same isnull, "not", "notexists", and "notin" can optimize her, but "<>" and so on cannot be optimized and indexes are not used.
　　
23. Use queryanalyzer to check the SQL statement query plan and evaluate and analyze whether the SQL statement is optimized. Generally, 20 of the Code occupies 80 resources, and our optimization focuses on these slow points.
　　
24. If the in or query is not indexed, use the display statement to specify the index: Select * frompersonmember (Index = ix_title) whereprocessidin ('male', 'female ')
　　
25. Pre-calculate the results to be queried and place them in the table. Select the results when querying. This was the most important method before sql7.0. For example, hospital hospitalization fee calculation.
　　
26. Appropriate indexes can be used for Min () and max.
　　
27. There is a principle in the database that the code is closer to the data, the better. Therefore, the default is the preferred one, which is rules, triggers, and constraint (constraints such as the external key checkunique ......, The maximum length of the data type, etc. are constraints), procedure. This not only requires low maintenance work, high programming quality, and fast execution speed.
　　
28. If you want to insert a large binary value to the image column and use a stored procedure, do not insert it using an embedded insert Statement (whether Java is used or not ). In this way, the application first converts the binary value to a string (twice the size of the string), and then converts it to a binary value after the server receives the character. the stored procedure does not have these actions: Method: createprocedurep_insertasinsertintotable (fimage) values (@ image). You can call this stored procedure on the foreground to input binary parameters, which significantly improves the processing speed.
　　
29. Between is faster in some cases than in, and between can locate the range based on the index faster. Use the query optimizer to see the difference. Select * fromchineseresumewheretitlein ('male', 'female ') Select * fromchineseresumewherebetween 'male' and 'femal' are the same. Because in may be more than once, it may be slower sometimes.
　　
30. If it is necessary to create an index for a global or local temporary table, it may increase the speed, but not necessarily because the index also consumes a lot of resources. Its creation is the same as that of the actual table.
　　
31. Do not create useless things, such as wasting resources when generating reports. Use it only when necessary.
　　
32. The or clause can be divided into multiple queries and connected to multiple queries through Union. Their speed is only related to whether an index is used. If a query requires a joint index, unionall is more efficient. no index is used for multiple or statements, and it is rewritten to the form of union to try to match the index. Whether or not indexes are used for a critical issue.
　　
33. Use a view as little as possible, which is less efficient. Operations on a view are slower than operations on a table. You can replace it with storedprocedure. What's special is not to use nested views. nested views increase the difficulty of searching for original data. Let's look at the essence of the View: it is an optimized SQL statement stored on the server that has produced a query plan. When retrieving data from a single table, do not use a view pointing to multiple tables. Read data directly from the view that only contains the table. Otherwise, unnecessary overhead is added, the query is disturbed. to speed up View query, MSSQL adds the View index function.
　　
34. Do not use distinct or orderby when necessary. These actions can be executed on the client. They increase additional overhead. This is the same as Union and unionall.
　　
Selecttop20ad. companyName, comid, position, AD. referenceid, worklocation, convert (varchar (10), Ad. postdate, 120) aspostdate1, workyear, Week ('jcnad00329667 ', 'jcnad132168', 'jcnad00337748 ', 'jcnad00338345 ',
'Jcnad00333138 ', 'jcnad00303570', 'jcnad00303569 ',
'Jcnad00303568 ', 'jcnad00306698', 'jcnad00231935 ', 'jcnad00231933 ',
'Jcnad00254567', 'jcnad00254585 ', 'jcnad00254608 ',
'Jcnad00254607 ', 'jcnad00258524', 'jcnad00332379', 'jcnad00268618 ',
'Jcnad00279196 ', 'jcnad00268613') orderbypostdatedesc
　　
35. In the post-in nominal value list, place the most frequent values at the beginning and the least value at the end to reduce the number of judgments.
　　
36. When selectinto is used, it locks the system table (sysobjects, sysindexes, etc.) and blocks access from other connections. When creating a temporary table, use the show statement instead of selectinto. droptablet_lxhbegintranselect * Tables = 'xyz' -- In another connection, select * fromsysobjects can see that selectinto locks the system table and createtable also locks the system table (whether it is a temporary table or a system table ). So never use it in things !!! In this case, use real tables or temporary table variables for temporary tables that are frequently used.
　　
37. Generally, redundant rows can be removed before the groupby having statements, so try not to use them for row removal. Their execution sequence should be optimal as follows: Select WHERE clause Selects all appropriate rows, groupby groups statistical rows, and having clause removes redundant groups. In this way, the consumption of groupby having is small and the query speed is fast. Grouping and having large data rows consume a lot of resources. If the goal of groupby is not to include computing, but to group, it is faster to use distinct.
　　
38. Updating multiple records at a time is faster than updating multiple records at a time, that is, batch processing is good.
　　
39. Use less temporary tables and replace them with result sets and table variables. Table variables are better than temporary tables.
　　
40. In SQL2000, calculated fields can be indexed. The following conditions must be met:
　　
A. The expression of calculated fields is definite.
　　
B. Data Types of text, ntext, and image cannot be used.
　　
C. The following options must be prepared: ansi_nulls = on, ansi_paddings = on ,.......
　　
41. Try to put data processing on the server to reduce network overhead, such as using stored procedures. Stored procedures are compiled, optimized, organized into an execution plan, and stored in the database as SQL statements. They are a collection of control flow languages and are fast. You can use a temporary stored procedure to execute dynamic SQL statements repeatedly. This process (temporary table) is stored in tempdb. In the past, due to SQL Server's lack of support for complex mathematical computing, we had to put this job on another layer to increase network overhead. SQL2000 supports udfs and now supports complex mathematical computing. the return value of a function is not too large, which is costly. User-Defined Functions consume a large amount of resources like the cursor. If a large result is returned, the stored procedure is used.
　　
42. Do not use the same function repeatedly in one sentence, waste resources, and put the result in a variable before calling it faster.
　　
43. selectcount (*) is less efficient. Try to change the method as much as possible, while exists is faster. pay attention to the difference: the returned values of selectcount (fieldofnull) fromtable and selectcount (fieldofnotnull) fromtable are different !!!
　　
44. When the server has enough memory, the number of prepared threads = the maximum number of connections is 5, which can maximize the efficiency; otherwise, the number of prepared threads <the maximum number of connections is used to enable the sqlserver thread pool, if the number is equal to the maximum number of connections, the performance of the server is seriously damaged.
　　
45. Access your table in a certain order. If you lock table A and table B first, you should lock them in this order in all stored procedures. If you first lock table B in a stored procedure and then lock Table A, this may lead to a deadlock. If the lock sequence is not designed in advance, it is difficult to find deadlocks.
　　
46. Use sqlserverperformancemonitor to monitor the load of the corresponding hardware: Memory: pagefaults/sec counters. If this value increases by chance, it indicates that there were threads competing for memory. If it continues high, memory may be the bottleneck.
Process:
　　
1. dpctime refers to the percentage of services received and provided by the processor during the sample interval in the deferred program call (DPC. (DPC is running at a lower priority interval than the standard interval ). Because DPC is executed in privileged mode, the percentage of DPC time is part of the privileged time percentage. These times are calculated separately and are not part of the total number of interval computations. This total number shows the average busy hours as the percentage of instance time.
　　
2. processortime counter if the value of this parameter continuously exceeds 95, it indicates that the bottleneck is the CPU. You can consider adding a processor or changing a faster processor.
　　
3. privilegedtime indicates the percentage of idle processor time used in privileged mode. (Privileged mode is a processing mode designed for operating system components and operating hardware drivers. It promises to directly access hardware and all memory. Another mode is the user mode. It is a finite processing mode designed for applications, Environment subsystems, and integer subsystems. The operating system converts the application thread to the privileged mode to access the Operating System Service ). The privileged time includes the time when services are interrupted and DPC is provided. The high privileged time ratio may be caused by a large number of failed device intervals. This counter displays the average busy hours as part of the sample time.
　　
4. usertime indicates CPU-consuming database operations, such as sorting and executing aggregatefunctions. If the value is very high, you can consider increasing the index, try to use simple table join, horizontal table segmentation, and other methods to reduce the value. Physicaldisk: curretndiskqueuelength counter. The value must not exceed 1.5 of the number of disks ~ 2 times. To improve performance, you can add disks. Sqlserver: cachehitratio the higher the value, the better. If it continues below 80, you should consider increasing the memory. Note that the value of this parameter is accumulated after being started from sqlserver. Therefore, after a period of operation, this value cannot reflect the current value of the system.
　　
47. Analyze selectemp_nameformemployeewheresalary> 3000 in this statement, if salary is of the float type, the optimizer optimizes it to convert (float, 3000) Because 3000 is an integer, we should use 3000.0 during programming instead of converting the DBMS during runtime. Conversion of the same character and integer data.
　　
48. query Association and write order
　　
Selecta. personmemberid, * fromchineseresumea, personmemberbwherepersonmemberid = B. referenceidanda. personmemberid = 'cnprh1_1' (A = B, B = 'number ')
　　
Selecta. personmemberid, * fromchineseresumea, personmemberbwherea. personmemberid = B. referenceidanda. personmemberid = 'jcnprh1_1 'andb. referenceid = 'cnprh00001' (A = B, B = 'number', A = 'number ')
　　
Selecta. personmemberid, * fromchineseresumea, personmemberbwhereb. referenceid = 'jcnprh1_1 'anda. personmemberid = 'jcnprh1_1' (B = 'number', A = 'number ')
　　
49,
　　
(1) If no owner code is entered, thencode1 = 0code2 = 9999elsecode1 = code2 = owner code endif: Select owner name fromp2000where owner code >=: code1and owner code <=: code2
　　
(2) If no owner code is entered, then select owner name fromp2000elsecode = owner code select owner code fromp2000where owner code =: codeendif the first method only uses one SQL statement, the second method uses two SQL statements. When no owner code is entered, the second method is obviously more efficient than the first method because it has no restrictions. When the owner code is entered, the second method is still more efficient than the first method. It not only lacks one restriction condition, but also is the fastest query operation because of equality. Do not worry about writing programs.
　　
50. The new method for querying pages in jobcn is as follows: Use the performance optimizer to analyze the performance bottleneck. If I/O or network speed is used, the following method is effective. It is better to use the current method on the CPU or memory. Please differentiate the following methods, indicating that the smaller the index, the better.
　　
Begin
　　
Declare @ local_variabletable (fidintidentity (1, 1), referenceidvarchar (20 ))
　　
Insertinto @ local_variable (referenceid)
　　
Selecttop100000referenceidfromchineseresumeorderbyreferenceid
　　
Select * From @ local_variablewherefid> 40 andfid <= 60
　　
End and
　　
Begin
　　
Declare @ local_variabletable (fidintidentity (1, 1), referenceidvarchar (20 ))
　　
Insertinto @ local_variable (referenceid)
　　
Selecttop100000referenceidfromchineseresumeorderbyupdatedate
　　
Select * From @ local_variablewherefid> 40 andfid <= 60
　　
End
　　
Begin
　　
Createtable # temp (fidintidentity (1, 1), referenceidvarchar (20 ))
　　
Insertinto # temp (referenceid)
　　
Selecttop100000referenceidfromchineseresumeorderbyupdatedate
　　
Select * from # tempwherefid> 40 andfid <= 60 droptable # temp
　　
End

Appendix: storage process writing experience and optimization measures from: Web Teaching Network

1. Suitable for readers: database developers, who have a large amount of data in the database and who are interested in optimizing the SP (stored procedure.

II. Introduction: complex business logic and database operations are often encountered during database development. In this case, SP is used to encapsulate database operations. If there are many SP projects and there is no certain specification for writing, it will affect the difficulties of system maintenance and the difficulty of understanding the big SP logic in the future, in addition, if the database has a large amount of data or the project has high performance requirements for the SP, it will encounter optimization problems. Otherwise, the speed may be slow. After hands-on experience, an Optimized SP is hundreds of times more efficient than an optimized SP with poor performance.

Iii. content:

1. If developers use tables or views of other databases, they must create a view in the current database to perform cross-database operations. It is best not to directly use "Databse. DBO. table_name ", because sp_depends cannot display the cross-database table or view used by the SP, it is not convenient to verify.

2. Before submitting the SP, the developer must have used setshowplanon to analyze the query plan and perform its own query optimization check.

3. High program running efficiency and application optimization. Pay attention to the following points during SP writing:

A) SQL usage specifications:

I. Avoid large transaction operations as much as possible. Use the holdlock clause with caution to improve the system concurrency capability.

Ii. Try to avoid repeated accesses to the same or several tables, especially tables with large data volumes. You can consider extracting data to a temporary table based on the conditions and then connecting it.

III. avoid using a cursor as much as possible, because the efficiency of the cursor is poor. If the cursor operation has more than 10 thousand rows of data, it should be rewritten; if the cursor is used, try to avoid table join operations in the cursor loop.

IV. pay attention to where statement writing, and the order of statements must be taken into account. The order before and after condition clauses should be determined based on the index order and range size, and the field order should be consistent with the index order as much as possible, the range is from large to small.

V. do not perform functions, arithmetic operations, or other expression operations on the left side of "=" in the WHERE clause. Otherwise, the system may not be able to correctly use the index.

Vi. Use exists instead of selectcount (1) to determine whether a record exists. The count function is used only when all the rows in the statistical table are used, and count (1) is more efficient than count.

VII. Try to use "> =" instead of "> ".

VIII. Pay attention to replacement between some or clause and union clause

IX. Pay attention to the data types connected between tables to avoid the connection between different types of data.

X. Pay attention to the relationship between parameters and data types in stored procedures.

XI. Focus on the data volume of insert and update operations to prevent conflicts with other applications. If the data volume exceeds 200 data pages (400 Kb), the system will update the lock and the page-Level Lock will be upgraded to the table-Level Lock.