70-464 Developing Microsoft SQL Server Databases

Hi, My name is Jimmy Sam. If you need DBA help, please email to jimmy_sam001@yahoo.com for further discussion. Thanks.
mssql certification 70-464     70-464     20464     20464 w     ppt pdf     aiotestking     online test     70-461     70-462     70-463     70-465     
clr  10776A module 16 CLR, Managed Code xml  10776A module 17,18 spatial  10776A module 19 fulltext  10776A module 20

columnstore  20464B module 6 memory_optimized, in-memory table  20464B module 11 native_compiled, in-memory related  20464B module 11 
filestream and filetable  20464B module 15 extended_events_session  20762B Module 18

mssqltips 70464 
10776A 
20464D: Developing Microsoft SQL Server 2014 Databases 
55144B: SQL Server 2014 Performance Tuning and Optimization 
20762B: Developping SQL Server Database 
OD20762A: OnDemand Developping SQL Server Database 

simon _ 

encryption 
sql_profiler 
stats 
buildzaure  
1. Tables & Views
2. Indexes
3. Procedures & Functions
4. Transaction
5. in-momory objects
6. Query Tuning

1. Implement Database Objects
2. Implement Programming Objects
3. Design Database Objects
4. Optimization and Troubleshooting Queries

top   prev   next  

Quick Search



 
    search term:       

        




top   prev   next  

Implement database objects



Implement database objects (30%-35%)

Tables And Columns
Create and alter tables
Develop an optimal strategy for using temporary objects, including table variables and temporary tables; 
define alternatives to triggers; 
define data version control and management; 
implement @Table and #table appropriately; 
create calculated columns; 
implement partitioned tables, schemas, and functions; 
implement column collation; 
implement online transaction processing (OLTP); 
implement columnstore and sparse columns

Logins And User Security
Design, implement, and troubleshoot security
Implement data control language statements appropriately, 
troubleshoot connection issues, 
implement execute as statements, 
implement certificate-based security, 
create loginless users, 
define appropriate database roles and permissions, 
implement contained users, 
implement cross db ownership chaining, 
implement schema security, 
implement server roles

Locking And Concurrency
Design the locking granularity level
Choose the right lock mechanism for a given task; 
handle deadlocks; 
design index locking properties; 
fix locking and blocking issues; 
analyze a deadlock scenario; 
design appropriate isolation level, including Microsoft ActiveX data objects defaults; 
design for locks and lock escalation; 
design transactions that minimize locking; 
reduce locking contention; 
identify bottlenecks in data design; 
design appropriate concurrency control, such as pessimistic or optimistic

Indexes
Implement indexes
Inspect physical characteristics of indexes and perform index maintenance; 
identify unused indexes; 
implement indexes; 
optimize indexes, including full, filter, statistics, and force 

Data Types
Implement data types
Select appropriate data types, including BLOBs, GUIDs, XML, and spatial data; 
develop a Common Language Runtime (CLR) data type; 
implement appropriate use of @Table and #table; 
determine values based on implicit and explicit conversions

Constraints
Create and modify constraints
Create constraints on tables, 
define constraints, 
modify constraints according to performance implications, 
implement cascading deletes, 
configure constraints for bulk inserts

Preparation resources
CREATE TABLE (Transact-SQL) 
CREATE USER (Transact-SQL) 
SET TRANSACTION ISOLATION LEVEL (Transact-SQL) 
data type 


top   prev   next  

Implement programming objects


Implement programming objects (15-20%)
stored procedures
Design and implement stored procedures
Create stored procedures and other programmatic objects; 
implement different types of stored procedure results; 
create a stored procedure for the data access layer; 
analyze and rewrite procedures and processes; 
program stored procedures by using T-SQL and CLR; 
implement parameters, including table valued, input, and output; 
implement error handling, including TRY - CATCH; 
configure appropriate connection settings

functions
Design T-SQL table-valued and scalar functions
Modify scripts that use cursors and loops into a SET-based operation, 
design deterministic and non-deterministic functions

Create, use, and alter user-defined functions (UDFs)
Implement deterministic or non-deterministic functions; 
implement CROSS APPLY by using UDFs; 
implement CLR functions

Views
Create and alter views
Set up and configure partitioned tables and partitioned views; 
create indexed views

Preparation resources
sys.dm_os_volume_stats (Transact-SQL

SQL Server agent stored procedures (Transact-SQL)

Processing stored procedure results


top   prev   next  

Design Database Objects


Design database objects (25 ~ 30%)
Design tables
Apply data design patterns; 
develop appropriately normalized and de-normalized SQL tables; 
design transactions; 
design views; 
implement GUID as a clustered index appropriately; 
design temp tables appropriately, including # vs. @; 
design an encryption strategy; 
design table partitioning; 
design a BLOB storage strategy, including filestream and filetable; 
design tables for In-Memory OLTP

Design for concurrency
Concurrency, locking, isolation level
Develop a strategy to maximize concurrency; 
define a locking and concurrency strategy; 
design a transaction isolation strategy, including server database and session; 
design triggers for concurrency

Design indexes
Design indexes and data structures; 
design filtered indexes; 
design an indexing strategy, including column store, semantic indexes, and INCLUDE; 
design statistics; 
assess which indexes on a table are likely to be used, given different search arguments (SARG); 
design spatial and XML indexes

Design data integrity
Constraints
Design a table data integrity policy, 
including checks, 
primary key, 
foreign key, 
uniqueness, 
XML schema, 
and nullability; 
select a primary key

Design for implicit and explicit transactions
implicit and explicit transactions
Manage transactions; 
ensure data integrity by using transactions; 
manage distributed transaction escalations; 
design savepoints; 
design error handling for transactions, including TRY, CATCH, and THROW

Preparation resources
SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

CREATE INDEX (Transact-SQL)

Transaction Statements (Transact-SQL)


top   prev   next  

Optimize and troubleshoot queries


Optimize and troubleshoot queries (25 ~ 30%)
Optimize and tune queries
Tune a poorly performing query, including avoiding unnecessary data type conversions;
identify long-running queries; 
review and optimize code; 
analyze execution plans to optimize queries; 
tune queries using execution plans and Microsoft Database Tuning Advisor (DTA); 
optimize queries using pivots and common table expressions (CTE); 
design database layout to optimize queries; 
implement query hints; 
tune query workloads; 
implement recursive CTE; 
implement full text and semantic search; 
analyze execution plans; 
implement plan guides

Troubleshoot and resolve performance problems
Interpret performance monitor data; 
integrate performance monitor data with SQL Traces

Optimize indexes
Develop an optimal strategy for clustered indexes; 
analyze index usage; 
optimize indexes for workload, including data warehousing and OLTP; 
generate appropriate indexes and statistics by using INCLUDE columns; 
create filtered indexes; 
implement full-text indexing; 
implement columnstore indexes; 
optimize online index maintenance

Capture and analyze execution plans
Collect and read execution plans, 
create an index based on an execution plan, 
batch or split implicit transactions, 
split large queries, 
consolidate smaller queries, 
review and optimize parallel plans

Collect performance and system information
Monitor performance using Dynamic Management Views, 
collect output from the Database Engine Tuning Advisor, 
design Extended Events Sessions, 
review and interpret Extended Event logs; 
optimize Extended Event session settings, 
use Activity Monitor to minimize server impact and determine IO bottlenecks, 
monitor In-Memory OLTP resources

Preparation resources
Database Engine Tuning Advisor
DBCC SHRINKFILE (Transact-SQL)
Create indexes with included columns


top   prev   next  

Plan Guide


EXEC sp_create_plan_guide 
    @name = N'Guide1', 
    @stmt = N'SELECT TOP 1 * 
              FROM Sales.SalesOrderHeader 
              ORDER BY OrderDate DESC', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (MAXDOP 1)';

Plan Guide
@name: Plan Guide Name
@stmt: sql statement
@type: Objects, SQL
@module: NULL, procedure_name
@params: NULL, binding_variable @0, @1
@hints: @xml_showplan N'OPTION (MAXDOP 1)'  

N'OPTION (MAXDOP 1)'
N'OPTION (HASH JOIN)' 
N'OPTION (PARAMETERIZATION FORCED)'
N'OPTION (TABLE HINT(HumanResources.Employee, FORCESEEK))'
N'OPTION (TABLE HINT(e,INDEX(IX_Employee_OrganizationLevel_OrganizationNode)),TABLE HINT(c,FORCESEEK))'
N'OPTION (TABLE HINT(e,INDEX(IX_Employee_OrganizationLevel_OrganizationNode), NOLOCK, FORCESEEK ))';
N'OPTION (TABLE HINT ( e, NOLOCK))';
N'OPTION (TABLE HINT(e))'

Using the INDEX and FORCESEEK Query Hints in Plan Guides

How to: Create a Plan Guide (SQL Server Management Studio)

Understanding Plan Guides

How SQL Server Matches Plan Guides to Queries



top   prev   next  

TYPE

CREATE TYPE SessionDataTable AS Table



top   prev   next  

RULE

create rule transaction sql 

CREATE RULE range_rule  
AS   
@range>= $1000 AND @range <$20000;  

CREATE RULE list_rule  
AS   
@list IN ('1389', '0736', '0877');  

CREATE RULE pattern_rule   
AS  
@value LIKE '__-%[0-9]'  

sp_bindrule 

-- today is a rule name
USE master;  
GO  
EXEC sp_bindrule 'today', 'HumanResources.Employee.HireDate';  

sp_help
sp_helptext 
sp_rename

A rule cannot be bound to a text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, CLR user-defined type, or timestamp column. 
A rule cannot be bound to a computed column. 

-- The rule will allow only data in the form 123 456-7890 or UNKNOWN.
CREATE RULE phone_rule
AS
(@phone='UNKNOWN') OR (LEN(@phone)=12 AND
ISNUMERIC(LEFT(@phone,3))=1
AND SUBSTRING(@phone,4,1)=' '
AND ISNUMERIC(SUBSTRING(@phone,5,3))=1
AND SUBSTRING(@phone,8,1)='-'
AND ISNUMERIC(RIGHT(@phone,4))=1 )

 
-- 1. Binding the rule to the column:
EXEC sp_bindrule 'phone_rule', 'authors.phone'

-- 2. Unbinding the rule (before dropping):
EXEC sp_unbindrule 'authors.phone'

-- 3. Dropping the rule:
DROP RULE phone_rule



top   prev   next  

Query Hints


table hints 

join hints 

query hints 

-- updlock
SELECT Name AS EmployeeName FROM HR.Employees WITH ( UPDLOCK) WHERE ID = @MYID;

-- updlock, readpast, 
-- with update lock for selected rows, 
-- but if a row is locked by others, skip it instead of waiting for release
SELECT TOP 1 @queueid = QUEUEID FROM DBO.QUEUE 
WITH (updlock, readpast);

-- force to using a specific index
SELECT StartDate, ComponentID 
FROM Production.BillOfMaterials  
WITH( INDEX (FIBillOfMaterialsWithComponentID) )  
WHERE ComponentID in (533, 324, 753, 855, 924);  
GO  

-- for table level lock
UPDATE Production.Product  
WITH (TABLOCK)  
SET ListPrice = ListPrice * 1.10  
WHERE ProductNumber LIKE 'BK-%';  
GO  

-- force index
SELECT *  
FROM Sales.SalesOrderHeader AS h  
INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESEEK)  
    ON h.SalesOrderID = d.SalesOrderID   
