MERGE statement

http://www.sqlservercentral.com/articles/Stairway+Series/92557/

 

DMV List

SELECT
*
FROM sys.system_objects
WHERE name LIKE ‘dm_%’
AND type = ‘V’
ORDER BY name

 

Lock escalation

http://aboutsqlserver.com/2012/01/11/locking-in-microsoft-sql-server-part-12-lock-escalation/

 

DBCC Page

http://www.mssqltips.com/sqlservertip/1578/using-dbcc-page-to-examine-sql-server-table-and-index-data/

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/12/13/more-undocumented-fun_3a00_-dbcc-ind_2c00_-dbcc-page_2c00_-and-off_2d00_row-columns.aspx

To figure out which objects the page belongs to, look for Metadata: ObjectId in the Header section of DBCC page. Use that object id to plug into the following command to obtain the object.

SELECT OBJECT_NAME(ObjectId)

 

 

Statistics

STATS_DATE
Returns the date of the most recent update for statistics on a table or indexed view.

USE AdventureWorks2012;
GO
SELECT name AS stats_name, 
    STATS_DATE(object_id, stats_id) AS statistics_update_date
FROM sys.stats 
WHERE object_id = OBJECT_ID('Person.Address');
GO

If statistics correspond to an index, the stats_id value in the sys.stats catalog view is the same as the index_id value in the sys.indexes catalog view, and the following query returns the same results as the preceding query. If statistics do not correspond to an index, they are in the sys.stats results but not in the sys.indexes results.

USE AdventureWorks2012;

GO
SELECT name AS index_name, 
    STATS_DATE(object_id, index_id) AS statistics_update_date
FROM sys.indexes 
WHERE object_id = OBJECT_ID('Person.Address');
GO

http://technet.microsoft.com/en-us/library/ms190330.aspx

 

DROP_EXISTING Clause of CREATE index

Rebuild the Clustered Index in a Single Step

Because of the dependency of nonclustered indexes on the clustered index, rebuilding the clustered index as separate DROP INDEX AND CREATE statements causes all the nonclustered indexes to be rebuilt twice. To Avoid this, use the DROP_EXISTING clause of the CREATE INDEX statement to rebuild the clustered index in a single atomic step. Similary, you can also use the DROP_EXISTING clause with a nonclustered index.

 

Querying Plan Cache

SELECT [cp].[refcounts] ,
[cp].[usecounts] ,
[cp].[objtype] ,
[st].[dbid] ,
[st].[objectid] ,
[st].[text] ,
[qp].[query_plan],
cp.plan_handle
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp;

where the value in cp.plan_handle can be used to single out a particular plan and use that value to clear cache for just 1 proc..

example dbcc freeproccache(plan_handle)

— From Sql Server Execution Plans by Grant Fritchley, page 52

 

Indexes

Heap: A crude stack of data with a row identifier as a pointer to the storage location. This data is not ordered or searchable except by walking through the adta, row by row- scan.

When a clustered index is placed on a table, the key values of the index establish an order for the data. Further, with a clustered index, the data is stored with the index so that the data itself is now ordered. When a clustered index is present, the pointer on the non clustered index consists of the values that define the clustered index.

Tables with indexes require more storage and memory space for the index pages in addition to the data pages of the table. DML statements take longer and more processing time is required to maintain the indexes of constantly changing tables. This is because, unlike a SELECT statement, data manipulation queries modify the data content of a table. If an INSERT statement adds a row to a table, then it also has to add a row in the index structure. If the index is a clustered index, the overhead is greater still, because the row has to be added to the data pages themselves in thr right order, which may require other data rows to be repositioned below the entry position of the new row.

CLUSTERED INDEX
The main difference between the two types (clustered and non clustered index) is that the leaf pages in a clustered index are the data pages of the table and are therefore in the same order as the data to which they point. This means that the clustered index IS the table.The leaf pages of a clustered index and the data pages of the table the index is on, are one and the same. Because of this, table rows are physically sorted on the clustered index column, and since there can only be one physical order of the table data, a table can have only one clustered index.

Very good article about indexes and covering indexhttp://www.simple-talk.com/sql/learn-sql-server/using-covering-indexes-to-improve-query-performance/

 

Spatial datatypes and index

• Spatial index can be based on only a single column of spatial data type
• The table must have a primary key before you can create a spatial index
• Geography and geometry are spatial data types
• STDistance function is a geography function that returns the shortest distance between a point in a geography instance and a point in another geography instance – benefits from a spatial index

 

Performance Data Warehouse

Data collectors for performance data warehouse is configured using one of the following 4 collector types

• T-SQL Query
• SQL Trace
• Query Activity
• Performance Counter

They are based on SSIS packages and SQL Agent jobs

T-SQL Query collector – allows you to specify SELECT statements to execute, as well as the database to execute the query against. Results stored within a table (output table parameter of data collector). The following are requirements of the result set

• result set does not  contain data collector reserved words e.g. snapshot_id, database_name, snapshot_time etc
• All columns in the result set must have a name
• image, text, xml data types cannot be included
• only a single result set is returned

SQL Trace Collector – supports either the default trace or user-defined trace. Results of the trace are written to a file and Data Collector uses the fn_trace_getTable to extract contents. Results of the data collection are stored in snapshots.trace_info and snapshots.trace_data tables in the performance data warehouse.

Peformance Counter Collector – allows you to define any combination of objects, counters and counter instances. Results of data collection stored in the snapshots.performance_counters table in performance data warehouse

Query Activity Collector – gathers information from sys.dm_exec_requests , sys.dm_exec_sessions, and sys.dm_exec_query_stats