SELECT*

I am trying to add some more additional explanation to the below points. A) SQL Server Access Methods: (1) Index Search/Per Second: Number of index searches. Index searches are used to start range scans, single index record fetches, and to reposition within an index. Index searches are preferable to index and table scans.  For OLTP applications, optimize for more index searches and less scans (preferably, 1 full scan for every 1000 index searches). Index and table scans are expensive I/O operations. (2) Full Scans/Sec : This counter monitors the number of full scans on base tables or indexes. Values greater than 1 or 2 indicate that we are having table / Index page scans. If we see high CPU then we need to investigate this counter, otherwise if the full scans are on small tables we can ignore this counter.  A few of the main causes of high Full Scans/sec are • Missing indexes • Too many rows requested Queries with missing indexes or too many rows requested w...

Logical Reads: Reading data pages from Cache. Physical Reads : Reading Data Pages from Hard Disk Buffer Cache Hit Ratio: (logical reads-Physical Reads)/logical read*100% Logical Reads:   Logical read indicates total number of data pages needed to be accessed from data cache to process a query. It is very possible that logical read will access the same data pages many times. So count of logical read value may be higher than actual number of pages in a table. Usually the best way to reduce logical read is to apply correct index or to rewrite the query. Physical Reads: Physical read indicates the total number of pages that are read from disk. In case no data in data cache, the physical read will be equal to number of logical read. And usually it happens for first query request. And for subsequent same query request the number will be substantially decreased because the data pages have been in the data cache. Buffer Cache Hit Ratio:   Buffer hit ratio will be calcu...