Software Development,SQL Tips, Information Technology Technical tutorials

Asp.net,PL/SQL tips-Tricks for error resolution version 2014,2012,2008,2005,Procedural language,Stored Procedure, computer , excel macro programming,Visual Studio,Film,Musical notations,Indian ,Western Music.

January 14, 2015

Tips for SQL Server Database Engine Tuning Advisor 2012

I want to share some tips through image sharing in  SQL Server Database Engine Tuning Advisor 2012.Database Engine Tuning Advisor  is used to recommend indexes and other actions which is required to improve performance of your database. 

If there is a big size data then you should use index technique to improve database performance.

Database Engine Tuning Advisor

Database Engine Tuning Advisor

Database Engine Tuning Advisor steps

Database Engine Tuning Advisor

Database Engine Tuning Advisor

Database Engine Tuning Advisor

Database Engine Tuning Advisor

January 12, 2015

What is difference between the index by table and nested tables

Following are the  difference between the index by table and nested tables.

Index by table -
1)Not store in database 
2)Automatically initialize when declared .
3)Index By Table is something like a hash-map or map in another languages where you can index a value with either String or Integer.

Nested table 
1)Store in database 
2)Initialize using contractor .
3)Where are Nested Table is something like Array in another languages but the difference is it is not fixed in size

Disadvantage of Bulk Collect in oracle

Bulk collect fetch all rows from table in single shot so if table have so many rows then it can cause consume too much session memory and can raise an error .for this developer should set limit parameter carefully by keeping performance or memory overhead in mind.

Suppose I have 10000 rows in my emp table but my session doesn't have enough memory to hold 10000 row on that case we can use limit parameter.

declare c_limit PLS_INTEGER=100;
cursor emp_cur is select empno from emp where deptno=&depnto;
type l_emp_id_t is table of emp.empno%type;
l_emp_ids l_emp_id_t ;
begin
open emp_cur;
loop
fetch
emp_cur bulk collect into l_emp_ids limit c_limit;
exit when l_emp_ids.COUNT=0;
end loop;
end;

January 6, 2015

What is Ref cursor in SQL?

 Ref cursor is called dynamic cursor and it is a datatype and it is used to you can open the multiple select statement at run time and it is dynamic change the query

Ref cursor is used to write more then one select statement in cursor.

Ref cursor is 'pointer' datatype that allow you to quickly reference cursor result , using ref cursor we use the re-usability of cursor ,ref cursor is variable defined as a cursor type , which will point to, or reference a cursor result , ref cursor can be assigned to other ref cursor variable 

Ref cursor is one data type and can be defined into one variable,this variable can be called as ref_cursor variable.

We can use this variable for different SQL statements at run time.

For more details you can visit this site mentioned in image


Ref cursor

December 28, 2014

The Top Ten Software Companies in India

Names

TCS LIMITED
TCS LIMITED

WIPRO LIMITED
WIPRO LIMITED

INFOSYS TECHNOLOGIES LIMITED
INFOSYS TECHNOLOGIES LIMITED

SATYAM COMPUTER SERVICES LIMITED
SATYAM COMPUTER SERVICES LIMITED

I-FLEX SOLUTIONS LIMITED
I-FLEX SOLUTIONS LIMITED

TATA INFOTECH LIMITED
TATA INFOTECH LIMITED

CMC LIMITED
CMC LIMITED

MPHASIS BFL LIMITED
MPHASIS BFL LIMITED

MASTEK LIMITED
MASTEK LIMITED
MASTEK LIMITED

NIIT LIMITED

NIIT LIMITED

December 14, 2014

Career and opportunity in NGO'S

Working in NGO needs different sets of knowledge, attitude and skills. And obviously it offers a good salary also. But the initial days are very crucial as every moment a development worker has to deal with people.

In India different courses offered by various institutions offers an opportunity to enter into development sector (NGO sector is known as development sector for the professionals). The reputed institutes includes, TISS, Tata –Dhan Academy, XISS,KIIIT and IRMA. Etc. these institutes offers different courses in different names. Like MSW, Development Studies, Development Management, Rural Management etc.

The job opportunity is very good. Initially one can earn between Rs.15000-20000. In abroad one can earn more but in most of the cases it need at least three years of experience.

lease visit website of the few reputed NGOs. You could visit devnetjobs.com where you will find many opportunities.

There are many NGOs working in Bangalore. Please explore with them. In many of the cases the NGOs offer volunteering opportunity.

The professional NGOs organised stage shows for fundraising. You could be part of that. But before that you have to prove yourself.

December 1, 2014

Difference in CHAR and VARCHAR2 Data type in SQL

Char is fixed length Datatype. and Varchar is variable length datatype.

