v$session for oracle and sql server

select * from sys.sysprocesses

can be used for sql server instead of v$session in oracle

If you work with SQL Server or Oracle you at some point probably will need to determine your unique connection identifier. For SQL Server, the connection identifier is called a Server Process ID or SPID. It can be obtained from the global @@SPID as:
SELECT @@SPID
Once the SPID is determined, it can be used to query various system tables (e.g., sysprocesses, sysobjects, etc.) to obtain process information.
For Oracle, the connection identifier is called a Session ID or SID. To get the Session ID, the V$SESSION view must be queried as:

SELECT sid from v$session where audsid = userenv('sessionid');
When the SID is determined, it can be used to find out session information using other Oracle provided system views such as V$SQL.

Disk I/O:The Performance Bottle neck

Many people think of "performance tuning" as optimizing loops, algorithms, and memory use. In truth, however, you don't get the huge performance gains from optimizing CPU and memory use (which is good), but from eliminating I/O calls.

Disk I/O is responsible for almost all slow websites and desktop applications. It's true. Watch your CPU use next time you open a program, or your server is under load. CPUs aren't the bottleneck anymore - your hard drive is. At the hardware level, the hard drive is the slowest component by an incredibly large factor. Today's memory ranges between 3200 and 10400 MB/s. In contrast, today's desktop hard drive speeds average about 50 MB/s (Seagate 500GB), with high-end drives getting 85MB/s (WD 640, Seagate 1TB). If you're looking at bandwidth, hard drives are 200-300 times slower. Bandwidth, though, isn't the killer - it's the latency. Few modern hard drives have latencies under 13 milliseconds - while memory latency is usually about 5 nanoseconds - 2,000 times faster.

You're probably looking at these numbers and thinking, "13ms is quite fast enough for me, and my app is only dealing with small files". However, I have a question: what other applications are using that drive? If you're on a shared server, the odds are high that between 25 and 2500 ASP.NET apps are being run on the same drive.

CPU, bandwidth, and memory throttling is becoming more and more common on shared servers and virtualization systems, but practical disk throttling isn't even on the horizon from what I can tell. Improper I/O usage from any app affects everybody.

Since hard drives are slow, pending operations go into a queue. So even if your app only needs a single byte of data from the hard drive, it still has to wait its turn. It's quite common for disk operations to take several seconds on a shared server under heavy load. If any application on the server is paging to disk from exessive memory use, it can take several minutes, causing a timeout.

Realistic I/O performance is really hard to simulate in a development environment. On a dedicated development machine, disk queues are short, and response times are usually near the optimal 13ms, which tends to give software developers gravely incrorrect ideas about the performance characteristics of their application.

This is one of my favourite article i read,refer Click to read more

String Searching in a database

This is the query which searches for string in the entire DB.
Provide 2 inputs as DB name and Search string.
This query takes more time, but will sure get you the results.
You can customize the search criteria…


----------------------------------------------------------
Use [Database_Name]
go
declare @SearchChar varchar(8000)
Set @SearchChar = 'Search_Text' -- Like 'renju%', '11/11/2006'
declare @CMDMain varchar(8000), @CMDMainCount varchar(8000),@CMDJoin varchar(8000)
declare @ColumnName varchar(100),@TableName varchar(100)
declare dbTable cursor for
SELECT
Distinct b.Name as TableName
FROM sysobjects b
WHERE
b.type='u' and b.Name <> 'dtproperties' order by b.name

open dbTable
fetch next from dbTable into @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
declare db cursor for
SELECT c.Name as ColumnName
FROM sysobjects b,syscolumns c
WHERE C.id = b.id and
b.type='u' and b.Name = @TableName
order by b.name

open db
fetch next from db into @ColumnName
set @CMDMain = 'SELECT ' + char(39) + @TableName + char(39) + ' as TableName,'+
' ['+ @TableName + '].* FROM [' + @TableName + ']'+
' WHERE '
set @CMDMainCount = 'SELECT Count(*) FROM [' + @TableName + '] Where '
Set @CMDJoin = ''

WHILE @@FETCH_STATUS = 0
BEGIN
set @CMDJoin = @CMDJoin + 'Convert(varchar(5000),[' +@ColumnName + ']) like ' + char(39) + @SearchChar + char(39) + ' OR '
fetch next from db into @ColumnName
end
close db
deallocate db
Set @CMDMainCount = 'If ('+ @CMDMainCount + Left(@CMDJoin, len(@CMDJoin) - 3)+ ') > 0 Begin '
Set @CMDMain = @CMDMainCount + @CMDMain + Left(@CMDJoin, len(@CMDJoin) - 3)
Set @CMDMain = @CMDMain + ' End '
Print @CMDMain
exec (@CMDMain)
fetch next from dbTable into @TableName
end
close dbTable
deallocate dbTable


Renju