SQL Server Optimization

Although the query speed is a lot of reasons, but if through a certain optimization, you can also make the query problem to a certain extent to be resolved.

The reasons for the slow query are many, and the following are common:

1. No index or index is not used (this is the most common problem of slow query, is the defect of program design)

2. I/O throughput is small, creating a bottleneck effect.

3. A computed column is not created causing the query not to be optimized.

4. Not enough memory

5. Slow network speed

6. The amount of data queried is too large (can use multiple queries, other methods to reduce the amount of data)

7. Lock or deadlock (this is also the most common problem of slow query, is the defect of program design)

8. Sp_lock, sp_who, active users view because of read and write competing resources.

9. Unnecessary rows and columns returned

10. Query statement is not good, no optimization

You can refine your query by:

2. Put the data, logs, indexes on different I/O devices, increase the read speed, before tempdb should be placed on the RAID0, SQL2000 no longer support. The larger the amount of data (size), the more important it is to increase I/O.

4. Split table vertically and horizontally, reducing the size of the table (Sp_spaceuse)

6. Upgrading hardware

8. Based on the query criteria, the index is indexed, the index is optimized, the access method is optimized, and the data amount of the result set is limited. Note that the fill factor is appropriate (preferably using the default value of 0). The index should be as small as possible, using a Lie Jian index with a small number of bytes (refer to the creation of the index), do not Jianjian a single index on a limited number of values such as the gender field

10. Increase speed

12. Expand the memory for the server, Windows 2000 and SQL Server 2000 can support 4-8g memory. Configure virtual Memory: The virtual memory size should be configured based on the services that are running concurrently on the computer. When you run Microsoft SQL Server 2000, consider setting the virtual memory size to 1.5 times times the physical memory installed on your computer. If you have additional full-text search features installed and you plan to run the Microsoft search service to perform full-text indexing and querying, consider: Configure the virtual memory size to be at least 3 times times the physical memory installed on the computer. Configure the SQL Server max server memory server configuration option to 1.5 times times the physical memory (half of the virtual memory size setting).

14. Increase the number of server CPUs, but it is important to understand that parallel processing requires resources such as memory more than serial processing. The use of parallel or string travel is the MSSQL automatic evaluation option. A single task is decomposed into multiple tasks and can be run on the processor. For example, delays in sorting, connecting, scanning, and group by words are performed simultaneously, and SQL Server determines the optimal level of parallelism based on the load of the system, and complex queries that consume large amounts of CPU are best suited for parallel processing. However, the update operation Update,insert,delete cannot be processed in parallel.

16. If you are querying using like, it is not possible to simply use index, but the full-text index consumes space. Like ' a% ' uses an index, like '%a ' does not use an index. When using the like '%a% ' query, the query time is proportional to the total length of the field value, so you cannot use the char type, but varchar. The full-text index is long for the value of the field.

18. DB Server and application server separation; OLTP and OLAP separation

20. A distributed partitioned view can be used to implement a federation of database servers. A consortium is a set of servers that are managed separately, but they work together to share the processing load of the system. This mechanism of forming a federation of database servers through partitioned data can expand a set of servers to support the processing needs of large, multi-tiered Web sites.

    1. Before implementing a partitioned view, you must first horizontally partition the table

    2. After the member tables are created, a distributed partitioned view is defined on each member server, and each view has the same name. This enables queries that reference the distributed partitioned view name to run on any member server. The system operates as if each member server has a copy of the original table, but there is only one member table and one distributed partitioned view on each server. The location of the data is transparent to the application.

22. Rebuild index DBCC REINDEX, DBCC INDEXDEFRAG, shrink data and log DBCC SHRINKDB, DBCC SHRINKFILE. Sets the auto-shrink log. For large databases do not set the database autogrow, it will degrade the performance of the server. There's a lot of emphasis on T-SQL, and here's a list of common points: first, the DBMS processes the query plan:

    1. Lexical and syntactic checking of query statements

    2. Query optimizer to submit statements to the DBMS

    3. Optimization of optimizer's algebra optimization and access path optimization

    4. Generating query plans from precompiled modules

    5. Then submit it to the system at the right time to process the execution

    6. Finally, the execution results are returned to the user second, look at the SQL Server data storage structure: A page size of 8K (8060) bytes, 8 pages for a disk area, according to B-Tree storage.

24. The difference between commit and rollback. Rollback: Rollback of all transactions; commit: Commit the current transaction. It is not necessary to write transactions in dynamic SQL, if you want to write on the outside, such as: Begin TRAN EXEC (@s) commit trans or write dynamic SQL as a function or stored procedure.

26. Limit the number of rows returned with a WHERE clause in the query SELECT statement, avoid table scans, and, if you return unnecessary data, waste the server's I/O resources, aggravating the burden of network degradation. If the table is large, locks the table during the table scan and prevents other joins from accessing the table, otherwise serious consequences.