WHERE h.TotalDue > 100  
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);  
GO 

-- for using index
SELECT h.SalesOrderID, h.TotalDue, d.OrderQty  
FROM Sales.SalesOrderHeader AS h  
    INNER JOIN Sales.SalesOrderDetail AS d   
    WITH (FORCESEEK (PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID (SalesOrderID)))   
    ON h.SalesOrderID = d.SalesOrderID   
WHERE h.TotalDue > 100  
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);   
GO

-- force a full table scan
SELECT h.SalesOrderID, h.TotalDue, d.OrderQty  
FROM Sales.SalesOrderHeader AS h  
    INNER JOIN Sales.SalesOrderDetail AS d   
    WITH (FORCESCAN)   
    ON h.SalesOrderID = d.SalesOrderID   
WHERE h.TotalDue > 100  
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);  

-- in-memory db
SELECT * FROM dbo.Customers AS c   WITH (SNAPSHOT)   
LEFT JOIN dbo.[Order History] AS oh ON c.customer_id=oh.customer_id;



top   prev   next  

Select Statement

-- offset and fetch next 
select JobTitle, loginID 
from humanresources.exmployee 
order by hiredate
offset 10 rows fetch next 5 rows only;

-- change collate
SELECT Name COLLATE Arabic_CI_AI From Profuction.Product;

SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS



top   prev   next  

Sequence

-- sequence

CREATE SEQUENCE MySchema.MySeq1  
    START WITH 1  
    INCREMENT BY 1 ;  
GO  
SELECT NEXT VALUE FOR MySchema.MySeq1 AS MyCurrSeq1;  

CREATE SEQUENCE Test.TestSeq  
    AS int  
    START WITH 1  
    INCREMENT BY 1 ;  
GO  

CREATE TABLE Test.MyTable  
(  
    IDColumn nvarchar(25) PRIMARY KEY,  
    name varchar(25) NOT NULL  
) ;  
GO 

ALTER TABLE Test.MyTable  
    ADD   
        DEFAULT N'AdvWorks_' +   
        CAST(NEXT VALUE FOR Test.CounterSeq AS NVARCHAR(20))   
        FOR IDColumn;  
GO  

next value for 





top   prev   next  

Sparse Column



Use Sparse Columns

CREATE TABLE DocumentStore  
    (DocID int PRIMARY KEY,  
     Title varchar(200) NOT NULL,  
     ProductionSpecification varchar(20) SPARSE NULL,  
     ProductionLocation smallint SPARSE NULL,  
     MarketingSurveyGroup varchar(20) SPARSE NULL ) ;  
GO 

A sparse column must be nullable and cannot have the ROWGUIDCOL or IDENTITY properties. 
A sparse column cannot be of the following data types: 
text, ntext, image, timestamp, user-defined data type, geometry, or geography; 
or have the FILESTREAM attribute.

A sparse column cannot have a default value.

A sparse column cannot be bound to a rule.

Although a computed column can contain a sparse column, 
a computed column cannot be marked as SPARSE.

A data mask can be defined on a sparse column, 
but not on a sparse column that is part of a column set.

Merge replication does not support sparse columns or column sets.

The sparse property of a column is not preserved when the table is copied





top   prev   next  

cast and convert


CAST is a SQL standard whereas CONVERT is an addition to the T-SQL dialect. 
you should use CAST unless you need to make use of the CONVERT function's formatting functionality.
cast (@s as xml)
convert (xml, @s)

-- Syntax for CAST:  
CAST ( expression AS data_type [ ( length ) ] )  

-- Syntax for CONVERT:  
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )  

SELECT  CAST(10.6496 AS int) as trunc1,
        CAST(-10.6496 AS int) as trunc2,
        CAST(10.6496 AS numeric) as round1,
        CAST(-10.6496 AS numeric) as round2;
10 -10 11 -11

SELECT   
   GETDATE() AS UnconvertedDateTime,  
   CAST(GETDATE() AS nvarchar(30)) AS UsingCast,  
   CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601  ;  
GO

SELECT   
   '2006-04-25T15:50:59.997' AS UnconvertedText,  
   CAST('2006-04-25T15:50:59.997' AS datetime) AS UsingCast,  
   CONVERT(datetime, '2006-04-25T15:50:59.997', 126) AS UsingConvertFrom_ISO8601 ;  
GO  

SELECT CAST(10.3496847 AS money);


cast and convert 
Implicit and explicit conversions
Implicit conversions can be costly and they should be avoided if possible.





top   prev   next  

Data Type


-- Data Type:
http://msdn.microsoft.com/en-us/library/msl76089.aspx 

http://msdn.microsoft.com/en-us/library/msl87745.aspx



TinyInt: 0 to 255
SmallInt: -32,768 to 32,767

Int: -2,147,483,648 to 2,147,483,647
int: -2 A 31 (-2,147,483,648) to 2 A 31-1 (2,147,483,647) (just 10 digits max) 

BigInt: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
bigint: -2 A 63 (-9,223,372,036,854,775,808) to 2 A 63-1 (9,223,372,036,854,775,807) 

Decimal: - 99,999,999,999,999,999,999,999,999,999,999,999,999 to 99,999,999,999,999,999,999,999,999,999,999,999,999 using decimal(38,0)


rowversion: timestamp 
CREATE TABLE Table1 
(
ID int IDENTITY(1,1),
Name varchar(100),
Version rowversion
)

http://msdn.microsoft.com/en-us/library/ms182776.aspx
http://msdn.microsoft.com/en-us/library/ms187942.aspx
http://msdn.microsoft.com/en-us/library/ms190348.aspx





top   prev   next  

Spatial Data Type


spatial data types overview 

Spatial Data Type: geometry 

stastext 

stequals 





top   prev   next  

xml


xml 10 series 

xml data type
xml data type: The @xmlDocument can also be of xml type or of (n)varchar(max) type. 
[n]varchar(max)
varbinary(max)

Create Instances of XML Data 
xml:
<Project>
<Tasks>
<IsFinished>true</IsFinished>
</Tasks>
</Project>


XQuery Language Reference (SQL Server) 


query() Method (xml Data Type) 

value() Method (xml Data Type) 

exist() Method (xml Data Type) 

modify() Method (xml Data Type) 

nodes() Method (xml Data Type) 

OPENXML (SQL Server) 

@mp:id 

value() and nodes() 

SELECT T2.Loc.query('.')  
FROM   T  
CROSS APPLY Instructions.nodes('/root/Location') as T2(Loc)  

-- IsFinished is an element
SELECT Projects.details.query ( '//Task/IsFinished="True"')
From Projects WHERE Projects.Id = 1;

simple talk 

xml nodes vs. openxml 

OPENXML (Transact-SQL) 

openxml examples 

Replacing OPENXML with the XML nodes() Function in SQL Server 2005
Prior to SQL Server 2005 we have always used the OPENXML rowset provider to parse the XML and produce a rowset.

DECLARE @xmlString XML = N'123456'

SELECT doc.col.value('./text()[1]','INT') AS A
FROM @xmlString.nodes('/TAG1/A')   doc(col)

sp_xml_preparedocument ( @XmlXmlDocumentHandle OUTPUT, @XmlDocument )
sp_xml_preparedocument 

OPENXML

 Selective XML Indexes (SXI) 
CREATE SELECTIVE XML INDEX SXI_index  
ON Tbl(xmlcol)  
FOR   
(  
    pathTitle = '/book/title/text()' AS XQUERY 'xs:string',   
    pathAuthors = '/book/authors' AS XQUERY 'node()',  
    pathId = '/book/id' AS SQL NVARCHAR(100)  
)  

https://www.simple-talk.com/sql/learn-sql-server/introduction-to-xml-schema/

open xml: example
https://docs.microsoft.com/en-us/sql/relational-databases/xml/examples-using-openxml

an XML schema collection
http://www.idera.com/glossary/xml-schema-collection
http://www.yaldex.com/sql_server/progsqlsvr-CHP-7-SECT-6.html
https://dba.stackexchange.com/questions/141297/how-to-script-xml-schema-collection-in-sql-server-2012/141308
https://sqluninterrupted.com/2012/02/22/xml-schema-collections-in-sql-server-2/
https://docs.microsoft.com/en-us/sql/relational-databases/xml/xml-schema-collections-sql-server
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-xml-schema-collection-transact-sql



top   prev   next  

fulltext search

CREATE UNIQUE INDEX ui_ukJobCand ON HumanResources.JobCandidate(JobCandidateID);

CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume) 
   KEY INDEX ui_ukJobCand 
   WITH STOPLIST = SYSTEM;
GO
fulltext catalog and index 

simple talk 
sqlmag 


full text search 

create fulltext index 



top   prev   next  

