24 June,2012 by Jack Vamvas
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