Hot answers tagged execution-plan
Check to ensure the database compatibility level is the same on the 2 servers. I ran a quick test on a SQL Server 2012 instance and see the TOP operator is introduced if the compatibility level is 100 or lower. Unless you have a specific reason to do otherwise, it is best to use the 110 (SQL Server 2012) compatibility level on a SQL Server 2012 instance.
To get a clustered index seek, you'd need a clustered index that supports your filter (e.g. leading key would have to be Personal_ID, not ID). You can't force a seek if there's no index with a leading column of Personal_ID that supports the filter. This does not mean you should change the existing clustered index, unless this is the only query you ever ...
How do I get the execution plans when they don't display here? One option could be to capture the plan_handle and then look up the query plan afterwards for that plan_handle using the following query: SELECT CONVERT(XML, query_plan) from sys.dm_exec_text_query_plan( 0x0600050059E32C0/*Truncated for brevity, replace with your full plan_handle*/, ...
What is up with FROM part JOIN model ON 1=1? This the same as FROM part, model, which is a cartesian join and will result in a very large number of rows. Is that join supposed to be like that? You will likely help us help you if you provide details about the tables involved. Please "script" the definition of the tables, along with any indexes defined on ...
You can use the option recompile as mentioned by Erik for sure: Additionally, you can also temporarily disable the caching of that particular execution plan using DBCC FREEPROCCACHE(Plan_handle) You can find the cached plan handle (for that query) from below : SELECT cp.plan_handle, st.[text] FROM sys.dm_exec_cached_plans AS cp CROSS APPLY ...
According to the docs there are three ways to recompile a stored procedure and/or the queries inside of a stored procedure. WITH RECOMPILE This is probably the most direct way, and easiest way to accomplish your goals. If you are willing to edit the definition of the stored procedure then you can automatically force a recompile every time: CREATE ...
I reckon you have a value for ROWCOUNT. Do: SET ROWCOUNT 0 ...to turn it off.
SQL Server thinks that the dates are more selective because it knows what the value in the Start and End date fields look like. Because of the trimming on the StudentId column it has no idea how many rows there are. Once you fix that to not use the trimmed computed column it should work as expected.
Hash joins require an "equijoin predicate". So I rewrote the query as an explicit join (instead of IN...(subselect)), and instead of using A.key = B.key as the join condition, I used A.key > B.key - 1 AND A.key < B.key +1.
Without having created any tests I would guess that it's not possible to seek an index without having seek predicates, especially not if you use SELECT * and get all table columns. I would try to filter on an index-column that is part of the index you want to seek in and additionally retrieve only columns that are covered by this index. Documentation ...
Only top voted, non community-wiki answers of a minimum length are eligible