Space used in tempdb by each Session

SELECT DB_NAME(DATABASE_ID) DBNAME
,SP.SESSION_ID
,S.LOGIN_NAME
,S.HOST_NAME
,(SUM(USER_OBJECTS_ALLOC_PAGE_COUNT)*8)/1024.00 ALLOCATED_SPACE_MB
,(SUM(USER_OBJECTS_DEALLOC_PAGE_COUNT)*8)/1024.00 DEALLOCATED_SPACE_MB
FROM TEMPDB.SYS.DM_DB_SESSION_SPACE_USAGE SP
INNER JOIN TEMPDB.SYS.DM_EXEC_SESSIONS S ON S.SESSION_ID = SP.SESSION_ID AND S.HOST_NAME IS NOT NULL
GROUP BY DB_NAME(DATABASE_ID),SP.SESSION_ID,S.LOGIN_NAME, S.HOST_NAME
ORDER BY ALLOCATED_SPACE_MB DESC

Determining indexes utilization Or unused indexes

You can use following DMV to check index utilization or to see unused indexes.

SYS.DM_DB_INDEX_USAGE_STATS

By determining the stats, you can see what indexes are mostly used by the queries and what indexes are unused and overhead for the system from maintenance point of view. These stats/counters will be initialized to empty whenever SQL Server restarts, database is de-attached, or database is shutdown.

When you perform SELECT, INSERT, UPDATE or DELETE operations, then this DMV keeps track all of your performed operations in each separate counter as:

USER_SCANS: selection performed on whole table without specifying any criteria. (SELECT * operation)

USER_SEEKS: selection performed by specifying any criteria. (Either looking up a single or doing range scan)

USER_LOOKUPS: large selection performed with specified criteria by using non-clustered indexes.

USER_UPDATES: level of maintenance when INSERT, UPDATE or DELETE operation performed.

Above counters have associated dates, these are updated when above any counter incremented. This means this date will reflect about the last date of action performed or last time when index was accessed.

LAST_USER_SCAN
LAST_USER_SEEK
LAST_USER_LOOKUP
LAST_USER_UPDATE

You can use the following query to get index name and its associated user stats.

SELECT SI.INDEX_ID,SI.NAME,USER_SCANS,USER_SEEKS,USER_LOOKUPS, USER_UPDATES, LAST_USER_SCAN, LAST_USER_SEEK, LAST_USER_LOOKUP,LAST_USER_UPDATE
FROM SYS.DM_DB_INDEX_USAGE_STATS DMI
INNER JOIN SYS.INDEXES SI ON SI.INDEX_ID=DMI.INDEX_ID AND DMI.OBJECT_ID=SI.OBJECT_ID
WHERE DMI.OBJECT_ID=OBJECT_ID('TEST')

Example:

1) Create test table
CREATE TABLE TEST
(VAL1 INT IDENTITY(1,1)
,VAL2 VARCHAR(2)
,VAL3 VARCHAR(3)
)

2) Create Indexes
CREATE CLUSTERED INDEX VAL1_INDEX ON TEST (VAL1)
CREATE NONCLUSTERED INDEX VAL2_INDEX ON TEST (VAL2)
CREATE NONCLUSTERED INDEX VAL3_INDEX ON TEST (VAL3)

3) Insert test data

DECLARE @I INT
SET @I=1
WHILE (@I<100000)
BEGIN
INSERT INTO TEST VALUES('C','B')
SET @I=@I+1
END

4) Check USER_UPDATES and LAST_USER_UPDATE values

5) Update 50% data with new values

UPDATE TEST SET VAL2='D',VAL3='F' WHERE VAL1>50000

6) Check values for USER_UPDATES, LAST_USER_UPDATE, USER_SEEKS, LAST_USER_SEEK

7) SELECT * FROM TEST

8) Check values for USER_SCANS and LAST_USER_SCAN

9) SELECT * FROM TEST WHERE VAL1=40

10) Check values for USER_SEEKS and LAST_USER_SEEK

11) SELECT * FROM TEST WHERE VAL2='F'

12) Check values for USER_SEEKS, LAST_USER_SEEK, USER_LOOKUPS and LAST_USER_LOOKUP

Calcualting estimated time for any running SQL command

you can use the following SQL statement to calculate estimated time for any sql command. This script is applicable for Sql Server 2005 and above.

1) you can get the command label by executing below procedures or sql statement, that you will use in estimated time calculation query.

Exec sp_Who
Or
Exec sp_Who2
Or
Select * From sys.sysprocesses
Or
Select * From sys.dm_exec_requests

2) script for estimated time calculation, in example it is calculating time for back up command.
-------------------------------------
SELECT
R.SESSION_ID
,R.COMMAND
,CONVERT(NUMERIC(6,2),R.PERCENT_COMPLETE) AS [PERCENT COMPLETE]
,CONVERT(VARCHAR(20),DATEADD(MS,R.ESTIMATED_COMPLETION_TIME,GETDATE()),20) AS [ETA COMPLETION TIME]
,CONVERT(NUMERIC(6,2),R.TOTAL_ELAPSED_TIME/1000.0/60.0) AS [ELAPSED MIN]
,CONVERT(NUMERIC(6,2),R.ESTIMATED_COMPLETION_TIME/1000.0/60.0) AS [ETA MIN]
,CONVERT(NUMERIC(6,2),R.ESTIMATED_COMPLETION_TIME/1000.0/60.0/60.0) AS [ETA HOURS]
,CONVERT(VARCHAR(100)
,(SELECT SUBSTRING(TEXT,R.STATEMENT_START_OFFSET/2
,CASE WHEN R.STATEMENT_END_OFFSET = -1 THEN 1000 ELSE (R.STATEMENT_END_OFFSET-R.STATEMENT_START_OFFSET)/2 END)
FROM SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE)))
FROM SYS.DM_EXEC_REQUESTS R
WHERE COMMAND IN ('BACKUP DATABASE')

---------------------------------

How to get Sql text without using dbcc inputbuffer

You can use a dynamic management view for this purpose, details given below:

Dynamic Management View:
sys.dm_exec_sql_text(sql_handle)

sql_handle SQL handle of the text to be looked up.

It retruns:
dbid - Database ID, you can get database name by using DB_Name(dbid) function.
Objectid - Object ID, you can get Object name by using Object_Name(objectid) funciton.
number - store procedures number if any
encrypted - if text is encrypted, it will have value 1
text - text of SQL query

Example:

SELECT T.TEXT, P.*
FROM SYS.SYSPROCESSES P
OUTER APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) T