Thursday, January 29, 2015

SQL query to return multiple SUMs in a single line result

Another good question from one of my customers today --

"... how to take two or more records and summarize them into one record. "  


I can't tell you how many times I've done this when reporting traded volumes.  Daily, weekly or monthly, grouped by symbol, or trader, or even trading desk.... This new customer's data is a little different, but the approach is the same.  This is just a quick post, providing a method to bring your data counts back in single line result-sets.

USE DBA; -- change to your dbname

DECLARE @input TABLE (UserID INT, FiscalYear INT, GiftAmt DECIMAL(5,2))
INSERT @input (UserID,FiscalYear,GiftAmt)
VALUES (1,2012,100.00),
  (1,2012,50.00),
  (1,2013,110.00),
  (1,2014,50.00),
  (1,2014,50.00),
  (1,2014,75.00),
  (2,2012,65.00),
  (2,2014,55.00),
  (2,2014,55.00),
  (2,2014,75.00),
  (3,2012,10.00),
  (5,2011,20.00),
  (5,2013,55.00),
  (5,2014,55.00);

-- Look at your data, see you've got 14 records
-- SELECT * FROM @input

-- Now bring it back out in 4 lines, one for each UserID
SELECT 
UserID,
   ISNULL(SUM(FY1Amt),0.00[FY1Amt],
   ISNULL(SUM(FY2Amt),0.00) [FY2Amt],
   ISNULL(SUM(FY3Amt),0.00) [FY3Amt],
   ISNULL(SUM(FY4Amt),0.00) [FY4Amt]
FROM
(
SELECT 
UserID,
FiscalYear,
CASE WHEN FiscalYear = 2011 THEN SUM(GiftAmt) END [FY1Amt],
CASE WHEN FiscalYear = 2012 THEN SUM(GiftAmt) END [FY2Amt],
CASE WHEN FiscalYear = 2013 THEN SUM(GiftAmt) END [FY3Amt],
CASE WHEN FiscalYear = 2014 THEN SUM(GiftAmt) END [FY4Amt]
FROM
@input a
GROUP BY
UserID,
FiscalYear 
) x
GROUP BY 
x.UserID

Your results will look like this:

   UserID   FY1Amt  FY2Amt  FY3Amt    FY4Amt
      1          0.00       150.00     110.00       175.00
      2          0.00         65.00       0.00       185.00
      3          0.00        10.00        0.00          0.00
      5        20.00          0.00      55.00         55.00

Now that I think about it, I believe I did a similar post once before, for the single-line result sets. A different method, maybe a bit older.. but the single line output is achieved the same.  If you haven't seen that one already, take a look at it here.

I will warn as I did on that first post.  SQL Server is used to store and manage the data, not to make it pretty. There are many very clever things you can do to manipulate your output, but you've got to be wary of the performance implications. Here's a little information from MSFT on the same thing.  See the the 'Logic separation' section, about keeping the business logic separate from the data manipulation.


Wednesday, January 28, 2015

SQL Server -- Daily DBA Checklist

" If you were running a DB area - What key items would you check / monitor every morning? "

I received that question from one of my customers recently.  A very good question, in my opinion, and one that should be asked of any potential DBA candidate.   

Best case, the DBA would have automated checks in place that run 24/7, proactively notifying, or alerting of any problems BEFORE they occur.  There are many different was to define these type of alerts - SQL Server Agent, SMO, Windows, WMI, and more.  Regardless, if an automated routine is in place, the daily DBA checklist becomes fairly minimal.  One cannot, however, just assume the alerts are running… and the checklist still exists.  I've provided three different groups of checks.  

Group 1    
This 1st group must be done every day.  Each of these items are perfectly alert-able, but the alerts may not be setup yet, so I suggest that each of these checks must occur daily, via automated routine or manually: 
        
          Backups
               a. All backups completed successfully?
               b. Capacity concerns on backup drive?
               c. Backup durations very long?
         Security
               a. Review SQL Server log AND Windows Security Event Log
        Capacity
               a. Data and Log file space -- file sizes, growth?
               b. Disk space - ALL drives -- available disk space?
        
        OS Event Logs/SQL Server Logs
               a. Review for any abnormal events
        Scheduled jobs - (Agent, Windows Scheduled tasks, etc.)
               a. Confirm all scheduled jobs ran successfully
               b. Even check job step duration, make sure nothing is taking too long


Group 2
This 2nd group is also extremely valuable, but it only provides an immediate state, UNLESS the DBA has setup an automated collection.  This can be done using the Data Collector, to capture statistics for Server Activity, Disk Usage and Query Statistics.  These collections can be run every day, creating moving metrics that grow and change as your business activity does.  When certain events or conditions are detected, the DBA can be notified/alerted.  This is not available out of the box with SQL Server.  It’s got to be setup, and managed regularly (see here).  If it is not in place, the checking should still occur, at least on a weekly basis.  Even just the current state is very valuable.

               Review blocking issues
                   a. SQL Server performance counters
              Review table/index fragmentation statistics
                   a. See my query here, using sys.dm_db_index_physical_stats, sys.indexes
              Review performance statistics
                   a. See sp_blitz from Brent Ozar’s team


Group 3
Lastly, this check should be done at least once monthly.  It can be completely automated, but given that it only occurs monthly, the manual invocation is acceptable, too.  Don't just look at the success/fail status of the backup job.  You've got to restore a .bak file and confirm the integrity of the data.  The last thing you want is to be doing an audit, and find your .bak files are unusable.

Restore a backup at least weekly, to validate backup solution

One last thing that I would note is that if the DBA is logging ALL changes that are made everywhere, as is recommended, then he/she should take time to review these metrics on a regular basis, too.  One server or 100, one DBA or a Team -- it's just a very good thing to keep an eye on.

Wednesday, January 21, 2015

When was your SQL Server database last restored?

Just a real quick post... I took on a new project very recently, and I was doing an assessment of their farm.  Last DBCC's, last backups... even last restores.   I had several different statements, but I returned multiple restore dates, when the database data and log file were restored in separate operations.  This statement takes that into account, and just gives you back a single entry, for the most recent restore date for your databases.  It also tells you from what file they were restored.  Check it out.  Let me know what you think.

  /*  
When was your database last restored? 
From what file?                                                              */

     WITH LastRestored AS
     (
    SELECT
            rh.destination_database_name [DatabaseName],
            rh.restore_date [Restored],
            bmf.physical_device_name [SourceFile],
            RowNum = ROW_NUMBER() OVER (PARTITION BY bs.database_name 
                  ORDER BY rh.restore_date DESC )
     FROM
           msdb..restorehistory rh INNER JOIN msdb..backupset bs 
               ON rh.backup_set_id = bs.backup_set_id INNER JOIN msdb..backupmediafamily bmf 
   ON bs.media_set_id = bmf.media_set_id
     )
     SELECT DatabaseName,Restored,SourceFile
     FROM LastRestored
     WHERE RowNum = 1


Your results will look a little something like this:

   DatabaseName                Restored                               SourceFile
   AdventureWorks2012      2015-01-20 21:59:41.130     C:\Backups\AdventureWorks2012_Backup.bak
   ReportServer              2015-01-20 11:39:33.853     C:\SSRS\ReportServerLog.bak
   ReportServerTempDB      2015-01-20 11:39:43.433     C:\SSRS\ReportServerTempDBLog.bak


Here are a couple of references with greater detail on the tables I am targeting:

RestoreHistory                 https://msdn.microsoft.com/en-us/library/ms187408(v=sql.110).aspx
BackupSet                       https://msdn.microsoft.com/en-us/library/ms186299.aspx
BackupMediaFamily     https://msdn.microsoft.com/en-us/library/ms190284(v=sql.110).aspx


Friday, January 2, 2015

Find all user-created objects in your system databases

System databases are for system objects.  Pretty simple.  If user objects are created within the system databases, then we also need to include these databases in our DR plans.  For this and countless other reasons, it really is not a good practice to allow user-defined objects within your SQL Server system databases.

In this post I've just given a couple of quick statements you can use to identify user created objects in any of your system databases.


/*  Use this to find any user-defined objects in the system databases, if exists.  
    As-is, it targets the master database, but works the same in any system db.
To target model or msdb, change the database name in the USE line.            */

USE master;  -- << use master, model or msdb
SELECT
o.name [ObjectName],
        s.name [Schema/Owner],
o.type_desc [ObjectType]
FROM
sys.all_objects o INNER JOIN sys.schemas s
          ON o.schema_id = s.schema_id LEFT OUTER JOIN sys.database_principals po
            ON o.principal_id = po.principal_id LEFT OUTER JOIN sys.database_principals ps
              ON s.principal_id = ps.principal_ID
WHERE
o.is_ms_shipped <> 1; -- 'is_ms_shipped' means it was shipped by MSFT on the cd.  
      -- If is_ms_shipped = 0, that means it is user-created.


In cases of upgraded databases, or database diagramming, I have heard that is_ms_shipped is not always 100% reliable.  I have never actually seen that, but to be safe, you could use this approach, which is a combination of is_ms_shipped and the 'sys' schema name.

SELECT * FROM sys.objects 
WHERE SCHEMA_NAME(schema_id) <> 'sys'  
AND is_ms_shipped = 0  
AND parent_object_id NOT IN (
SELECT object_id  
FROM sys.objects  
WHERE SCHEMA_NAME(schema_id) = 'sys'  
        OR is_ms_shipped = 1); 


These are additional references to the different system catalog views: