You have a large database which is currently in recovery, and you have no idea how long it will take. I’ve been there more than once, and this has helped me out:
SELECT der.session_id, der.command, der.status, der.percent_complete, *
FROM sys.dm_exec_requests AS der
Also, I found a nice post the other day with a query which displays time remaining on a restore. Could come in handy! Check it out here: How much longer will the SQL Server database restore take
Today I’d like to share with you a simple script to check fragmentation on indexes.
You can of course edit the fragmentation percentages from 10 and 40 to something else if you’d like to categorize them differently.
SELECT
OBJECT_NAME(indstat.object_id, indstat.database_id) AS obj_name,
QUOTENAME(sysind.name) [index_name],
CASE
WHEN avg_fragmentation_in_percent < 10 THEN 'LOW'
WHEN avg_fragmentation_in_percent < 40 THEN 'MEDIUM'
ELSE 'HIGH'
END as frag_level,
indstat.*
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED')
AS indstat
INNER JOIN sys.indexes sysind ON indstat.object_id = sysind.object_id AND
indstat.index_id = sysind.index_id
ORDER BY avg_fragmentation_in_percent DESC
The first time I needed to specify port was when I had to connect through a firewall, and no ports were open except for the SQL port which was set to a non-standard port (IOW: not 1433).
I searched for what felt like hours on microsofts sites and google to no avail. I tried colon, space and everything I could think of to specify port number but nothing worked.
I ended up asking a colleague of mine who has worked with MS SQL for many, many years, and it turned out to be the easiest thing in the world! You just separate the host name or IP (and instance if not installed as default) and the port number with a comma. I’ve never used a comma before so I never thought to try it.
Example if you want to connect your instance with IP 1.2.3.4 and name myinstance on port 61195 you type 1.2.3.4\myinstance,61195.
If DNS is available you could just type MyHostName\MyInstance,61195.
This didn’t end up being the most interesting post in the world, but my hope is that someone else searching for the answer will find it some day 
I’ve previously posted about deadlocks in MSSQL and figuring out the process creating problems. However; deadlocks is only a tiny bit of what can be bottlenecks in your setup. I found this tool some time ago which could analyze trace files and suggest where your bottlenecks are. It’s called SQL Nexus, is open source and available here.
Another tip would be to run Microsofts best practices analyzer. If you’re not sure if your setup is according to best practices, it can be a real help!
Links for: SQL 2005 and SQL 2008 R2.
I’ve worked with MSSQL databases for some years, and deadlock situations are always something which can ruin an otherwise good setup.
Here’s something I found useful in finding the culprit:
First execute
SELECT * FROM sys.sysprocesses WHERE blocked <> 0
Find the process(es) you think create the unwanted deadlock. Find the SQL_HANDLE for that process and execute the following where you change SQL_HANDLE out with the SQL_HANDLE you’ve found.
USE [tempdb]
GO
CREATE TABLE Test
(RowID INT IDENTITY PRIMARY KEY)
GO
CREATE TRIGGER Audit_Test
ON Test
FOR INSERT, UPDATE, DELETE
AS
BEGIN
SELECT TEXT
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(SQL_HANDLE)
WHERE session_id = @@SPID
END
GO
INSERT INTO Test DEFAULT VALUES;
GO