How to Delete Millions of Rows From SQL Server Table ?

Sometimes there will be the requirement to delete million rows from a multi million table, as it is hard to run a single Delete Statement Like below Query 1 because it could eventually fill up your transaction log  and may not be truncated from log until all the rows have been deleted and the statement is completed because it will be treated as open transaction. Instead we can achieve the same by running the delete in multiple batches in an easy way.  Once we knew the number of rows we need to delete, we will choose the batch size and number of batches we need to run like in Query 2 where I need to Delete to 1 million rows, I chose batch size as 10000 and number of batches to 100 , so that 10000*100 equals to 1 Million rows. Number of batches could be more approximately.

Query 1:  Query to Delete records older than ‘1/1/2015’

Select Count(*) from History Where Date <= ‘1/1/2015’

1000000 – 1 Million rows and we need to delete all 1 million rows.

Delete from History Where Date <= ‘1/1/2015’

Query 2:

DELETE Top (10000) from History Where Date <= ‘1/1/2015’

Go 100

The Tricky part is ‘Go ‘ which is batch separator , the number we pass to the Go is the number of times you want to run that Query and each run time is considered as single batch.

Advertisements

SQL server updates or service pack hung or failed to install!!

        When installing sql server update or service pack, one of the reason to fail if you have invalid registry settings stored from previous installations and may not be valid anymore due to the change in location of the binaries when you installed previously. I recently had an issue while installing sql service pack 3 for sql 2008 r2 and thought it would be useful to share.

When ever your installation hung and does not move forward or failed, you can look into the installation logs that can be found under, C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log and every time you try to install it creates sub folder with logs in it, so check the time stamp for the current installation logs. Search through installation for any errors. Here is the sample of the error I got and the reason for the installation hang and never moves forward.

 

MSI (s) (0C:34) []: SOURCEMGMT: Attempting to use LastUsedSource from source list.

MSI (s) (0C:34) []: SOURCEMGMT: Trying source \\************\sql\Microsoft SQL Server 2008 DEV R2\x64\setup\sql_engine_core_inst_msi\.

MSI (s) (0C:34) []: Note: 1: 1314 2: \\************\sql\Microsoft SQL Server 2008 DEV R2\x64\setup\sql_engine_core_inst_msi\

MSI (s) (0C:34) []: ConnectToSource: CreatePath/CreateFilePath failed with: -2147483648 1314 -2147483648

MSI (s) (0C:34) [] ConnectToSource (con’t): CreatePath/CreateFilePath failed with: -2147483648 -2147483648

MSI (s) (0C:34) []: SOURCEMGMT: net source ‘\\**********\sql\Microsoft SQL Server 2008 DEV R2\x64\setup\sql_engine_core_inst_msi\’ is invalid.

MSI (s) (0C:34) []: Note: 1: 1706 2: -2147483647 3: sql_engine_core_inst.msi

MSI (s) (0C:34) []: SOURCEMGMT: Processing net source list.

MSI (s) (0C:34) []: Note: 1: 1706 2: -2147483647 3: sql_engine_core_inst.msi

MSI (s) (0C:34) []: SOURCEMGMT: Processing media source list.

MSI (s) (0C:34) []: Note: 1: 2203 2: 3: -2147287037

MSI (s) (0C:34) []: SOURCEMGMT: Source is invalid due to missing/inaccessible package.

 

As the error highlited in red, it is looking for the location of the binaries in the location where it was when the last time sql server or updates are installed as it stores this location in registry in location HKEY_CLASSES_ROOT>installer>Products> then we have to scroll down each product list under with some system number and find one for the SQl server Database Engine services and it will be missing for all other components too. So in order to fix this you need edit manually in registry and find the missing packages using a VB script provided from Microsoft as provide in the KB 969052.

 

 

The other easy workaround that I found and worked for me is try repair from programs and features > right click on SQL server > click change/remove >repair and not it will ask for the location of the SQL server binaries, once you pointed to the new binaries location it does repair the setup support files in registry and updates the new location of the missing packages for lastusedsource, once this is done reboot the server and re-install service pack and installed successfully.

If you click on net under the source list it will display your old location and new locations.

 


Finding top 10 large tables in each database in an instance of SQL server

It is a good practice to find and monitor your large SQL server tables, we may surprise in some cases where our large audit or logging tables that are not being cleaned up frequently and fill into multiple GB’s of data and may result in higher backup and restore times. Working with your development team on these large tables to see if they are serving any business else can be archived and reduces huge maintenance over head with backup/restores and also saves disk space available.

