All Questions

Filter by
Sorted by
Tagged with
2
votes
0answers
38 views

Advantage of querying data outside a filtered index

Is there any way to take an advantage of having a filtered index to speed DML queries targeting data sets outside that index? My playground: Table with billion rows and 100 columns Unique clustered ...
2
votes
1answer
97 views

How to rewrite CASE expression to short-circuit evaluation

I would like to know if there is any way to rewrite the EXPRESSION in the query (just expression, not the whole query) to short-circuit useless evaluation of THEN phase? Demo data: CREATE TABLE #...
8
votes
2answers
156 views

Very similar queries, vastly different performance

I have two very similar queries First query: SELECT count(*) FROM Audits a JOIN AuditRelatedIds ari ON a.Id = ari.AuditId WHERE ari.RelatedId = '1DD87CF1-286B-409A-8C60-3FFEC394FDB1' ...
10
votes
1answer
451 views

What are the ramifications of setting ARITHABORT ON for all connections in SQL Server?

So I've determined that the erratic behavior of my SQL Server is because of .Net SqlClient Data Provider's default setting of SET ARITHABORT OFF. With that said, I've read various articles that debate ...
2
votes
1answer
214 views

Implicit Conversion of VARCHAR Column to NVARCHAR does not cause expected table scan

Given the following script, I can see implicit conversion and Data type precendence have a negative impact on a query plan -- create objects CREATE DATABASE ConvertTest GO USE ConvertTest GO CREATE ...
2
votes
0answers
53 views

Poor query planning in Postgres 10

The query planner in Postgres10 seems to be ignoring the fact that my tables are clustered. I have to explicitly put "hints" in my query to have it perform the correct type of join. Here's my ...
1
vote
1answer
633 views

Postgres bypassing index with IN clause with 100+ values?

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 ...
3
votes
1answer
146 views

How can I find out if a SQL Function can be inlined?

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 ...
2
votes
1answer
232 views

Inefficient execution plan selected by postgresql

