I am going PREPAID cellular plan ...

Currently, with T-Mobile, I am paying $49.99 family plan, which gives me 2 lines, 500 minutes, free T-Mobile to T-Mobile calls, free nights and weekends. I pay extra for my 5GB data plan ($25) and my wife is using the 200MB ($12). Plus another $4.99 for each messaging plan. So overall, I am paying around $100 per month. My family plan is an old plan (grandfathered), so I also get the full discount for new devices in I choose to extend the 2 year contract.

Now, my plan is not bad at all, $100 for 2 persons - it is actually considered to be quite awesome - since most my friends who have newer plans on T-Mobile (or other carriers) usually pay between $80 or more per person. So basically almost half of what most of my friends with smartphones are paying.

Now, let's consider the overall ownership cost over 2 years - since that is the length of the standard cellphone contract. If I pay $279 for each device (assuming Samsung Galaxy S3), add the monthly payment, over 2 years, I am paying about $2,950+. Of course, that is cheaper than my friends who is paying $110 per month for their family plan. But, I plan to reduce the overall cost some more. How? By going PREPAID!

To each their own, but after assessing my needs (getting some stats from my bills for the last 6+ months), I think getting the Monthly 4G plan for $30 will be sufficient for me (and my wife). This plan will get me 5GB data, unlimited text and 100 minutes voice. So for both of us, that will be $60 per month - without contract. There is a drawback to this - that if I buy a new device, I have to pay full price without any discount. So that means $600 for Samsung Galaxy S3. So in short, no contract, I am paying cheaper per month, but paying 3+ times IF buying a new device. But what is the overall 2 years cost of ownership? Let's assume we both buy GS3 and paying $30 Monthly 4G plan - which totaling $2,640 - a saving of $300 over 2 years. It looks a small saving on paper, but in reality, it is actually a pretty good deal. Let's list down the benefit:

• No contract. Only pay as needed. So the potential saving here is actually larger. If I think for the next month that I will be in a WiFi bubble, I may switch to $10 pay as you go ans save some more. If I am traveling, I can switch back to the Monthly 4G etc. 
• In a contract deal, if I am not getting a new device once my contract is up - this means that I am giving free money to the carrier, since the cost of the phone is factored in the monthly bill. With no contract prepaid, I don't have that burden. This also means that if I elect to switch carrier or get a new cellphone/device, I don't have to wait until the contract is done.
• My wife data plan will get an upgrade, from 200MB per month to 5GB. Now, I am assuming that she needs that 5GB - if she does not (because of WiFi), she can switch to a cheaper pay-by-the-day based on usage.
• From 300 texts per month to unlimited texts. This is also assuming we need that. I mostly do not need it, but there is no plan with voice and data only. 
• If spending $100 or more in 1 year, get a Gold status, which means 15% more minutes and your minutes can be used for a full year (instead of the regular 90 days).
• No overages!

So overall, I am fairly convinced (for now) that the prepaid/Monthly 4G will save me some money but still fulfill my need for mobile communication.

T-Mobile also has Pay-As-You-Go plan (for voice prepaid) and Pay-By-The-Day plan (for use only when needed). Depending on your needs, these plans can potentially be cheaper than the Monthly 4G plan that I am planning to use.

 

 

Microsoft Windows engineer John Lam also has a similar story in his blog. 

-- read more and comment ...

SQL Tuning Scripts

While tuning the SQL database for the project I am working on, I found these scripts by Brent Ozar that were extremely useful. These scripts rely on DMV on your SQL instance - so they are best run after you let your SQL databases run for a while and DMV data got flushed after a reboot. Brent also created videos explaining these scripts. Please note that all these scripts are Brent's creation - not mine.

FIND MISSING INDEXES
In the result, the "impact" column is the indicator how needed the index is. Brent recommends that 10 million or higher number means high - that the index is needed.

