SQL2005 « Lohn Solutions

Big Trys and Small Trans

The catch phrase around the office these days is “Big Trys and small Trans.” This came about as we were review stored procedure changes and I two suggestions. First, that the entire procedure has a try…catch around it and second that the transaction be as small as possible.

Big Try

We’ve got standards in place [...]

T-SQL Tuesday #002: A Puzzling Situation – Updating Statistics

This post is in response to Adam Machanic’s “T-SQL Tuesday #002: A Puzzling Situation”. One of the databases I manage has multiple very large tables. The biggest contains about 4.8 billion rows. We had to move this database to another server and while we did, we decided we needed to update the statistics. Note it [...]

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 [...]

DBA Training #2

Backups and Backup StrategiesMicrosoft’s SQL Server Backup Overview

http://msdn.microsoft.com/en-us/library/ms175477.aspx

What we use (all definitions taken from link above):

1. Full backups

A full backup contains all the data in a specific database or set of filegroups or files, and [...]

DBA Training #1

General DBA Responsibilities

Availability/Recoverability
Security
Performance
Develop/Deploy

RecoverabilityRecoverability means that, if a data entry error, program bug or hardware failure occurs, the DBA can bring the database backward in time to its state at an instant of logical consistency before the damage was done. Recoverability activities include making database backups and storing them in ways that minimize the [...]

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 [...]

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 [...]

Lohn Solutions

Categories

Archives

Blogroll