1. Basics - OPERATORS

 Logical operator => operators used to define the plan in conceptual way
 Physical operator => implement the conceptual plan into actual plan using physical operator
 Physical read => actual physical read from hard disk (mdf/ldf => extent => pages => data rows)
 Logical read => its a cache memory created by SQL engine to store the retrieved row for future use if same query applied

2. Test - the scan types and other statistics like no of physical and logical read

* enable statistics "set statistics io on" 
* to see query plan click "Display Estimated Query Plan" or "Ctrl + J" and identify which type of scan used (table/seek)
* then, execute the query by involving the interested column in condition see it in "messages" tab of the output to see "no of logical & physical read"

3. Table scan - SMALL RECORD TABLES

 * searches one by one record
 * best for tables with very few records
 * It doesn't requrired to build B-tree datastructure (as like in index/seek scan)
 * Apply "test" step

4. Seek Scan/Index Scan - LARGE RECORD TABLES

 * searches using the B-tree datastructure from root node => non-leaf node => leaf node
 * best for tables with more records
 * It stores the records physicall in an order based on the indexed column
 * It takes times to construct B-tree and traverse the B-tree while searching (which is not in Table scan)

5. B-tree structure terminologies

 * For clustered index, there will be ROOT node, non-leaf and leaf node. Leaf node contains actual data
 * For non-clustered index, there will be ROOT node, leaf node which has "ROWID" used to point to either non-leaf clustered index or ACTUAL DATA (heap table)
 * to avoid the "ROWID" lookup in clustered index we can use covering index and skip the ROWID lookup and searching on data directly

6. ROWID - what and where it exists?

 * ROWID used by non-clustered index
 * which is used NON-CLUSTERED index to point either the HEAP table (if there is no index available) or NON-LEAF node of clustered index
 * when you search using non-clustered index then it inturn LOOKUP using this ROWID to traverse to the exact row data
 * how do we eliminate the RID or LOOKUP? Solution is covering index

Note: A table without any index is called "Heap" table and its default scan type is "TABLE SCAN"

7. COVERING INDEX - how to create?

 * using composite key
 * include keyword
 * include the cluster index column inside the non-cluster index column 
 * EX: customername is non-cluster, customercode is cluster, then involve "customercode" in the non-cluster index along with "customername"

8. INDEX SIZE - keep as small as possible (int < float < varchar)

 * index takes its required size to store
 * if more size then more pages and more jumps required while retrieve
 * can test by chaning the cluster index key data type from int < bigint < numeric <varchar and check the query plan
 * use below query to see no of pages taken by the index with respect to data type
 * select PhysicalStatistics.page_count, * from sys.indexes indextable join sys.dm_db_index_physical_stats(DB_ID(N'HappySaleRouter'),

  OBJECT_ID(N'dbo.customer'),NULL,NULL,'DETAILED') as PhysicalStatistics ON indextable.index_id = PhysicalStatistics.index_id 

  where indextable.object_id=OBJECT_ID(N'dbo.customer')

9. NUMERIC SEARCH - than other datatype

 * you can search by cluster index of INT and notedown no of logical read
 * then you change the datatype of above INT to other datatype (ex: nvarchar) and see no of logical read
 * there will be markable change in no of logical read because of no of pages required to store is more in other than INT datatype

10. INDEXED VIEW - what is indexed view?

 * Its a virtual table to persist the outcome of a SELECT query
 * In normal view, it does query on base table and only thing is the query plan and optimization already done
 * In indexed view, it will query in the virtual table
 * When ever there is a change in the table then this virtual table get sync and this take its own time. 
 * For more transactional table INDEXED VIEW will be more intensive

11. DATABASE ENGINE TUNNING ADVISOR - get the suggestion from the tunning advisor

 * Its 3 steps process
 * Step 1 : PROFILER => Gather the workload (data) by executing your query. For this you can use Profiler tool and save the output as .trace file
 ex: fire multiple query with tables having huge and less records
 * Step 2 : TUNNING ADVISOR => verify existing indexes are OK by loading the above recorded workload in "TUNING ADVISOR" and click "Start Analsis"
 Note: It needs extra memory => goto "Tuning Options" => "Advanced Tuning options" => "Max space for reco" => 5MB
 * Step 3 : Folllow the list of recommendation given in above steps by "Tuning Advisors" one by one
 Note: You can use "Progress" to see the rationale behind it given the recommendataion and why not if any recommendataion for any tables

You have no rights to post comments