Category: Uncategorized

DBAmp performance considerations

Updating and Deleting rows using SQL

DBAmp supports updating and deleting Salesforce.com objects with SQL.
In order to get the maximum performance with your UPDATE and DELETE
statements, you need to understand how SQL Server handles
UPDATE/DELETE statements with a linked server (like DBAmp).
For example, take the following SQL UPDATE
Update SALESFORCE…Account
Set AnnualRevenue = 4000
Where Id=’00130000005ZsG8AAK’

Using the Display Estimated Execution Plan option from the Query
Analyzer, you can see that SQL Server will retrieve the entire Account
table from Salesforce and then search for the one row that has the Id of
00130000005ZsG8AAK. Then, SQL Server will update the AnnualRevenue of
that row.
Obviously, this UPDATE statement has poor performance which gets worse
as the size of the Account table grows. What we need is a way to retrieve
only the row with Id 00130000005ZsG8AAK and then update the
AnnualRevenue of that row. To do this, use an OPENQUERY clause as the
table name.

Update OPENQUERY(SALESFORCE,
‘Select Id, AnnualRevenue from Account
where Id=”00130000005ZsG8AAK” ‘)
set AnnualRevenue = 4000
Using an OPENQUERY clause insures that we retrieve only the row with the
proper Id.
You can construct stored procedures that make your code more readable
and that use the above technique. See the Create SF_UpdateAccount.sql
file in the DBAmp program directory as an example. Using this stored
procedure, we can do updates to the Account table using the following SQL:

exec SF_UpdateAccount ‘00130000008hz55AAA’,’BillingCity’,”’Denver”’
or
exec SF_UpdateAccount ‘00130000008hz55AAA’,’AnnualRevenue’,’20000′

You can use the SF_UpdateAccount stored procedure as a template for
building your own specialized stored procedures. See the file Create

SF_UpdateAnnualRevenue.sql for an example. Then, use the following
SQL to update the Annual Revenue of an account.
exec SF_UpdateAnnualRevenue ‘00130000009DCEcAAO’, 30000
Deleting rows with SQL has the same caveats. For best performance with
deletion by Id, use an OPENQUERY clause in the SQL statement. An
example of a stored procedure that deletes Accounts by Id is in the file
Create SF_DeleteAccount.sql.


For maximum scalability, please consider using the sf_TableLoader stored
procedure instead of SQL Update or Delete statements. The
sf_TableLoader stored procedure takes advantage of the ability to batch
together requests to the salesforce.com api.

Get a snapshot of indexes

Spreadsheet inventory of existing indexes

You need to run the below script under each database:

sp_BlitzIndex @GetAllDatabases = 1, @Mode = 2

Inventory of missing indexes

sp_BlitzIndex @GetAllDatabases = 1, @Mode = 3

Find large unused indexes

/* To find large, unused, junk drawer objects: */

sp_BlitzIndex @Mode =2 , @sortorder = 'size' sp_BlitzIndex @Mode =2 , @sortorder = 'rows'

/* For management overview before & after doing index tuning*/ sp_BlitzIndex @Mode=1

Database maintenance best practices

UPDATE: INDEXES & STATISTICS 

  • default to weekly, but then escalating to daily for specific stats that need it
  • You will need to update Column stats separately. Column stats do not get updated when you rebuild indexes or index stats

side note to be aware: Each time you update indexes or statistics on a table clears out the plan cache for those tables. Having outdated statistics can cause all sorts of issues, but updating statistics can also cause issues like parameter sniffing, we are not looking for a perfect plan but more like a good plan, a balance between the issues of outdated statistics against the parameter sniffing.

BACKUP SCHEDULE

Full Backups – WEEKLY

Differential Backups – DAILY (EVERY NIGHT)

Transaction Log Backups – Every few hours (ONLY IF Databases need to meet RPO of every few hours, additionally Database needs to be in FULL recovery mode)

SQL Server Checklist

1. Post installation, run the below script to set MAXDOP, CTFP and reserve memory for SQL server

