Recompile a stored procedure – SQL Server

After performing any maintenance activities and bulk updates  or inserts , recompiling the stored procedure or updated table will create new plan and give performance advantage.

sp_recompile ‘object_name’

——Script to recompile all the stored procedures on table.

USE AdventureWorks2012;

GO

EXEC sp_recompile N’Sales.Customer’;

GO

——- Script to recompile a specific stored procedure

USE TestDB;

GO

sp_recompile ‘sp_salesorders’

How to find the version and edition of SQL Server

SELECT SERVERPROPERTY(‘ProductVersion’) AS Version, SERVERPROPERTY(‘ProductLevel’) AS SPLevel, SERVERPROPERTY(‘Edition’) AS Edition;

(or)

SELECT @@VERSION;

Query for database restore history records — SQL Server

SELECT [rs].[destination_database_name],
[rs].[restore_date],
[bs].[backup_start_date],
[bs].[backup_finish_date],
[bs].[database_name] as [source_database_name],
CASE rs.[restore_type]
WHEN ‘D’ THEN ‘Database’
WHEN ‘I’ THEN ‘Differential’
WHEN ‘L’ THEN ‘Log’
WHEN ‘F’ THEN ‘File’
WHEN ‘G’ THEN ‘Filegroup’
WHEN ‘V’ THEN ‘Verifyonlyl’
END AS RestoreType,
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC