Tuesday, 19 August 2014

Tuning Tidbits


  1. can avoid sort by in exectuion plan ,by including that column in index,so that it will get sorted in index itself so no need to sort again

Sql Queries


To list the index of the table

 
SELECT 
     TableName = t.name,
     IndexName = ind.name,
     IndexId = ind.index_id,
     ColumnId = ic.index_column_id,
     ColumnName = col.name,
     is_included_column,
     ind.*,
     ic.*,
     col.* 
FROM 
     sys.indexes ind 
INNER JOIN 
     sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
INNER JOIN 
     sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
INNER JOIN 
     sys.tables t ON ind.object_id = t.object_id 
WHERE 
     ind.is_primary_key = 0 
     AND ind.is_unique = 0 
     AND ind.is_unique_constraint = 0 
     AND t.is_ms_shipped = 0 
     and t.name = 'lcp_productdetail'
ORDER BY 
     t.name, ind.name, ind.index_id, ic.index_column_id
     
     
   

To fetch the last executed SP



SELECT DB_Name(SqlTxt.[dbid]) AS [Database]     , 
Object_Name(SqlTxt.objectid, SqlTxt.[dbid]) AS [Stored Procedure]     , 
Max(stat.last_execution_time) As [Last Executed Time] FROM sys.dm_exec_query_stats AS stat     
CROSS APPLY sys.dm_exec_sql_text(stat.sql_handle) AS SqlTxt     INNER JOIN sys.procedures SP         
ON SP.object_id = SqlTxt.objectid     
where db_name(SqlTxt.[dbid])     = 'MMP_Generator'
and stat.last_execution_time >'2014-08-11 09:08:50.860'
AND SqlTxt.[dbid] IS NOT NULL GROUP BY 
db_name(SqlTxt.[dbid])     , 
Object_Name(SqlTxt.objectid, SqlTxt.[dbid]) ORDER BY 1, 2   


To change the identity value of a table



DBCC CHECKIDENT (yourtable, reseed, 34)

This will insert the new row with the identity value as 35.
Note:Even if already there is value 35 in the table,it will get repeated,so have to be careful if it is considered as the column for the uniqueness of the table.(go through the below link)

Useful Links

  1. Identity reseed

Monday, 18 August 2014

Big Data


Hadoop Basics




keywords

  1. Book:Hadoop the Definitive Guide Third Edition --Tim White
  2. Terms Used : MapReduce,HDFS,Pig,Hive,HBase,Sqoop,Cluster,Nodes,parallel Processing
  3. Packages used in java for map,reduce and map Reduce job are as follows
    • import org.apache.hadoop.mapreduce.mapper
    • import org.apache.hadoop.mapreduce.reducer
    • import org.apache.hadoop.mapreduce.job

Acronymns


Abbreviation Expansion Extra Details
ACIDAtomicity,Consistency,Isolation,Durability --
B TREEBusiness Tree --
BIDSBusiness Intelligent Development Studio --
DACDedicated Administrative Control --
DBCCDataBase Console Commands --
DETADataBase Engine Tuning Advisor --
DMFDeclarative Management Framework --
DMVDynamic Management Views --
DOPDegree Of Parallelism --
DPTDirty Page Table --
DTSData Transformation Services --
ETLExtract Transform Load --
IAMIndex Allocation map --
KPIKey Performance Indicator --
LOBLarge Objects --
LSNLog Sequence Number --
MAPIMessaging Application Programming Interface --
MDACMicrosoft DataAccess Components --
MDBMultiDimensional DataBase --
MLMAPMinimally Logged Map --
MSDTCMicrosoft Distributed Transaction Coordinator --
NUMANon Uniform Memory Access --
ODBCOpen DataBase Connectivity --
OLEObject Linking And Embedding --
PFSPage Free Space --
RAIDReduntant Array Of Independent Disks --
RIDRow Identifier --
SNISQL Server Network Interface --
TDETransparent Data Encryption --
TDSTabular Data Stream --
VASVirtual Address Space --
VLFVirtual Log File --
WMIWindows Management Instrumentation --
DMXData Mining Extensions --
ASSLAnalysis Services Scripting Language --
UNCUniversal Naming Convention --
MSMQMicroSoft Message Queue --
TRCTrace file --
LAMP Linux,Apache,MySQL,Perl/PHP/Python Source :WikiPedia--MySql
JSON JavaScript Object Notation It is used primarily to transmit data between a server and web application, as an alternative to XML.
BSON Binary JSON --
YARN Yet Another Resource Negotiator --

Useful Links

  1. Enterprise-Database- Systems

Wednesday, 13 August 2014

DB -General


DB Type
Type Example
RDBMS MS-SQL,Oracle,My-SQL,PostGreSQL,MS-Access,Hive,SysBase,Tera-Data
Wide Column Store Data Base Cassandra,HBase
Document Store DataBase MongoDB,CouchDB
Source Link