SQL Server database query slow for many reasons, often the following:
1, no index or index is not used (this is the most common problems slow query is programming defects)
2, I / O throughput is small, the formation of a bottleneck effect.
3, does not create a computed column does not result in query optimization.
4, memory 5, 6, the network is slow, check out the data too large (can use multiple queries, other ways to reduce the amount of data)
7, lock or deadlock (which is the most common query slow problem is programming defects)
8, sp_lock, sp_who, activities of users to view, read and write due to compete for resources.
9, to return the unwanted rows and columns 10, the query is not good, not optimal
By the following methods to optimize the query:
1, the data, log, index into different I / O devices to increase the read speed, before you can Tempdb should be placed on RAID0 on, SQL2000 is not supported. Amount of data (size) the greater the increase I / O and more important.
2, vertical, horizontal partition table, reducing table size (sp_spaceuse)
3, upgrade the hardware
4, according to query, indexing, index optimization, optimizing access to restrict the result set of data. Note that the fill factor to be appropriate (preferably using the default value 0). Index should be as small as possible, use a small number of bytes a column built a good index (see index creation), not to field a limited number of values of a single index such as the construction of gender field.
5, to improve network speed.
6, extend the server's memory, Windows 2000 and SQL server 2000 to support 4-8G of memory.
Configure virtual memory: virtual memory size should be based on a concurrent computer is configured to run the service. Running Microsoft SQL Server? 2000, may consider the size of the virtual memory settings for the computer's physical memory installed in 1.5 times. If another installation of the full-text search, and intended to run the Microsoft Search service to perform full-text indexing and querying, consider: the size of virtual memory is configured to at least the physical memory installed on the computer 3 times. The SQL Server max server memory server configuration option is configured to 1.5 times the physical memory (virtual memory size set to half).
7, increasing the number of server CPU; but must understand the need for parallel processing, serial processing resources such as more memory. Using parallel or serial assessment of stroke is automatically selected MsSQL. A single task into multiple tasks, you can run on the processor. For example, the query delay sorting, connectivity, scanning and simultaneous sentence GROUP BY, SQL SERVER according to system load conditions determine the optimal level of parallelism, complex consumes a lot of CPU for parallel processing of most queries. But the update operation UPDATE, INSERT, DELETE can not parallel.
8, If you are using a query like, then simply use the index does not work, but the full-text index, consumption of space. like''a%''to use an index like''% a''do not use an index with like''% a%''query, the query time-consuming and is proportional to the total length of field values, it can not use the CHAR type, but is VARCHAR. for the value of a long field full-text index built.
9, DB Server and APPLication Server separation; OLTP and OLAP separation
10, distributed partitioned view can be used to implement the database server of the Commonwealth.
Commonwealth is a group of separate management of servers, but they share mutual cooperation system processing load. Such data form a database server through the partition mechanism for the Commonwealth to expand a group of servers to support large-scale multi-processing Web site needs. For more information, see the design of the joint database server. (See SQL help file''partition''view)
a, in achieving the partition view, you must first level of partition table
b, the founding members of the table, in each member server, define a distributed partitioned view, and each view has the same name. Thus, reference queries were distributed partitioned view a member server in any running. System operation, as each member server has a copy of the original table, like, but in fact only one member of each server table and a distributed partitioned view. The location of the application data is transparent.
11, rebuild the index DBCC REINDEX, DBCC INDEXDEFRAG, shrink the data and log DBCC SHRINKDB, DBCC SHRINKFILE. Settings automatically shrink the log. For large databases do not set the database to grow automatically, it will reduce server performance.
The wording in the T-sql there is great stress, the common elements listed below: First, DBMS query plan is the process of dealing with this:
1 query lexical, syntax check 2, the statement submitted to the DBMS's query optimizer 3, the optimizer to do algebraic optimization and optimal access path 4, from the pre-compiled query plan generation module 5, and then submitted at the right time to address the implementation of the system 6, the final results will be implemented to return to the user.
Secondly, look at the data stored in SQL SERVER structure: a page size of 8K (8060) bytes, 8 pages as a panel, in accordance with the B tree store.
12, Commit and rollback difference Rollback: rollback of all things. Commit: presentation of the current things. Dynamic SQL is not necessary to write things in, if the write please write on the outside, such as: begin tran exec (@ s) commit trans or write function, dynamic SQL or stored procedure.
13 Where in the query Select statement using the words limit the number of rows to return, to avoid the table scan, if the return unnecessary data, a waste of server I / O resources, adding to the burden of reducing the network performance. If the large table in the table during the scan table is locked, join to access the table against other serious consequences.
14, SQL comment stated no impact on the implementation of the
15, try not to use the cursor, it takes a lot of resources. If you need row-by-row implementation, to maximize the use of non-cursor techniques, such as: the client cycle, with a temporary table, Table variables, with sub-queries, and so on with the Case statement.
Cursor can be extracted in accordance with the options it supports Category: Only enter must, in accordance to the last row from the first line of the order of extraction line. FETCH NEXT is the only allowed the extraction operation is the default mode. Rolling resistance can be anywhere in the cursor line were extracted arbitrary. Under the cursor in SQL2000 become functional very powerful, his purpose is to support the circulation.
There are four concurrent options READ_ONLY: do not allow updates through the cursor positioning (Update), and the result set rows in the composition does not lock.
OPTIMISTIC WITH valueS: transaction control optimistic concurrency control is a standard part of the theory. Optimistic concurrency control for such a situation, that is, open the cursor and update the line in the interval, only a small chance for a second user updates a row. With this option when a cursor is open, no lock control at least the line, which will help to maximize processing power. If the user tries to modify a line, this line will be the current value of this line with the last extract obtained were compared. If any value is changed, the server will know that other people have updated this trip and will return an error. If the value is the same, the server implementation of the changes.
Select the concurrent option OPTIMISTIC WITH ROW VERSIONING: optimistic concurrency control options for this line-based version control. Use row versioning, which the table must have a version identifier, the server can use to determine whether the line read into the cursor has changed. In SQL Server, this performance provided by the timestamp data type, which is a binary number, that change the relative order of the database.
Each database has a global current timestamp value: @ @ DBTS. Each change in any way, the row with a timestamp column, SQL Server timestamp column first store the current value of @ @ DBTS, then add @ @ DBTS value. If a table has a timestamp column, the timestamp will be credited to the row level. The server can compare the current timestamp value in a row and the last extracted timestamp stored value to determine whether the row has been updated. Server need not compare all the column values, you can only compare timestamp column. If the application of the no timestamp column form requires line-based version of the optimistic concurrency control, the cursor defaults to values based on optimistic concurrency control. SCROLL LOCKS this option to implement pessimistic concurrency control. In the pessimistic concurrency control, in the database, read the cursor result set rows, the application will attempt to lock the database row. When using server cursors, the line read into the cursor will place an update in its lock. If you open the cursor within a transaction, that transaction will be kept update lock to the transaction is committed or rolled back; when extracting the next row, the cursor will remove the lock. If you open the cursor in the affairs, then extract the next row, the lock will be discarded.
Therefore, whenever the user needs to completely pessimistic concurrency control, the cursor should be opened within a transaction. Update lock will prevent any other task for updating lock or exclusive lock to prevent other tasks to update the row. However, the update lock does not prevent shared locks, so it does not prevent other tasks to read lines, unless the second task with update locks are required to read. Scroll lock as defined under the cursor SELECT statement in the specified lock tips, these options can generate concurrent cursor scroll lock. Scroll lock extracted for each line, and keep the cursor to the next extraction or closed, whichever occurs first. The next extraction, the server for the new extraction line for scroll lock, and the release of the last extract Bank rolling lock. Scroll lock lock independent of the transaction, and can be maintained until after a commit or rollback operation. If the options presented to close the cursor is off, the COMMIT statement does not close any open cursors, and scroll lock is retained until after the submission, in order to maintain the isolation of the extracted data. Obtained depends on the type of scroll lock cursor concurrency options, and cursor SELECT statement locks tips. Prompt read-only optimistic locking value
Specify the NOLOCK would prompt the prompt specified in the cursor within the table is read-only.
16, using Profiler to track queries, the searching time required to identify the problem SQL; optimization optimizer with index index
17, note UNion and UNion all the difference. UNION all good
18, note that using DISTINCT, in the absence of necessary, do not use, it will query with UNION as slow. Duplicate records in the query where there is no problem
19, query not to return unwanted rows, columns
20, with sp_configure''query governor cost limit''or SET QUERY_GOVERNOR_COST_LIMIT to limit the query consumes resources. When the assessment of inquiry beyond the limits of resources consumed, the server automatically cancel the query, the query out before the kill. SET LOCKTIME set the lock time
21, with select top 100 / 10 Percent to limit the number of rows returned by the user or SET ROWCOUNT to limit the operation of the line
22, in SQL2000 before general do not use the following words: "IS NULL", "<>", "!=", "!>", "! <" , "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE''% 500''", because they do not go all the index table scan. WHere do not words in the column names added functions, such as Convert, substring, etc., if you must use the function of the time, and then create a calculated column to create the index instead. You can also modify the wording: WHERE SUBSTRING (firstname, 1,1) =''m''to WHERE firstname like''m%''(index scan), be sure to separate the function and column names. And the index can not build too much and too big. NOT IN will repeatedly scan the table, use EXISTS, NOT EXISTS, IN, LEFT OUTER JOIN to replace, especially the left to connect, and Exists faster than the IN, NOT operation is the slowest. If the column contains a null value, before its index does not work, and now 2000's optimizer to deal with. The same is the IS NULL, "NOT", "NOT EXISTS", "NOT IN" to optimize her, and "<>" and still can not be optimized, and less than the index.
23, using the Query Analyzer, view the SQL statement and the assessment of whether the query plan is optimized SQL. Generally account for 20% of the code 80% of the resources, we optimize the focus of these slow place.
24, if you use IN or OR query did not take such an index was found, using the show affirms the specified index: SELECT backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp FROM PersonMember (INDEX = IX_Title) WHERE processid IN ('M', 'F')
25, will need to check on the results of a good pre-calculated table, the query time and then SELECT. This SQL7.0 used to be the most important means. For example, inpatient hospital costs calculated.
26, MIN () and MAX () can use the appropriate indexes.
27, the database has a principle of the code from the data, the closer the better, so give priority to Default, followed by Rules, Triggers, Constraint (constraint, such as primary health outside the health CheckUNIQUE ... ..., the maximum length of data types and so are the constraints), Procedure. It not only maintenance of small, high quality programming, and execution speed.
28, if you want to insert a large binary value to the Image column, use the stored procedure, do not use the embedded INsert to insert (I do not know if JAVA). Because this application will first convert the binary value of the string (twice the size it), the server by the characters later he converted to binary values. Stored procedure, no such action: method: Create procedure p_insert as insert into table (Fimage) values (@ image), in the foreground call the stored procedure parameters of the incoming binary, so the processing speed improved significantly.