Small and simple points that can make huge improvement of performance

In the most cases, small and simple points that can make huge improvement of performance - more than complicated SQL tricks. A little thinking is usually the best thing.

Here are some examples.

Don't do unnecessary JOINs!

Don't think that "it's better to bring more data - it can't make a damage" - it can! it can make more SQL work, more data, more calculations, etc.

Don't do LEFT JOIN where you can DO JOIN!

The explanation is simple: INNER JOIN looking for the match, and if it don't find it - it goes on, and LEFT JOIN don't filter, so it continue to scan the entire table. This causes that INNER JOIN uses indexes, etc. in order to be efficient and quick, what LEFT JOIN can't do.

As in the previous tip - don't think "it's better to bring more data - it can't make a damage".

Filter!

When it's required and when you can - filter (using Where clauses). Filter also can use indexes, etc. that wouldn't be used without it.

I improved performance of the query from 15 minutes to 1 second simply by add a WHERE clause. In most cases, the addition of this condition is also important both correct and logical - not only for performance.

SQL Server Error Log Scanning

Exec master..xp_readerrorlog

http://www.sqlservercentral.com/articles/Administration/72162/

Locks details

Get current locks details:

SELECT  EC2.session_id AS 'Blocker Process Id',

        ES2.login_name AS 'Blocker Login Name',

        ES2.program_name AS 'Blocker Program',

        DB_Name(ER2.database_id) AS 'Blocked DB Name',

        ES2.host_name AS 'Blocker Computer',

        CA2.[text] AS 'Blocker Command',

        EC1.session_id AS 'Blocked Process Id',

        ES1.login_name AS 'Blocked Login Name',

        ES1.program_name AS 'Blocked Program',

        DB_Name(ER1.database_id) AS 'Blocker DB Name',

        ES1.host_name AS 'Blocked Computer',

        CA1.[text] AS 'Blocked Command',

        Cast(Cast((WT1.wait_duration_ms/60000.0) As Decimal(15,2)) As VarChar) + ' min.' 

AS 'Blocked Time',

        (Case When WT2.session_id Is Null Then 'Top level blocker' Else 'Secondary blocker' End)

AS 'Block Level'

FROM sys.dm_exec_connections EC1

INNER JOIN sys.dm_exec_sessions ES1 ON EC1.session_id=ES1.session_id

INNER JOIN sys.dm_exec_requests ER1 ON EC1.session_id=ER1.session_id

CROSS APPLY sys.dm_exec_sql_text(EC1.most_recent_sql_handle) CA1

INNER JOIN sys.dm_os_waiting_tasks WT1 ON EC1.session_id=WT1.session_id

LEFT JOIN sys.dm_os_waiting_tasks WT2 ON WT1.blocking_session_id=WT2.session_id

INNER JOIN sys.dm_exec_connections EC2 ON WT1.blocking_session_id=EC2.session_id

INNER JOIN sys.dm_exec_sessions ES2 ON EC2.session_id=ES2.session_id

INNER JOIN sys.dm_exec_requests ER2 ON EC2.session_id=ER2.blocking_session_id

CROSS APPLY sys.dm_exec_sql_text(EC2.most_recent_sql_handle) CA2

ORDER BY 'Blocked Time' Desc

http://blogs.microsoft.co.il/blogs/gerireshef/archive/2013/08/14/2406578.aspx

Move SQL server database files

-- 1. set DB offline:

ALTER DATABASE MyDataBase SET OFFLINE;

-- 2. move the files

ALTER DATABASE MyDataBase MODIFY FILE ( NAME = 'MyBbFile', FILENAME = 'E:\SQL\Data\MyBbFile.mdf' );

ALTER DATABASE MyDataBase MODIFY FILE ( NAME = 'MyBbFile_log', FILENAME = 'E:\SQL\Log\MyBbFile_log.ldf' );

ALTER DATABASE MyDataBase MODIFY FILE ( NAME = 'MyBbFile_BLOBStorage', FILENAME = 'E:\SQL\BLOB\MyBbFile.ndf' );

-- 3. copy-paste the files to the new folder (via Windows)

-- 4. set DB online:

ALTER DATABASE MyDataBase SET ONLINE;

-- get data of the DB files:

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'MyDataBase');

Get tables size

SELECT t.NAME AS TableName,

p.rows AS RowCounts,

SUM(a.total_pages) * 8 AS TotalSpaceKB, 

SUM(a.used_pages) * 8 AS UsedSpaceKB, 

(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB

FROM sys.tables t

INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID 

AND i.index_id = p.index_id

INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id

WHERE t.is_ms_shipped = 0

GROUP BY t.Name, p.Rows

ORDER BY TotalSpaceKB desc

Get aggregations of few columns for each record - using 'Values' operator

SELECT ValuesTestID, ValuesTestName, 

IntCol1, IntCol2, IntCol3, IntCol4, IntCol5, IntCol6,

( Select MAX(ValuesTestName)

From    (Values (IntCol1), (IntCol2), (IntCol3), (IntCol4), (IntCol5), (IntCol6)) 

UniqueColumn(ValuesTestName)

) MaxIntCol,

( Select SUM(ValuesTestName)

From    (Values (IntCol1), (IntCol2), (IntCol3), (IntCol4), (IntCol5), (IntCol6)) 

UniqueColumn(ValuesTestName)

) SumIntCol

FROM ValuesTest

Get DBs files locations and drive details

SELECT DISTINCT 

dovs.database_id as database_id,

DB_NAME(dovs.database_id) as DBName,

mf.physical_name as PhysicalFileLocation,

dovs.logical_volume_name as LogicalDriveName,

dovs.volume_mount_point as Drive,

CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB

FROM sys.master_files mf

CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs

ORDER BY DB_NAME(dovs.database_id)