Tag Info

Hot answers tagged

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 ...


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 ...


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 ...


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.


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 ...


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 ...


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 ...



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