How to remove a specific query plan from cache.

Sometimes our query cache would use the same table structure even if the table structure has been changed say we added or removed more columns , but the query cache still would follow old schema and we receive error that schema doesnt match.

Example:Error Line: 262; Error Severity: 16; Error State: 4;
Error Message: Could not complete cursor operation because the table schema changed after the cursor was declared.

In such cases below query becomes handy to clear the query cache

-- Use the below query to get the handle of the query which is being run
SELECT plan_handle, st.text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'SELECT * FROM TableA%'
GO

-- Use the SQL Handle to remove the specific plan from the cache
DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000)
GO

Initiate a Backup of database

How do I initiate a backup of database automatically using script and backup file needs to be
unique with date and time(till seconds) is included in backup file.

Below mentioned command takes DBName as the input parameter and it creates a folder based on database name and places backup file inside that.

DECLARE @DBNAME VARCHAR(100)
DECLARE @EXEC_STR VARCHAR(100)
DECLARE @Backup_Folder VARCHAR(5000)
DECLARE @Backup_Path VARCHAR(5000)
SELECT @DBNAME = 'master'
SELECT @Backup_Folder = 'E:\' + @DBNAME + '\Backup\'
SELECT @EXEC_STR = 'md ' + @Backup_Folder
exec master..xp_cmdshell @EXEC_STR
SELECT @Backup_Path = @Backup_Folder + @DBNAME + '_Full_' + replace(replace(convert(varchar(20),getdate(),113),':','_'),' ' , '_') + '.bak'
SELECT @Backup_Path
BACKUP DATABASE @DBNAME TO DISK = @Backup_Path WITH NOFORMAT
, NOINIT
, NAME = 'Full Database Backup'
, SKIP,
,STATS = 5
GO

Calculate BACKUP & Restore Time Estimate

Use this DMV to calculate the estimated time for restore and backup operations:

SELECT command,
s.text,
start_time,
percent_complete,
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

What is Selectivity

What is Selectivity:
The ratio between the number of distinct values in the indexed column to the number of records in a table is called as selectivity of an index. So it is obvious that ideal selectivity
would be 1(or Closer to 1) which means most of the values in column are unique

Examples of Good Selectivity:
Consider a table having say 1000 records and one of the indexed column has 900 distinct values then the selectivity would be 900/1000 = 0.90
Indexes on these columns are the most selective and the most effective in optimizing performance. So, the term selectivity of an index means percentage of rows in a table having
the same value for the indexed column.

Examples of Bad Selectivity:
Consider a table having say 1000 records and one of the indexed column has 50 distinct values then the selectivity would be 50/1000 = 0.05
It is evident that a full table scan is more efficient as using such an index where much more I/O is needed to scan repeatedly the index and the table.

B-TREE Indexes improve performance of queries which select small percentage of rows from a table. As a thumb rule, we should create indexes on tables that are often
queried for less than 15% of the table's rows.

Let us understand selectivity by means of a example

IF EXISTS ( SELECT 'X' FROM sysobjects where TYPE = 'U' and name = 'TEST_INDEX_SEL')
DROP TABLE TEST_INDEX_SEL
GO

CREATE TABLE TEST_INDEX_SEL
( Col1_Int INT IDENTITY(1,1) NOT NULL,
Col2_Varchar VARCHAR(255) NOT NULL,
Col3_Varchar VARCHAR(2000) NOT NULL,
COl4_Flag BIT NOT NULL
)

--Poplulate 1000 rows
DECLARE @iCount INTEGER
SELECT @iCount = 1

WHILE @iCount < = 1000
BEGIN

INSERT INTO TEST_INDEX_SEL(Col2_Varchar,Col3_Varchar,COl4_Flag)
SELECT 'ABC' + CONVERT(VARCHAR,@iCount),'\\ABC' + CONVERT (VARCHAR,@iCount) + '\Share',1

SELECT @iCount = @iCount + 1

END

--Update some rows randomly for sampling
UPDATE TEST_INDEX_SEL
SET Col2_Varchar = 'ABC2',
Col3_Varchar = '\\ABC5\Share',
COl4_Flag = 0
WHERE Col1_Int IN ( SELECT TOP 100 COL1_INT FROM TEST_INDEX_SEL ORDER BY NEWID())


UPDATE TEST_INDEX_SEL
SET Col2_Varchar = 'ABC203',
Col3_Varchar = '\\ABC764\Share',
COl4_Flag = 0
WHERE Col1_Int IN ( SELECT TOP 100 COL1_INT FROM TEST_INDEX_SEL ORDER BY NEWID())


UPDATE TEST_INDEX_SEL
SET Col3_Varchar = '\\ABC378\Share',
COl4_Flag = 0
WHERE Col1_Int IN ( SELECT TOP 100 COL1_INT FROM TEST_INDEX_SEL ORDER BY NEWID())

Now for example selectivity of Col2_Varchar can be calculated as follows:

SELECT COUNT (DISTINCT Col2_Varchar) FROM TEST_INDEX_SEL

-- Output
-- 808

SELECT COUNT ('X') FROM TEST_INDEX_SEL

-- Output
-- 1000

So selectivity would be arrived by dividing these 2 values which is not bad and its 0.8
which is just 20% away from ideal value of 1

SELECT 808/1000 = 0.808

--To find for all columns in that table
SELECT CONVERT(NUMERIC(5,2),COUNT (DISTINCT Col2_Varchar))/
(SELECT COUNT ('X') FROM TEST_INDEX_SEL),
CONVERT(NUMERIC(5,2),COUNT (DISTINCT Col3_Varchar))/
(SELECT COUNT ('X') FROM TEST_INDEX_SEL),
CONVERT(NUMERIC(5,2),COUNT (DISTINCT COl4_Flag))/
(SELECT COUNT ('X') FROM TEST_INDEX_SEL)
FROM TEST_INDEX_SEL T

/* Output

Col2_Selecvity Col3_Selectivity Col4_Selectivity
0.8080000000000 0.7260000000000 0.0020000000000

*/

By the above you we can infer that its ideal to create indexes on Col2 if the same is used in WHERE clauses and it wouldn’t help much to create on Col4 as it has low selectivity and that is the reason why many tables which have a bit value as 0 or 1 tend to be slow as it has low selectivity.

Query to change default data and log path

This query modifies the registry value of a data and log folder path for default instance of SQL Server and for named instance "MSSQLServer" has to be replaced with instance name

USE master
GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\SQLDB\DATA'
GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'Z:\SQLDB\LOG'
GO

Create Linked Server using Query

EXEC master.dbo.sp_addlinkedserver @server=N'Linked_Server_Name', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'SQL_Instance'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Linked_Server_Name',@useself=N'False',@locallogin=NULL,@rmtuser=N'username',@rmtpassword='password'GOEXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'data access', @optvalue=N'true'GOEXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'pub', @optvalue=N'false'GOEXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'rpc out', @optvalue=N'true'GOEXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'use remote collation', @optvalue=N'true'
GO