New answers tagged

2

For the 'good' plan, all the table variable cardinality estimates are 1 row. This is the most common outcome when using table variables, unless trace flag 2453 is enabled, or a statement-level recompilation occurs (for example because OPTION (RECOMPILE) is used, or one of the regular tables in the query has passed its recompilation threshold. For the 'bad' ...


5

Since you are running Microsoft SQL Server 2014 - 12.0.2000, the very first RTM build including the new Cardinality Estimator I would strongly suggest you try updating to one of the latest CU's. As stated in this blog post on msdn You need to apply SP1 but you must also enable trace flag 4199 in order to activate the fix. SQL Server 2014 Service ...


2

We solved the issue. The issue was caused by the new Cardinality Estimator in SQL 2014. We disabled the new estimator by activating trace flag 9481 and removing the query plan from the cache then the query worked again.


1

I found that your query had many redundancies in the conditions, and you used cross joins that were good candidates for simple joins. This might confuse the planner. Perhaps you could try the following rearrangement of the query (it is functionally exactly the same but uses joins and removes all the redundant comparisons) to see what the planner comes up ...


1

One suggestion that I saw when the new CE was announced came from Brent Ozar. You needed to test this out before you went live with 2014. Run on 2014 but with the level indicating 2012 and benchmark the times and plans. Then change to 2014 level and see which plans changed. From there you attempted to tune the bad plans. Trace flag 4199 has been around for ...


2

Based on my experience with such issue and as mentioned in This Blogs.msdn article You need to apply SP1 but you must also enable trace flag 4199 in order to activate the fix.SQL Server 2014 Service Pack 1 made various fixes on new Cardinality Estimator (new CE). The release notes also documents the fixes. So I suggest to suppress regression you ...


0

You could disable it on a per database level by changing the compatibility level; assuming they don't use SQL 2014 specific language features. But ultimately I think you need to determine which queries have bad execution plans and why. To do this you could capture a replay trace and start replaying it on a test bed with and without the trace flag / compat ...


7

It is not possible to directly connect part of the query text (e.g. GROUP BY) with a specific operation in the final execution plan. You can write a query to find plans that: Contain a Hash Match Aggregate; and The query text contains a GROUP BY clause ...which is not quite the same thing, since this will find plans where the grouping logic was ...


4

Summary The question does not provide execution plans or a full reproduction script, but based on the information given, you should use a FAST 1 hint. If you can, you should also consider converting the scalar function to the inline table-valued type. AdventureWorks-based examples Download the Microsoft sample database here. Table type CREATE TYPE ...


8

You actually have 595,947 matching rows, which is about 3% of your data. So the cost of the lookup adds up quickly. Suppose you have 100 rows per page in your table, that's 200,000 pages to read in a table scan. That's a lot cheaper than doing 595,947 lookups. With the GROUP BY clause in the question, I think you'll be better off with a composite key on ...


5

The field in your WHERE condition is not the leading field of the index. You have measure defined as NVARCHAR so prefix the literal with an N: where Measure = N'FinanceFICOScore'. Consider creating a Clustered Index on SnapshotKey. If it is unique then it can be a PK (and Clustered). If not unique then it cannot be a PK, but can still be a non-unique ...


12

Index seek might not be the best choice if you return many rows and/or the rows are very wide. Lookups can be expensive if your index is not covering. See #2 here. In your scenario, the query optimizer estimates that performing 50,000 individual lookups will be more expensive than a single scan. The optimizer's choice between scan and seek (with RID ...


8

If you are asking whether it is possible to enforce the database defaults for every connection that ever happens on the server, then no. Those defaults only take effect if something contradictory isn't set at the connection level. In any individual batch in SSMS you can use statements like SET ANSI_NULLS ON, SET ANSI_NULLS OFF, SET QUOTED_IDENTIFIER ON, and ...


25

The semantics of the two statements are different: The first does not set the value of the variable if no row is found. The second always sets the variable, including to null if no row is found. The Constant Scan produces an empty row (with no columns!) that will result in the variable being updated in case nothing matches from the base table. The left ...


2

Here is how I used the answer to my question. I'm a great fan of sp_whoisactive. If you don't have that, stop reading; download here. So I've set it up to collect a snap-shot every 10 minutes, like this: DROP TABLE dbo.HESPOmonitoring_output DECLARE @s VARCHAR(MAX) EXEC sp_WhoIsActive @output_column_list = ...


2

The number of records in TransmittedManifests table is much less than the number of records in LWTest. In such scenario, good solution is to use NOT EXISTS approach (as in Query 2) to reduce the number of actual rows. Refer Joins without JOIN - Rob Farley Now, Martin Smith’s comment helped me demystifying the count per execution (estimate) and total count ...


4

You should retype the various Name columns from nvarchar(max) to nvarchar([right size]). It's unlikely that names will be up to 2GB in length, and making them max sized prevents them being used as a key in an index. A good general rule of thumb is to avoid large object data types wherever possible. You'll probably need to make that change to the EF code, ...


2

The problem is that TopN Sort in the top left. It has to pull in all possible rows to then find the smallest one. If you have an index on DiameterInches, it's likely to start searching on the smallest ones first, and give you vastly improved performance. Of course, then you'll need an index on Widgets.SizeId, so that it can find those easily. And it'll ...


6

I believe you will have to do some XML query work to get that estimated cost. See if this is what you are looking for: ;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT TOP 1000 st.text ,cp.size_in_bytes ,cp.plan_handle ,QP.query_plan ...


2

The estimate for the inner (lower) input to a nested loops join is per iteration in SSMS. The 'actual' number of rows shown in SSMS is a total over all iterations. This often causes confusion, and is the result of a questionable design decision. The one row estimate therefore needs to be multiplied by the estimated number of executions. Many people prefer ...


4

Assuming that your query includes literal values (rather than being a prepared statement where the values change per execution) and assuming that the database isn't configured to force every literal to be treated as a bind variable (which has other side effects), the query SELECT col1 FROM table WHERE col2 = 'foo' is completely different from SELECT ...


1

See this example query for getting these execution plans. Now it's ordered by CPU consumption: SELECT qs.execution_count, qs.total_worker_time/1000 total_worker_time_msec, ((qs.total_worker_time/1000)/qs.execution_count) AVGtotal_worker_time_msec, qs.max_worker_time/1000 max_worker_time_msec, qs.total_elapsed_time/1000 ...


4

You would just pull the execution plans for the most relevant queries from the plan cache. I would query sys.dm_exec_query_stats, and join to sys.dm_exec_query_plan and sys.dm_exec_sql_text to get the plan and query text, respectively. Within sys.dm_exec_query_stats you can pull queries with the highest IO, CPU, execution count, etc. - whatever is most ...


7

Now my question is ,since nested loops does a key lookup once for each row returned from seek,should seek reads be 25*3 :75 same as key lookups If the question is "should the seek also require 75 reads?" then the answer is no, for the reasons Itzik gave, and quoted in the question: Seek to the leaf of index: 3 reads (the index has three levels) ...


2

Why query plan changes by number of rows? If you are going to select 80% rows of the table then full table scan is most efficient and If you are going to select less than 10% rows from the table then reading an index followed by a table access by rowid may be more efficient than a full table scan. Optimizer makes decision according to the statistics of ...



Top 50 recent answers are included