Backup your databases... where?

Backing up my databases locally has been in my build list for a very long time.  Why?  Well, primarily for manageability.  I had to know that my backups were always good, and I did not want to introduce any other factors, such as network queues or interruptions.  I always told myself to back them up local first, and go from there.

Today I ran into a blog that suggested it may be time to revisit this practice. 

First and foremost, what are the backups for?  Recovery.  Aren't they?  If I backup the databases locally, and my server goes down, don't I need to need to recover the hardware first, before I can get to my database .bak file(s).  I am almost embarrassed to admit I have not looked at it from this perspective before. 

What about moving data around production networks intraday?  I don't know about you, but I have worked on some very secure AND controlled networks.  In many cases, it was often frowned upon to move backup files from production to DEV/QA intraday.  Not only the network, but the production resource itself!  During certain hours, these machines are locked down.  It would be much easier to access last night's backup, if it was written to a network share, rather than the production server.

There are a few other reasons, but I believe these two are the most pointed, obvious reasons to consider writing your backups to a network share, rather than the local disk. 

Where is SQL Server 2012 Books Online?

I have run quite a few v2012 installations recently, and I wanted to post a couple of quick notes for my readers.  The installation is still rather lengthy, but it is all pretty straightforward.  The features do appear differently throughout the install, in comparison to v2008.  You will see those differences noted here:       http://technet.microsoft.com/en-us/library/bb500459.aspx

 

One notable difference --  Books Online is gone!  If you've managed SQL Server for as long as I have, you may also find this a little odd.  In short, Microsoft has moved to a new Help technology.  The documentation is no longer included in the installation, and it must be either viewed online, or downloaded as a local help collection.   
    http://technet.microsoft.com/en-us/library/ms166020.aspx 

 

Fortunately, you can still install BOL as you've used it before, as written here by Kumar Vivek: 

    http://blogs.msdn.com/b/kvivek/archive/2012/03/10/download-books-online-product-documentation-for-sql-server-2012.aspx

 

Lastly, this topic describes the differences of v2012 at the level of each component, such as the installation, SSAS, SSIS, SSRS, etc.  

    http://technet.microsoft.com/en-us/library/bb500435.aspx

SQL Server 2012 Upgrade Advisor

This is just a short one, but if you're moving to SQL v2012, I strongly encourage you to take a look at the Upgrade Advisor (UA).  I believe the UA to be largely improved from previous releases, but this is just my opinion.  Regardless, if you're performing an upgrade, I encourage you to check your environment for conflicts ahead of time.  There may be deprecations, or other changes that will cause breakage, and you want to know about these beforehand.  Where is the Upgrade Advisor ?

 

It is in the SQL v 2012 Feature Pack.  See here: 

   http://www.microsoft.com/en-us/download/details.aspx?id=29065

See this topic for much more information regarding the UA, and the different paths you can take to the v2012 engine:   http://technet.microsoft.com/en-us/library/bb677622.aspx

Deprecated SQL Server 2008 commands

Run a lot of your normal commands within your new SQL Server v2012 instances, and you're going to see a lot of this:

                    Msg 2812, Level 16, State 62, Line 1
                    Could not find stored procedure ' xxxxxxxx '.

This is simply due to the code deprecations... that we should have read about ahead of time.   :-) 
There are many different deprecations, but these are a few examples of sp_dboption, old and new:

  OLD:  
      EXEC sp_dboption dbname, 'auto create statistics', true
      EXEC sp_dboption dbname, 'auto update statistics', true
      EXEC sp_dboption dbname, 'read only', true

  NEW:
      ALTER DATABASE dbname SET AUTO_CREATE_STATISTICS ON
      ALTER DATABASE dbname SET AUTO_UPDATE_STATISTICS ON
      ALTER DATABASE dbname SET READ_ONLY

This is a much more detailed reference of the SQL Server 2008 deprecations from Microsoft:
     http://msdn.microsoft.com/en-us/library/ms143729(v=sql.100).aspx

Drop all tables within your database

First, let me say that you need to be sure you want to do this.  I've been working SQL Server for longer than I care to admit, and I know, there will come a time when you'll need to do this.

Here is the quick and dirty, using the undocumented sp_MSForEachTable:

  USE YourDatabase

  EXEC sp_MSForEachTable @command1 = "DROP TABLE ?" 

 

As I said, that is an undocumented procedure, and it really is best to avoid using those procedures in production.  Here is another method to do the same:

  --DROP ALL TABLES 
  USE YourDatabaseName
  GO
  
    DECLARE @TableName VARCHAR(150)
    DECLARE @SQL VARCHAR(300)

    SELECT @TableName = (
        SELECT TOP 1 [name] 
        FROM sys.objects 
        WHERE [type] = 'U' 
        ORDER BY [name] )

    WHILE @TableName IS NOT NULL
    BEGIN
        SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@TableName) +']'
        EXEC (@SQL)
        PRINT 'Dropped Table : ' + @TableName
        SELECT @TableName = (
          SELECT TOP 1 [name] 
          FROM sys.objects 
          WHERE [type] = 'U' 
          AND [name] > @TableName 
          ORDER BY [name] )
    END

