SQL Server – Statistics – Fake Stats

Background

Quick follow-up from our last post.

Referenced Post

SQL Server – Clone Database – Schema & Statistics
Link

Statistics

Statistics helps SQL Server pick the most efficient query plan.

Alternatives

If we cannot :-

  1. Restore Database
    • Cannot, due to
      • Size
      • Data Sensitivity
  2. DBCC Clone Database
    • Cannot, due to
      • Need only a couple of tables
      • SQL Server Version does not support DBCC Cloning
        • To use DBCC Cloning, SQL Server has to be v2012 or above
  3. SQL Server Management Studio ( SSMS ) / Script Statistics
    • Cannot, due to
      • SQL Server Version does not Statistics reverse engineering

Create Artificial Statistics

Outline

  1. Source Instance
    • Generate Schema
      • Determine table level statistics
        • Number of Records
        • page Consumption
  2. Destination Instance
    • Replay Schema
    • Apply Table Statistics

Source Instance

Generate Schema

Using Sql Server Management Studio ( SSMS), Generate Table Schema, Indexes, and Constraints

Determine Table Statistics

Number of Records
SQL

select
        [table]
            = quotename(tblSS.name)
              + '.'
              + quoteName(tblSO.name)

        , [rowCount]
            = tblSP.[rows]

from   sys.schemas tblSS

inner join sys.tables tblSO

    on tblSS.schema_id = tblSO.schema_id

inner join sys.indexes tblSI

    on tblSO.object_id = tblSI.object_id

inner join sys.partitions tblSP

    on  tblSI.object_id = tblSP.object_id

    and tblSI.index_id = tblSP.index_id

where  tblSO.[type] = 'U'

and     tblSI.index_id in (0,1)

order by
        [table] asc

Image

table.rowCount.20181115.1109AM.PNG

Number of Records
SQL
--use [tempdb]
go

select
        [table]
            = quotename(tblSS.name)
              + '.'
              + quoteName(tblSO.name)

        , [rowCount]
            = max(tblSP.[rows])

        , [reservedPageCount]
            = sum(tblDPS.reserved_page_count)

from   sys.schemas tblSS

inner join sys.tables tblSO

    on tblSS.schema_id = tblSO.schema_id

inner join sys.indexes tblSI

    on tblSO.object_id = tblSI.object_id

inner join sys.partitions tblSP

    on  tblSI.object_id = tblSP.object_id

    and tblSI.index_id = tblSP.index_id

inner join sys.dm_db_partition_stats tblDPS

    on  tblSI.object_id = tblDPS.object_id

    and tblSI.index_id = tblDPS.index_id

where  tblSO.[type] = 'U'

/*

    and     tblSI.index_id in (0,1)

*/

group by

          tblSI.object_id

        , quotename(tblSS.name)

        , quoteName(tblSO.name)

order by
        [table] asc
<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>
Image

sys.dm_db_partition_stats.20181115.1252PM

Destination Instance

Replay Schema

Using Sql Server Management Studio ( SSMS), replay generated Table Schema, Indexes, and Constraints.

Set Table Statistics

SQL
Syntax
update statistics [schema].[table] with rowcount = [rowCount]<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>
Sample
update statistics Dimension.Date with rowcount = 500000

Lab

Metadata

Table Statistics

Here is table statistics once the fake stats are applied

table.properties.20181115.11239AM.PNG

Query Plan

Query Plan – Original

queryPlan.Original.20181115.1132AM

Query Plan – Revised

queryPlan.Revised.20181115.1130AM.PNG

Explanation

Faking Statistics gives us a more realistic view of how the query will execute against live data.

One thought on “SQL Server – Statistics – Fake Stats

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s