Here is the script that finds top 10 large tables that are ordered by reserved disk space.

 

--CREATING STAGING TABLES S
CREATE TABLE MASTER.DBO.SPT_SPACE
(
DBNAME VARCHAR(50) NOT NULL,
OBJID VARCHAR(300) NULL,
ROWS INT NULL,
RESERVED DEC(15) NULL,
DATA DEC(15) NULL,
INDEXP DEC(15) NULL,
UNUSED DEC(15) NULL
)
CREATE TABLE [DBO].[TOP10_LARGE](
[DBNAME] [VARCHAR](50) NOT NULL,
[TABLE_NAME] [SYSNAME] NULL,
[ROWS] [CHAR](11) NULL,
[RESERVED_KB] [VARCHAR](18) NULL,
[DATA_KB] [VARCHAR](18) NULL,
[INDEX_SIZE_KB] [VARCHAR](18) NULL,
[UNUSED_KB] [VARCHAR](18) NULL
) ON [PRIMARY]
EXEC SP_MSFOREACHDB 'USE ?

DECLARE @ID INT
DECLARE @TYPE CHARACTER(2)
DECLARE @PAGES INT
DECLARE @DBNAME SYSNAME
DECLARE @DBSIZE DEC(15,0)
DECLARE @BYTESPERPAGE DEC(15,0)
DECLARE @PAGESPERMB DEC(15,0)

SET NOCOUNT ON

-- CREATE A CURSOR TO LOOP THROUGH THE USER TABLES
DECLARE C_TABLES CURSOR FOR
SELECT ID
FROM SYSOBJECTS
WHERE XTYPE = ''U''

OPEN C_TABLES

FETCH NEXT FROM C_TABLES
INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN

/* CODE FROM SP_SPACEUSED */
INSERT INTO MASTER.DBO.SPT_SPACE (DBNAME, OBJID, RESERVED)

SELECT DB_NAME(), OBJID = @ID, SUM(RESERVED)
FROM SYSINDEXES
WHERE INDID IN (0, 1, 255)
AND ID = @ID

SELECT @PAGES = SUM(DPAGES)
FROM SYSINDEXES
WHERE INDID < 2
AND ID = @ID
SELECT @PAGES = @PAGES + ISNULL(SUM(USED), 0)
FROM SYSINDEXES
WHERE INDID = 255
AND ID = @ID
UPDATE MASTER.DBO.SPT_SPACE
SET DATA = @PAGES
WHERE OBJID = @ID
/* INDEX: SUM(USED) WHERE INDID IN (0, 1, 255) - DATA */
UPDATE MASTER.DBO.SPT_SPACE
SET INDEXP = (SELECT SUM(USED)
FROM SYSINDEXES
WHERE INDID IN (0, 1, 255)
AND ID = @ID)
- DATA
WHERE OBJID = @ID

/* UNUSED: SUM(RESERVED) - SUM(USED) WHERE INDID IN (0, 1, 255) */
UPDATE MASTER.DBO.SPT_SPACE
SET UNUSED = RESERVED
- (SELECT SUM(USED)
FROM SYSINDEXES
WHERE INDID IN (0, 1, 255)
AND ID = @ID)
WHERE OBJID = @ID

UPDATE MASTER.DBO.SPT_SPACE
SET ROWS = I.ROWS
FROM SYSINDEXES I
WHERE I.INDID < 2
AND I.ID = @ID
AND OBJID = @ID

FETCH NEXT FROM C_TABLES
INTO @ID
END

CLOSE C_TABLES
DEALLOCATE C_TABLES'

EXEC SP_MSFOREACHDB' USE ?
INSERT INTO MASTER.DBO.TOP10_LARGE
SELECT TOP 10
DBNAME,
TABLE_NAME = (SELECT NAME FROM SYS.SYSOBJECTS WHERE ID = OBJID),
ROWS = CONVERT(CHAR(11), ROWS),
RESERVED_KB = LTRIM(STR(RESERVED * D.LOW / 1024.,15,0) ),
DATA_KB = LTRIM(STR(DATA * D.LOW / 1024.,15,0) ),
INDEX_SIZE_KB = LTRIM(STR(INDEXP * D.LOW / 1024.,15,0)),
UNUSED_KB = LTRIM(STR(UNUSED * D.LOW / 1024.,15,0) )

FROM MASTER.DBO.SPT_SPACE A, MASTER.DBO.SPT_VALUES D
WHERE D.NUMBER = 1 AND D.TYPE = ''E'' AND DBNAME = ''?''ORDER BY RESERVED DESC'
GO

