Tag Info

New answers tagged

0

I'm using Dynamics AX fairly extensively and you can view the estimated execution plan of this query relatively easy if you declare all your @p1..@Pn parameters like this: declare @P1 bigint, @p2 nvarchar(10), .... SELECT 2 AS f1, T3.RECID AS f2, T4.RECID AS f3, ...


-1

Dynamics AX converts X++ Joins to T-SQL Cross Joins.


1

Since it seems clear that a statistic already exists for the particular column, I offer two possibilities: Object Explorer is pointed to a different database - I'm suspicious because the database has DEV in the name, so it's possible that in one case you're looking at dev and in another you're not. The plan being used is from before the statistic was ...


0

There are reasons why Oracle might change exec plan without statistics updates. There are Dynamic sampling, Bind variables peeking and Performance baselines. These features should help Oracle to adapt the execution plan with actual load and actual data characteristics. There can be various reasons why Oracle decides to use different exec plan to the same ...


-1

Basically, yes in the short term. If it were intending to go a different way it would generate a different plan, and no database (to my knowledge) backtracks and finds a different plan once it has started processing. If you are looking at a plan generated before a query was run then any statistics (rows, number of iterations of lopped constructs, and so ...


3

The short answer is that no, the same SQL doesn't automatically lead to the same execution plan. For example, one may have a small table and the optimizer may realise that there's no point in using an index since an FTS (Full Table Scan) would be cheaper than trawling through an index and then performing lookups. The "No" answer is the reason that Oracle ...


0

If there is a particular plan you want it to use, regardless of the statistics, then try using a plan guide for that query. It seems pointless to try to persuade the optimiser when there's a known behaviour you want to use. It's better to update your stats AFTER your ETL, not just to persuade your ETL to run well.


3

It looks like SQL Server is generating a parameterized query plan that can work for any value of @CustomerPartitionKey. In order to do so, it seems to treat @CustomerPartitionKey as both a partition and a column you are seeking upon. If we take a look at the query plan where we have the bad estimate (3000 rows estimated, 300000 actual), we see that there ...


3

I can reproduce the bad plan. I found three workarounds: OPTION (RECOMPILE) INNER LOOP JOIN hints A nasty, crazy rewrite: . SELECT y.* FROM (VALUES (@CustomerPartitionKey)) x(CustomerPartitionKey) CROSS APPLY ( SELECT --TOP 300 CI.ContactId, I.Ordinal, I.Identifier FROM #identifiers I INNER JOIN ...


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


2

Here is how I look at locks by process/table/lock type: SELECT HostName, "OS UserName", Login, spid, "Database", TableID, "Table Name_________", IndID, -- [Index Name], "Lock Type", "Lock Mode", Status, -- Resource, Count(*) AS "Lock Count" FROM ( SELECT Convert(VarChar(30), ...


0

Yes you can view the locks and its type during the query execution via Adam mechanics's SP_whoisactive click here to view Moreover if you want to create a block report with you can do with help of trace as explained here


1

You can view the locks for a session using sp_lock or sys.dm_tran_locks. In both ways you can filter by the session. You can also use Extended Events to do that.


2

When running the execution plan, I noticed that each one of the 5 or so queries that are parsing the filter data are costing about 12% which is over 60% of the query just to determine the data we are going to be filtering by. The query costs are based on estimates even in the actual execution plans. They do not tell you how efficient the query ...



Top 50 recent answers are included