The SSRS report wont show up in sql server 2005
The query runs for hours say 10-12 and even more, so cannot get the actual plan as of now.
Edit: Just added few indexes and now query takes 4 hours but gave the result. Still no better for us. Attaching the Actual plan.
Also, I used this post from Jonathan https://www.sqlskills.com/blogs/jonathan/digging-into-the-sql-plan-cache-finding-missing-indexes/ to find missing indexes from buffer, but this query never showed up there.
Attached is the Actual plan named as production_ssrs..
Adding: Because of this query theres lot of blocking : Analysing the wait stats:
I checkd that for 4 hours when this query is running , 90% wait time is
link textASYNC_NETWORK_IO
Other stats:
reads 45,480,151
writes 10,863
physical_reads 354,051
Any guidance is really appreciated as we need to improve this at earlist because of problems user facing for same.
Edit: @Aaron , sir Please find attached the actual plan ran from SQL sentry explorer
- Add the column extOnEqpComponent_EcmIDFK to the index dbo.tblEqpExpectedTask.Boost_Performance
- Add the column sitSubdivision to the index dbo.tblSitSite.Major_Performance_Improvement_2
- Add the column wraFiniValue to the index dbo.tblWooReadingActual.Boost_Performance
(I don't know the data types or all other potential uses of these columns, so you may want to experiment both with adding them to the key and adding them as INCLUDE columns.)
The hard-coded list of sitsubdivision values – have you thought of putting those in a table so that they could be indexed and joined against? As is, this is just a large series of OR conditionals that are hard for SQL Server to optimize.
But really I think all of those XML PATH concatenations are going to kill you no matter what you do with the indexes etc. I would probably start with not returning 700K+ rows to the client in the first place – maybe you could consider pagination, so you just return the top 100 WooID rows, and then from there do the XML PATH operations on just 100 rows instead of all 700K.
SQL server 2005 running on windows server 2003 on 32 bit platform, with 16 GB of RAM. So we understand, its asking more with current infra.
And yes, i have talked with developer on re-writing the code as of now to retrive the desired results instead of 700K.
Thanks again for the inputs!