Thursday, January 29, 2015

InnoDB or MyISAM, That's the question

Currently MySQL Supports two storage engines InnoDB (default for MySQL 5.5+) and MyISAM. When you are creating a table you may be prompted with the question which one I should choose and why? This article is an attempt to shed some light to clear your thoughts make the correct decision.

Before go any further let's see what a Storage Engine actually does. In simple terms it is the most integral part of any database engine. If we put it to a definition  "Storage engine is what stores, handles, and retrieves information from a table". The marvel of MySQL we could have different storage engines for different tables. Even though there are plenty of storage engines supported by MySQL, this article will be focused on InnoDB and MyISAM. Which are the most common and frequently used storage engines.


The MyISAM type is actually a branch, or child, of the ISAM engine. If you are ok with not having TRANSACTION, and with having table-level locking as opposed to row-level locking, MyISAM is typically the best for performance and functionality. The MyISAM type is great for sites that have a very low INSERT/UPDATE rate and a very high SELECT rate. Other options might want to be considered with very high INSERT/UPDATE queries due to the restrictions of table-level locking causing a decrease in performance.

The max number of rows supported for MyISAM is ~4.29E+09 with up to 64 indexs per table. It is also good to note that fields of TEXT/BLOB can be fully indexed for cases such as searching.


Compared to MyISAM, InnoDB provides many more feature to increase performance. There is some additional time spent during initial setup, as opposed to MyISAM, but the benefits far outweigh the time spent. One major difference is the ability to do row-level locking, as opposed to table-level locking, to increase performance time. This allows parallel INSERT/UPDATE/DELETE queries to be ran on the same table, unlike MyISAM where each query has to wait its turn to run.

Additionally, InnoDB provides foreign key functionality. This allows you to ensure that dependent data in table one is present before inserting data into table two. Likewise, it prevents data in table one from being deleted if there is data in table two that depends on it.

InnoDB also provides caching for data and indexes in memory, as well as on disk, which provides a large increase in performance gain. For those low on RAM this might not be the ideal solution, but with memory as cheap as it is this is becoming less of an issue.


In conclusion, while there is no perfect catchall storage engine, it can be fairly safe to say that InnoDB and MyISAM are the typical go-to for many applications and DBAs. It is good to note, however, that though they are a good catchall, they may not be ideal for every situation and there may be another storage engine available that will increase performance in your application/environment.

Friday, January 25, 2013

Impersonating a Windows Login in SQL Server

In our day to day life as DBA's  most common issues we get are related to security. Specially some accounts not having required access to do certain things. This can be real messy if you don't have a well defined security architecture in place. From SQL Server 2005 on wards Microsoft has introduced Role Based security which has reduced the effort of DBA's remarkably. However if you mess up the Security Roles it will be a nightmare to fix a security issue.

So in this article I am providing a simple script which can be really handy when you want to check how a DB object behaves when its been called using a specific windows login in SQL Server.

SETUSER 'DomainName\UserName'

-- Here Goes Your SP Call or any other Query you want to check, this query will run using the credentials of the above user and report any errors that user may encounter

Always happy to answer any queries via comments.

Thursday, January 24, 2013

FIX : CLR .NET Assemblies security error after Database Restore

Recently I had to migrate a massive database from one hosting provider to another (Awesome Amazon Web Services). After backup and restore I noticed all the Stored Procedures calling CLR assemblies fails with a security error. 

"Msg 10314, Level 16, State 11, Procedure xxx_xxxxxx, Line 285
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65627. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly" 

This was really strange as it was a direct restore and all the assemblies resides in side the DB itself. So here is what you have to do to fix this issue;

1. Query the table sys.assembly_files and sys.assemblies make sure your assemblies are present in the restored DB. If not you have to re register the assemblies.
2.  Make sure your restored DB has a owner (Ideally sa)
3. Make sure the login who owned the db in the previous server is created in the new db server, if it is a SQL Server login the new login should have the same SID as the previous servers login, Windows logins just having the same name would sufficient.
4. Then run the below command to make your DB trustworthy.

SET TRUSTWORTHY ON  --after restoration database has this option equal to off

This should simply solve your issue. Please share with me if you find any trouble doing this workaround.

Tuesday, September 13, 2011

Round UP with CEILING

Rounding up in TSQL is bit tricky but if you want just to round up a decimal value to the nearest integer then you can use tsql Math function CEILING function to achieve this easily.

CEILING function return the next integer that is larger than the number entered.

SELECT CEILING(43.98) -- Returns 44
SELECT CEILING(43.18) -- Returns 44
SELECT CEILING(0.18) -- Returns 1
SELECT CEILING(-0.18) -- Returns 0
SELECT CEILING(-43.18) -- Returns -43

MSDN reference :

Monday, August 29, 2011

View SQL Server Plan Cache

The major reason we use Stored Procedures is because it allows SQL Server to cache the Execution plan so it can be reused. This gives a performance boost as the Query Optimizer doesn't have to go through the expensive steps of choosing the right query plan for the query.

But have you ever actually seen whether your stored procedure is actually has a plan cached in the server or whether it is been reused? There are quite a lot of scenarios Query Optimizer is forced to do a recompile of a Procedure which can be a huge performance hit. It is entirely a different topic for another article which I will write in the future. 

In this article I am going to show you how you can view whether a procedure is cached or not, how many times it has been reused, etc.This is the query which you can use to view these information;

SELECT UseCounts,RefCounts, Cacheobjtype, Objtype, 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 

This will return below columns;

Number of times the plan has reused since caching
Number of times the other cached objects reused this plan
Object type out of one of the following;                                                        Compiled Plan
Parse Tree
Extended Proc
CLR Compiled Functions
CLR Compiled Procedures
Object type with ragards to the database (SP, View, Trigger,  etc)
Database name which the cached object belongs to
SQL Statement Cached

However execution plans are not kept in the cache forever. They removed from the cache if one of the following criteria's met;

  • More memory is required by the system
  • The "age" of the plan has reached zero
  • The plan isn't currently being referenced by an existing connection

Aging is happens using a formula that multiplies the estimated cost of the plan by the number of times it has been used (UseCounts). There is a SQL Process called "Lazywriter" (Love the name) which is responsible for removing plans from the cache.

You can see this at work, first select a particular SP. Then simply restart the SQL Server Service and then run the above script. You wont see your SP in the cache, then run the SP. Then run the above script again, then you can see your SP is cached. Running again you will see the UseCounts column increases if your SP is not recompiling at each run.

This is a good way to be sure your SP is not recompiling as there are lot of situations where it does.

Wednesday, August 24, 2011

Formatting Dates in SSIS

When you want to format a date in a SSIS package it is not straightforward. However there are two ways you can do this.

First method is formatting the date from your TSQL. You can use the CONVERT() function to do this;

CONVERT(VARCHAR(20),GETDATE(),101) -- 08/25/2011
CONVERT(VARCHAR(20),GETDATE(),103) -- 25/08/2011
CONVERT(VARCHAR(20),GETDATE(),105) -- 25-08-2011
CONVERT(VARCHAR(20),GETDATE(),112) -- 20110825

For more formatting codes with CONVERT() function please refer this

The second method is converting it in a expression in SSIS. Below is a sample code for that;

(DT_WSTR,4)YEAR(GETDATE()) + "/" + RIGHT(“00″ + (DT_WSTR,2)MONTH(getdate()),2) +  "/" + RIGHT(“00″ + (DT_WSTR,2)DAY(getdate()),2)

This will format the date to the format of  YYYY/MM/DD. You can switch the statements and format as you desires.

Please leave a comment if you have any questions regarding this.