I have the following SQL query explain analyze select CERTIFICATE_ID, SERIAL_NUMBER, SUBJECT_DISTINGUISHED_NAME, NOT_BEFORE, NOT_AFTER, FIRST_SEEN, REVOKED, LINT_ERRORS from (...
2
votes
1answer
295 views

SQL Profiler plus Include Actual Execution Plan

Is there any option to show "Include Actual Execution Plan" plus query cost and recommended indexes in SQL Profiler? I see Showplan XML, but it shows only the execution plan without recommended ...
32
votes
3answers
4k views

Why does SQL Server use a better execution plan when I inline the variable?

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 = @...
5
votes
1answer
2k views

Do foreign keys affect performance in Oracle?

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 ...
0
votes
1answer
44 views

Query performance drastically depends on the database in use

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 ...
1
vote
2answers
68 views

View crossing multiple databases, but a query only using one is slow

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 ...
1
vote
3answers
4k views

SQL Server: Improve CTE performance with case statements

I need your help, I need some guiadance to improve the performance for the following given view. I have a view writen with the below code: with timeframes as ( select p.SEARCH_NUM, case when p....
1
vote
2answers
897 views

Why is my index on the join keys not used in a very simple hash join case?

I have two tables: create table animal ( aid integer, cid integer, aname varchar(255) default NULL::character varying, species text ); create table daily_feeds ( aid integer, ...
3
votes
1answer
300 views

Same query, same workstation, different execution plans

I ran the same query on the same workstation against two different databases that yielded completely different execution plans. The structure and indexes are the same. The only difference is the data....
1
vote
1answer
45 views

Understanding Sort Estimates with TOP(variable)

I have the below query and I am trying to understand the sort estimate when I use TOP with a variable. PLAN create table justint ( c1 int ) insert into justint select top 1000000 row_number() ...
5
votes
3answers
952 views

CASE statement and SARGability - specific use case

edit: SQL Server - I'm hoping this is a generic enough question I need not specify version, but most of the instances I'm working with are 2012 or later. I am not good enough to mock up data and ...
5
votes
2answers
154 views

Joining a function and a view

I have a function get_sa001 and a view Axis_RefCustomer. When I execute my function get_sa001 on it's own for a certain period let's say 2016 - 2017, the execution time is about ~6 seconds. SELECT d....
2
votes
1answer
74 views

Function which returns a table for a selected period of time

I have a PostgreSQL function on my hands. The function works but the results are very slow. I'm hopping someone will be able to help me reduce the execution time. I myself was thinking along the ...
4
votes
1answer
1k views

Aggregation in Outer Apply vs Left Join vs Derived table

Consider the following setup. There are three tables involved #CCP_DETAILS_TEMP,Period and ACTUALS_DETAILS #CCP_DETAILS_TEMP will have 50000 records, ACTUALS_DETAILS can have 5000000 records and ...
0
votes
1answer
87 views

Parallel execution sequence

Which statement is correct about the parallel executions plan? A. The CUSTOMERS and SALES tables are scanned simultaneously in parallel and then joined in parallel. B. First, the CUSTOMERS table is ...
3
votes
2answers
289 views

How to diagnose try_parse being very slow?

I had a very large query that ran slower than I thought it should do, but no amount of digging through the query execution plan helped shed any light on the slowness. Eventually I narrowed it down ...
2
votes
2answers
816 views

Query over SSRS/RPC:Completed a lot slower than SSMS

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 ...
6
votes
1answer
2k views

Why does this execution plan have Compute Scalars?

I have a simple SELECT statement. USE [AdventureWorks2014] GO SELECT * FROM Sales.SalesOrderDetail sod The execution plan has two Compute Scalar. Why is this? I was expecting to just get the ...
2
votes
1answer
75 views

Query with Variable not estimating right

I have a query with several joins that is not performing well. After a lot of triage, I decided to take the query apart and look at just the driving table. Below is an example of the query based on ...
0
votes
1answer
718 views

Does PostgreSQL Explain Analyze Execution Time consider Fetching time

I have some tests on GitHub which record how much time it takes to execute a query and fetch all results. If I check the execution plan, I get: Index Scan using post_comment_pkey on post_comment ...
3
votes
1answer
644 views

.Net Application Using Inefficient Query Plan - caused by ARITHABORT setting? [duplicate]

One of our applications is causing a problem in that there is a stored procedure that returns fine in SSMS, in under 1 sec, but in the application its taking anything up to 10 minutes, depending on ...
3
votes
2answers
2k views

IN Clause - performance difference between listing values and query

I changed this query SELECT ... FROM linkedServer.DB.Schema.Table1 t1 LEFT JOIN linkedServer.DB.Schema.Table2 t2 ON t1.ORDER_ID = t2.ORDER_ID WHERE t1.BRANCH_ID NOT IN ( '009991', '009992', '...
0
votes
1answer
144 views

How to make a query on multiple columns without writting all possible permutations?

I'm at my first question on stackexchange, as i have a few days since I struggle on this matter: I want to make a complex query(PLSQL) on a table that has col1,col2,col3,col4,col5 having values like (...
3
votes
1answer
477 views

Finding hash match aggregate

After reading following blogs, I understand that hash match aggregate causes blocking. Using appropriate indexes it can be made as stream aggregate. Blocking/non-blocking aggregate operators The ...
2
votes
2answers
189 views

SQL Server - SQL Plan Management for baseline

I am a .Net developer. I have a database upgrade coming up (from SQL Server 2005 to SQL Server 2012). The database team will be handling the migration. But I have seen forums posts saying that after ...
1
vote
3answers
382 views

Is having Estimated number of Rows > Actual Number of Rows an issue to worry about?

I've a table [order] where a simple select shows cardinal difference between Estimated Number of Rows and Actual Number of Rows and in a strange way. Generally Estimated Number of Rows skews as ...
9
votes
2answers
544 views

Why is TOP operation in SQL execution plan

After searching for a while, I decided to post this question for lack of finding an answer and apologize if there is a similar question/answer out there. When running the query below on two similarly ...
6
votes
2answers
2k views

Joining two subqueries with union all and join leads to suboptimal execution plan

I've faced the similar problem on my production environment, but I managed to reproduce this behavior on Northwind DB. Consider the following query: USE NORTHWND; DECLARE @ids TABLE ( Id NCHAR(50) ...
11
votes
3answers
840 views

How to answer why suddenly we need indexes or query needs to be changed

I am junior DBA with 3 years of experience. Our job is to fine-tune queries or advise developers that particular code should be re-written or indexes are needed. One simple question the dev team asks ...
0
votes
2answers
144 views

Identical SQL Server databases hosted in the same physical DB server

We have hosted similar SQL Server 2014 databases TestDB and TESTDB_Copy on the same physical server. The two databases are almost the same and there may be minor differences in the number of records (...
7
votes
1answer
552 views

Two servers, different execution plans. Several orders of magnitude difference in run time

Two servers, Development and Live. Live server is an Amazon RDS SQL Server Web instance. Both servers have identical schema and data. There is a good spatial index on the geometry column. On my ...
3
votes
2answers
122 views

When does SQL Server determine to use a new index?

I have been performing some maintenance on a server that runs some software the company bought, so I can't really change how the queries have been written. I have used some tools to determine some of ...
4
votes
1answer
155 views

Why does my query end up with two seeks instead of one and how do I fix that?

I have these two tables: CREATE TABLE [TaskItems] ( [ItemId] UNIQUEIDENTIFIER NOT NULL UNIQUE PRIMARY KEY, [LastOperationTime] DATETIME NULL DEFAULT GETUTCDATE(), [OwnerId] ...
0
votes
1answer
110 views

Access and filter in oracle plan

I came to a execution plan where oracle choose to access the index but instead of using the columns as predicate it only filter by them. My question is what are the reasons that can cause that(all ...
4
votes
1answer
2k views

Non-Clustered Index key columns to cover a variable where clause

To whoever reads this, sorry for the long question and thanks so much for your time. I'm needing some help on understanding what columns should be key/non key in the following situation, and what ...
2
votes
0answers
55 views

Identical query & instance but other execution plans & performance [duplicate]

I have 2 identical queries . One is executed with parameters, the other is executed with hardcoded values. Somehow sql server is creating 2 different execution plans for the 2 queries. The first one ...
2
votes
1answer
2k views

T-SQL Query runs quickly on local PC, but slow on SQL Server?

I have a query with a few joins and a couple of basic SUM(...) statements, and no parameters. It's been running without issue for months - but has suddenly started running very slowly on the server (...
1
vote
1answer
112 views

A query executed in a backup of a database is performing better than if executed in the original database

We have two databases with the same data, let's call it Database A which is the original and Database B which is the backup from Database A. A query is performing much better in Database B for example ...
2
votes
2answers
1k views

How can you tell when a query plan reuse is causing performance problems

When investigating a performance issue, a workmate suggested running DBCC FREEPROCCACHE to clear the plan cache. He came to this conclusion after noticing performance improved after a reboot. This ...
3
votes
2answers
11k views

SQL Query too slow on SUM function

I've a table with around 32 million rows having clustered unique index on CountryID,RetailerID,ProductID,DateID,EventID,TypeID and query is SELECT f.RetailerID , TypeID ...
1
vote
1answer
341 views

Relative cost of two similar queries involving XML columns

I have two queries as shown below which do the same thing. Here xmlcolumn is a column with datatype XML. I use these queries to search for a string anywhere in the XML column. I checked the execution ...
64
votes
4answers
54k views

Index Seek vs Index Scan

Looking at an execution plan of a slow running query and I noticed that some of the nodes are index seek and some of them are index scan. What is the difference between and index seek and an index ...