EXEC sys.sp_configure N'cost threshold', N'50'
 GO
 EXEC sys.sp_configure N'max degree of parallelism', N'4'
 GO
 USE master; EXEC sp_configure 'show advanced option', '1';
 /* Set max memory to 85% of the OS's memory: */
 DECLARE @StringToExecute NVARCHAR(400);
 SELECT @StringToExecute = N'EXEC sys.sp_configure N''max server memory (MB)'', N''' + CAST(CAST(physical_memory_kb / 1024 * .85 AS INT) AS NVARCHAR(20)) + N''';'
 FROM sys.dm_os_sys_info;
 EXEC(@StringToExecute);
 GO
 RECONFIGURE WITH OVERRIDE
 GO

2. Install DBATools (open source power shell modules) for remote administration. Prompt yes to all the prompts that follow and complete the installation

Install-Module dbatools

3. Install sp_WhoIsActive stored proc

Windowssystem32> Install-DbaWhoIsActive -sqlinstance SQLSERVER01 -Database master

4.  Install First Reponder Kit

Install-DbaFirstResponderKit -SqlInstance SQLSERVER01 -Database master

5. Enable Remote DAC (Dedicated Admin Connection). This is to be used only when you are troubleshooting emergencies. Promptly disconnect the session once you are done using it

sp_configure 'remote admin connections', 1;

6. Setup Database Mail to send out email alerts

--Declare and Set Values
 /*
 @SMTPServer - You will need to get SMTP server that you want to use to send email out.
 @AdminEmail - After setup, the script will send test email to this email address
 @DomainName - This will be part of SQL Server Email Name
 @replyToEmail - this is how you will see for reply to email when you get the email.
 */
 use master
 DECLARE @SMTPServer VARCHAR(100)
 DECLARE @AdminEmail VARCHAR(100)
 DECLARE @DomainName VARCHAR(100)
 DECLARE @replyToEmail VARCHAR(100)
 SET @SMTPServer = 'smtp.office365.com'
 SET @AdminEmail = 'sql-alerts@company.com'
 SET @DomainName = '@company.com'
 SET @replyToEmail = 'SQL-Alerts@company.com'
 --Change Global Configuration Settings
 exec sp_configure 'show advanced options', 1
 exec sp_configure 'Database Mail XPs', 1
 exec sp_configure 'Agent XPs',1
 RECONFIGURE WITH OVERRIDE
 --Database Mail Configuration
 --This will setup database mail. It does not require the SQL Server service to be restarted.
 --At the end of the script, it will send a Test Mail.
 declare @servername varchar(100)
 declare @email_address varchar(100)
 declare @display_name varchar(100)
 declare @testmsg varchar(100)
 set @servername = replace(@@servername,'','_')
 set @email_address = 'db_mail@company.com'
 set @display_name = 'Database Mail'
 set @testmsg = 'Test from ' + @servername
 IF EXISTS(SELECT * from msdb.dbo.sysmail_profile)
 PRINT 'DB mail already configured'
 ELSE
 BEGIN
 --Create database mail account.
 exec msdb.dbo.sysmail_add_account_sp
 @Account_name = 'SMTP_database_mail'
 , @description = 'Primary database alerts profile'
 , @email_address = @email_address
 , @replyto_address = @replyToEmail
 , @display_name = @display_name
 , @mailserver_name = @SMTPServer
 ,@mailserver_type = 'SMTP'
 ,@port = 587
 ,@username = 'sql-alerts@company.com'
 ,@password = 'Password here'
 ,@enable_ssl = 1
 --Create global mail profile.
 exec msdb.dbo.sysmail_add_profile_sp
 @profile_name = 'DBA_Profile'
 , @description = 'Default profile for DBA use'
 --Add the account to the profile.
 exec msdb.dbo.sysmail_add_profileaccount_sp
 @profile_name = 'DBA_Profile'
 , @Account_name = 'SMTP_database_mail'
 , @sequence_number=1
 --grant access to the profile to all users in the msdb database
 use msdb
 exec msdb.dbo.sysmail_add_principalprofile_sp
 @profile_name = 'DBA_Profile'
 , @principal_name = 'public'
 , @is_default = 1
 END
 --send a test message.
 exec msdb..sp_send_dbmail
 @profile_name = 'DBA_Profile',
 @recipients = @AdminEmail,
 @subject = @testmsg,
 @body = @testmsg

DB mail profile creation using powershell (Alternative method using dbatools):

$account = New-DbaDbMailAccount -SqlInstance 'SQLSERVER01' -Name 'SMTP_database_mail' -EmailAddress sql-alerts@company.com -MailServer smtp.office365.com -description 'Primary database alerts profile' -ReplyToAddress SQL-Alerts@company.com
 $profile = New-DbaDbMailProfile -SqlInstance 'SQLSERVER01' -Name 'DBA profile' -MailAccountName 'SMTP_database_mail'

7. Enable SQL Agent notification: Once database mail is setup, you now can enable SQL Agent notifications. To take effect, this may require the SQL Agent service to be restarted. In order to sent out email from SQL Agent, you need to configure the alert system. The script below will do this. It uses the extended stored proc “xp_instance_regwrite” to write two values to the registry. The first setting just tell the SQL Agent Alert system to use Database mail as the email option and the second setting tells the SQL Agent Alert system which mail profile to set.

USE [msdb]
 EXEC msdb.dbo.sp_set_sqlagent_properties
 @email_save_in_sent_folder=1
 EXEC master.dbo.xp_instance_regwrite
 N'HKEY_LOCAL_MACHINE'
 , N'SOFTWAREMicrosoftMSSQLServerSQLServerAgent'
 , N'UseDatabaseMail'
 , N'REG_DWORD'
 , 1
 EXEC master.dbo.xp_instance_regwrite
 N'HKEY_LOCAL_MACHINE'
 , N'SOFTWAREMicrosoftMSSQLServerSQLServerAgent'
 , N'DatabaseMailProfile'
 , N'REG_SZ'
 , N'DBA_Profile'

8. Setup Email Operator

USE [msdb]
 GO
 EXEC msdb.dbo.sp_add_operator @name=N'DBA',
 @enabled=1,
 @weekday_pager_start_time=90000,
 @weekday_pager_end_time=180000,
 @saturday_pager_start_time=90000,
 @saturday_pager_end_time=180000,
 @sunday_pager_start_time=90000,
 @sunday_pager_end_time=180000,
 @pager_days=0,
 @email_address=N'SQL-Alerts@company.com',
 @category_name=N'[Uncategorized]'
 GO

9. Setup Email alerts to alert upon any corruption, data or log file being Full and other errors

USE [msdb]
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Database Filegroup Full',
 @message_id=1105,
 @severity=0,
 @enabled=1,
 @delay_between_responses=300,
 @include_event_description_in=1,
 @category_name=N'[Uncategorized]',
 @job_id=N'00000000-0000-0000-0000-000000000000'
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Database TRNLog Full',
 @message_id=9002,
 @severity=0,
 @enabled=1,
 @delay_between_responses=300,
 @include_event_description_in=1,
 @category_name=N'[Uncategorized]',
 @job_id=N'00000000-0000-0000-0000-000000000000'
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Demo: Sev. 14',
 @message_id=0,
 @severity=14,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @category_name=N'[Uncategorized]',
 @job_id=N'00000000-0000-0000-0000-000000000000'
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Demo: Sev. 19',
 @message_id=0,
 @severity=19,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @category_name=N'[Uncategorized]',
 @job_id=N'00000000-0000-0000-0000-000000000000'
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Demo: Sev. 20',
 @message_id=0,
 @severity=20,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @category_name=N'[Uncategorized]',
 @job_id=N'00000000-0000-0000-0000-000000000000'
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Demo: Sev. 21',
 @message_id=0,
 @severity=21,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @category_name=N'[Uncategorized]',
 @job_id=N'00000000-0000-0000-0000-000000000000'
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Demo: Sev. 22',
 @message_id=0,
 @severity=22,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @category_name=N'[Uncategorized]',
 @job_id=N'00000000-0000-0000-0000-000000000000'
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Demo: Sev. 23',
 @message_id=0,
 @severity=23,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @category_name=N'[Uncategorized]',
 @job_id=N'00000000-0000-0000-0000-000000000000'
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Demo: Sev. 24',
 @message_id=0,
 @severity=24,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @category_name=N'[Uncategorized]',
 @job_id=N'00000000-0000-0000-0000-000000000000'
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Demo: Sev. 25',
 @message_id=0,
 @severity=25,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @category_name=N'[Uncategorized]',
 @job_id=N'00000000-0000-0000-0000-000000000000'
 GO
 USE [msdb]
 GO
 EXEC msdb.dbo.sp_add_operator @name=N'DBA',
 @enabled=1,
 @pager_days=0,
 @email_address=N'SQL-Alerts@company.com',
 @pager_address=N''
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Database Filegroup Full', @operator_name=N'DBA', @notification_method = 1
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Database TRNLog Full', @operator_name=N'DBA', @notification_method = 1
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Demo: Sev. 14', @operator_name=N'DBA', @notification_method = 1
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Demo: Sev. 19', @operator_name=N'DBA', @notification_method = 1
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Demo: Sev. 20', @operator_name=N'DBA', @notification_method = 1
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Demo: Sev. 21', @operator_name=N'DBA', @notification_method = 1
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Demo: Sev. 22', @operator_name=N'DBA', @notification_method = 1
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Demo: Sev. 23', @operator_name=N'DBA', @notification_method = 1
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Demo: Sev. 24', @operator_name=N'DBA', @notification_method = 1
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Demo: Sev. 25', @operator_name=N'DBA', @notification_method = 1
 GO
%d bloggers like this: