วิธีดู user ที่ online อยู่ใน SQL

exec sp_who

คำสั่งหยุดการทำงานของ thread นั้น

kill [XXX]

Dynamics Ax 2012 Error Code 90, Unable to connect to database

-- This query will rollback any transaction which is running on that database

-- and bring SQL Server database in a "single user" mode

ALTER DATABASE DATABASENAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE

-- Marking database READ_ONLY, disable logging,

-- and limiting access only to members of the sysadmin fixed server role

ALTER DATABASE DATABASENAME SET EMERGENCY

-- Checks the logical and physical integrity of all the objects in the specified database

DBCC checkdb(DATABASENAME)

-- In case of simple failure then we can use allow data loss option to recover Database

--  DBCC CheckDB (DATABASENAME, REPAIR_ALLOW_DATA_LOSS)

-- Set database accessibility to it's original state, allowing all logins

ALTER DATABASE DATABASENAME SET MULTI_USER

ALTER DATABASE DATABASENAME  SET ONLINE;

EXEC sp_resetstatus DATABASENAME

Monitor how many memory usage by index in SQL server

sys.dm_os_buffer_descriptors - Returns information about all the data pages that are currently in the SQL Server buffer pool

sys.dm_db_index_usage_stats - Returns counts of different types of index operations and the time each type of operation was last performed in SQL Server.

These query for search index usage in buffer pool

SELECT count(*) AS cached_pages_count, obj.name, index_id, i.name AS IndexName
FROM sys.dm_os_buffer_descriptors AS bd
    INNER JOIN
    (
        SELECT object_id, object_name(object_id) AS name
            ,index_id ,allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p
                ON au.container_id = p.hobt_id
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT object_id, object_name(object_id) AS name  
            ,index_id, allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p
                ON au.container_id = p.partition_id
                    AND au.type = 2
    ) AS obj
        ON bd.allocation_unit_id = obj.allocation_unit_id
    LEFT JOIN sysindexes i ON obj.object_id = i.id AND obj.index_id = i.indid
WHERE database_id = db_id()
GROUP BY obj.name, index_id, i.name
--HAVING obj.name = 'INVENTSERIAL'

ORDER BY cached_pages_count DESC;

Clear MS SQL database buffer คำสั่งเครีย buffer ของ SQL server

DBCC DROPCLEANBUFFERS