November 4, 2014 at 4:14 am
Hi Team,
I have updated the statistics of one of my Fact table containing 3lacs rows of data.
And The query was to join the fact table with other 4 dimension tables of each 1500 rows of data. Except one of those tables has 30000 rows of data and the same table along with fact table had huge data updates on them.
Usually it use to take 14 Secs when used to clear buffer and cache data. But when I updated the statistics of both table and the primary clustered index of all the above involved tables the query was finishing in 25 Secs.
Please help.
November 4, 2014 at 4:38 am
kiran.vaichalkar (11/4/2014)
Hi Team,I have updated the statistics of one of my Fact table containing 3lacs rows of data.
And The query was to join the fact table with other 4 dimension tables of each 1500 rows of data. Except one of those tables has 30000 rows of data and the same table along with fact table had huge data updates on them.
Usually it use to take 14 Secs when used to clear buffer and cache data. But when I updated the statistics of both table and the primary clustered index of all the above involved tables the query was finishing in 25 Secs.
Please help.
Is this a blog post, or do you have a question? What is a lac?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 4, 2014 at 5:02 am
A lac is 100, 000
If you're running update statistics on tables where you've either done a full load of the tables or rebuilt your indexes, on large tables you'll be 'updating' your stats with a smaller sample set, which could impact performance.
Is this the case here?
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
November 5, 2014 at 6:00 am
Yes,
It was my FACT Table where i've updated the stats of the Index, which has hit the perfomance.
Perviously it use to take 14 Secs to retrive the data. now it almost takes 22 Secs.
What can we do now?
Updating Stats have inface degraded the performance. Please Help.
November 5, 2014 at 6:54 am
What sample did you use when you updated the statistics and was this different than what you used when you had your 14-second time? Using FULLSCAN takes longer, but produces more accurate statistics.
Also, is your query hitting the clustered index or a nonclustered index? I'm asking because if you updated statistics on your clustered index and the query hits a nonclustered index, your query would be using stale statistics on the NCI.
November 5, 2014 at 11:04 pm
Hi Ed Wagner,
The sample data added is no diff to the existing.
So I was not relying on FULLSCAN.
What is NCI??
Also I updated the stats of Clustered Index on a fact table. But the query optimiser uses non clustered index.
And have degraded the proformance.
November 6, 2014 at 5:32 am
kiran.vaichalkar (11/5/2014)
Hi Ed Wagner,The sample data added is no diff to the existing.
So I was not relying on FULLSCAN.
What is NCI??
Also I updated the stats of Clustered Index on a fact table. But the query optimiser uses non clustered index.
And have degraded the proformance.
An NCI is a shortened form of nonclustered index. If you added a lot of data to the table, you've very likely changed the distribution of data in the columns. Statistics contain information about the distribution of data within your columns. The query optimizer uses the statistics to determine which query plan to use when executing your query. If the statistics are stale, the optimizer might use a sub-optimal plan or it might end up doing a full scan.
You say your query is using a nonclustered index, so I would try updating statistics on your nonclustered index and see what the results are. I view this as a pretty simple thing to do so we can exclude this as the source of the problem. The next thing would be to check the actual execution plan.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply