In our previous blog post, we talked about the general approach to tuning up SQL Serve performance. In this particular blog post, we will show how we improved the speed of capturing the existing statistics.
The challenge
One challenge, in particular, dealt with capturing the existing statistics in the form of a STATS_STREAM and save it into a usable ALTER STATISTICS statement. The STATS_STREAM returned by running the following statement is in binary format:
WITH STATS_STREAM,NO_INFOMSGS
In order to save the STATS_STREAM with the appropriate ALTER STATISTICS statement requires converting the STATS_STREAM to a varchar format.
The built-in function
can run for several minutes converting a single value to hexstr. We utilized a method blogged by Dan Holmes to convert these values using a more predictable method, bring the elapsed time to sub-second.
Using Dan’s method we can store ALTER STATISTICS … WITH STATS_STREAM = ‘…’ for over 2,000 statistics in less than a minute, versus over 50 minutes by using the built-in function master.dbo.fn_varbintohexstr(variable or column name)
The solution
We created the following procedure to update statistics:
@run_mode VARCHAR(100) = 'none',
@stat_update-TYPE INT = 3,
@stat_update_pct INT = 30,
@stat_update_custom INT = 3,
@stat_update_custom_dynamic INT = 500000,
@small_table_rows INT = 1000000,
@drop_binary_stat bit = 1,
@WithNewRowsOnly bit = 0,
@CreateRollbackStmt bit = 1,
@stat_update_groups tinyint = 7,
@stat_groups_schedule VARCHAR(200) = '0,0,0,0,0,0,0,0',
@help bit = 0
AS
BEGIN
This resulted in a significant performance increase (we need to place more precise information here).