SELECT * FROM MASTER.DBO.TOP10_LARGE WHERE DBNAME NOT IN ('MASTER', 'TEMPDB','MSDB', 'MODEL')

--CLEANING UP ALL TABLES.

-- DROP TABLE MASTER.DBO.TOP10_LARGE

-- DROP TABLE MASTER.DBO.SPT_SPACE

 

 

Always – ON Availability group Alerts

After setting up Always ON , we need to configure SQL server Alerts to know about the availability Group errors and other failover information you may want to know when occurred. Here is the list of error and scripts to create them.

You may don’t want to configure all alerts and flood inboxes when happened, you can choose which one to configure using below query,

SELECT message_id [error_number], severity, text
FROM sys.messages
WHERE text LIKE (‘%availability%’)
AND is_event_logged = 1;


Here is the Script to configure all alerts related to AG’s

————————————————————
— Always – ON HA Error Alerts
————————————————————

USE [msdb]
GO

/****** Object: Alert [HA Error – 35254] Script Date: 10/20/2014 1:55:32 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35254′,
@message_id=35254,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35262] Script Date: 10/20/2014 1:55:43 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35262′,
@message_id=35262,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35273] Script Date: 10/20/2014 1:56:01 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35273′,
@message_id=35273,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35274] Script Date: 10/20/2014 1:56:16 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35274′,
@message_id=35274,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35275] Script Date: 10/20/2014 1:56:34 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35275′,
@message_id=35275,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35276] Script Date: 10/20/2014 1:56:46 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35276′,
@message_id=35276,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35279] Script Date: 10/20/2014 1:57:03 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35279′,
@message_id=35279,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35299] Script Date: 10/20/2014 1:57:23 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35299′,
@message_id=35299,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41048] Script Date: 10/20/2014 1:57:38 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41048′,
@message_id=41048,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41049] Script Date: 10/20/2014 1:57:56 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41049′,
@message_id=41049,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41050] Script Date: 10/20/2014 1:58:13 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41050′,
@message_id=41050,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41051] Script Date: 10/20/2014 1:58:29 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41051′,
@message_id=41051,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41052] Script Date: 10/20/2014 1:58:43 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41052′,
@message_id=41052,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41053] Script Date: 10/20/2014 1:59:06 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41053′,
@message_id=41053,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41054] Script Date: 10/20/2014 1:59:21 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41054′,
@message_id=41054,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41055] Script Date: 10/20/2014 1:59:34 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41055′,
@message_id=41055,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41089] Script Date: 10/20/2014 1:59:47 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41089′,
@message_id=41089,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [AG Data Movement – Resumed] Script Date: 10/20/2014 2:13:37 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’AG Data Movement – Resumed’,
@message_id=35265,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [AG Data Movement – Suspended] Script Date: 10/20/2014 2:13:45 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’AG Data Movement – Suspended’,
@message_id=35264,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [AG Role Change] Script Date: 10/20/2014 2:13:51 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’AG Role Change’,
@message_id=1480,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

Make sure to enable e-mail operator for each alert after executing above script.

What is Parameter Sniffing in simple words?

I have been asked recently what is parameter sniffing in an interview. Well, we all know how Optimizer in SQL server works to retrieving the data from the tables. We do also know that Optimizer reads the statistics and internal pre-compiled plans instead generating a new plan for parameterized Queries and Store procedures. Here we are, how Optimizer knows we are executing a similar query as it searching for pre-compiled plan for us because it was reading the parameters we passed to the stored procedure or a parameterized query and it is called “sniffing”.

What it counts for Performance, Good or bad?

 “It depends “Yes “It depends” – I know we use this word many times in regards to databases

Good:

By default, Parameter sniffing is enabled and SQL Server caches the plans and reads the parameters those passed to the stored procedures or parameterized SQL Queries. We benefit from it as Optimizer will go and search for the plan it compiled earlier in cache when we execute the same query again. Optimizer will not need to re-create the plan to execute the same query multiple times.

Bad:

This is all good only when there are good statics are maintained and typical parameters are used. What changes day by day is the requirement for using the new parameters with different words and optimizer will tries to use the same old plan in cache and may not be good for the new parameters as data has grown a lot and statistics have not updated yet and may be the plan for new parameter was doing table scans.

Work Around:

  1. Until we see a performance issue with this parameter sniffing it is better to leave it by default and we really benefit from it.
  2. When we see lot of performance issue, we could disable it using a trace flag 4136, this will disable the parameter sniffing at the server level.
  3. We could use it to disable only for specific query then we could use the recompile  query hint