Data Storage

Data Storage

Size	Equal to
8 bits	1 byte
1024 bytes	1 kilobyte
1024 kilobytes	1 megabyte
1024 megabytes	1 gigabyte
1024 gigabytes	1 terabyte
1024 terabyte	1 Petabyte
1024 Petabyte	1 Exabyte
1024 Exabyte	1 Zettabyte
1024 Zettabyte	1 Yottabyte

SSIS SSDT Windows

SSDT > Edit >

Window	Shortcut Key
Solution Explorer	Ctrl + Alt + L
Team Explorer	Ctrl+\ , Ctrl+M
Server Explorer	Ctrl + Alt + S
SQL Server Object Explorer	Ctrl+\ , Ctrl+S
Bookmark Window	Ctrl + K
Call Hierarchy	Ctrl +Alt+K
Class View	Ctrl + Shift + C
Code Definition Window	Ctrl+\ , D
Object Browser	Ctrl + Alt + J

SQL Indexes

 

 

SQL  Indexes

Contents 1       Types of Indexes. 2 1.1        Clustered Index. 2 1.2        Non-Clustered Index. 2 1.3        Full-Text Index. 2

 

1         Types of Indexes

-          Indexes are used to speed-up query process in SQL Server, resulting in high performance.

-           

1.1       Clustered Index

-          A clustered index defines the order in which data is physically stored in a table.  Therefore, there can be only one clustered index per table. In SQL Server, the primary key constraint automatically creates a clustered index on that particular column.

-          An index that is created on more than one column is called “composite index”.

-          How to Create Indexes?

o   SSMS > Databases > Database_Name > Tables > Table_Name > Select Column(s)  RtClikc > Primary Key OR

o    

use schooldb   CREATE CLUSTERED INDEX IX_tblStudent_Gender_Score ON student(gender ASC, total_score DESC)

o    

-          How to see the existing Indexes?

o   sp_helpindex >> EXE sp_helpindex TableName

o   SSMS > Databases > Database_Name > Tables > Table_Name > Indexes

-           

1.2       Non-Clustered Index

·                A non-clustered index doesn’t sort the physical data inside the table. In fact, a non-clustered index is stored at one place and table data is stored in another place, more than one non-clustered index per table can create.  due to this additional step that non-clustered indexes are slower than clustered indexes. 

·                How to Create Indexes?

o         

use schooldb   CREATE NONCLUSTERED INDEX IX_tblStudent_Name ON student(name ASC)

 

1.3       Full-Text Index.

SQL Performance Tuning

Issues:-

• Long Running Query / Procedure
• Slow Response Table / View / Database       
  

Solutions:-

1. Find the Query  - Activity Monitor /
   
2. Analysis the Query
3. Consider the correct Indexs
4. Ensure Index Statistics
5. Consider Partitioned Table /Views
6. Consider to create Full Text Index
7. Analyse the query by using Live Query statistics
8. Consider the Performance Tuning Tools
1. PERFMON
2. DTA (Database Engine Tuning Adviser)
   

Activity Monitor (In SSMS Ctrl+alt+A): - Overview Pane / Process Pane / Resource Wait Pane / Date I/O Pane / Recent Expensive Pane

ie > Analysis the  Query -Multiple OR to IN, if possible text to numeric, instead of storing  some repeated values introducing look up table,  implement proper index, some times denationalization
- Correct Developer mistakes like Select * from instead of selecting required columns, some times for different conditions they need different sent of data for that Developer select all the data without where condition - they can disconnect the recordset and they can fetch required records whenever they need. 
- Check execution plan
- Use CTE when dealing with specific set of data / self join.  Avoid Self join, Use Profiler to generate trace file, use DTA tool to get recommendations. Use Partition of Table / View / Index / filegroup / Partition function
---------------------------------------------------------------------------------------------
Analysis the Query: -
- Find out the query by using SYS.DM_EXEC_QUERY_STATS and  SYS.DM_EXEC_SQL_TEXT

 SELECT
    plan_handle, total_worker_time, total_worker_time / execution_count AS AVG_EXEC_TIME,
    TEXT -- actual query