SELECT ... FOR XML, stuff(), text(), data()


SELECT ... FOR XML
https://msdn.microsoft.com/en-us/library/ms345137(v=sql.90).aspx

SELECT * FROM Customers FOR XML AUTO, TYPE;

data()
text()
When you use text(), the value is embedded as plain text between the nodes, 
without text() you get the value as an XML element.

text() 

-- format transformation, stuff()
SELECT (SELECT * FROM Customers FOR XML AUTO, TYPE).query(
'{
   for $c in /Customers
   return 
     
 }')


https://blog.sqlauthority.com/2009/11/25/sql-server-comma-separated-values-csv-from-table-column/




top   prev   next  

Encryption



IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = '23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
GO

CREATE CERTIFICATE Sales09
WITH SUBJECT = 'Customer Credit Card Numbers';
GO

CREATE SYMMETRIC KEY CreditCards_Key11
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Sales09;
GO
-- Create a column in which to store the encrypted data.
ALTER TABLE Sales.CreditCard
ADD CardNumber_Encrypted varbinary (128);
GO

-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY CreditCards_Key11 DECRYPTION BY CERTIFICATE Sales09;
-- Encrypt the value in column CardNumber using the
-- symmetric key CreditCards_Key11.
-- Save the result in column CardNumber_Encrypted.

UPDATE Sales.CreditCard
SET CardNumber_Encrypted = EncryptByKey (Key_GUID ('CreditCards_Key11')
, CardNumber, 1, HashBytes ('SHA1', CONVERT (varbinary
, CreditCardID)));
GO

CREATE SYMMETRIC KEY Key1 WITH ALGORITHM = SHA1 ENCRYPTION BY CERTIFICATE Cert1;
ALTER TABLE Table1 ADD Column2 varbinary(256);
OPEN SYMMETRIC KEY Key1 DECRYPTION BY CERTIFICATE Cert1;
Update Table1 SET Column2 = EncryptByKey ( Key_GUID('Key1'), COlumn1);



top   prev   next  

SHOWPLAN_XML


1. Display Estimated Execution Plan
2. Include Actual Execution Plan

SET SHOWPLAN_TEXT ON
SET SHOWPLAN_TEXT OFF

SET SHOWPLAN_XML  ON
SET SHOWPLAN_XML  OFF

SET STATISTICS PROFILE ON
SET STATISTICS PROFILE OFF

SET STATISTICS XML ON
SET STATISTICS XML OFF

SET STATISTICS TIME ON
SET STATISTICS TIME OFF 

SET STATISTICS IO ON;
GO
SET STATISTICS IO OFF;
GO

execute plans 



top   prev   next  

Sql Server Tuning Advisor

http://www.databasejournal.com/features/mssql/getting-starting-with-database-engine-tuning-advisor-in-sql-server-part-1.html

https://technet.microsoft.com/en-us/library/ms170096(v=sql.105).aspx

https://blog.sqlauthority.com/2015/05/11/sql-server-introduction-to-database-engine-tuning-advisor-a-k-a-dta/

Start and Use the Database Engine Tuning Advisor
https://docs.microsoft.com/en-us/sql/relational-databases/performance/start-and-use-the-database-engine-tuning-advisor

https://www.mssqltips.com/sqlservertip/1872/sql-server-database-engine-tuning-advisor-for-performance-tuning/

https://www.mssqltips.com/sqlservertutorial/286/database-engine-tuning-advisor/
Partition Recommendations
Index Recommendations
Index View Recommendations
CREATE STATISTICS Recommentations

https://docs.microsoft.com/en-us/sql/relational-databases/statistics/statistics
https://docs.microsoft.com/en-us/sql/relational-databases/statistics/delete-statistics

Limitation:Database Engine Tuning Advisor does not do the following:
1. Recommend indexes on system tables.
2. Add or drop unique indexes or indexes that enforce PRIMARY KEY or UNIQUE constraints.
3. Tune single-user databases.
4. Database Engine Tuning Advisor does recommend unique clustered indexes on views 
   when indexed views are part of its recommendation.

1. Performance Tools, and then click Database Engine Tuning Advisor. 
2. SQL Server Management Studio Tools menu, click Database Engine Tuning Advisor
3. the SQL Server Management Studio Query Editor 
   Select a query in the Transact-SQL script, or select the entire script, 
   right-click the selection, and choose Analyze Query in Database Engine Tuning Advisor. 
   The Database Engine Tuning Advisor GUI opens and imports the script as an XML file workload. 
   You can specify a session name and tuning options to tune the selected Transact-SQL queries 
   as your workload.
4. SQL Server Profiler Tools menu, click Database Engine Tuning Advisor

-- -ip: Top 1000 plan cache events
dta -E -D DatabaseName -ip -s SessionName 
 
-- -ip: Top 2000 plan cache events
dta -E -D DatabaseName -ip .n 2000-s SessionName1 

-- -ipf: To analyze events for all databases in the instance
dta -E -D DatabaseName -ip .ipf .n 2000 -s SessionName2  

-- -if: To tune a database by using a workload and dta utility default settings
dta -E -D DatabaseName -if WorkloadFile -s SessionName  

--  If you do not need to examine the results before implementing them, 
    you can use the -a option with the dta command prompt utility. 
    dta -E -D DatabaseName -if WorkloadFile -s SessionName -a  

-- Apply Recommendations on the Actions menu ( Apply now or Schedule for later )



top   prev   next  

Sql Server Profiler

sp_trace_create
sp_trace_setevent
simple talk 

codeproject 

youtube 
_ 

sql server profiler 
start sql server profiler 

Start SQL Server Profiler
1. Performance Tools --> SQL Server Profiler. 
2. Database Engine Tuning Advisor --> Tools --> SQL Server Profiler
3. SQL Server Management Studio --> Tools --> SQL Server Profiler
4. SSMS --> Query Editor --> right-click and then select Trace Query in SQL Server Profiler
5. Activity Monitor --> Click the Processes pane, 
   right-click the process that you want to profile, 
   and then click Trace Process in SQL Server Profiler. 


view a trace  

View and Analyze Traces with SQL Server Profiler

SELECT  TextData, Duration, CPU  
FROM    trace_table_name  
WHERE   EventClass = 12 -- SQL:BatchCompleted events  
AND     CPU < (Duration * 1000)  


sys.fn_trace_getinfo ( { trace_id | NULL | 0 | DEFAULT } )    

sys.fn_trace_gettable ( 'filename' , number_files )  

sys.fn_trace_getfilterinfo  

sys.fn_trace_geteventinfo  

SELECT * FROM fn_trace_geteventinfo(2) ;  
GO  

open a trace file 

open a trace table 

create a script 

Analyze Deadlocks with SQL Server Profiler 

SQL Server Distributed Replay ( SQL Server Profiler Trace Replay ) 





top   prev   next  

Optimize for Ad hoc Workloads


Optimize for Ad hoc Workloads
-- plan_hash vs. plan_hash_stub



top   prev   next  

Plan Cache

Clean Plan Cache
-- Show query plan for UK
DBCC FREEPROCCACHE
GO




top   prev   next  

An Extended Events session



GUI:
SSMS --> Management --> Extented Events --> Sessions --> 1. AlwaysOn_Health 2.system_health

a Wizard, 
an Extended Events Properties Editor, 
a Data Viewer.

right-click the session 
and select Stop Session from the context menu. 
Right-click the Session again and select Properties, and the Editor opens,

For different types of Events, Targets, Filters, and Actions.

to configure those events with Actions, Filters, and Event Fields.

gui tools for extended event 

video on youtube

CREATE EVENT SESSION [system_health] ON SERVER
ADD EVENT sqlclr.clr_allocation_failure(
    ACTION(package0.callstack,sqlserver.session_id)),
ADD EVENT sqlclr.clr_virtual_alloc_failure(
    ACTION(package0.callstack,sqlserver.session_id)),
ADD EVENT sqlos.memory_broker_ring_buffer_recorded,
ADD EVENT sqlos.memory_node_oom_ring_buffer_recorded(
    ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack)),
ADD EVENT sqlos.scheduler_monitor_deadlock_ring_buffer_recorded,
ADD EVENT sqlos.scheduler_monitor_non_yielding_iocp_ring_buffer_recorded,
ADD EVENT sqlos.scheduler_monitor_non_yielding_ring_buffer_recorded,
ADD EVENT sqlos.scheduler_monitor_non_yielding_rm_ring_buffer_recorded,
ADD EVENT sqlos.scheduler_monitor_stalled_dispatcher_ring_buffer_recorded,
ADD EVENT sqlos.scheduler_monitor_system_health_ring_buffer_recorded,
ADD EVENT sqlos.wait_info(
    ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([duration]>(15000) AND 
          ([wait_type]>(31)   AND 
          ([wait_type]>(47) AND [wait_type]<(54) 
        OR [wait_type]<(38) 
        OR [wait_type]>(63) AND [wait_type]<(70) 
        OR [wait_type]>(96) AND [wait_type]<(100) 
        OR [wait_type]=(107) 
        OR [wait_type]=(113) 
        OR [wait_type]>(174) AND [wait_type]<(179) 
        OR [wait_type]=(186) 
        OR [wait_type]=(207) 
        OR [wait_type]=(269) 
        OR [wait_type]=(283) 
        OR [wait_type]=(284)) 
      OR [duration]>(30000) AND [wait_type]<(22)))),
ADD EVENT sqlos.wait_info_external(
    ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([duration]>(5000) AND ([wait_type]>(365) AND [wait_type]<(372) 
        OR [wait_type]>(372) AND [wait_type]<(377) 
        OR [wait_type]>(377) AND [wait_type]<(383)
        OR [wait_type]>(420) AND [wait_type]<(424)
        OR [wait_type]>(426) AND [wait_type]<(432)
        OR [wait_type]>(432) AND [wait_type]<(435)
        OR [duration]>(45000) AND ([wait_type]>(382) AND [wait_type]<(386) 
        OR [wait_type]>(423) AND [wait_type]<(427)
        OR [wait_type]>(434) AND [wait_type]<(437)
        OR [wait_type]>(442) AND [wait_type]<(451)
        OR [wait_type]>(451) AND [wait_type]<(473)
        OR [wait_type]>(484) AND [wait_type]<(499)
        OR [wait_type]=(365) 
        OR [wait_type]=(372) 
        OR [wait_type]=(377) 
        OR [wait_type]=(387) 
        OR [wait_type]=(432)
        OR [wait_type]=(502))))),
