SQL Database Data File Growth Performance Impact

I was On call this week and one of our databases files became over 90% full.  This is the threshold we set to alert us to manually expand any file.  We have autogrowth just in case, but we want to manually expand files after hours to avoid the performance impact on SQL server when expanding [...]

January 31st, 2009 | Tags: , , | Category: SQL2005 | 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 [...]

January 29th, 2009 | Tags: , , | Category: SQL2005, Scripts | Leave a comment

Performance Counters to Monitor for SQL

I would suggest once a quarter, set up a performance monitor log to collect the following counters for a couple of 24 hour periods.  Look at the results in both a graph and report forms to determine the peak usage times and to see if there are any bottlenecks on your SQL server.

Here are the [...]

January 6th, 2009 | Tags: , , | Category: SQL2005, SQL2008 | Leave a comment