sql performance tuning steps

an execution plan, and stored in a database of SQL statements, is a collection of control flow language, the speed of course fast. 48, the return value of the function is not too large, this overhead is very large. A user-defined function that executes as a cursor consumes a large amount of resources if a large result is returned with a stored procedure. 49, as far as possible to avoid repeated access to the same or several tables, especially the lar

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' pageiolatch%' order by Wait_type Through the above

rownumObviously, this writing will result in reading the table first, then sorting, and then taking the first 5 records, the plan is as follows:If we write like this, the semantics are the same, but it saves a lot of reading and sorting costs:Select/*+ Index (T1,IDX1_T1) */* from T1 where rownumThe execution plan for this SQL is as follows:As you can see, the SQL does not follow the hint instructions and s

1. Check the database space usage to see which tables are consuming larger disk spaceExecute the following statement:SelectO.name, SUM (p.reserved_page_count) asReserved_page_count, SUM (p.used_page_count) asUsed_page_count, SUM ( CaseWhen (p.index_id2) Then (p.in_row_data_page_count+P.lob_used_page_count+p.row_overflow_used_page_count)Elsep.lob_used_page_count+P.row_overflow_used_page_count End) asDataPages, SUM ( CaseWhen (p.index_id2) then Row_countElse 0end) asrowcounts fromsys.dm_db_partiti

DISTINCTdatabase_id,object_idFrom Sys.dm_db_missing_index_details) as Ddmid on ddmid.database_id = ddios.database_idand ddmid.object_id = ddios.object_idWHERE Ddios.page_lock_wait_in_ms > 0 and object_name (ddios.[ OBJECT_ID]) like ' Pos_transmst 'ORDER by Ddios.page_lock_wait_count DESC;/*******************************************************************************************--page IO Latch Wait *******************************************************************************************/ SEL

not in with not existsIn a subquery, the NOT IN clause performs an internal sort and merge, in either case, not in is the least effective because it performs a full table traversal of the table in the subquery, and in order to avoid using not, it can be written as an outer join (Outer Joins) or not EXISTSLow efficiencySELECT *FROM dbo. OrdersWHERE id_p>0 and Id_p not in (SELECT IdFROM dbo. Persons)EfficientSELECT *From OrdersWHERE id_p >0 and not EXISTS (SELECT IdFrom Persons WHERE persons.id =

specific application requires the participation of the Operation Department, product department, Development Department, O M department, QA (test), Monitoring Department, and so on. The same project (business) has performance problems, performance Tuning is not only required by the O M department, but also solved this perf