ADD EVENT sqlserver.connectivity_ring_buffer_recorded(SET collect_call_stack=(1)),
ADD EVENT sqlserver.error_reported(
    ACTION(package0.callstack,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack)
    WHERE ([severity]>=(20) 
       OR ([error_number]=(17803) 
       OR  [error_number]=(701) 
       OR  [error_number]=(802)
       OR  [error_number]=(8645)
       OR  [error_number]=(8651)
       OR  [error_number]=(8657) 
       OR [error_number]=(8902)))),
ADD EVENT sqlserver.security_error_ring_buffer_recorded(SET collect_call_stack=(1)),
ADD EVENT sqlserver.sp_server_diagnostics_component_result(SET collect_data=(1)
    WHERE ([sqlserver].[is_system]=(1) AND [component]<>(4))),
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'system_health.xel',max_file_size=(5),max_rollover_files=(4)),
ADD TARGET package0.ring_buffer(SET max_events_limit=(5000),max_memory=(4096))
WITH (MAX_MEMORY=4096 KB,
      EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
      MAX_DISPATCH_LATENCY=120 
      SECONDS,
      MAX_EVENT_SIZE=0KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

-- ------------------------------------------------------------------------------

The Extended Event GUI
http://jasonbrimhall.info/2015/09/25/the-extended-event-gui/

https://blogs.msdn.microsoft.com/extended_events/

https://www.mssqltips.com/sqlservertip/2731/managing-sql-server-extended-events-in-management-studio/

https://www.mssqltips.com/sql-server-tip-category/99/extended-events/

-- ------------------------------------------------------------------------------

Brent Ozar On extended events 

The following only available in extended event, not in profiler
1. Availability Groups: Track when a replica's state changes
2. BUffer Pool Extensions: Track when pages are read or evicted, Track the PLE
3. Columnstore Indexes: Count reads and readaheads
4. Filestream: See access requests - and failures
5. In-Memory OLTP
6. Windows Azure Storage: Reads complete - or fail

7. async_io request
8. checkpoint
9. latch


CREATE EVENT SESSION [Query Performance Metrics] ON SERVER 
ADD EVENT sqlserver.rpc_completed
GO

CREATE EVENT SESSION blocked_process_test ON SERVER 
ADD EVENT sqlserver.blocked_process_report


CREATE EVENT SESSION waits ON SERVER
ADD EVENT sqlos.wait_info (
	WHERE (sqlserver.session_id = 58))
ADD TARGET package0.event_file
    (SET FILENAME = 'C:\data\waits.xel')

ALTER EVENT SESSION waits ON SERVER STATE = START
GO
ALTER EVENT SESSION waits ON SERVER STATE = STOP

SELECT *, CAST(event_data AS XML) AS 'event_data'
FROM sys.fn_xe_file_target_read_file('C:\data\waits*.xel', NULL, NULL, NULL)

CREATE TABLE #waits (
    event_data XML)
GO
INSERT INTO #waits (event_data)
SELECT CAST (event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file (
    'C:\data\waits*.xel', NULL, NULL, NULL)

-- -------------------------------------------
SELECT
    waits.wait_type,
    COUNT (*) AS wait_count,
    SUM (waits.duration) AS total_wait_time_ms,
    SUM (waits.duration) - SUM (waits.signal_duration) AS total_resource_wait_time_ms,
    SUM (waits.signal_duration) AS total_signal_wait_time_ms
FROM 
    (SELECT
        event_data.value ('(/event/@timestamp)[1]', 'DATETIME') AS datetime,
        event_data.value ('(/event/data[@name=''wait_type'']/text)[1]', 'VARCHAR(100)') AS wait_type,
        event_data.value ('(/event/data[@name=''opcode'']/text)[1]', 'VARCHAR(100)') AS opcode,
        event_data.value ('(/event/data[@name=''duration'']/value)[1]', 'BIGINT') AS duration,
        event_data.value ('(/event/data[@name=''signal_duration'']/value)[1]', 'BIGINT') AS signal_duration
     FROM #waits
    ) AS waits
WHERE waits.opcode = 'End'
GROUP BY waits.wait_type
ORDER BY total_wait_time_ms DESC

DROP EVENT SESSION waits ON SERVER 

-- ------------------------------------------------

An Extended Events session
sql central

technet





top   prev   next  

Tables


CREATE TABLE dbo.table1 ( 
 ID int IDENTITY(1,1) NOT NULL,
 Username varchar(200) COLLATE Latin1_General_CI_AS NOT NULL,
 Email varchar(100) NULL,
 FirstName varchar(200),
 LastName  varchar(200),
 FullName AS ( FirstName + ' ' + LastName) PERSISTED,
 CONSTRAINT PK_table1 PRIMARY KEY CLUSTERED(ID ASC);
);

SELECT Name COLLATE Arabic_CI_AI From Profuction.Product;

CREATE NONCLUSTEREDINDEX myidx1 on schema1.Table1 (Username) INCLUDE (Email);
 Email varchar(100) NULL,
 CONSTRAINT PK_table1 PRIMARY KEY CLUSTERED(ID ASC);
);

CREATE NONCLUSTEREDINDEX myidx1 on schema1.Table1 (Username) INCLUDE (Email);

CREATE TABLE dbo.Customer
(
	CustomerID int IDENTITY(1,1) PRIMARY KEY,
	CustomerName nvarchar(50) NOT NULL
);

CREATE TABLE dbo.CustomerOrder
(
	CustomerOrderID int IDENTITY(1000001,1) PRIMARY KEY,
	CustomerID int NOT NULL
	  FOREIGN KEY REFERENCES dbo.Customer (CustomerID),
	OrderAmount decimal(18,2) NOT NULL
);

ALTER TABLE dbo.CustomerOrder
  DROP CONSTRAINT FK__CustomerO__Custo__66603565;
GO

ALTER TABLE dbo.CustomerOrder
  ADD CONSTRAINT FK_CustomerOrder_Customer
  FOREIGN KEY (CustomerID) 
  REFERENCES dbo.Customer (CustomerID)
  ON DELETE CASCADE;
GO
CREATE TABLE dbo.SimpleOrders(
	orderid int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	custid int NOT NULL FOREIGN KEY REFERENCES Sales.Customers(custid),
	empid int NOT NULL FOREIGN KEY REFERENCES HR.Employees(empid),
	orderdate datetime NOT NULL
);
GO
CREATE TABLE dbo.SimpleOrderDetails(
	orderid int NOT NULL FOREIGN KEY REFERENCES dbo.SimpleOrders(orderid),
	productid int NOT NULL FOREIGN KEY REFERENCES Production.Products(productid),
	unitprice money NOT NULL,
	qty smallint NOT NULL,
 CONSTRAINT PK_OrderDetails PRIMARY KEY (orderid, productid)
);
GO
ALTER TABLE dbo.FactProductInventory DROP CONSTRAINT PK_FactProductInventory
GO
ALTER TABLE [dbo].[FactProductInventory] DROP CONSTRAINT [FK_FactProductInventory_DimDate];
GO
ALTER TABLE [dbo].[FactProductInventory] DROP CONSTRAINT [FK_FactProductInventory_DimProduct];
GO



top   prev   next  

Schema

-- Create a schema
CREATE SCHEMA Reporting AUTHORIZATION dbo;
GO

-- Create a schema with an included object
CREATE SCHEMA Operations AUTHORIZATION dbo
CREATE TABLE Flights (FlightID int IDENTITY(1,1) PRIMARY KEY,
		Origin nvarchar(3),
		Destination nvarchar(3));
GO

GRANT EXECUTE ON SCHEMA::Sales to xxx;

Grant User1 the EXECUTE permission on sp1.
http://msdn.microsoft.com/en-us/library/ms191291.aspx

-- Create the same table in a different schema
CREATE TABLE Reporting.Flights (FlightID int IDENTITY(1,1) PRIMARY KEY,
	      Origin nvarchar(3),
	      Destination nvarchar(3));



top   prev   next  

Views

 
View With Check Options:
-- insert rows into the underlying tables by using the view.
http://msdn.microsoft.com/en-us/library/ms180800.aspx
http://msdn.microsoft.com/en-us/library/ms187956.aspx

-- ensure that the view can update the data in the table, except for the data in Column1.
CREATE View View1 AS SELECT ... WHERE COlumn1='CityA' WIth CHECK OPTION;
-- Create a new view
CREATE VIEW Person.IndividualsWithEmail
AS
SELECT p.BusinessEntityID, Title, FirstName, MiddleName, LastName, E.EmailAddress
FROM Person.Person AS p
JOIN Person.EmailAddress as e
on p.BusinessEntityID=e.BusinessEntityID;
GO

-- Query the view definition via OBJECT_DEFINITION 
SELECT OBJECT_DEFINITION(OBJECT_ID(N'Person.IndividualsWithEmail',N'V'));
GO

-- Alter the view to use WITH ENCRYPTION
ALTER VIEW Person.IndividualsWithEmail
WITH ENCRYPTION
AS
SELECT p.BusinessEntityID, Title, FirstName, MiddleName, LastName 
FROM Person.Person AS p
JOIN Person.EmailAddress as e
on p.BusinessEntityID=e.BusinessEntityID;



top   prev   next  

Temp Tables

CREATE TABLE #People
( 
personid UNIQUEIDENTIFIER,
firstname VARCHAR(80),
lastname VARCHAR(80),
dob DATETIME,
dod DATETIME,
sex CHAR(1)
);
GO



top   prev   next  

Temp Varibles

-- Do the same with a Table Variable

DECLARE @people TABLE 
( 
personid UNIQUEIDENTIFIER,
firstname VARCHAR(80),
lastname VARCHAR(80),
dob DATETIME,
dod DATETIME,
sex CHAR(1)
)
INSERT @people
SELECT TOP(250)*
FROM dbo.people

