SQL

Error: Cannot use file for clustered server

Recently I was trying to restore 100 databases into a newly provisioned shared clustered disks and ran into this error message using Commvault: [code language=”SQL”] ERROR CODE: Query Result [Cannot use file ‘G:\Data\database1.mdf’ for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.

Determine SQL Server install date

[sourcecode language=”sql”] SELECT createdate AS [SQL Server Install Date] FROM sys.syslogins WHERE [sid] = 0x010100000000000512000000; [/sourcecode] – This T-SQL command helps to determine the date and time that SQL Server was installed

Use T-SQL command to get Windows information

[sourcecode language=”sql”]SELECT windows_release, windows_service_pack_level, windows_sku, os_language_version FROM sys.dm_os_windows_info OPTION (RECOMPILE); [/sourcecode] – Gives you major OS version, Service Pack, Edition, and language info for the operating system

Find SQL server and OS Version information for current instance

[sourcecode language=”sql”] SELECT @@VERSION AS [SQL Server and OS Version Info]; or SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’) [/sourcecode] – This T-SQL command will help to determine the version of SQL server you are running and corresponding Windows Operating System version

Top Cached SPs By Execution Count (SQL 2008)

[sourcecode language=”sql”] SELECT TOP(250) p.name AS [SP Name], qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.total_worker_time AS [TotalWorkerTime], qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.cached_time FROM sys.procedures AS p WITH (NOLOCK) INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id] WHERE qs.database_id = DB_ID() ORDER BY qs.execution_count DESC OPTION (RECOMPILE); [/sourcecode] – This script tells you which cached stored procedures are called the most often – This helps you characterize and baseline your workload

Top Cached SPs By Avg Elapsed Time (SQL 2008)

[sourcecode language=”sql”] SELECT TOP(25) p.name AS [SP Name], qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.total_elapsed_time, qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.total_worker_time AS [TotalWorkerTime], qs.cached_time FROM sys.procedures AS p WITH (NOLOCK) INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id] WHERE qs.database_id = DB_ID() ORDER BY avg_elapsed_time DESC OPTION (RECOMPILE); [/sourcecode] – This helps you find long-running cached stored procedures that may be easy to optimize with standard query tuning techniques

Top Cached SPs By Total Worker time (SQL 2008)

[sourcecode language=”sql”] SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second], qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.cached_time FROM sys.procedures AS p WITH (NOLOCK) INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id] WHERE qs.database_id = DB_ID() ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE); [/sourcecode] — This helps you find the most expensive cached stored procedures from a CPU perspective — You should look at this if you see signs of CPU pressure

Top Cached SPs By Total Logical Reads (SQL 2008)

[sourcecode language=”sql”] SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads AS [TotalLogicalReads], qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second], qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.cached_time FROM sys.procedures AS p WITH (NOLOCK) INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id] WHERE qs.database_id = DB_ID() ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE); [/sourcecode] – This helps you find the most expensive cached stored procedures from a memory perspective – You should look at this if you see signs of memory pressure

Top Cached SPs By Total Physical Reads (SQL 2008)

[sourcecode language=”sql”] SELECT TOP(25) p.name AS [SP Name],qs.total_physical_reads AS [TotalPhysicalReads], qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads], qs.execution_count, qs.total_logical_reads,qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.cached_time FROM sys.procedures AS p WITH (NOLOCK) INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id] WHERE qs.database_id = DB_ID() AND qs.total_physical_reads > 0 ORDER BY qs.total_physical_reads DESC, qs.total_logical_reads DESC OPTION (RECOMPILE); [/sourcecode] — This helps you find the most expensive cached stored procedures from a read I/O perspective — You should look at this if you see signs of I/O pressure or of memory pressure

Top Cached SPs By Total Logical Writes (SQL 2008)

[sourcecode language=”sql”] SELECT TOP(25) p.name AS [SP Name], qs.total_logical_writes AS [TotalLogicalWrites], qs.total_logical_writes/qs.execution_count AS [AvgLogicalWrites], qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second], qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.cached_time FROM sys.procedures AS p WITH (NOLOCK) INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id] WHERE qs.database_id = DB_ID() ORDER BY qs.total_logical_writes DESC OPTION (RECOMPILE); [/sourcecode] — Logical writes relate to both memory and disk I/O pressure — This helps you find the most expensive cached stored procedures from a write I/O perspective

Lists top statements by average input/output usage for current database

[sourcecode language=”sql”] SELECT TOP(50) OBJECT_NAME(qt.objectid) AS [SP Name], (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO], SUBSTRING(qt.[text],qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2 ELSE qs.statement_end_offset END – qs.statement_start_offset)/2) AS [Query Text] FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK) CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.[dbid] = DB_ID() ORDER BY [Avg IO] DESC OPTION (RECOMPILE); [/sourcecode] — Helps you find the most expensive statements for I/O by SP

Backup all SQL instance databases

Note: — This scripts backs up all databases without having to explicitly state them — Also names them according to database name with the date appended to the end e.g ‘DB_SYSADS_20121210.BAK’ — Change the @path parameter to where you want the backups to be dumped to — The databases stated after the WHERE command will not be backed up. In the example above the system databases are omitted. You can add other databases within the parentheses if you want them omitted, e.