SQL Server tuning

See a list of all the indexes in the database

select * from sys.dm_db_index_physical_stats(null,null,null,null,null)

Index fragmentation

SELECT DB_NAME() AS DatabaseName,
Object_name(i.object_id) AS TableName,
i.index_id,
name AS IndexName,
avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'Limited') AS ips
INNER JOIN sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
ORDER BY avg_fragmentation_in_percent desc

Show filegroups and db files

select * from sys.filegroups
select * from sys.database_files

Additional commands

SET SHOWPLAN_XML OFF
GO
SET STATISTICS PROFILE OFF
SET STATISTICS IO ON
-- clear query plans
dbcc freeproccache;
-- clear buffer cache
dbcc dropcleanbuffers;

Examples using AdventureWorks2016

use AdventureWorks2016

select pp.name
from Production.Product pp
join Sales.SalesOrderDetail ss
on pp.ProductID = ss.ProductID
join Sales.SalesOrderHeader oh
on ss.SalesOrderID = oh.SalesOrderID;

select check_clause from INFORMATION_SCHEMA.CHECK_CONSTRAINTS
where CONSTRAINT_NAME = 'CK_Employee_SickLeaveHours';

SET STATISTICS PROFILE on

SELECT  sql_text.text, last_execution_time, creation_time
FROM    sys.dm_exec_query_stats AS stats
        CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS sql_text
order by last_execution_time DESC

SELECT * FROM sys.dm_exec_query_transformation_stats

EXEC sys.sp_updatestats @resample = '' -- char(8)

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION

	SELECT TOP 100 * FROM AdventureWorks2016CTP3.Person.Person-- WITH(TABLOCKX);

	SELECT resource_type, request_mode, COUNT(*) AS lock_count
	FROM sys.dm_tran_locks
	WHERE request_session_id = @@SPID
	GROUP BY resource_type, request_mode;

ROLLBACK

DBCC SHOW_STATISTICS ('Person.Person', 'IX_Person_LastName_FirstName_MiddleName')

SELECT * FROM sys.stats
WHERE OBJECT_ID = OBJECT_ID('Person.Person')
ORDER BY stats_id;

DBCC SHOW_STATISTICS ('Person.Person', 'PK_Person_BusinessEntityID')

DBCC SHOW_STATISTICS ('Person.Person', 'IX_Person_LastName_FirstName_MiddleName')

SELECT * FROM Person.Person WHERE LastName = 'Alonso';

SELECT * FROM Person.Person WHERE LastName = 'Acca';

See the query plans

SELECT 		databases.name,
	dm_exec_sql_text.text AS TSQL_Text,
	dm_exec_query_stats.creation_time, 
	dm_exec_query_stats.execution_count,
	dm_exec_query_stats.total_worker_time AS total_cpu_time,
	dm_exec_query_stats.total_elapsed_time, 
	dm_exec_query_stats.total_logical_reads, 
	dm_exec_query_stats.total_physical_reads, 
	dm_exec_query_plan.query_plan
FROM sys.dm_exec_query_stats 
CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
INNER JOIN sys.databases
ON dm_exec_sql_text.dbid = databases.database_id
WHERE dm_exec_sql_text.text LIKE '%t_item_master%';

Connecting to HBase from Erlang using Thrift

The key was to piece together steps from the following two pages:

Thrift API and Hbase.thrift file can be found here
http://wiki.apache.org/hadoop/Hbase/ThriftApi

Download the latest thrift*.tar.gz from http://thrift.apache.org/download/

sudo apt-get install libboost-dev
tar -zxvf thrift*.tar.gz
cd thrift*
./configure
make
cd compiler/cpp
./thrift -gen erl Hbase.thrift

Take all the files in the gen-erl directory and copy them to your application’s /src.
Copy the thrift erlang client files from thrift*/lib/erl to your application or copy/symlink to $ERL_LIB

Can connect using either approach:

