PostgreSQL 9.4.2, 9.3.7, 9.2.11, 9.1.16 & 9.0.20 Released!
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.
[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
[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
[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
[sourcecode language=”sql”] SELECT name indexname FROM sys.indexes WHERE object_id = object_id(‘XYZTable’) AND allow_row_locks = 0 AND allow_page_locks = 0 [/sourcecode]
[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
[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
[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
[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
[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
[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
[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
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.