Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Improving the performance characteristics of a database application or system.

4
votes
2answers
66 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 ...
0
votes
1answer
40 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
30 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
33 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
36 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
56 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
107 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
37 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 ...
-1
votes
0answers
4k views

PostgreSQL vs MySQL Configuration file Comparison For Performance

How I can make my PostgreSQL 10 fast like my MySQL, My Hardware: Processor 12 Core RAM 42GB (20GB Ram Already used for MySQL in same machine) HDD 1TB Usage: SELECT-ONLY Searching - LIKE% Column &...
0
votes
0answers
47 views

Should Clustered Columnstore Index be the default standard now for Data warehouse?

Should Clustered Columnstore indexes be the default standard now for db partitions with 1 Million rows > ? We have a Kimball data warehouse, with Fact and Dim tables. Our Kimball data warehouse is ...
8
votes
1answer
150 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
19 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
429 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 ...
0
votes
0answers
59 views

Stored Procedure to emulate a workflow execution

I created a stored sequence procedure to execute a list of other stored procedures in order listed in a table and with an indicator to identify which is currently available. This is the table This ...
2
votes
1answer
71 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 ...
0
votes
1answer
57 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 ...
0
votes
2answers
47 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
54 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
52 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
159 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
48 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
399 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 ...
0
votes
1answer
51 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
434 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
71 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
28 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
91 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
193 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
329 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
61 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
84 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
84 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
37 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
122 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
51 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
26 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
42 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 ...
0
votes
0answers
17 views

Partially rotating MongoDB collection efficiency

I have a MongoDB collection with approximately 20M objects in it. Nightly, I am deleting (2 + incomplete portion) months of data and replacing them with an import from CSV that uses PyMongo 3.0....
3
votes
1answer
527 views

Tempdb Add files require Restart

Does adding Tempdb files require a restart? We would like to Add files on the same drive, Not Delete and Not modify. We tested in Dev and QA environment, restart was not required. However for ...
-1
votes
2answers
346 views

UPDATE trigger causes deadlock

I have a After Update trigger where trigger insert a value in same table to find a duration column.My goal is whenever end_time value is inserted then trigger will find duration in hh:mm:ss format and ...
1
vote
3answers
390 views

If Exists - Select Prevent Repeating Code

We receive an order list, and validate it starts with certain prefixes, example if OrderNumber does not start with ABC, than display order list to user as error. We use exists, to search for first ...
1
vote
3answers
214 views

SQL Server Upgrading Compatibility Level from 2005 to 2012

We have old database in SQLServer 2012 Enterprise in Compatibility mode: 90 - SQL Server 2005 . What are the consequence of changing database compatibility mode from 90 into 110-SQL Server 2012? Many ...
0
votes
2answers
111 views

Tempdb files Solid state on Same drive?

We currently have tempdb (8 files) on solid state drive. We know that primary mdf and transaction log ndf are recommended to be on different drives, to reduce contention. Should tempdb files be on ...
-1
votes
1answer
56 views

Transaction Log Backup during Index Rebuild and Integrity Maintenance Jobs

Brent Ozar mentioned there is nothing wrong about conducting transaction log backups during full backups. https://www.brentozar.com/archive/2015/12/what-happens-to-transaction-log-backups-during-full-...
-1
votes
2answers
55 views

Index Seeks cost of OR operator

I have a query which is slow and it takes 15 seconds to execute. The query fetches data from a view and in the where clause, I have few conditions which includes OR and AND. exec sp_executesql N'...
0
votes
0answers
61 views

Postgresql behaviour between diferent stages

I have three Postgresql instances (Postgresql clusters) in three diferent stages. The instances are the sames with the same datas and the same parameters. But i do not have the same behaviour between ...
2
votes
0answers
63 views

SORT(DISTINCT) :Is it because of Kitchen sink queries?

One of the SQL queries I am working on is slow in performance. I have been trying to fix it for the last two days, unsuccessfully. The query is generated from an application (Linq to sql -EFF), and ...
-3
votes
1answer
119 views

Read Committed Snapshot Isolation Remove All Writer/Read Contention [duplicate]

Does Read Committed Snapshot Isolation remove read-write contention in any condition? This is what I am reading online. However, if a read query executes and acquires a shared lock first, it can block ...