28. The comment statement for SQL has no effect on execution.

29. As much as possible without using the cursor, it consumes a lot of resources. If you need to execute row-by-row, try to use non-cursor technology, such as: In the client loop, with temporary tables, table variables, subqueries, with case statements and so on. Cursors can be categorized according to the extraction options it supports:

    2. READ_ONLY: The cursor is not allowed to locate updates (update), and there is no lock in the row that makes up the result set.

    4. optimistic with ValueS: optimistic concurrency control is a standard part of transaction control theory. Optimistic concurrency control is used in situations where there is only a small chance for a second user to update a row in the interval between opening the cursor and updating the row. When a cursor is opened with this option, there is no lock to control the rows in it, which will help maximize its processing power. If the user attempts to modify a row, the current value of this row is compared with the value obtained when the row was last fetched. 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 chooses this concurrency option.

    8. SCROLL LOCKS This option for pessimistic concurrency control. In pessimistic concurrency control, when a row of a database is read into a cursor result set, the application attempts 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 a cursor is opened within a transaction, the transaction update lock is persisted until the transaction is committed or rolled back, and the cursor lock is dropped when the next row is fetched. If you open a cursor outside of a transaction, the lock is discarded when the next row is fetched. Therefore, each time a user needs full pessimistic concurrency control, the cursor should open within the transaction. An update lock prevents any other task from acquiring an update lock or exclusive lock, preventing other tasks from updating the row. However, updating a lock does not prevent a shared lock, so it does not prevent other tasks from reading the row unless the second task also requires a read with an update lock. Scroll locks These cursor concurrency options can generate scroll locks based on the lock hints specified in the SELECT statement defined by the cursor. The scroll lock is fetched on each line at fetch and remains until the next fetch or the cursor closes, whichever occurs first. The next time the fetch occurs, the server acquires a scroll lock for the row in the new fetch and releases the last scroll lock to fetch rows. A scroll lock is independent of the transaction lock and can be persisted after a commit or rollback operation. If the option to close the cursor at commit is off, the commit statement does not close any open cursors, and the scroll lock is persisted to the commit to maintain isolation of the extracted data. The type of scroll lock acquired depends on the cursor concurrency options and cursors.

    10. Forward-only the rows must be fetched in the order from the first row to the last row. Fetch NEXT is the only allowed fetch operation and is the default.

    12. Scrollable can randomly fetch any row anywhere in the cursor.

    14. The technique of cursors becomes very powerful under SQL2000, and his purpose is to support loops. There are four concurrency options:

31. Use Profiler to track queries, get the time needed to query, find out where SQL is, and optimize indexes with the index optimizer.

33. Note the difference between Union and UNION all: the Union ALL is good

35. Note using DISTINCT, do not use if it is not necessary, and it will slow down the query as well as union. Duplicate records are not a problem in the query.

37. Do not return rows and columns that are not required when querying

39. Use sp_configure ' query governor cost limit ' or set Query_governor_cost_limit to limit the resources consumed by the query. When an estimate query consumes more resources than the limit, the server automatically cancels the query and kills it before the query. Set Locktime Setting the lock time

41. Use select top 100/10 Percent to limit the number of rows returned by the user or set rowcount to restrict the rows of the operation

43. Before SQL 2000, it is generally not to use the following words: "Is NULL", "" ","! = ","! ","! "," not "," not EXISTS "," Not in "," not as ", and" like '%500 ' ", because they do not go Indexes are all table scans. Also do not add functions, such as convert,substring, in the WHERE clause, if you must use a function, create a computed column and then create an index instead. You can also work around: where SUBSTRING (firstname,1,1) = ' m ' is changed to where FirstName like ' m% ' (index Scan), be sure to separate function and column names. And the index cannot be built too much and too large. The not-in will scan the table multiple times, using EXISTS, not EXISTS, in, and left OUTER joins to replace, especially the Zuo connection, while EXISTS is faster than in, and the slowest is the not operation. If the value of the column contains null, the previous index does not work, and now 2000 of the optimizer can handle it. The same is ' is NULL ', ' not ', ' not EXISTS ', ' not ' to optimize her, and ' "is still not optimized, not indexed.

45. Use Query Analyzer to view the SQL statement's query plan and evaluate whether the analysis is optimized for SQL. The average 20% of the code occupies 80% of the resources, and the focus of our optimization is these slow places.

47. If you use in or or etc. to find that the query does not go through the index, use the display declaration to specify the index: SELECT * from personmember (index = ix_title) WHERE ProcessID in (' Male ', ' female ')

49. The results of the query need to be pre-calculated in the table, and then select the query. This is the most important means before SQL7.0. For example, the hospital's hospitalization fee calculation.

