New answers tagged

0

Oracle now has a broad suite of monitoring tools, known as the Autonomous Health Framework that is free to use to all licensed Oracle database versions. It tracks both database performance information, and operating system information.


0

Additionally you can compare the before and after actual execution plans of the queries you're trying to tune by creating these nonclustered indexes: Actual execution plans are generated after the Transact-SQL queries or batches execute. Because of this, an actual execution plan contains runtime information, such as actual resource usage metrics and runtime ...


0

Here is my check list for when I give the DBA the orders to CREATE INDEX Proof that it actually solves a very specific business problem Now, there are some transactions that could be negatively impacted, proof of minor impact is included (where needed)


0

You need an index on the table that is referencing the table from which you want to delete: CREATE INDEX ON catalog_image (vehicle_id); This is because every DELETE on catalog_vehicle has to make sure that the row about to be deleted is not referenced. It does that by running a statements equivalent to SELECT EXISTS (SELECT 1 FROM ONLY catalog_image WHERE ...


2

I will preface by saying: a Table Lock is generally taken only when the number of locks on a single table hits 5,000, or 40% of lock memory has been used. See the documentation for further details. With that in mind: An Index Scan does not necessarily take a table lock, it will only do so once it hits the thresholds. This may not happen even on a large ...


0

The answer seems pretty much hidden in those wait stats. I probably see you have LCK.. waits followed by resource semaphore. It’s just a wild guess but probably you have queries being run ( could be in multiple threads ) even with large memory grants. When such will run it will make other queries wait on further memory to be granted. So either you have ...


0

We're using https://github.com/devops-works/slowql here and it works like a charm. It reads a slow query log and replays it on a server at the desired speed. It does not filetr the slow quelry log though.


0

I believe removing the memory limit is a good choice. I may get "pig piled" for this but I have used the default 2147483647 for max memory before on systems where SQL Server is the only app running and SQL Server does a pretty good job of grabbing what it needs without causing the OS to page out memory. If you choose a limit make sure you leave 4-...


1

If you select 1M rows, your query can't be fast. If you write the result on disk, the time is probably more than doubled. But you say that writing to a different disk doesn't make the query any faster. That could be because the table reading and the file writing are not parallelised, I don't know. But it could also indicate that the bottleneck is the CPU ...


1

If you only need to store aggregates, then storing those is a good idea. As with aggregates, the visitor_ip is going to be unique, we may as well make that the primary key. In MariaDB-10.5 or later you can use the inet6 type: CREATE TABLE visits (ip inet6 NOT NULL PRIMARY KEY, number_of_visits int NOT NULL); And update using IPv4 mapped ...


0

Add these composite indexes: t2 (post_user): INDEX(brand_id, user_type, user_id, post_id) -- replacing brand_user_idx t1 (comment): INDEX(brand_id, post_id, id) (and remove the USE INDEX) The problem is that it must gather all the possible rows based on the WHERE and ON, then sort them, and finally peel off 50. There is no shortcut to get avoid the sort ...


2

You may need NULL in the IsVerifiedOn date for another purpose later. If a situation ever arises where you know that something has been verified, but you don't know the date on which it was verified, you will be in trouble, because you can't say that. In general, NULL in a field always means "no data here". Assigning it another meaning can ...


1

I believe you might be "over thinking" it. I've never heard the phrase "over-optimization is an anti-pattern" and I would generally disagree with that. Rather than worrying too much about anti-patterns, architect to your use cases and be reasonable. If you know you'll never reasonably need the IsVerifiedOn datetime field in the near ...


1

Charlieface suggested: Backup/restore to somewhere else and run CHECKDB there. Two vCPUs and 16GB RAM for a 5TB instance seems rather undersized, but upgrading your storage speed is probably the first priority, can you get SSDs? See also: Minimizing the impact of DBCC CHECKDB : DOs and DON'Ts by Aaron Bertrand. A faster CHECKDB by Bob Ward of Microsoft. 3 ...


1

Changing NOT IN to NOT EXISTS as suggested by a_horse_with_no_name was the solution: not in () in Postgres is not as efficiently optimized as in Oracle. Try rewriting that as a NOT EXISTS condition. I replaced the subquery AND (bd.INSTANCE_ID, bd.ATTRIBUTE_ID, bd.INSERTED) NOT IN (SELECT ... ) with AND NOT exists ( SELECT 1 FROM my_data dc ...


1

To speed up the inner loop as much as possible, you could use this strange and very specialized index: CREATE INDEX ON my_data ( (CASE WHEN (tenant = 1) THEN 1 WHEN (tenant = 2) THEN 3 ELSE -1 END), inserted ) WHERE modified_record IS NOT NULL AND tenant = ANY ('{1,2}'::bigint[]) AND status = 'DELETED';


1

If there is something else in the WHERE clause, such as WHERE state = 2 AND foo = "blah" Then add a composite index INDEX(state, foo) Then the "cardinality" question applies to the combination of those two columns; the index is more likely to be used. At the same time, drop the existing INDEX(state) as being useless; even 'in the way'.


6

Expanding on SMor's comment: The short answer is no - it does not matter whether you put filters into the join or the where clause when you use INNER JOINs. Use outer joins changes the situation greatly. And as usual, there are no absolute answers to any performance question. If 2 queries are logically the same, you need to examine the execution plans to ...


-1

was faced with a similar predicament using SQL 2008R2. The Main transaction table has three columns that are related to another table Notes. My solution was to create a subquery for the columns of concern and outer join to the main table. Tbl1 has three columns GId, VId and BId which relate to the primary key FieldID in Tbl2. The value we want for the report ...


13

Pretty much any "does it matter if I write something x way or y way" question can only be answered "maybe". Optimizers have to consider a lot of potential join paths and query plans and they don't have a lot of time to produce a plan. That means they end up using a lot of heuristics to prune potential plan trees early rather than fully ...


0

Procedure cache is not used for generic insert statements so I'm guessing what's really happening is the insert is firing a trigger (on the table being inserted into). If this is the case I'd want to take a look at the source code for the trigger. My guess is the trigger works 'ok' for single row inserts but was not designed/coded properly to support multi-...


0

You can use $indexStats to debug if all indexes are in use to delete some og them and speed up the inserts: db.collection.aggregate( [ { $indexStats : { } } ] ).pretty() More info: indexStats


3

in my ad-hoc SSMS query window, does it matter? In this context no, it doesn't matter. But not using SELECT * is a good habit to get into anyway, to reduce the temptation to lazily copy such queries into code destined for production. For the avoidance of doubt: while it is perfectly acceptable for quick inspection/diagnostics via SSMS or similar tools, for ...


1

Well, other than the fact that the top query will return 1000 rows while the bottom will return only 1 (I assume that's not your question), no, I wouldn't expect to see a performance or query plan difference between the two in most normal circumstances, especially for ad-hoc SSMS queries like you describe. The clear exception is when you don't actually need ...


8

My answer will not be a comprehensive one. Every data professionals have their own way of diagnosing a problem. As far as performance counters I will look at these ones together. SQL Server: Buffer Manager: Page life expectancy SQL Server: Buffer Manager: Buffer Cache Hit Ratio SQL Server: Buffer Manager: Lazy writes/sec SQL Server: Buffer Manager: Free ...


1

The exact queries for SGA and PGA tuning you might be looking for are: SELECT sga_size, sga_size_factor, estd_db_time_factor FROM v$sga_target_advice ORDER BY sga_size ASC; SELECT round (PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb, ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc, ESTD_OVERALLOC_COUNT FROM v$pga_target_advice; You should ...


3

That's correct in case only one file grows in the time: the benefit of making 8 data files is lost once the files are full and it behaves as though it is one file only To avoid such cases until SQL Server 2016 (13.x) you could use trace flag 1117: When a file in the filegroup meets the autogrow threshold, all files in the filegroup grow. This trace flag ...


1

Doesn't look like it. The time stats for that operator don't show it running for long, <RunTimeCountersPerThread Thread="0" ActualRows="2302947" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="5417" ActualCPUms="5406" /> ...


Top 50 recent answers are included