Pages

Wednesday, May 12, 2010

SQL Query to find the Last Activity Details on a Database

At times there may be need to find when was the last activity done on a database. This also might help you to clean up old unused databases on a SQL Server Instance.

I found out below query with slight modifications of mine.

You can Modify the Order By Criteria as per you convinience.

USE [YOURDATABASENAME]
GO
SELECT
      T.NAME
      ,USER_SEEKS
      ,USER_SCANS
      ,USER_LOOKUPS
      ,USER_UPDATES
      ,LAST_USER_SEEK
      ,LAST_USER_SCAN
      ,LAST_USER_LOOKUP
      ,LAST_USER_UPDATE
FROM
      SYS.DM_DB_INDEX_USAGE_STATS I JOIN
      SYS.TABLES T ON (T.OBJECT_ID = I.OBJECT_ID)
WHERE
      DATABASE_ID = DB_ID('YOURDATABASENAME')
ORDER BY LAST_USER_SEEK DESC

SQL Query to get the running process details

Hi,

I came across this vital SQL Query to find all the process details running on a instance showing the Most CPU consumed process at first.Thought might help.

USE MASTER
GO
SELECT SPID, SP.DBID, DB_NAME(SP.DBID) AS DBNAME,
TEXT,CPU,HOSTNAME,LOGIN_TIME,LOGINAME,STATUS,PROGRAM_NAME,NT_DOMAIN, NT_USERNAME
FROM SYSPROCESSES SP CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE)
ORDER BY CPU DESC

Tuesday, May 4, 2010

Finding Unused Indexes on Database

Sometimes the number of indexes on your database may be huge in number and it would be a teadous task to check which are indexes being used and which are not. So here is one of the solution. I found this T-SQL Statement useful which may ease out your task.

Thanks to Search Engines.

USE [YOURDATABASENAME]
GO
SELECT O.NAME AS OBJECT_NAME, I.NAME AS INDEX_NAME,
I.TYPE_DESC, U.USER_SEEKS, U.USER_SCANS,
U.USER_LOOKUPS, U.USER_UPDATES
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID
LEFT JOIN SYS.DM_DB_INDEX_USAGE_STATS U ON I.OBJECT_ID = U.OBJECT_ID
AND I.INDEX_ID = U.INDEX_ID
AND U.DATABASE_ID = DB_ID()
WHERE O.TYPE <> 'S' -- NO SYSTEM TABLES!
ORDER BY (ISNULL(U.USER_SEEKS, 0) + ISNULL(U.USER_SCANS, 0) + ISNULL(U.USER_LOOKUPS, 0) + ISNULL(U.USER_UPDATES, 0)), O.NAME, I.NAME