Find all referencing columns, or dependencies

What happens if you are changing a column (or columns) in a table that is referenced elsewhere within the database?  How do you find the associated dependencies, such that they can be modified as well?

/* Using INFORMATION_SCHEMA CONSTRAINT_COLUMN_USAGE and REFERENCTIAL_CONSTRAINTS. */

   SELECT
      r.TABLE_NAME [Table],
      r.COLUMN_NAME [Column]
   FROM
      INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u INNER JOIN         
         INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS fk
        ON u.CONSTRAINT_CATALOG = fk.UNIQUE_CONSTRAINT_CATALOG
        AND u.CONSTRAINT_SCHEMA = fk.UNIQUE_CONSTRAINT_SCHEMA
        AND u.CONSTRAINT_NAME = fk.UNIQUE_CONSTRAINT_NAME INNER JOIN 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE r
      ON fk.CONSTRAINT_CATALOG = r.CONSTRAINT_CATALOG
      AND fk.CONSTRAINT_SCHEMA = r.CONSTRAINT_SCHEMA
      AND fk.CONSTRAINT_NAME = r.CONSTRAINT_NAME
   WHERE 
     u.TABLE_NAME = 'YourTable'
     AND u.COLUMN_NAME = 'YourColumn'

Or, maybe you're changing a procedure, and you need to find all of the underlying dependencies (aka, tables) that are referenced by that stored procedure?   Try this:

   SELECT
       referenced_schema_name,
       referenced_entity_name,
       referenced_minor_name
   FROM
       sys.dm_sql_referenced_entities ('dbo.YourProcedureName','OBJECT')

List all table constraints, by table

This query will return  your table constraints, ordered by table: 

SELECT

     so.[Name] [ContraintName],
     tab.[Name] [Table],
     scol.[Name] [Column]
FROM
     sysobjects so INNER JOIN (
          SELECT [Name],[ID]
          FROM sysobjects
          WHERE XType = 'U'
    ) tab
       ON so.[Parent_Obj] = tab.[ID] INNER JOIN sysconstraints sc
         ON so.[ID] = sc.Constid INNER JOIN syscolumns scol
           ON sc.[ColID] = scol.[ColID]
           AND tab.[ID] = scol.[ID]
ORDER BY
     tab.[Name]

Using sys.dm_db_index_operational_stats

In addition to sys.dm_db_index_usage_stats, I also encourage you to take a look at sys.dm_db_index_operational_stats.  This DMO (dynamic management object) can be used to return current low-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.  I have used it to help me determine which indices are used, as well as HOW they are used.  By that, I mean whether you are scanning the index, or seeking, and even which tables said index is targeting.  Basically, this is one of the many tools that you can use to assess your index utilization. 

See BOL:  http://technet.microsoft.com/en-us/library/ms174281(v=sql.105).aspx

This query will return the number of inserts, updates and delete operations against all indices in the targeted database:

SELECT

    DB_NAME(database_id) [Database],
    ios.object_id [ObjectID],
    QUOTENAME(OBJECT_SCHEMA_NAME(ios.object_id,database_id)) + N'.'+     
       QUOTENAME(OBJECT_NAME(ios.object_id, database_id)) [ObjectName],
    i.index_id [IndexID],
    i.name [IndexName],
    i.fill_factor [FillFactor],
    ios.partition_number [PartitionNumber],
    CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END + i.[type_desc] [IndexType],
    ios.leaf_insert_count [LeafInserts],
    ios.leaf_update_count [LeafUpdates],
    ios.leaf_delete_count [LeafDeletes],
FROM
    sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL) ios INNER JOIN sys.indexes i
      ON ios.object_id = i.object_id   
      AND ios.index_id = i.index_id
ORDER BY
    ios.page_latch_wait_count + ios.page_io_latch_wait_count DESC

This query will return the TOP 10 objects with waits on page locks:

SELECT

    TOP 10 OBJECT_NAME(os.object_id, os.database_id) [ObjectName],
    os.index_id [IndexID],
    partition_number [PartitionNumber],
        page_lock_wait_count [PageLockWaitCount],
        page_lock_wait_in_ms [PageLockWaitMS],
    CASE WHEN mid.database_id IS NULL THEN 'N' ELSE 'Y' END [MissingIndex]
FROM
    sys.dm_db_index_operational_stats(db_id(),NULL,NULL,NULL) os
