execution-plan's questions - English 1answer

586 execution-plan questions.

I have the following tables (taken from the Sakila database): film: film_id is pkey actor: actor_id is pkey film_actor: film_id and actor_id are fkeys to film/actor I am selecting a ...

https://www.brentozar.com/pastetheplan/?id=HyUBphZy7 So the developer of this app should really be figuring this out, but it's taking too long and we are suffering from performance issues. I'm ...

Here is the situation. I have a fairly simple query. It returns ~22k rows: select * from tbl_A When I run this query in SSMS (2017 7.6), I get a fairly simple plan: SELECT <= Clustered Index ...

We have a 10GB table with 30GB indexes on it. The table consists of a few dozens of attributes a primary key on the id (varchar(32) type) and a dozen of indexes on anything but id key (except the ...

I have a SQL query that I am trying to optimize: DECLARE @Id UNIQUEIDENTIFIER = 'cec094e5-b312-4b13-997a-c91a8c662962' SELECT Id, MIN(SomeTimestamp), MAX(SomeInt) FROM dbo.MyTable WHERE Id = @...

I have read threads here and here and I get that elapsed time is the total duration of the task - and if the elapsed time is less than CPU time, the query went parallel. After typing that, I was ...

I read that only Trivial Plans can be Simple Parameterized, and that not all queries (even when the plan is Trivial) can be Simple Parameterized. Then why is this plan showing Full Optimization, and ...

For two last statements of the code below produce actual execution plan. You can see that startup predicate on @Par1 is placed in a different position that completely changes the actual number of rows ...

I have two inline functions that run quickly enough alone. When I left join them like this: select * from Function1(-1) F1 left join Function2(-1) F2 on F1.key = F2.key ...I get my result set in ...

Is it ever possible for a SQL Server query running with an effective degree of parallelism x to have parallel workers assigned to more than x distinct schedulers? Even if the execution plan has many ...

My C# application issues a query via Entity Framework which runs quickly in dev and test, but slowly in production. I thought perhaps a bad query plan had been cached, so I tried DBCC freeproccache ...

I have this query: SELECT d.devicename, d.template, d.deleteflag, SUM(isUnexpected(gv.value,sv.param1,sv.param2,sv.comparator,sv.operator) ) totalDeviation FROM vm_wells_situ s ...

My execution plan is posted on PasteThePlan.com. The second nested loop is warning that there is no join predicate. I'm still new to this so I'm confused. Is that because I am using a CTE to determine ...

As a part of partitioning table,1M records were inserted into a table using insert statement and I forgot to capture the time taken to insert. Is there any way to find out the time taken to execute ...

We have a SP query for a report, and every now & then (once a week), SQL will create and utilize a bad query plan. CPU spikes, subsequent queries back up and time out, etc... after about 30 ...

I have a query which contains a subquery. When executed on a local copy of my database it runs as normal and executes within < 1 second. I've noticed however that if I run this same query on our ...

A few days ago I had a question about warnings in the execution plan Type conversion in expression may affect “CardinalityEstimate” - on a computed column? and that was solved, thanks Paul. Now I ...

I have an Azure SQL Database that powers a .NET Core API app. Browsing the performance overview reports in the Azure Portal suggests that the majority of the load (DTU usage) on my database server is ...

I would like someone to explain to me how each of the two queries below would generate their execution plan in SQL Server where my function [dbo].[GetAdditionalProjectDatesForCalendar] is an ITVF (...

[Salutations] (check one) [ ] Well trained professional, [ ] Casual reader, [ ] Hapless wanderer, I have a (check all that apply) [ ] query [ ] stored procedure [ ] database thing maybe that ...

I'm running a query which updates an Oracle table through Datastage. The query is taking a long time to execute so I want to examine the query/session on that Oracle table in the database and ...

Ok, so I have a non-stored-procedure query that we're using in an SSRS report. This query has been hellishly slow (I've had the original version of this query running for the past two hours, still not ...

I'm having real issues with the performance of this query in my application: SELECT DISTINCT ON (pole_prices.station_id, pole_prices.fuel_grade_id) pole_prices.* FROM "pole_prices" ...

Regardless of whether you create indexes for your foreign keys (which if course you should), does having foreign keys have any impact on performance in Oracle databases? One specific area i was ...

I have a slow stored procedure like CREATE PROCEDURE [dbo].[MyProc] ( @Param1 nvarchar(20), @Param2 nvarchar(20) ) AS BEGIN set nocount on ;with cte (col1, col2, col3) as ( ...

Is an implicit conversion in a UNION ALL when one of the data type is VARCHAR(MAX) something to worry about? e.g. DECLARE @value1 VARCHAR(1)= 'a'; DECLARE @value2 VARCHAR(MAX)= 'a'; SELECT @value1 ...

We have a reporting query which is erratic in terms of execution plan and run duration. It is either 5 seconds or as slow as 5 minutes. The query is a Select statement with no DML involved. One thing ...

I've table, example as Id int primary key(clustered index) Name varchar(255)not null (non-clustered index) .. .. Other columns I execute stored procedur as update table1 set table1.Name=isnull(@...

Please could somebody explain why the first query below scans on every join to the REFERENCE_VALUES table but the second query seeks on the same joins? The first query does have other joins, but I ...

I have a query that calls a remote table through Linked Server. However, in the execution plan the Remote Query is 71% of total cost and the time taken to run is over 4mins. It returns all rows in the ...

I'm on SQL-Server 12.0.5203.0 There is a query covering multiple databases. I think It is not useful to post the actual query (as it is rather complex and confidential, just think of a query like ...

is there a way to fetch the XML generated by the "Actual Execution Plan" in SQL server 2012 - 2014, but via JDBC driver for Java -- sqljdbc_6.0.8112.100_enu? (preferable forward compatible with 2016) ...

I have a query that calculates row_Number(). The table has a clustered index on the same columns (and order) as the partitioning and ordering for the row number. When using a merge join (many to one),...

I have a group of 5 developers that need to be able to view the plans of queries they have written for a database, and that database only. I refuse to make them a member of sysadmin. I do not want ...

I have a merge join in azure data warehouse. My estimated execution plan currently shows it as a many to many join. I'd like to know if it is possible to achieve a many to one join. Currently I'm ...

I am attempting to update a query that utilizes the IN operator within a WHERE clause predicate with EXISTS to compare potential performance improvements and better understand what is happening behind ...

I have a basic [UserActivity] table that captures an ActivityTypeId per UserId and the ActivityDate at which the Activity occurred. I am writing a query/stored procedure that allows for input of the @...

Azure data warehouse supports both clustered and non-clustered indexes in addition to columnstore (which is the default for any new table). I know that having a large clustering key is normally ...

I have the following stored procedure, inserting new rows in a join table: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[InsertNewRows] @PropertyId int, @...

I ran the sp_BlitzCache script from the Brent Ozar toolkit on a server that I am monitoring (I am new at the company for about 3 weeks). The result of the procedure has the following values: ╔════════...

Given a view like this example, crossing multiple databases in the same database cluster, with each database having a unique INSTANCE_NO. Please note that each database can be split using the ...

I have noticed execution plans sometimes include a StatementParameterizationType attribute. What is this, what does it mean, and when does it appear?

I have two databases with the same data, one on an SQL Server 2008 R2 instance, and one on an SQL Server 2016 instance. Both SQL Server instances are on the same machine, and the database files are on ...

I had a query change execution plans for an unknown reason. The stats were gathered about 24 hours before the change and the rate of data increase has not changed over the period in question. Here is ...

I have a table that captures the host platform that a user is running on. The table's definition is straightforward: IF OBJECT_ID('[Auth].[ActivityPlatform]', 'U') IS NULL BEGIN CREATE TABLE [...

I'm a little bit confused about how query optimizer eliminates unnecessary columns from view's query when you need to select only specific one. Here is my view: CREATE VIEW Schema1.Object1 AS ...

I have a (anonymised) query plan There is a table (Object8) which has a high variation on the number of Actual Rows vs Estimated Rows on two columns (Column2 and Column6) which can be seen in the ...

I am using SentryOne Plan Exporer. When I right click in the execution plan pane in SSMS, the first option is View with SentryOne Plan Explorer Clicking this fires up an instance of Plan ...

I have a scenario where a search on a range with the same value on both sides is using a plan with an index that is not the composite index I expected. After some work, I was able to generate a sample ...

Fulltext queries against this database (storing RT (Request Tracker) tickets) seem to be taking a very long time to execute. The attachments table (containing the fulltext data) is about 15GB. The ...

Related tags

Hot questions

Language

Popular Tags