Suppose you have 2 columns, 1 with Char(40) another with Varchar2(40)  and both columns has values with only 10 characters in a record that time SQL server still occupy 40 character space in system for 1st column and 10 character space in system for 2nd column.

Simple pick char if length is going to remain same otherwise pick varchar as a speed both have its advantage . Choosing data type cannot be done by yes or no you have to choose data type as per your requirement both are good and bad. 

November 13, 2014

Suggestions to get rid of from recovery mode of SQL database

These are just some Suggestions to get rid of from recovery mode of SQL database. Mostly suspension of database made due to any long process and long suspended query which generating deadlock and infinite loop for your database.

Try these option.

A. Alter database dbname set emergency
Alter database dbname set single_user
dbcc checkdb(‘dbname’,repair_allow_Data_loss)
alter database dbname set multi_user


B. we have to run dbcc checkdb only in the third step,because User does not want to loose the data. If that don't works, then we have to use allow data loss. but first option is better to restore with available backup, if back up is not there then we have to use this method.


C.First find which file is corrupted, if Data file corruption immediately take Tail log backup then restore full backup followed by differential , log backups with no recovery finally restore tail log backup with recovery.

D.

1. Ensure that the device file is actually available.
2. Use the supplemental stored procedure sp_resetstatus to reset the status of a suspect database. For more information on sp_resetstatus, see the "Resetting the Suspect Status" topic in the SQL Server Books Online. For SQL Server 6.0 and 6.5, if you have not already done so, create this procedure by executing the Instsupl.sql script, found in the Mssql\Install directory. For SQL Server 7.0 and later, this procedure is created at installation by the inscat.sql script, found in the Mssql\Install directory.
3. Use the supplemental stored procedure sp_resetstatus to reset the status of a suspect database. If you have not already done so, create this procedure by executing the Instsupl.sql script, found in the Mssql\Install directory. For more information on sp_resetstatus, see the "Resetting the Suspect Status" topic in the SQL Server Books Online.
4. Execute sp_resetstatus in the master database for the suspect database:
use master
go
exec sp_resetstatus your_database_name

5. Stop and restart SQL Server.
6. Verify that the database was recovered and is available.
7. Run DBCC NEWALLOC, DBCC TEXTALL and DBCC CHECKDB.

use master
go
sp_configure 'allow updates', 1
reconfigure with override
go

select status from sysdatabases where name = 'Testdb'
4194329
update sysdatabases set status= 32768 where name = 'Testdb'

dbcc rebuild_log ('your database name', 'full path to a new transaction log file')

