working on it ...

Filters

Explore Public Snippets

Sort by

Found 13 snippets

    public by cghersi  4151  7  7  0

    Check the missing indexes in SQL Server

    This query returns the list of missing indexes for the queries that are currently running on the server
    SELECT 
    	er.session_id,
    	er.blocking_session_id,
    	er.start_time,
    	er.status,
    	dbName = DB_NAME(er.database_id),
    	er.wait_type,
    	er.wait_time,
    	er.last_wait_type,
    	er.granted_query_memory,
    	er.reads,
    	er.logical_reads,
    	er.writes,
    	er.row_count,
    	er.total_elapsed_time,
    	er.cpu_time,
    	er.open_transaction_count,
    	s.text,
    	qp.query_plan,
    	logDate = CONVERT(DATE,GETDATE()),
    	logTime = CONVERT(TIME,GETDATE())
    FROM sys.dm_exec_requests er 
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) s
    CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp
    WHERE 
    	CONVERT(VARCHAR(MAX), qp.query_plan) LIKE '%<missing%'            

    public by cghersi  2741  9  6  3

    Retrieve the SQL commands that are currently executing in MS SQL Server

    Thanks to avinash.kote http://www.sqlservercentral.com/scripts/118732/
    SELECT  des.session_id, des.status, des.login_name, des.[HOST_NAME],
            der.blocking_session_id, DB_NAME(der.database_id) as database_name,
            der.command, des.cpu_time, des.reads, des.writes, dec.last_write,
            des.[program_name], emg.requested_memory_kb, emg.granted_memory_kb,
            emg.used_memory_kb, der.wait_type, der.wait_time, der.last_wait_type,
            der.wait_resource,
            CASE des.transaction_isolation_level 
              WHEN 0 THEN 'Unspecified' 
              WHEN 1 THEN 'ReadUncommitted' 
              WHEN 2 THEN 'ReadCommitted'
              WHEN 3 THEN 'Repeatable' 
              WHEN 4 THEN 'Serializable' 
              WHEN 5 THEN 'Snapshot' END AS transaction_isolation_level,
            OBJECT_NAME(dest.objectid, der.database_id) as OBJECT_NAME, 
            dest.text as full_query_text,
            SUBSTRING(dest.text, der.statement_start_offset /2,
              (CASE WHEN der.statement_end_offset = -1 THEN DATALENGTH(dest.text) 
                    ELSE der.statement_end_offset END - 
              der.statement_start_offset) / 2
            ) AS [executing_statement], deqp.query_plan
    FROM  sys.dm_exec_sessions des
    LEFT JOIN sys.dm_exec_requests der on des.session_id = der.session_id
    LEFT JOIN sys.dm_exec_connections dec on des.session_id = dec.session_id
    LEFT JOIN sys.dm_exec_query_memory_grants emg on des.session_id = emg.session_id      
    CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) dest
    CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) deqp
    WHERE des.session_id <> @@SPID
    ORDER BY  des.session_id                      

    public by cghersi  3208  5  6  2

    Detect the worst SQL queries in avg CPU time in SQL Server

    Detect worst performing sql queries which is slowing down Microsoft SQL Server, this script return top queries taxing sql server CPUs. Thanks to Patrick Akhamie: http://www.sqlservercentral.com/scripts/TOP+CPU+Queries/110012/
    use tempdb
    go
    
    IF object_id('tempdb..##FindTopCPUQueries_set1') is not null DROP TABLE [dbo].[##FindTopCPUQueries_set1]
    GO
    
    declare @ServerTime datetime = getdate()
    
    , @ConvertMiliSeconds bigint = 1000
    
    , @FilterMoreThanMiliSeconds bigint = 1
    
    , @FilterHours bigint = 2
    
    , @execution_count bigint = 2
    
    , @debugFlg bit = 0
    
    
    
    if @debugFlg=1 select @ServerTime as ServerTime, @ConvertMiliSeconds as ConvertMiliSeconds
    
    , @FilterMoreThanMiliSeconds as FilterMoreThanMiliSeconds, @FilterHours as FilterHours 
    
    , @execution_count as execution_count
    
    
    
    
    
    select TOP 300
    
    @@servername as servername,@ServerTime as runtime
    
    ,isnull(db_name(QueryText.dbid),'PreparedSQL') as DBName 
    
     ,SUBSTRING(QueryText.text, (QueryStats.statement_start_offset/2)+1, 
    
    (isnull((
    
    CASE QueryStats.statement_end_offset
    
     WHEN -1 THEN DATALENGTH(QueryText.text)
    
     WHEN 0 THEN DATALENGTH(QueryText.text)
    
     ELSE QueryStats.statement_end_offset
    
     END - QueryStats.statement_start_offset),0)/2) 
    
     + 1) AS QueryExecuted
    
    ,total_worker_time AS total_worker_time
    
    ,QueryStats.execution_count as execution_count
    
    ,statement_start_offset,statement_end_offset
    
    ,(case when QueryText.dbid is null then OBJECT_NAME(QueryText.objectid) else OBJECT_NAME(QueryText.objectid, QueryText.dbid) end) as ObjectName
    
    ,query_hash
    
    ,plan_handle
    
    ,sql_handle
    
    into ##FindTopCPUQueries_set1
    
    from sys.dm_exec_query_stats as QueryStats
    
    cross apply sys.dm_exec_sql_text(QueryStats.sql_handle) as QueryText
    
    where QueryStats.query_hash IN 
    
    (
    
    select QueryStatsBaseTable.query_hash 
    
    from sys.dm_exec_query_stats QueryStatsBaseTable
    
    where last_execution_time > DATEADD(hh,-@FilterHours,GETDATE())
    
    group by query_hash
    
    having (sum(total_worker_time)/sum(execution_count))>@ConvertMiliSeconds and sum(execution_count)>@execution_count
    
    )
    
    ORDER BY total_worker_time/execution_count DESC;
    
    
    
    if @debugFlg=1 select * from ##FindTopCPUQueries_set1 order by QueryExecuted
    
    
    
    IF object_id('tempdb..##FindTopCPUQueries_set2') is not null DROP TABLE [dbo].[##FindTopCPUQueries_set2]
    
    
    
    select 
    
    servername,runtime,max(DBName) as DBName,max(QueryExecuted) as QueryExecuted,(sum(total_worker_time)/sum(execution_count))/@ConvertMiliSeconds as AvgCPUTime
    
    ,sum(execution_count) as execution_count,query_hash, max(ObjectName) as ObjectName
    
    into ##FindTopCPUQueries_set2
    
    from ##FindTopCPUQueries_set1
    
    group by query_hash,servername,runtime
    
    order by AvgCPUTime desc
    
    
    
    
    
    select * from ##FindTopCPUQueries_set2
    
    --where QueryExecuted like 'select TOP 300%'
    
    order by AvgCPUTime desc

    public by cghersi  327994  0  6  1

    Retrieve useful information regarding indexes in MS SQL Server

    This script helps the DBA to retrieve valuable information regarding indexes. Here the complete explanation of the output fields: object_type: type of object (will either be "U" for User-Defined Table or "V" for View) database_name: name of the database schema_name: name of the schema object_name: name of the object create_date: date the obje
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SET NOCOUNT ON
    SET ANSI_WARNINGS OFF
    SET ARITHABORT OFF
    SET ARITHIGNORE ON
    SET TEXTSIZE 2147483647
    
    
    -----------------------------------------------------------------------------------------------------------------------------
    --	Declarations / Sets: Declare And Set Variables
    -----------------------------------------------------------------------------------------------------------------------------
    
    DECLARE @Database_ID AS SMALLINT
    
    
    SET @Database_ID = DB_ID ()
    
    
    -----------------------------------------------------------------------------------------------------------------------------
    --	Error Trapping: Check If Temp Table(s) Already Exist(s) And Drop If Applicable
    -----------------------------------------------------------------------------------------------------------------------------
    
    IF OBJECT_ID (N'tempdb.dbo.#temp_index_breakdown_keys_filters', N'U') IS NOT NULL
    BEGIN
    
    	DROP TABLE dbo.#temp_index_breakdown_keys_filters
    
    END
    
    
    IF OBJECT_ID (N'tempdb.dbo.#temp_index_breakdown_size_info', N'U') IS NOT NULL
    BEGIN
    
    	DROP TABLE dbo.#temp_index_breakdown_size_info
    
    END
    
    
    -----------------------------------------------------------------------------------------------------------------------------
    --	Table Insert: Insert Index Key, Include Key, And Filter Definition Values Into Temp Table
    -----------------------------------------------------------------------------------------------------------------------------
    
    SELECT
    	 sqI.[object_id]
    	,sqI.index_id
    	,STUFF (CONVERT (NVARCHAR (MAX), sqI.index_key), 1, 2, N'') AS index_key
    	,STUFF (CONVERT (NVARCHAR (MAX), sqI.include_key), 1, 2, N'') AS include_key
    	,sqI.filter_definition
    	,RANK () OVER
    				(
    					ORDER BY
    						 sqI.[object_id]
    						,CONVERT (NVARCHAR (MAX), sqI.index_key)
    						,sqI.filter_definition
    				) AS dupe_rank
    INTO
    	dbo.#temp_index_breakdown_keys_filters
    FROM
    
    	(
    		SELECT
    			 I.[object_id]
    			,I.index_id
    			,(
    				SELECT
    					  N', '
    					+ C.name
    					+ N' • '
    					+ TYPE_NAME (C.user_type_id)
    					+ ISNULL ((N': [ ' + (CASE
    											WHEN C.system_type_id <> C.user_type_id THEN LOWER (TYPE_NAME (C.system_type_id))
    											END) + N' ]'), N'')
    					+ N' '
    					+ (CASE
    							WHEN TY.name NOT IN (N'bigint', N'bit', N'date', N'datetime', N'datetime2', N'datetimeoffset', N'decimal', N'float', N'int', N'money', N'numeric', N'real', N'smalldatetime', N'smallint', N'smallmoney', N'time', N'tinyint') THEN CONVERT (NVARCHAR (30), C.max_length)
    							ELSE CONVERT (NVARCHAR (30), C.max_length) + N' (' + CONVERT (NVARCHAR (30), COLUMNPROPERTY (C.[object_id], C.name, 'Precision')) + N',' + ISNULL (CONVERT (NVARCHAR (30), COLUMNPROPERTY (C.[object_id], C.name, 'Scale')), 0) + N')'
    							END)
    					+ N' '
    					+ (CASE
    							WHEN IC.is_descending_key = 0 THEN N'[A]'
    							WHEN IC.is_descending_key = 1 THEN N'[D]'
    							ELSE N'[N/A]'
    							END) AS [text()]
    				FROM
    					sys.index_columns IC
    					INNER JOIN sys.columns C ON C.[object_id] = IC.[object_id]
    						AND C.column_id = IC.column_id
    					INNER JOIN sys.types TY ON TY.user_type_id = C.user_type_id
    				WHERE
    					IC.is_included_column = 0
    					AND IC.[object_id] = I.[object_id]
    					AND IC.index_id = I.index_id
    				ORDER BY
    					IC.key_ordinal
    				FOR
    					 XML PATH ('')
    					,TYPE
    			 ) AS index_key
    			,(
    				SELECT
    					  N', '
    					+ C.name
    					+ N' • '
    					+ TYPE_NAME (C.user_type_id)
    					+ ISNULL ((N': [ ' + (CASE
    											WHEN C.system_type_id <> C.user_type_id THEN LOWER (TYPE_NAME (C.system_type_id))
    											END) + N' ]'), N'')
    					+ N' '
    					+ (CASE
    							WHEN TY.name NOT IN (N'bigint', N'bit', N'date', N'datetime', N'datetime2', N'datetimeoffset', N'decimal', N'float', N'int', N'money', N'numeric', N'real', N'smalldatetime', N'smallint', N'smallmoney', N'time', N'tinyint') THEN CONVERT (NVARCHAR (30), C.max_length)
    							ELSE CONVERT (NVARCHAR (30), C.max_length) + N' (' + CONVERT (NVARCHAR (30), COLUMNPROPERTY (C.[object_id], C.name, 'Precision')) + N',' + ISNULL (CONVERT (NVARCHAR (30), COLUMNPROPERTY (C.[object_id], C.name, 'Scale')), 0) + N')'
    							END) AS [text()]
    				FROM
    					sys.index_columns IC
    					INNER JOIN sys.columns C ON C.[object_id] = IC.[object_id]
    						AND C.column_id = IC.column_id
    					INNER JOIN sys.types TY ON TY.user_type_id = C.user_type_id
    				WHERE
    					IC.is_included_column = 1
    					AND IC.[object_id] = I.[object_id]
    					AND IC.index_id = I.index_id
    				ORDER BY
    					IC.key_ordinal
    				FOR
    					 XML PATH ('')
    					,TYPE
    			 ) AS include_key
    			,I.filter_definition
    		FROM
    			sys.indexes I
    	) sqI
    
    
    -----------------------------------------------------------------------------------------------------------------------------
    --	Table Insert: Insert Size Values Into Temp Table
    -----------------------------------------------------------------------------------------------------------------------------
    
    SELECT
    	 DDPS.[object_id]
    	,DDPS.index_id
    	,SUM (CASE
    			WHEN DDPS.index_id < 2 THEN DDPS.row_count
    			END) AS [rows]
    	,SUM (DDPS.reserved_page_count) AS total_pages
    	,SUM (DDPS.used_page_count) AS used_pages
    	,SUM (CASE
    			WHEN DDPS.index_id < 2 THEN DDPS.in_row_data_page_count + DDPS.lob_used_page_count + DDPS.row_overflow_used_page_count
    			ELSE DDPS.lob_used_page_count + DDPS.row_overflow_used_page_count
    			END) AS data_pages
    INTO
    	dbo.#temp_index_breakdown_size_info
    FROM
    	sys.dm_db_partition_stats DDPS
    GROUP BY
    	 DDPS.[object_id]
    	,DDPS.index_id
    
    
    -----------------------------------------------------------------------------------------------------------------------------
    --	Main Query: Final Display / Output
    -----------------------------------------------------------------------------------------------------------------------------
    
    SELECT
    	 (CASE
    		WHEN sqBAQ.row_filter = 1 THEN sqBAQ.[type]
    		ELSE ''
    		END) AS object_type
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN DB_NAME ()
    		ELSE ''
    		END) AS [database_name]
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN SCHEMA_NAME (sqBAQ.[schema_id])
    		ELSE ''
    		END) AS [schema_name]
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN OBJECT_NAME (sqBAQ.[object_id])
    		ELSE ''
    		END) AS [object_name]
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN sqBAQ.create_date
    		ELSE ''
    		END) AS create_date
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN caMDKL.modify_date
    		ELSE ''
    		END) AS modify_date
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (25), CONVERT (MONEY, sqBAQ.[rows]), 1)), 4, 22))
    		ELSE ''
    		END) AS [rows]
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (25), CONVERT (MONEY, (sqBAQ.total_pages * 8) / 1024.0), 1)
    		ELSE ''
    		END) AS total_mb
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (25), CONVERT (MONEY, (sqBAQ.used_pages * 8) / 1024.0), 1)
    		ELSE ''
    		END) AS used_mb
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (25), CONVERT (MONEY, (sqBAQ.unused_pages * 8) / 1024.0), 1)
    		ELSE ''
    		END) AS unused_mb
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (25), CONVERT (MONEY, (sqBAQ.data_pages * 8) / 1024.0), 1)
    		ELSE ''
    		END) AS data_mb
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (25), CONVERT (MONEY, (sqBAQ.index_pages * 8) / 1024.0), 1)
    		ELSE ''
    		END) AS index_mb
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (6), CONVERT (DECIMAL (5, 2), ISNULL (((sqBAQ.data_pages + .0) / sqBAQ.used_pages) * 100, 0)))
    		ELSE ''
    		END) AS pct_data
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (6), CONVERT (DECIMAL (5, 2), ISNULL (((sqBAQ.index_pages + .0) / sqBAQ.used_pages) * 100, 0)))
    		ELSE ''
    		END) AS pct_index
    	,sqBAQ.type_desc AS index_type
    	,ISNULL (sqBAQ.index_name, '') AS index_name
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		WHEN sqBAQ.is_primary_key = 0 AND sqBAQ.is_unique = 0 THEN REPLICATE ('.', 6)
    		WHEN sqBAQ.is_system_named = 0 THEN 'No'
    		WHEN sqBAQ.is_system_named = 1 THEN 'Yes'
    		ELSE ''
    		END) AS system_named
    	,(CASE
    		WHEN sqBAQ.is_primary_key = 1 THEN 'Yes'
    		ELSE ''
    		END) AS is_pk
    	,(CASE
    		WHEN sqBAQ.is_unique_constraint = 1 THEN 'C'
    		WHEN sqBAQ.is_unique = 1 THEN 'I'
    		ELSE ''
    		END) AS [unique]
    	,(CASE
    		WHEN sqBAQ.is_disabled = 1 THEN 'Yes'
    		ELSE ''
    		END) AS [disabled]
    	,(CASE
    		WHEN sqBAQ.is_hypothetical = 1 THEN 'Yes'
    		ELSE ''
    		END) AS hypothetical
    	,REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, sqCC.total_columns), 1)), 4, 23)) AS total_columns
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, caMDKL.[index_columns]), 1)), 4, 23))
    		END) AS [index_columns]
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, caMDKL.include_columns), 1)), 4, 23))
    		END) AS include_columns
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, (caMDKL.[index_columns] / sqCC.total_columns) * 100), 1)), 1, 23))
    		END) AS index_pct_of_columns
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, (caMDKL.include_columns / sqCC.total_columns) * 100), 1)), 1, 23))
    		END) AS include_pct_of_columns
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, ((caMDKL.[index_columns] + caMDKL.include_columns) / sqCC.total_columns) * 100), 1)), 1, 23))
    		END) AS total_pct_of_columns
    	,CONVERT (VARCHAR (25), CONVERT (MONEY, (ISNULL (sqBAQ.individual_index_pages, 0) * 8) / 1024.0), 1) AS key_mb
    	,CONVERT (VARCHAR (6), CONVERT (DECIMAL (5, 2), ISNULL (((sqBAQ.individual_index_pages + .0) / sqBAQ.index_pages) * 100, 0))) AS key_mb_pct
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (25), CONVERT (MONEY, sqKL.total_max_length), 1)), 4, 22))
    		END) AS max_key_size_bytes
    	,(CASE
    		WHEN sqKL.total_max_length > 900 THEN 'Yes'
    		ELSE ''
    		END) AS over_key_size_limit
    	,ISNULL (ttIBKF.index_key, N'') AS index_key
    	,ISNULL (ttIBKF.include_key, N'') AS include_key
    	,ISNULL (ttIBKF.filter_definition, N'') AS filter_definition
    	,(CASE
    		WHEN sqED02.dupe_id IS NOT NULL THEN CONVERT (VARCHAR (20), sqED02.dupe_id) + ' - [' + CONVERT (VARCHAR (11), sqED02.total_dupes) + ']'
    		ELSE ''
    		END) AS dupe_id
    	,sqBAQ.is_unused AS unused
    	,ISNULL (CONVERT (VARCHAR (10), STATS_DATE (sqBAQ.[object_id], sqBAQ.index_id), 23), '') AS statistics_date
    	,(CASE
    		WHEN sqBAQ.[allow_row_locks] = 0 THEN 'No'
    		WHEN sqBAQ.[allow_row_locks] = 1 THEN 'Yes'
    		ELSE ''
    		END) AS row_locks
    	,(CASE
    		WHEN sqBAQ.[allow_page_locks] = 0 THEN 'No'
    		WHEN sqBAQ.[allow_page_locks] = 1 THEN 'Yes'
    		ELSE ''
    		END) AS page_locks
    	,(CASE
    		WHEN sqBAQ.[ignore_dup_key] = 0 THEN 'No'
    		WHEN sqBAQ.[ignore_dup_key] = 1 THEN 'Yes'
    		ELSE ''
    		END) AS ignore_dupes
    	,(CASE
    		WHEN sqBAQ.no_recompute = 0 THEN 'Yes'
    		WHEN sqBAQ.no_recompute = 1 THEN 'No'
    		ELSE ''
    		END) AS auto_stats
    	,(CASE
    		WHEN sqBAQ.is_padded = 0 THEN 'No'
    		WHEN sqBAQ.is_padded = 1 THEN 'Yes'
    		ELSE ''
    		END) AS padded
    	,(CASE
    		WHEN sqBAQ.fill_factor = 0 THEN 100
    		ELSE sqBAQ.fill_factor
    		END) AS fill_factor
    	,(CASE
    		WHEN sqBAQ.user_seeks > 0 THEN CONVERT (VARCHAR (20), sqBAQ.user_seeks)
    		ELSE ''
    		END) AS user_seeks
    	,(CASE
    		WHEN sqBAQ.user_scans > 0 THEN CONVERT (VARCHAR (20), sqBAQ.user_scans)
    		ELSE ''
    		END) AS user_scans
    	,(CASE
    		WHEN sqBAQ.user_lookups > 0 THEN CONVERT (VARCHAR (20), sqBAQ.user_lookups)
    		ELSE ''
    		END) AS user_lookups
    	,(CASE
    		WHEN sqBAQ.user_updates > 0 THEN CONVERT (VARCHAR (20), sqBAQ.user_updates)
    		ELSE ''
    		END) AS user_updates
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_user_seek, 23), '') AS last_user_seek
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_user_scan, 23), '') AS last_user_scan
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_user_lookup, 23), '') AS last_user_lookup
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_user_update, 23), '') AS last_user_update
    	,(CASE
    		WHEN sqBAQ.system_seeks > 0 THEN CONVERT (VARCHAR (20), sqBAQ.system_seeks)
    		ELSE ''
    		END) AS system_seeks
    	,(CASE
    		WHEN sqBAQ.system_scans > 0 THEN CONVERT (VARCHAR (20), sqBAQ.system_scans)
    		ELSE ''
    		END) AS system_scans
    	,(CASE
    		WHEN sqBAQ.system_lookups > 0 THEN CONVERT (VARCHAR (20), sqBAQ.system_lookups)
    		ELSE ''
    		END) AS system_lookups
    	,(CASE
    		WHEN sqBAQ.system_updates > 0 THEN CONVERT (VARCHAR (20), sqBAQ.system_updates)
    		ELSE ''
    		END) AS system_updates
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_system_seek, 23), '') AS last_system_seek
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_system_scan, 23), '') AS last_system_scan
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_system_lookup, 23), '') AS last_system_lookup
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_system_update, 23), '') AS last_system_update
    FROM
    
    	(
    		SELECT
    			 O.[type]
    			,O.[schema_id]
    			,O.[object_id]
    			,CONVERT (VARCHAR (10), O.create_date, 23) AS create_date
    			,CONVERT (VARCHAR (10), O.modify_date, 23) AS modify_date
    			,sqDDPS.[rows]
    			,sqDDPS.total_pages
    			,sqDDPS.used_pages
    			,(CASE
    				WHEN sqDDPS.total_pages > sqDDPS.used_pages THEN sqDDPS.total_pages - sqDDPS.used_pages
    				ELSE 0
    				END) AS unused_pages
    			,sqDDPS.data_pages
    			,(CASE
    				WHEN sqDDPS.used_pages > sqDDPS.data_pages THEN sqDDPS.used_pages - sqDDPS.data_pages
    				ELSE 0
    				END) AS index_pages
    			,sqI.type_desc
    			,sqI.name AS index_name
    			,sqI.is_system_named
    			,sqI.is_primary_key
    			,sqI.is_unique
    			,sqI.is_disabled
    			,sqI.is_hypothetical
    			,sqI.individual_index_pages
    			,sqI.is_unused
    			,sqI.[allow_row_locks]
    			,sqI.[allow_page_locks]
    			,sqI.[ignore_dup_key]
    			,sqI.no_recompute
    			,sqI.is_padded
    			,sqI.fill_factor
    			,sqI.user_seeks
    			,sqI.user_scans
    			,sqI.user_lookups
    			,sqI.user_updates
    			,sqI.last_user_seek
    			,sqI.last_user_scan
    			,sqI.last_user_lookup
    			,sqI.last_user_update
    			,sqI.system_seeks
    			,sqI.system_scans
    			,sqI.system_lookups
    			,sqI.system_updates
    			,sqI.last_system_seek
    			,sqI.last_system_scan
    			,sqI.last_system_lookup
    			,sqI.last_system_update
    			,sqI.is_unique_constraint
    			,sqI.index_id
    			,sqI.row_filter
    		FROM
    			sys.objects O
    			INNER JOIN
    
    				(
    					SELECT
    						 ttIBSI.[object_id]
    						,SUM (ttIBSI.[rows]) AS [rows]
    						,SUM (ttIBSI.total_pages) AS total_pages
    						,SUM (ttIBSI.used_pages) AS used_pages
    						,SUM (ttIBSI.data_pages) AS data_pages
    					FROM
    						dbo.#temp_index_breakdown_size_info ttIBSI
    					GROUP BY
    						ttIBSI.[object_id]
    				) sqDDPS ON sqDDPS.[object_id] = O.[object_id]
    
    			INNER JOIN
    
    				(
    					SELECT
    						 I.[object_id]
    						,I.type_desc
    						,I.name
    						,KC.is_system_named
    						,I.is_primary_key
    						,I.is_unique
    						,I.is_disabled
    						,I.is_hypothetical
    						,(CASE
    							WHEN ttIBSI.used_pages > ttIBSI.data_pages THEN ttIBSI.used_pages - ttIBSI.data_pages
    							END) AS individual_index_pages
    						,(CASE
    							WHEN I.[type] = 0 THEN ''
    							WHEN I.[type] = 1 THEN REPLICATE ('.', 6)
    							WHEN I.is_primary_key = 1 THEN REPLICATE ('.', 6)
    							WHEN I.is_unique = 1 THEN REPLICATE ('.', 6)
    							WHEN EXISTS
    
    								(
    									SELECT
    										*
    									FROM
    										sys.index_columns IC
    										INNER JOIN sys.foreign_key_columns FKC ON FKC.parent_object_id = IC.[object_id]
    											AND FKC.parent_column_id = IC.column_id
    									WHERE
    										IC.[object_id] = I.[object_id]
    										AND IC.index_id = I.index_id
    								) THEN REPLICATE ('.', 6)
    
    							WHEN DDIUS.[object_id] IS NOT NULL THEN (CASE
    																		WHEN DDIUS.user_seeks + DDIUS.user_scans + DDIUS.user_lookups + DDIUS.user_updates = 0 THEN 'Y/N'
    																		ELSE 'No'
    																		END)
    							ELSE 'Yes'
    							END) AS is_unused
    						,I.[allow_row_locks]
    						,I.[allow_page_locks]
    						,I.[ignore_dup_key]
    						,S.no_recompute
    						,I.is_padded
    						,I.fill_factor
    						,DDIUS.user_seeks
    						,DDIUS.user_scans
    						,DDIUS.user_lookups
    						,DDIUS.user_updates
    						,DDIUS.last_user_seek
    						,DDIUS.last_user_scan
    						,DDIUS.last_user_lookup
    						,DDIUS.last_user_update
    						,DDIUS.system_seeks
    						,DDIUS.system_scans
    						,DDIUS.system_lookups
    						,DDIUS.system_updates
    						,DDIUS.last_system_seek
    						,DDIUS.last_system_scan
    						,DDIUS.last_system_lookup
    						,DDIUS.last_system_update
    						,I.is_unique_constraint
    						,I.index_id
    						,ROW_NUMBER () OVER
    											(
    												PARTITION BY
    													I.[object_id]
    												ORDER BY
    													 I.is_primary_key DESC
    													,(CASE
    														WHEN I.[type] = 0 THEN 'Z'
    														ELSE 'A'
    														END)
    													,I.[type]
    													,I.name
    											) AS row_filter
    					FROM
    						sys.indexes I
    						INNER JOIN dbo.#temp_index_breakdown_size_info ttIBSI ON ttIBSI.[object_id] = I.[object_id]
    							AND ttIBSI.index_id = I.index_id
    						LEFT JOIN sys.key_constraints KC ON KC.parent_object_id = I.[object_id]
    							AND KC.unique_index_id = I.index_id
    						LEFT JOIN sys.stats S ON S.[object_id] = I.[object_id]
    							AND S.stats_id = I.index_id
    						LEFT JOIN master.sys.dm_db_index_usage_stats DDIUS ON DDIUS.[object_id] = I.[object_id]
    							AND DDIUS.index_id = I.index_id
    							AND DDIUS.database_id = @Database_ID
    				) sqI ON sqI.[object_id] = O.[object_id]
    
    		WHERE
    			O.[type] IN ('U', 'V')
    			AND O.is_ms_shipped = 0
    			AND NOT
    
    				(
    					SCHEMA_NAME (O.[schema_id]) = N'dbo'
    					AND O.name = N'sysdiagrams'
    					AND O.[type] = 'U'
    				)
    
    	) sqBAQ
    
    	INNER JOIN
    
    		(
    			SELECT
    				 C.[object_id]
    				,COUNT (*) + .0 AS total_columns
    			FROM
    				sys.columns C
    			GROUP BY
    				C.[object_id]
    		) sqCC ON sqCC.[object_id] = sqBAQ.[object_id]
    
    	LEFT JOIN dbo.#temp_index_breakdown_keys_filters ttIBKF ON ttIBKF.[object_id] = sqBAQ.[object_id]
    		AND ttIBKF.index_id = sqBAQ.index_id
    	LEFT JOIN
    
    		(
    			SELECT
    				 IC.[object_id]
    				,IC.index_id
    				,SUM (C.max_length) AS total_max_length
    			FROM
    				sys.index_columns IC
    				INNER JOIN sys.columns C ON C.[object_id] = IC.[object_id]
    					AND C.column_id = IC.column_id
    			WHERE
    				IC.is_included_column = 0
    			GROUP BY
    				 IC.[object_id]
    				,IC.index_id
    		) sqKL ON sqKL.[object_id] = sqBAQ.[object_id] AND sqKL.index_id = sqBAQ.index_id
    
    	LEFT JOIN
    
    		(
    			SELECT
    				 sqED01.dupe_rank
    				,sqED01.total_dupes
    				,ROW_NUMBER () OVER
    									(
    										ORDER BY
    											(SELECT NULL)
    									) AS dupe_id
    			FROM
    
    				(
    					SELECT
    						 ttIBKF.dupe_rank
    						,COUNT (*) AS total_dupes
    					FROM
    						dbo.#temp_index_breakdown_keys_filters ttIBKF
    					GROUP BY
    						ttIBKF.dupe_rank
    					HAVING
    						COUNT (*) > 1
    				) sqED01
    
    		) sqED02 ON sqED02.dupe_rank = ttIBKF.dupe_rank
    
    	CROSS APPLY
    
    		(
    			SELECT
    				 (CASE
    					WHEN sqBAQ.modify_date = sqBAQ.create_date THEN REPLICATE ('.', 18)
    					ELSE sqBAQ.modify_date
    					END) AS modify_date
    				,LEN (ttIBKF.index_key) - LEN (REPLACE (ttIBKF.index_key, '•', '')) AS [index_columns]
    				,ISNULL (LEN (ttIBKF.include_key) - LEN (REPLACE (ttIBKF.include_key, '•', '')), 0) AS include_columns
    		) caMDKL
    
    ORDER BY
    	 sqBAQ.[type]
    	,SCHEMA_NAME (sqBAQ.[schema_id])
    	,OBJECT_NAME (sqBAQ.[object_id])
    	,sqBAQ.row_filter
    
    
    -----------------------------------------------------------------------------------------------------------------------------
    --	Cleanup: Drop Any Remaining Temp Tables
    -----------------------------------------------------------------------------------------------------------------------------
    
    IF OBJECT_ID (N'tempdb.dbo.#temp_index_breakdown_keys_filters', N'U') IS NOT NULL
    BEGIN
    
    	DROP TABLE dbo.#temp_index_breakdown_keys_filters
    
    END
    
    
    IF OBJECT_ID (N'tempdb.dbo.#temp_index_breakdown_size_info', N'U') IS NOT NULL
    BEGIN
    
    	DROP TABLE dbo.#temp_index_breakdown_size_info
    
    END

    public by cghersi  3358  3  6  4

    Change the location of MS SQL Server database files

    When you start playing with serious DB, one of the most important parts is to place (if possible) the files in different drives (physical, not only logical partitions!!!). But what if you already have your DB in production with tons of data on the same location? This script helps in solving the issue. It should be executed in three steps: 1) p
    -- 1) Execute this first chunk to put the DB offline
    ALTER DATABASE MyDBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE MyDBName SET OFFLINE;
    ALTER DATABASE MyDBName MODIFY FILE 
    (
       Name = MyDBName_Data,
       Filename = 'D:\DB\MyDBName.mdf'
    );
    
    ALTER DATABASE MyDBName MODIFY FILE 
    (
       Name = MyDBName_Log, 
       Filename = 'D:\DBLog\MyDBName_log.ldf'
    );
    
    ALTER DATABASE MyDBName MODIFY FILE 
    (
       Name = MyDBName_Idx, 
       Filename = 'E:\DBIndex\MyDBName_log.ndf'
    );
    
    -- 2) Manually move the files in the right location
    
    -- 3) Execute this second chunk to put the DB online
    ALTER DATABASE my SET ONLINE;
    
    ALTER DATABASE my SET MULTI_USER;

    public by cghersi  3778  1  9  0

    Safe conversion of string to float in SQL Server 2012

    This function allows a safe conversion of strings to float values.
    CREATE FUNCTION [dbo].[isFloatTryConvertible] (@str nvarchar(70))
    RETURNS bit WITH SCHEMABINDING
    AS
    BEGIN
    	DECLARE @res bit = 0;
    
    	IF (TRY_CONVERT(float, @str) IS NOT NULL)
    		SET @res = 1;
    	
    	RETURN @res;
    END

    public by cghersi  3434  0  6  0

    Detect and received deadlock info on SQL Server

    The first requirement is to turn on the appropriate Trace Flags: DBCC TRACEON (3605,1204,1222,-1) The second requirement is to ensure you have DB Mail setup and working (optional). Many thanks to Geoff Albin: http://www.sqlservercentral.com/articles/deadlock/71374/
    --== FIRST STEP ==--
    --== We will create a temporary table to hold the error log detail. ==--
    --== Before we create the temporary table, we make sure it does not already exist. ==--
     IF OBJECT_ID('tempdb.dbo.ErrorLog') IS Not Null
     BEGIN
     DROP TABLE tempdb.dbo.ErrorLog
     END
     --== We have checked for the existence of the temporary table and dropped it if it was there. ==--
     --== Now, we can create the table called tempdb.dbo.ErrorLog ==--
    CREATE TABLE tempdb.dbo.ErrorLog (Id int IDENTITY (1, 1) NOT NULL, 
    logdate DATETIME, procInfo VARCHAR(10), ERRORLOG VARCHAR(MAX))
    --== We create a 3 column table to hold the contents of the SQL Server Error log. ==--
    --== Then we insert the actual data from the Error log into our newly created table. ==--
     INSERT INTO tempdb.dbo.ErrorLog
     EXEC master.dbo.sp_readerrorlog
    --== With our table created and populated, we can now use the info inside of it. ==--
     BEGIN
    --== Set a variable to get our instance name. ==--
    --== We do this so the email we receive makes more sense. ==--
     declare @servername nvarchar(150)
     set @servername = @@servername
    --== We set another variable to create a subject line for the email. ==-- 
     declare @mysubject nvarchar(200)
     set @mysubject = 'Deadlock event notification on server '+@servername+'.'
     --== Now we will prepare and send the email. Change the email address to suite your environment. ==-- 
     EXEC msdb.dbo.sp_send_dbmail @recipients='DBA_Super_Hero@email.com',
     @subject = @mysubject,
     @body = 'Deadlock has occurred. View attachment to see the deadlock info',
     @query = 'select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''%Deadlock encountered%'' order by Id DESC)',
     @query_result_width = 600,
     @attach_query_result_as_file = 1
     END
     --== Clean up our process by dropping our temporary table. ==--
     DROP TABLE tempdb.dbo.ErrorLog
     
     
     
     --== SECOND STEP: ==--
     --== SQL Server Agent Job ==--
     USE [msdb]
    GO
    BEGIN TRANSACTION
    DECLARE @ReturnCode INT
    SELECT @ReturnCode = 0
    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
    BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    END
    DECLARE @jobId BINARY(16)
    EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Deadlock Job', 
     @enabled=1, 
     @notify_level_eventlog=0, 
     @notify_level_email=0, 
     @notify_level_netsend=0, 
     @notify_level_page=0, 
     @delete_level=0, 
     @description=N'No description available.', 
     @category_name=N'[Uncategorized (Local)]', 
    @owner_login_name=N'sa', @job_id = @jobId OUTPUT
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Deadlock has occurred.', 
     @step_id=1, 
     @cmdexec_success_code=0, 
     @on_success_action=1, 
     @on_success_step_id=0, 
     @on_fail_action=2, 
     @on_fail_step_id=0, 
     @retry_attempts=0, 
     @retry_interval=0, 
     @os_run_priority=0, @subsystem=N'TSQL', 
     @command=N'--== This is for SQL 2005 and higher. ==--
    --== We will create a temporary table to hold the error log detail. ==--
    --== Before we create the temporary table, we make sure it does not already exist. ==--
     IF OBJECT_ID(''tempdb.dbo.ErrorLog'') IS Not Null
     BEGIN
     DROP TABLE tempdb.dbo.ErrorLog
     END
     --== We have checked for the existence of the temporary table and dropped it if it was there. ==--
     --== Now, we can create the table called tempdb.dbo.ErrorLog ==--
    CREATE TABLE tempdb.dbo.ErrorLog (Id int IDENTITY (1, 1) NOT NULL, 
    logdate DATETIME, procInfo VARCHAR(10), ERRORLOG VARCHAR(MAX))
    --== We create a 3 column table to hold the contents of the SQL Server Error log. ==--
    --== Then we insert the actual data from the Error log into our newly created table. ==--
     INSERT INTO tempdb.dbo.ErrorLog
     EXEC master.dbo.sp_readerrorlog
    --== With our table created and populated, we can now use the info inside of it. ==--
     BEGIN
    --== Set a variable to get our instance name. ==--
    --== We do this so the email we receive makes more sense. ==--
     declare @servername nvarchar(150)
     set @servername = @@servername
    --== We set another variable to create a subject line for the email. ==-- 
     declare @mysubject nvarchar(200)
     set @mysubject = ''Deadlock event notification on server ''+@servername+''.''
     --== Now we will prepare and send the email. Change the email address to suite your environment. ==-- 
     EXEC msdb.dbo.sp_send_dbmail @recipients=''DBA_Super_Hero@email.com'',
     @subject = @mysubject,
     @body = ''Deadlock has occurred. View attachment to see the deadlock info'',
     @query = ''select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''''%Deadlock encountered%'''' order by Id DESC)'',
     @query_result_width = 600,
     @attach_query_result_as_file = 1
     END
     --== Clean up our process by dropping our temporary table. ==--
     DROP TABLE tempdb.dbo.ErrorLog
    ', 
     @database_name=N'master', 
     @flags=0
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    COMMIT TRANSACTION
    GOTO EndSave
    QuitWithRollback:
     IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
    EndSave:
    GO
     

    public by cghersi  4084  6  7  0

    Check the DB Size of a MS SQL Server instance

    This stored procedure retrieves some interesting figures regarding the size of the current database both in terms of file sizes and available space. It also stores the results into a table named DBSize in order to keep a story of such figures (e.g. It can be invoked in a maintenace plan...)
    CREATE PROCEDURE [dbo].[DB_CheckDBSize]
    AS
    BEGIN
    	SET NOCOUNT ON;
    	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    
    	declare @dbname varchar(50) = db_name();
    	declare @dbsize table (Dbname sysname,dbstatus varchar(50),Recovery_Model varchar(40) default ('NA'), file_Size_MB decimal(30,2)default (0),
    							Space_Used_MB decimal(30,2)default (0),Free_Space_MB decimal(30,2) default (0)) 
    
    	insert into @dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB) 
    		select	DB_NAME() AS DbName, 
    				CONVERT(varchar(20),DatabasePropertyEx(@dbname,'Status')) ,  
    				CONVERT(varchar(20),DatabasePropertyEx(@dbname,'Recovery')),  
    				sum(size)/128.0 AS File_Size_MB, 
    				sum(CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT))/128.0 as Space_Used_MB, 
    				SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,'SpaceUsed') AS INT))/128.0 AS Free_Space_MB  
    		from	sys.database_files  
    		where	type=0 
    		group by type
    
    	-------------------log size-------------------------------------- 
    	declare @logsize table (Dbname sysname, Log_File_Size_MB decimal(38,2)default (0),log_Space_Used_MB decimal(30,2)default (0),log_Free_Space_MB decimal(30,2)default (0)) 
    
    	insert into @logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB) 
    		select	DB_NAME() AS DbName, 
    				sum(size)/128.0 AS Log_File_Size_MB, 
    				sum(CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT))/128.0 as log_Space_Used_MB, 
    				SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,'SpaceUsed') AS INT))/128.0 AS log_Free_Space_MB  
    		from	sys.database_files  
    		where	type=1 
    		group by type 
    
    	--------------------------------database free size 
    	declare @dbfreesize table (name sysname, database_size varchar(50), Freespace varchar(50)default (0.00)) 
      
    	insert into @dbfreesize(name,database_size,Freespace) 
    		SELECT	database_name = db_name() 
    				,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + 'MB') 
    				,'unallocated space' = ltrim(str(( 
    					CASE  
    						WHEN dbsize >= reservedpages 
    							THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576 
    						ELSE 0 
    						END 
    					), 15, 2) + ' MB') 
    		FROM ( 
    			SELECT dbsize = sum(convert(BIGINT, CASE  
    							WHEN type = 0 
    								THEN size 
    							ELSE 0 
    							END)) 
    				,logsize = sum(convert(BIGINT, CASE  
    							WHEN type <> 0 
    								THEN size 
    							ELSE 0 
    							END)) 
    			FROM sys.database_files 
    		) AS files 
    		,( 
    			SELECT reservedpages = sum(a.total_pages) 
    				,usedpages = sum(a.used_pages) 
    				,pages = sum(CASE  
    						WHEN it.internal_type IN ( 
    								202 
    								,204 
    								,211 
    								,212 
    								,213 
    								,214 
    								,215 
    								,216 
    								) 
    							THEN 0 
    						WHEN a.type <> 1 
    							THEN a.used_pages 
    						WHEN p.index_id < 2 
    							THEN a.data_pages 
    						ELSE 0 
    						END) 
    			FROM sys.partitions p 
    			INNER JOIN sys.allocation_units a 
    				ON p.partition_id = a.container_id 
    			LEFT JOIN sys.internal_tables it 
    				ON p.object_id = it.object_id 
    		) AS partitions 
    	----------------------------------- 
       
    	declare @alldbstate table (dbname sysname, DBstatus varchar(55), R_model Varchar(30)) 
    
    	insert into @alldbstate (dbname,DBstatus,R_model) 
    	select name,CONVERT(varchar(20),DATABASEPROPERTYEX(name,'status')),recovery_model_desc from sys.databases 
      
    	insert into @dbsize(Dbname,dbstatus,Recovery_Model) 
    	select dbname,dbstatus,R_model from @alldbstate where DBstatus <> 'online' 
      
    	insert into @logsize(Dbname) 
    	select dbname from @alldbstate where DBstatus <> 'online' 
      
    	insert into @dbfreesize(name) 
    	select dbname from @alldbstate where DBstatus <> 'online' 
      
    	select	CONCAT('  ' + d.Dbname, ' (', d.dbstatus, ' - ', d.Recovery_Model, '): DBTotSize = ', (file_size_mb + log_file_size_mb), ' MB.')
    	from	@dbsize d join 
    			@logsize l on d.Dbname=l.Dbname
    
    	union
    
    	select	CONCAT('Data: Used ', d.Space_Used_MB, ' MB + Free ', d.Free_Space_MB, ' MB = ', d.file_Size_MB, ' MB.')
    	from	@dbsize d
    
    	union
    
    	select	CONCAT('Log: Used ', log_Space_Used_MB, ' MB + Free ', l.log_Free_Space_MB, ' MB = ', l.Log_File_Size_MB, ' MB.')
    	from	@logsize l
    
    	union
    
    	select	'TotalFreeSpace = ' + fs.Freespace
    	from	@dbfreesize fs
    
    	INSERT INTO dbo.DBSize 
    		(Timestamp, Dbname, DBStatus, RecoveryModel, TotalFileSizeMB, DataSpaceUsedMB, DataFreeSpaceMB, DataFileSizeMB, LogSpaceUsedMB,
    		 LogFreeSpaceMB, LogFileSizeMB, TotalFreeSpaceMB)
    		SELECT	GETDATE(), d.Dbname, d.dbstatus, d.Recovery_Model, file_size_mb + log_file_size_mb, d.Space_Used_MB, d.Free_Space_MB, d.file_Size_MB,
    				log_Space_Used_MB, l.log_Free_Space_MB, l.Log_File_Size_MB, fs.Freespace
    		FROM	@dbsize d join 
    				@logsize l on d.Dbname=l.Dbname join
    				@dbfreesize fs on d.Dbname = fs.name
    END

    public by cghersi  7325  31  8  0

    How to correctly Shrink Log File for SQL Server

    In case your transaction log reaches abnormal dimension (e.g. you forgot the backup of the log in your maintenace plan...) it is highly useful this script that allows you to safely shrink the transaction log to the wanted dimension
    USE AdventureWorks
    GO
    
    -- Use some dynamic SQL just only not to re-write several times the name 
    -- of your db, or to insert this snippet into a loop for all your databases...
    DECLARE @dbname varchar(50) = 'AdventureWorks';
    DECLARE @logFileName varchar(50) = @dbname  + '_log';
    DECLARE @SQL nvarchar(max);
    SET @SQL = REPLACE('ALTER DATABASE {dbname} SET RECOVERY FULL;', '{dbname}', @dbname);
    EXECUTE(@SQL);
    
    DECLARE @path nvarchar(255) = N'F:\BCK_DB\logBCK' + CONVERT(CHAR(8), GETDATE(), 112) + '_'
      + REPLACE(CONVERT(CHAR(8), GETDATE(), 108),':','') + '.trn';
    
    BACKUP LOG @dbname TO DISK = @path WITH INIT, COMPRESSION;
    
    DBCC SHRINKFILE(@logFileName);
    
    -- determine here the new file size and growth rate:
    SET @SQL = REPLACE('ALTER DATABASE {dbname} MODIFY FILE (NAME = ' + @logFileName + ', SIZE = 32000MB, FILEGROWTH = 10%);', 
    	'{dbname}', @dbname);
    EXECUTE(@SQL);
    GO

    public by cghersi  4135  1  7  0

    Testing script for Stored Procedures

    This script helps developers test the performance of a stored procedure or block of code. Although it was written specifically to assist me in comparing stored procedures, it can easily be modified for other testing purposes. The script expects two tables, 'Seq1' and 'Seq2' to exist, and be of the structure as listed in the header comment of the
    /*************************************************************************************************
    ** File: "20090217 - testing script.sql"
    ** Desc: This is a more generalized script to run performance testing on the sequence 
    ** comparison code. It can be modified to test any other spds. This script requires
    ** the existence of two tables, Seq1 and Seq2, which should have the following structure:
    ** 
    ** CREATE TABLE Seq1 (
    **   CodeLineTxt varchar(max), /* stores the original line of code */
    **   CodeLineNum int not null identity(1,1), /* stores the line number */
    **   MatchLineNum int /* stores the matching line of code from spd #2 */
    ** )
    ** 
    ** Return values: report & results
    ** 
    ** Called by: 
    ** 
    ** Parameters:
    ** Input
    ** ----------
    ** none
    **
    ** Output
    ** -----------
    ** none
    **
    ** Auth: Jesse McLain
    ** Email: jesse@jessemclain.com
    ** Web: www.jessemclain.com
    ** Blog: http://jessesql.blogspot.com/2009/02/comparing-spds-part-3-performance.html
    **
    ** Date: 02/16/2008
    **
    ***************************************************************************************************
    ** Change History
    ***************************************************************************************************
    ** Date:    Author:         Description:
    ** -------- --------        -------------------------------------------
    ** 20080216 Jesse McLain    Created script
    **************************************************************************************************/
    
    SET NOCOUNT ON
    
    DECLARE @RunsPerInputSize int         ; SET @RunsPerInputSize = 1      /* #runs of @InputSize to execute */
    DECLARE @InputSizeIncrement int       ; SET @InputSizeIncrement = 50   /* @InputSize to increment btwn outer runs */
    DECLARE @TotalNumberIncrements int    ; SET @TotalNumberIncrements = 1 /* #increments to execute */
    
    DECLARE @StartTime datetime           ; SET @StartTime = GETDATE()
    DECLARE @StopTime datetime            ; SET @StopTime = GETDATE()
    DECLARE @Seq1 varchar(max)            ; SET @Seq1 = 'Test Sequence "'
    DECLARE @Seq2 varchar(max)            ; SET @Seq2 = 'Test Sequence "'
    DECLARE @TestValue varchar(2)         ; SET @TestValue = ''             /* holder to load values into seq tables */
    DECLARE @InputSize int                ; SET @InputSize = 0              /* input size for the current run */
    DECLARE @InputValueIdx int            ; SET @InputValueIdx = 1          /* counter */
    DECLARE @RunIdx int                   ; SET @RunIdx = 1                 /* counter */
    DECLARE @IncrIdx int                  ; SET @IncrIdx = 1                /* counter */
    DECLARE @Seq1Size int
    DECLARE @Seq2Size int
    DECLARE @Seq1Sizea int
    DECLARE @Seq2Sizea int
    DECLARE @PcntMatch decimal(9, 2)
    
    CREATE TABLE #PerformanceResults (
      InputSize int,
      RunStart datetime,
      RunDone datetime,
      PcntMatch decimal(9, 2)
    )
    
    /* the straight-forward approach to testing would be to start at the smallest
    input size, run through as many runs as we need for that, move on to the next
    input size, test that, until we test the max input size. We don't do that here.
    The problem with that approach is that if there's an external process running
    during the testing of an input size, the results for that size might be false.
    The approach here is to test the min size once, then the next largest size, 
    until the max size is tested, then start all over and repeat until we've tested
    each inputsize as many as "@RunsPerInputSize" times. */
    
    SET @RunIdx = 1
    /* outer loop to increment the number of runs per input size */
    WHILE @RunIdx <= @RunsPerInputSize
    BEGIN
      SET @IncrIdx = 1
      /* inner loop to increment each input size */
      WHILE @IncrIdx <= @TotalNumberIncrements
      BEGIN
        PRINT 'Testing size ' + LTRIM(STR(@IncrIdx)) + '/' + LTRIM(STR(@TotalNumberIncrements))
         + ', for run ' + LTRIM(STR(@RunIdx)) + '/' + LTRIM(STR(@RunsPerInputSize))
     
        SET @InputSize = @IncrIdx * @InputSizeIncrement
     
     
        /* insert rows into test table 1 */
        TRUNCATE TABLE Seq1
        SET @InputValueIdx = 1
        WHILE @InputValueIdx <= @InputSize
        BEGIN
          SET @TestValue = CHAR(FLOOR(RAND() * 10) + 65) + CHAR(FLOOR(RAND() * 10) + 65)
          INSERT INTO Seq1 (CodeLineTxt) VALUES (@TestValue)
          SET @Seq1 = @Seq1 + @TestValue
          SET @InputValueIdx = @InputValueIdx + 1
        END
        UPDATE Seq1 SET MatchLineNum = 0
        SET @Seq1 = @Seq1 + '"'
    
    
        /* insert rows into test table 2 */
        TRUNCATE TABLE Seq2
        SET @InputValueIdx = 1
        WHILE @InputValueIdx <= @InputSize
        BEGIN
          SET @TestValue = CHAR(FLOOR(RAND() * 10) + 65) + CHAR(FLOOR(RAND() * 10) + 65)
          INSERT INTO Seq2 (CodeLineTxt) VALUES (@TestValue)
          SET @Seq2 = @Seq2 + @TestValue
          SET @InputValueIdx = @InputValueIdx + 1
        END
        UPDATE Seq2 SET MatchLineNum = 0
        SET @Seq2 = @Seq2 + '"'
    
    
        /* wrap the executing code around timers to test */
        SET @StartTime = GETDATE()
      
        EXEC spd_SequenceCompare
    
        SET @StopTime = GETDATE()
    
    
        /* record results */
        SELECT @Seq1Size = COUNT(*) FROM Seq1
        SELECT @Seq2Size = COUNT(*) FROM Seq2
      
        SELECT @Seq1Sizea = COUNT(*) FROM Seq1 T1 WHERE MatchLineNum <> 0
        SELECT @Seq2Sizea = COUNT(*) FROM Seq2 T1 WHERE MatchLineNum <> 0
      
        SET @PcntMatch = 100.0 * (@Seq1Sizea / (1.0 * @Seq1Size) + @Seq2Sizea / (1.0 * @Seq2Size)) / 2
    
        INSERT INTO #PerformanceResults (InputSize, RunStart, RunDone, PcntMatch)
        VALUES (@InputSize, @StartTime, @StopTime, @PcntMatch)
     
        SET @IncrIdx = @IncrIdx + 1
      END
    
      SET @RunIdx = @RunIdx + 1
    END
    
    SELECT 
      InputSize, 
      NumberOfRuns = COUNT(*),
      AverageRunTime = AVG(CONVERT(decimal(9, 2), CONVERT(varchar(max), DATEDIFF(ss, RunStart, RunDone))
       + '.' + CONVERT(varchar(max), DATEDIFF(ms, RunStart, RunDone)))),
      AveragePercentMatch = AVG(PcntMatch)
    FROM #PerformanceResults
    GROUP BY InputSize
    
    
    DROP TABLE #PerformanceResults
    
    SET NOCOUNT OFF
    • Public Snippets
    • Channels Snippets