Consulting 101-105, SQL Server Performance Tuning by Pinal Dave

Consulting 101 – Why Do I Never Take Control of Computers Remotely?

Consulting 102 – Why Do I Give 100% Guarantee of My Service?

Consulting 103 – Why Do I Assure SQL Server Performance Optimization in 4 Hours?

Consulting 104 – Why Do I Give All the Performance Tuning Scripts to My Customers?

Consulting 105 – Why Don’t I Want My Customers to Return for the Same Problem?

Advertisements

SQL Server function to return the “Xrd” day of a month and year.

USE [Tools]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- select dbo.XrdDay(4,'thursday', 'november', 2018)	-- Thanksgiving
-- select dbo.XrdDay(4,'thursday', 'november', 2019)	-- Thanksgiving
-- select dbo.XrdDay(4,'thursday', 'november', 2020)	-- Thanksgiving

-- Must have the day of week and the name of month spelled correctly.
alter function [dbo].[XrdDay](
	@xrd int				--3rd, 4th, 5th, etc.
	,@dayname varchar(8)	--'saturday'
	,@month varchar(10)		--'february'
	,@year smallint			--year
)
returns date
as 
begin
	-- declare @xrd int = 3; declare @dayname varchar(8) = 'monday'; declare @month varchar(10) = 'september'; declare @year smallint = 2019;

	declare @Result date=  @month + ' ' + convert(varchar(64),(@xrd*7) - 6) + ',' + convert(varchar(64), @year);	-- get Sunday.
	--select @Result, datename(weekday, @Result);

	--I'm fairly sure this is inefficient, but I can't seem to recall of another way around it.
	--to make it better, we need to calc @sunday+int==dayname in one go, not this while loop.
	while datename(weekday, @Result) collate SQL_Latin1_General_CP1_CI_AS != @dayname set @Result = dateadd( day, 1, @Result );
	--select @Result, datename(weekday, @Result);

	return @Result;
end;

TempDB Best Practices

TempDB Best Practices

  1. TempDB should have as many data files as there are cores available to SQL Server, but only up to 8 data files. Too many TempDB data files can cause contention. Recommendation: Create 8 files and test from there on.
  2. TempDB should only have 1 log file. Extra log files will improve performance as they are written to sequentially.
  3. The data files for TempDB should all be the same size.
  4. TempDB data files and TempDB log files should be on separate disks.
  5. TempDB files should be on the fastest disks.