Every Question..What does it mean? Why is this? How it works?
Microsoft .Net (pronounced dot (.) net) may be a package element that runs on the Windows software package.
.Net provides tools and libraries that change developers to form Windows package a lot of quicker and easier.
Microsoft describes it as:".Net is that the Microsoft internet Service strategy to attach data, people,
system and devices through software".I'm Choulla Naresh..!
To rebuild all indexes on a table:
ALTER INDEX ALL ON YourTableName REBUILD;
To reorganize all indexes on a table:
ALTER INDEX ALL ON YourTableName REORGANIZE;
Rebuilding indexes reorganizes the entire index tree, while reorganizing is a lighter operation that defragments the leaf level of the index.
You can use the following query to list all indexes on a specific table:
SELECT i.name AS IndexName, i.type_desc AS IndexType, c.name AS ColumnName FROM sys.indexes i INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE i.object_id = OBJECT_ID('YourTableName');
Replace 'YourTableName' with the actual table name. This query returns all indexes and their corresponding columns for the specified table.
You can retrieve the last executed queries using the following query:
SELECT deqs.creation_time, dest.text AS QueryText, deqs.execution_count, deqs.total_worker_time, deqs.total_physical_reads, deqs.total_logical_reads FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest ORDER BY deqs.creation_time DESC;
This gives you a list of recently executed queries along with execution statistics.
To identify blocked sessions and the session causing the block:
SELECT blocking_session_id AS BlockingSession, session_id AS BlockedSession, wait_type, wait_time, wait_resource FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;
This query shows all sessions that are currently blocked and the blocking session’s ID.
You can backup a database using the following command:
BACKUP DATABASE YourDatabaseName TO DISK = 'C:\Backup\YourDatabaseName.bak';
This command creates a backup of the YourDatabaseName database to the specified location.
To enable the execution plan for a query, run:
SET SHOWPLAN_ALL ON;
Then run your query. The execution plan will be displayed as part of the result. To turn off the execution plan, use:
SET SHOWPLAN_ALL OFF;
You can use the following query to find the largest tables by data size:
SELECT t.NAME AS TableName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE t.NAME NOT LIKE 'dt%' AND i.OBJECT_ID > 255 GROUP BY t.Name, p.Rows ORDER BY TotalSpaceKB DESC;
This query returns the largest tables by row count and space usage.
You can get the list of all users in a database using this query:
SELECT name AS Username FROM sys.database_principals WHERE type IN ('S', 'U') -- 'S' for SQL user, 'U' for Windows user AND name NOT LIKE '##%'; -- Exclude system users
This lists all users in the current database.
Use the following query to find currently running queries:
SELECT r.session_id, r.start_time, s.text AS query_text, r.status, r.command, r.total_elapsed_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s;
This returns details about all currently running queries, including the session ID, query text, and execution status.
You can check the status of running transactions using this query:
SELECT session_id, transaction_id, database_id, transaction_begin_time, transaction_state, transaction_status
FROM sys.dm_tran_active_transactions;