Rahman Agoro, the frontline SQL DBA

Just another WordPress.com weblog

Field notes from SQL BITS 7 full conference

Posted by rahmanagoro on October 4, 2010


I recently attended the SQL bits 7 full conference in the UK, I will be sharing tips and other information that I was able to pick up on the day. I will divide this into the different days and all the sessions that I was able to attend.

Thursday Session

I attended Rob Farleys Advanced Query Tuning training on Thursday, it was a tough start to the day as my train to York got canceled as soon as I got to the station, ok its not too bad I was offered another ticket to get on the train leaving in about 3 minutes, hooray !! It was only the beginning of a tough start, I mistakenly got into a fancy steam train to Newcastle, it looked like one of the Harry Potter trains, something just didn’t feel right when I got into it. Luckily for me, I was able to get out of the train in time before it departed as it would have been a slow steam train all the way to Newcastle and there are no stops before then.

Ok finally, I missed my train and got on the next one 30 minutes later. I only missed about 30 minutes of the training and to be honest I choose to attend this course because I believe that query tuning is an art that one needs to master by constantly learning and constantly being challenged to tune queries, so my expectations were that I will learn something that I have not seen or heard of before.

Rob Farleys Session

During this session I was expecting to be thought some advanced SQL topics, below are the notes that I took during the session.

  • Investigate the use of a full join
  • Always check on the execution plan properties as it does have some useful information on it.
  • Do some more research on join types, nested loops, merge join, hash join etc.
  • Using a distinct and group by with a scalar function, the thing to look out for here is that if one uses the distinct keyword, all the records are passed to the function but if a group by is used, it reduces the number of records that is passed to the scalar function.
  • One should not use the between clause because of the issue with 23:59:999, one should use < @date and >@date instead. 
  • Indexes can sometimes use a different collation and one should be wary of this.

 

Brian McGeHee

Health Checks for SQL server. 

This session sounded useful as I have a set of checks that I would normally carry out on database servers that I manage every morning. These includes the following. 

  • Checking connectivity to the server.
  • Checking the error log
  • Checking the backups
  • Checking SQL replication
  • Checking waits on the database

The point of attending this session was to see if there are any other checks that I may be missing out and ones that I can easily automate. What I have done is automated this checks using a combination of SSIS for collecting the data from multiple servers and using SSRS for the web presentation for the reports.

Notes that I took down on the day includes the following. 

  • Check Brian McGehess’s website for more information about the daily checks.
  • Extract baseline information about your servers.
  • Create a best practice manual
  • When commissioning a new server, ensure that firmware is always up to date.
  • Look at collecting event log across multiple servers (I want to automate this using powershell)
  • Using PAL (Nice handy tool)
  • Download SQL server tacklebox
  • Download Brent Ozar’s blitz script

Briand McGeHee’s website can be found on: www.BrianMcGehee.com/healthcheck.zip

Brant Ozar’s SQL Server and SAN session

This session was to discuss managing a SQL server instance on a SAN from a storage perspective, things that a DBA should look out for and other tips. I have been to several of Brent’s presentation, and he generally a good speaker. So far its always been fun. 

Notes that I took down on the day includes the following.

  • Windows task manager on virtual machines should not be relied upon, in fact he said its in a mess.
  • Run CPU-Z to get some visibility on physical hardware, check www.cpuid.com
  • Minimise the number of virtual cores
  • Strip out unnecessary virtual hardware, i.e. floppy drive, CD drive etc.
  • Remove all background services
  • Monitor for throttling.
  • Avoid affinity masking

 

Brent also touched on some VMware memory best practices

They include the following

  • Setting memory reservation
  • Set minimum and maximum memory allocation appropriately

HyperV 

  • Avoid dynamic memory for SQL server
  • VMware counters to check out include the following.
  •  % processor time
  • Host processor speed
  • Limit (mhz)
  • Reservation (Mhz) – ask your sysadmins about reservation on vmware hosts.

VMware memory counters

  • Memory limit
  • Emory reservation (MB)
  • Memory ballooned
  • Memory swapped (This should always be 0)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: