sql query performance tuning tips

compression is 167M, the size of the table after compression is only 40% of the original table, the effect is obvious, and because most of the table's fields are only IDs, the relative repetition value is not too much.However, we see that the size of the index varies substantially, so we continue to compress the index:5. Compression indexAlter index idx_tb_wcb_id on Tb_wcbrebuildwith (Data_compression=row)6, the comparison after the index compressionsp_spaceused ' ms_visit_qst_opt '/*name

Microsoft, one is not perfect function also dare to take Out (2000) The second functional architecture of the outstanding design makes the function has a strong continuity and Scalability (2005 2000 of the function to retain and improve). The optimization tool is simple to use 1: Use SQL Server Porfiler (Event Viewer) to record all the steps of the operation database in the business system and save it as a working file. 2: Open sql2005 's database

index, the TPS from the previous 110 to 6000 such. Two. The SQL statement index does not work: When performing a performance test, the server is running under the circumstances: The CPU of the database is up to 90%-99.9%, and the CPU of the application is low; Sufficient memory (free memory >20m); Network normal; Disk input/output is normal; CPU-High Monitor diagram The logs a

Changing the vast majority of SQL queries to stored procedures can undoubtedly improve some performance.All operations that use "select * from xxx" are specific to the required fields.Join is used to connect more than two tables with a large amount of data, and views are used for queries with little changes to the basic data table, and indexes are created for the view. The reason is from the SQL Server onli

We recommend that you use the following methods and Suggestions after testing! 17.Use the table alias (alias) When connecting multiple tables in an SQL statement, use the table alias and prefix the alias on each column. in this way, the parsing time can be reduced and the syntax errors caused by column ambiguity can be reduced. (Note: ColumnAmbiguity refers to the SQLDifferent tables have the same columnName,When SQLThe column appears in the s

released, the data page will not be in the memory of the data cache, This causes the memory problem to be the bottleneck of the disk. PAGEIOLATCH_EX is write data, this is generally the disk write speed obviously keep up, and memory is not directly related. Here is the resource wait time for query pageiolatch_x: Select Wait_type, waiting_tasks_count, Wait_time_ms, Max_wait_time_ms, Signal_wait_time_ms from sys.dm_os_wait_stats wherelike' pageiolat

Limit query rows and Leleti high-performanceThis rule is very simple, and here is no detail.Use Search to parameterize (sargable conditions) to improve performanceSargable by the search ARGument Able shorthand, literally means that the searching can be parameterized? is still more obscure ah ...In summary, using sargable judgment can help the query optimizer make more efficient use of the index, and increas

In our OLAP implementation, SQL is extremely complex and uses a lot of temporary tables. During the installation of tempdb, the local disk path for SQL Server installation is selected by default, and no disk array is used. When learning PostgreSQL, we found that many experts suggest placing the temporary tablespace on SSD or using RAID0 + 1 to increase the write speed and

the query executes. It is clear that the real plan will be more accurate because it is physically running queries against the database. However, this approach may not be the best option for systems with large load and/or long-term queries. In a typical case, I run this tool when I think there is a problem with a particular query, or if I expect a query to be cal

When we are doing SQL optimization, we often encounter the need to sort a large number of datasets and then take the results from the pre-ordered collection, in which case, when we follow the usual thinking to write SQL, the system will read the filter to get all the collection, then sort, and then take out a very small number of results from the sorting results, the process, The scanning, filtering, sortin

I. Problems with indexing (index) 1. Indexes (index), using or not? It's a problem. Whether a full table scan or an index range scan mainly considers the SQL query speed problem. The main concern here is the number of records to read. According to Donald K. Burleson, the principle of using index range scanning is: For tables with the original sort of data, queries that read less than 40% of the number of

The failure effect is described above. Just want to explain that in the performance tunning aspect can only according to the situation to seek the reason and solves. This is an interesting process. Great principles are experiences that help us make fewer mistakes. Therefore, poor design inevitably leads to performance problems. Inexperienced programmers are bound to write bad code. But a good design can mak

ObjectiveIn the previous article, we analyzed how the query optimizer works, including: detailed operation steps of the query optimizer, analysis of filter conditions, optimization of index items, and other information.In this article we analyze the detection of several key indicator values in the course of our operation.These indicator values are analyzed to analyze the operation of the statement and to an

10.1 Order problems for connecting query tablesThe parser for SQL Server processes the table names in the FROM clause in a right-to-left order, so the table that is written in the FROM clause (the underlying table driving tables) will be processed first, and in the case of multiple tables in the FROM clause, the tables with the fewest number of record bars must be selected as the underlying table. When