-- Now run the select. It will fail. Run it all as a batch
SELECT count(*) FROM @people



top   prev   next  

CTE

WITH CTE_year AS
(
SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders
)
SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM CTE_year
GROUP BY orderyear;

-- Recursive CTE
WITH EmpOrg_CTE AS
(SELECT empid, mgrid, lastname, firstname --anchor query
FROM HR.Employees
WHERE empid = 5 -- starting "top" of tree. Change this to show other root employees
UNION ALL
SELECT child.empid, child.mgrid, child.lastname, child.firstname -- recursive member which refers back to CTE
FROM EmpOrg_CTE AS parent
JOIN HR.Employees AS child
ON child.mgrid=parent.empid
)
SELECT empid, mgrid, lastname, firstname
FROM EmpOrg_CTE;



top   prev   next  

Partition

Use DemoDB;
ALTER DATABASE DemoDB ADD FILEGROUP FG0000
GO
ALTER DATABASE DemoDB ADD FILE (NAME = F0000, FILENAME = 'C:\temp\F0000.ndf', SIZE = 3MB, FILEGROWTH = 50%) TO FILEGROUP FG0000;
GO
ALTER DATABASE DemoDB ADD FILEGROUP FG2000
GO
ALTER DATABASE DemoDB ADD FILE (NAME = F2000, FILENAME = 'C:\temp\F2000.ndf', SIZE = 3MB, FILEGROWTH = 50%) TO FILEGROUP FG2000;
GO
ALTER DATABASE DemoDB ADD FILEGROUP FG2001
GO
ALTER DATABASE DemoDB ADD FILE (NAME = F2001, FILENAME = 'C:\temp\F2001.ndf', SIZE = 3MB, FILEGROWTH = 50%) TO FILEGROUP FG2001;
GO
ALTER DATABASE DemoDB ADD FILEGROUP FG2002
GO
ALTER DATABASE DemoDB ADD FILE (NAME = F2002, FILENAME = 'C:\temp\F2002.ndf', SIZE = 3MB, FILEGROWTH = 50%) TO FILEGROUP FG2002;
GO
ALTER DATABASE DemoDB ADD FILEGROUP FG2003
GO
ALTER DATABASE DemoDB ADD FILE (NAME = F2003, FILENAME = 'C:\temp\F2003.ndf', SIZE = 3MB, FILEGROWTH = 50%) TO FILEGROUP FG2002;
GO


-- Create a partitioned table
CREATE PARTITION FUNCTION PF (int) AS RANGE RIGHT FOR VALUES (20000101, 20010101, 20020101);
CREATE PARTITION SCHEME PS AS PARTITION PF TO (FG0000, FG2000, FG2001, FG2002, FG2003);

CREATE TABLE dbo.order_table
(datekey int, amount int)
ON PS(datekey);
GO


INSERT dbo.order_table VALUES (20000101, 100);
INSERT dbo.order_table VALUES (20001231, 100);
INSERT dbo.order_table VALUES (20010101, 100);
INSERT dbo.order_table VALUES (20010403, 100);
GO

-- View partition metadata
SELECT ps.name AS PartitionScheme, pf.name AS PartitionFunction, p.partition_number AS PartitionNumber, 
fg.name AS Filegroup, prv_left.value AS StartKey, prv_right.value AS EndKey, p.row_count Rows
FROM sys.dm_db_partition_stats p
INNER JOIN sys.indexes i
ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.data_spaces ds
ON ds.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.partition_schemes ps
ON ps.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
LEFT OUTER JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.filegroups fg
ON fg.data_space_id = dds.data_space_id
LEFT OUTER JOIN sys.partition_range_values prv_right
ON prv_right.function_id = ps.function_id AND prv_right.boundary_id = p.partition_number
LEFT OUTER JOIN sys.partition_range_values prv_left
ON prv_left.function_id = ps.function_id AND prv_left.boundary_id = p.partition_number - 1
WHERE OBJECT_NAME(p.object_id) = 'order_table'
AND i.index_id = 0
ORDER BY PartitionNumber
GO

-- Split the partition at the end to add a new partition for 2003 onwards
ALTER PARTITION FUNCTION PF() SPLIT RANGE(20030101);
GO

-- View partition metadata again
SELECT ps.name AS PartitionScheme, pf.name AS PartitionFunction, p.partition_number AS PartitionNumber, 
fg.name AS Filegroup, prv_left.value AS StartKey, prv_right.value AS EndKey, p.row_count Rows
FROM sys.dm_db_partition_stats p
INNER JOIN sys.indexes i
ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.data_spaces ds
ON ds.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.partition_schemes ps
ON ps.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
LEFT OUTER JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.filegroups fg
ON fg.data_space_id = dds.data_space_id
LEFT OUTER JOIN sys.partition_range_values prv_right
ON prv_right.function_id = ps.function_id AND prv_right.boundary_id = p.partition_number
LEFT OUTER JOIN sys.partition_range_values prv_left
ON prv_left.function_id = ps.function_id AND prv_left.boundary_id = p.partition_number - 1
WHERE OBJECT_NAME(p.object_id) = 'order_table'
AND i.index_id = 0
ORDER BY PartitionNumber
GO

-- Merge 2000 and pre-2000
ALTER PARTITION FUNCTION PF()
MERGE RANGE (20000101);
GO

-- view partition info
SELECT ps.name AS PartitionScheme, pf.name AS PartitionFunction, p.partition_number AS PartitionNumber, 
fg.name AS Filegroup, prv_left.value AS StartKey, prv_right.value AS EndKey, p.row_count Rows
FROM sys.dm_db_partition_stats p
INNER JOIN sys.indexes i
ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.data_spaces ds
ON ds.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.partition_schemes ps
ON ps.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
LEFT OUTER JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.filegroups fg
ON fg.data_space_id = dds.data_space_id
LEFT OUTER JOIN sys.partition_range_values prv_right
ON prv_right.function_id = ps.function_id AND prv_right.boundary_id = p.partition_number
LEFT OUTER JOIN sys.partition_range_values prv_left
ON prv_left.function_id = ps.function_id AND prv_left.boundary_id = p.partition_number - 1
WHERE OBJECT_NAME(p.object_id) = 'order_table'
AND i.index_id = 0
ORDER BY PartitionNumber
GO

-- Create an archive table
CREATE TABLE dbo.archive_staging_table
(datekey int, amount int)
ON FG0000;
GO

-- Switch the partition
ALTER TABLE dbo.order_table SWITCH PARTITION $PARTITION.PF(20000101)
TO dbo.archive_staging_table 
GO

-- view archive data
SELECT * FROM dbo.archive_staging_table;

-- view partition info
SELECT ps.name AS PartitionScheme, pf.name AS PartitionFunction, p.partition_number AS PartitionNumber, fg.name AS Filegroup, 
prv_left.value AS StartKey, prv_right.value AS EndKey, p.row_count Rows
FROM sys.dm_db_partition_stats p
INNER JOIN sys.indexes i
ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.data_spaces ds
ON ds.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.partition_schemes ps
ON ps.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
LEFT OUTER JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.filegroups fg
ON fg.data_space_id = dds.data_space_id
LEFT OUTER JOIN sys.partition_range_values prv_right
ON prv_right.function_id = ps.function_id AND prv_right.boundary_id = p.partition_number
LEFT OUTER JOIN sys.partition_range_values prv_left
ON prv_left.function_id = ps.function_id AND prv_left.boundary_id = p.partition_number - 1
WHERE OBJECT_NAME(p.object_id) = 'order_table'
AND i.index_id = 0
ORDER BY PartitionNumber
GO



top   prev   next  

Indexes

index dictionary view

fillfactor and pad_index
online index:

-- Query sys.indexes to view the structure
-- (note also the name chosen by SQL Server for the constraint and index)

SELECT * FROM sys.indexes WHERE OBJECT_NAME(object_id) = N'PhoneLog';
GO
SELECT * FROM sys.key_constraints WHERE OBJECT_NAME(parent_object_id) = N'PhoneLog';
GO

nonclustered index vs. clustered index

CREATE NONCLUSTERED INDEX IX_Book_Publisher
ON dbo.Book (PublisherID, ReleaseDate DESC);
GO

CREATE NONCLUSTERED INDEX IX_Book_Publisher
ON dbo.Book (PublisherID, ReleaseDate DESC)
INCLUDE (Title)
WITH DROP_EXISTING;
GO

-- Query the sys.index_columns system view

SELECT * FROM sys.index_columns;
GO

-- Note the is_included_column column, the key_ordinal column and the is_descending_key column

-- Combine several system views in a query to locate any included columns in the database

SELECT s.name AS SchemaName,
OBJECT_NAME(i.object_id) AS TableOrViewName,
i.name AS IndexName,
c.name AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.object_id = ic.object_id 
INNER JOIN sys.columns AS c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id 
INNER JOIN sys.objects AS o
ON i.object_id = o.object_id
INNER JOIN sys.schemas AS s
ON o.schema_id = s.schema_id 
WHERE ic.is_included_column <> 0
AND s.name <> 'sys'
ORDER BY SchemaName, TableOrViewName, i.index_id, ColumnName;

Index Information
-- Check the level of fragmentation via sys.dm_db_index_physical_stats
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.PhoneLog'),NULL,NULL,'DETAILED');
GO

select * FROM   sys.dm_db_index_operational_stats (db_id(),NULL,NULL,NULL ) A;
GO

-- Show writes versus reads to see candidates for unused indexes
SELECT convert(varchar(120),object_name(ios.object_id)) AS [Object Name], 
i.[name] AS [Index Name], 
   SUM (ios.range_scan_count + ios.singleton_lookup_count) AS 'Reads',
SUM (ios.leaf_insert_count + ios.leaf_update_count + ios.leaf_delete_count) AS 'Writes'
FROM   sys.dm_db_index_operational_stats (db_id(),NULL,NULL,NULL ) ios
INNER JOIN sys.indexes AS i
 ON i.object_id = ios.object_id 
    AND i.index_id = ios.index_id
