Questions tagged [performance-tuning]
Improving the performance characteristics of a database application or system.
592
questions
22
votes
4
answers
31k
views
SQL Server has encountered occurences of I/O requests taking longer than 15 seconds
On Production SQL Server, we have following config:
3 Dell PowerEdge R630 servers, combined into Availability Group
All 3 are connected to a single Dell SAN storage unit which is a RAID array
From ...
- 5,589
0
votes
2
answers
254
views
same query gets slower after other queries get called (using a lot of prepared statements inside stored procedures)
I have a query that, when i run it first after i restart mysql, takes 2 seconds, but when I run a sequence of other queries before it (this query belongs to a procedure between other procedures) ...
- 63
0
votes
0
answers
144
views
SQL Server inconsistently reporting missing indexes
I have a stored procedure that is not performing well and I am trying to tune it. I am confused by what I see in SSMS and the Database Tuning Engine Advisor.
In SSMS I have 'Include Actual ...
- 101
2
votes
1
answer
372
views
What is Page Life Expectancy precisely?
What is the value returned by Page Life Expectancy, precisely?
The use of the word "will" in this documentation
Indicates the number of seconds a page will stay in the buffer pool without ...
- 545
2
votes
1
answer
718
views
Is it possible to move mongodb indexes to another file system?
Is it possible to separate MongoDB files location so the indexes are on another file system?
In the example below, I have database with 1.5TB of data + indexes of 330GB.
I have a very fast storage ...
- 33
2
votes
4
answers
11k
views
How to determine if it is necessary to increase the sga and the pga in Oracle database
How to determine if it is necessary to increase the sga and the pga in a database oracle 12c, is there any procedure, any specific view where can I be guided that indicates to me that it is necessary ...
- 149
1
vote
2
answers
110
views
Inline function Plan Properties in SQL Server 2008
Currently I use the below two queries to get some properties from the execution plan in stored procedures, the problem arises when I try to run it in a SQL Server 2008 version. Do you know if there is ...
user141153
1
vote
0
answers
836
views
Does keyset/cursor pagination scale query performance better than offset pagination?
I am seeing behavior where the query that powers my infinite scrolling list seems to be slowing down as the number of rows grow.
Naturally, I used offset pagination because the library I am using ...
- 225
10
votes
3
answers
1k
views
Converted a Scalar function to a TVF function for parallel execution-Still running in Serial mode
One of my query in was running in serial execution mode after a release and i noticed that two new functions were used in a view which is referenced in the LINQ to SQL Query generated from the ...
- 1,315
0
votes
1
answer
63
views
mysql query with 1 derived table and 1 inner join takes forever to load
I tried removing some chunks of code little by little and found out that the condition inside the inner join is the devil. Can someone enlighten me on how to achieve this when I have like 10,000+ rows ...
0
votes
3
answers
1k
views
Make SQL query "latency proof"
I have developed an application which connects to a database over a WAN.
As long as I'm using the application on my local network, everything works fine.
When I connect over the WAN, the database ...
- 1
0
votes
1
answer
50
views
Why these Two Queries performace so different?
This is Query 1:
SELECT
A.innings_no AS Inn, bowler, OT.Out_Name AS OutType, Runs
FROM
(SELECT
a.innings_no, a.striker, SUM(b.runs_scored) AS Runs
FROM
ball_by_ball a
...
- 101
1
vote
1
answer
176
views
Need Help in extended events SQL Server [duplicate]
I have created an extended event as below. I want to know how to query the historical data using T-SQL to see the issues at particular timestamps. The trace is running as of now
CREATE EVENT SESSION [...
0
votes
2
answers
83
views
does sequence of columns in where condition matters the sequence of composite key? in Oracle Database
here, In the code where exists clause contains all primary key columns in where, I want to know that the sequence of this columns in it, matters in query performance.
update temp_fd_dtl_svj a
set ...
0
votes
1
answer
70
views
Filegroup: [primary] dbcc shrinkfile() need balanced extent counts per file when done
I'm working with a ~1 terabyte db, server 40+cores, memory 500G+, db files on SAN (no control over lun segments), the bottleneck now is file contention. Currently running 2014 but by final "release" ...
- 140
-3
votes
1
answer
924
views
Partitioning and Performance of DB Table [closed]
After partitioning a table the performance of select queries returning large datasets degrades as compared to performance earlier. For small datasets there is no degradation. No change in index is ...
0
votes
2
answers
1k
views
What kind of SSD to speed up querying a lot of data?
For research purposes I am using a big MariaDB database (~500 GB) with 20 Tables (engine: myISAM) and around 9 Billion rows. Due to missing free storage on my Laptop (16 GB RAM), I put the data on an ...
- 115
3
votes
1
answer
2k
views
Empty Table: Insert into Table WITH (TABLOCK)
We have a clustered index table which is empty. Table has around eg: 20 columns, 50 million rows, around (40GB).
Is it best practice to apply Insert With (TABLOCK) for empty tables such as this? The ...
user172734
0
votes
1
answer
93
views
Are these stats reasonable? If not, where should I start?
The below is the output result of mysqladmin for our mysql server which has been having consistent problems for months since we switched from a dedicated server to the cloud. In the switch, we ...
- 3
2
votes
3
answers
23k
views
SQL Server: Performance Insert Into vs Select Into
I am hearing different things from colleagues/research. What are good guidelines in performance for, Select Into vs Insert into when creating a temp table? I know difference is minimal for small ...
user172193
1
vote
2
answers
818
views
oracle slow query with diferent user [closed]
I have a question with an oracle query that takes almost 20 minutes when it is executed from a schema and yet when it runs as sys users it runs in 1 second maximum ... how is this possible? ... I do ...
- 149
1
vote
1
answer
1k
views
Debug Connection Pool Error
I have been getting the below error.
System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.InvalidOperationException: Timeout expired. The timeout ...
- 1,315
6
votes
2
answers
15k
views
why is this left join faster than an inner join?
So I'm tuning this query, and I am pretty sure that in this instance, I can replace an inner join with a left join without affecting the data. However, I'm not entirely sure why this is faster. Here ...
- 415
2
votes
0
answers
64
views
Recommendations around managing large amounts of data that needs to be aggregated (SUM)
I am looking for alternatives to redesign a part of our system that needs to perform cumulative SUMs over large data sets of rows based on user and dates.
How the system works
We are running a web (...
0
votes
1
answer
2k
views
Index vs Statistics [duplicate]
In SQL Server, when to create statistics only on a table column instead of index and when create to index (which creates statistics as well automatically)? I mean both improve query performance then ...
2
votes
2
answers
1k
views
Slow query that includes a WHERE clause with SUBSTRING
I have the following query in SQL Server 2016, as there is no code for the "enter code here" column, I had to do a search with SUBSTRING. But as expected it is slow, the query ends in 00:03:37.
with ...
- 523
4
votes
2
answers
939
views
CXPACKET wait on SQL Server
Recently I am experiencing performance issue on a SQL Server database and I have two queries that show me the wait as shown below, one of the databases on this server is a member of Always On replica, ...
- 409
1
vote
1
answer
916
views
pgbench - 20-30% variation in benchmark results (non-repeatable benchmarks)
Problem
I'm trying to use pgbench to help me tune Postgres for my hardware, but I'm seeing a very strange behaviour. I cannot seem to get a stable number for TPS in consecutive runs of pgbench. Since ...
- 323
0
votes
1
answer
1k
views
sp_WhoIsActive waiting resource
I have a huge query in SQL Server 2016 – when I run the query for a subset of records, it completes in 15 minutes. When I extrapolate it to total number of records, it is supposed to be complete in 1 ...
- 901
0
votes
1
answer
2k
views
TSQL or SSIS for Direct Insert Speed Performance
I am moving data from Table A into Table B. Tables have the exact same DDL, table structure, and are straight inserts. We are Not transforming or modifying data.
If TSQL Server and SSIS have the ...
user162943
3
votes
2
answers
2k
views
Is NEWSEQUENTIALID unique within a database for Many tables?
Is NEWSEQUENTIALID unique within a database? We plan many tables in a database with NEWSEQUENTIALID, inserting simultaneously and want to ensure every guid will be unique, regardless if they are in ...
user162241
0
votes
1
answer
693
views
powerful VPS but slow MS SQL- why? [closed]
VPS (Contabo) : 10 cores (Xeon E5 2.2GHz), 60 GB RAM, SSD, MS SQL Web Edition (configured to use 10 CPUs), Windows Server 2016
When running SQL takes ~32GB of RAM,
When I execute SQL a stored ...
- 147
0
votes
1
answer
881
views
Update sys.job$ set this_date=:1 where job=:2 as one of the most time-consuming queries - oracle
I am currently helping some organization with their Oracle database performance. They are using Oracle 11gR2 Standard Edition in Windows 2008. As there are no enterprise performance packages ...
- 193
0
votes
1
answer
230
views
Oracle 9i historical long queries since instance startup
please I would like to know any query that shows the queries or the top 10 queries that take more time to finish since the beginning of the instance with user and the sql sentence, I have seen several ...
- 135
0
votes
1
answer
1k
views
Loading 400 Million Records into MySQL Hangs on "Sending Data"
I have a large dataset of approximately 420 million records and I was able to load them into a temporary table in a timely manner of about 15 minutes using a LOAD DATA INFILE statement. Super awesome. ...
- 151
1
vote
3
answers
482
views
MariaDB "EXECUTE AS" or User Impersonation
With MariaDB, is there a way to impersonate a user execution context when connected with a higher privileged account?
For instance, when connected with a "MasterUser" execute a SELECT using the more ...
- 13
0
votes
2
answers
56
views
Index columns on pre-production?
If I understand correctly, indexing a column will increase SELECT speed but decrease INSERT speed.
Say your app is new and you have identified columns which are good candidates for indexing (based on ...
- 169
0
votes
1
answer
249
views
Query Locks Table And Takes Long Time to Run
I have a query that can take a long time to run. I tried running the same query in a test server and it runs very quick.
However, in production it seems to hang and causes blocking of other queries.
...
- 533
1
vote
1
answer
197
views
SQL Except Statement and Add in One more column
How do I conduct a Except statement and Add in one more column?
Three tables with the exact same structure. (CustomerTransaction1,CustomerTransaction2, CustomerTransactionDiff)
I want the LoadDate ...
user162241
3
votes
1
answer
2k
views
Memory Pressure in SQL Server - Enable LPIM or not?
I have slow query execution in my SQL Server 2016.
Server configurations:
Total Physical Memory : 128 GB
SQL server Max Memory : 102 GB
Transaction replication is enabled.
Database Size:1.6 TB
...
- 1,315
1
vote
1
answer
966
views
SP_WhoIsActive - RESOURCE_SEMAPHORE_QUERY_COMPILE
Had an issue with one of our production servers earlier today. Many transactions were suspended with RESOURCE_SEMAPHORE_QUERY_COMPILE wait.
Have sp_whoisactive data logged to table every minute. Below ...
0
votes
1
answer
120
views
DB2 Workfile scan
We just migrate from LUW to zos and we're finding that in z/os the accesspath explains show new node named WFSCAN (Workfile scan) and this rises when there is a join, group by or any kind of sort.
...
- 101
1
vote
2
answers
4k
views
SSIS vs T-SQL Join Performance, Difference Databases on Same server
We are researching the performance difference between SSIS Lookup and T-SQL Join. We want to join two tables; tables are in the same SQL server instance, different databases.
I suspect for small ...
user162241
3
votes
1
answer
3k
views
Oracle 12c - Slow query to tables ALL_CONS_COLUMNS and ALL_CONSTRAINTS
We have successfully upgraded Oracle 11g to Oracle 12c. So far it's been going well. But We noticed one problem, queries made for Oracle's constraint tables (ALL_CONS_COLUMNS and ALL_CONSTRAINTS) are ...
- 61
3
votes
1
answer
1k
views
MySQL creates tables very slow
On my home machine running Ubuntu 18.04 and MySQL version 8.0.12 with better hardware, table creation, database creation and so on are very slow. On my other computer with older hardware (slower SSD), ...
- 31
5
votes
1
answer
709
views
what are memory fractions and how to get rid of them (or the sort operator) in the example below?
This execution plan has the following memory fractions:
Memory fraction input:1, Memory Fraction Output:1.
it is a very simple query:
SELECT [a].[activityId],
...
- 16.2k
3
votes
1
answer
122
views
I've got 60 million sales of 800 different liquid products whose price change daily
..and I'm having a bit of a battle getting to customer stats like who's bought the most, and when was their last purchase.
I've no problem writing the query, it's just really slow
Sales
CustID, ...
- 344
-2
votes
1
answer
2k
views
What is the Optimized Way to Dynamic SQL Select all Columns from Table?
I just generated this script.
It will select all columns from a table dynamically.
Is there an alternative or more clean, efficient way of conducting this?
Additionally, I want to place line breaks ...
user158823
33
votes
1
answer
42k
views
Warning about memory "Excessive Grant" in the query plan - how to find out what is causing it?
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 ...
- 16.2k
1
vote
1
answer
109
views
Stuck at optimizing "not in clause" for a medium-sized query across two tables
I have a system to teach English. For each word someone learns, I record:
LearnedWords
-- Id
-- UserId
-- WordId
And for each new text that I give to a learner, I want to indicate what ...
- 1,437