Optimizing SQL for Performance tuning

Poptest is the only training institute for developing Test and development engineers in China, aiming at the ability of the trainees to be competent in automated testing, performance testing and testing tools development. If you are interested in the course, please consult qq:908821478, call 010-84505200.

How do I speed up queries?

1. Upgrading hardware

2, according to the query criteria, index, optimize the index, optimize access mode, limit the data volume of the result set.

3, expand the memory of the server

4. Increase the number of server CPUs

5, for large databases do not set the database autogrow, it will reduce the performance of the server

6, in the query SELECT statement using the WHERE clause to limit the number of rows returned, avoid table scan, if the return of unnecessary data, wasted the server's I/O resources, aggravating the burden of the network to reduce performance. If the table is large, locks the table during the table scan and prevents other joins from accessing the table, with serious consequences.

7. Do not return rows or columns that are not required when querying

8, using SELECTTOP100/10 Percent to limit the number of rows returned by the user or Setrowcount to restrict the operation of the row

9, in the face value of the list, will appear the most frequent values on the front, the least appear in the last face, reduce the number of judgments

10, generally in a hgroupbyaving sentence before you can eliminate the redundant lines, so try not to use them to do the work of the culling line. Their order of execution should be optimal as follows:

The WHERE clause of select selects all the appropriate rows, GroupBy is used to group the statistical rows, and the having words are used to remove the redundant groupings. So groupby a having the overhead of small, query fast. For large data rows to group and have a very consuming resource. If the purpose of GroupBy does not include calculation, just grouping, then use distinct faster

11, one update multiple records score multiple updates each time a fast, that is, batch processing good

12. Slow network speed

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

14, lock or deadlock (this is also the most common problem of slow query, is the defect of program design)

15, sp_lock,sp_who, the activity of the user view, the reason is to read and write competitive resources.

16. Return unnecessary rows and columns

17, query statement is not good, no optimization

You can refine the query by using the following methods

1, put the data, logs, indexes on different I/O devices, increase the read speed, previously can be tempdb should be placed on the RAID0, SQL2000 is not supported. The larger the amount of data (size), the more important it is to increase I/O.

2. Vertical and horizontal partition table, reduce the size of the table (Sp_spaceuse)

3. Upgrading hardware

4, according to the query criteria, index, optimize the index, optimize access mode, limit the data volume of the result set. 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

5, improve speed;

6, expand the memory of 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. 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 servermaxserver memory server configuration option to 1.5 times times the physical memory (half of the virtual memory size setting).

7. Increase the number of server CPUs, but it is important to understand that parallel processing of serial processing requires resources such as memory. 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 the sorting, connection, scanning and groupby sentence execution, SQL Server based on the load of the system to determine the optimal level of parallelism, complex need to consume a large number of CPU queries are most suitable for parallel processing. However, the update operation Update,insert,delete cannot be processed in parallel.

8, if you use like to query, simple to use index is not, but the full-text index, consumption of space. Like ' a% ' uses the index like '%a ' when querying with like '%a% ' without an index, the query time is proportional to the total length of the field value, so the char type is not used, but varchar. The full-text index is long for the value of the field.

9, DB Server and applicationserver separation; OLTP and OLAP separation

10. 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. For more information, see Designing federated database servers. (Refer to SQL Help file ' partitioned view ')

A, before implementing a partitioned view, you must first horizontally partition the table

b, after creating the member table, define a distributed partitioned view 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.

11. Rebuild the 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 grammatical checking of query statements

2. Query optimizer to submit statements to the DBMS

3 optimization of optimized algebra and access paths

4. Generate query plan by precompiled module

5, and then at the appropriate time to submit to the system processing execution

6, finally return the execution result to the user next, 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.

12. The difference between commit and rollback rollback: roll back all things. Commit: Commit the current thing. There is no need to write things in dynamic SQL, if you want to write on the outside such as: Begintranexec (@s) CommitTrans or write dynamic SQL as a function or stored procedure.

13, in the query SELECT statement using the WHERE clause to limit the number of rows returned, avoid table scan, if the return of unnecessary data, wasted the server's I/O resources, aggravating the burden of the network to reduce performance. If the table is large, locks the table during the table scan and prevents other joins from accessing the table, with serious consequences.

14. The SQL Comment Statement has no effect on execution

15, as far as possible without using the cursor, it occupies a large number 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: forward-only the rows must be fetched in the order from the first row to the last row. Fetchnext is the only allowed fetch operation and is also the default. Scrollable can randomly fetch any row anywhere in the cursor. The technique of cursors becomes very powerful under SQL2000, and his purpose is to support loops.

There are four concurrency options

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.

Optimisticwithvalues: 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. Select this concurrency option Optimisticwithrow VERSIONING: This optimistic concurrency control option is based on row versioning. With row versioning, the table must have some version identifier that the server can use to determine whether the row has changed after it has been read into the cursor.

In SQL Server, this performance is provided by the timestamp data type, which is a binary number that represents the relative order in which changes are made in the database. Each database has a global current timestamp value: @ @DBTS. Each time a row with a timestamp column is changed in any way, SQL Server stores the current @ @DBTS value in the timestamp column, and then increases the value of the @ @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 a row with the timestamp value stored at the last fetch to determine whether the row has been updated. The server does not have to compare the values of all columns, just compare the timestamp columns. If an application requires optimistic concurrency based on row versioning for tables that do not have timestamp columns, Reise considers optimistic concurrency control based on numeric values.

Scrolllocks 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 option and the lock hint in the cursor SELECT statement.

Lock prompt read-only optimistic numeric optimistic row version control lock silent unlocked unlocked unlocked not locked nolock unlocked unlocked unlocked holdlock shared share share update updlock error update update TABLOCKX error unlocked unlocked update other Unlocked Unlocked Unlocked Update * Specifies that the NOLOCK hint will make the table specified with this hint read-only in cursor.

16, use Profiler to track the query, get the time required to query, find out the problem of SQL; optimizing indexes with the index optimizer

17, pay attention to the difference between union and UnionAll. UnionAll Good

18, pay attention to using distinct, do not use when not necessary, it will make the query slower than the union. Duplicate records are not a problem in the query.

19. Do not return rows or columns that are not required when querying

20. Use sp_configure ' query governor cost limit ' or setquery_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. Setlocktime the time to set the lock.

Optimizing SQL for Performance tuning