Monday, December 23, 2013

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: 
 
Lastly, this topic describes the differences of v2012 at the level of each component, such as the installation, SSAS, SSIS, SSRS, etc.  

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

Friday, December 20, 2013

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

Wednesday, December 18, 2013

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')

Tuesday, December 17, 2013

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]

Monday, December 16, 2013

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
 
 

Friday, December 13, 2013

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.
 

Thursday, December 12, 2013

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;