Questions tagged [performance-tuning]
Improving the performance characteristics of a database application or system.
540
questions
0
votes
0
answers
23
views
Two views yielding the same results switch places performance-wise when called remotly
An SQL Server 2000 has a view "A" that produces around 700 rows of data in about 30 seconds. I was tasked with optimizing "A" and created a view "B" that produces the ...
2
votes
2
answers
31
views
Find Which Queries will be affected by Proposed Change to CTFP
The Cost Threshold for Parallelism Setting on one of our servers is set at what is generally considered too low (15) and we are considering increasing to 50 in hope to reduce CPU as it is becoming ...
- 1,450
0
votes
1
answer
54
views
SQL Query, how to know if is resource intensive? I/O
I am not a SQL guru, but I am troubleshooting an issue with our marketing platform as its become slugish and slow, I've requested the top 50 heavy/resource intensive queries executed on our sql server ...
- 357
-1
votes
0
answers
39
views
Table Statistics are out of date quickly
We have a table in our dev environment that has 2 million rows. Recently we dropped a few unused indexes from this table. A week after that, we noticed that the stored procedure that uses this table ...
0
votes
0
answers
20
views
Database design for fixed records
Potentially trivial database design which I'm trying to overthink.
Let's say that I have:
A fixed number of records (on the order of the 1000s, you can think about a static product catalog).
The ...
- 1
2
votes
2
answers
312
views
Need help tuning a complicated query
I've inherited a rather complex stored procedure that is causing timeouts in our Production environment. I've used SQL Sentry Plan Explorer to help me look at some of the issues. I've identified a ...
- 121
0
votes
2
answers
36
views
mysql cpanel database using too much memory!
I've been trying to figure out why mysqltuner is reporting such dangerous amounts of maximum possible memory usage 63.2G (404.10% of installed RAM).
I'm an amature at this and could do with some help ...
1
vote
1
answer
43
views
More columns vs. more rows to optimize storage and performance?
I have a table with this structure:
ticker VARCHAR NOT NULL,
interval VARCHAR NOT NULL,
ts TIMESTAMP WITHOUT TIME ZONE NOT NULL,
price ...
- 153
0
votes
5
answers
46
views
MySQL's maximum memory usage is dangerously high
I'm trying to understand what is not working in my configuration.
When I run the
[root@server ~]# ./mysqltuner.pl
>> MySQLTuner 1.9.8
* Jean-Marie Renouard <jmrenouard@gmail....
2
votes
1
answer
59
views
Avoiding Hash match to improve CPU usage for a specific query
We are trying to resolve or figure out change in plan for one of the top sql queries by CPU.
This query we have found (it's a vendor-based product) which unfortunately I do not have access to extract ...
- 703
0
votes
0
answers
18
views
When is the mysql performance schema event "wait/synch/cond/sql/MYSQL_BIN_LOG::COND_done" fired?
I am using Aurora Mysql 5.7.mysql_aurora.2.07.2 and am facing bottlenecks on load testing with a write-heavy workload. On enabling performance insights I noticed that a large number of sessions were ...
- 101
0
votes
0
answers
46
views
PostgreSQL Database Performance issue
I have performance issue with PostgreSQL Database. These are excerpted related tables:
CREATE TABLE public.patient (
patient_id serial4 NOT NULL,
user_id int4 NULL,
cid varchar(13) NULL,
...
1
vote
0
answers
48
views
Potential causes for unusually long queries (postgres)
Recently we've noticed some SELECT queries taking an unusually long time to complete and are a bit stumped on what the reason could be.
Looking at DB (and server) CPU and IO during the time the ...
- 11
0
votes
1
answer
59
views
How do I move PostgreSQL temp_files to a different tablespace/disk for CURSORs
While working on performance improvements on our PostgreSQL database (Ubuntu Focal, PostgreSQL 13.3), I created a "temporary" tablespace on a fast local NVME drive. This works well and both ...
- 103
0
votes
0
answers
40
views
What could cause "spikes" of slow queries in MongoDB?
I operate a fairly busy HTTP backend over a MongoDB cluster consisting of a primary instance and two secondary nodes. Most commands and queries are served by the primary DB node, and on the surface, ...
- 101
0
votes
0
answers
59
views
Left join performing very badly
Consider the following situation, where the two tables below need to be joined. The query is currently performing very badly. There are no indices defined nor are the ids marked as PK.
I was thinking ...
- 153
0
votes
0
answers
34
views
SQL Server 2014 consuming more memory than max server memory setting
For the past month, I have noticed our SQL Production server has been consuming more memory than we allocated for it. This is the current structure:
Total OS Ram: 31Gb
SQL Server currently using ...
- 63
1
vote
2
answers
110
views
Memory utilization by SQL Server 2016 [duplicate]
I am running a SQL Server 2016 Enterprise Edition on Windows Server, with 128 GB of memory. During installation, I assigned 124 GB to the SQL server, since the server is dedicated to SQL only, leaving ...
- 11
-2
votes
1
answer
62
views
LEFT JOIN Performance Issue
I have a view, which has a column that comes from query
Without this query my count(*) against that view executes in less than 2 seconds, whereas it takes 2 days for the view with above extra column ...
- 1
6
votes
1
answer
568
views
SQL Server table query with pagination performance tuning, understand the current solution
as stated in the title i start a performance tuning of a table query with pagination generated by a legacy program that use Linq To SQL as ORM.
I have found this resource in which is highly ...
- 287
0
votes
0
answers
23
views
Proceeds JOIN only if condition met on the Owner table
I have many hasOne relationships like this:
components
component_headings
component_images
Here one Component can only have one hasOne to any of [headings, images].
Logically when a Component.type =...
- 101
0
votes
1
answer
86
views
Getting warnings while generating query Execution plan in Oracle , PLSQL developer
These are the steps I follow to generate a real Execution Plan in PLSQL developer :
1)Using this hint in my main query /+gather_plan_statistics/
select /*+gather_plan_statistics*/ from mi_factcustomer
...
- 1,294
0
votes
0
answers
20
views
Oracle Index Clustering Factor
My question is about Clustering Factor (CF) of Indexes in oracle
I have created a table as follows :
Create table T2 as select c1,c2,c3,c4,c5,c6 from T1 order by c1,c2,c3,c4,c5,c6;
Create Index IDX ...
- 141
1
vote
2
answers
349
views
SQL Server how to upload big json into column, performance issue
I am experiencing poor performance on a single row insert/update statement over a table when a nvarchar(max) column has few MB of data.
Here my table structure :
CREATE TABLE [dbo].[tbl_set_Cart](
...
- 287
-1
votes
1
answer
50
views
Database Tuning for 3rd Party Software Database [closed]
I'm starting out as a SQL Server consultant trying to focus on maintenance and performance improvements. I'm using Brent Ozar's first responder's kit and tools. I've had good success with one client ...
- 101
1
vote
2
answers
188
views
Query Performance improvement in Cassandra
I have a table in my Cassandra db.
CREATE TABLE table (
pk uuid,
status int,
location text,
type text,
id text,
updatedtimestamp timestamp,
PRIMARY KEY (pk)
);
CREATE ...
- 13
0
votes
2
answers
63
views
Sql Server Disk I/O Troughput In Performance monitor
i use sql server 2019 and trace flag 1117(Grow All Files in a FileGroup Equally) is on in my DataBase.
I need to consider the correct number of data files for primary file group according to my system ...
- 11
-1
votes
1
answer
96
views
Postgres IO bottleneck [closed]
I'm getting consistent 100% IO utilisation with quite low overall throughput, which is causing system slow down, I'm unsure whether this is a 'real' IO limit or the result of configuration and ...
- 11
1
vote
2
answers
51
views
DBCC shrink file history
I ran shrink file(.mdf) through GUI by selecting Task -> Shrink -> Files and selected second option i.e Reorganize pages before releasing unused space. Now, I would like to know the history of ...
- 57
2
votes
0
answers
142
views
How to determine the Tipping Point of which Physical Join Operator gets used to process a query?
I've been noticing some strange occurrences lately on my development SQL Server instance where the Nested Loops Physical Join Operator is used when a Hash Join would be much more efficient, within a ...
- 24.1k
3
votes
1
answer
116
views
sp_HumanEvents @event_type = N'blocking' and logging job help needed
Those who use excellent sp_HumanEvents, maybe the author himself, please help me understand something I am missing (cause of my stupidity).
#1
When monitoring blocking, blocked process threshold must ...
- 935
0
votes
1
answer
47
views
Could hardware RAM disks (with battery) have any benefit in database servers?
For database servers with many writes, I am wondering why can't these ramdisk/ramdrive be a solution to avoid wearing out SSDs and to avoid losing data if too much is kept in normal ram.
Can these ...
- 131
0
votes
0
answers
29
views
Sql id and plan creation pattern
Oracle database.
I've got this customer who is complaining about some ODI application taking longer than before (meh!).
Currently, it is not clear what the app does (I'm asking..) but he told me that ...
- 19
1
vote
2
answers
280
views
I am burning trough NVME SSDs, 3x 60GB MySQL databases (web crawler)
I made a web crawler that is split on 3 computers, each of them makes many db queries, I think around 200 3000-4000 queries per second (each server) non-stop, with short spikes to 12000-14000.
I am ...
- 131
1
vote
1
answer
45
views
Identify the line of a procedure of a spid/session
In SQL Server 2017, how can I identify or pinpoint at which line of a stored procedure a spid (session) is currently at and what the execution plan of the current statement looks like?
- 11
0
votes
1
answer
32
views
What dataset(s) is Marcus Winand using in his book "SQL Performance Explained"?
I am currently reading SQL Perfrmance Explained by Marcus Winand. I would like to follow along with the queries and examples he used to explain database tuning. It would greatly allow me to understand ...
- 103
0
votes
2
answers
258
views
MySQL 8.0 Hanging and Disconnecting At Random Times - Slow Downs - Need Help
Yesterday, I noticed in my web logs that most traffic disappeared for 2 hours. I decided to do a routine Ubuntu 20.04 upgrade yesterday that I haven't done in 1-2 months, and it seems like a lot of ...
- 33
0
votes
1
answer
65
views
Oracle. Old Partitions having Stale Statistics
Our production database tables are range partitioned based on date.
Some of the tables and indexes have hundreds of partitions. And some of partitions which are old ( based upon partition key ) have ...
- 141
0
votes
2
answers
613
views
An error occurred while updating the entries. See the inner exception for details.; [INNER] Execution Timeout Expired
One of our production SQL Servers the database is performing poorly. On the application side, they are experiencing this error:
An error occurred while updating the entries. See the inner exception ...
- 63
0
votes
2
answers
34
views
How to check the source of analysis task in Oracle Performance Analyzer?
We have provided sql tuning set as input to the analysis task of performance analyzer
variable l_task_id VARCHAR2(64);
exec :l_task_id:=dbms_sqlpa.create_analysis_task(
sqlset_name => '<...
-1
votes
1
answer
50
views
Oracle Execution Plan - Outline Question
I'm spending the last few days trying to learn some tips/tricks on tuning an Oracle query being run in Crystal Report. Obviously it has some performance issues. I suspect there are some deficiencies ...
- 147
0
votes
2
answers
282
views
In PostgreSQL, how can I make Bitmap Index Scan parallelized?
I want to improve the performance of an SQL statement.
I am on version 13.
Here are the sample codes and the query I am interested in.
drop table ords;
CREATE TABLE ords (
ORD_ID INT NOT NULL,
CUST_ID ...
- 41
1
vote
0
answers
53
views
Query performance in describing a race's relative finishing positions
Is there a faster, and more parsimonious way to do this?
For each race (i.e. 1, 2, and 3), there are many race entries X (horses), some details including it's finishing position, and in the same row ...
- 11
0
votes
1
answer
37
views
Sql Server consuming lot of memory Creating Bottleneck for my web application Hosted in the same server
HI I have a server with 64 GB Ram and I have assigned around 52GB to the SQl Server itself. However Even this has effected the performance of the server and the web application hosted in the same ...
1
vote
3
answers
152
views
Should the T and L phase of a well tuned ETL report process take the same amount of time regardless of the time bucket width and data size?
For an ETL reporting system, is it normal for the total execution time for a pull of 15 minutes with no data to be similar to a 24 hour pull with data?
I had expected that the total time for ETL when ...
- 443
0
votes
0
answers
14
views
SSRS High IOPS and Slowdowns
Please let me preface by saying that I understand poorly formed reports can cause poor SSRS performance and I have people looking at that issue separately.
I have an SSRS server that is seeing ...
- 1
3
votes
2
answers
1k
views
MySQL 8.0 Tuning Advice Needed - Slow Locking & Various Issues
I recently upgraded my database to MySQL 8.0 and I'm using phpmyadmin to get status information. I also upgraded my virtual server to 4GB RAM and 2 vCPUs, intended to be a MySQL-only server for my ...
- 33
-2
votes
1
answer
44
views
Database scoped configuration applicable to Secondary
I saw some new database scoped configurations like MAXDOP and legacy cardinality for secondary options and have few questions or doubts i am not able to understand
MAXDOP-
How do i setup MAXDOP, say ...
- 703
1
vote
0
answers
46
views
Why performance of this query deteriorates over time?
I have a 1 million row table in oracle 19c. I am running this query. Table has an index on ID column, stats are up to date.
select <columns>
from (select <columns> from <tableA> ...
- 21
6
votes
5
answers
3k
views
Is OPTION (RECOMPILE) used in production environments?
Is OPTION (RECOMPILE) used in production?
This option seems to get a lot of bad press. Is it deserved?
I have a DBA who, so far, is not a fan of the idea of OPTION (RECOMPILE) within the meat of ...
- 443