asked
Statistics,table scan,index seek,index scan demos...
Hi, I am looking for a code or script which is going to explain how the sql server is used statistics to create estimated execution plan before it's going to use table scan or index seek or index scan etc. I have checked one of the code as per the records the table is having 1 lack records. It's having cluster index on employee_id. When I am trying to do select on 90% of data from table with employee_id column it's doing "index seek" every time(Estimated rows and Actual row also 90k). As per the statistics information it need to do a "index scan" right? Could you please provide a script or examples to know about when it's doing table scan, index scan and index seek and lookup as well. Regards, Narendra.
To see the statistics you use [DBCC SHOW_STATISTICS][1]. You'll get three sets of data, the header, the density graph and the histogram. The primary means of SQL Server determining the number of rows for a given query is the histogram. Look at your value that compiled the query plan. See where it falls within the histogram. If it falls completely outside the histogram, prior to SQL Server 2014, the optimizer assumes 1 row. It could mean that you need a statistics update. Make sure that you have auto_create statistics set to on, and then, for some tables, some indexes, you'll need to manually update the statistics using [UPDATE STATISTICS][2] command. [1]:
https://msdn.microsoft.com/en-us/library/ms174384.aspx [2]:
https://msdn.microsoft.com/en-us/library/ms187348.aspx
18 People are following this question.
