New answers tagged execution-plan
12
Short version: seek is much better
Less short version: seek is generally much better, but a great many seeks (caused by bad query design with nasty correlated sub-queries for instance, or because you are making many queries in a cursor operation or other loop) can be worse than a scan, especially if your query may end up returning data from most of the rows ...
2
Others have defined well enough the differences between seek and scan. In this instance, your query itself and the execution planner should give you the information you need to see which values are used as predicates (filters) for the query in each part. Typically it's a good practice to always add non clustered indexes on foreign keys, and depending on the ...
3
If you wish to dig the subject, a very helpful book (at least for me) is SQL Server Execution Plans by Grant Fritchey, freely available at RedGate here.
If you have a query such as
SELECT *
FROM myTable
SQL Server will likely use an Index scan, as it needs to go through all the rows to display the required results.
On the contrary,
SELECT *
FROM ...
3
Generally, seeks are good, scans are bad.
Seeks are where the query is able to make effective use of the index, and use it to find the rows it needs.
Scans are where the query is looking through the whole index trying to find what it needs.
How does SQL choose? Deep in the internals of the query optimiser, the decision is made based on your query and the ...
2
As Remus said, table statistics are stored in the database similar to other objects like tables and indexes. They play a big role in selecting the execution plan, but there are other factors.
That being said, SQL Server knows another type of statistics, statistics that give us information about recent behavior. For example the DMVs ...
8
Addition to what Remus has mentioned, I would suggest you read --
SQL Server Statistics Questions We Were Too Shy to Ask
Aarons answer to - Where are Statistics physically stored in SQL Server?
UNDERSTANDING SQL SERVER STATISTICS
11
The buffer pool is a cache of the database. There is never an 'or', things that are in the buffer pool are also in the database, always. And anything read from the database must be, even temporarily, present in the buffer pool.
As for the question: statistics are in the database so a backup/restore will preserve the statistics.
Note though that ...
5
The cost is the same (1%) for both the slow and fast cases. Does that
mean the warning can be ignored? Is there a way to show "actual" times
or costs. That would be so much better! Actual row counts are the same
for the operation with the spill.
The cost shown is always the optimizer's estimated cost of the iterator, computed according to its ...
2
Sql server's query optimizer does not take variations in disk performance into consideration when compiling a query plan. Paul White provides a great overview of Sql Server's cost based optimizer here: http://sqlblog.com/blogs/paul_white/archive/2010/09/01/inside-the-optimizer-plan-costing.aspx
Some key points are:
The optimizer isn't trying to ...
Top 50 recent answers are included