51. MIN () and MAX () can use the appropriate index.

53. The database has a principle is that the code closer to the better, so the first choice of default, followed by rules, Triggers, Constraint (constraints such as external health main health checkunique ..., the maximum length of data type, etc. are constraints), Procedure. This is not only a small maintenance work, writing programs of high quality, and the speed of execution.

55. If you want to insert a large binary value into an image column, use a stored procedure and never insert it with inline inserts (whether Java is not known). Because the application first converts the binary value to a string (twice times its size), the server receives the character and converts it to a binary value. Stored procedures do not have these actions: Method: Create procedure P_insert as INSERT into table (Fimage) VALUES (@image), call this stored procedure in the foreground to pass in binary parameters, so processing speed significantly improved.

57. Between at some point faster than in, between can find a range faster based on the index. The difference is visible with the query optimizer. SELECT * from Chineseresume where title in (' Male ', ' female ') Select * from chineseresume where between ' male ' and ' female ' are the same. Because in will be compared several times, it is sometimes slower.

59. Creating indexes on global or local temporary tables, if necessary, can sometimes improve speed, but not necessarily because indexes also consume a lot of resources. His creation is the same as the actual table.

61. Do not build non-functioning transactions such as generating reports, wasting resources. Use it only when necessary to use the transaction.

63. Words with or can be decomposed into multiple queries, and multiple queries are concatenated through union. Their speed is only related to whether the index is used, and if the query requires a federated index, the union all executes more efficiently. Multiple or words are not indexed, changed into union form and try to match the index. A key question is whether to use the index.

65. Minimize the use of views, it is inefficient. The view operation is slower than the direct table operation and can be replaced by stored procedure. In particular, instead of nesting views, nested views add to the difficulty of finding the original data. We look at the nature of the view: It is a well-optimized SQL that has generated query planning on the server. When retrieving data for a single table, do not use a view that points to more than one table, either directly from the table or only the view that contains the table, otherwise it adds unnecessary overhead and the query is disturbed. In order to speed up the query of the view, MSSQL added the function of the view index.

67. Do not use distinct and order by when it is not necessary, these actions can be changed in the client execution. They add extra overhead. This is the same as union and union all.

69. In the back face list, the most frequently occurring values are placed at the front, with the fewest occurrences of the last face, reducing the number of judgements.

71. When you use SELECT INTO, it locks the system tables (sysobjects,sysindexes, and so on), blocking access to other connections. When creating a temporary table, use the Display declaration statement instead of SELECT INTO. drop table T_LXH BEGIN TRAN SELECT * into T_lxh from chineseresume where name = ' XYZ '--commit, in another connection select * from Sysobj ECTS can see that SELECT into locks system tables, and Create table locks system tables (whether temporary or system tables). So don't use it within a transaction!!! In this case, use a real table, or a temporary table variable, if it is a temporary table that you want to use frequently.

73. It is generally possible to eliminate unnecessary rows before group by and having a sentence, so try not to use them to do the culling work. Their order of execution should be optimal: the WHERE clause of the SELECT selects all the appropriate rows, group by is used to group the statistical rows, and the having words are used to exclude redundant groupings. This way, group by has a small cost, fast query. Grouping and having a large data row consumes resources. If the purpose of group by is not to include calculations, just groups, then use distinct faster

75. Update multiple records at one time the score is updated one faster, that is, batch processing is good

77. Instead of using temporary tables, try to replace it with a result set and a table-type variable, which is better than a temporary table.

79. Under SQL2000, calculated fields are indexed and the conditions that need to be met are as follows:

    1. The expression of the calculated field is determined

    2. cannot be used in text, ntext, image data type

    3. The following options must be formulated ansi_nulls = on, ansi_paddings = ON, ....

81. Try to put the data processing work on the server, reducing the network overhead, such as using stored procedures. Stored procedures are compiled, optimized, and organized into an execution plan, and stored in a database of SQL statements, is a collection of control flow language, the speed of course fast. Dynamic SQL, which is executed repeatedly, can use temporary stored procedures that are placed in tempdb (temporary tables). Previously, because SQL Server did not support complex math calculations, it was forced to put this work on top of other tiers and increase the overhead of the network. SQL2000 supports UDFs, which now supports complex mathematical calculations, the return value of functions is not too large, which is expensive. A user-defined function that executes like a cursor consumes a large amount of resources, if a large result is returned with a stored procedure.

83. Don't use the same function repeatedly in one sentence, wasting resources, and putting the result in a variable and then calling it faster.

85. SELECT COUNT (*) is inefficient and tries to make the most of his writing, while exists is fast. Also, note the difference: Select count (field of NULL) from table and select count (field of NOT null) the return value of the FROM table is different!!!