{ok, TFactory} = thrift_socket_transport:new_transport_factory("localhost", 9090, []).
{ok, PFactory} = thrift_binary_protocol:new_protocol_factory(TFactory, []).
{ok, Protocol} = PFactory().
{ok, C0} = thrift_client:new(Protocol, hbase_thrift).

Or by using the utility, need to investigate the difference

{ok, C0} = thrift_client_util:new("localhost", 9090, hbase_thrift, []).

Basic CRUD commands

% Load records into the shell
rr(hbase_types).

% Get a list of tables
{C1, Tables} = thrift_client:call(C0, getTableNames, []).

% Create a table
{C2, _Result} = thrift_client:call(C1, createTable, ["test", [#columnDescriptor{name="test_col:"}]]).

% Insert a column value
% TODO: Investigate the attributes dictionary's purpose
{C3, _Result} = thrift_client:call(C2, mutateRow, ["test", "key1", [#mutation{isDelete=false,column="test_col:", value="wooo"}], dict:new()]).

% Delete
{C4, _Result} = thrift_client:call(C3, mutateRow, ["test", "key1", [#mutation{isDelete=true}], dict:new()]).

% Get data
% TODO: Investigate the attributes dictionary's purpose
thrift_client:call(C4, getRow, ["test", "key1", dict:new()]).

TODO: Research how to use connection pooling with thrift.

TODO: Document connecting to Cassandra using thrift, but all the hard work has already been done by Roberto at https://github.com/ostinelli/erlcassa

Pro JavaScript for Web Apps – crossroads

Apress has an awesome book that covers KnockoutJS: Pro JavaScript for Web Apps.

You will get stuck on Chapter 4, when using the latest latest version of crossroads, hasher and signals because setting the context of hasher to be crossroads doesn’t seem to work anymore:

     hasher.initialized.add(crossroads.parse, crossroads);
     hasher.changed.add(crossroads.parse, crossroads);
     hasher.init();

     crossroads.addRoute("select/{item}", function (item) {
           viewModel.selectedItem(item);
     });

The code works with the version of libraries included in the book’s downloadable source code, but if you do a latest git pull of all 3 libraries you need to change the code as per the example as given in the Hasher GitHub readme

crossroads.addRoute("select/{item}", function (item) {
  viewModel.selectedItem(item);
});
function handleChanges(newHash, oldHash) {
  crossroads.parse(newHash);
};
hasher.changed.add(handleChanges);
hasher.initialized.add(handleChanges);
hasher.init();

Also, the crossroads routes must be set up before the hasher.init(). Otherwize the hasher initialized callback will have no routes to execute.
Otherwise, the book is great! Bunch of people in the office want to borrow it once I am done with it:)

Installing Erlang R15B from source in Ubuntu Oneiric

Download and extract Erlang source code:

wget http://www.erlang.org/download/otp_src_R15B.tar.gz
tar xfvz otp_src_R15B.tar.gz

Install c compiler, make, git and other needed tools to compile just about anything C based in Ubuntu

sudo apt-get install build-essential git-core libwxgtk2.8-dev libgl1-mesa-dev libglu1-mesa-dev libpng3 wx-common default-jre default-jdk fop

Install Erlang build dependencies, this is a shortcut to not having to wonder what dependencies are needed to build Erlang from source

sudo apt-get build-dep erlang

Build and install erlang

./configure
make
make docs
sudo make install
sudo make install-docs

Sharding in ChicagoBoss

ChicagoBoss provides “vertical sharding” out of the box: each model can be stored in a different database or db_adapter.

Sample boss.config where models wiki and author are stored in MySQL and all other models will be using mock db_adapter:

[{boss, [
{applications, [cb_tutorial]},
{db_host, "localhost"},
{db_port, 1978},
{db_adapter, mock},
{log_dir, "log"},
{server, mochiweb},
{port, 8001},
{session_adapter, mock},
{session_key, "_boss_session"},
{session_exp_time, 525600},
{db_shards, [
[
{db_host, "localhost"},
{db_adapter, mysql},
{db_port, 3306},
{db_username, "root"},
{db_password, "password"},
{db_database, "wiki"},
{db_shard_id, first_shard},
{db_shard_models, [wiki, author]}
]
]}
]}].

Note: db_shard_id tuple is required for mysql db_adapter because of the way mysql db_adapter included with CB creates connection pools. Think of it as a connection pool name. DBIdentifier in source code.

Sharding examples are endless: you can persist models of click-stream data to Riak, store content of pages in PostgreSQL, store some logs in Archive type of MySQL database and so on, all based on one configuration file.

Fun with ChicagoBoss Models

I decided to create some examples for CB Model API documentation.

Let’s say we want to create a basic 1:Many entity relationship between a blog post and comments in our hypothetical blog software. 1 blog post can have 0 or many comments. Create a new empty ChicagoBoss application by running:

git clone git://github.com/evanmiller/ChicagoBoss.git
cd ChicagoBoss
make
make app PROJECT=blogy
cd ../blogy

Create a model for a blog post. Put this into src/model/post.erl

-module(post, [Id, PostTitle, PostText]).
-compile(export_all).
-has({comments, many}).

Each blog post can have many comments, CB requires that you add -has({comments, many}). to the module declaration. Note that the comment model name must end with s as the first element in the tuple (tag of the tuple).

Comment must belong to a post. We add a simple length of the PostId check into validation_tests/0. Put this into src/model/comment.erl

-module(comment, [Id, PostId, CommentAuthor, CommentText]).
-compile(export_all).
-belongs_to(post).

validation_tests() -> [{fun() -> length(PostId) > 0 end, "Comment must have a post."}].
(wildbill@f15)1> P1 = post:new(id, "Awesome first post", "ftw").
{post,id,"Awesome first post","ftw"}
(wildbill@f15)2> {ok, P1Saved} = P1:save().
{ok,{post,"post-1","Awesome first post","ftw"}}
(wildbill@f15)3> P1Saved:id().
"post-1"
(wildbill@f15)4> C1 = comment:new(id, P1Saved:id(), "Anonymous", "Comment text").
{comment,id,"post-1","Anonymous","Comment text"}

At this point, the shell has variable C1 representing a new comment that is associated with our first blog post.

(wildbill@f15)5> C1:belongs_to().
[{post,{post,"post-1","Awesome first post","ftw"}}]
(wildbill@f15)6> {ok, C1Saved} = C1:save().
{ok,{comment,"comment-2","post-1","Anonymous", "Comment text"}}
(wildbill@f15)7> C1Saved:belongs_to_names().
[post]

Running ChicagoBoss unit tests in your application

If you get the following error after you run make test in the source directory of your ChicagoBoss web application:

=INFO REPORT==== 8-Jan-2012::21:49:44 ===
Starting Boss in production mode....

=INFO REPORT==== 8-Jan-2012::21:49:44 ===
Starting master services on nonode@nohost
{"init terminating in do_boot",{{badmatch,{error,{"no such file or directory","wiki.app"}}},[{boss_web_test,bootstrap_test_env,2,[{file,"src/boss/boss_web_test.erl"},{line,16}]},{boss_web_test,run_tests,1,[{file,"src/boss/boss_web_test.erl"},{line,41}]},{init,start_it,1,[]},{init,start_em,1,[]}]}}

Crash dump was written to: erl_crash.dump
init terminating in do_boot ()
make: *** [test] Error 1

That means that you need to run make before running make test

Installing Erlang on Fedora

The following has been tested on Fedora 13 and 15:

yum install bison bison-devel ncurses ncurses-devel zlib zlib-devel openssl openssl-devel gnutls-devel gcc gcc-c++ wxBase.i686 wxGTK.i686 wxGTK-devel.i686 unixODBC.i686 unixODBC-devel.i686 fop
wget http://www.erlang.org/download/otp_src_R15B.tar.gz
tar xfvz otp_src_R15B.tar.gz
cd otp_src_R15B
./configure --with-ssl
make
su -c 'make install'
make docs
su -c 'make install-docs'