May 23, 2011 at 4:08 am
Hello Guys, I can see in my database that there are 20 or in some cases even 25 automatically created statistics [starting with "_WA_"]. Does having so many statistics on the table harm the performance ? We run our maintenance schedule every Sunday and rebuild indexes [update stats are OFF]. Does stats gets updated whenever indexes gets rebuilt ? We are facing performance issues in first two days of week. Once the statistics are updated, issue gets resolved.
May 23, 2011 at 4:23 am
sqlnaive (5/23/2011)
Does having so many statistics on the table harm the performance ?
Nope.
We run our maintenance schedule every Sunday and rebuild indexes [update stats are OFF]. Does stats gets updated whenever indexes gets rebuilt ?
Only the stats associated with the indexes are updated during an index rebuild. Column statistics (which is what you're talking about) do not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 23, 2011 at 4:47 am
Gail, How are these _WA_ stats created and on what criteria ? Are these good for performance ?
Please let me know if you have some useful document link on this.
May 23, 2011 at 4:59 am
They're created by the query optimiser when it needs to know the row distribution of a column and there are no existing statistics.
Read up on statistics and auto_create_statistics in Books Online.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 24, 2011 at 12:02 am
Off lately we are getting our sproc stuck at every day at same time. We have to update the statistics and then the process starts smoothly. We have pin point two tables for which update stats are required. The auto stats is on for those tables. What can we do for this ?
May 24, 2011 at 1:58 am
Create a job that runs update statistics with full scan on those tables. Schedule it to run as often as you find necessary.
Auto update stats only kicks in after a specific threshold, on larger tables it's often too large.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 24, 2011 at 4:59 am
Gail, we have few tables who have rowcount at around 1000000. These tables have inserts and updates every day for approx four hours. Thereafter delete operation occurs as well. Will autostats be good option for such environment ?
May 25, 2011 at 8:13 am
We have arranged daily rebuild index jobs on selected tables. I was comparing index fragmentation at various times and strangely found that for one table after data deletion, the index fragmentation decreased. For rest of the tables after deletion, it increased the fragmentation level.
May 25, 2011 at 11:38 am
sqlnaive (5/24/2011)
Gail, we have few tables who have rowcount at around 1000000. These tables have inserts and updates every day for approx four hours. Thereafter delete operation occurs as well. Will autostats be good option for such environment ?
Not enough information.
If you are seeing queries that suddenly have bad performance, bad performance that is completely fixed by an update statistics, you may have a stale statistics problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 25, 2011 at 11:17 pm
Auto Update Statistics True
Auto Update Statistics Asynchronously True
Don't over kill the indexes
if require rebuild then rebuild the indexes and if required reorganize then reorganize it,dont apply rebuild on all indexes
1-avg_fragement_percent >10 < 30 then apply reorganize and page count must be greater than 1000
2-avg_fragement_percent > 30 then rebuild and page count must be greater than 1000
check the compatibility option of the database must be recent one,if selection is on specific data then apply filtered indexes
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 26, 2011 at 2:06 am
While checking the indexes on few of the tables I found that one clustered index shows two entries. One for IN_ROW_DATA and another for LOB_DATA. Though there are only two columsn for that clustered index. One is varchar(40) and other is small int. As per books online, LOB_DATA gets created only for text or varchar(max) datatype columns. What should be done in this case ?
May 26, 2011 at 4:21 am
sqlnaive (5/26/2011)
While checking the indexes on few of the tables I found that one clustered index shows two entries. One for IN_ROW_DATA and another for LOB_DATA. Though there are only two columsn for that clustered index.
There are only two columns in the clustered index key, but the clustered index contains all the columns in the table. That's what makes it a clustered index. Hence if there's a LOB column in the table, it's part of the clustered index.
No difference to what's already been discussed.
Bear in mind that the 10%, 30% are guidelines, not hard and fast absolute rules.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 26, 2011 at 5:35 am
Thanks a lot again Gail. I got it now. There was a varchar(max) field on the table. 🙂
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply