Questions tagged [performance-tuning]

Improving the performance characteristics of a database application or system.

Filter by
Sorted by
Tagged with
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
-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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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....
user avatar
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 ...
user avatar
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 ...
user avatar
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, ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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, ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 =...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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]( ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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?
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 => '<...
user avatar
-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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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> ...
user avatar
  • 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 ...
user avatar
  • 443

1
2 3 4 5
11