Tag Info

New answers tagged

3

Use a composite index: INDEX(confirmada, codTipoTransaccion, idTransaccionOriginal, idTransaccion) The columns can be in any order, so I recommend shuffling them to meet other need(s) and/or facilitate removing some other index from t2. If you need to discuss this further, please provide SHOW CREATE TABLE for each table. Edit For JOIN ... ON ...


0

Well I have some ideas which could cause this behavior. Do you monitor your memory pressure? Maybe your queries raise a certain limit which will cause the flush of the plan cache. I don't know your application, but does this correspondent with your logs from your frontend servers? Is there pressure too during this time? Do you have a dedicated SQL Server ...


0

Have you considered using plan guides to freeze the better execution plan? After you've rebuilt your plan, you can freeze that plan in cache using plan guides. From MSDN: Plan guides let you optimize the performance of queries when you cannot or do not want to directly change the text of the actual query Here is a process that I often use to save ...


2

For future readers, I went ahead and tested both methods (with and without the subquery), and they both worked equally well on all tested environments. I do not know if the database statistics needed updated on the databases that originally had a problem or not, but like many developers, I am not in charge of that and don't have any ability to alter it. ...


1

Thanks for all the help. I thought I would send an updated version of the above query that we found helpful. -- Search plan cache for Memory Grant issues IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp -- Collect more info about the plan here if required, eg usecounts, objtype etc, SELECT IDENTITY( INT, 1, 1 ) rowId, query_plan, db = ...


2

The declaration of singleton in the path expression of the index enforces that you can not add multiple <Number> elements but the XQuery compiler does not take that into consideration when interpreting the expression in the value() function. You have to specify [1] to make SQL Server happy. Using typed XML with a schema does not help with that either. ...


3

This is not an issue, it is by design and is considered a performance improvement. If your queries do not run as expected you will need to address this in the code of your database/application. You can force the old carnality estimator by using the trace flag 9481 at the server, session, or query level. This will force it no matter what the compatibility ...



Top 50 recent answers are included