0
votes
0answers
52 views

SQL-Server 2012: Same query takes different Query Plans - but why?

Recently we had an issue with a query, which reached a timeout when executed the call through our web application. Using a monitoring tool I found the "bad query" and took it. I run it directly on the ...
5
votes
2answers
245 views

Terrible Bad Query Plan

I have a weird query plan problem. I have two databases (Let's call them DB1 and DB2) where both are sitting in the same SQL-Server instance and have identical schema. In there, we have a couple ...
0
votes
2answers
51 views

Identical Servers but different query execution plans [duplicate]

We have two server with identical hardware configuration and SQL server configuration. The only difference in the server's being: Server SQL1 - 256 GB RAM Server SQL2 - 128 GB RAM The same database ...
0
votes
1answer
70 views

Slow running aggregate of aggregate query

I am looking for the total balance for all accounts that had a negative balance as of a specific point in time. I've already got the code below, and it works, but works horribly. The transactions ...
1
vote
0answers
60 views

How to use join with this query? [closed]

This query is working fine, and giving the right result. In this query all columns are in one table, but now I have made some changes. Now columns named Semester and Major are in different Table ...
0
votes
1answer
69 views

SQL Server sometimes uses a lot more cpu for query

I have a stored procedure with a quite heavy SELECT statement that includes five joins and uses FieldName LIKE '%searchterm%' on multiple columns. This query usually finishes in about 2-5 seconds. But ...
2
votes
0answers
78 views

SQL Server - Logical Reads lowered, Execution time remained the same

A view executed Select columns from view where col= "date range" (view contains calculation in it Col1+Col2*col3 AS Col4). It took 7 seconds for 500K rows to return, logical reads some 144k. Added ...
1
vote
2answers
200 views

Why can't we access SQL Server when a long query is running in Management Studio?

We have a SQL Server Database (2012 Standard edition). The database is accessed through an ASP.Net web page which displays the count of databases in different tables on page load. This work fine ...
0
votes
2answers
82 views

Database SQL Script very, very slow (string manipulation over 700k rows)

I have table containing 700,000 rows containing a string id field with values such as rec-232276-dup-0 and rec-354240-org. The rec- is constant but the other parts of the id can change. I want to ...
0
votes
1answer
82 views

How to increase sql server performance? denormalization (making cascade delete off) vs making duplicate data

I have a database with following related table ( sql server enterptise edtion 2014) each state have many citites which each city has may municpalArea and so on State => Cities = > ...
0
votes
0answers
86 views

Memory Issue - SQL Server 2008r2

I am trying to troubleshoot a memory issue on a 2008r2 instance. Very high resource semaphore waits are present and a general slowdown of queries,backups,dbcc checks. Only one instance of SQL Server ...
1
vote
2answers
96 views

Is there a more efficient way to run this query for first/max transaction?

For each account, I need the first transaction date, the largest transaction amount, and the time taken to get from first to largest. Data is truncated and reloaded frequently, and I have read-only ...
0
votes
1answer
48 views

If a query contains two identical subqueries, will they both run?

Say I have a SELECT query that contains two identical subqueries, like the below: SELECT (SELECT SUM(Price) FROM Product p WHERE p.GroupID = g.GroupID) AS 'TotalCost', (SELECT SUM(Price) ...
1
vote
0answers
49 views

Slow compilation of a SQL statement

I have SQL Server 2014 and a large table (more than 200Gb) with 100M rows and more than 300 columns. For one "unpivot" query I wanted to see its estimated execution plan and it took more than 90 ...
0
votes
1answer
47 views

Does it make sense to order condition in where query?

We have a clustered index in table Tbl with order of A,B,C. Does it make senses to write a query to have WHERE A = @a, B = @b, C = @c or it'll be as same as WHERE C = @c, B = @b, A = @a Does the ...
0
votes
0answers
55 views

Last few rows from a non-indexed table in Oracle

I'm trying to run a query on an Oracle DB through SQL Server [dont' ask, it's a requirement] The Oracle table in question [lets call it hugeTable] has over 200 million rows and no index. I'm not ...
0
votes
0answers
56 views

Index Tuning Question

I have the following query: SELECT T1.ITEMID, T1.ITEMTYPE, T1.PURCHMODEL, T1.HEIGHT, T1.WIDTH, T1.SALESMODEL, T1.COSTGROUPID, T1.REQGROUPID, T1.EPCMANAGER, ...
5
votes
2answers
358 views

Index seek much slower with OR condition compared with separate SELECTs

Based on these questions and the answers given: SQL 2008 Server - performance loss possibly connected with a very large table Large table with historical data allocates too much of SQL Server 2008 ...
0
votes
2answers
104 views

View with left joins are very slow

I have a view based on join of 3 tables and and query on view is taking 10 mins to fetch the results. Can anybody help me in reducing the time taken to fetch results? VIEW CREATE view ...
3
votes
4answers
171 views

Are we a good fit for SQL Server Enterprise and table partitioning?

We currently have a production database that contains millions of records (14 million in the largest table). One function of the database is to provide time sheet information so a single table could ...
0
votes
3answers
116 views

Why is my select statement so slow?

SELECT name FROM sys.databases WHERE name not in ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB','1303VISBOC') AND CASE WHEN state_desc = 'ONLINE' THEN ...
1
vote
1answer
65 views

Performance of checking for data in related tables

NB: this question is purely academic / to help improve my understanding of SQL Server performance. Given a master table which relates to one or more other tables, how would you determine the best ...
0
votes
2answers
137 views

Query Optimization required - Subquery using Inner Join

Could anybody advise how can I optimize this query below please? SELECT IT.[iInternalTransactionID] , IT.[iInternalAccountID] , C.[iContractID] , IT.[dtTransactionDate] AS TransactionDate , ...
0
votes
0answers
60 views

A working view now starts timing out

I have 2 identical databases (one larger, one smaller) with identical views. Recently a view on the smaller DB started timing out. Google searches seems to point to deadlocking of some sorts. If I ...
4
votes
2answers
120 views

Clustered Table Scan Because of “SELECT *”

I have a Records table with over 100 columns and very many rows, and a nonclustered index on 5 fields based on my access paths: CREATE NONCLUSTERED INDEX [IX_Records_CustomerID] ON [dbo].[Records] ( ...
6
votes
1answer
151 views

Sargable Queries

Very simple question I hope, I'm currently having some scripts I inherited into my project reviewed by the database admins. This has never been done before apparently and lots of issues are being ...
1
vote
2answers
1k views

Improve query performance when selecting almost all rows with many “group by” columns

I have a table with 20 columns and about 600,000 records. The maximum row size is only around 100 bytes. The table is repopulated every few days, but the number of records remain about the same. For ...
-3
votes
2answers
163 views

how to solve slow running sub query TSQL

I have a problem with a slow running query. I have analysed the query with the sql server profiler but I can't seem to find a good solution. I did find why it is running slow. If ask the full ...
5
votes
2answers
457 views

An efficient way to compare two large data sets in SQL

Currently, I'm comparing two data sets, that contain unique StoreKey/ProductKey combinations. The 1st data set has the unique StoreKey/ProductKey combinations for sales between begin January 2012 and ...
1
vote
1answer
156 views

Understanding execution plans using TVPs

I have some slow running queries (8 seconds across 1M records grouped by months) and have been trying to make sense of the execution plans. We use around 10 TVPs to send in a set of filters that the ...
5
votes
2answers
256 views

Cannot tune database any further; what next?

We have a vendor-supplied application. It is under support and we are talking to their developers (we have their largest database size by an order of magnitude), but in the meantime, we have the ...
4
votes
1answer
147 views

Search, filter and group across all columns in a tall and wide dataset quickly [closed]

We have certain datasets which may span 80 to 300 fields, across 300,000 to 35 million rows. For each dataset, we want to produce a web based analysis tool (a web grid) which would allow our users ...
2
votes
1answer
174 views

Does SQL check each And condition if first condition fails? [duplicate]

For a query of below kind does sql process data for each And condition if first And condition fails? If Emp_ID is not 12 for a row then does it check condition of Emp_Name? So how SQL process this ...
0
votes
1answer
59 views

NTEXT,Indexing, and lob logical reads

We have an query that would benefit from a covering index but one of the columns that would be returned is a NTEXT data type. I know this can't be added to the index and I also know it would be good ...
4
votes
2answers
543 views

Why is my plan guide not being used?

We recently ran into the tipping point issue and some of our report queries that used to complete execution within a couple of seconds are now taking more than 2 min because the query optimizer simply ...
1
vote
1answer
191 views

Retrieve next queue item

I have a simple table in SQL Server 2012 which implements a processing queue. As data has been inserted the query to retrieve the next item has gone from <100ms to a constant 5-6secs. I would be ...
3
votes
2answers
162 views

How can I eliminate this costly index seek operation from my query?

I have two tables in Sql Server 2008 that look something like this: Transaction_Details: Dataset, Department, Charge_ID, Transaction_Type, dos_month, post_month, amount ...
2
votes
2answers
300 views

SQL server query is slow *without* including execution plan

I have just set up fresh installation of SQL Server 2012 on a new server and I'm experiencing very strange performance problems. Long story short, for some reason not including execution plan makes my ...
3
votes
1answer
93 views

How do I deal with query getting slower when a select parameter is not specified inline?

I faced this problem twice with SQL Azure and two different queries so far. Something like this: select top(100) * from SomeView WHERE AccountId='actual-guid-typed-in-here' runs super fast but ...
0
votes
3answers
270 views

Is table partitioning improving performance? Is it worth it?

I have just been involved in a project on which I will have to develop a data migration process and a web interface that uses an already existing SQL Server DB. This DB was developed by another person ...
3
votes
1answer
79 views

Every query plan statistic says my query should be faster, but it is not

I have a bit of a odd situation. I have two queries that produce the same result, according to SSMS and SQL Sentry Plan Explorer the first query should cost 95% and the 2nd query should cost 5%. ...
0
votes
1answer
124 views

Take query results and insert into new table [closed]

So I have the following query: DECLARE @tbl TABLE(value int) *--The same function but with the number of the random numbers ;WITH Nbrs (n) AS ( SELECT 1 UNION ALL SELECT 1 + n FROM ...
4
votes
2answers
206 views

Query tuning to meet timeout settings [closed]

We have an web app with a 30 second time out on search screens. I've analyzed the original query and added a missing index for one of the query's 9 joins. Other things like expected and actual ...
3
votes
2answers
3k views

High CXPACKET and LATCH_EX waits

I am having some performance issues with a data processing system which I am working on. I have gathered wait stats from a one hour peroid which show a large amount of CXPACKET and LATCH_EX wait ...
0
votes
2answers
517 views

How can I speed up this query on table with 65 million records

select hash_id, hash_value, substring(T.cri, 1, 7), substring(T.cri, 9, D2.Pos-9), substring(T.cri, D2.Pos + 1, D3.Pos - D2.Pos - 1), ...
0
votes
2answers
56 views

Which one is better way to insert data? [closed]

I have two cases case create proc createEmployee @firstName varchar(255), @lastName varchar(255) = null as insert into Employee_Tbl values (@firstName, @lastName) case create proc ...
4
votes
2answers
81 views

Spatial index not used

I have a table with a geometry column. For one record, there is only one Point stored. A spatial index has been created but queries searching for the nearest location do not use this index resulting ...
-4
votes
2answers
82 views

Performance with IN clause

Does indexed data affect query performance? means If I passed value as 1,2,3,4... in ordered form in any IN clause, can it improve query performance? and what happen in case I pass value randomly ...
4
votes
1answer
112 views

Drastic performance difference in View Dependencies between SQL Server 2005 and 2008 R2

Background I am a fledgling DBA who was hired in the middle of a migration from SQL Server 2005 x64 to 2008 R2 x64. We also migrated from a local server to a colo virtual server, and moved some of ...
1
vote
1answer
187 views

Performance of dynamic SQL Server reporting queries?

I have to build an application that allows the end user to select what columns they want in their report. For this I build a dynamic T-SQL stored procedure. However, there are up to 60 columns ...