SELECT OBJECT_NAME(ddius.[object_id], ddius.database_id) AS [object_name] , ddius.index_id , ddius.user_seeks , ddius.user_scans , ddius.user_lookups , ddius.user_seeks + ddius.user_scans + ddius.user_lookups
Continue readingAuthor:
Find indexes for database in SQL SERVER
use AdventureWorks2012 go SELECT DB_NAME(ddius.[database_id]) AS database_name , OBJECT_NAME(ddius.[object_id], DB_ID(‘AdventureWorks2012’)) –<< replace db name AS [object_name] , asi.[name] AS
Continue readingHow to find who are directly connected to the SQL Server instance ?
SELECT dec.client_net_address , des.host_name , dest.text FROM sys.dm_exec_sessions des INNER JOIN sys.dm_exec_connections dec ON des.session_id = dec.session_id CROSS APPLY
Continue readingHow to identify where the bulk of the connections originate in SQL SERVER ?
SELECT dec.client_net_address , des.program_name , des.host_name , –des.login_name COUNT(dec.session_id) AS connection_count FROM sys.dm_exec_sessions AS des INNER JOIN sys.dm_exec_connections AS
Continue readingHow to list all empty tables in your database in SQL Server
Use AdventureWorks2012 go ;WITH Empty AS ( SELECT OBJECT_NAME(OBJECT_ID) [Table], SUM(row_count) [Records] FROM sys.dm_db_partition_stats WHERE index_id = 0 OR
Continue readingALL DMV’s In SQL SERVER
SELECT * FROM SYS.dm_os_memory_allocations –<< Notice that all DMVs starts with SYS.DM SELECT * FROM SYS.dm_db_xtp_nonclustered_index_stats SELECT * FROM SYS.dm_db_mirroring_past_actions
Continue readingWhat are Dynamic Management Views (DMVs)
What are Dynamic Management Views (DMVs) Another tool at your disposal to measure performance and view details about the SQL
Continue readingWhat is Index ?
What Is an Index? · An index can be best described as a pointer to data in a table.
Continue readingHow to Move tempDB to another drive in Sql Server ?
Step 1: first we have to check the location of TempDB:- USE tempdb GO SELECT * FROM sys.database_files Step 2:
Continue readingHow to Set Maximum and minimum memory in sql server ?
Use master go sp_configure ‘show advanced options’, 1; GO RECONFIGURE; GO sp_configure ‘max server memory’, 4096; —provide memory want to
Continue reading