New answers tagged

0

I confirm that a full or incremental backup during business hours can make your database inaccessible to end users and web services. Factors that can make things worse: allocating too many channels to RMAN using an out of support version like Oracle 10 having a database that is CPU or memory or network IO bound running Dataguard at the same time ...


3

That depends on your disk subsystem. A full backup is going to involve a metric crud-load of I/O (that is a technical term). That is going to put a huge load on your I/O subsystem. Depending on how I/O bound your system is, how much spare bandwidth your I/O subsystem has, how fast your I/O subsystem is, etc. the impact will range from "yeah, maybe it's a ...


0

I had a very similar issue in Oracle 12c. In experimenting, the PRECOMPUTE_SUBQUERY optimizer hint 'forced' the subquery to execute first, thus making use of the defined indexes. In your snippet, that would look like this: ... where t.ObjectId = 12345 or t.Id in ( select /*+ PRECOMPUTE_SUBQUERY */ TaskId from ObjectAffectingTasks where ...


0

I will agree with @A_Horse_With_No_Name and mention that PostgreSQL is a excellent open source DB engine that has grown by leaps and bounds. It's fully ACID compliant out of the box and has a lot of features you can use. If you were deciding just between MariaDB and MySQL though I would go with MariaDB personally. Many large companies such as Wikipedia ...


0

I know of no such limit, although I have never had more than tens of materialized views, not thousands. However a way of hedging against such a problem is to make one materialised view with the customer as a column and create an index on the customer column. That way, Postgres will be able to pick out the relevant parts of the view very quickly. You ...


2

There seems to be a bug in MySQL (checked up to 5.6.21 on sqlfiddle.com) where the optimizer does not identify the optimal index when it is available. It can be worked around by using force index() hint: SELECT post_modified_gmt FROM ( SELECT @rownum:=@rownum+1 rownum, wp_posts.post_modified_gmt FROM (SELECT @rownum:=0) r, wp_posts FORCE ...


0

This will be a lot faster, but has flaws: SELECT id, ( SELECT count(*) FROM tickets WHERE order_id = o.id ) as attached_tickets FROM orders AS o LIMIT 1; It will need INDEX(order_id) on tickets. Which order.id do you want? There is no ORDER BY to say which one LIMIT 1 will pick. This may not ...


0

You can simply use IN for XYZ, so that you don't need to have one for each relation): SELECT * FROM T1 LEFT JOIN T2 ON (T2.T1_ID = T1.ID) LEFT JOIN T3 ON (T3.T2_ID = T2.ID) LEFT JOIN T4 ON (T4.T3_ID = T3.ID) LEFT JOIN XYZ ON (XYZ.ID IN (T1.XYZ_ID, T2.XYZ_ID, T3.XYZ_ID, T4.XYZ_ID))


2

So initially it's T2[ id PK, name, T1_id NOT NULL, -- because it is hierarchy, T2 can not exist outside the parent (T1). FK1 = T1_id ref T1(id) ] ... and you also say LINK_T1_T2_XYZ[T1.id, T2.id, XYZ.id] with a check T1 should parent of T2 You can migrate keys down the entities hierarchy T2[ id, name, T1_id NOT NULL, ...


1

First off, if your example is ACTUALLY a good representation of what you are trying to accomplish, then you are going about it all wrong.. You have three domains here - one is businesses, one is business offices, and one is products. Table BUSINESS should only contain information about the actual business (Netflix, for example) ... no office information at ...


0

I would guess that there is a weekly maintenance job that is running on that one day a week, that is affecting something that is forcing extra reads of the index. Is your daily batch job always run long on the same day of the week each week? If so, look at the views dba_scheduler_jobs and dba_scheduler_schedules to see if any jobs are scheduled. Now the ...


1

This is a bit of a fishing expedition but here would be my troubleshooting process assuming you don't have your own stats to help clear things up. Check the history of database size and compressed backup size from your msdb catalogs. Have they changed over the past few weeks. Sometimes an application will go bezerk and fill a database with garbage; what ...


1

It depends on the activity on the table. It there is no activity then there should be no difference.


1

As long as the developers only have that role in the development database instance, I see no problem; far better to tune now than later. Now, if your development machine has limited resources, there may be a reason to limit access. But in general, usually tuning tasks run from a few minutes to the default maximum of 30 minutes; during that time, Oracle may ...


2

UPDATE: What Worked... So after reading the link from @Raj and reading @ora-600's answer I tried to validate the database with the RMAN command backup check logical validate database;. While this worked fine, it was also clear that it was not looking at everything that the ANALYZE INDEX command would. After trying many different variations, I finally ...


3

Please look at the three main queries in the following answer (of mine): SQL Server performance: PREEMPTIVE_OS_DELETESECURITYCONTEXT dominant wait type The first query looks at currently running queries. The second two queries (labeled "Query #1" and "Query #2") work in combination to find high-cost queries over a period of time.


6

You are probably running into parameter sniffing issues. The fact that you execute the query with a different ARITHABORT setting makes SQL Server create a new plan and not reuse the existing plan as that setting is part of the cache key. Have a look at Slow in the application, fast in SSMS, it has a lot of information such as this: We have seen that ...


2

I think the article Raj quoted (https://www.pythian.com/blog/analyze-index-validate-structure-dark-side/) describes this pretty well. "clustering factor" was also my first guess while reading the description of your problem. I also prefer to use RMAN to check for corruption. RMAN> backup check logical validate database; Afterwards you can query ...


-2

I believe completely wiping out the data and doing an insert would be faster. As each update would require a DB scan to find the matching record and update it.


0

The real issue is not nested views in themselves. The real issue is the proliferation of nested views as developers layer additional tweaks on existing views. I have found queries with a nested view 4 layers that actually joined to one of the views in it's definition. Our tendency to take the easy way out rather than analyze and solve a problem is the root ...


0

I added a few minor tweaks as suggested by the community but no one said what I was doing was wrong. I have updated the my github project and will do future enhancements there. Thanks everyone that provided some feedback! Github project link


1

I had a similar problem. As it turns out, those ON DELETE CASCADE triggers were slowing things down quite a bit, because those cascaded deletions were awfully slow. I solved the problem by creating indexes on the foreign key fields on the referencing tables, and I went from taking a bunch of hours for the deletion to a few seconds.


2

I'm troubleshooting an issue with one of our SharePoint databases, essentially huge drops in PLE which I think are caused by increased I/O activity (I see increased reads, writes and lazy writes at the same time) I can see from the question that you have SQL Server 2012 SP1. There was Bug in SQL Server 2012 which forced PLE to plummet but that was fixed ...


1

P_S in MySQL versions below 5.7.4 doesn't collect information regarding executed prepared statements: https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-4.html. Maybe your application is using prepared statements instead of simple ad-hoc statements (parameters concatenated). Some client libraries, like PHP's PDO can avoid this (MySQL's) issue by ...


8

You are using a linked server to access Table1 and Table2. The first query is sent as is to the other server and executed there returning only the rows you want. The second query is doing a join between a local table TB_BRANCH and a remote table Table1. To do that it fetches all rows from Table1 and all rows from Table2 to your local server and does the ...


-4

Subqueries are always low on performance when involved with joins. Try replacing the subquery with a LEFT JOIN TB_BRANCH b ON t1.BRANCH_ID = b.BRANCH_ID WHERE b.START_DT <> '99999999' and see if the performance improves.


3

With schemabinding can improve performance in some cases. I couldn't recall all the details, but found an excellent blog post that goes through this in detail: With user defined functions that may need to update data. You might benefit from improved performance that would prevent an Eager Table Spool. SQL Server SCHEMABINDING Edit: Paul White ...


8

am I correct in thinking that LOB_DATA pages can cause slow scans not only because of their size, but also because SQL Server can't scan the clustered index effectively Yes, reading LOB data not stored in-row leads to random IO instead of sequential IO. The disk performance metric to use here to understand why it is fast or slow is Random Read IOPS. ...


22

Not all reads are equal. SQL Server knows that accessing LOB data is expensive, and tries to avoid it when possible. There are also detailed differences in the way the LOB data is read in each case: Summary The numbers are different because: The select reads the LOB in packet-sized chunks The variable assignment test does not read the LOB at all The ...


8

Presence of XML field causes most of the table data to be located on LOB_DATA pages (in fact ~90% of table pages are LOB_DATA). Merely having the XML column in the table does not have that effect. It is the presence of XML data that, under certain conditions, causes some portion of a row's data to be stored off row, on LOB_DATA pages. And while one (or ...


0

I'm wondering if you can use the undocumented stored proc sp_MSforeachtable rather than use cursors? Here's a little bit of code that brings back the number of rows in each table. Can you modify it to do what you need to do? use pubs2014 go create table #rowcount (tablename varchar(128), rowcnt int) exec sp_MSforeachtable 'insert into #rowcount select ...


1

You don't need loops, but one CASE per searched value: select ... from ( select ... case when 'Bach' IN (col1,col2,col3,col4,col5) then 1 else 0 end + case when 'Joan' IN (col1,col2,col3,col4,col5) then 1 else 0 end + case when 'Mike' IN (col1,col2,col3,col4,col5) then 1 else 0 end as matches from tab ) dt where matches > 0 ...


0

If you need a commercial tool then SQL Sentry Performance Advisor would likely get you to the bottom of it the fastest; it will capture queries, query times, query plans, and query aborts - but specifically it also has the best visualisation and graphing of the bunch. It's quite pricey but they have a fully functional trial.


2

The Brent Ozar Unlimited First Responder Kit is a good and free place to start. It will walk you through looking for slow queries and comes with a handy worksheet of what to do.


5

This is too long for a comment so I'll chime in with another case where one might want to turn off auto update stats. I've worked with databases supporting high-volume OLTP workloads and a stringent query performance SLA in milliseconds. Nearly all queries were trivial with a lot of attention to query and index tuning detail and some of the tables were ...


4

You are correct, I also believe that in most cases the Auto Update statistics should be set to true we should allow SQL Server to decide when to update stats and believe me it does good job. When this is set to true it make sure stats are updated about distribution of data in the field which would eventually help optimizer to prepare better plan. The ...


4

Generally I would say that having auto update statistics on is beneficial. But like any setting, there are reasons you can turn it on or off. One is that some tables have a lot of churn, and perhaps queries are not very sensitive to accurate statistics. Think ETL or other bulk scenarios where you're changing a lot of data, but either not reading it from ...


1

If you lump all the column_## columns into a JSON field, you can have a limit of 4GB. The question of how much gets complicated. InnoDB has 4 ROW_FORMATs: REDUNDANT, COMPACT, DYNAMIC, COMPRESSED. I think you could have 400 columns of VARCHAR(255) or TEXT (or ...) in ROW_FORMAT=DYNAMIC. That format uses a 20-byte pointer in the record to where the actual ...


-1

I found this link useful reference; https://www.percona.com/blog/2011/04/25/performance-schema-overhead/


0

Regarding Asynchronous and Synchronous connections, this is not about speed of the connection, but about the results of the synchronization. Syncronous - The purpose is to make sure that the Primary server and the synchronously updated Secondary server are kept in synch. The transaction is only complete once the Primary and Secondary server transactions ...


3

There are a number of different factors involved but it is entirely possible to have a replica that is synchronous be faster or slower than an asynchronous one. To list a few: Replica is under too much load Hardware specification differences (i.e. synch runs ssd and async runs rotational) or synch is using 8 cores @ 3.6 GHz and async is using 4 cores @ ...


4

...once they are no longer in cache, both selecting and deleting the same 12 thousand rows takes ~40 seconds. This seems to indicate that the storage subsystem is inadequate. If this is the cause, SQL Server will probably be waiting with one of the PAGEIOLATCH_XX wait types. It definitely appears that the LOB data must be loaded into memory on the ...


5

Here's one method that doesn't require a looping construct: DECLARE @sql nvarchar(MAX) = N''; SELECT @sql += N'ALTER DATABASE ' + QUOTENAME(name) + N' SET RECOVERY FULL; ' FROM sys.databases WHERE database_id > 4 AND name NOT IN ( N'distribution', N'SSISDB' ); PRINT @sql; --EXEC(@sql); I agree with wBob's answer in that one need not be ...


13

As far as loops go for this type of thing, don't worry about it. Loops and cursors have a bad reputation because there are normally better set-based approaches that are often faster. For admin stuff, sometimes loops are the only way, and no set-based ways of doing this spring to mind, although you can parallelise tasks with tools like start in DOS, SSIS, ...



Top 50 recent answers are included