SQL server performance trouble shooting tools

http://sqlserver-qa.net/blogs/tools/archive/2008/06/05/sql-server-troubleshooting-tools-pssdiag-sqldiag-sqlnexus-rml-utilities-and-readtrace-which-one-to-choose.aspx

Bunch of articles for SQL performace

http://www.simple-talk.com/sql/performance/

Finding the Causes of Poor Performance in SQL Server

Finding the Causes of Poor Performance in SQL Server, Part 1

Finding the Causes of Poor Performance in SQL Server, Part 2

Add "Command Prompt Here" Shortcut to Windows Explorer

Through the Registry

Navigate in your Registry to
HKEY_LOCAL_MACHINE/Software/Classes/Folder/Shell
and create a key called "Command Prompt" without the quotes.

Set the default string to whatever text you want to appear in the right-click menu.
Create a new key within your newly created command prompt named "command," and set the default string to
cmd.exe /k pushd %1
You may need to add %SystemRoot%\system32\ before the cmd.exe if the executable can't be found.

The changes should take place immediately. Right click a folder and your new menu item should appear.

Clean Duplicate records

DECLARE @Count int
DECLARE @name nvarchar(50)
DECLARE @culture_name nvarchar(50)

DECLARE dublicate_cursor CURSOR FAST_FORWARD FOR
SELECT name, culture_name , Count(*) - 1
FROM resource
GROUP BY name, culture_name
HAVING Count(*) > 1

OPEN dublicate_cursor

FETCH NEXT FROM dublicate_cursor INTO @name, @culture_name, @Count

WHILE @@FETCH_STATUS = 0
BEGIN

SET ROWCOUNT @Count
DELETE FROM resource WHERE name = @name AND culture_name = @culture_name
SET ROWCOUNT 0

FETCH NEXT FROM dublicate_cursor INTO @name, @culture_name, @Count
END

CLOSE dublicate_cursor
DEALLOCATE dublicate_cursor

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

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:

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

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