Tuesday, March 30, 2010

Find the table column order not matching

if(OBJECT_ID('tempdb..#tmp')>1)
drop table #tmp

create table #tmp (database_name nvarchar(100), table_name nvarchar(100), column_name nvarchar(100), ordinal_position int)


EXEC master..sp_MSForEachDB '
if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
use [?]
insert into #tmp
select db_name(), table_name , column_name , ordinal_position from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ''users''
end
'
go

select * from #tmp;

select *
from #tmp t1
inner join #tmp t2 on t1.column_name = t2.column_name
where t1.ordinal_position <> t2.ordinal_position

Wednesday, March 24, 2010

A simple way to loop true the time counter

startme:
print convert(nvarchar(100),GETDATE(),109);
waitfor delay '00:00:01';

if(GETDATE() > '2010-03-24 15:18:00.713')
begin
goto endme;
end
else
begin
goto startme;
end

endme:

Wednesday, March 17, 2010

Displaying Execution Plans

Check the query IO, TIME, Execution Plan, Plan Text while running a query

Displaying Execution Plans1

Select Top 100 Slow Queries

SELECT DISTINCT TOP 100
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
WHERE s.execution_count > 1
ORDER BY AvgElapsedTime desc , s.max_elapsed_time DESC, ExecutionCount DESC;
GO

Tuesday, March 16, 2010

View SQL cached Plans and clean the execution cached plans

use Master
go
--clear cache
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
go

USE master
GO
--view cached plans
SELECT UseCounts,RefCounts, Cacheobjtype, Objtype,
ISNULL(DB_NAME(dbid),'ResourceDB') AS DatabaseName, TEXT AS SQL
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
ORDER BY dbid,usecounts DESC;
GO

Friday, March 12, 2010

sp_kill

use master
go

if exists (select name from sysobjects where name = 'sp_kill' and type = 'p' )
drop proc sp_kill
go

create proc sp_kill
@dbname varchar(100) = null, -- When specified, kills all spids inside of the database
@loginame varchar(50) = null, -- When specified, kills all spids under the login name
@hostname varchar(50) = null -- When specified, kills all spids originating from the host machine
as
begin
set nocount on
select spid, db_name(dbid) as 'db_name', loginame, hostname into #tb1_sysprocesses from master.dbo.sysprocesses (nolock)
declare @total_logins int, @csr_spid varchar(100)
set @total_logins = ( select count(distinct spid) from #tb1_sysprocesses )
if @dbname is null
begin
if @loginame is null
begin
if @hostname is null
begin
if @total_logins > 0
begin
declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where loginame <> 'sa' and spid <> @@spid
open csr_spid
fetch next from csr_spid into @csr_spid
while @@fetch_status = 0
begin
set nocount on
exec ('kill ' + @csr_spid)
fetch next from csr_spid into @csr_spid
end
close csr_spid
deallocate csr_spid
end
end
else
begin
if @total_logins > 0
begin
declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where hostname = @hostname and loginame <> 'sa' and spid <> @@spid
open csr_spid
fetch next from csr_spid into @csr_spid
while @@fetch_status = 0
begin
set nocount on
exec ('kill ' + @csr_spid)
fetch next from csr_spid into @csr_spid
end
close csr_spid
deallocate csr_spid
end
end
--------------------------------------------------
end
else
begin
if @total_logins > 0
begin
declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where loginame = @loginame and loginame <> 'sa' and spid <> @@spid
open csr_spid
fetch next from csr_spid into @csr_spid
while @@fetch_status = 0
begin
set nocount on
exec ('kill ' + @csr_spid)
fetch next from csr_spid into @csr_spid
end
close csr_spid
deallocate csr_spid
end

end
-----------------------
end
else
begin
if @total_logins > 0
begin
declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where db_name = @dbname and loginame <> 'sa' and spid <> @@spid
open csr_spid
fetch next from csr_spid into @csr_spid
while @@fetch_status = 0
begin
set nocount on
exec ('kill ' + @csr_spid)
fetch next from csr_spid into @csr_spid
end
close csr_spid
deallocate csr_spid
end

end
drop table #tb1_sysprocesses
end