LEFT OUTER JOIN
          (SELECT DISTINCT database_id, object_id
                      FROM sys.dm_db_missing_index_details id ) id
             ON os.database_id = id.database_id
             AND os.object_id = id.object_id
ORDER BY
   page_lock_wait_count DESC
  

Are your indexes being used?

Too many indexes are just as bad as too few.  The ones we create really should read the data quickly, with as little I/O as possible.  I have mentioned before how much I love the DMV's -- Dynamic Management Views  -- introduced in v2005, and expanded upon in v2008.  You can use the sys.dm_db_index_usage_stats DMV to review your user seeks, scans and lookups.  Very helpful to see which indices are being used, and which ones aren't. 

This is a very good overview from BOL: 
    http://technet.microsoft.com/en-us/library/ms188917(v=sql.105).aspx

This is example script uses the sys.dm_db_index_usage_stats DMV, along with sys.objects and sys.indexes, to report which indexes have not been used:

  SELECT   
      OBJECTNAME = OBJECT_NAME(i.OBJECT_ID),
      INDEXNAME = i.NAME,
      i.INDEX_ID
  FROM     
      SYS.INDEXES I INNER JOIN SYS.OBJECTS o

        ON i.OBJECT_ID = o.OBJECT_ID

  WHERE 

      OBJECTPROPERTY(o.OBJECT_ID, 'IsUserTable') = 1

      AND i.INDEX_ID NOT IN (

            SELECT s.INDEX_ID

            FROM SYS.DM_DB_INDEX_USAGE_STATS s

            WHERE s.OBJECT_ID = i.OBJECT_ID

            AND i.INDEX_ID = s.INDEX_ID

       )

  ORDER BY

      OBJECTNAME,

      i.INDEX_ID,

      INDEXNAME ASC

 

This example script lists each index used within a query execution, ordering by those that have been scanned the most.  Take a look at the seeks, scans, lookups and user reads/writes.  This is very helpful output when you are trying to determine which indices are needed, and which potentially could be removed. 

  SELECT
      OBJECT_NAME(ius.[object_id], ius.database_id) [ObjectName],
      ius.index_id,
      ius.user_seeks,
      ius.user_scans,
      ius.user_lookups,
      ius.user_seeks + ius.user_scans + ius.user_lookups [UserReads],
      ius.user_updates [UserWrites],
      ius.last_user_scan,
      ius.last_user_update
  FROM
      sys.dm_db_index_usage_stats ius
  WHERE 
      ius.database_id > 4     –- TO EXCLUDE SYSTEM TABLES
      AND OBJECTPROPERTY(ius.OBJECT_ID, 'IsUserTable') = 1
      AND ius.index_id > 0  -- TO EXCLUDE HEAPS
  ORDER BY
      ius.user_scans DESC


Please remember, collections from the DMVs are dynamic.  They will be reset any time your SQL Server service is restarted.  I often use this DMV as I am reviewing my index strategies.  Because it is dynamic, I also find it useful to schedule a collection from this DMV on a recurring basis. Maybe weekly, or monthly... just depending upon the activity you are reviewing, and how many times the index in question may be executed.  This provides a nice set of statistics to measure.  Again, very helpful for troubleshooting performance, and reviewing your index strategies.
 

To Cursor or not to Cursor. That is the question.

There are many different opinions out there regarding cursor use.  Many places where they simply should NOT be used, and many places where they are the only thing that will work.  Personally, I try to avoid them at all costs.  If there is a SET based option, I will find it.  :-)    To be a little more tech savvy, I will say that it is not uncommon for cursors to be notably slower than any set based operation.  In addition to the runtime, they can also introduce table-level locking.  In my book, cursors should only be used when you truly need to access only one record at a time, and when it cannot be handled differently. 

That is what I am going to sample here -- what different methods can be used as an alternative to the cursors?  We can perform the cursor-ing without actually using a cursor.  I am using AdventureWorks v2008:

/* WHILE LOOP w/TABLE VARIABLE METHOD, INSTEAD OF A CURSOR 

   Using AdventureWorks v2008    */




  SET NOCOUNT ON;

  GO

  DECLARE @data TABLE(ID INT IDENTITY PRIMARY KEY CLUSTERED,

                     SalesOrderID INT NOT NULL)

  DECLARE

      @i INT = 1,

    @ii INT,

    @SalesOrderID INT,

    @LineCount INT;

   

    INSERT @data (SalesOrderID)

    SELECT SalesOrderID

    FROM Sales.SalesOrderHeader

    WHERE OnlineOrderFlag = 1;

  

    SET @ii = @@ROWCOUNT;

    WHILE @i <= @ii

    BEGIN

        SET @SalesOrderID = (SELECT SalesOrderID FROM @data WHERE ID = @i);

         SET @LineCount = ISNULL(@LineCount, 0) + ISNULL((

                SELECT  COUNT(*)

                FROM  Sales.SalesOrderDetail

                WHERE SalesOrderID = @SalesOrderID

                ),0);

   

        SET @i += 1;

    END

    IF @LineCount <> 60398

    BEGIN

        RAISERROR('Error',16,1);

        PRINT @LineCount;

    END

  GO




  SET NOCOUNT OFF;

       

 

