The SSRS report wont show up in sql server 2005

SQLDB 2015-11-16 12:17:47

I am trying to imrpove the performance of the query attached.

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

link text

/storage/attachments/1410-actual-exec-plan.queryanalysis
Aaron Bertrand 2015-11-17 14:01:58
So this plan was gathered from Management Studio? Any chance you can capture it within an updated build of Plan Explorer (Help -> Check for Updates)? This way (1) it will include runtime metrics, and (2) it will include a properly anonymized statement (rather than just Statement1). That all said, it really looks like stats might be your issue – you have several operations with estimated rows of 2.7 billion and actuals of 38K, and others with estimates of 3K rows and actuals of 700K. These estimates are causing the optimizer to make very sketchy decisions about how to tackle some of these operators. So my first suggestion would be to update the statistics on the tables involved, and reissue the query with OPTION (RECOMPILE) to ensure the plan you have now has been invalidated. Next I would look at Table1 – all those columns are gathered from a key lookup, and I suspect that either the table is a heap and shouldn't be, or that the indexing is not optimal (for that many output columns, with that many rows, I would expect a scan or range scan on the clustered index).
SQLDB 2015-11-18 12:49:40
@Aaron, Thanks so much for the review. As mentioned by you i have updated the sqlplan from updated build of plan explorer. I have updated the stats as well, but could not complete as this db being used 24*5 causing further slowness. However i have updated few and query is now taking 2 hours. Not sure if this is related to stats or query being in buffer. Would appreciate if you can help me in analysing what else i might look to get this resolved.
Aaron Bertrand 2015-11-18 20:23:11
Thanks for the update. What I meant by "capture it within an updated build" is to actually enter the query into the Command Text pane and then click Get Actual Plan. The new plan you attached was generated in Management Studio and then opened in Plan Explorer. It also has the query text included, but it is truncated, so seeing the entire query in some way would be useful as well.
SQLDB 2015-11-22 06:06:48
@Aaron, Sure, i have attached the new plan as ran from explorer. I can see many REd on index seek and orange on Key look up. What could possibly be wrong apart from stats, which i am checking to update. Is there something on code. Note: MAXDOP is set to 1 for this reporting server whose DB act as subscriber from its publisher. Thanks
Aaron Bertrand 2015-11-23 16:05:14
To eliminate the worst key lookups, you can try to see if these will improve performance:

  1. Add the column extOnEqpComponent_EcmIDFK to the index dbo.tblEqpExpectedTask.Boost_Performance
  2. Add the column sitSubdivision to the index dbo.tblSitSite.Major_Performance_Improvement_2
  3. 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.

SQLDB 2015-11-26 11:57:31
Aaron@ Sir, thanks so much for the efforts! I've tried adding those columns on the index mentioned and now they take around 10-15 mins to complete, but its acceptable because we are dealing with issues like:

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!

November 16, 2015
No Comments