My goal this week was to spend more time on Database Tuning requests. And go for a run at lunchtimes.

Large companies  are always implementing new processes . As a DBA , the requirements for supplying information , particularly  corporate audit information are continuous.  Time management becomes critical. This week I reached stretching point. Creating scripts to gather information helps – but creating ad-hoc reports can be time consuming, and distracts from managing Production environements.

 There’s a constant debate about either having a large team or maintaining a small and nimble team of IT experts. One of the disadvantages of maintaining a small but nimble team, is getting anyone to commit to a schedule. When I’m working in a small team – in a large environment – scheduling work is a challenge.

 I recently consulted in a very large SQL Server environment. One of the key challenges faced by any DBA , was controlling the types of queries executed by application administrators. Even out of hrs – these queries cause havoc – conflicting with maintenance tasks and causing the problem to extend onto the business day. An application admin may run a one-off query not supported on a 500 million row table index structure.

 For example, one admin executed a SQL query – where the optimizer chose a CLUSTERED INDEX scan. The two predicates used , did not appear in any Nonclustered Indexes.  We established it was to be a one-off data update .  I created a temporary Nonclustered Index – then dropped after the job

I’m dealing with one app that causes orphaned transactions to appear in SQL Server. The app uses MSDTC . Basically , they restart the application (for various reasons) without a graceful commit or rollback. I’ve set up a procedure to identify the orphaned transactions through sys.dm_tran_locks  and kill the transaction. This should be seen as a last resort! The locks are placed for a reason –as the DBA how do I know if data consistency is compromised – with specific attention to exclusive locks. I’ve requested the app team research some transaction management before a planned outage

Posts this week on SQLserver-DBA.com

Powershell script to compare two file sizes

SQL Stored Procedure Commenting

Why does Query Optimizer choose a Clustered Index Scan?

How to decide on Index - ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS

SQL Execution Plan in text

Powershell List all Patches Updates on a Server