All Questions

Filter by
Sorted by
Tagged with
3
votes
1answer
114 views

Direct values on predicate produce not-so-good plan

I'm using StackOverflow dump to run some tests. In particular, I'm querying this table: I have created this index: I'm running the following query (just forcing an index to test alternatives) I ...
7
votes
3answers
131 views

SQL Server creates different plans when OR condition is re-arranged

I was reviewing an under-performing query which looks like this: WHERE manymany.Active = -1 AND manymany.Check1 = -1 AND manymany.WebsiteID = @P1 AND CURRENT_TIMESTAMP BETWEEN ISNULL(manymany....
4
votes
2answers
94 views

Seeking Help Optimizing Self-Joins

This query takes ~5x longer than it should due to the WHERE IN() statement at the end. I am a junior DBA in over my head trying to figure out a way to optimize it. Is there a way to check IN() the ...
1
vote
1answer
226 views

Adhoc queries memory consumption in SQL Server

I have a SQL Server 2012 and it has just one (1) database in it. When I generate a server dashboard report it shows that 50% of CPU usage is related to my database and the rest is associated to Adhoc ...
3
votes
1answer
114 views

Any way to tune / optimize this query?

Actual Execution Plan here: https://www.brentozar.com/pastetheplan/?id=H1fgOQTRX Non Clustered Indices did not seem to change much. SELECT DISTINCT TOP ? Object1.Column1 AS Column2, Object1....
11
votes
2answers
3k views

MAXDOP = 1, Query Hints and Cost Threshold For Parallelism

If an instance has MAXDOP set at 1 and query hints are used to allow specific queries to go parallel, is the Cost Threshold For Parallelism value still used by SQL to decide whether or not to actually ...
5
votes
2answers
108 views

How to teach the optimizer to use index instead of fts with join on data logging master/detail tables?

SQL Server 2012 optimizer does not get it right. Test case, summary: This is a simplified test scenario. DDL statements at the bottom. I have two tables for data logging, A and B. There is a 1:n ...
3
votes
1answer
174 views

Optimize query without rewriting it - vendor app

Here is a query that uses a lot of CPU on my SQL Server (8 cores): DELETE TOP(1) rd FROM (SELECT TOP 99.9999 PERCENT * FROM APM_IisBb_Request_Details ORDER BY RequestDate ASC) rd JOIN ...
2
votes
5answers
3k views

LIKE Query Optimization

I have the table shown below: create table Order_Details ( ID int not null primary key ,Order_ID int ,Order_Description nvarchar(40) ) We have inserted 27,500,000 rows in above table, and when I ...
2
votes
1answer
59 views

View columns elimination

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 ...
2
votes
0answers
192 views

Poor SQL query optimization of Recursive CTE with iTVF, environment specific

I'm developing a recursive CTE which utilizes an inlined TVF. In our dev environment, the recursive CTE executes exactly as expected. In our production environment, the first iteration of the CTE ...
1
vote
1answer
111 views

Why is an index I created not being used in a query execution?

Background: this question stemmed from another question I asked, so you can get additional DDL/indexes from there, if need be. I have a query inside an indexed view, like this: SELECT [a].[...
3
votes
1answer
2k views

SQL Server performance issue (CXPACKET wait type)

I have a performance issue with production SQL Server 2012 which has 24 cores and 32 GB of RAM. From wait type I can see CXPACKET is on the top based on the following SQL Server metrics, should I ...
1
vote
0answers
90 views

SQL Server 2012: An older Index that used to help a SP overnight

Hopefully this isn't a repeat question. I haven't had much luck finding an existing answer. I have an index on a somewhat large table (About 60 million rows, 25gbs with indexes) that has been in ...
2
votes
1answer
89 views

SQL Server - Do automatically created column statistics get dropped automatically after time?

The retired SQL Server 2000 documentation https://www.microsoft.com/en-us/download/details.aspx?id=51958 states: "The statistics generated for a column can be deleted if you no longer want to ...
2
votes
0answers
128 views

Why does optimizer estimate 1 row for this plan?

Here's my plan I'm having trouble with: https://www.brentozar.com/pastetheplan/?id=r168tltbe I'm betting this is something easy I'm not seeing. I have a query that is running very slowly because ...
2
votes
1answer
145 views

How to remove a key lookup where two tables are joining to optimize speed?

I am trying to optimze the execution speed of a poor running stored proc. I have reduced a couple of key lookups and the relevant query now runs under 2 minutes. But, ideally, for some hundred rows, ...
3
votes
0answers
3k views

Optimize long LEFT OUTER JOIN query

I have query which has 40+ left outer joins. Query is not that slow but I'm interested that can I optimize this (procedure is most used one in db). I had idea that "a" table has type column which ...
1
vote
1answer
412 views

Stored procedure execution time went from under 2mins to “infinity” without modifications

I have an SSRS report that pulls it's source from a vendor's MSSQL 2012 database. It's an extensive dataset so I've created a stored procedure that runs at the same time every morning. There's quite ...
1
vote
0answers
739 views

How to avoid Hash Match by ordering the underlying view or subquery in SQL Server

Suppose a is a table with 800 rows and a primary key on Id and bView is a view that queries several tables and ends up with a total of 8000 rows, and I want to do the following type of query without a ...
2
votes
0answers
164 views

Procedure runs slower over time; fixed by cache clear or reboot

We are experiencing a strange issue where an application which was fixed by creating missing index requests, updating stale stats, and removing lookups and other scans, was running fine once fixed, ...
13
votes
2answers
22k views

Fill in missing dates with data value from previous populated date for group

Picture help desk tickets that gets transfered between departments. We want to know what the department is at the end of the day for each ticket for each day that the ticket is open. The table ...
0
votes
0answers
532 views

SQL query too slow to fetch single row of data

we have this SP, running extremely slow to fetch a single row of data from tables within 100K rows, which seems not that big. It takes onn avg 15-20 mins to complete. We have tried to remove all ...
9
votes
3answers
3k views

Execution Plan is NOT using INDEX, It uses Table Scan

I know when it comes to using an index or a table scan, SQL Server uses statistics to see which one is better. I have a table with 20 million rows. I have an index on (SnapshotKey, Measure) and this ...
8
votes
3answers
329 views

How to improve estimate of 1 row in a View constrained by DateAdd() against an index

Using Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64). Given a table and index: create table [User].[Session] ( SessionId int identity(1, 1) not null primary key CreatedUtc ...
0
votes
1answer
14k views

Filter first then join or join first then filter?

Consider 5 tables with more than 1 billion records each. A query needs to join them. And I know that less than 10% of their records will be require. Say they all have a Date dimension, and only data ...
2
votes
1answer
798 views

Set a filegroup to readonly

I have a set of dimensions which are updated only during specific times. I have moved these tables to a specific filegroup named Dimensions. I wanna keep this filegroup as readonly. When ETL runs, it ...
4
votes
2answers
477 views

A lot of ad-hoc queries impact on server

We have a third party application which generates a lot of ad-hoc statements (our server has 16GB of memory and there are 140000 ad-hoc queries present in the cache).I've enabled 'Optimize for Ad-hoc ...
2
votes
1answer
53 views

Does the query optimiser re-use usable data?

This question is to expand my knowledge on how the query optimizer of SQL Server works. I like to know how stuff works behind the scenes so I can better optimist it/debug it. I have the following ...
9
votes
5answers
895 views

Getting a scan though I expect a seek

I need to optimize a SELECT statement but SQL Server always does an index scan instead of a seek. This is the query which, of course, is in a stored procedure: CREATE PROCEDURE dbo.something @...
6
votes
2answers
514 views

Will the Query Optimizer Ignore A Fragmented Index?

Scenario: I have a heavy OLTP table with an index. I see many inserts, updates and deletes and the index fragments heavily within a day or less. While on day one of the index build, the optimizer ...
1
vote
3answers
3k views

Improve a query using cursor to SET Based approach

I am still new to Query optimisation, and I have a Stored Procedure which uses a Cursor to go through each row within the Table, and performs the following operations: Calculate the Time Difference ...
2
votes
2answers
3k views

Force Sql server to use index seek in merge join

I am testing Merge join in sql server. I have an INNER JOIN and force the optimizer to do MERGE JOIN: ID in Personal Table is a Primary key ID in Abteilung Table is a primary key There is no other ...
2
votes
1answer
351 views

How can I find out what stats objects were used during a SQL Server 2014 query?

I’ve read here that trace flags 3604, 9292 and 9204 enable debugging output that shows what statistics objects were used by the cardinality estimator during query compilation in SQL Server 2012. They ...
5
votes
2answers
133 views

Help optimising delete statement

Using SQL Server 2012 Standard - I'm running a delete on a table based on the contents of another table. It's taking rather a long time (5 hours) and doesn't seem to be optimal to me, would appreciate ...
1
vote
1answer
389 views

storing as smallint, querying as int

A multi-billion-row fact table in our database has 10 measures stored as int columns. The value ranges for some of these columns won't ever be above the +/-32K range of a smallint. To save I/O, we're ...
1
vote
1answer
552 views

Is there a way I can shorten the below query into a single query?

I have the below query. IS there a way I can put this into one single query? If not, can I shrink it further anyway? Please advice. DECLARE @highRegion TABLE(regionId INT, countR INT) DECLARE ...
15
votes
2answers
438 views

Index on a persisted computed column not seekable

I have table, called Address, that has a persisted computed column called Hashkey. The column is deterministic but not precise. It has a unique index on it that is not seekable. If I run this query, ...
1
vote
1answer
259 views

Dynamic Partition Elimination when using “OR ” or “IN”

Say I have a query like this: SET @ContainerId = 5 SELECT * FROM Container.Container_Partitioned WHERE containerId = @ContainerId When I run this, I get a query plan with Actual Partition ...
12
votes
1answer
2k views

How does SQL Server's optimizer estimate the number of rows in a joined table?

I am running this query in the AdventureWorks2012 database: SELECT s.SalesOrderID, d.CarrierTrackingNumber, d.ProductID, d.OrderQty FROM Sales.SalesOrderHeader s JOIN Sales....
2
votes
1answer
3k views

Optimise select distinct from large table

I have a table that contains 320,071,712 number of records, the report that I am currently working on can filter the records by date, after which the number of records comes down to 145,878,852. The ...
2
votes
0answers
50 views

Filtered Index is not selected once an identical, non-filtered, index is made [duplicate]

I have a lot of historical data, but 98% of the time, my data entry team only looks up data from the last 5 days. So I added a filtered index to help those lookups be faster. My test query looks ...
3
votes
1answer
5k views

Find out beforehand how many records a query has

I develop reports in ExtJS resulting of queries in SQL Server 2012. I use Ext.Grid with paging, therefore I can use offset to limit the amount of records a query will return. But for this ExtJS ...
4
votes
2answers
632 views

Moving data from table with VARCHAR(50) fields to table with numeric fields increases table size

I want to understand why migrating data in a table with all VARCHAR(50) fields to a table with optimized smaller types caused the new table (containing 61,065,164 rows) to be 4.46 GB, which is larger ...
0
votes
0answers
45 views

SQL Server 2012: query to see queries that are consuming more resources [duplicate]

I'm trying to find/build a query to show me those queries that are consuming more resources in SQL Server so that I can optimize them or at least have a statistical idea of what is really happening. ...
1
vote
1answer
218 views

Incorrect unit allocation size. error comes when Tuning the Database

Here I am Using the SQL IDERA tool for checking the Index and performance of the Database, then i found below error, can any one tell what is it talking about. Incorrect unit allocation size. Drive ...
0
votes
0answers
439 views

Create Temporary Index for bulk operation and Delete after its completed

I am moving bulk data to archive tables. Some times query returns up to 4.5million records, which takes about 2:15 minutes for a table of 40 columns. I want too increase the performance for the ...
2
votes
1answer
6k views

How to get rid of clustered index scan?

I have a query that was generated by C#/Linq-To-Entities code. As with this kind of query, it looks seriously ugly and I doubt it would help to include it here. But I have run it through a query ...
1
vote
2answers
56 views

Should I include as much information as possbile to help the optimiser?

Reading the article "Dynamic Search Conditions in T‑SQL" I saw the following statement in the "Using sp_executesql" section. Double Feature You may wonder why there are two conditions on the ...
1
vote
2answers
161 views

What does enabling containment on server incur?

Having read in a book of Patrick LeBlank "Microsoft® SQL Server® 2012 Step by Step": "Benefits and limitations of contained databases As with most features, there are benefits and limitations ...