Tag Info

New answers tagged

2

Yes, you can specify the SQL_ID. The usage would be something like this: SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('gwp663cqh5qbf',0)); To find the SQL IDs for the current session, you'll first need to get the SID for your session, and then search v$session for the related SQL_IDs: select sys_context('USERENV','SID') from dual; select SQL_ID from ...


0

You can begin from reading on MSDN Query Tuning. Initially to Begin with , find the Queries which needs to be tuned on basis of how bad they perform in terms of resource consumption or time they are taking to execute and then analyse their execution plans accordingly. There are many free tools available out there apart from SQL server inbuilt tools like ...


0

I think this stack exchange link gives a good rundown of why it is doing what it is doing, and I believe it has most to do with the size of the results sets and how they are indexed/sorted in your case. Order and Order Details, from this database are 830 rows and 2155 rows respectively. These are similar results sets where one has 2 rows (roughly) for ...


1

Well...We solved the problem. There was an Update, inside the procedure running that "select * from...". I commented the update. no more problems.


1

This may be an issue with OLEDB calls to remote servers (linked servers and SSIS configurations use OLEDB). This is a design flaw, Microsoft SQL Server bug that wasn't patched until SQL Server 2012 SP1 from what I recall where it doesn't allow remote stats to be used to optimize the query remotely. You will need to run sp_WhoIsActive (download | docs) from ...


2

It's a simple select. Why is this using fetch_cursor? The SELECT is system-generated by the Distributed Query framework, and is associated with the UPDATE you found. The Remote Update query plan operator uses the sp_cursor model to fetch rows from the remote data source. This is the cause of all the cursor API calls. I believe the cursor plan you show ...


9

Your Predicate is different to your Seek Predicate. A Seek Predicate is used to search the ordered data in the index. In this case, it'll be doing three seeks, one for each ItemState that you're interested in. Beyond that, the data is in ItemPriority order, so no further "Seek" operation can be done. But before the data is returned, it checks every row ...


3

GetItemToProcessIndex is not fully seekable because your where clause is on ItemState + LastAccessTime + CreationTime. The indexed columns and the where clause are a not perfect match. If you create a covering index on ItemState + LastAccessTime + CreationTime, for each match you get from GetItemToProcessIndex, you also get the value of your Primary Key ...


2

If these are quite large databases I would ask if they are on different physical disks, with one disk significantly slower or busier than the other? (i.e. one is SSD, and the other is a standard HDD). For smaller databases though, the data will quickly be cached, and so speed of the disk would stop being relevant at that point. I'd suggest turning on ...


0

One possible reason could be locking/blocking. If there is lots of activity going on in your 'Slow' DB, your queries might be waiting for long time before being able to acquire the required locks.


6

I can reproduce the plan that you describe on SQL Server 2012 (on prem) by running the DDL in your question and then fiddling the stats so SQL Server thinks that the table is much larger than reality. UPDATE STATISTICS [dbo].[JobItems] WITH ROWCOUNT = 10000000, pagecount = 10000000 And then running the query with OPTION (MAXDOP 1, CONCAT UNION, ORDER ...


5

Which option from above will perform better? Best case, both will produce exactly the same execution plan, with the same runtime performance. This can require some careful design and some fairly advanced skills, as Rob Farley mentions in his answer. Rob also has a blog post describing the core issue, and it is also discussed in one of his chapters from ...


2

I show some important things about views in my talk at http://bit.ly/Simplification - the key thing would be to make sure that you're not doing needless joins, that they get optimised out when you don't need those columns. My talk generally covers the idea of modularisation for an interface for developers, so it's probably quite useful.



Top 50 recent answers are included