How to dis-aggregate summarized data in SQL Server ?

Now that is just weird to me.  Why would you want to 'dis-aggregate' data? Who wants to look at the details?  If you're not an accountant, I mean... For example, instead of this:

You want to see this:

Turns out it's fairly common to need to see the details behind a summarized report. This is a quick example for dis-aggregating data. 

-- load your summarized data

CREATE TABLE #aggregated (

  [filename] VARCHAR(25),downloads INT,downloaded date,data1 VARCHAR(15),

  data2 VARCHAR(15),data3 VARCHAR(15),processed bit

)

INSERT #aggregated ([filename],downloads,downloaded,data1,data2,data3,processed)

VALUES

       ('filename1.txt',5,'11/15/2015','md1','md2','md3',0),

       ('filename1.txt',79,'12/31/2015','md1','md2','md3',0),

       ('filename1.txt',24,'1/15/2016','md1','md2','md3',0),

       ('filename2.xlsx',49,'10/15/2015','md5','string','string2',0),

       ('filename2.xlsx',25,'2/15/2016','md6','string','string3',0),

       ('filename2.xlsx',39,'12/15/2015','md7','string','string4',0),

       ('filename2.xlsx',8,'1/15/2016','md','string','string5',0);

-- table for the disaggregated details

CREATE TABLE #disaggregated (

  [filename] VARCHAR(25),[action] CHAR(8),downloaded date,data1 VARCHAR(15),

  data2 VARCHAR(15), data3 VARCHAR(15)

)

-- set based approach (rather than cursor)

