Questions tagged [performance-tuning]
Improving the performance characteristics of a database application or system.
0
votes
0answers
11 views
what is the overhead of Mysql database users
i have one application where we wish to create new connection for all users so everything is new for all means , new database, new mysql users everything new but is it a good practice.
we are doing ...
0
votes
0answers
24 views
RAC DB Slowness - AWR Report
While going through the AWR report found that the row lock waits on indexes and buffer busy waits are more (rest of the stats look normal).
Could someone tell me if we can interpret by the below if ...
0
votes
1answer
64 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 ...
0
votes
1answer
83 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 ...
3
votes
1answer
55 views
Identify Query Waiting on resources
I have some query with CXCONSUMER wait time when i ran sp_whoisactive as shown in the screenshot below.
When i checked the status it is suspended.That means it is waiting for some process to release ...
2
votes
1answer
112 views
Microsoft Optimal method for columns into 1 word: XML Raw or Concat
What is fastest, performance method to make columns into one word? Does Microsoft have recommendation, or is there performance Big-O studies on FOR XML RAW vs Concat?
Company has multiple tables with ...
1
vote
2answers
93 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 ...
-1
votes
0answers
10 views
Innodb_buffer_pool_instances in mysql 5.5.49 is working?
I defined my.cnf parameters as follows:
[mysqld-5.5]
thread_stack = 524288
table_open_cache = 1000
key_buffer_size = 512MB
innodb_buffer_pool_size = 5GB
innodb_buffer_pool_instances = 5
...
0
votes
1answer
52 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 ...
0
votes
1answer
38 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 ...
0
votes
1answer
29 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 ...
0
votes
3answers
182 views
Create Slowly Changing Dimension table from Repeated Data
I am receiving transaction snapshot data from a file, it contains a history of repeated data. Currently trying to find Slowly Changing Dimensions on table with [ProductId] Business Key. Many ...
0
votes
1answer
48 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. ...
0
votes
2answers
20 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 ...
7
votes
2answers
163 views
SQL Staging Tables: Primary Key Clustered or Heap
We are taking legacy flat txt files and inserting them into stage tables with SSIS.The question arose whether table should have primary clustered key index or not. This is direct flat file import with ...
1
vote
1answer
61 views
SSIS Data Source: Embedded Queries, Stored Procedures, or Functions
We are moving data from OLTP server into Data Warehouse server, utilizing SSIS to conduct this.
When writing queries from the OLTP Data Source, it it better to utilize?
1) Inline Embedded queries
2)...
0
votes
2answers
33 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 ...
0
votes
1answer
35 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.
...
1
vote
1answer
37 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 ...
2
votes
1answer
86 views
Huge Memory Grant resulting in RESOURCE SEMAPHORE waits for other queries
Issue:
I have few queries which is requesting huge memory grants(~7GB).These queries are run often and this is causing other queries to wait for memory.So i am seeing RESOURCE_SEMAPHORE wait type.
...
3
votes
1answer
124 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
...
0
votes
1answer
45 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 ...
8
votes
1answer
166 views
What is the algorithm behind the EXCEPT operator?
What is the internal algorithm of how the Except operator works under the covers in SQL Server? Does it internally take a hash of each row and compare?
David Lozinksi ran a study, SQL: Fastest way to ...
0
votes
1answer
22 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.
...
0
votes
2answers
1k 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 ...
2
votes
1answer
161 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 ...
1
vote
1answer
80 views
How Large Table before Using Clustered Columnstore Index
At what size should we consider applying Columnstore Index on a table? Is there a SQL Server storage or design guidance?
Our Data Warehouse is 5TB total. Additionally, we have tables which are 100GB ...
1
vote
2answers
138 views
Can a table have Both a Clustered Columnstore Index and Nonclustered Columnstore Index?
Can I a table have both Clustered Columnstore Index and Nonclustered Columnstore Index?
I am seeing example strategies to use Clustered Columnstore Indexes with only Nonclustered Binary row indexes.
...
3
votes
1answer
80 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), ...
1
vote
1answer
79 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],
...
2
votes
2answers
295 views
Performance differences between RID Lookup vs Key Lookup?
Are there any performance differences between when a non-clustered index uses the clustered index's key to locate the row vs when that table doesn't have a clustered index and the non-clustered index ...
-2
votes
1answer
69 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 ...
3
votes
1answer
817 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 ...
1
vote
1answer
52 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 ...
0
votes
1answer
57 views
Data modeling with the goal to get the best performance in Oracle and SQL Server [closed]
I have a question about how I can model my stock database in order to get the best performance possible.
In SQL Server or in the Oracle, each update executed generates a little lock.
I'd like to ...
6
votes
4answers
487 views
How to most efficiently find out if a record has child records?
I am writing a query that returns a single record from the parent table. I would like to also return in this query if it has any children. This is a one to many relationship.
parent:
-parent_id
-...
1
vote
1answer
73 views
Does logical reads in RAM appears in wait stats or where?
Concerning execution model of SQLOS (RUNNING state, RUNNABLE queue, WAITER list), what will be the state of a task when there is a logical read of pages in RAM currently going on?
If it's a WAITER ...
0
votes
0answers
30 views
Select Candidate Tables for File Restructuring / Migration
I have a VLDB with a default setup: a single MDF file in the PRIMARY file group. I want to create a new file group which will contain a few of the most high-capacity or highly trafficked tables found ...
3
votes
2answers
102 views
Performance considerations regarding Date Vs. Char/Number/Varchar2 in Oracle
I've been working with Oracle 11g for about 7 years and I was asked for a consulting job on a small project.
The problem they had was high CPU usage (more than 90%) on a 11gR2 with Windows2008 as ...
1
vote
1answer
392 views
Why is MySQL slow on the Ubuntu dedicated machine while fast on the Ubuntu VM?
I have a dedicated machine with intel i5, 8GB RAM, 7200RPM HDD with Ubuntu 18.04 OS. I have installed only MySQL-5.7.22 in it, see the configuration file's content at the end of this question. I ...
2
votes
2answers
34 views
Performance Metrics be on Own Server or Own Server Instance
We would like to collect SQL Server performance metrics (memory, Cpu, page writes/sec, batches/sec,active sessions), etc. I heard its a rule not to collect Sql server metrics on the live Financial ...
3
votes
1answer
513 views
What performance advantages does append-only postgres allow?
Suppose we have a Postgres Database with only a single table. It is known that no UPDATE statements will ever be run on it, nor will any DELETEs. It is 100% append-only.
Some databases exist that ...
0
votes
0answers
99 views
Is there any performance benefit of using ENUM instead of TinyInt in big data for MySQL?
I read a lot of articles about ENUM. but most of them are comparing ENUM to joins not tinyint.
I need fields with 2-5 different statuses.
In this situation is there any performance or storage benefit ...
4
votes
1answer
129 views
How to Find Pages/Sec from sys.dm_os_performance_counters
How do we find pages/sec from dm_os_performance_counters DMV, I do not see it here:
SELECT * FROM sys.dm_os_performance_counters
where counter_name like '%page%'
Monitor Memory Usage
Pages/sec ...
2
votes
3answers
110 views
How MySQL use the InnoDB Buffer?
For example, when using mysqltuner.pl, it told me to increase the InnoDB Buffer Size to > 5GB as my data is 5.6GB
Does it mean: if I allocate enough for the InnoDB Buffer, data will be loaded from ...
1
vote
2answers
38 views
Can indexes really decrease performances in a low volume writes DB? [closed]
I'm trying to do a bit of a clean up/review of the database I'm currently working on (which I didn't design, by the way). The DB is 'big' in term of objects (hundreds of tables, hundreds of SP, ...
3
votes
2answers
140 views
Should Archive Tables have their own Surrogate Identity Key
Should archive tables have their own surrogate identity id/key? For a customer sales table example:
We have original table, and then an additional archive table with surrogate archive id.
Rules ...
1
vote
1answer
59 views
Defragmenting indexes SQL Server bases on avg_page_space_used_in_percent
I am wondering if I should take into account information from
sys.dm_db_index_physical_stats
column
avg_page_space_used_in_percent
when deciding on defragmenting/not defragmenting an index.
How ...
0
votes
0answers
28 views
What's the meaning of trx0sys.c in show engine innodb status?
Hi I have gone through https://www.percona.com/blog/2006/07/17/show-innodb-status-walk-through/ howto read/interpret "show engine innodb mutex" output? https://www.percona.com/blog/2011/09/...
0
votes
0answers
44 views
Executing IF Exists condition
I have a sproc that scripts out permissions and in the process, I add a check to see if the object exists before dumping the results into a table. Then, I have another re-assign permissions sproc that ...
