Get top total logical reads queries
dm_exec_query_plan, dm_exec_query_stats
March 2020
How to and where to use inbuilt objects in SQL server
There are multiple options to get database table and index details. Here I explain some useful objects and their use. sp_help Reports information about a database object (any object listed in the sys.sysobjects compatibility view), a user-defined data type, or a data type. https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-help-transact-sql?view=sql-server-ver15 sp_helpindex Reports information about the indexes on a table or view. https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-helpindex-transact-sql?view=sql-server-ver15 sp_helpconstraint …
How to and where to use inbuilt objects in SQL server Read More »
How to get all database-level information using T-SQL
How to get database information using system tables. Here is the sample with flowing fields: database_id : Database Id dbName : Database name Status : database state like 0 DataFiles : count of data files 1 = RESTORING 2 = RECOVERING | SQL Server 2008 and later 3 = RECOVERY_PENDING | SQL Server 2008 and …
How to get all database-level information using T-SQL Read More »
How to find unsaved file location for SQL query in management studio
This is important if we not saved files on disk and SQL management studio close. Un save file location C:\Documents and Settings\<user name>\My Documents\SQL Server Management Studio\Backup Files\Windows Vista/7/10 %USERPROFILE%\Documents\SQL Server Management Studio\Backup Files OR %USERPROFILE%\AppData\Local\Temp Sql Query find using statics Query Recovery Settings There is a setting in Management Studio that allows the Autorecover …
How to find unsaved file location for SQL query in management studio Read More »
How to find SQL Server Job history
This topic describes how to view the Microsoft SQL Server Agent job history log using SQL Server Management Studio, Transact-SQL, or SQL Server Management Objects. To view the job history log In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance. Expand SQL Server Agent, and then …
How to kick out all user from SQL server database
There are a few ways to kick out the user from the database. I believe it works with most of SQL Server versions and editions. Complete your task…… Some time need to wait for rallback existing transactions
T-SQL function
What are function available in SQL Server SQL Server supports these analytic functions: CUME_DIST (Transact-SQL) LEAD (Transact-SQL) FIRST_VALUE (Transact-SQL) PERCENTILE_CONT (Transact-SQL) LAG (Transact-SQL) PERCENTILE_DISC (Transact-SQL) LAST_VALUE (Transact-SQL) PERCENT_RANK (Transact-SQL) Analytic functions calculate an aggregate value based on a group of rows. Unlike aggregate functions, however, analytic functions can return multiple rows for each group. Use …
Allways on Availability Group
Here i am trying to explain most common internal objects to get information of database with always on. Following query provides good overview of Always On availability groups health and status. Show All availability groups visible to this server where this Server is a Secondary replica Show All Databases in an availability group visible to …
Top Procedure by execution count
dm_exec_procedure_stats, dm_exec_query_plan