WHILE EXISTS(SELECT 1 FROM #aggregated WHERE processed = 0)

BEGIN

   DECLARE

      @next INT = (SELECT TOP 1 downloads FROM #aggregated WHERE processed = 0),

      @count INT = 0

      

      WHILE @count < @next

      BEGIN

         INSERT #disaggregated ([filename],[action],downloaded,data1,data2,data3)

         SELECT [filename],'download',downloaded,data1,data2,data3

         FROM #aggregated

         WHERE downloads = @next

         SELECT @count = @count + 1

     END

     UPDATE #aggregated SET processed = 1 WHERE downloads = @next

END

Take a look at the details in your your #disaggregated table:

Or just run this to see that the count of what we just disaggregated corresponds to the numeric 'downloads' value in the aggregated data set.

--  query to show the counts of what was inserted

SELECT a.[filename],a.downloads,count(b.[action]) [details]

FROM #aggregated a INNER JOIN #disaggregated b

  ON a.[filename] = b.[filename]

  AND a.downloaded = b.downloaded

  AND a.data1 = b.data1

  AND a.data2 = b.data2

  AND a.data3 = b.data3

GROUP BY

  a.[filename],

  a.downloads

Should return this:

Hope it helps!  Let me know what you think.

How to use wildcards with the SQL Server LIKE operator

I answered a question today in one of my groups... a whole lot of LIKE operator and wildcard conditions. Great subject matter! This is just a few different tips for using SQL's LIKE operator with wildcards.  Be sure to look at the data you're loading into the variable, and read my comments for each of the sample statements.  

   /*   table variable */

   DECLARE @DBVersions TABLE (

       DatabaseVersion VARCHAR(25) NOT NULL,

       VersionDate DATETIME NOT NULL,

       ModifiedDate DATETIME NOT NULL )

   /*  load some test data  */

   INSERT @DBVersions (DatabaseVersion,VersionDate,ModifiedDate)

   VALUES ('ssAsxb567','2016-01-14','2015-03-14'),

          ('ss%sxb567','2016-01-14','2015-03-14'),

          ('ABXB23','2016-01-14','2015-03-14'),

          ('aBxb234','2016-01-14','2015-03-14');

   /* just look at the raw data  */

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions

   /*

     7 different conditions with wildcard

   #1 return anything that contains OR begins with the letter 'A'

   #2 return anything beginning with 'A', but does not contain it elsewhere

   #3 return anything that contains the letter 'A', but does not begin with it

   #4 return anything LIKE the given DatabaseVersion, but is in ALL CAPS

   #5 return anything that LIKE the given DatabaseVersion, regardless of CASE

   #6 return anything that has 3 digits the given DatabaseVersion

   #7/8 return anything that has the % literally within the DatabaseVersion      */

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions

   WHERE DatabaseVersion LIKE '%A%'

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions

   WHERE DatabaseVersion LIKE 'A%'

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions

   WHERE DatabaseVersion LIKE '%A%'

   AND DatabaseVersion NOT LIKE 'A%'

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions

   WHERE DatabaseVersion LIKE 'ABX%' COLLATE Latin1_General_CS_AS; --only UPPERCASE

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions

   WHERE DatabaseVersion LIKE 'ABX%' -- you'll see we get them both back on this one

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions

   WHERE DatabaseVersion LIKE 'ABX___' -- now only one

 

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions

   WHERE (DatabaseVersion) LIKE '%' -- why do we get them all?!

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions

   WHERE (DatabaseVersion) LIKE '%[%]%' -- use the brackets as an escape character

                                        -- first and last %s are the WILDCARD

                                        -- the middle one is treated as a literal

                                        -- good stuff  

That's all I've got for now, but I may add more later. Take a look at this for more information, and more examples:

    https://msdn.microsoft.com/en-us/library/ms179859(v=sql.110).aspx

"The syspolicy_purge_history SQL Server Agent job may fail in SQL Server 2008"

... and in SQL Server 2012. Doubt I've found a v2012 bug, but I've definitely found the same failure there. My customer's syspolicy_purge_history job failed last night with this error:

A job step received an error at line 1 in a PowerShell script. The corresponding line is '(Get-Item SQLSERVER:\SQLPolicy\CLUSNOD3\VISION).EraseSystemHealthPhantomRecords()'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Failed to connect to server .
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I'll bet the server wasn't found because 'CLUSNOD3\VISION' does not exist. This occurred on SQLPROD\VISION, where 'CLUSNOD3' is the name of one of the cluster nodes. See Step #6 in the 1st method of correction suggested by MSFT in this Kb:     https://support.microsoft.com/en-us/kb/955726

Easy fix. Just "replace the computer node name by using the virtual server name for the cluster instance".  Ok.  But how do you know for sure what the virtual server name is? Run this on the instance where the failure occurred:

     SELECT SERVERPROPERTY('MachineName')     

The 'MachineName' property returns the computer name on which the SQL Server instance is running. If it is a clustered instance, it returns the name of the virtual server.  My result is 'SQLPROD'. 

Exactly as outlined in the 955726 Kb, I edited step #3 of the sysolicy_purge_history job, replacing 'CLUSNODE3' with 'SQLPROD', like this:

   (Get-Item SQLSERVER:\SQLPolicy\SQLPROD\VISION).EraseSystemHealthPhantomRecords()

The job now completes without error.

Not sure how the incorrect servername got in there, but that's another issue entirely... The syspolicy_purge_history Agent job was introduced with v2008 with Policy Based Management.  Take a look at these for details regarding syspolicy_purge_history, and Policy Based Management in general:

   https://msdn.microsoft.com/en-us/library/dd795279.aspx
    https://msdn.microsoft.com/en-us/library/bb510667(v=sql.105).aspx

Determine space used by each SQL Server table

A customer asked me today, 'how much space are all my tables taking?'.  Great question! It is very helpful to know the size of your tables;  not just the rowcount, but the physical space each table is using.  This is a quick query that you can use for just that.  I am JOINing sys.tables, sys.indexes and a couple more catalog views, and returning back the record count and the size of the data and index pages for each of your tables.

WITH spaceUsed

AS (

       SELECT

        o.object_id,

        s.name [SchemaName],

        o.name [TableName],

        CASE MAX(i.index_id) WHEN 1 THEN 'Clustered' ELSE 'Heap' END [IndexType],

        SUM(p.rows) [RecordCount],

        SUM(a.total_pages) [DataPages]

    FROM

        sys.tables o INNER JOIN sys.indexes i

          ON i.object_id = o.object_id INNER JOIN sys.partitions p

            ON p.object_id = o.object_id

            AND p.index_id = i.index_id INNER JOIN sys.allocation_units a

              ON a.container_id = p.partition_id INNER JOIN sys.schemas s

               ON s.schema_id = o.schema_id

    WHERE

        o.type = 'U'

        AND i.index_id IN(0,1)

    GROUP BY

        s.name,

        o.name,

        o.object_id

    ),

IndexPages 

AS (

      SELECT

         o.object_id,

         o.name [TableName],

         SUM(a.total_pages) [IndexPages]

      FROM

         sys.objects o INNER JOIN sys.indexes i

           ON i.object_id = o.object_id INNER JOIN sys.partitions p

              ON p.object_id = o.object_id

AND p.index_id = i.index_id INNER JOIN sys.allocation_units a

                ON a.container_id = p.partition_id

      WHERE

         i.index_id <> 0

      GROUP BY

         o.name,

         o.object_id

   )

     -- return details

     SELECT

         s.SchemaName,

         s.TableName,

         s.IndexType,

         s.RecordCount,

         s.DataPages,

         (s.DataPages * 8) [DataPageSizeKB],

         ISNULL(i.IndexPages, 0) IndexPages,

         (ISNULL(i.IndexPages, 0) * 8) [IndexPageSizeKB]

     FROM

         spaceUsed s LEFT JOIN IndexPages i

           ON s.object_id = i.object_id

     ORDER BY

         s.SchemaName,

         s.TableName

This is the output from AdventureWorks2012 on my own instance:

Nothing too complex. There's probably several other details you could include as well. Take a look at this reference to the each of the catalog views I used, as well as many more:

    https://msdn.microsoft.com/en-us/library/ms189783(v=sql.110).aspx

Can I get the index creation date from SQL Server?

um.... No.  Well, not for ALL of them, anyway.  The sys.indexes catalog view does not include a creation date for the indexes, and sys.objects only stores data for indexes associated with primary key and unique constraints.  So... we do not have a create date for indexes that are not associated with primary or unique constraints.

Take a look at the two queries below;  the 1st one joins sys.objects and sys.indexes, but the date returned is of the table creation, not the index. It may be close, but it's not guaranteed to be the date the index was created.  The 2nd query, however, just takes a look into sys.objects for the create date (crdate) of the index that SQL Server creates for us automatically when any PRIMARY KEY or UNIQUE constraint is created. This one is definitely IS the create date for the indexes associated with each constraint.

-- #1. Not the index create date

USE AdventureWorks2012;

SELECT

    i.name [IndexName],

    o.name [TableName],

    o.create_date [Created] -- this is the table, not the index

FROM

    sys.indexes i INNER JOIN sys.objects o

      ON i.object_id = o.object_id

WHERE

    o.name IN ('BusinessEntityContact','UnitMeasure')

Results:

     

-- #2. crdate for primary/unique constraint indexes

SELECT

    name [IndexName],

    crdate [Created]

FROM

    sys.sysobjects

WHERE

    xtype IN('pk','uq') -- all you need to find pk/uq constraints

    AND name IN

('PK_BusinessEntityContact_BusinessEntityID_PersonID_ContactTypeID','PK_UnitMeasure_UnitMeasureCode')

Result:
    

The AND portion of the WHERE clause in query #2 is only there because I wanted to give you back the same constraints returned in query #1.  If you take a look at the create dates for both, you will see they are definitely different than the date the tables were created.

So... can we get the created date for ALL indexes?  Sure, but not with out-of-the-box SQL Server. You could use a DDL event trigger to audit object creation/modifications. Possibly the topic of a later post....

Until then, please take a look at this reference to sys.indexes.  

    https://msdn.microsoft.com/en-IN/library/ms173760.aspx

Check Transaction Log space used in all databases

Super fast way to check the transaction log usage for all of your databases.  Pretty short and sweet, but it gives you a fast look at how much of your transaction logs are being used, per database. 

    /* quick check on log file usage */

    SELECT

       instance_name [Database],

       [LOG File(s) Size (KB)] [LogFileSizeKB],

       [Log File(s) Used Size (KB)] [LogFileSpaceUsedKB],

       [Percent Log Used] [%LogInUse]

    FROM

    (

       SELECT * FROM sys.dm_os_performance_counters

       WHERE counter_name IN

         ('Log File(s) Size (KB)','Log File(s) Used Size (KB)','Percent Log Used')

       AND instance_name != '_Total'

     ) source pivot (

          MAX(cntr_value)

          FOR counter_name IN   

          ([LOG File(s) Size (KB)],[Log File(s) Used Size (KB)],[Percent Log Used])

     ) p2

This is the output from one of my instances:

Pretty cool.  You can add data file sizes in there, too, or you can capture several other statistics like Lock Waits/sec, Lock Requests/sec, the number of active transactions.  Two of my favorites are the Log Growths and Log Shrinks. Monitoring and being aware of your log growths/shrinks is huge!  Run this, you will see those counters and more:

     SELECT object_name, counter_name, instance_name, cntr_value, cntr_type

     FROM sys.dm_os_performance_counters;

Definitely take a look at this piece from MSDN regarding the other available counters:     https://msdn.microsoft.com/en-us/library/ms187743(v=sql.110).aspx

This is more information about using sys.dm_os_performance_counters to monitor system activity on a whole:   https://technet.microsoft.com/en-us/library/ms190382(v=sql.110).aspx