Twitter

Follow SQLQuill on Twitter

Subscribe

MN Pass

Challenges I’m Currently Facing

Seven base SQL Servers used for the web application:

  • 3 – Clustered, SQL 2005 SP3 Enterprise edition
  • 2 – Clustered, SQL 2005 SP3 Standard edition
  • 2 – Stand Alone, SQL 2005 SP3 Standard edition

Three Log Shipping SQL servers:

  • 1 – Clustered, SQL 2005 SP3 Enterprise edition
  • 2 – Stand Alone, SQL 2005 SP3 Standard edition

One Utility Clustered Standard edition:

  • 1 – Clustered, SQL 2005 SP3 Standard edition

I was the lead DBA on the most recent release of our newest version of our website.  We moved all the databases to new servers and made a bunch of updates.  Here were some of the challenges:

  • Total data size – 2.2 TB of data
  • SQL Servers crossing 4 domains and still communicating
  • Changes to environment:
    • Code changes for new functionality
    • SQL2005 SP3 applied
    • Litespeed upgraded from version 4.8 to 5.02
    • New SQL Server in the environment and one DB moved to it
    • Maintenance window reduction to 4 hours per week
    • Security – Kerberos, double hop,
      • Tightened,
      • Changes in how hot fixes are deployed
  • Tools – I3, Litespeed
  • CPU Spikes post deploy – scheduled procedure wasn’t tuned properly
  • LogShipping to get large DBs ready for deployment
  • LogShipping Issues post deploy – restores missing files, manually restoring a log or two every few hours to keep things in sync.

Here are the current SQL 2005 Technologies that we are using in production:

  • Clustering
  • Partitioning
  • Fragmentation based re-indexing
  • Synonyms
  • LogShipping with both native and LiteSpeed
September 4th, 2009 | Tags: , , | Category: SQL2005, Weekly Challenges | Leave a comment

SQL 2005 – Script to find missing indexes

SET CONCAT_NULL_YIELDS_NULL OFF
SELECT weighted_cost = avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),
 s.*,
 'CREATE INDEX IX_' +
 REPLACE(REPLACE(COALESCE(equality_columns, inequality_columns), '[', ''), ']', '') +
 ' ON ' + d.statement + '(' + COALESCE(equality_columns, inequality_columns) +
 CASE WHEN equality_columns IS NOT NULL
  THEN
  CASE WHEN inequality_columns IS NOT NULL
   THEN ', ' + inequality_columns                
   END      
  END       + ')' +
  CASE WHEN included_columns IS NOT NULL
   THEN ' INCLUDE (' + included_columns + ')'
  END
FROM    sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g      
 ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s      
 ON g.index_group_handle = s.group_handle
ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC;
January 29th, 2009 | Tags: , , | Category: Scripts, SQL2005 | Leave a comment