WHERE  OBJECTPROPERTY(ios.object_id,'IsUserTable') = 1
GROUP BY object_name(ios.object_id),i.name
ORDER BY Reads ASC, Writes DESC

rebuild all index for a table
ALTER INDEX ALL ON dbo.PhoneLog REBUILD;
GO



top   prev   next  

Stored Procedure

-- All stored procedures must be signed.  Certificates
ADD Signature To ... BY CERTIFICATE CERT2;

A Table-Valued Parameter ( TVP )
Common Language Runtime ( CLR )

http://msdn.microsoft.com/en-us/library/ms131102.aspx
http://msdn.microsoft.com/en-us/library/bb522446.aspx
http://msdn.microsoft.com/en-us/library/bb510489.aspx

CREATE PROC Production.GetBlueProducts
AS
BEGIN
SELECT p.ProductID,
 p.Name,
 p.Size,
 p.ListPrice 
FROM Production.Product AS p
WHERE p.Color = N'Blue'
ORDER BY p.ProductID;
END;
GO

CREATE PROC Production.GetBlueProductsAndModels
AS
BEGIN
SELECT p.ProductID,
 p.Name,
 p.Size,
 p.ListPrice 
FROM Production.Product AS p
WHERE p.Color = N'Blue'
ORDER BY p.ProductID;

SELECT p.ProductID,
 pm.ProductModelID,
 pm.Name AS ModelName
FROM Production.Product AS p
INNER JOIN Production.ProductModel AS pm
ON p.ProductModelID = pm.ProductModelID 
ORDER BY p.ProductID, pm.ProductModelID;
END;
GO

-- Store procedure output parameter

CREATE PROC sales GetOrderCountByDueDate
@DueDate datetime,
@OrderCount int OUTPUT
AS
SELECT (OrderCount = COUNT (1)
FROM Sales.SalesOrderHeader AS soh
WHERE soh.DueDate = @DueDate;
GO

DECLARE @DueDate datetime '20050713';
DECLARE @Ordercount int;
EXEC sales.GetORderCountyByDate @DueDate, @orderCount OUTPUT;

select @OrderCount;

-- store procedure recompilation
sp_recompile 'xyz'
exec xxx with recompile
option ( optimize for unknown )
create proc xyz with recompile

ALTER PROC Production.GetBlueProductsAndModels
AS
BEGIN
SELECT p.ProductID,
 p.Name,
 p.Size,
 p.ListPrice 
FROM Production.Product AS p
WHERE p.Color = N'Blue'
ORDER BY p.ProductID;

SELECT p.ProductID,
 pm.ProductModelID,
 pm.Name AS ModelName
FROM Production.Product AS p
INNER JOIN Production.ProductModel AS pm
ON p.ProductModelID = pm.ProductModelID 
WHERE p.Color = N'Blue'
ORDER BY p.ProductID, pm.ProductModelID;
END;
GO

-- Query sys.procedures to see the list of procedures
SELECT SCHEMA_NAME(schema_id) AS SchemaName,
name AS ProcedureName
FROM sys.procedures;
GO

-- stored procedure with parameters
CREATE PROC Production.GetProductsAndModelsByColor
@Color nvarchar(15)
AS
BEGIN
SELECT p.ProductID,
 p.Name,
 p.Size,
 p.ListPrice 
FROM Production.Product AS p
WHERE p.Color = @Color 
ORDER BY p.ProductID;

SELECT p.ProductID,
 pm.ProductModelID,
 pm.Name AS ModelName
FROM Production.Product AS p
INNER JOIN Production.ProductModel AS pm
ON p.ProductModelID = pm.ProductModelID 
WHERE p.Color = @Color 
ORDER BY p.ProductID, pm.ProductModelID;
END;
GO



top   prev   next  

CLR ( The common language runtime ) 


https://msdn.microsoft.com/en-us/library/ms254498.aspx

EXEC sp_configure 'clr enabled', '1';
RECONFIGURE;

CLR User-Defined Types
https://msdn.microsoft.com/en-us/library/ms131120(SQL.100).aspx

CREATE ASSEMBLY
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-assembly-transact-sql

https://msdn.microsoft.com/en-us/library/ms131043(v=sql.100).aspx
CREATE ASSEMBLY FirstUdf FROM 'FirstUdf.dll';
GO

CREATE FUNCTION CountSalesOrderHeader() RETURNS INT 
AS EXTERNAL NAME FirstUdf.T.ReturnOrderCount; 
GO

SELECT dbo.CountSalesOrderHeader();
GO

-- CREATE ASSEMBLY [SimonBlog] FROM path WITH PERMISSION_SET = SAFE
-- Here the PERMISSION_SET can be SAFE, UNSAFE or EXTERNAL_ACCESS. 

CREATE ASSEMBLY TaxCalc FROM 'C:\temp\TreyTax.DLL';
--Error, distraction: CREATE ASSEMBLY TaxCalc FROM 'C:\temp\Amortize.cs;


CREATE PROCEDURE Accounting.Amortize(@total decimal(8,2), @period int)
RETURNS decimal(8.2) AS EXTERNAL NAME TaxClac.TreyResearch.Amortize;


CLR User-Defined Types

clr user defined types 

create user defined types 

registering user defined types in sql server 

USE AdventureWorks;  
CREATE ASSEMBLY Point  
FROM '\\ShareName\Projects\Point\bin\Point.dll'   
WITH PERMISSION_SET = SAFE;



top   prev   next  

Function

-- Scalar Function
CREATE FUNCTION dbo.EndOfPreviousMonth (@DateToTest date)
RETURNS date
AS BEGIN
RETURN DATEADD(day, 0 - DAY(@DateToTest), @DateToTest);
END;
GO

-- Determine if the function is deterministic. The function is not deterministic.
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.EndOfPreviousMonth'),'IsDeterministic');
GO

-- TVF table-valued function
SELECT t.CountryID, u.Country, t.CustomerName
FROM SChema1.Table1 AS t
CROSS APPLY Schema1.Udf1 ( t.CountryID ) AS u;

CREATE FUNCTION SChema1. Udf1 ( @CountryID int )
RETURNS TABLE
AS
RETURN
SELECT Country FROM SChema1.Table2 Where CountryID = @CountryID;

-- create a table-valued function
CREATE FUNCTION Sales.GetLastOrdersForCustomer 
(@CustomerID int, @NumberOfOrders int)
RETURNS TABLE
AS
RETURN (SELECT TOP(@NumberOfOrders)
		      soh.SalesOrderID,
		      soh.OrderDate,
		      soh.PurchaseOrderNumber
	FROM Sales.SalesOrderHeader AS soh
	WHERE soh.CustomerID = @CustomerID
	ORDER BY soh.OrderDate DESC
       );
GO

SELECT * FROM Sales.GetLastOrdersForCustomer(17288,2);
GO

-- CROSS APPLY could be used to call this function
SELECT c.CustomerID,
     c.AccountNumber,
     glofc.SalesOrderID,
     glofc.OrderDate 
FROM Sales.Customer AS c
CROSS APPLY Sales.GetLastOrdersForCustomer(c.CustomerID,3) AS glofc
ORDER BY c.CustomerID,glofc.SalesOrderID;



top   prev   next  

Transaction

IF OBJECT_ID('dbo.SimpleOrderDetails','U') IS NOT NULL
DROP TABLE dbo.SimpleOrderDetails;
IF OBJECT_ID('dbo.SimpleOrders','U') IS NOT NULL
DROP TABLE dbo.SimpleOrders;
GO

--
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

--
BEGIN TRY
INSERT INTO dbo.SimpleOrderDetails(orderid,productid,unitprice,qty) VALUES (999,77,26.20,15);
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrNum, ERROR_MESSAGE() AS ErrMsg;
END CATCH;

-- 
BEGIN TRY
BEGIN TRANSACTION
	INSERT INTO dbo.SimpleOrders(custid, empid, orderdate) VALUES (68,9,'2006-07-15');
	INSERT INTO dbo.SimpleOrderDetails(orderid,productid,unitprice,qty) VALUES (99, 2,15.20,20);
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrNum, ERROR_MESSAGE() AS ErrMsg;
ROLLBACK TRANSACTION
END CATCH;



top   prev   next  

Locking And Deadlocks

sp_lock

syslockinfo

-- Create a deadlock trace
DBCC TRACEON (1222,-1)
GO

-- Talk about read committed versus repeatable read
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRANSACTION
... ...
ROLLBACK TRANSACTION
COMMIT TRANSACTION
-- ? END TRANSACTION 

SELECT request_session_id AS Session,
resource_database_id AS DBID,
Resource_Type,
resource_description AS Resource,
request_type AS Type,
request_mode AS Mode,
request_status AS Status
FROM sys.dm_tran_locks
ORDER BY [session]

SELECT session_id,wait_duration_ms,wait_type,
blocking_session_id,resource_description
FROM sys.dm_os_waiting_tasks
WHERE session_id > 50



top   prev   next  

lock escalation



lock escalation 

Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, 
reducing system overhead while increasing the probability of concurrency contention.

Lock escalation is triggered when lock escalation is not disabled on the table by using 
the ALTER TABLE SET LOCK_ESCALATION option,

Lock Escalation Thresholds
A single Transact-SQL statement acquires at least 5,000 locks on a single nonpartitioned table or index.

A single Transact-SQL statement acquires at least 5,000 locks on a single partition of a partitioned table 
and the ALTER TABLE SET LOCK_ESCALATION option is set to AUTO.

The number of locks in an instance of the Database Engine exceeds memory or configuration thresholds.

If locks cannot be escalated because of lock conflicts, the Database Engine periodically triggers lock escalation 
at every 1,250 new locks acquired.



top   prev   next  

BPE: Buffer Pool Extension

-- Enable buffer pool extension
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(FILENAME = 'C:\temp\MyCache.bpe', SIZE = 10GB );

-- View buffer pool extension details
SELECT * FROM sys.dm_os_buffer_pool_extension_configuration;

-- Monitor buffer pool extension
SELECT * FROM sys.dm_os_buffer_descriptors;

-- Disable buffer pool extension
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF;

-- View buffer pool extension details again
SELECT * FROM sys.dm_os_buffer_pool_extension_configuration;



top   prev   next  

Columnstore Indexes

-- Create a clustered columnstore index, default is on all columns
CREATE CLUSTERED COLUMNSTORE INDEX [IX_CS_FactProductInventory]
ON dbo.FactProductInventory;

http://www.patrickkeisler.com/2014/04/what-is-non-clustered-columnstore-index.html

Columnstore indexes - overview
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview

Columnstore indexes - what's new
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-what-s-new

Columnstore Indexes Described.
https://msdn.microsoft.com/en-us/library/gg492088(v=sql.120).aspx

Using Nonclustered Columnstore Indexes
https://msdn.microsoft.com/en-us/library/dn589806(v=sql.120).aspx

1. Columnstore indexes coulnd’t be created with the INCLUDE keyword

2. Once you create a nonclustered columnstore index on a table, 
you cannot directly modify the data in that table. A query with INSERT, UPDATE, DELETE, or MERGE will fail and return an error message. 

option 1:
ALTER INDEX mycolumnstoreindex ON mytable DISABLE;  
-- update mytable --  
ALTER INDEX mycolumnstoreindex on mytable REBUILD  

option 2:
DROP INDEX mycolumnstoreindex ON mytable  
-- update mytable --  
CREATE NONCLUSTERED COLUMNSTORE INDEX mycolumnstoreindex ON mytable;  

-- -----------------------------------------

https://msdn.microsoft.com/en-us/library/dn589807(v=sql.120).aspx
To create a clustered columnstore index, first create a rowstore table as a heap or clustered index, and then use the CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL) statement to convert the table to a clustered columnstore index
CREATE TABLE T1(  
    ProductKey [int] NOT NULL,   
    OrderDateKey [int] NOT NULL,   
    DueDateKey [int] NOT NULL,   
    ShipDateKey [int] NOT NULL);  
