Some people often ask in the foreword forum "My database is very slow, what is the way to increase the speed?" This is an old topic, and it is a common problem and one of the most demo of DBA most resolved. I want to talk about the SQL Server tuning, if you can, try to express your own point of view, if you have a repost, you can use the address. Tuning can be tuned from multiple methods, typically tuning multiple parts. Let's take a head on the storage.
After establishing a new instance, MSSQL will be MASTER, Model, MSDB, Tempdb four system databases, three system databases of Master, Model, MSDB, suggest that the user database is stored, and the general system database should be particularly careful. The primary database protects the classification information, configuration information, user database information, job information, and database template. Once the system database is damaged, the entire instance may crash.
This temporary database of Tempdb, which has a large impact on performance. Tempdb and other databases can be increased, which can be reduced. When the data file needs to grow, the continuity of the remainder cannot be maintained. At this time, the file will generate debris, which causes performance to decline. This debris belongs to foreign fragments. To prevent foreign fragments in Tempdb, there must be enough hard disk space. Tempdb's capacity is typically placed on average. You should also allow Tempdb to automatically grow, such as you have a large JOIN operation, which has built a Tempdb capacity, which will fail. You have to set a reasonable unit growth. Because if you set it too small, you will have many foreign fragments, but will take up more resources. One of the most effective practices of SQLServer tuning is that the operation of competing resource is independent. Tempdb is a part that needs to be independent and TEMPDB and other system libraries are common. It is the most likely to access the most frequent libraries, all processed temporary tables, subquers, group by, sort, DISTINCT, connection, etc. It is best suited to a device with fast reading and writing capabilities. For example, RAID0 volumes or RAID0 1 volumes. Method of moving Tempdb:
1, Location tempdb now stored with Enterprise Manager or sp_helpdb 2, using alter database tempdb modify file (name = 'tempdev', filename = 'newpath / newfilename', size = 20mb) alter database tempdb modify file (name = 'templog ', filename =' newpath / newfilename ', size = 20MB) 3, turn off SQL Server Raised 4, and deleted the old TEMPDB file storage is the last use of NTFS format, the read speed of the NTFS format is fast than FAT32. In fact, this is part of SQL Server tuning and system optimization.
Speaking of this addition, there are four parts that affect SQL Server tuning: system adjustment accounts for 2.5%, and the database is preferably 17.5%, design tuning accounts for 20%, and the programmable space is 60%. Oh, when the DBA gets a system, design tuning and program tuning is uncontrollable, and should be able to change the software you can't change. Let's take a look at the index steps, which is conducive to everyone's understanding of the index. First, heap 1, SQLServer checks the corresponding data line 2 in the SysIndexes table, read Indid values (should be 0), SQL Server starts reading the firstiam value, getting the first page of the IAM of the stack ( 8KB) position. (IAM couples together in each area of the stack) 3, SQLServer is found according to the area address provided by IAM, a field of one area, a data page looks up until it takes the required data. Second, the cluster index 1, SQL Server checks the corresponding data line in the sysIndexes table, find IndID 1, SQL Server starts reading the value of the root column. (Column value is the address of the root page) 2, start search after finding the root page, such as the table to search for 10 records, here is the "1981" value, the value of the index "1981" with the root page Index comparison. Since "1981" is between 1900 and 2000. So SQL Server starts searching for the middle page of 1900. 3. After finding the middle page of "1900", the index value "1981" continues to compare with the index of the intermediate page, since "1981" is between 1980 and 1990. So SQLServer starts searching for the data page of 1980. (Note This step is based on the intermediate page to find data page) 4. After finding the data page where "1980" is located, continue the index of the index value "1981" and quickly find "1981" on this data page soon. "The data line.
Third, non-cluster index 1, SQLServer checks the corresponding data line in the sysIndexes table, after finding Indid, the value is 2 to 251, SQLServer starts reading the root column value. 2. After finding the root page, compare the "1981" with the index of the root page, since "1981" is between 1900 and 2000. So SQL Server starts searching for the middle page of 1900. (Note that this step is based on the intermediate page to find the leaf page) 3. After finding the intermediate page, the index value "1981" continues to compare with the index of the intermediate page, since "1981" is between 1980 and 1990. So SQL Server starts searching for the leaf page of 1980. 4. After finding the leaf page where "1980" is located, continue to call the index value "1981" and the keyword comparison on the leaf page, find the keyword "1981" on the leaf page. 5. According to the data pages and data line information provided by the data line ID, locate the specified data pages and data lines, find "1981" this record is "crystal". The stack is the fastest when the data is less than 8K, and it doesn't need to find an index. It should be used to use the index when your data is originally in a page. Cluster index, block query is the fastest when using cluster index query, if you use Between, you should be physically continuous, you should try to reduce the UPDAET to it, which should make it physically discontinuous. Non-cluster indexes are independent of physical order, designing it must have a high alternative, and can improve query speed, but these non-clustered indexes affect speed when Update, and take up space, if you are willing to use space and modification Time exchange speed can be considered. If the index is established on the view, the result set of the view will be stored, and it can be improved to the specific query performance, but it will also severely reduce performance, generally in data warehouse in data relatively stable. in. Ok, why UPDATE will affect the index, make a bit: 1 2 3 4 5 6 7 8 | 1 2 .. They are physically continuous after INSERT, each number represents a data, 1K, the division is filling before the division Full page, when doing a pointer jump, the efficiency is the best, and this is UPDATE 3 to change its data to 1.2K, more than the original page (8K) size, this SQL Server will: 1 2 4 5 6 7 8 | 1 2 ..... | 3 (1.2K) Put 3 to the last space that can be inserted. The 2 pointer is still pointing 3. If you retrieve it, when you retrieve 2, the physical pointer will jump to 3, and then jump back to 4. If you understand, you should understand why you don't advocate the UPDATE index, do not advocate the use of the VARCHAR type. It should be growing for varchar, if you frequent Update it, your index will be against it. The fill factor also provides a fill factor to reduce the impact from this, such as 20%, when inserted into the data, discovers this page to 20% available space, SQLServer will continue to insert this page Is a new page of the application such as: 1 2 3 4 5 6 | 7 8 1 2 .. When you add Update 3 for 1.2k, you will not put 3 points to other pages. Is that the bigger the fill factor, the better, not, if it is too big, waste space is nothing, mainly affecting the efficiency of query, should be a maximum consumption in the query from reading a new page. So you must set it according to your actual situation. It is also important to maintain indexes. Update is a way to destroy the index, which not only makes the pointer jump, but also makes data redundancy, and has a lot of fragments.
You need to use DBCC Indexdefrag to organize the specified table or view of the aggregation index and auxiliary index fragment. In addition, we know that the index generally has a root such as 1 2 3 4 5. When the index is established, the root is 3, take the middle. When we started to fill in data to this table, such as this column is a sequential growth, such as 1 2 3 4 5 ... 10000, then the root node offset, should be rooted or 3, just like this tree Changed a single tree, only one direction. And this phenomenon is very common. The maintenance index is also very simple, the most effective way is to rebuild the index with DBCC DBREINDEX. ----------- The top of the copyright is PENGDALI (strong V3.0) ----------------------------- -------------------- SQL Server Reconstructs the TEMPDB database when the service is restarted, if the system runs during the operation, SQL Serve does not remember After the size of the increase, restart the service after restarting the service, still restore the initial size, but if the user uses manual adjustment TEMPDB size, restart the service SQL Server will rebuild the TEMPDB to the user specified size.
Test example
Tempdb initialization size is 8MB
1) Automatically grow the tempdb to grow select b. * INTO #T from sysprocesses A, SYSOBJECTS B Restart After using sp_helpdb 'tempdb' to see Tempdb and return to 8MB2) Users use ALTER Database to adjust to 100MB,
Use mastergoalter Database Tempdbmodify file (name = Tempdev, Size = 100MB)
After restarting the service, use the Tempdb size is 100MB.
The following query can see the change of Tempdb Select A.FileName, A.Name, A.Size * 8.0 / 1024.0 as OriginalSize_mb, f.size * 8.0 / 1024.0 as currentfiles a join tempdb..sysfiles f on a. FileID = f.fileidwhere dbid = db_id ('tempdb') and a.size <> f.size
Summary: When the system automatically adjusts the Tempdb size, read and write to the file will temporarily block the file, so if we predict that Tempdb will increase to a certain size, you can adjust it, avoid recomparation during performance decrease in performance.
For some queries, the cost payd by the compilation process generates a query plan is part of the implementation of the cost of the entire query, so use the prior compiled plan to save time and avoid recompile.
Reasons for stored procedures Recompile
referenced objects, running the sp_recompile system stored procedure againsta table referenced by the stored procedure, restoring the database containing the stored procedure or any object referenced by the stored procedure, or the stored procedures plan dropping from the cache.
Delete or rebuild the process uses the with recomplie statement in the process, or use sp_recomlile when executing When the storage process is restored to restore the database next time, if the stored procedure is removed from the cache, if there is enough data that occurs if the process reference table occurs Change If the user inserts a DML statement in the DDL statement set concat_null_yields_null
While these recompilations are normal and can not be helped, DBAs and developers should not assume that all stored procedure recompiles are for normal reasons and should take a proactive approach to determine if they have a recompile problem. You can use profile tracking process of recompiling build a new Tracking event delete all, select SP: Recompile, sp: start, and sp: completed and sp: stmtcompleted and sp: stmtcompleted and sp: stmtcompleted and sp: stmtcompileted and sp: stmtcompiles can be viewed
Microsoft's Recomparation of the Minimization Application TroubleShoting Stored Procedure RecompilaionHttp: //support.microsoft.com/support/kb/Articies/q243/5/86.asp ------------- Quoted from happydreame () ----------------------
- 2006-12-4 date of accession for the following authors: Xiao Shi
In the initial stage of application system development, due to the relatively small development database data, for querying SQL statements, the complicated views of the complex view may not have the performance of the SQL statement, but if the application is submitted, after the application is submitted, along with the database Increase in data, the response speed of the system is one of the most important issues that need to be solved by the system. A very important aspect of system optimization is the optimization of SQL statements. For massive data, inferior SQL statements and high quality SQL statements can reach hundreds of times, visible to a system is not simply implementing its function, but to write high quality SQL statements, improve system availability .
In most cases, Oracle uses the index to traverse the table, the optimizer mainly improves performance based on the defined index. However, if the SQL code written in the WHERE clause written in the SQL statement is unreasonable, it will cause an optimizer to delete an index and use a full mete scan, usually this SQL statement is the so-called inferior SQL statement. When writing SQL statements, we should clearly remove the index according to what principles, which helps to write high-performance SQL statements.
Second, SQL statement writing attention problem
The following is a detailed description of the issues that need to pay attention to the WHERE clause writing of some SQL statements. In these WHERE clauses, even if some columns have an index, since the inferior SQL is written, the system cannot use the index when running the SQL statement, and the full surface scan is used, which causes the pole of the response speed. Large decrease.
IS NULL & IS NOT NULL
You cannot use NULL to index, and any columns containing NULL values will not be included in the index. Even if the index has multiple columns, as long as there is a null in these columns, the column is excluded from the index. That is to say, if there is a null value, even if the index of the index is not improved.
Any statement optimizer using is NULL or IS NOT NULL in the WHERE clause is not allowed to use indexes.
2. Joint columns
For a column, the optimizer does not use the index even if the final connection value is a static value. Let's take a look at an example. It is assumed to have an employee table, divided into two columns in two columns (first_name and last_name), now query a staff called Bill Clinton.
Below is a SQL statement using a join query, select * from employsswherefirst_name || '' || last_name = 'beill cliton';
Above this statement can be queried in the employee of Bill Cliton, but it is important to note that the system optimizer is not used by the index created based on Last_Name.
When using the following SQL statement, the Oracle system can use the index created based on_name.
Select * from Employewherefirst_name = 'beill' and last_name = 'cliton';
How do I deal with this situation? If a variable (Name) is stored in the name of the staff of the Bill Cliton, how do we avoid traversing throughout, use the index? You can use a function, you can separate the last names and names in the Variable Name, but you need to pay attention, this function does not act on the index column. Here is the SQL query script:
Select * from Employewherefirst_name = Substr ('&& name', 1, INSTR ('&& name', '') -1) ANDLAST_NAME = SUBSTR ('&& name', INSTR ('&& name', ') 1)
3. Talking> 1 LIKE statement
This is the same as the example above. The current demand is like this, requiring people who contain cliton in the query name in the employee table. You can use the following query SQL statement:
Select * from Employee WHERE LAST_NAME LIKE '% CLITON%';
Here, due to wildcard (%), the Oracle system does not use the index of Last_name. In many cases, this may not be avoided, but must have the bottom, and wildcards will reduce the query speed so use. However, when the wildcard appears in other locations of the string, the optimizer can utilize the index. The index in the query below is used:
Select * from Employee WHERE LAST_NAME LIKE 'C%';
4. ORDER BY statement
The ORDER BY statement determines how Oracle will be sorted by the returned query results. The ORDER BY statement does not have a special restriction on the columns to be sorted, or the function can be added to the column (like join or attachment, etc.). Any non-index item or computational expression in the ORDER B statement will reduce the query speed.
Carefully check the ORDER BY statement to find non-indexes or expressions, they reduce performance. The solution to this problem is to override the Order By statement to use an index, or establish another index for the columns used, and should absolutely avoid using expressions in the ORDER BY clause.
5. NOT
We often use some logical expressions in the WHERE clause, such as greater than, less than, equal to, and eliminating, and not equals, and (and), OR (or), and NOT (non). NOT can be used to reverse any logical operation symbol. Below is an example of a Not clause:
... WHERE NOT (status = 'valid')
If you want to use NOT, you should add parentheses in front of the reflective phrase and add the NOT operator in front of the phrase. The NOT operator is included in another logical operator, which is not equal to (<>) operator. In other words, even if you don't explicitly join the NOT words in the WHERE clause, NOT is still in the operator, see example: ... WHERE status <> invalid ';
Look at the following example:
Select * from Employee WHERE SALY <> 3000;
For this query, it can be rewritten as not using NOT:
Select * from Employee WHERE SALY <3000 or Salary> 3000;
Although the results of these two queries are the same, the second query scheme will be faster than the first query scheme. The second query allows Oracle to use indexes to the SALARY column, and the first query cannot use an index.
6. In and EXISTS
Sometimes a column and a range of values are compared. The easiest way is to use subqueries in the WHERE clause. Two formats can be used in the WHERE clause.
The first format is to use the IN operator:
... WHERE column in (SELECT * from ... where ...);
The second format is to use the exist operator:
... WHERE EXISTS (SELECT 'X' from ... where ...);
I believe that most people will use the first format because it is relatively easy to write, and actually the second format is much higher than the first format. In Oracle, you can rewrite almost all IN operator sub-queries to use the subquery using Exists.
In the second format, the subquery starts with 'SELECT' X '. Use the EXISTS clause No, no matter what data extracted from the table, it only views the WHERE clause. Such an optimizer does not have to traverse the entire table, and the work can be done based on the index (here it assumes an index in the column used in the WHERE statement). Relative to the in clause, exists uses a linked sub-query to construct some difficulties than in sub child.
By using the exist, the Oracle system will first check the main query, then run the sub-query until it finds the first match, which saves time. When the IN subquery is executed, the Oracle System first performs sub-queries and stores the result list in a temporary table added in an index. Before executing subqueries, the system will hang the primary query first. When the sub-query is executed, the primary query is stored in the temporary table. This is why using Exists is much faster than using IN usually queries.
At the same time, NOT EXISTS should be used as much as possible to replace Not in, although both NOTs (can not use indexes), NOT EXISTS is higher than NOT inquiry efficiency.
