Sunday, 16 November 2014
Friday, 17 October 2014
Defragmentation
Query to find the fragmentation percent
SELECT OBJECT_NAME(A.OBJECT_ID),avg_fragmentation_in_percent,* FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),NULL,NULL,NULL,NULL) A INNER JOIN SYS.INDEXES B on a.index_id = b.index_id AND A.OBJECT_ID= B.OBJECT_ID AND database_id = DB_ID() AND (index_type_desc ='CLUSTERED INDEX' or index_type_desc ='NONCLUSTERED INDEX') ORDER BY 2
To Reorganize the Index can use the below code ALTER INDEX INDEXNAME ON TABLENAME REORGANIZE
Useful Links
Tuesday, 19 August 2014
Tuning Tidbits
-
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
Monday, 18 August 2014
Big Data
Hadoop Basics
keywords
- Book:Hadoop the Definitive Guide Third Edition --Tim White
- Terms Used : MapReduce,HDFS,Pig,Hive,HBase,Sqoop,Cluster,Nodes,parallel Processing
- 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 |
|---|---|---|
| ACID | Atomicity,Consistency,Isolation,Durability | -- |
| B TREE | Business Tree | -- |
| BIDS | Business Intelligent Development Studio | -- |
| DAC | Dedicated Administrative Control | -- |
| DBCC | DataBase Console Commands | -- |
| DETA | DataBase Engine Tuning Advisor | -- |
| DMF | Declarative Management Framework | -- |
| DMV | Dynamic Management Views | -- |
| DOP | Degree Of Parallelism | -- |
| DPT | Dirty Page Table | -- |
| DTS | Data Transformation Services | -- |
| ETL | Extract Transform Load | -- |
| IAM | Index Allocation map | -- |
| KPI | Key Performance Indicator | -- |
| LOB | Large Objects | -- |
| LSN | Log Sequence Number | -- |
| MAPI | Messaging Application Programming Interface | -- |
| MDAC | Microsoft DataAccess Components | -- |
| MDB | MultiDimensional DataBase | -- |
| MLMAP | Minimally Logged Map | -- |
| MSDTC | Microsoft Distributed Transaction Coordinator | -- |
| NUMA | Non Uniform Memory Access | -- |
| ODBC | Open DataBase Connectivity | -- |
| OLE | Object Linking And Embedding | -- |
| PFS | Page Free Space | -- |
| RAID | Reduntant Array Of Independent Disks | -- |
| RID | Row Identifier | -- |
| SNI | SQL Server Network Interface | -- |
| TDE | Transparent Data Encryption | -- |
| TDS | Tabular Data Stream | -- |
| VAS | Virtual Address Space | -- |
| VLF | Virtual Log File | -- |
| WMI | Windows Management Instrumentation | -- |
| DMX | Data Mining Extensions | -- |
| ASSL | Analysis Services Scripting Language | -- |
| UNC | Universal Naming Convention | -- |
| MSMQ | MicroSoft Message Queue | -- |
| TRC | Trace 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
Wednesday, 13 August 2014
DB -General
| 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 |
Saturday, 10 May 2014
Subscribe to:
Posts (Atom)