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 ...
Query execution plan does not show the locking details by default, is it possible to view the locks, along with the type, acquired during the execution of a query?
In the following queries the Student table has Clustered Index on StudentID column, and the Enrollment Table also has a Clustered Index on EnrollmentID as well as Non-Clustered index on (SessionID, ...
I have an SQL statement that is taking about 40 seconds to run and the only thing I see is an high hash match and sort. I have added indexs to the temp table but still taking an long time to complete.
We have a mariadb table (stories) with above 1TB of data, periodically running a query that fetches recently added rows for indexing somewhere else.
version : 10.1....
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 ...
I know that a single batch can have more than one query plan but want to learn some more about the cases when more than one execution plans are generated. Is it because of a batch length or the use of ...
this is my first question so please be patient if I miss to add something in here.
My question is why the OR in the WHERE clause causes the query to be executed the way it is (which is ...
I'm having an issue with a multicolumn index (integer, geom), where geom are PostGIS points, and the integer is a point aggregator with business meaning. I do searches by boxes in the geom column, ...
There are two cases from different DBs and my main questions are:
why it is happening?
what can I do to stop it from happening?
OK, case #1
EXPLAIN (ANALYZE, BUFFERS)
SELECT "notes"."id" AS t0_r0,...
This execution plan has the following memory fractions:
Memory fraction input:1, Memory Fraction Output:1.
it is a very simple query:
We found the leading cause. If you join tables with the same name but in different schemas you can get this result. Same with joining two tables using the same alias (whyever this works at all)....
In the [dbo].[Programs] table, the column [Id] is the PrimaryKey (not par of a composite key). There are also quite a few other indexes on that table.
When I'm running this simple query, SELECT [Id] ...
I have a piece of SQL that seems to run really fast in Environment A, but the exact same query runs really slow in Environment B!
Environments are supposed to be the same, so what should I do and/or ...
I have 2 queries.
--update statistics #guid with all
--create statistics s1 on #guid(guid)
select * from Party a join #guid b on a.[Party_GUID] = b.guid --...
I've added a new index to account for some performance issues however the queries that I would expect to be using the new index are still are not using it. In SQL Server you can flush an execution ...
I run the following query against Postgres 9.5 which returns article information along with the currently valid price:
WITH selected_article_rm(id) AS (
I have a very complicated query that produces correct results slowly but ok in SQL Server 2012. After upgrade to SQL Server 2014 and after setting compatibility mode to 2014 (120) for the database, it ...
I face an issue where SQL Server generates a un-optimal execution plan: a
Nested Loop join and seek to the dimensional table and executes 2M reads on it.
The Sort operation Estimation is 100 rows ...
I am running a query that is giving the warning about a memory Excessive Grant.
There are too many tables and indexes used, including a complex view, and therefore it is difficult to add all of the ...
I'm running into quite a strange problem. I'm running the same script to generate data and do some matching later on, on an older 2008R2 instance. The last query (an UPDATE) does a single table scan ...
The following is a simplification of a performance problem encountered with the Query Store:
CREATE TABLE #tears
plan_id bigint NOT NULL
INSERT #tears (plan_id)
I executed the following locally in SSMS for each of two instances of SQL Server 2012 SP4-OD (11.0.7469.6) that live in separate Azure VMs and get very different execution plans:
SET STATISTICS IO, ...
I have some queries that are parameterized, but they are still creating a new execution plan each time. I am using SQL Server 2016.
Queries are like:
(@P1 varchar(1043),@P2 varchar(6))
I'm struggling to understand where a row estimate is coming from in an execution plan.
Paste the plan link
@BatchKey INT = 1, @ParentBatchKey INT = 1,
@QuoteRef varchar(50) = 'Q00018249',
I have a query where using select * not only does far fewer reads, but also uses significantly less CPU time than using select c.Foo.
This is the query:
select top 1000 c.ID
from ATable a
Execution Plan as Query
, VP.ROUTE AS Route
-- Group by Customer
, CustomerQuantity = SUM(...
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 am executing a SELECT on 2 Hosts, "old db" running mysql 5.5.54 and "new db" running mariadb 5.5.56. Both the runtime and the EXPLAIN show a difference, with the mariadb being significantly slower. ...
So, as the title says, I have a query that is run by SQL Server Reporting Services, when it ran by SSRS, I can see in SQL Profiler it's executed using RPC Protocol, and takes about 30 seconds. When I ...
I have a query the first part of which looks like this -
With CTE AS (
from tableA A
JOIN tableB B ON
My application goes slow sometimes and when i check the query plan plan for a particular operation,i see that serial pans got generated and gets stored in the plan cache and this is being taken for ...
Sql Server Database on Azure (Sql Server 2014/12.0.2000.8)
I have numerous stored procedures that all had the same basic select properties and joins, so I decided to generate a view that encompassed ...
I am mainly a .NET developer using Entity Framework ORM. However, because I don't want to fail in using the ORM, I am trying to understand what happens within the data layer (database). Basically, ...
I'm using postgres 9.2.4, and run the following query:
explain analyze select * from bubu where id not in
(select bubu_id from kuku limit 33554431);
My boss wants me to parse a set of query plans stored in a table and to determine how many nested loop operators with unordered prefetching are present in each plan. I only have around 100 query plans ...
I've encountered this condition while optimising a SP, there's a part of the execution plan I really don't get: SQL Server does an Index scan, then it filter the results. What are the reason for SQL ...
I've got a hash match spill going on here.
I've updated statistics with FULLSCAN on the tables involved so it isn't that.
Any pointers much appreciated.
I'm new to execution plans in SQL Server and I'm trying to understand why SQL Server is doing some operations. The query is:
from KSS_V_EMPLEADOS_PER_3_CERRADOS p, COMBHOMEACCT c, ...
I came across this on another forum:
PG has a limit of 100 for values in IN queries after which the index on said column is not used. For ex: a typical SELECT ... WHERE IN (...) query on the PK ...
Does the SentryOne Plan Explorer have a properties window similar to SSMS. I see the tool tip window next to the operators in the execution plan but no properties option ?
I work on SQL Server 2016 and i had severe performance issues with few queries.
So I posted several posts in here and got solution/suggestion etc and greatly improved the execution time and learned a ...
Consider the below query:
CREATE PROC dbo.GetPage @orderid AS INT = 0, -- anchor sort key
@pagesize AS BIGINT = 25
TOP (@pagesize) orderid, orderdate, custid, empid
My question is why a multi-column index is not used for a index only scan, when a partial index with equivalent information (i think) is.
CREATE TABLE test
I have the following query that is taking a long time to execute (nearly 20 seconds).
SELECT Count(*) FROM "workorder" "wo"
LEFT JOIN "workorder_fields" "wof"
I need to find number of pages scanned by the operator in a sql-server query plan.
SET STATISTICS IO ON
This returned number of logical, physical, read-ahead pages scanned per table,
but I ...
In this answer Erwin breaks down IMMUTABLE functions into those that can be inlined, and those that can't. He has an example with to_char() that shows an IMMUTABLE function over to_char() will ...
Id int primary key(clustered index)
Name varchar(255)not null (non-clustered index)
I execute stored procedur as
update table1 set table1.Name=isnull(@...
I have a query which involves a full-text search like this:
SELECT TOP 30 PersonId,
I have a query that populates a temp table with 1692 rows. I had used a table variable but performance was terrible. Now it is improved but still not ideal.
However, I then need to split the TaskName ...