dbcc rebuild_log ('Testdb', 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\testdb.ldf')

use Testdb
go
sp_dboption 'Testdb, 'single_user', true
go
dbcc checkdb ('Testdb', repair_allow_data_loss)

update sysdatabases set status= 0 where name = 'Testdb'

DBCC CHECKALLOC ('Testdb')
and
DBCC CHECKDB ('Testdb')

sp_dboption 'Testdb', 'single_user', 'false'

use master
go
sp_configure 'allow updates', 0
go

use master
go
exec sp_resetstatus Testdb

DBCC NEWALLOC
DBCC TEXTALL
DBCC CHECKDB

November 7, 2014

Finding Reasons for Slow SQL Server database performance

If you found your SQL Database performance is very slow then you can check below points.

1.Check if any blocking.
2- Check execution plan for any costly operator.
3- We can run DTA and take help from that but keep in mind it's not necessary to implement all suggestions provided by DTA.
4. Index fragmentation .
5. Missing indexes.
6. Stale update stats
7. Blocking & Deadlocks of queries.
8. Excessive compilation & recompilation.
9.Check long queries using trace of suspended records on high working time of transactions.
10.Check RAM utilization at SQL Server.

What is VLF in SQL?

VLF  stand for virtual log file. Its the internal structure of log file.

Virtual log file means while running (DBCC loginfo)it will shows the status 0 indicates Recoverable 1 indicates Active 2 indicates unused 3.indicates reused.

The Tlog file internally divided into virtual log files. You can see how many vlfs you have in your log file can be find out by running dbcc loginfo. Too many vlfs will might slow down the db recovery. Each vlf will be having status 0 to 9. These status number indicates the current vlf's status i.e 0 reusable 1 waiting for log backup 2 Active and so on.

November 3, 2014

What is a minimal logged in SQL

1.Minimal information it writes in the log file, when databse in bulk model.

2.If transaction is minimally logged only enough information is logged in transaction log to rollback the transaction no information is logged to roll it forward. This is why you loose point in time recovery if operation is minimally logged.

November 1, 2014

What is difference between Single user mode and Offline mode om SQL

1.When database is in offline users cant connect we need to bring our database in to emergency mode to connect to db for this we will try to bring in single user mode so that we can able to connect with single user.

2.Single user mode means access only SA admin,offline mode means no one user can not access.

3.To bring the database online from offline,there is no need of putting it in emergency mode.

4.If 
database in single user mode it will allow only one connection, it is in offline we can't do any thing.

October 26, 2014

Total IPs Required for fail-over clustering in SQL

How many Total ip's required build win&SQL Failover clustering Active/Active & Active/Passive?

Ans.In a 2 node cluster each ip for each node and one ip for win cluster and one for msdtc and one for each cluster group .. If u use private network for heartbeat then 2 more ip .. So total of 5-7

In a two node cluster 7ip's are needed two private ip's for connecting to quarum. one for node A and other for node B, two public ip's one for node A and other one For node B and one for msdic and one for msclust and one ip for switch for connecting to san drives in which data is stored. 

Total 7ip's are needed.

Script to apply any user permission to the all databases of SQL

/* It is a new feature and it has ability to create user defined server roles
and assign server level/scope permissions. */
/*
- Following actions are performed to implement this new feature:
- Created Server role
- Created Login and made member of server role
- Granted Standard view permission
After completion above steps the login has ability to make connection any database with data reader
permission.
*/

USE master
GO
CREATE SERVER ROLE ALLDBREADER
Go
CREATE LOGIN [DBREADER] WITH PASSWORD=N'123',
DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE ALLDBREADER ADD MEMBER [DBREADER]
GO
GRANT CONNECT SQL TO ALLDBREADER
GRANT VIEW ANY DATABASE TO ALLDBREADER
GRANT VIEW ANY DEFINITION TO ALLDBREADER
GRANT VIEW SERVER STATE to ALLDBREADER
GRANT CONTROL SERVER TO ALLDBREADER
DENY SHUTDOWN TO ALLDBREADER
Go

USE master
GO
CREATE SERVER ROLE ALLDBREADER
Go
GRANT CONTROL SERVER TO ALLDBREADER
DENY ALTER ANY DATABASE TO ALLDBREADER
Go

October 7, 2014

Script or query for FAILED JOBS Details with error Messages in SQL

You can use following Script or query for FAILED JOBS Details with error Messages in SQL. I hope it will help you.

USE Msdb
Declare @lastRunDate int =replace(CAST(getdate()as date),'-','')
SELECT Sj.name as JobName ,CASE SJH.run_status WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
END AS JobStatus,SJH.message from sysjobhistory SJH inner join sysjobs SJ
ON SJH.job_id = SJ.job_id
where SJH.step_id = 0
and sjh.run_date =@lastRunDate
and SJH.run_status =0

September 30, 2014

What is SQL Server Profile

SQL Server Profiler. It is a graphical based tool provided by SQL Server. It enables you to trace a query. For example.you have a SP which you wish to trace. you can execute the SP and start a profiler trace on it. It will show you row by row execution of the SP

SQL Server Profiler is used to trace and analyze the SQL Server running processes and queries in the cases of performance issue.

It is taken high memory so not used in main server.

It is have so many events for trace. so your select desired event and filter it.

It can use to find the blocking by applying trace and catch everythng runing on the server

How to Get historical data of blocked process in SQL


WITH [Blocking]AS (SELECT w.[session_id]
,s.[original_login_name]
,s.[login_name]
,w.[wait_duration_ms]
,r.[scheduler_id]
,w.[wait_type]
,r.[status]
,r.[wait_resource]
,w.[resource_description]
,s.[program_name]
,w.[blocking_session_id]
,s.[host_name]
,r.[command]
,r.[percent_complete]
,r.[cpu_time]
,r.[total_elapsed_time]
,r.[reads]
,r.[writes]
,r.[logical_reads]
,r.[row_count]
,q.[text]
,q.[dbid]
,p.[query_plan]
,r.[plan_handle]
FROM [sys].[dm_os_waiting_tasks] w
INNER JOIN [sys].[dm_exec_sessions] s ON w.[session_id] = s.[session_id]
INNER JOIN [sys].[dm_exec_requests] r ON s.[session_id] = r.[session_id]
CROSS APPLY [sys].[dm_exec_sql_text](r.[plan_handle]) q
CROSS APPLY [sys].[dm_exec_query_plan](r.[plan_handle]) p
WHERE w.[session_id] > 50
)
Insert into Blocking_v1 ([session_id]
,[blocking_session_id]
,[WaitingUserSessionLogin]
,[BlockingUserSessionLogin]
,[scheduler_id]
,[WaitingUserConnectionLogin]
,[BlockingSessionConnectionLogin]
,[WaitDuration]
,[WaitType]
,[WaitRequestMode]
,[WaitingProcessStatus]
,[BlockingSessionStatus]
,[WaitResource]
,[WaitResourceType]
,[WaitResourceDatabaseID]
,[WaitResourceDatabaseName]
,[WaitResourceDescription]
,[WaitingSessionProgramName]
,[BlockingSessionProgramName]
,[WaitingHost]
,[BlockingHost]
,[WaitingCommandType]
,[WaitingCommandText]
,[WaitingCommandRowCount]
,[WaitingCommandPercentComplete]
,[WaitingCommandCPUTime]
,[WaitingCommandTotalElapsedTime]
,[WaitingCommandReads]
,[WaitingCommandWrites]
,[WaitingCommandLogicalReads]
,[WaitingCommandQueryPlan]
,[WaitingCommandPlanHandle])
SELECT b.[session_id] AS [WaitingSessionID]
,b.[blocking_session_id] AS [BlockingSessionID]
,b.[login_name] AS [WaitingUserSessionLogin]
,s1.[login_name] AS [BlockingUserSessionLogin]
,b.scheduler_id
,b.[original_login_name] AS [WaitingUserConnectionLogin]
,s1.[original_login_name] AS [BlockingSessionConnectionLogin]
,b.[wait_duration_ms] AS [WaitDuration]
,b.[wait_type] AS [WaitType]
,t.[request_mode] AS [WaitRequestMode]
,UPPER(b.[status]) AS [WaitingProcessStatus]
,UPPER(s1.[status]) AS [BlockingSessionStatus]
,b.[wait_resource] AS [WaitResource]
,t.[resource_type] AS [WaitResourceType]
,t.[resource_database_id] AS [WaitResourceDatabaseID]
,DB_NAME(t.[resource_database_id]) AS [WaitResourceDatabaseName]
,b.[resource_description] AS [WaitResourceDescription]
,b.[program_name] AS [WaitingSessionProgramName]
,s1.[program_name] AS [BlockingSessionProgramName]
,b.[host_name] AS [WaitingHost]
,s1.[host_name] AS [BlockingHost]
,b.[command] AS [WaitingCommandType]
,b.[text] AS [WaitingCommandText]
,b.[row_count] AS [WaitingCommandRowCount]
,b.[percent_complete] AS [WaitingCommandPercentComplete]
,b.[cpu_time] AS [WaitingCommandCPUTime]
,b.[total_elapsed_time] AS [WaitingCommandTotalElapsedTime]
,b.[reads] AS [WaitingCommandReads]
,b.[writes] AS [WaitingCommandWrites]
,b.[logical_reads] AS [WaitingCommandLogicalReads]
,b.[query_plan] AS [WaitingCommandQueryPlan]
,b.[plan_handle] AS [WaitingCommandPlanHandle]
FROM [Blocking] b
INNER JOIN [sys].[dm_exec_sessions] s1
ON b.[blocking_session_id] = s1.[session_id]
INNER JOIN [sys].[dm_tran_locks] t
ON t.[request_session_id] = b.[session_id]
WHERE t.[request_status] = 'WAIT'
GO

What is the Difference b/w SP_WHO n SP_WHO2 in SQL

1.SP_who2 shows one extra column that is RAM utilization than SP_WHO.

2.SP_who2 has these extra columns 
a. CPU Time
b. Disk IO
c. Last Batch
d. Program Name , apart from that SP_who is documented and Sp_who2 is not documented in BOL.

August 24, 2014

What is Covering Index in SQL

A covering index is a non-clustered index which includes all columns referenced in the query and therefore, the optimizer does not have to perform an additional look-up to the table in order to retrieve the data requested. As the data requested is all indexed by the covering index, it is a faster operation.

covering index is a non-clustered index which stores additional columns at leaf level to avoid bookmark lookup to heap/clustered index.this is typically created using INCLUDE clause.

Let say we have a table tab1 and we have columns starts from column 1 to 10. and we have clustered index on column1 and non clustered index on column 2 . for example you written a query select * from tab1 where column 2=200. this query first will go and look for the matching data in non clustered index and because of our query want to retrieve all the columns it will perform key look up , not helathy. this time i had one more non clustered index on column3 and column4 ,composite.in this case our query select column3,column4 from tab1 where column3 =200 no need to check in clustered index and table it can retrieve the data from non clustered index(in this case covering) index.

August 20, 2014

Port in sql server

SQL server assign default port 1433 to default instance...for named instance it dynamically allots a port number, ideally it is five digit port number.

To see port of SQL Server you can check as below.

Go to configuration manager-->SQL Serve network Configuration-> Select instance-->right click TCP/IP properties-->IP Properties