Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

statistic on varchar(max) column Expand / Collapse
Author
Message
Posted Saturday, December 7, 2013 1:29 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 18, 2014 11:11 AM
Points: 596, Visits: 1,687
Is there any point in keeping auto-generated stats on either varchar(max) or large varchar columns ( e.g. varchar(3000) ) ? The data in these columns is either free-form notes entered by users or xml data )

I'm finding some of our auto-created statistics take a very long time to update with "update statistics." We use fullscan but I'd rather not get into why we do that --- the decision followed extensive testing.

The updates to these stats also coincide with some of the 15-second IO messages in the sql log ( tempdb ). Some of these stats are on "note" type fields with lots of free-form text and one other one includes about 15 columns ( that one was a database tuning advisor creation ).

I noticed that the histogram steps in some of these stats are either empty, or a pretty messy collection of free-form text data.



Post #1520839
Posted Monday, December 9, 2013 8:49 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 18, 2014 11:11 AM
Points: 596, Visits: 1,687
I've decided to exclude 3 specific statistics from our stats maintenance jobs -- these almost always cause the 15-second IO messages and take a long time to update at fullscan. They may be auto-updated from time-to-time.

Removing them practically requires an act of Congress, so I may hold off on that effort for a while.

I confirmed that when running update Statistics on one of these with no sample percentage specified, the sampling rate was .2 %

Records sampled: 604275
Record count: 231,741,201



Post #1521147
Posted Tuesday, December 10, 2013 12:30 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 2,929, Visits: 2,946
Do you use those varchar(max) (or other heavy types) columns in WHERE conditions?
Do you have Full Text search implemented?




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1521646
Posted Tuesday, December 10, 2013 1:39 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 18, 2014 11:11 AM
Points: 596, Visits: 1,687
I doubt the varchar(max) and other long varchar columns are used in where clauses, except rarely on an adhoc basis when somebody was doing research ( which is probably when the stat was auto-created ). We do have two columns under full-text catalogs -- and I was wondering about the use of statistics on those columns.




Post #1521660
Posted Tuesday, December 10, 2013 2:53 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, August 17, 2014 8:42 PM
Points: 463, Visits: 1,030
If you are using them in the where clauses then the stats will auto created and you might know it already and you really do not have control on it.

From what I see either you don't want to update those statistics or change the sampling rate.

1. For changing the sampling rate : Check Trace flag 2371
2. If you decide not to update those stats then add a step in the job to drop those stats and it won't update during that window, how ever will get recreated if you run those queries again.
DROP STATISTICS table.statistics_name



--SQLFRNDZ
Post #1521683
Posted Tuesday, December 10, 2013 4:45 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 18, 2014 11:11 AM
Points: 596, Visits: 1,687
Since sql re-creates those _wa_sys stats using hexidecimal for the table's object id and the column's object id, it seems to always use the same same, so I just excluded those named statistics from my custom job.
We're now using update stats with fullscan because we found that it runs multi-threaded, unlike lesser percentages. Plus, for our database, the only way to beat the speed of running fullscan was to drop below 30% sample rate which produces poor stats for our application.



Post #1521713
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse