Column-oriented databases, just another post

The debate about column-oriented databases and relational databases continues. Conor Cunningham published this post recently, which is a good read.

Fight!

Getting table size per database

The script below will give a simple list of much space each object within your database uses.

I can’t remember where I got it. If you recognize it, please let know.

-- Create the temp table for further querying CREATE TABLE #temp(     rec_id        int IDENTITY (1, 1),     table_name    varchar(128),     nbr_of_rows    int,     data_space    decimal(15,2),     index_space    decimal(15,2),     total_size    decimal(15,2),     percent_of_db    decimal(15,12),     db_size        decimal(15,2)) -- Get all tables, names, and sizes EXEC sp_msforeachtable @command1="insert into #temp(nbr_of_rows, data_space, index_space) exec sp_mstablespace '?'",             @command2="update #temp set table_name = '?' where rec_id = (select max(rec_id) from #temp)" -- Set the total_size and total database size fields UPDATE #temp SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #temp) -- Set the percent of the total database size UPDATE #temp SET percent_of_db = (total_size/db_size) * 100 -- Get the data SELECT * FROM #temp ORDER BY total_size DESC DROP TABLE #temp

SQL Trace or DMV’s without sysadmin permissions

Sometimes a login has only a limited set of permissions, perhaps readonly credentials to a database or server. However, it might be necessary for this login to run traces and access server info from DMV’s.

• To run SQL Trace you need the ALTER TRACE permission.
• To access DMV’s you need the VIEW SERVER STATE permission.

The administrator can assign this through SSMS or as T-SQL. This is of course documented in BOL here.

SARGable predicates

This post is worth reading:

http://blogs.msdn.com/b/conor_cunningham_msft/archive/2010/12/18/conor-vs-more-sargable-predicates.aspx