87. When the server has enough memory, the number of threads to be configured = The maximum number of connections is +5, so that it can maximize the efficiency; otherwise, use the "Configure thread pool of the maximum number of threads to enable SQL Server" To resolve, if the number = The maximum number of connections +5, severely damage the performance of the server.

89. Access your table in a certain order. If you lock table A and then lock table B, you must lock them in this order in all stored procedures. If you (inadvertently) lock table B in a stored procedure, and then lock Table A, this could result in a deadlock. Deadlocks can be difficult to find if the locking sequence is not well designed in advance.

90. Monitor the load Memory:page faults/sec counters for the appropriate hardware through SQL Server Performance Monitor If the value occasionally goes up, it indicates that the thread is competing for memory at that time. If it continues to be high, then memory can be a bottleneck.

    1. % DPC time refers to the processor used in the deferred program call (DPC) during the sample interval Percentage of services received and delivered. (DPC is running at a lower interval than the standard interval priority). Because DPC is performed in privileged mode, the percentage of DPC time is part of the percentage of privileged time. These times are calculated separately and are not part of the total number of interval calculations. This total shows the average busy time as a percentage of the instance time.

    2. %processor Time counter If the parameter value continues to exceed 95%, it indicates that the bottleneck is CPU. Consider adding a processor or swapping it for a faster one.

    3. % Privileged Time refers to the percentage of non-idle processor times used for privileged mode. (Privileged mode is a processing mode designed for operating system components and manipulating hardware drivers.) It allows direct access to hardware and all memory. Another mode is User mode, which is a limited processing mode designed for application, environment sub-system and integer sub-system. The operating system translates the application thread into privileged mode to access the operating system services). The% of privileged time includes the time to service the interruption and DPC. A high privilege time ratio can be caused by a large number of intervals that failed devices produce. This counter displays the average busy time as part of the sample time. The

    4. % User time represents CPU-intensive database operations such as sorting, executing aggregate functions, and so on. If the value is high, consider increasing the index, using a simple table join, and horizontally splitting the large table to reduce the value. Physical DISK:CURRETN Disk Queue Length counter this value should not exceed 1.5~2 times the number of disks. To improve performance, you can increase the disk. Sqlserver:cache hit Ratio counter the higher the value, the better. If it lasts below 80%, you should consider increasing the memory. Note that the value of this parameter is incremented after starting SQL Server, so the value will not reflect the current value of the system after a period of time has elapsed.

    5. Process:

92. Analyze the Select Emp_name form. Employee where salary 3000 if the salary is a float type in this statement, the optimizer optimizes it to convert (float,3000), because 3000 is an integer, We should use 3000.0 in programming instead of waiting for the DBMS to be transformed by the runtime. Conversions of the same character and integer data.

93. The order in which the query is associated with the write
    

    select a.personmemberid, * from chineseresume a,personmember b where  personmemberid = b.referenceid and a.personmemberid =  ' JCNPRH39681 '   (a  = B ,B =  ' number ') Select a.personmemberid, * from chineseresume  a,personmember b where a.personMemberID = b.referenceid and  a.personmemberid =  ' JCNPRH39681 '  and b.referenceid =  ' JCNPRH39681 '   (a  = b ,b =  ' number ', a =  ' number ') Select a.personmemberid, * from  chineseresume a,personmember b where b.referenceid =  ' JCNPRH39681 '   and a.personmemberid =  ' JCNPRH39681 '   (b =  ' number ', a =  ' number ') 

95. 1. If no owner code is entered then code1=0 code2=9999 ELSE code1=code2= owner Code END If Execute SQL statement 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 =:code END IF The first method uses only 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 constraints; When the owner code is entered, the second method is still more efficient than the first method, which is not only a limiting condition, but also the fastest query operation because of the equality operation. We write programs not to be afraid of trouble.

96. The new method of querying paging in JOBCN now (below), using the Performance Optimizer to analyze the bottleneck of performance, if the method is optimized in the way of I/O or network speed, if it is in CPU or memory, the current method is better. Please distinguish between the following methods, indicating that the smaller the index, the better.

    declare  @local_variable  table  (fid int identity, Referenceid varchar (20)) insert into  @local_variable   (Referenceid) Select top 100000 referenceid from  chineseresume order by ReferenceIDselect * from  @local_variable  where  Fid > 40 and fid <= 60end  and begindeclare  @local_variable  table  (Fid int identity, Referenceid varchar ()) insert into  @local_ variable  (Referenceid) select top 100000 referenceid from chineseresume  order by updatedateselect * from  @local_variable  where Fid >  40 and fid <= 60end  of different begincreate table  #temp   (FID int  Identity (Referenceid varchar), insert into  #temp   (Referenceid) select top  100000 referenceid from chineseresume order by updatedateselect * from  #temp  where  Fid > 40 and fid <= 60 drop table  #tempend

SQL Server Optimization