September 5, 2011 at 4:23 am
There is a table with some 25 lakhs of record. It has already 8 statistics. During the query analysing, system has recommended 7 more statistics for that table. What is the optimum no of statistics for a table. I think lot of index will lead to loss of performance. Is there any constraint for no of statistics also?
Thanks for the replies
September 5, 2011 at 4:34 am
I think it's all down to what performance you need. In extreme tests I was able to show that for inserts adding secondary indexes to a table could degrade performance by 10%, the same with foreign key constraints. However taking a very extreme example I added a number of indexes to a cross database report ( it was aweful - but it's what the client wanted 🙂 ) and got the execution time down from 21 hours to 6 seconds. They hadn't added indexes because it would "slow down" the data imports. However if it then takes a day to get your report then it's false economy.
I work on the basis that generally people expect their queries to work quickly,users having to sit around waiting usually proves complaints to managers that the system is "slow" which the business then equate to the applications being "crap".
So you have to balance indexes and statistics against user expectations and essentially find a solution which works for you - sometimes this involves removing secondary indexes for loads and putting them back afterwards - this tends not to work so well when tables approach 100's of millions or billions of rows.
Regardless of what you do, assuming you have auto stats turned on, sql server will generate stats behind the scenes anyway. I never really hold much with those " what's the correct ....... " as often it just depends. Hope this helps.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 5, 2011 at 6:53 am
loop
Thanks
September 5, 2011 at 7:16 am
forsqlserver (9/5/2011)
loop
What exactly are you trying to tell us?
I've noticed a few of your "loop" posts recently but I can't figure out the intention behind it...
September 5, 2011 at 7:40 am
Just to be included in the answers
Thanks
September 5, 2011 at 7:59 am
forsqlserver (9/5/2011)
Just to be included in the answers
You don't have to do that. If you go to the top right of the page with the question on it and click on Topic Options, you can subscribe without posting anything.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 5, 2011 at 8:03 am
There's not enough information here to give you good info. First, the database tuning adviser is a pile of steaming brown stuff. It's recommendations are frequently useless and sometimes dangerous. I do not advocate it's use.
Second, from the sounds of things, you're adding manual statistics to the tables, not indexes. Those are different things. In general, I don't think adding statistics, in most cases, is the right way to go. There are edge cases where it helps, but most of the time picking a good index is a better approach.
Finally, you're going to have to evaluate those suggestions. What columns are they adding to indexes? Do those columns already exist in different indexes? What data types are the columns? What do the queries look like that are running against the table? What do the execution plans for those queries look like.
Unfortunately query performance tuning is much, much more involved than simply running the DTA and then taking it's recommendations at face value.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply