SQL Server optimization 50 Methods

Although the query speed is slow for many reasons, through certain optimization, the query problem can be solved to a certain extent.

There are many reasons for slow query speed. The following are common causes:
1. No index or no Index 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. No Create computingColumn causes the query to be not optimized.
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.Data, logs, and indexes are stored on different I/O devices to increase the reading speed.In the past, Tempdb can be placed on RAID0, and SQL2000 is not supported. The larger the data size (size), the more important it is to increase I/O.
2. vertical and horizontalSplit the table to reduce the table size.(Sp_spaceuse)
3. upgrade hardware
4,Create indexes, optimize indexes, and optimize access methods based on query Conditions,Limit the data size of the result set. Note:Fill factor should be appropriate(It is best to use the default value 0 ). The index should be as small as possible,Index Using a column with a small number of bytesGood (refer to index creation ),Do not set a limited number of valuesTo create a single index, such as gender field.
5. Improve network speed;
6,Increase Server Memory, Windows 2000 andSQL Server2000 supports 4-8 GB memory. Configure virtual memory: the virtual memory size should be configured based on services running concurrently on the computer. When running Microsoft SQL Server 2000, considerSet the virtual memory size to the computerInstallPhysical memory1.5 times. If you have installedFull-text retrievalTo run the Microsoft Search Service for full-text indexing and query, consider setting the virtual memory size to at least three times the physical memory installed on your computer. Configure the SQL Server max server memory Server configuration option to 1.5 times the physical memory (half the virtual memory size ).
7. AddServer CPU countBut must understandParallel processing requires more resources than serial ProcessingFor example, memory. 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 group by statements of delayed queries are executed simultaneously, SQL SERVER 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.. HoweverUPDATE, INSERT, and DELETE operations cannot be processed in parallel..
8. If you use like for query, you cannot simply use index,Full-text indexes consume space. Like 'a % 'uses the index like' % a' without the index. When you use like '% a %' to query, the query time is proportional to the total length of the field value.So the CHAR type is not supported, but VARCHAR.Create a full-text index with a long field value.
9,DB Server and APPLication ServerSeparation; OLTP and OLAP Separation
10,Distributed partition ViewCan be used for implementationDatabaseServer consortium. A consortium is a group separatedManagementBut they work together to share the processing load of the system. This passPartition dataThe Database Server consortium mechanism 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, you must firstHorizontal Partition Table
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 distributed partition view names can be 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,Re-IndexingDbcc reindex, dbcc indexdefrag,Shrink data and logsDbcc shrinkdb, dbcc shrinkfile. Set automatic log shrinking.For large databases, do not set Automatic database growth. This will reduce server performance.. The writing of T-SQL is very important. The following lists common points: first, the process of DBMS processing the query plan is as follows:
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.Data storage structure of SQL SERVER: the size of a page is 8 K (8060) bytes, and 8 pages are a disk Area, Which is stored according to the B-tree.
12,Difference between Commit and rollbackRollback: rolls back all transactions.Commit: Submit the current transaction.There is no need to write transactions in dynamic SQL. If you want to write transactions, write them out.For example, begin tran exec (@ s) commit trans or write dynamic SQL into functions or stored procedures.
13.Use the Where clause in the Select statement to limit the number of returned rowsTo avoid table scanning. If unnecessary data is returned, the server's I/O resources are wasted, increasing the network burden and reducing performance.If the table is large, the table is locked during table scan and other connections are prohibited from accessing the table. Otherwise, the consequences are serious.
14. The SQL statement does not affect execution.
15. Try not to use the cursor. It occupies a large amount of resources. If row-by-row is required,Use non-cursor technology as much as possible, such as loop on the client, using temporary tables, Table variables, subqueries, and Case statements.. The cursor can follow itSupported extraction optionsClassification:
Forward onlyThe row must be extracted from the first row to the last row. Fetch next is the only allowed extraction operation and is also the default method.
RollabilityArbitrary rows can be randomly extracted 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: Update cannot be located through the cursor, and there is no lock in the rows that make up the result set.
Optimistic with valueS: 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 and selects this concurrency option.
OPTIMISTIC WITH ROW VERSIONING: 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 SQL Server, 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, SQL Server 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.
SCROLL LOCKSThis option 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 the 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 concurrency options of the cursor and the cursor.
16. UseProfiler to track the query and obtain the time required for the queryTo locate the SQL problem.Index OptimizerOptimize the index.
17. NoteDifference between UNion and UNion all:Good UNION ALL
18. Use DISTINCT unless necessary. Similar to UNION, it slows down the query.Duplicate records are no problem in the query.
19. QueryDo not return unwantedRows and columns
20. UseSp_configure 'query governor cost limit 'or SET QUERY_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. Set locktime: SET the lock time.
21. Use select top 100/10 Percent to limit the number of rows returned by the user orSET ROWCOUNTTo restrict the operation rows
22. Before SQL2000,Do not use the following words: "Is null", "<> ","! = ","!> ","! <"," NOT "," not exists "," not in "," not like ", and" LIKE '% 100' ", because they do NOT leave the index and are all table scans. AlsoDo not add a function to the column name in the WHere clause, Such as Convert and substring. If a function is required,Create a calculated column and then create an index.. You can also change the syntax of where substring (firstname,) = 'M' to WHERE firstname like'm % '(index scan). You must separate the function from the column name. In addition, the index cannot be too large or too large.Not in scans the table multiple times and uses EXISTS, not exists, IN, left outer join instead, especially left join.And Exists is faster than IN, and the slowest operation is NOT. If the column value contains null, its index does NOT work IN the past, and now the 2000 optimizer can process it. The same is null, "NOT", "not exists", "not in" can optimize her,And "<>" cannot be optimized, The index is not used.
23. UseQuery AnalyzerTo check whether the SQL statement query plan and evaluation analysis are optimized. Generally, 20%CodeIt occupies 80% of the resources, and our optimization focuses on these slow points.
24. If in or is used, the query is not indexed,Specify an index using the display statement: SELECT * FROM PersonMember (INDEX = IX_Title) WHERE processid IN ('male', 'female ')
25. SetThe results to be queried are pre-calculated and placed in the table.SELECT again when querying. This was the most important method before SQL7.0. For example, hospital hospitalization fee calculation.
26,MIN () and MAX ()Suitable indexes can be used.
27,There is a principle in the databaseThe closer the code is to the data, the better. Therefore, Default is selected first, namely, 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, use the stored procedure. Do not INsert the value 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: Create procedure p_insert as insert into table (Fimage) values (@ image), and calls this stored procedure on the foreground to pass in binary parameters, this significantly improves the processing speed.
29,Between is faster than IN some times,Between can be fasterLocate the range based on the index. Use the query optimizer to see the difference. Select * from chineseresume where title in ('male', 'female ') Select * from chineseresume where between 'male' and 'female' are the same. Because in may be more than once, it may be slower sometimes.
30.Create indexes for global or local temporary tables if necessary, Sometimes it can 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 anyTransactionsFor exampleReportsIs a waste of resources. Use a transaction only when necessary.
32,The OR statement can be divided into multiple queries.And connect multiple queries through UNION. TheirThe speed is only related to whether an index is used.If UNION indexes are required for queries, union all is more efficient.Indexes are not used for multiple OR statements.And then try to match the index in the form of UNION. Whether or not indexes are used in a key issue.
33,Minimize the use of views, which is less efficient. Operations on views are slower than operations on tables., You can use stored procedure to replace her.In particular, do not use view nestingNested views increase the difficulty of searching for original data. Let's seeView essence: it is an optimized SQL statement stored on the server that has generated 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 addsView index.
34. Do not use DISTINCT or order by unless necessary. These actions can be executed on the client. They increase additional overhead. This is the same as UNION and union all.
Select top 20 ad. companyname, comid, position, ad. referenceid, worklocation, convert (varchar (10), ad. postDate, 120) as postDate1, workyear, degreedescription FROM your ad where referenceID in ('jcnad00329667 ', 'jcnad132168', 'jcnad00337748', 'jcnad00338345 ',

'Authorization', 'authorization', 'cnad00303569 ', 'cnad00303568', 'cnad00306698 ', 'authorization', 'cnad00231933', 'authorization', 'cnad00254585 ', 'authorization ', 'jcnad00254607 ', 'jcnad00258524', 'jcnad00332379', 'jcnad00268618 ',

'Jcnad00279196 ', 'jcnad00268613') order by postdate desc
35.IN the list of post-nominal values, place the most frequent values at the beginning and the least value at the end to reduce the number of judgments..
36. WhenWhen select into is used, it locks the system table.(Sysobjects, sysindexes, etc.), blocking access to other connections.Use the show statement to create a temporary tableInstead of select. drop table t_lxh begin tran select * into t_lxh from chineseresume where name = 'xyz' -- commit in another connection SELECT * from sysobjects You Can See That select into locks the system table,Create table also locks the system table (whether it is a temporary table or a system table ). So never use it in a transaction !!!In this case, use real tables or temporary table variables for temporary tables that are frequently used.
37. The redundant rows can be removed before the group by and HAVING clauses.Try not to use them for row Removal. Their execution sequence should be optimal as follows: select Where clause Selects all appropriate rows, and Group By is used to Group statistics rows,Having is used to remove unnecessary groups.. In this way, Group By Having has a low overhead and fast query.Grouping and Having large data rows consumes a lot of resources.. If the purpose of Group BY is not to include computing, but to Group, it is faster to use Distinct.
38,Multiple records updated at a time score multiple updates each time fastThat is to say, batch processing is good.
39,Use less temporary tables and replace them with result sets and Table variables whenever possible., Table type 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 process data on the serverTo reduce network overhead, suchUse 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, because SQL SERVER did not support complex mathematical computing, it 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 are executed like cursors, consuming a large amount of resources.,If a large result is returned, the stored procedure is used.
42. Do not use the same function repeatedly in one sentence, wasting resourcesThe result is put in a variable and then called faster.
43,Low Efficiency of select count (*), Try to change his writing style, while EXISTS is fast. note the difference: the return values of select count (Field of null) from Table and select count (Field of NOT null) from Table are different !!!
44. When the serverEnough memory,Number of preparation threads = maximum number of connections + 5 to maximize efficiencyOtherwise, use the number of prepared threads <maximum number of connections to enable the SQL SERVER thread pool. If the number is equal to the maximum number of connections + 5, the performance of the SERVER is seriously damaged.
45,Access your table in a certain order. If you lock table A and table B firstIn this order in all stored proceduresTo lock them. 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 detail in advance, it is difficult to find deadlocks.
46. PassSQL Server Performance Monitor monitors the load of the corresponding hardwareMemory: Page Faults/sec counters if this value increases occasionally, it indicates that there were threads competing for Memory. If it continues high, memory may be the bottleneck.
Process:
1.% DPC Time indicates that the processor is used during the sample intervalDeferred program call(DPC) Percentage of received and provided services. (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 time values are calculated separately and are not part of the total number of interval values. This total number shows the average busy hours as the percentage of instance time.
2. If the value of % Processor Time counter exceeds 95%, the bottleneck is the CPU. You can consider adding a processor or changing a faster processor.
3.% Privileged Time 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 allows direct access to 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 the service is interrupted and the 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.% User Time indicates CPU-consuming database operations, such as sorting and executing aggregate functions. If the value is very high, you can consider increasing the index and try to reduce the value by using simple table join and horizontal table segmentation methods. Physical Disk: Curretn Disk Queue Length counter this value should not exceed 1.5 ~ 2 times. To improve performance, you can add disks. SQLServer: Cache Hit Ratio counter. The higher the value, the better. If the duration is lower than 80%, consider increasing the memory. Note that the value of this parameter is accumulated after SQL Server is started. Therefore, after running for a period of time, this value cannot reflect the current value of the system.
47. Analyze select emp_name form. in this statement, if salary is of the Float type, the optimizer optimizes it to Convert (float, 3000). Because 3000 is an integer, we shouldProgrammingUse 3000.0 instead of converting the DBMS at runtime.. Conversion of the same character and integer data.
48,Query Association and write order
Select a. personMemberID, * from chineseresume a, personmember B where personMemberID = B. referenceid and a. personMemberID = 'jcnprh1_1 '(A = B, B = 'number ')
Select. personMemberID, * from chineseresume a, personmember B where. personMemberID = B. referenceid and. personMemberID = 'jcnprh1_1 'and B. referenceid = 'cnprh00001' (A = B, B = 'number', A = 'number ')
Select. personMemberID, * from chineseresume a, personmember B where B. referenceid = 'jcnprh1_1 'and. personMemberID = 'jcnprh1_1' (B = 'number', A = 'number ')
49,
(1) IF no owner code is entered, THEN code1 = 0 code2 = 9999 ELSE code1 = code2 = owner code end if the SQL statement executed is: SELECT owner name FROM P2000 WHERE owner code> =: code1 AND owner code <=: code2
(2) IF no owner code is entered, then select owner name FROM P2000 ELSE code = owner code SELECT owner code FROM P2000 WHERE owner code =: the first method of code end if only uses one SQL statement, and 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. WeDo not worry about writing programs
50. The new method for querying pages in JOBCN is as follows: Use the performance optimizer to analyze performance bottlenecks. 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. Separate the following methods:The smaller the index, the better..
Begin
DECLARE @ local_variable table (FID int identity (1, 1), ReferenceID varchar (20 ))
Insert into @ local_variable (ReferenceID)
Select top 100000 ReferenceID from chineseresume order by ReferenceID
Select * from @ local_variable where Fid> 40 and fid <= 60
End and
Begin
DECLARE @ local_variable table (FID int identity (1, 1), ReferenceID varchar (20 ))
Insert into @ local_variable (ReferenceID)
Select top 100000 ReferenceID from chineseresume order by updatedate
Select * from @ local_variable where Fid> 40 and fid <= 60
End
Begin
Create table # temp (FID int identity (1, 1), ReferenceID varchar (20 ))
Insert into # temp (ReferenceID)
Select top 100000 ReferenceID from chineseresume order by updatedate
Select * from # temp where Fid> 40 and fid <= 60 drop table # temp
End