SQL Server DBA scripts for day-to-day tasks
1. Check Database Size:
SELECT name AS 'Database Name', size * 8 / 1024 AS 'Size (MB)'
FROM sys.master_files
WHERE DB_NAME(database_id) NOT IN ('master', 'tempdb', 'model', 'msdb');
2. Check Fragmentation:
SELECT OBJECT_NAME(ind.OBJECT_ID) AS 'Table', ind.name AS 'Index',
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30
ORDER BY indexstats.avg_fragmentation_in_percent DESC;
3. Backup Information:
SELECT database_name, backup_start_date, backup_finish_date,
compressed_backup_size/1024/1024 AS 'Backup Size (MB)'
FROM msdb.dbo.backupset
ORDER BY backup_start_date DESC;
4. Check Active Sessions:
SELECT session_id, login_time, host_name, program_name
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;
5. Find Blocking Processes:
SELECT
spid, blocking_session_id, waitresource, waittype, waittime
FROM
sys.sysprocesses
WHERE
blocking_session_id <> 0;
6. Top CPU Consuming Queries:
SELECT TOP 10 total_worker_time/1000 AS 'Total CPU Time (s)',
execution_count, total_elapsed_time/1000 AS 'Total Elapsed Time (s)',
sql_text.text AS 'Query'
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sql_text
ORDER BY total_worker_time DESC;
7. **Check Index Usage:**
SELECT OBJECT_NAME(s.object_id) AS 'Table', i.name AS 'Index',
user_seeks, user_scans, user_lookups, user_updates
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i ON i.index_id = s.index_id
AND s.object_id = i.object_id
WHERE database_id = DB_ID();
8. **Find Unused Indexes:**
SELECT OBJECT_NAME(i.object_id) AS 'Table', i.name AS 'Index',
i.index_id, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS s ON i.index_id = s.index_id
AND i.object_id = s.object_id
AND s.database_id = DB_ID()
WHERE s.user_seeks IS NULL
AND s.user_scans IS NULL
AND s.user_lookups IS NULL
AND s.user_updates IS NULL;
9. **Check Last Backup Date for Each Database:**
SELECT database_name, MAX(backup_finish_date) AS 'Last Backup Date'
FROM msdb.dbo.backupset
GROUP BY database_name;
10. **List SQL Server Agent Jobs:**
SELECT job_id, name, enabled
FROM msdb.dbo.sysjobs;
11. **Check Disk Space:**
EXEC xp_fixeddrives;
12. **List All Tables in a Database:**
USE YourDatabaseName;
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
13. **Find Recently Executed Queries:**
SELECT TOP 10 creation_time, last_execution_time,
total_physical_reads, total_logical_reads, total_logical_writes,
execution_count, text
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY last_execution_time DESC;
14. **Check for Long-Running Queries:**
SELECT
r.session_id,
s.text AS 'Query',
r.status,
r.start_time,
r.total_elapsed_time
FROM
sys.dm_exec_requests r
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS s
WHERE
r.total_elapsed_time > 5000 -- Adjust the threshold as needed
ORDER BY
r.total_elapsed_time DESC;
15. **List All Users and Their Permissions:**
SELECT
princ.name AS 'User',
perm.permission_name,
perm.state_desc,
obj.name AS 'Object'
FROM
sys.database_principals princ
JOIN
sys.database_permissions perm ON princ.principal_id = perm.grantee_principal_id
JOIN
sys.objects obj ON perm.major_id = obj.object_id
ORDER BY
princ.name, obj.name;
16. **Identify Missing Indexes:**
SELECT
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) *
(migs.user_seeks + migs.user_scans) AS improvement_measure,
'CREATE INDEX [missing_index_' +
CONVERT(VARCHAR, mig.index_group_handle) + '_' +
CONVERT(VARCHAR, mid.index_handle) + '_' +
LEFT(PARSENAME(mid.statement, 1), 32) + ']'
AS create_index_statement,
mid.statement AS 'Table'
FROM
sys.dm_db_missing_index_groups mig
INNER JOIN
sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN
sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY
improvement_measure DESC;
17. **Check for Database Integrity:**
DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
18. **List SQL Server Logins and Their Roles:**
SELECT
sp.name AS 'Login',
sp.type_desc,
sp.is_disabled,
sp.create_date,
sp.modify_date,
sp.default_database_name,
sl.name AS 'Role'
FROM
sys.server_principals sp
LEFT JOIN
sys.server_role_members srm ON sp.principal_id = srm.member_principal_id
LEFT JOIN
sys.server_principals sl ON srm.role_principal_id = sl.principal_id
WHERE
sp.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
ORDER BY
sp.name, sl.name;
SQL Server DBA scripts for various tasks:
19. Find Largest Tables in a Database:
SELECT
t.NAME AS TableName,
i.name AS IndexName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB,
SUM(a.used_pages) * 8 / 1024 AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 AS UnusedSpaceMB
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 t.is_ms_shipped = 0
GROUP BY
t.NAME, i.object_id, i.index_id, i.name, p.rows
ORDER BY
TotalSpaceMB DESC;
20. Check SQL Server Agent Job History:
USE msdb;
SELECT
job.name AS 'Job Name',
run_status,
run_date,
run_duration
FROM
sysjobs job
INNER JOIN
sysjobhistory history ON job.job_id = history.job_id
ORDER BY
run_date DESC, run_time DESC;
21. Identify Top Wait Types:
SELECT
wait_type,
wait_time_ms,
(wait_time_ms * 1.0) / SUM(wait_time_ms) OVER() * 100.0 AS 'Percentage'
FROM
sys.dm_os_wait_stats
WHERE
wait_time_ms > 0
ORDER BY
wait_time_ms DESC;
22. Check TempDB Usage:
USE tempdb;
SELECT
name AS FileName,
size * 8 / 1024 AS FileSizeMB
FROM
sys.master_files
WHERE
database_id = DB_ID('tempdb');
23. List Active Sessions and their Queries:
SELECT
session_id,
status,
blocking_session_id,
wait_type,
wait_time,
sql_text.text AS 'Query'
FROM
sys.dm_exec_requests
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS sql_text
WHERE
session_id <> @@SPID;
SQL Server DBA scripts for different purposes:
24. Check Last Backup Status for Each Database:
SELECT
database_name,
backup_start_date,
backup_finish_date,
CASE WHEN backup_finish_date IS NULL THEN 'Not backed up recently' ELSE 'OK' END AS backup_status
FROM
msdb.dbo.backupset
ORDER BY
database_name, backup_finish_date DESC;
25. List Active Transactions:
DBCC OPENTRAN;
26. Check for Orphaned Users:
sp_change_users_login 'Report';
27. Identify CPU Usage by Database:
SELECT
DB_NAME(database_id) AS 'Database',
total_worker_time/1000 AS 'Total CPU Time (s)'
FROM
sys.dm_exec_query_stats
ORDER BY
total_worker_time DESC;
28. Check for Expensive Queries in the Plan Cache:
SELECT TOP 10
text,
query_plan,
total_elapsed_time/1000 AS 'Total Elapsed Time (s)'
FROM
sys.dm_exec_query_stats
CROSS APPLY
sys.dm_exec_sql_text(sql_handle)
CROSS APPLY
sys.dm_exec_query_plan(plan_handle)
ORDER BY
total_elapsed_time DESC;
29. Find Tables with Missing Statistics:
SELECT
t.NAME AS TableName,
i.name AS IndexName
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
WHERE
i.index_id NOT IN (SELECT index_id FROM sys.stats);
30. Check for Full-Text Catalog Population Status:
SELECT
FULLTEXTCATALOGPROPERTY('YourCatalogName', 'PopulateStatus') AS 'Population Status';
SQL Server DBA scripts for troubleshooting and diagnosing issues:
31. Check for Blocked Processes:
SELECT
spid,
blocked,
loginame AS 'Blocked By',
last_batch,
hostname,
program_name,
cmd
FROM
master.dbo.sysprocesses
WHERE
blocked <> 0;
32. Find Open Transactions and Their Details:
DBCC OPENTRAN;
33. Check SQL Server Error Log:
EXEC xp_readerrorlog;
34. Check for Longest Running Queries:
SELECT TOP 10
s.text AS 'Query',
r.start_time,
r.total_elapsed_time
FROM
sys.dm_exec_requests r
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS s
ORDER BY
r.total_elapsed_time DESC;
35. Identify Resource-Intensive Queries:
SELECT TOP 10
qt.text AS 'Query',
qs.total_worker_time,
qs.total_logical_reads,
qs.total_logical_writes
FROM
sys.dm_exec_query_stats qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY
qs.total_worker_time DESC;
36. Check for Memory Usage by Buffer Pool:
SELECT
(count(*) * 8) / 1024 AS 'Buffer Pool Size (MB)'
FROM
sys.dm_os_buffer_descriptors;
37. Verify SQL Server Configuration Options:
EXEC sp_configure;
38. Check Wait Stats for Performance Tuning:
SELECT
wait_type,
wait_time_ms,
signal_wait_time_ms
FROM
sys.dm_os_wait_stats
WHERE
wait_time_ms > 0
ORDER BY
wait_time_ms DESC;
39. Find Missing Indexes for Query Optimization:
SELECT
statement AS 'Table',
equality_columns,
inequality_columns,
included_columns,
user_seeks,
user_scans
FROM
sys.dm_db_missing_index_details
ORDER BY
avg_total_user_cost DESC;
40. Check for Active TempDB Sessions:
SELECT
session_id,
user_objects_alloc_page_count,
user_objects_dealloc_page_count,
internal_objects_alloc_page_count,
internal_objects_dealloc_page_count
FROM
sys.dm_db_session_space_usage;
SQL Server AlwaysOn Availability Group (AG) scripts for monitoring and managing:
41. Check AlwaysOn Availability Group Health:
SELECT
ag.name AS 'Availability Group',
ar.replica_server_name AS 'Replica',
adc.database_name AS 'Database',
ags.is_local, ags.is_primary_replica,
ar.role_desc, ar.operational_state_desc
FROM
sys.dm_hadr_availability_group_states ags
INNER JOIN
sys.availability_groups ag ON ags.group_id = ag.group_id
INNER JOIN
sys.dm_hadr_availability_replica_states ar ON ags.group_id = ar.group_id
AND ags.replica_id = ar.replica_id
INNER JOIN
sys.availability_databases_cluster adc ON ag.group_id = adc.group_id;
42. Check AG Listener Status:
SELECT
agl.dns_name AS 'Listener DNS Name',
agl.port AS 'Listener Port',
ags.dns_name AS 'Replica DNS Name',
ar.replica_server_name AS 'Replica Server',
ar.role_desc,
ar.operational_state_desc
FROM
sys.availability_group_listeners agl
INNER JOIN
sys.availability_group_listener_ip_addresses aglip ON agl.listener_id = aglip.listener_id
INNER JOIN
sys.availability_replicas ar ON aglip.replica_id = ar.replica_id
INNER JOIN
sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id
AND ar.group_id = ars.group_id;
43. Check Data Synchronization Status:
SELECT
db_name(database_id) AS 'Database',
synchronization_state_desc,
synchronization_health_desc
FROM
sys.dm_hadr_database_replica_states;
44. Check AG Backup Preferences:
SELECT
group_name AS 'Availability Group',
replica_server_name AS 'Replica Server',
preferred_backup_replica AS 'Preferred Backup Replica',
backup_priority AS 'Backup Priority'
FROM
sys.availability_replicas;
45. Check AG Failover Readiness:
SELECT
replica_server_name AS 'Replica Server',
replica_mode_desc AS 'Replica Mode',
connection_state_desc AS 'Connection State',
synchronization_health_desc AS 'Synchronization Health'
FROM
sys.dm_hadr_availability_replica_states;
46. Check AG Log Send and Redo Queue Size:
SELECT
replica_server_name AS 'Replica Server',
log_send_queue_size,
redo_queue_size
FROM
sys.dm_hadr_database_replica_states;
47. View AG Listener Properties:
SELECT
listener_id,
dns_name AS 'Listener DNS Name',
port AS 'Port'
FROM
sys.availability_group_listeners;
48. Check AG Endpoints Status:
SELECT
name AS 'Endpoint Name',
state_desc AS 'State',
port AS 'Port'
FROM
sys.availability_endpoints;
SQL Server AlwaysOn Availability Group scripts for various tasks:
49. Check AG Cluster Resource Status:
SELECT
ag.name AS 'Availability Group',
ar.replica_server_name AS 'Replica Server',
cr.resource_type,
cr.resource_state,
cr.online_cluster_node_id AS 'Online Node ID'
FROM
sys.dm_hadr_availability_group_states ags
INNER JOIN
sys.availability_groups ag ON ags.group_id = ag.group_id
INNER JOIN
sys.dm_hadr_availability_replica_states ar ON ags.group_id = ar.group_id
AND ags.replica_id = ar.replica_id
INNER JOIN
sys.dm_hadr_cluster_members cm ON ar.replica_server_name = cm.member_name
INNER JOIN
sys.dm_hadr_cluster cl ON cm.cluster_id = cl.cluster_id
INNER JOIN
sys.dm_hadr_cluster_networks cn ON cm.network_name = cn.network_name
INNER JOIN
sys.dm_hadr_cluster_nodes cnodes ON cn.cluster_network_id = cnodes.cluster_network_id
INNER JOIN
sys.dm_hadr_cluster_instances ci ON cn.cluster_instance_id = ci.cluster_instance_id
INNER JOIN
sys.dm_hadr_cluster_resources cr ON ci.cluster_instance_id = cr.cluster_instance_id;
50. Check AG Backup History:
SELECT
ag.name AS 'Availability Group',
ar.replica_server_name AS 'Replica Server',
bs.database_name AS 'Database',
backup_start_date,
backup_finish_date,
type,
compressed_backup_size / 1024 / 1024 AS 'Compressed Backup Size (MB)'
FROM
msdb.dbo.backupset bs
INNER JOIN
msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
INNER JOIN
sys.availability_groups ag ON bs.group_id = ag.group_id
INNER JOIN
sys.availability_replicas ar ON bs.replica_id = ar.replica_id
ORDER BY
backup_start_date DESC;
51. Check AG Replica Connection States:
SELECT
ag.name AS 'Availability Group',
ar.replica_server_name AS 'Replica Server',
ars.role_desc,
ars.connected_state_desc
FROM
sys.availability_replicas ar
INNER JOIN
sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id
INNER JOIN
sys.availability_groups ag ON ar.group_id = ag.group_id;
52. Identify AG Endpoint Permissions:
SELECT
endpoint_id,
name AS 'Endpoint Name',
principal_id,
state_desc AS 'State',
permission_granted_desc AS 'Permission Granted'
FROM
sys.database_mirroring_endpoints;
53. Check AG Failover Thresholds:
SELECT
ag.name AS 'Availability Group',
ar.replica_server_name AS 'Replica Server',
ars.failover_threshold,
ars.health_check_timeout
FROM
sys.availability_replicas ar
INNER JOIN
sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id
INNER JOIN
sys.availability_groups ag ON ar.group_id = ag.group_id;
SQL architecture involves various aspects, from performance tuning to schema design. Here are some SQL scripts that can be useful for SQL architects:
61. Identify High CPU Queries:
SELECT TOP 10
total_worker_time/1000 AS 'Total CPU Time (s)',
total_logical_reads,
total_elapsed_time/1000 AS 'Total Elapsed Time (s)',
text
FROM
sys.dm_exec_query_stats
CROSS APPLY
sys.dm_exec_sql_text(sql_handle)
ORDER BY
total_worker_time DESC;
62. Check Index Fragmentation:
SELECT
OBJECT_NAME(ind.OBJECT_ID) AS 'Table',
ind.name AS 'Index',
indexstats.avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN
sys.indexes ind ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE
indexstats.avg_fragmentation_in_percent > 30
ORDER BY
indexstats.avg_fragmentation_in_percent DESC;
63. Identify Missing Indexes:
SELECT
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) *
(migs.user_seeks + migs.user_scans) AS 'Improvement Measure',
'CREATE INDEX missing_index_' +
CONVERT(VARCHAR, mig.index_group_handle) +
'_' + CONVERT(VARCHAR, mid.index_handle) +
' ON ' + mid.statement +
' (' + ISNULL(mid.equality_columns, '') +
CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END +
ISNULL(mid.inequality_columns, '') + ')' +
ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS 'Create Index Statement'
FROM
sys.dm_db_missing_index_groups mig
INNER JOIN
sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN
sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY
migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC;
64. Identify Unused Indexes:
SELECT
object_name(i.object_id) AS 'Table',
i.name AS 'Index',
i.index_id,
dm_ius.user_seeks,
dm_ius.user_scans,
dm_ius.user_lookups,
dm_ius.user_updates
FROM
sys.indexes AS i
LEFT JOIN
sys.dm_db_index_usage_stats AS dm_ius ON i.object_id = dm_ius.object_id AND i.index_id = dm_ius.index_id AND dm_ius.database_id = DB_ID()
WHERE
dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups = 0
AND dm_ius.user_updates > 0
ORDER BY
i.name;
65. Check Schema Dependencies:
SELECT
object_name(referencing_id) AS 'Referencing Object',
referenced_schema_name,
referenced_entity_name,
referenced_id
FROM
sys.sql_expression_dependencies
WHERE
referenced_database_name IS NOT NULL;
66. Check Table Dependencies:
SELECT
object_name(referencing_id) AS 'Referencing Object',
referenced_schema_name,
referenced_entity_name,
referenced_id
FROM
sys.sql_expression_dependencies
WHERE
referenced_database_name IS NULL;
Writing out all possible SQL scripts would be an extensive task, but I can provide you with a diverse set of SQL scripts that cover various tasks. Let’s break them down into different categories:
Database Management:
1. Create a Database:
CREATE DATABASE YourDatabaseName;
2. Drop a Database:
DROP DATABASE YourDatabaseName;
3. List All Databases:
SHOW DATABASES;
Table Management:
4. Create a Table:
CREATE TABLE YourTableName (
column1 datatype,
column2 datatype,
...
);
5. Insert Data into a Table:
INSERT INTO YourTableName (column1, column2, ...) VALUES (value1, value2, ...);
6. Select All Rows from a Table:
SELECT * FROM YourTableName;
Data Retrieval:
7. Basic SELECT Query:
SELECT column1, column2 FROM YourTableName WHERE condition;
8. Aggregate Functions (e.g., SUM, AVG):
SELECT AVG(column1), MAX(column2) FROM YourTableName;
9. Filtering and Sorting:
SELECT * FROM YourTableName WHERE condition ORDER BY column1 DESC;
Data Modification:
10. Update Data in a Table:
UPDATE YourTableName SET column1 = value1 WHERE condition;
11. Delete Data from a Table:
DELETE FROM YourTableName WHERE condition;
Advanced Queries:
12. JOIN Operation:
SELECT t1.column1, t2.column2 FROM Table1 t1 INNER JOIN Table2 t2 ON t1.id = t2.id;
13. Subquery:
SELECT column1 FROM YourTableName WHERE column2 IN (SELECT column2 FROM AnotherTable WHERE condition);
14. CASE Statement:
SELECT column1,
CASE WHEN column2 > 50 THEN 'High' WHEN column2 > 20 THEN 'Medium' ELSE 'Low' END AS 'Priority'
FROM YourTableName;
Miscellaneous:
15. Create an Index:
CREATE INDEX idx_name ON YourTableName (column1);
16. Backup a Database:
BACKUP DATABASE YourDatabaseName TO DISK = 'C:\YourBackupPath\backup.bak';
17. Restore a Database:
RESTORE DATABASE YourDatabaseName FROM DISK = 'C:\YourBackupPath\backup.bak';
Advanced Data Manipulation:
18. Common Table Expressions (CTE):
WITH YourCTE AS (
SELECT column1, column2 FROM YourTableName WHERE condition
)
SELECT * FROM YourCTE;
19. Window Functions (e.g., ROW_NUMBER, RANK):
SELECT column1, column2, ROW_NUMBER() OVER (ORDER BY column1) AS RowNum FROM YourTableName;
20. Pivot Table:
SELECT *
FROM YourTableName
PIVOT (
MAX(column2) FOR column1 IN ('Value1', 'Value2', 'Value3')
) AS PivotTable;
Working with Dates:
21. Retrieve Current Date and Time:
SELECT GETDATE() AS CurrentDateTime;
22. Date Manipulation:
SELECT DATEADD(DAY, 7, GETDATE()) AS OneWeekLater;
23. Extracting Date Parts:
SELECT YEAR(column1) AS ExtractedYear, MONTH(column1) AS ExtractedMonth FROM YourTableName;
User Management:
24. Create a User:
CREATE USER 'YourUsername'@'localhost' IDENTIFIED BY 'YourPassword';
25. Grant Permissions:
GRANT SELECT, INSERT ON YourTableName TO 'YourUsername'@'localhost';
26. Revoke Permissions:
REVOKE SELECT ON YourTableName FROM 'YourUsername'@'localhost';
Stored Procedures and Functions:
27. Create a Stored Procedure:
CREATE PROCEDURE YourStoredProcedure
AS
BEGIN
-- SQL Statements
END;
28. Execute a Stored Procedure:
EXEC YourStoredProcedure;
29. Create a Function:
CREATE FUNCTION YourFunction (@parameter INT)
RETURNS INT
AS
BEGIN
-- SQL Statements
END;
30. Execute a Function:
SELECT dbo.YourFunction(5) AS Result;
Monitoring and Optimization:
31. Check Query Execution Plan:
SET SHOWPLAN_TEXT ON;
GO
-- Your Query Here
32. Identify Blocking Processes:
SELECT session_id, blocking_session_id, wait_type
FROM sys.dm_exec_requests
WHERE blocking_session_id IS NOT NULL;
33. Check Table Size:
EXEC sp_spaceused 'YourTableName';
Understanding and analyzing the execution plan is crucial for optimizing queries. Here are additional SQL scripts related to query execution plans:
Query Plan Analysis:
34. Display Estimated Execution Plan:
SET SHOWPLAN_XML ON;
-- Your Query Here
SET SHOWPLAN_XML OFF;
35. Display Actual Execution Plan:
SET STATISTICS XML ON;
-- Your Query Here
SET STATISTICS XML OFF;
36. Identify Missing Indexes:
SELECT
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) *
(migs.user_seeks + migs.user_scans) AS 'Improvement Measure',
'CREATE INDEX missing_index_' +
CONVERT(VARCHAR, mig.index_group_handle) +
'_' + CONVERT(VARCHAR, mid.index_handle) +
' ON ' + mid.statement +
' (' + ISNULL(mid.equality_columns, '') +
CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END +
ISNULL(mid.inequality_columns, '') + ')' +
ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS 'Create Index Statement'
FROM
sys.dm_db_missing_index_groups mig
INNER JOIN
sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN
sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY
migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC;
Query Statistics:
37. Enable Query Execution Statistics:
SET STATISTICS IO, TIME ON;
-- Your Query Here
SET STATISTICS IO, TIME OFF;
38. Check Logical and Physical Reads:
SELECT
total_logical_reads,
total_physical_reads
FROM
sys.dm_exec_query_stats
WHERE
sql_handle = 0xYourSQLHandle;
39. Check Execution Time and CPU Time:
SELECT
total_elapsed_time,
total_worker_time
FROM
sys.dm_exec_query_stats
WHERE
sql_handle = 0xYourSQLHandle;
Index Analysis:
40. Identify Index Usage:
SELECT
index_id,
user_seeks,
user_scans,
user_lookups,
user_updates
FROM
sys.dm_db_index_usage_stats
WHERE
object_id = OBJECT_ID('YourTableName');
41. Check Index Fragmentation:
SELECT
index_id,
avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('YourTableName'), NULL, NULL, NULL);
Here are more SQL scripts focused on query optimization, index management, and additional execution plan analysis:
Query Optimization:
42. Identify Top Resource-Consuming Queries:
SELECT TOP 10
text AS 'Query Text',
total_elapsed_time,
total_logical_reads,
total_worker_time
FROM
sys.dm_exec_query_stats
CROSS APPLY
sys.dm_exec_sql_text(sql_handle)
ORDER BY
total_elapsed_time DESC;
43. Identify Index Usage for a Specific Table:
SELECT
t.name AS 'Table Name',
i.name AS 'Index Name',
s.user_seeks,
s.user_scans,
s.user_lookups
FROM
sys.dm_db_index_usage_stats s
INNER JOIN
sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
INNER JOIN
sys.tables t ON i.object_id = t.object_id
WHERE
t.name = 'YourTableName';
Temporary Table and Table Variable Analysis:
44. Check Temporary Table Usage:
SELECT
t.name AS 'Temporary Table Name',
o.name AS 'Object Name',
o.type_desc AS 'Object Type',
o.create_date,
o.modify_date
FROM
tempdb.sys.objects o
INNER JOIN
tempdb.sys.tables t ON o.object_id = t.object_id;
45. Check Table Variable Usage:
SELECT
name AS 'Table Variable Name',
create_date,
modify_date
FROM
tempdb.sys.tables
WHERE
name LIKE '#%'
AND type = 'U';
Additional Execution Plan Analysis:
46. View Operator-Level Details in Execution Plan:
SET STATISTICS PROFILE ON;
-- Your Query Here
SET STATISTICS PROFILE OFF;
47. Identify Sort and Hash Warnings:
SELECT
text AS 'Query Text',
query_plan,
total_rows
FROM
sys.dm_exec_query_stats
CROSS APPLY
sys.dm_exec_sql_text(sql_handle)
CROSS APPLY
sys.dm_exec_query_plan(plan_handle)
WHERE
query_plan LIKE '%Sort%' OR query_plan LIKE '%Hash Match%';
Index and Statistics Maintenance:
48. Rebuild All Indexes in a Database:
DECLARE @TableName NVARCHAR(255);
DECLARE rebuildIndexes CURSOR FOR
SELECT
'[' + SCHEMA_NAME(schema_id) + '].[' + name + ']' AS TableName
FROM
sys.tables;
OPEN rebuildIndexes;
FETCH NEXT FROM rebuildIndexes INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD;');
FETCH NEXT FROM rebuildIndexes INTO @TableName;
END
CLOSE rebuildIndexes;
DEALLOCATE rebuildIndexes;
49. Update Statistics for All Tables:
DECLARE @TableName NVARCHAR(255);
DECLARE updateStatistics CURSOR FOR
SELECT
'[' + SCHEMA_NAME(schema_id) + '].[' + name + ']' AS TableName
FROM
sys.tables;
OPEN updateStatistics;
FETCH NEXT FROM updateStatistics INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('UPDATE STATISTICS ' + @TableName + ';');
FETCH NEXT FROM updateStatistics INTO @TableName;
END
CLOSE updateStatistics;
DEALLOCATE updateStatistics;
50. To find the lead blocker.
SELECT
spid
,sp.STATUS
,loginame = SUBSTRING(loginame, 1, 12)
,hostname = SUBSTRING(hostname, 1, 12)
,blk = CONVERT(CHAR(3), blocked)
,open_tran
,dbname = SUBSTRING(DB_NAME(sp.dbid),1,10)
,cmd
,waittype
,waittime
,last_batch
,SQLStatement =
SUBSTRING
(
qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2
)
FROM master.dbo.sysprocesses sp
LEFT JOIN sys.dm_exec_requests er
ON er.session_id = sp.spid
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0
51. To find the long running queries.
--Long Running quries
SELECT
r.session_id
--, r.blocking_session_id
, r.start_time
, TotalElapsedTime_ms = r.total_elapsed_time
, r.[status]
, r.command
, DatabaseName = DB_Name(r.database_id)
, r.wait_type
, r.last_wait_type
, r.wait_resource
, r.cpu_time
, r.reads
, r.writes
, r.logical_reads
, REPLACE(REPLACE(REPLACE(cast(t.text as varchar(5000)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), '') AS [executing batch]
, SUBSTRING(
t.[text], r.statement_start_offset / 2,
( CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH (t.[text])
ELSE r.statement_end_offset
END - r.statement_start_offset ) / 2
) AS [executing statement]
--, p.query_plan
FROM
sys.dm_exec_requests r
CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) AS t
CROSS APPLY
sys.dm_exec_query_plan(r.plan_handle) AS p
ORDER BY
r.total_elapsed_time DESC;
MAXDOP (Max Degree of Parallelism) is a SQL Server configuration option that controls the number of processors used for the execution of a single query. Here are some scripts related to MAXDOP:
Check Current MAXDOP Settings:
1. View Current MAXDOP Configuration:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max degree of parallelism';
Set or Change MAXDOP:
2. Set MAXDOP Globally:
EXEC sp_configure 'max degree of parallelism', 4; -- Set the desired value
RECONFIGURE;
3. Set MAXDOP for a Specific Query:
SELECT
column1,
column2
FROM
YourTableName
OPTION (MAXDOP 4); -- Set the desired value
Check Query Execution Plan for Parallelism:
4. Identify Parallel Queries in Execution Plan:
SET STATISTICS PROFILE ON;
-- Your Query Here
SET STATISTICS PROFILE OFF;
Dynamic MAXDOP Adjustments:
5. Dynamic MAXDOP Based on Server Cores:
DECLARE @MaxDOP INT;
SELECT @MaxDOP = CASE WHEN cpu_count > 4 THEN 4 ELSE cpu_count END
FROM sys.dm_os_sys_info;
EXEC sp_configure 'max degree of parallelism', @MaxDOP;
RECONFIGURE;
6. Dynamic MAXDOP Based on Query Cost:
DECLARE @MaxDOP INT;
SELECT @MaxDOP = CASE WHEN total_worker_time > 1000000 THEN 4 ELSE 1 END
FROM sys.dm_exec_query_stats
WHERE sql_handle = 0xYourSQLHandle;
EXEC sp_configure 'max degree of parallelism', @MaxDOP;
RECONFIGURE;
Monitor MAXDOP Usage:
7. Check Current MAXDOP Usage:
SELECT
query_plan,
total_worker_time,
max_degree_of_parallelism
FROM
sys.dm_exec_query_stats
CROSS APPLY
sys.dm_exec_query_plan(plan_handle)
WHERE
max_degree_of_parallelism > 1;
8. Check Resource Wait Statistics for Parallelism:
SELECT
wait_type,
waiting_tasks_count
FROM
sys.dm_os_wait_stats
WHERE
wait_type LIKE 'CXPACKET%';
Comments
Post a Comment