Index Analysis never completes

gbritton 2016-09-08 15:50:13

Just downloaded SQL Sentry Plan Explorer. Loaded up a query and clicked Index Analysis. It never finished. Waited an hour.

What am I doing wrong?

Aaron Bertrand 2016-09-08 15:55:44
Is it possible you just haven't selected an operation from the drop-down on the Index Analysis tab, or inadvertently hit the up arrow when the tab loaded? (I can't tell – because you've anonymized your plan, this strips out the Index Analysis data completely.)
gbritton 2016-09-08 16:00:19
I don't see a drop down on the Index Analysis tab. Am I looking in the right place?
Aaron Bertrand 2016-09-08 16:15:14
There's a drop-down here to select the index analysis data for a specific operation from the query plan: [see image] However the plan you've uploaded has the elements in the drop-down, but it really seems like the plan wasn't generated by the current version of Plan Explorer. The operations are loaded but there is no index data with them. We'd need to know more details about the system, if you can reproduce the issue for other queries, etc.
gbritton 2016-09-08 16:50:29
Yup, every query I try has the same issue. Plan Explorer reports Version 3.0 build 10.9.9095.0. Brand new install. First couple of queries trying out the tool.

What else can I send to help? Oh btw, I can't view the image to the drop-down — corp. firewall blocking it. Let me try another way…

gbritton 2016-09-08 16:55:11
Nope still can't see the image. (tried it through Azure. should circumvent the fw. I get:

type Status report


description Access to the specified resource () has been forbidden.

gbritton 2016-09-08 16:57:15
Can't see the image (I get a 403). I'm running Version 3.0 build 10.9.9095.0 (just grabbed it today). Same issue with other queries.
gbritton 2016-09-08 17:17:50
I can reproduce with other queries, sure. But on a meta level. it seems my replies keep disappearing!
Aaron Bertrand 2016-09-08 16:09:05
Also, was this an estimated plan you generated previously (or pulled from SSMS)? The index analysis module works by collecting index, statistics, and other metadata when you generate a new estimated or actual plan from within Plan Explorer. It won't collect any data if you load a pre-existing .sqlplan, .queryanalysis, or .pesession file.
gbritton 2016-09-08 16:11:49
Not pulled from SSMS. I just loaded up the tool then grabbed a known bad query and pasted it in then generated a new estimated plan.
Aaron Bertrand 2016-09-08 17:49:10
Is it possible your executing user has delete and showplan permissions but not select and / or show statistics permissions? I was able to reproduce this scenario and get the completely blank Index Analysis tab like you did. If this is the case we should be able to surface an error message to the tab in a future update.
gbritton 2016-09-08 17:52:09
I have sysadmin on the instance
gbritton 2016-09-09 12:43:21
I have sysadmin on the instance.

FWIW I started up profiler to watch the action. AFAICS all the queries from Plan Explorer succeeded

Aaron Bertrand 2016-09-09 13:31:45
Any chance you could supply a backup of your database so that we can try to reproduce? Or at least copy enough tables and data into an "empty" database that still demonstrates the problem? Or even a stats-only copy of the database? This seems to be something specific to your environment, but we could spin a lot of wheels guessing. 🙂
gbritton 2016-09-09 13:34:00
I won't be able to supply a backup (tons of proprietary info). I can however send profiler traces if that's any help. Just need to know what you want to see.
SQLSaurus 2016-09-08 15:55:53
Hi there,

Sorry you are having trouble.

Would you mind sending me a version of the session that is not anonymized?

We would like to have a look at the details.

jhall at sqlsentry dot com.