Monday, May 23, 2016

High vs Low isolation levels in database systems

High Isolation
  • More system resources used
  • More chances of table locking causing other transactions to be blocked. This can harm performance dramatically
  • Reduces types of other concurrency effects that users may encounter

Low Isolation
  • Less system resources used
  • More chances of reading inaccurate data, "dirty reads" and lost data, phantom reads
  • Can potentially improve performance if used correctly
  • Increases types of other concurrency effects that users may encounter

Potential problems to be aware of when using NOLOCK liberally


The sql table hint NOLOCK can be dangerous when abused, especially if there are other stored procedures that depend on and write to the same tables around the same time or concurrently. Reading uncommitted data can impact the integrity of the results returned. These are the same dangers present when using "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"

Bad things that could potentially happen:

  • Reading uncommitted, inaccurate data ("dirty reads")
  • Missing data from other transactions that occurred during read
  • Reading multiple versions of the same rows
  • Read errors occurring if data is moved during read

Saturday, May 21, 2016

What to avoid when writing stored procedures

A list of bad practices to avoid when writing sql stored procedures

  • Using Subqueries when you could use a temp table
  • Not uppercasing keywords and built in functions
  • No consistent formatting
  • Using nested views
  • Not using a consistent naming scheme for sprocs in the same db
  • Not using "BEGIN" and "END" keywords
  • Not using "SET NOCOUNT ON" when needed
  • Selecting all columns when you only need a few
  • Not using comments
  • Not making comments legible
  • Using cursors
  • Counting rows to verify if data exists
  • Misuse of Object-Relational Mappers (ORMs)
  • Negative searches
  • Not doing large comparisons against temp tables or table variables
  • Double dipping instead of writing one query with good conditions
  • Not using local variables
  • Comparing a lot of strings 
  • Clustering with GUIDs
  • Using "UPDATE" too liberally when "CASE" could be used
  • Doing updates and deletes in large batches
  • Using NOLOCK too much
  • Not using paramerterized queries
  • Importing bulk data with "INSERT"
  • Using "SELECT MAX(ID)" when importing

Friday, October 30, 2015

Running a program as a different user over vpn in Windows



If you ever need to run an application as a different user but that user does not have an account on your local pc:

runas /netonly /user:DOMAINGOESHERE\USERNAMEGOESHERE "path to executable"

Use this batch script template. It's pretty self explanatory, it will ensure that any network communication done by that app will use the credentials specified and not your local account.
In my case I was attempting to run SQL Management Studio from my local pc while using a VPN service.

Sunday, August 9, 2015

Windows 10 - Upgrading from Windows 7 or 8 Tips


  • Check out the FAQ for Windows 10 here: Link to FAQ
  • Upgrading from Windows 7/8/8.1
    • You don't have to wait for the update windows icon. Just download the standalone installer here.
    • If you upgraded from windows 7 or 8, I recommend backing up your important files and doing a clean install of Windows 10 after the upgrade. You can do this by creating a bootable installation medium using the installer provided by Microsoft and booting from it. For me, this helped isolate any driver issues that needed to be taken care of and the whole experience was just smoother after a clean install. Download the standalone installer here. 
    • If you have already upgraded once, you do not need a key for the clean install. Microsoft registers your hardware to verify your legitimacy, so you don't have to worry about finding a Windows 10 key for future installations. Just click "skip" during the installation when it gets to that part.
    • Link to list of common errors

  • Drivers
    • Make sure you update your drivers after your clean install. Open up settings > Connected devices and scroll down to the link that says Device manager. From there, go through your devices and check for updates (Right Click > Update Driver Software).
    • If you are experiencing display or sound related issues, this is the first thing you should try.

Friday, July 31, 2015

No Cisco AnyConnect? No problem

I was used to using Cisco's AnyConnect VPN client on Windows, but needed an alternative on linux for various reasons.

1) installed the openconnect plugin for ubuntu's network manager on my laptop running lubuntu:

sudo apt-get install network-manager-openconnect

3) installed Remmina, a client for RDP. Worked like a charm.

sudo apt-get install remmina

Click Here to Buy This Shirt