GO  
CREATE CLUSTERED COLUMNSTORE INDEX cci_T1 ON T1;  
GO  

-- -----------------------------------------

Using Clustered Columnstore Indexes
-- Cannot be clustered. Only nonclustered columnstore indexes are available.

104 series
http://www.nikoport.com/2013/07/05/clustered-columnstore-indexes-part-1-intro/


-- column store index
CREATE NONCLUSTERED COLUMNSTORE INDEX ON Table_Name (Column1,Column2,... Column N)
GO

CREATE NONCLUSTERED COLUMNSTORE INDEX IX_SampleData_ColStore 
 ON SampleData (VarcharColumn1,FloatColumn1);
GO

-- Query hint for prevent using nonclustered columnstore index
select [LastName],Count([FirstName]),Count([MiddleName])
from dbo.Test_Person 
group by [LastName]
Order by [LastName]
OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX)

--error message: Cannot start the columnstore index build because it requires at least 142360 KB

SQL Server 2012 Column Store Index Example


CREATE NONCLUSTERED COLUMNSTORE INDEX  
ON 
(
 Col1,
 Col2,
 ....
 ....
 Coln
)
GO

-- Now execute and log statistics

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;



top   prev   next  

Memory-Optimized Tables

im memory oltp   

In-Memory OLTP (In-Memory Optimization) 

-- add a filestream filegroup
-- Can only has one filestream group, inside the group, can has multiple files
ALTER DATABASE MemoryOptimizedTableDemoDB ADD FILEGROUP [MemoryOptimizedTableDemoDB_MOTdata] 
CONTAINS MEMORY_OPTIMIZED_DATA
GO
ALTER DATABASE MemoryOptimizedTableDemoDB ADD FILE 
( 
NAME = [MemoryOptimizedTableDemoDB_folder1], 
FILENAME = 'c:\data\MemoryOptimizedTableDemoDB_folder1'
) TO FILEGROUP [MemoryOptimizedTableDemoDB_MOTdata]
GO
ALTER DATABASE MemoryOptimizedTableDemoDB ADD FILE 
(
NAME = [MemoryOptimizedTableDemoDB_folder2], 
FILENAME = 'c:\data\MemoryOptimizedTableDemoDB_folder2'
) TO FILEGROUP [MemoryOptimizedTableDemoDB_MOTdata] 
GO

-- view: Database Properties -- filegroup -- MEMORY OPTIMIZED DATA ( name and FILESTREAM Files )

CREATE TABLE dbo.MemoryTable
(id INTEGER NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),
date_value DATETIME NULL)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

-- DURABILITY = SCHEMA_AND_DATA
-- DURABILITY = SCHEMA_ONLY

-- View memory-optimized table stats
SELECT o.Name, m.*
FROM
sys.dm_db_xtp_table_memory_stats m
JOIN sys.sysobjects o
ON m.object_id = o.id



top   prev   next  

Native Compiled Stored Procedure

Natively Compiled Stored Procedures 
Native Compilation of Tables and Stored Procedures 
Atomic Blocks 

CREATE PROCEDURE dbo.InsertData
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english')
DECLARE @Memid int = 1
WHILE @Memid <= 500000
BEGIN
	INSERT INTO dbo.MemoryTable VALUES (@Memid, GETDATE())
	SET @Memid += 1
END
END;
GO

SELECT name, description FROM sys.dm_os_loaded_modules  
where description = 'XTP Native DLL';



top   prev   next  

DMV for Waits

SELECT * FROM sys.dm_exec_requests
GO

SELECT * from sys.dm_os_waiting_tasks
GO

SELECT * from sys.dm_os_wait_stats
GO



top   prev   next  

BLOB, filestream and filetable


filestream 

ssms --> Object Explorer --> Properties from instance context menu --> Advanced --> Filestream Access Level --> Full access enabled


-- ---------------------------
USE master
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO
EXEC xp_cmdshell 'IF NOT EXIST C:\DemoFileTable MKDIR C:\DemoFileTable';
GO
-- in order to create directory or folder inside sql server database, xp_cmdshell need to be enabled
-- ---------------------------
EXEC sp_filestream_configure 
  @enable_level = 3
, @share_name = N'FS';


0 = disabled (this is the default)
1 = enabled only for T-SQL access
2 = enabled for T-SQL access and local file system access
3 = enabled for T-SQL access, local file system access, and remote file system access

SELECT 
 SERVERPROPERTY ('FilestreamShareName') ShareName
,SERVERPROPERTY ('FilestreamConfiguredLevel') ConfiguredLevel
,SERVERPROPERTY ('FilestreamEffectiveLevel') EffectiveLevel;

CREATE DATABASE fs 
WITH FILESTREAM
( 
NON_TRANSACTED_ACCESS = FULL,
DIRECTORY_NAME = N'DemoFileTable'
);
GO
ALTER DATABASE fs
ADD FILEGROUP fs_fg_filestream CONTAINS FILESTREAM
GO
ALTER DATABASE fs
ADD FILE
(
    NAME= 'fs_filestream',
    FILENAME = 'C:\db\fs'
)
TO FILEGROUP fs_fg_filestream
GO

USE fs;
GO
CREATE TABLE dbo.BLOB (
 ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
 BLOB VARBINARY(MAX) FILESTREAM NULL
)
To store a BLOB using FILESTREAM you must have a column of type VARBINARY(MAX) with the FILESTREAM attribute specified.  
In addition the table must have a UNIQUEIDENTIFIER column with the ROWGUIDCOL attribute

DECLARE @ID UNIQUEIDENTIFIER
SET @ID = NEWID()
INSERT INTO dbo.BLOB
 (ID, BLOB)
VALUES 
 (@ID, CAST('BLOB Placeholder' AS VARBINARY(MAX)))
SELECT ID, BLOB 
FROM dbo.BLOB
WHERE ID = @ID
SELECT BLOB.PathName() 
FROM dbo.BLOB
WHERE ID = @ID;

GET_FILESTREAM_TRANSACTION_CONTEXT() 

CREATE PROCEDURE dbo.stp_AddBLOB
AS
BEGIN
  DECLARE @ID UNIQUEIDENTIFIER
  SET @ID = NEWID()
  INSERT INTO dbo.BLOB
    (ID, BLOB)
  VALUES 
    (@ID, CAST('' AS VARBINARY(MAX)))
  SELECT ID, BLOB.PathName()
  FROM dbo.BLOB
  WHERE ID = @ID
END
GO
CREATE PROCEDURE dbo.stp_GetBLOB
@ID UNIQUEIDENTIFIER
AS
BEGIN
  SELECT BLOB.PathName()
  FROM dbo.BLOB
  WHERE ID = @ID
END
GO


70-462: Chapter 3 Lesson 1
Filestream:
Filestream 
Introduction 

Filetable:
Filetable 
USE fs;
GO
CREATE TABLE DemoFileTable AS FILETABLE
WITH
( 
FILETABLE_DIRECTORY = 'DemoFileTableFiles',
FILETABLE_COLLATE_FILENAME = database_default
);
GO

View in SSMS: object explorer --> database ---> mydb1 --> tables --> FileTables --> my_filetable1

select * from DemoFileTable; 
mssqltips filetable 

Usig FILESTREAM to Store BLOB 

FileTable Schema
FileTable Schema(or Table) 

sp_configure filestream on

Manage FileTables 

NON_TRANSACTED_ACCESS FULL|READ_ONLY|OFF

-- Disable write access.  
ALTER DATABASE database_name  
    SET FILESTREAM ( NON_TRANSACTED_ACCESS = READ_ONLY );  
GO  
  
-- Disable non-transactional access.  
ALTER DATABASE database_name  
    SET FILESTREAM ( NON_TRANSACTED_ACCESS = OFF );  
GO  


DIRECTORY_NAME

FILETABLE_COLLATE_FILENAME

sp_kill_filestream_non_transacted_handles

Loading FIles into a FileTable  For new files, just copy the files, For files already existed in db, need to adjust file path pointers.

SELECT * FROM sys.filetables;  
GO  
  
SELECT * FROM sys.tables WHERE is_filetable = 1;  
GO  

