Thoughts on Building Data & AI Systems — Satya Katari

 select top 10 * from master.dbo.sysprocesses  where status <> ‘background’ AND cmd NOT IN  (‘signal handler’,’lock monitor’,’log writer’,’lazy writer’,’checkpoint sleep’,’awaiting command’,’TASK MANAGER’) and spid > 50  and spid <> @@spid  order by CPU desc

Microsoft SQL Server provides DBAs and Developers with several index related dynamic management views and functions, which they can use to maintain, modify, tune and identify missing indexes. Some of these dynamic management views (DMVs) and functions (DMFs) you might use are listed as follow: sys.dm_db_index_operational_stats — Returns information about low level input/output (I/O) operations, locking and latching contention, and access methods. sys.dm_db_index_usage_stats — Returns an index operations count, identifying operations performed and the last time each operation was performed. sys.dm_db_index_physical_stats — Returns information about table and index size and fragmentation, including both in-row and LOB data. sys.dm_db_missing_index_details — Returns detailed information about missing… Read more

1.Master 2. MSDB 3. mssqlsystemresource 4. User Databases 5.  Distribution 6.  Model 7. TempDB

There  are so many way of finding the sql server started date time. 1. SQL Server Log 2.  using DMV 3. Using TempDB Creation date (as it is recreated when SQL Server is started every time) 4. Start Time of Default Trace Examples :  1. SQL Server Log : SQL Server Management Studio –> Management –> SQL Server Logs –> We need to Search for the “SQL SERVER Starting….” or  sp_readerrorlog 0,1,’Copyright (c)’     2. Using DMV : SELECT sqlserver_start_time FROM sys.dm_os_sys_info;   3. Using TempDb Creation Date select crdate from sys.sysdatabases where name=’tempdb’ 4. Default Trace :   SELECT login_time FROM sys.dm_exec_sessions… Read more

Master Purpose – Core system database to manage the SQL Server instance.  In SQL Server 2005, the Master database is the logical repository for the system objects residing in the sys schema.  In SQL Server 2000 and previous editions of SQL Server, the Master database physically stored all of the system objects. Functionality Instance configurations Databases residing on the instance Files for each database Logins Linked\Remote servers Endpoints MSSQLResource Purpose – The Resource database is responsible for physically storing all of the SQL Server 2005 system objects. This database has been created to improve the upgrade and rollback of SQL… Read more