Hot answers tagged

11

If ForeignId, ForeignTable, IsMain is not known* to be unique in ExternFile, then the QO will need to include that table to work out the count. Any time multiple rows match, the count will be affected. Join Simplification in SQL Server Designing for simplification (SQLBits recording) * The optimizer does not currently recognize filtered unique indexes as ...


10

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.


10

The most likely situation is that the new SQL 2014 Cardinality Estimator is yielding a poor row estimate for one or more joins in your query and this has led SQL Server to choose an inefficient plan. If you are able to run the query in SQL 2014 with "include actual execution plan" turned on, you can use the query below in another tab to view the real-time ...


9

The declaration of singleton in the path expression of the index enforces that you can not add multiple <Number> elements but the XQuery compiler does not take that into consideration when interpreting the expression in the value() function. You have to specify [1] to make SQL Server happy. Using typed XML with a schema does not help with that either. ...


9

Your Predicate is different to your Seek Predicate. A Seek Predicate is used to search the ordered data in the index. In this case, it'll be doing three seeks, one for each ItemState that you're interested in. Beyond that, the data is in ItemPriority order, so no further "Seek" operation can be done. But before the data is returned, it checks every row ...


8

SQL Server 2012 has an indicator in the plan itself, RetrievedFromCache, which can be either "true" or "false". This appears to be the property you are asking about. This is a sample (the last line shows the property): <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" ...


7

You could add a calculated column to the table and build an index from the calculation. For instance, the table would be: CREATE TABLE dbo.InverterData ( InverterID bigint NOT NULL , TS datetime NOT NULL , ValueA decimal(18, 2) NULL , ValueB decimal(18, 2) NULL , TS15 AS (DATEADD(MINUTE, DATEDIFF(MINUTE, 0, TS ) / 15 * 15, 0)) ...


7

is it possible to view the locks, along with the type, acquired during the execution of a query? Yes, for determining locks, You can use beta_lockinfo by Erland Sommarskog beta_lockinfo is a stored procedure that provides information about processes and the locks they hold as well their active transactions. beta_lockinfo is designed to gather as ...


6

1. How to understand estimated operator cost? Tb1 which don't have index is scanned and cost is 2 %, whereas index is being used on tb2 and cost is 98%. The heap table is only fully scanned once, but the index seek is executed 1,000,000 times. The optimizer estimates that a million seeks in this case will represent 98.4% of the total cost of executing ...


6

I can reproduce the plan that you describe on SQL Server 2012 (on prem) by running the DDL in your question and then fiddling the stats so SQL Server thinks that the table is much larger than reality. UPDATE STATISTICS [dbo].[JobItems] WITH ROWCOUNT = 10000000, pagecount = 10000000 And then running the query with OPTION (MAXDOP 1, CONCAT UNION, ORDER ...


6

Instead, try WHERE InsertedOn>=CAST(GETDATE() AS date) AND InsertedOn<DATEADD(day, 1, CAST(GETDATE() AS date)) This expression is sargable which is what you want for optimum performance. Like @Mikael indicates, you would do well to design one of your indexes so that InsertedOn is the first column, and that all the other columns used in the ...


5

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 ...


5

Which option from above will perform better? Best case, both will produce exactly the same execution plan, with the same runtime performance. This can require some careful design and some fairly advanced skills, as Rob Farley mentions in his answer. Rob also has a blog post describing the core issue, and it is also discussed in one of his chapters from ...


5

The return type of the FOR XML PATH expression is NVARCHAR(MAX), not XML. I was being confused by the fact that SSMS renders any column named XML_F52E2B61-18A1-11d1-B105-00805F49916B as if it has the XML data type. If I run the following command, I get a clickable XML result cell in SSMS: SELECT 'hi' "@id" FOR XML PATH ('this'), root ('xml'); I was able ...


5

It was all down to collation of the column. It was different from the database's (and the table's) collation. Now changed the column's collation to database's and no more implicit conversion shows up. Have no idea about the internals and why it caused the problem.


4

The optimizer strives to get a plan that is "good enough", and this is not always the optimal one. A very common reason is a too complex query. Breaking it down to a few queries helps the optimizer choose a better plan. In some cases, too many indexes on a table can also cause this, as the optimizer might use an index that is not the best one because as ...


4

According to Infinite recompile message in the errorlog on the SQL Programmability & API Development Team Blog, this message is triggered when a statement in a batch recompiles 100 times in a row. This message does not necessarily mean there is a problem; it exists to help troubleshoot statements that might legitimately be recompiling that often (for ...


4

How does the query processor execute COMPUTE BY? It is a hard-coded sequence of operations at the top of the execution tree. There is a simple stream accumulation (hence the ordering requirement), some logic to detect the start of a new group, a little computation and data value copying, and direct calls to construct the alternate TDS result sets per ...


4

work_mem That's what makes your sort expensive: Sort Method: external merge Disk: 3904kB The sort spills to disk, which kills performance. You need more RAM. In particular, you need to increase the setting for work_mem. Per documentation: work_mem (integer) Specifies the amount of memory to be used by internal sort operations and hash ...


4

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*/, ...


4

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 ...


4

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 to force a recompile, if you are willing to edit the definition of the stored procedure then you can automatically force a recompile every time. There are some significant ...


4

I think that the key here is the lot of updates, and the bloat on the index. The index contains pointers to rows in the table that are no longer 'live'. These are the old versions of updated rows. The old row versions are kept around for a while, to satisfy queries with an old snapshot, and then kept around for a while more because no one want to do the ...


3

The following statement needs 75.6% of the whole Batch-Operation This is an estimate. The percentage costs are always estimates, even in a post-execution ('actual') execution plan. The 75.6% figure represents the estimated cost of this plan as a proportion of the estimated cost of the entire batch. Given the disparity between estimated rows and the ...


3

GetItemToProcessIndex is not fully seekable because your where clause is on ItemState + LastAccessTime + CreationTime. The indexed columns and the where clause are a not perfect match. If you create a covering index on ItemState + LastAccessTime + CreationTime, for each match you get from GetItemToProcessIndex, you also get the value of your Primary Key ...


3

I think that SQL Server simply does not have the appropriate optimization rules to yield the seek-into-Orders query that you are looking for in the case of a UNION ALL on both sides of the query. Such a query plan is theoretically possible, but the query optimizer is not capable of producing it for your query. In order to reach this conclusion, I compared ...


3

None of your indexes are good for this query IX_Company_Id works only for the WHERE IX_Project_02 has not overlap with any part of the query because the leading columns do not match a JOIN or WHERE This should be better because it matches the JOIN and the WHERE although iIt does rely on a matching (Company_Id, Unit_Id) index on Unit CREATE ...


3

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 ...


3

You are looking to choose rows from one of two tables dynamically. This is generally possible to achieve without dynamic SQL. To demonstrate, here is a simplified version based on the AdventureWorks sample database: The idea will be to choose rows from either the TransactionHistory or TransactionHistoryArchive tables for each Product, based on the value of ...


3

Then it looks like an optimizer's blind spot and you should use the second query. When there is a condition joining two tables a and b: a.id = b.id and an additional condition a.id > @some_constant, seems like the optimizer uses the "index condition" for where to start the index scan on a (id) index but it doesn't use it for the second index b (id). So, ...



Only top voted, non community-wiki answers of a minimum length are eligible