SELECT object_id, OBJECT_NAME(object_id) AS 'Object Name'  
    FROM sys.filetable_system_defined_objects  
    WHERE object_id = filetable_object_id;  
GO  

 



top   prev   next  

Statistics

sp_dboption ,'auto update statistics', 
sp_dboption ,'auto create statistics', 

msft 
alter database set AUTO_UPDATE_STATISTICS ON|OFF; -- default is on
set option 

DATABASEPROPERTYEX 
databasepropertyex 
://www.onlc.com/sql-server-training-classes-certification.htm

10977: UPdating Your SQL Server Skills to Microsoft SQL Server 2014

10985: Introduction to SQL Databases

10986: Updating YOur Skills to SQL Server 2016

10988: Managing SQL Business intelligence Operations

10987: Performance Tuning and Optimizing SQL Databases

10989: Analyzing Data With Power BI
-- 20778: Analyzing Data With Power BI

10990: Analyzing Data with SQL Server Reporting Services


20461: Querying Microsoft SQL Server

20462: Administering Microsoft SQL Server Databases

20463: Implementing a Data Warehouse with Microsoft SQL Server

20464: Developing Microsoft SQL Server databases

20465: Designing Database Solutions with Microsoft SQL Server

20466: Implementing Data Models and Reports with MIcrosoft SQL Server

20467: Designing Businsess Intelligence Solutions with MIicrosoft SQL Server 2014

20761: Querying Data With Transact-SQL

20762: Developing SQL Databases

20764: Aministering a SQL Database Infrastructure

20765: Provisioning SQL Databases

20767: Implementing a SQL Data Warehouse

20768: Developing SQL DAta Models

20778: Analyzing Data With Power BI

55005: Microsot Report Builder 3.0 with SQL 2008R2, SQL 2012 Express and SQL 2012

55144: SQL Server 2014 Performance Tuning and Optimization

55119: SQL Server 2012 Reporting Services



SELECT DATABASEPROPERTYEX('AdventureWorks2014', 'IsAutoShrink'); 

SELECT  
SERVERPROPERTY('MachineName') AS ComputerName,
SERVERPROPERTY('ServerName') AS InstanceName,  
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductVersion') AS ProductVersion,  
SERVERPROPERTY('ProductLevel') AS ProductLevel;  
GO  

EXEC sp_autostats 'Production.Product';
EXEC sp_autostats 'Production.Product', 'ON';
EXEC sp_autostats 'Production.Product', 'OFF';
EXEC sp_autostats 'Production.Product', 'OFF', AK_Product_Name_Index_01; 
sp_autostats: Displays or changes the automatic statistics update option, AUTO_UPDATE_STATISTICS, 
for an index, a statistics object, a table, or an indexed view. 

sp_updatestats

USE mydb
GO  
EXEC sp_updatestats; -- update statistics for all of the tables

sp_updatestats 

UPDATE STATISTICS
update statistics dbo.mytable with fullscan, columns; -- columns only
update statistics dbo.mytable with fullscan, index ;  -- index only

update statistics dbo.mytable with fullscan;  -- default is all
update statistics dbo.mytable with fullscan, all ;

alter index ix_ProductID on dbo.SalesOrderDetail rebuild;   -- equal to full scan index
alter index xxx rebuild; -- default  fullscan;

alter index ix_ProductID on dbo.SalesOrderDetail reorganize;  -- not update index statistics

select name, auto_created, stats_date(object_id, stats_id) as update_date from sys.stats
where object_id = object_id('dbo.mytable')



top   prev   next  

Index Rebuild And Stats Update


https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql

ALTER INDEX index1 ON table1 REBUILD;  
ALTER INDEX ALL ON table1 REBUILD;  
ALTER INDEX ALL ON dbo.table1 REBUILD; 

-- defragment the index in the least amount of time
ALTER INDEX ALL On Customers
REBUILD WITH ( ONLINE = OFF, STATISTICS_NORECOMPUTE = ON );

-- changed the Fillfactor to 80 as below: 
DBCC DBREINDEX ('myTable', '', 80)

SELECT
  db_name() AS DbName
, B.name AS TableName
, C.name AS IndexName
, C.fill_factor AS IndexFillFactor
, D.rows AS RowsCount
, A.avg_fragmentation_in_percent
, A.page_count
, GetDate() as [TimeStamp]
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A
INNER JOIN sys.objects B
   ON A.object_id = B.object_id
INNER JOIN sys.indexes C
   ON B.object_id = C.object_id AND A.index_id = C.index_id
INNER JOIN sys.partitions D
   ON B.object_id = D.object_id AND A.index_id = D.index_id
WHERE C.index_id > 0;

CREATE INDEX x ON Person.Person(title)
--Stats is up-to-date
DBCC SHOW_STATISTICS('Person.Person', 'x') WITH HISTOGRAM

UPDATE Person.Person SET Title = 'x' WHERE title = 'Ms.'
DBCC SHOW_STATISTICS('Person.Person', 'x') WITH HISTOGRAM
--Not up-to-date

ALTER INDEX x ON Person.Person REBUILD
DBCC SHOW_STATISTICS('Person.Person', 'x') WITH HISTOGRAM
--Up-to-date

UPDATE Person.Person SET Title = 'a' WHERE title = 'x'
DBCC SHOW_STATISTICS('Person.Person', 'x') WITH HISTOGRAM
--Not up-to-date

ALTER INDEX x ON Person.Person REBUILD WITH(STATISTICS_NORECOMPUTE = ON)
DBCC SHOW_STATISTICS('Person.Person', 'x') WITH HISTOGRAM
--Up-to-date

SELECT * FROM sys.stats WHERE object_id = OBJECT_ID('Person.Person')



top   prev   next  

Function vs. Like

-- slower
SELECT * FROM dbo.Cusmomers WHERE LEFT(CustomerName,1) = 'a';
-- Faster
SELECT * FROM dbo.Cusmomers WHERE CustomerName Like 'a%';

http://msdn.microsoft.com/en-us/library/ms179859.aspx
http://msdn.microsoft.com/en-us/library/ms187748.aspx



top   prev   next  

LoginLess User or EXECUTE AS USER = xxx

loginless user, execute as user = 'xxx'

-- Create LoginLess User
CREATE USER [testnologinuser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
GO

-- Giving more Permissions to testguest user
GRANT SELECT ON [dbo].[ErrorLog] TO [testnologinuser];
GRANT SELECT ON [dbo].[DatabaseLog] TO [testnologinuser];
GO
-- Changing the execution context
EXECUTE AS USER = 'testguest';
GO
-- Checking access to Tables
SELECT *
FROM sys.tables;
GO
-- Reverting Permissions
REVERT;
GO

-- ------------------------------------------
EXECUTE AS USER = 'MyAppUser';
GO
-- ------------------------------------------
EXECUTE AS LOGIN = 'MyAppUser'
go
-- ------------------------------------------
CREATE PROC my_app_schema.sp_demo
WITH EXECUTE AS 'MyAppUser'
AS
  SELECT user_name()
  SELECT * FROM my_app_schema.table1
  SELECT * FROM dbo.table2
go
-- -------------------------------------------


Graphical Execution Plan Icons
https://technet.microsoft.com/en-us/library/ms175913(v=sql.105).aspx

View:
-- Create View xxx with VIEW_METADATA
-- the design of Table2 was modified since you created the view
-- Need to run: EXEC sp_refreshview @viewname = 'dbo.View1';

-- --------------------------------------------------------------

sql server profiler trace:
1. log all of the queries to DB1 that become deadlocked.
   Provide a representation of the deadlock in XML format

-- ---------------------------------------------------------------

Extended Events:
1. Support real-time monitoring for the deadlocks
   enable and disable easily
   querying of the monitored data

2. Need to identify which parallel execution plans are running in serial
Answer: Using Extended Events.

http://msdn.microsoft.com/en-us/library/bb677278.aspx
http://msdn.microsoft.com/en-us/library/bb630282.aspx 
http://www.sql-server-performance.com/2006/queryexecution-plan-analysis/ 
http://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelismin-sqlserver/ 
http://www.sqlservercentral.com/articles/SQL+Server+2012/At+last%2c+execution+plans+show+true+thread+reservations./92458/ 
http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing-aparallel-query-executionplan.aspx 
http://sqlblog.com/blogs/paul_white/archive/2012/05/02/parallel-row-goalsgone-rogue.aspx 
http://msdn.microsoft.com/en-us/library/bb895310.aspx 
http://msdn.microsoft.com/enus/library/bb895313.aspx 
http://msdn.microsoft.com/en-us/library/hh231122.aspx

-- ----------------------------------------------------------------

Shrink Transaction Log File:
1): Alter database xxx set recovery simple;
2): DBCC Shrinkfile ( xxx_log, 10 );
3): ALter database xxx set recovery full;

-- ----------------------------------------------------------------

index: prevent key lookup
-- using covered index  ( INCLUDE ... ... )

-- ----------------------------------------------------------------

index: prevent page splits

index fill factor:

FILLFACTOR=80 to avoid page splits

http://msdn.microsoft.com/en-us/library/ms188783.aspx 
http://msdn.microsoft.com/en-us/library/ms177459.aspx

-- -----------------------------------------------------------------

identify unused indexes: sys.dm_db_index_usage_stats

identify indexes that need to be defragmented: sys.dm_db_index_physical_stats

-- -----------------------------------------------------------------

Ad hoc workloads:
Configure "Optimize for Ad hoc Workloads" : to use less memory for ad hoc query

-- ------------------------------------------------------------------

query hints:
1a. Update : hold a shared lock

1b. Update ... WITH (UPDLOCK) : hold exclusive lock, this way, it will avoid deadlock
It simply specifies that update locks are to be taken 
and held until the transaction completes. That means whilst your update command is firing, 
other update commands will have to wait and will fire only after your transaction is complete.

1c. UPdate ... WITH (READPAST):  
the UPDATE itself will add an exclusive lock on just the rows that get updated
if holds your transaction untill update is complete When user access this data he will get old data untill commit
                              
2. Optimized for unknown

-- ------------------------------------------------------------------


-- ------------------------------------------------------------------