Hi,
I am having issues with performance reports. We have two environments, a production environment and a DR/Test environment. Reports in the production environment which is larger and busier work fine, reports in the DR/Test environment either fail saying "The data source timed out" or more commonly just sit there doing nothing for hours. This is primarily when running a report against a Cluster for the last month. We are running vSphere 4.1 Build 491557. The message in the vpxd log is that the sql statement did not complete in time and this keeps being repeated. The sql being executed is as follows
SELECT DISTINCT sm.sample_time, sm.sample_interval FROM vpx_stat_counter sc WITH (NOLOCK), vpx_sample_time1 sm WITH (NOLOCK), vpx_hist_stat1 st WITH (NOLOCK) WHERE sc.entity_id = ? AND sc.counter_id = st.counter_id AND st.time_id = sm.time_id AND sm.sample_time > ? AND sm.sample_time <= ? ORDER BY sm.sample_time
I am not a dba but looking at the execution plan shows a table scan against vpx_stat_counter which is ok as there is no index on the table. I did a comparison of the table sizes and row count of all the vpx_hist_stat? tables and vpx_stat_counter and interestingly enough, the vpx_hist_stat? tables were all bigger in the Production environment as I would expect yet the vpx_stat_counter table is 4 times larger in the failing environment with over 200,000 rows.
The statistics settings in both environments are the same and all the roll up jobs are completing successfully. So my questions, why would vpx_hist_stat be 4 times bigger in a smaller environment ? Can the data in there be purged in some way ? Could an Index be added on the fly to that table to prevent the table scan ?
