Lessons learned from job hunting

These may be obvious, but I'm ranting on writing this to sum up some of my experiences of releasing my CV into the wild...

Recruiters...

Vary greatly
Some understand technology terms, others just match acronyms from your cv.

Love to talk
Well I don't. Not about the same stuff another recruiter told me 5 minutes ago anyway!
Email me the job spec as I don't have to slip out the office to talk to you all.

Have poor geography skills
Guildford and Sutton might both be in Surrey, but they are 23 miles apart. These locations are not interchangeable on a job advert.
Similarly, look at my home counties location before contacting me about 2 week contracts in Edinburgh or Belfast.

Are sparing with the truth
Queue a couple of interviews where the job role and my requirements were vastly different.

Are pushy ****ers
Which part of 'I'm not interested' don't you understand?

WILL send your CV WITHOUT your permission.
Even when asked not to.

Like to post fictitious roles to attract CVs
One recruiter even admitted this.

Like to take their time...
In responding to your communication, even though they expect the opposite behaviour from you.

Are slow to respond to polite requests
How many times have I asked you to remove me from your system?

Obviously the above rant cannot be leveled at ALL recuiters. Sadly though there exists a young, commission hungry and unscrupulous element who let the rest down. Parallels with the reputation of estate agents can be drawn.


London
Is a very expensive place. To commute to, to travel around, to get lunch in.
Money to compensate this has got to be good.


DBA
Is a 'catch all' job title.

At 3 different roles, it has been  -

1. Development focused
2. Maintenance focused
3. Architecture focused

Maybe I can forgive recruiters a little as duties range so wildly...

Anyway, I've signed a new contract for an exciting new challenge in April, so all the above is behind me.
Phew! Breath deeply, and relax....

TSQL : Removing a filegroup

Check no objects sit on the filegroup.
(If they do, then remove them)

SELECT i.*
FROM sys.indexes i
LEFT JOIN sys.filegroups AS fg
ON fg.data_space_id = i.data_space_id
WHERE fg.name = 'myfilegroup'

When all objects are gone, remove the file...

ALTER DATABASE mydatabase
REMOVE FILE mydatabaseFile

When the file has gone, remove the filegroup...

ALTER DATABASE mydatabase
REMOVE FILEGROUP mydatabaseFileGroup

Database Maintenance Checklist

Manually Check (Daily)

1. SQL Server Logs
2. SQL Agent Jobs
3. Windows Event Logs
4. Drive Space (unless you have a trustworthy tool/script that does so)

Automate these via Maintenance Plans / TSQL Agent Jobs -

• Backups - Full
• Backups - Transaction Log
• Backups - Delete old backup files from file system
• Indexes  - Rebuild vs Reorganise
• Statistics - Update (if reorganising indexes)
• History tables - Trim regularly
• SQL Server Error logs - Cycle Regularly

Schedule them so they do not clash and are appropriate to the

• RPO (Recovery Point Objective)
• RTO (Recovery Time Objective)

Recovery Point Objective (RPO)
What time do we need to restore to? (acceptable data loss).
1 day / 1 hour / none

Recovery Time Objective (RTO)
How long is acceptable for data recovery to take? (in the event of issues)

Using @@ROWCOUNT to test data existance

-- test data existence like this...

SELECT 1 FROM Staff WHERE DivisionID = @DivisionId
IF (@@ROWCOUNT > 0)
-- Data found
BEGIN
RAISERROR('Cannot delete Division, it has staff',16,1)
END

DELETE Division WHERE @DivisionId = DivisionId

Using @@IDENTITY to return an identity value

Returning an identity value...

-- given a table like this..

CREATE TABLE Division
(DivisionId INT IDENTITY(1,1) PRIMARY KEY,
DivisionName varchar(100))

-- retrieve ID like this...

INSERT INTO Division (DivisionName)
VALUES (@DivisionName)
SELECT @DivisionID = @@IDENTITY

2 Tales...

Which do you believe?

Microsoft uses Google Search Results (and denies it)


Microsoft on the issue of copying Googles results

Storage performance via TSQL

Storage performance via TSQL

On SQL 2000 looking at I/O statistics is achieved using fn_virtualfilestats

SELECT 
   d.name as DBName
   ,RTRIM(b.name) AS LogicalFileName
   ,a.NumberReads
   ,a.NumberWrites
   ,a.BytesRead
   ,a.BytesWritten
   ,a.IOStallReadMS
   ,a.IOStallWriteMS
   ,CASE WHEN (a.NumberReads = 0) THEN 0 ELSE a.IOStallReadMS / a.NumberReads END AS AvgReadTransfersMS
   ,CASE WHEN (a.NumberWrites = 0) THEN 0 ELSE a.IOStallWriteMS / a.NumberWrites END AS AvgWriteTransfersMS
FROM ::fn_virtualfilestats(-1,-1) a 
INNER JOIN sysaltfiles b ON a.dbid = b.dbid AND a.fileid = b.fileid
INNER JOIN sysdatabases d ON d.dbid = b.dbid
ORDER BY a.NumberWrites DESC

For SQL 2005+, sys.dm_io_virtual_file_stats is a available. This is a dynamic management function that shows how SQL is using the data files.

SELECT
 DB_NAME(filestats.database_id) AS DBName
 ,files.name AS LogicalFileName
 ,num_of_reads AS NumberReads
 ,num_of_writes AS NumberWrites
 ,num_of_bytes_read AS BytesRead
 ,num_of_bytes_written AS BytesWritten
 ,io_stall_read_ms AS IOStallReadMS
 ,io_stall_write_ms AS IOStallWriteMS
   ,CASE WHEN (num_of_reads = 0) THEN 0 ELSE io_stall_read_ms / num_of_reads END AS AvgReadTransfersMS
   ,CASE WHEN (num_of_writes = 0) THEN 0 ELSE io_stall_write_ms / num_of_writes END AS AvgWriteTransfersMS
FROM sys.dm_io_virtual_file_stats(-1,-1) filestats
INNER JOIN sys.master_files files
  ON filestats.file_id = files.file_id
  AND filestats.database_id = files.database_id
ORDER BY num_of_writes DESC

Further info on here c/o David Pless

If Disk Performance is an issue, consider these aspects

• Auditing disk configuration
• Physical file fragmentation
• Is the RAID configuration appropriate for volume of transactions.
• Is hardware behaving? Drive Controller cache ok?