Memory Grants Pending value
dm_os_performance_counters
April 2020
Find a current blocking connection
dm_tran_locks dm_os_waiting_tasks dm_exec_requests sys.sysprocesses
SQL Server Permission
This query populate result for SQL server login with role by column.
Kill all user processes for a database
Be careful with this stored procedure! I usually use in the Test/Dev environments. TSQL Procedures
I/O Statistics by file for the current database
dm_io_virtual_file_stats, database_files
Database model
There are three levels of data modeling, conceptual data model, logical data model, and physical data mode. Feature Conceptual Logical Physical Entity Names ✓ ✓ Entity Relationships ✓ ✓ Attributes /Column ✓ Primary Keys ✓ ✓ Foreign Keys ✓ ✓ Table Names ✓ Column Names ✓ …
How to access remote server database tables
By default, SQL Server does not allow ad hoc distributed queries using OPENROWSET and OPENDATASOURCE. When this option is set to 1, SQL Server allows ad hoc access. When this option is not set or is set to 0, SQL Server does not allow ad hoc access. https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/ad-hoc-distributed-queries-server-configuration-option?view=sql-server-ver15 OPENROWSET (Transact-SQL) Includes all connection information that …
How to find SQL Server database job step info using T-SQL
dbo.sysjobs (Transact-SQL) Stores the information for each scheduled job to be executed by SQL Server Agent. This table is stored in the msdb database. dbo.sysjobsteps (Transact-SQL) Contains the information for each step in a job to be executed by SQL Server Agent. This table is stored in the msdb database. dbo.sysproxies (Transact-SQL) Defines attributes of a SQL Server Agent …
How to find SQL Server database job step info using T-SQL Read More »
How to find SQL Server job fail from history
The dbo.sysjobhistory is Contains information about the execution of scheduled jobs by SQL Server Agent in MSDB database. Additional function agent_datetime Reference https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/dbo-sysjobhistory-transact-sql?view=sql-server-ver15
How to find SQL Server procedure statistics.
Returns aggregate performance statistics for cached stored procedures. The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view. On SQL Server, requires VIEW …
