What is the frequency of the CPUs?
Lower frequency will take longer to execute the given statement(s) which will result in longer duration when querying the data. The retrieval of the requested data is then down to the throughput (I/O) of the underlying hardware.
What happens if you/they run the query twice?
One the data is in memory, it shoudn't take that ...
That production has more CPUs does not matter when you use MaxDOP 1. Then only CPU Clock cycle matters. With many CPUs, you often get lower 1.x GHz clock cycle. A higher clock cycle on your QA could explain some of the difference.
If the database cannot fit into memory, then your hard disk speed difference sounds as if it alone can explain the different ...
In general I agree somewhat: if the customers are happy, don't start tuning.
But there are certainly things that can go wrong without you noticing before it is too late. Here is a random list of things that should cause an alert, without a claim to completeness:
Is there enough free disk space?
Is the CPU overloaded?
Is the I/O subsystem overloaded?
You are running MariaDB, PHP, and the web server all on the same 8GB machine? If so, 6G for innodb_buffer_pool_size is too high. Lower it to 4G. See if the RAM usage stays lower than 8G. If so, you can raise it some -- but not so much that swapping occurs.
Hmmm... One thing says 8G, one says 12G. Regardless, lower the buffer_pool setting to avoid ...
As already answered by Mr. Josh Darnell in detail about the decision made by optimizer and details on parallelism due to pre-sorted data, I would like to add my bit in the answer that, we mostly rely on logical reads for measuring performance and not the time because time varies a lot based on many factors like load on the server, network, disk etc(You can ...
Looking at the ratio of elapsed time to CPU time for both queries, we can see that the "no index" query benefited from parallelism - CPU time was about 3 times greater than elapsed time.
After adding the clustered index to Table_B, you got a serial version of that execution plan (CPU time and elapsed time were about equal). Or maybe an entirely ...
These are excessive; don't set them to more than about 1% of RAM:
It might help if you showed us the queries and SHOW CREATE TABLE.
See if your PHP code is using a high value in ini_set("memory_limit",...). The OS may be picking MySQL to swap even though PHP is hogging memory.
Both the log send queue and redo queue can be found in sys.sys.dm_hadr_database_replica_states (docs), and also available using the SQLServer:Database Replica Perfmon counters (docs).
With Perfmon counters, you'll need to collect counters individually on each replica. Each replica knows about the Send/Redo(recovery) queue affecting that replica ...
As stated by a previous user the lock type indicates that your query tries to acquire an Exclusive lock on a resource.
Is your predicate (Col1=@col1) supported by in index ?
Some options for a query referencing a specific table to acquire locks on other tables can be: triggers, fks and fk cascading.
More info is required. See this post for more ideas.
Access to a row using a non-clustered index, which idx_name is, requires extra random I/O: b-tree lookup finds the clustered (primary key) index value, then you need to go and fetch the actual row from the clustered index.
The alternative is a sequential scan of the clustered index itself, which does not incur that extra I/O cost and is also simply more ...
Yes, you can make MySQL not use as much swap by making it use huge memory pages. Huge pages are unswappable.
In my.cnf, [mysqld] section:
large-pages = 1
Additional OS level configuration is required. For example:
groupadd -g 630 hugetlb
usermod -G hugetlb mysql
sysctl -w vm.nr_hugepages = 20480
sysctl -w vm.hugetlb_shm_group = 630
You may need to increase ...
You mentioned that it is a queue table, so the data distribution is probably skewed: Most of the rows have status representing completion and ideally just few rows would be candidate for process.
For the query in the image, the ItemStatus values used in the predicate are literals so this is the perfect use case for a filtered index:
CREATE INDEX index_name
It has to scan the whole index. Look at the output of:
SHOW INDEX FROM user_actions;
You will see the approximate cardinality of each index. The approximation is based on a few random dives into the index, not an exhaustively checked exact number. The approximation is sufficient for balancing.
The problem with hypothetical questions is that we can't ask for more info. Nor can we ask for clarifications (did you mean PAGELATCH_IO, PAGEIOLATCH or PAGELATCH?).
Seeing just PAGEIOLATCH and low PLE doesn't necessarily mean that it is tempdb. 32 GB RAM is not much nowadays, but we know nothing more about this environment. We are waiting for I/O, that is ...
It seems that you haven't reproduced the question from the training materials here with complete accuracy.
Assuming that the question was about PAGELATCH_** waits, and that the latched pages are in the tempdb database, then those waits can be a common sign of tempdb allocation contention. And the most common solution to that is to increase the number of ...
There is a scenario where an estimated execution plan can contain an operator that doesn't get used at runtime, for example when a predicate renders the operator a no-op.
It's tricky to spot because there aren't many clues it's happened, other than the operator's actual execution count is 0. I helped drive a change in SentryOne Plan Explorer that makes it ...
But after the run, the fragmentation is still very high. Is that okay? What could be done?
The reason lies in the last column of the output you have pasted. See the column page_count. Unless you have page_count value > 2000 there is no point in rebuilding, reorganizing and updating stats of that index. This is because and I am quoting from article I ...
Bottom line: fragmentation is irrelevant for small indexes. Never bother about it for indexes less than some 1000 pages, or perhaps we should say 10000 nowadays. If you remember cassette tapes, we always have some noise in the background (my analogy, perhaps works better in Swedish).
Tip1: don't use single quotes for column names in SELECT list. It divert ...
We noticed a similar behaviour in our system.
And also encountered that writing the query with hardcoded parameters instead of using setParameter() would fixed the issue.
We are using MS SQL Server and after further investigation we noticed the the root cause of our issue is a default configuration of the sql server driver that transmits the query parameters ...
What you are ReComputing ? and how you are Recomputing is not so clear ?
Advice to Avoid DISTINCT,UNION is already given.
I don't know what is the purpose of CROSS JOIN in your query and whether it can be avoided or not.
If CROSS JOIN is producing not a row more not a row less then it will not hurt performance.
Instead of querying FROM Document again and ...
The amount of data we are talking about here is not nearly enough to be this big of a problem. Odds are, a bit of optimization will do you quite nicely.
So, opening your query plan, as I go along:
Fix your WHERE clauses. Your current query forces table scans just about everywhere because you use functions. IsNull(COALESCE(linkedlot.IsPrivate, c.IsPrivate), ...
I have experienced that when modifying the max_connection value to a high number 50000, for example, it is also needed to modify the kernel.sem (semaphores) value, I am not sure what would be the correct parameters in this case.
This values allowed me to change the max_connection to 40000 but not to 50000
------ Semaphore Limits --------
max number of arrays ...
The only way to efficiently maintain a large cache of relationships like this is to not recompute the whole lot. Unfortunately partial updates, and reliably knowing what to update, can be complex depending on the requirements dictated by your business rules.
compute a hash for all the records in the tables involved
This is your first (and possibly biggest) ...
Got to point out that batching up, whilst making the process of the batch probably quicker. your unit of work is now the batch, thus handling failures and retries is way way harder. Which one in the batch caused the failure.
Secondly co-ordinating all the inputs into a batch and then fanning back out is a level of complexity that you might not want.
There is not a lot that can be done. But one thing:
There are probably 2 sorts. (See EXPLAIN FORMAT=JSON SELECT ... for details.) You can get rid of one of them by making the ORDER BY the same as the GROUP BY:
GROUP BY item_name, tier
ORDER BY item_name, tier
As a minor improvement, I got rid of the useless CONCAT. (In this formulation it does not ...
I would say that a UNIQUE index is comparable to a PK index. The difference between the two depends on how the dbms handles NULL values. PostgreSQL allows multiple NULL values in a unique index (https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS) while other dbms do not. In most dbms, a PK constraint is ...
In PostgreSQL there is no difference between a primary key index and other indexes, so that does not surprise me.
The only slight difference could be caused by the size of the indexed data (wider values cause deeper indexes, so more blocks have to be traversed) or the speed of the comparison function. In most real world cases, the difference will be hard to ...
I think you should first understand the difference between Sync IO and Async IO. The information about the basic nature of both the I/O's can be found in Bob Dorr's I/O presentation blog see the section Async vs Sync IO.
In very simple meaning Async IO is one in which after putting I/O request the program or calling code will not wait for the I/O operation ...
The easiest way would be to stop using Profiler and go to Extended Events. The query_post_execution_showplan event has duration built right in and you can use that to filter capture quite neatly. Here's a simple example:
CREATE EVENT SESSION ExecPlansDuration
ADD EVENT sqlserver.query_post_execution_showplan
(WHERE ([duration] > (10000)))...