Forum Discussion

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    I would register an app and then go with PowerShell cmdlets.  By the way you need DMV code, not SQL.

    • dd8zc's avatar
      dd8zc
      Helper I
      4 years ago

      Thanks for the answer, lbendlin, I will check this possibility. 

       

      Why should I use DMV, whats the advantage? The linked article mentioned this SQL code:

      SELECT * FROM SYSTEMRESTRICTSCHEMA
($System.DISCOVER_STORAGE_TABLE_COLUMNS,
 [DATABASE_NAME] = '<Dataset Name>') //Sum DICTIONARY_SIZE (bytes)

SELECT * FROM SYSTEMRESTRICTSCHEMA
($System.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS,
 [DATABASE_NAME] = '<Dataset Name>') //Sum USED_SIZE (bytes)

       

      • lbendlin's avatar
        lbendlin
        Super User
        4 years ago

        There is no choice, you have to use DMV. It is a very limited subset of SQL.

         

        Dynamic Management Views (DMVs) in Analysis Services | Microsoft Docs

         

        The first query has no size information. The second one can be used as

         

        SELECT DIMENSION_NAME, USED_SIZE FROM SYSTEMRESTRICTSCHEMA
        ($System.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS,
        [DATABASE_NAME] = '<dataset name>')

         

        but then you have to do the aggregations yourself. DMV doesn't support any aggregations.