Did you know - AngularJS custom Service and Promise trivia

If you are writing a custom Angular service that says goes out and

• does a $http.get() and
• uses  .then() promise

the .then() promise will actually end up returning another promise that you can wire up to in your controller(where you are using this custom service). Also, note that in this scenario you are likely to use the revealing module pattern - like using a factory to return an object that you need instantiate, in order to use the custom service. This is different from the regular service singleton you get when using the built-in angular services.

Sql Update Stats Tip

Auto Update Stats only works with the default sampling which are changes that affect 20% of the total number of rows. For very large tables or even for smaller tables with a highly jagged distribution of data, we often need to manually update the stats using the FULLSCAN option. Otherwise the stats will remain highly skewed and the cardinality estimates that the Sql Optimizer depends on will be unreliable.

Note that FULLSCAN is not the panacea of all skewed data and if the data distribution is very jagged, it may not help much in updating the stats. That's where creating filtered stats comes handy, but that's another topic.

Command to check the histogram of the stats for an index, run
DBCC SHOW_STATISTICS('tablename', 'indexname')

Another option (Sql2008 and higher) to automatically trigger updating the stats (rather than the 20% default threshold) on say large tables is by turning on the trace 2371 like so

DBCC TRACEON(2371, -1)  -- the -1 is for all sessions
GO

DBCC TRACESTATUS
GO

The downside of turning on this trace is it results in recompilation of all queries accessing the table.


From SQL2008 onwars, the DMV sys.dm_db_stats_properties is useful in determining how much changes have taken place in the table before the update stats would be triggered. Of particular interest is the modification_counter column using the query

--Examine stats of a table
SELECT
     s.name,
     p.object_id,
     p.stats_id,
     p.last_updated,
     p.rows,
     p.rows_sampled,
     p.steps,
     p.unfiltered_rows,
     p.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties
  (s.object_id, s.stats_id) AS p
WHERE s.object_id = OBJECT_ID('tablename')


References:
http://msdn.microsoft.com/en-us/library/dd535534(SQL.100).aspx
http://support.microsoft.com/kb/2754171

Plan Cache Pollution

Sql Server will cache plans for "safe" statements but not in the way you might expect. For example, if we pass different integer values for a parameter in a safe statement, Sql Server may cache tinyint, smallint, int and bigint plans, thereby leading to Plan Cache pollution.

If Sql Server deems a set of ad hoc identical statements to be unsafe, then it will not parameterize them and end up creating a separate plan for each of the statement. For instance WHERE predicates with = and LIKE may throw up different plans, depending on the data selectivity. So Sql Server may use a non-clustered index that is available or decide to go for a full table scan, even for the same LIKE - depending on what parameter value has been used.

Now compiling the identical queries each time and having the separate plans may be good too as it will be optimized for each statement. The downside is the plan bloat and non-reuse of already compiled plans.

Likewise, when using variables as the predicate parameters, SQL Server deems them to be unsafe, so even if we pass in a highly selective value for the parameter, the optimizer will not use the non- clustered index available and will treat the variable value as "Unknown", and choose an index scan plan. This is because the variable value is not known until runtime to Sql Server.

So using a variable in an sd hoc sql statement fundamentally changes the way Sql Server uses the optimizer!

--------------------------------------------------------------------------------------
Plan Cache pollution with Dynamic String Execution

Even though it is a safe statement, with string concatenation and no strongly type parameter, this will lead to plan cache pollution.

declare @sql nvarchar(4000), @memberId INT = 12345;
select @sql = N'Select * from member where memberId="
                           + CONVERT(nvarchar(10), @memberId);

Exec (@sql);

To make it strongly typed, convert the INT variable back to its data type after the string concatenation within the string like so:

declare @sql nvarchar(4000), @memberId INT = 12345;
select @sql = N'Select * from member where memberId= CONVERT(INT,'
                           + CONVERT(nvarchar(10), @memberId) + N')';

Exec (@sql);

This will result in just one plan, no matter how many times you execute it with different values for the INT parameter. So this is a good way to right dynamic sql with strongly typed parameters.

Avoiding Sql Injection

1. Use QUOTENAME to wrap user supplied parameters to prevent Sql Injection y delimiting it. Only works with sys objects - table names, column names etc with nvarchar 128. 
2. REPLACE all single quotes with double quotes for simple string parameters and delimit the parameters.
3. Make sure that all parameters passed in are validated at the client application layer
4. For Dynamic String Executions in Stored procedure, you can even create a login-less user with very low privileges to execute that DSE within the stored procedure. This is done by restricting the execution context with EXECUTE AS

Sql Plan Cache

Running ad hoc statements (both Sql Server deemed safe and unsafe statements) pollute the plan cache. Running the same statements using sp_ExecuteSql (strongly typed and parameterized) helps avoid plan cache pollution for safe statements. But with unsafe statements, the forced plan caching by sp_ExecuteSql causes inaccurate estimates and related issues.
-------------------------------------------------------------------------------------------
Unlike adhoc statements that will create separate plans for different data types even for the same safe statement, sp_ExecuteSql will force one plan using strongly type parameterization. Consider the 2 batches below using sp_executesql(safe statement in this case):

declare @sql nvarchar(4000)
select @sql = N'Select n.* from member n where n.memberId = @memberId';
EXEC [sp_Executesql] @sql, N'@memberId INT', 123;

declare @sql nvarchar(4000)
select @sql = N'Select n.* from member n where n.memberId = @memberId';
EXEC [sp_Executesql] @sql, N'@memberId INT', 123456789;

Here a single plan will be in the plan cache and the same plan will be used in both the batch executions. So better plan reuse here. The plan like this is very stable and very beneficial to reuse the plan across multiple executions.

The catch is for unsafe or unstable plans, reusing the same plan forced by sql_executesql can actually be detrimental to sql performance!
------------------------------------------------------------------------------------------
From Pinal Dave

http://blog.sqlauthority.com/2010/08/28/sql-server-plan-cache-retrieve-and-remove-a-simple-script/

Running adhoc queries from say SSMS against Production would just pollute the plan cache and can cause cache bloat.
This query captures plans used only once in its lifetime

SELECT [text], cp.size_in_bytes, plan_handle
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype = N'Adhoc'
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;


You can see how much memory is already bloated by not-so-useful queries. If you want to remove any large plan cache which you do not think is useful to you, you can run the following command to remove it:

DBCC FREEPROCCACHE(plan_handle)
------------------------------------------------------------

From http://sqlmag.com/database-performance-tuning/sql-server-plan-cache-junk-drawer-your-queries

Script to find inefficient plans from the cache

--Missing Indexes
;WITH XMLNAMESPACES(DEFAULT
N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT dec.usecounts, dec.refcounts, dec.objtype
  , dec.cacheobjtype, des.dbid, des.text,deq.query_plan
FROM sys.dm_exec_cached_plans AS dec
CROSS APPLY sys.dm_exec_sql_text(dec.plan_handle) AS des
CROSS APPLY sys.dm_exec_query_plan(dec.plan_handle) AS deq
WHERE
deq.query_plan.exist
(N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple
/QueryPlan/MissingIndexes/MissingIndexGroup') <> 0
ORDER BY dec.usecounts DESC

--Convert Issues
;WITH XMLNAMESPACES(DEFAULT
N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
cp.query_hash, cp.query_plan_hash,
ConvertIssue =
  operators.value('@ConvertIssue','nvarchar(250)'),
Expression =
  operators.value('@Expression','nvarchar(250)'),
  qp.query_plan
FROM sys.dm_exec_query_stats cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY query_plan.nodes('//Warnings/PlanAffectingConvert')
  rel(operators)