/* ------------------------------------------------------------------
-- Title: FindMissingIndexes
-- Author: Brent Ozar
-- Date: 2009-04-01 
-- Modified By: Clayton Kramer ckramer.kramer(at)gmail.com
-- Description: This query returns indexes that SQL Server 2005 
-- (and higher) thinks are missing since the last restart. The 
-- "Impact" column is relative to the time of last restart and how 
-- bad SQL Server needs the index. 10 million+ is high.
-- Changes: Updated to expose full table name. This makes it easier
-- to identify which database needs an index. Modified the 
-- CreateIndexStatement to use the full table path and include the
-- equality/inequality columns for easier identifcation.
------------------------------------------------------------------ */
 
SELECT  
 [Impact] = (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans),  
 [Table] = [statement],
 [CreateIndexStatement] = 'CREATE NONCLUSTERED INDEX ix_' 
  + sys.objects.name COLLATE DATABASE_DEFAULT 
  + '_' 
  + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,'')+ISNULL(mid.inequality_columns,''), '[', ''), ']',''), ', ','_')
  + ' ON ' 
  + [statement] 
  + ' ( ' + IsNull(mid.equality_columns, '') 
  + CASE WHEN mid.inequality_columns IS NULL THEN '' ELSE 
   CASE WHEN mid.equality_columns IS NULL THEN '' ELSE ',' END 
  + mid.inequality_columns END + ' ) ' 
  + CASE WHEN mid.included_columns IS NULL THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END 
  + ';', 
 mid.equality_columns,
 mid.inequality_columns,
 mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs 
 INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle 
 INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle 
 INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID 
WHERE (migs.group_handle IN 
  (SELECT TOP (500) group_handle 
  FROM sys.dm_db_missing_index_group_stats WITH (nolock) 
  ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))  
 AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable') = 1 
ORDER BY [Impact] DESC , [CreateIndexStatement] DESC

FIND UNDER USED / UNUSED INDEXES
Since index is mainly used for reading, therefore tables that are written a lot but read rarely may be burdened by indexes. This script is useful for finding those tables. Within the result of this script, the "reads_per_write" is the ratio between the read and write to the table. Depending on the current performance, # of read/write, the index may or may not needed. Generally, Brent recommends to remove indexes where "read_per_write" is below 1.

SELECT 
o.name
, indexname=i.name
, i.index_id   
, reads=user_seeks + user_scans + user_lookups   
, writes =  user_updates   
, rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)
, CASE
 WHEN s.user_updates < 1 THEN 100
 ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
  END AS reads_per_write
, 'DROP INDEX ' + QUOTENAME(i.name) 
+ ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'
FROM sys.dm_db_index_usage_stats s  
INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id   
INNER JOIN sys.objects o on s.object_id = o.object_id
INNER JOIN sys.schemas c on o.schema_id = c.schema_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
AND s.database_id = DB_ID()   
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000
ORDER BY reads

FIND QUERIES THAT ARE MISSING INDEXES (using Plan Cache)
This is an EXPENSIVE query (not recommended to be run during peak-use-time), running against temporary cached plans.

SELECT qp.query_plan
, total_worker_time/execution_count AS AvgCPU 
, total_elapsed_time/execution_count AS AvgDuration 
, (total_logical_reads+total_physical_reads)/execution_count AS AvgReads 
, execution_count 
, SUBSTRING(st.TEXT, (qs.statement_start_offset/2)+1 , ((CASE qs.statement_end_offset WHEN -1 THEN datalength(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS txt 
, qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]' , 'decimal(18,4)') * execution_count AS TotalImpact
, qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Database)[1]' , 'varchar(100)') AS [DATABASE]
, qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Table)[1]' , 'varchar(100)') AS [TABLE]
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
cross apply sys.dm_exec_query_plan(plan_handle) qp
WHERE qp.query_plan.exist('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex[@Database!="m"]') = 1
ORDER BY TotalImpact DESC

-- read more and comment ...