FROM SYS.DM_EXEC_QUERY_STATS
CROSS APPLY
SYS.DM_EXEC_SQL_TEXT(plan_handle)
ORDER BY total_worker_time / execution_count DESC

- If multiple OR condition change to "IN"
- Try to use numeric instead of text
- Select the required columns instead of Select *

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

Consider the correct Indexs:-
- Use execution plan & identify missing indexes
- Index based on where condition

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

Ensure Index Statistics
- Use Maintenance Plan to ensure Minimal Index Fragmentation

----------------------------------------------------------------------------------------------
Consider Partitioned Tables / Views -  Partitioned Tables available only in the Enterprise SQL Server Edition,
- SQL Server table partitioning is a great feature that can be used to split large tables into multiple smaller tables, transparently. It allows you to store your data in many filegroups and keep the database files in different disk drives, with the ability to move the data in and out the partitioned tables easily.  Table partitioning improves query performance by excluding the partitions that are not needed in the result set.  Fortunately, SQL Server allows you to design your own partitioning solution without the need to upgrade your current SQL Server instance to Enterprise Edition. This new option is called Partitioned Views. Although this new solution is not as flexible as table partitioning, Partitioned Views will give you a good result if you design it properly.

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

Consider to create Full Text Index

----------------------------------------------------------------------------------------------
Analyse the query by using Live Query statistics

-----------------------------------------------------------------------------------------------
Consider the Performance Tuning Tools

PERFMON
- Select Counters to be considered

Ref:-
- brentozar.com

----------------------------------------------------------------------------------------------
DTA (Database Engine Tuning Adviser)
- This tool is auto installed with ssms installation
- This tool is used to analayse the given query / set of queries  and provide a set of  recommendations using which we can implement: (Indexes / Statistics /Partitions)

- DTA tool can get input as

1. Profiler trace file (Run the SQL Profiler and store the trace file)
2. Table to Analyse (We can select in DTA tool to analyse and get recommendations)
3. .sql query

Ref: Microsoft / 

SSIS Tasks

  Containers:-

Container	Description
Foreach Loop Container	Runs a control flow repeatedly by using an enumerator.
For Loop Container	Runs a control flow repeatedly by testing a condition.
Sequence Container	Groups tasks and containers into control flows that are subsets of the package control flow.
Task Host Container	Provides services to a single task.
Group Container	Groups tasks and containers into control flows that are subsets of the package control flow.

Tasks:-

Type	Tasks
Data Flow Task:-	Data Flow Task - The task that runs data flows to extract data, apply column level transformations, and load data. Contains Source, Transformation, Destination
Data Preparation Tasks	File System Task FTP Task Web Service Task XML Task Data Profiling Task Hadoop File System Task Hadoop Hive Task Hadoop Pig Task
Workflow Tasks	Execute Package Task Execute Process Task Expression Task Message Queue Task Send Mail Task WMI Data Reader Task WMI Event Watcher Task
SQL Server Tasks	Bulk Insert Task CDC Control Task Execute SQL Task Transfer Database Task Transfer Error Messages Task Transfer Jobs Task Transfer Logins Task Transfer Master Stored Procedures Task Transfer SQL Server Objects Task
Scripting Tasks	Script Task
Analysis Services Tasks	Analysis Services Execute DDL Task, Analysis Services Processing Task, Data Mining Query Task
Maintenance Tasks	Back Up Database Task Check Database Integrity Task Execute SQL Server Agent Job Task Execute T-SQL Statement Task History Cleanup Task Maintenance Cleanup Task Notify Operator Task Rebuild Index Task Reorganize Index Task Shrink Database Task Update Statistics Task
Custom Tasks	Custom Tasks
2016 - Azure Tasks	Azure Blob Upload Task Azure Blob Download Task Azure HDInsight Hive Task Azure HDInsight Pig Task Azure HDInsight Create Cluster Task Azure HDInsight Delete Cluster Task
??? Known tasks and to be organized to the above category	Execute SQL, Bulk Insert Task, Script Task, Script Component,