Here is another example, performing a DELETE without a cursor:

   /* DELETE WITHOUT A CURSOR */

    DELETE x

    FROM dbo.YourTable x INNER JOIN (

        SELECT y.tdate, y.symbol, MAX(inserted)[Inserted]

        FROM dbo.YourTable y

        GROUP BY y.tdate, y.symbol) z

      ON x.tdate = z.tdate

      AND x.symbol = z.symbol

      AND x.inserted <> z.inserted

We can also use a CTE rather than a cursor, as well.  This is just a simple CTE method for returning the data with the oldest TradeDate without cursoring through each record:

 

/* CREATE TABLE */ 

 

  CREATE TABLE dbo.TraderOrders
  (
      OrderDate DATETIME,
      TradingGroup CHAR(10),
      TraderID INT
  );

 

  /* INSERT SAMPLE DATA */
  INSERT dbo.TraderOrders VALUES ('2012-05-24 11:06:28.080','EquityDesk','32');
  INSERT dbo.TraderOrders VALUES ('2012-05-24 11:06:28.080','FuturesDesk','32');
  INSERT dbo.TraderOrders VALUES ('2012-06-08 13:30:14.130','FuturesDesk','11');
  INSERT dbo.TraderOrders VALUES ('2012-06-08 13:30:14.313','EquityDesk','11');
  INSERT dbo.TraderOrders VALUES ('2012-06-08 13:30:13.840','FuturesDesk','11');
  INSERT dbo.TraderOrders VALUES ('2012-06-08 13:30:13.450','EquityDesk','11');
  INSERT dbo.TraderOrders VALUES ('2012-06-08 13:30:13.050','FuturesDesk','11');
  INSERT dbo.TraderOrders VALUES ('2013-05-07 10:45:06.800','CashDesk','118');
  INSERT dbo.TraderOrders VALUES ('2013-05-07 10:45:04.133','CashDesk','118');
  INSERT dbo.TraderOrders VALUES ('2013-10-15 07:30:16.193','CashDesk','118');
  INSERT dbo.TraderOrders VALUES ('2013-01-26 13:51:43.923','CashDesk','118');
  INSERT dbo.TraderOrders VALUES ('2013-01-26 13:51:43.923','CashDesk','118');
  INSERT dbo.TraderOrders VALUES ('2013-01-26 13:51:44.427','CashDesk','118');
  INSERT dbo.TraderOrders VALUES ('2013-01-25 12:40:39.737','CashDesk','2');
  INSERT dbo.TraderOrders VALUES ('2013-01-25 12:40:40.890','CashDesk','2');
  INSERT dbo.TraderOrders VALUES ('2013-01-25 12:40:41.627','CashDesk','2');
  INSERT dbo.TraderOrders VALUES ('2013-01-25 12:40:43.277','CashDesk','2');
  INSERT dbo.TraderOrders VALUES ('2013-01-25 14:29:08.360','CashDesk','2');
  INSERT dbo.TraderOrders VALUES ('2013-01-21 19:36:34.660','CashDesk','101');
  INSERT dbo.TraderOrders VALUES ('2013-01-21 19:36:34.843','CashDesk','101');
  INSERT dbo.TraderOrders VALUES ('2013-01-21 19:36:35.013','CashDesk','101');
  INSERT dbo.TraderOrders VALUES ('2013-01-21 22:27:24.317','CashDesk','13');
  INSERT dbo.TraderOrders VALUES ('2013-01-21 22:31:21.443','FuturesDesk','13');
  INSERT dbo.TraderOrders VALUES ('2013-01-22 19:44:28.880','CashDesk','13');

 

  /* RETURN OLDEST TradeDate DATA per TraderID    */

   WITH CTE (OldestTradeData, TraderID)
   AS
   (
      SELECT
             MIN(TradeDate),
             TraderID
      FROM
             dbo.TraderOrders
      GROUP BY
             TraderID
    )

 

   SELECT DISTINCT to.*
   FROM
         dbo.TraderOrders to INNER JOIN CTE
           ON (CTE.OldestTradeData = to.TradeDate
           AND CTE.TraderID = to.TraderID
           AND to.VenueCode = (
                   SELECT TOP 1 TradingGroup
                    FROM
                         dbo.TraderOrders to2
                    WHERE
                         to2.TradeDate = to.TradeDate
                         AND to2.TraderID = to.TraderID
                  )
         )
  ORDER BY
        to.TraderID;