Hot answers tagged

12

If you can't change the query you can use a plan guide. Test the performance of the query with OPTION (QUERYTRACEON 4138) (will need someone with sysadmin permissions to try this). If that produces satisfactory performance you can apply this with a plan guide. If it doesn't produce satisfactory performance try and find a hint that does. Possibly OPTION (...


8

As you are specifically interested in locking rather than general waits the locks_lock_waits extended event sounds more suitable. With a filter on increment >= 200 CREATE EVENT SESSION [locks_lock_waits] ON SERVER ADD EVENT sqlserver.locks_lock_waits( ACTION(sqlserver.sql_text) WHERE ( [sqlserver].[is_system] = 0 ...


5

I don't know fully details of your environment, i.e. if the tables in question are mostly used for writing or reading. How often you do this delete? what is the primary key and clustered index of [sko].[stage_närvaro]? If I wanted to optimise this delete there are a few things I would consider: 1) an index on the underlying tables of the view ext....


5

If you are interested in locking, there are several extended events available: lock_acquired lock_released lock_escalation The first two events have a duration column in (microseconds) which you could filter on for your thresholds. They also have a resource_description action which will give you some detail on the resources involved. The lock_escalation ...


4

In SQL Server 2014 & up, new cardinality estimation logic was introduced. From BOL : The cardinality estimation logic, called the cardinality estimator, is re-designed in SQL Server 2014 to improve the quality of query plans, and therefore to improve query performance. The new cardinality estimator incorporates assumptions and algorithms that work ...


4

There is no benefit to including the ORDER BY columns in the SELECT list. On the contrary, having unrequired columns in the SELECT list incurs a fractional overhead in run time and a larger one in maintenance.


4

BCHR can be very misleading due to read-ahead: The Database Engine supports a performance optimization mechanism called read-ahead. Read-ahead anticipates the data and index pages needed to fulfill a query execution plan and brings the pages into the buffer cache before they are actually used by the query. This allows computation and I/O to overlap, taking ...


4

This query usually perform better: SELECT rn.ID, --... other columns go here rn.OrganisationID FROM ( SELECT *, n = ROW_NUMBER() OVER(PARTITION BY OrganisationID ORDER BY id) FROM #t ) rn WHERE n= 1


3

Whatever you do, if you go down the RDBMS route, I recommend that you choose PostgreSQL over MySQL - it is a far more sophisticated product and has lots more functionality. Check constraints Window (analytic) functions CTEs - Common Table Expressions Proper set operators - INTERSECT, EXCEPT as well as UNION (which MySQL does have) Far superior support for ...


2

You don't select those ids that are minimum in their systemno group so you select only those that have a lower id in their group: SELECT id FROM dbo.SomeTable st1 WHERE EXISTS ( SELECT * FROM dbo.SomeTable st2 WHERE st1.SystemNo = st2.SystemNo AND st2.id < st1.i )


2

It seems that you created view ext.v_imp_närvaro on the base table sko.stage_närvaro, if it is right then it will be good choice to create a temp table with qualifier rows of where clause and then execute delete statement on table and use temp table for check existent of rows.. SELECT person_id,termin_fakta,läsår_fakta INTO #Temp FROM [ext].[v_imp_närvaro]...


2

The bad plan is probably a culmination of many problems. Which means that there are many ways of tackling it. My guess is that the culmination of problems causes two plans to look falsely close to each other in cost, and then the differences in memory setting (probably effective_cache_size) between master and slave is the straw that broke the camel's back ...


1

FROM ( SELECT ... ) JOIN ( SELECT ... ) does not optimize well. Think of a better way to write the query. If that fails, put one of the subqueries into a TEMPORARY TABLE and add an index to it. Consider using the datatype DATE, not INT, for dates. OR is a performance killer (because it prevents use of an index). Consider other ways to deal with IS ...


1

First, add these indexes: ALTER TABLE cat_01 ADD INDEX `ShopBuyDates` (`shop_id`,`buy_date`) ALTER TABLE orders ADD INDEX `OrderSales` (`order_id`,`sales`) Then try this query, and report the results. Make sure to run it at least twice, and discard the first result's performance, to flush out the effects of populating the cache. SELECT 'cat_01' as ...


1

These metrics often have to be looked at in parallel with other metrics. Simply doing a table scan is not a bad thing in itself. Often the query engine will pick a table scan even if an index exists if it's faster, for example on a tiny table. You need to look at other metrics, such as Disk Read/Sec & Disk Write/Sec, along with PLE and others. Note ...


1

PLE just tells you how long the oldest page has been stored in memory before being evicted due to memory pressure. It could be that you have a high frequency of little queries that hit cache and perhaps a large one that flushes out the cache and requires them to recache. Without having additional data this looks like an environment that perhaps is split ...


1

On my tests, I do not see a significant difference (full table scan, 6 rows only, like your example): $ for i in $(seq 1 10); do for i in $(seq 1 100000); do echo "SELECT * FROM t1 WHERE CAST(c AS BINARY) = 'f';"; done | /usr/bin/time -f "%e" mysql -BN test > /dev/null; done 5.10 4.93 5.00 4.96 4.97 5.21 4.95 5.04 5.00 5.03 $ for i in $(seq 1 10); do for ...


1

I'd start investigation with the following: Check physical servers parameters like CPU, physical memory, hard drive speed, network , etc, and make sure they are comparable (ideally identical) . Check Oracle memory settings (memory_target) on both servers Make sure dbms_stats procedures were executed on new server after copying database (at least ...


1

Note sure if it will be more efficient select * from ( SELECT PID.ID, PID.OrganisationID , row_number() over (partition by PID.OrganisationID order by PID.id) as rn FROM #t AS PID WHERE PID.OrganisationID <> 0 ) tt where tt.rn = 1


1

There are benefits to indexing foreign keys as they provide better join performance as described by SQLSkill's team here and here. It is generally recommended to create an index which leads on the foreign key column(s), to support not only joins between the primary and foreign keys, but also updates and deletes. A script can be found here or Indexes ...


1

In 1GB of RAM, you may be able to set innodb_buffer_pool_size to 50M, maybe 100M. But definitely not 800M. The 80% advice is predicated on having at least 4GB of RAM, and 80% is too high even for that. Having other things on the same machine (Apache?) adds to the cramped quarters. If you have Apache, lower MaxClients to 10. Other things to keep out of ...



Only top voted, non community-wiki answers of a minimum length are eligible