November 6, 2008 at 8:47 am
DBADave (11/5/2008)
BOL provides an example on how to reduce fragmentation on a Heap, but I need to determine how to determine heap fragmentation in 2005.
use index physical stats. For an index, the avg fragmentation is the page fragmentation. For a heap it's extent fragmentation. Page fragmentation is meaningless for a heap
When I looked at the clustered index it was defined as a single column primary key with an INT data type. Why would I see LOB_DATA for this table?
Because you have a lob column in the table. The log_file column is image, which is a large object. Remember the clustered index is the table and has all of the columns at its leaf level, including the LOB
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
November 6, 2008 at 8:58 am
The_SQL_DBA (11/6/2008)
UPDATE STATISTICS DaveTable(anames)WITH FULLSCAN, NORECOMPUTE
GO
Why the norecompute? Turning off auto update on a stats set is a stupid thing to do unless you are regularly updating it manually and you know and are happy with the possible side effects of switching it off.
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
November 6, 2008 at 9:14 am
Can you explain a little more gila...
Do you mean why do you turn it off on the database....the option of the auto update statistics asynchronously
We turn ours off because with it on the processes were very slow and do full reindex every night.
November 6, 2008 at 9:23 am
TRACEY (11/6/2008)
Do you mean why do you turn it off on the database....the option of the auto update statistics asynchronously
I mean why disable the auto updating of stats, either at a database level or on specific indexes. There's a great potential for problems if you do so and don't understand the effects of doing it
We turn ours off because with it on the processes were very slow and do full reindex every night.
Generally, if the stats updates are slow, there's something else wrong, either hardware bottlenecks or poorly performing queries. The automatic stats updates run in read uncommitted, so they don't lock, and samples larger tables to reduce the IO impact.
The reindex will update the stats on the indexes. As long as the data doesn't change enough so that the stats get sufficiently inaccurate during 1 day, then that's OK. When do you update the column statistics that aren't part of indexes?
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
November 6, 2008 at 9:31 am
Once a week...i run it.
It was first when we went to SQL 2005 i had it set on ..i may review this again and see. I did recall doing a test with it on and with it off and it was a considerable amount slow.
Thanks Gila - i may go and do some more testing now that we gone through our SQL 2005 upgrade way back when.
Do you have it set on all your Databases ..(auto sync)...and i take it you never have to run this UPDATE STATISTICS ever..
November 6, 2008 at 9:38 am
TRACEY (11/6/2008)
Do you have it set on all your Databases ..(auto sync)...and i take it you never have to run this UPDATE STATISTICS ever..
Yes and no. I've found some cases where the auto update doesn't update often enough and manual updates are required (daily in the case of this table). Without the daily updates, the query plans went really bad and the queries really slow about 3 days after the update stats ran.
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
November 6, 2008 at 9:40 am
TRACEY (11/6/2008)
Do you have it set on all your Databases ..(auto sync)...and i take it you never have to run this UPDATE STATISTICS ever..
That is emphatically NOT correct. auto update only updates when a relatively large percentage of rows have been modified/added (20%??). That is unacceptable for many environments/applications. Some tables/databases may need to run update stats multiple times a day, others may not need to run it but once a month after some batch load.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 6, 2008 at 10:25 am
That's right. I forgot about the leaf level. If I plan on performing ONLINE reindexing then I will need to modify my code to check for LOB_DATA, but if I don't plan on doing ONLINE reindexing I can exclude that record when I check for fragmentation.
Thanks. Time to start coding.
November 6, 2008 at 10:46 am
What report do you run to get the tables with the out of stats on them.
Cheers.
November 6, 2008 at 11:13 am
One last thing now that im back on statistics....Do you do the auto sync for your tempdb, distribution tables. (I never know if to run update statistis) on these system tables.
Cheers
November 6, 2008 at 11:30 am
TRACEY (11/6/2008)
What report do you run to get the tables with the out of stats on them.Cheers.
There's no report that can tell you that the stats are sufficiently inaccurate to warrant an update. You can use the STATS_DATE function which will tell you when the stats were updated, but if the table doesn't change often, that can be a very long time ago without a problem.
You can use the rowmodcnt in sysindexes (deprecated in sql 2005 and will be removed in a future version) but that will only tell you how many changes (more or less) have occurred since the last time the stats were updated. It won't tell you if that number is too many.
The only real way to know if the stats need updating is to see if queries are using bad plans and hence running slower than usual. To know that you need to know your system well and know how it normally performs so that you can see when performance is worse than normal
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
November 6, 2008 at 11:51 am
Thanks gila - i do some monitoring. I turned in on now and keep eye on the query being executed to ensure they are using the indexes and not the full scans.
November 6, 2008 at 1:44 pm
TRACEY (11/6/2008)
Thanks gila - i do some monitoring. I turned in on now and keep eye on the query being executed to ensure they are using the indexes and not the full scans.
Actually, quite often the problem is the other way around. The queries are doing index seeks and bookmark lookups because the optimiser thinks there are very few qualifying rows. However there are lots and lots of qualifying rows and the scan would be cheaper than all the lookups.
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
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply