SQL Server Architecture
Disk Configuration |
|
Was asked what type of Read Policy and Write Policy the disks needed in a new SQL Server that was being built . I had no idea.
What I found is that Read Policy should be Adaptive Read Ahead. Adaptive Read Ahead will read ahead when using sequential sectors and turn off when random sectors.
Write-Policy depends on what type of files will be on the disk. Write-Through is recommended for TLogs being that it I/O completion is signaled when the data is written to disk. Write-Back caches the data which can be a problem in case of a power outage. You can see how this would be a problem with TLogs. TLogs need to be intact for recovery. Here is an article that I found that will give you much more detail.
http://windowsitpro.com/windows/raid-performance-configuration
What I found is that Read Policy should be Adaptive Read Ahead. Adaptive Read Ahead will read ahead when using sequential sectors and turn off when random sectors.
Write-Policy depends on what type of files will be on the disk. Write-Through is recommended for TLogs being that it I/O completion is signaled when the data is written to disk. Write-Back caches the data which can be a problem in case of a power outage. You can see how this would be a problem with TLogs. TLogs need to be intact for recovery. Here is an article that I found that will give you much more detail.
http://windowsitpro.com/windows/raid-performance-configuration
Change SQL Server Collation |
|
An application team asked to change the collation of the server after the install was complete. Let's just say it caused some frustration on my part. So here are the steps.
Open up the CMD prompt and navigate to the directory where the SetUp.exe file is located. If you keep your installation media on a different server you will need to map that location to a drive. From that directory run this command
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=<name of sys admin account> /SAPWD=<SA Password> /SQLCOLLATION=Latin1_General_CI_AS
Open up the CMD prompt and navigate to the directory where the SetUp.exe file is located. If you keep your installation media on a different server you will need to map that location to a drive. From that directory run this command
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=<name of sys admin account> /SAPWD=<SA Password> /SQLCOLLATION=Latin1_General_CI_AS
SQL Server Edition Info |
|
People will ask me the difference between Standard and Enterprise. I try not to store that in the memory banks. So he is the Microsoft link to that info.
You don't get much with Standard any more.
http://msdn.microsoft.com/en-us/library/cc645993.aspx
You don't get much with Standard any more.
http://msdn.microsoft.com/en-us/library/cc645993.aspx
Capping the Size of Your Data Files |
|
Had to cap the size of 30 database files. Didn't want to type all that. Here is the code to generate the Alter Database statement. It also adds a GB to the file when setting the MAXSIZE parameter.
SELECT DB_NAME(database_id) ,
name ,
physical_name ,
size * 8 / 1024 / 1024.0 AS DB_File_Size_GB ,
CASE max_size
WHEN -1 THEN 'UNLIMITED'
ELSE STR(max_size * 8.0 / 1024 / 1024, 10, 2)
END AS MAX_SIZE_GB ,
CASE WHEN is_percent_growth = 0 THEN STR(growth * 8.0 / 1024, 10, 1)
WHEN is_percent_growth = 1 THEN STR(growth, 10, 0) + '%'
END AS Growth ,
is_percent_growth ,
'alter database ' + CAST(DB_NAME(database_id) AS VARCHAR(20))
+ CHAR(13) + 'modify file ' + CHAR(13) + '(name = ' + name + ','
+ CHAR(13) + 'maxsize = ' + LTRIM(STR(( size * 8 / 1024 / 1024.0 + 1),10, 0)) + ' GB)' + CHAR(13)
+ '------' + CHAR(10)
FROM sys.master_files
SELECT DB_NAME(database_id) ,
name ,
physical_name ,
size * 8 / 1024 / 1024.0 AS DB_File_Size_GB ,
CASE max_size
WHEN -1 THEN 'UNLIMITED'
ELSE STR(max_size * 8.0 / 1024 / 1024, 10, 2)
END AS MAX_SIZE_GB ,
CASE WHEN is_percent_growth = 0 THEN STR(growth * 8.0 / 1024, 10, 1)
WHEN is_percent_growth = 1 THEN STR(growth, 10, 0) + '%'
END AS Growth ,
is_percent_growth ,
'alter database ' + CAST(DB_NAME(database_id) AS VARCHAR(20))
+ CHAR(13) + 'modify file ' + CHAR(13) + '(name = ' + name + ','
+ CHAR(13) + 'maxsize = ' + LTRIM(STR(( size * 8 / 1024 / 1024.0 + 1),10, 0)) + ' GB)' + CHAR(13)
+ '------' + CHAR(10)
FROM sys.master_files
Recommended Raid Levels for SQL Server |
|
RAID 1 for TLogs (good write, good write but it not as fault tolerant)
RAID 5 for Data Files (good read, poor write)
RAID 10 for TEMPDB (this is the fastest config but most expensive)
http://sqlmag.com/storage/sql-server-storage-best-practices
RAID 5 for Data Files (good read, poor write)
RAID 10 for TEMPDB (this is the fastest config but most expensive)
http://sqlmag.com/storage/sql-server-storage-best-practices
Upgrading an Edition after you installed SQL |
|
So when doing an upgrade we saw that SQL was only using 40 of the 120 logical processors. Found the new server had Enterprise edition and the other servers had Enterprise Core Edition. You are limited to 40 logical processors on Enterprise. Never would have thought that. So I got the install media and went to Maintenance to update the volume licensing key for the Core Edition. Walked through it, and got a warning saying that if you proceed AlwaysOn will be disabled. Holy Crap!!! This server had a 14TB database on it in an AG setup. If that happened that would totally suck. So we had our Microsoft resource on the line and he said just go ahead and upgrade. We did and all worked fine. No dropping of AlwaysOn.
SQL Server config template script |
|
Here is a script I am using at my current employer. If you don't like xp_cmdshell I will forward you to speak with Sean McCown and see what he says. He calls it "security theater".
sp_configure 'show advanced options',1
reconfigure
go
-- based up server having 16GBs
sp_configure 'max server memory (MB)',13926
reconfigure
go
-- using this value based upon conversation with bOb Taylor
sp_configure 'cost threshold for parallelism',75
reconfigure
go
sp_configure 'max degree of parallelism',8
reconfigure
go
--- enable if app uses API calls
sp_configure'optimize for ad hoc workloads',1
reconfigure
go
sp_configure 'remote access',1
reconfigure
go
sp_configure 'xp_cmdshell',1
reconfigure
go
--following steps will require a restart of SQL to take effect
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
, N'Software\Microsoft\MSSQLServer\MSSQLServer'
, N'DefaultData'
, REG_SZ
, N'E:\SQL\Data'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
, N'Software\Microsoft\MSSQLServer\MSSQLServer'
, N'DefaultLog'
, REG_SZ
, N'L:\SQL\Logs'
GO
-- modify tempdb and add files
alter database tempdb
modify file
(name = tempdev,
filename = 't:\tempdb.mdf',
size = 1 GB,
maxsize = unlimited,
filegrowth = 1 GB)
GO
alter database tempdb
add file
(name = tempdev2,
filename = 't:\tempdb2.ndf',
size = 1 GB,
maxsize = unlimited,
filegrowth = 1 GB)
GO
alter database tempdb
add file
(name = tempdev3,
filename = 't:\tempdb3.ndf',
size = 1 GB,
maxsize = unlimited,
filegrowth = 1 GB)
GO
alter database tempdb
add file
(name = tempdev4,
filename = 't:\tempdb4.ndf',
size = 1 GB,
maxsize = unlimited,
filegrowth = 1 GB)
GO
alter database tempdb
add file
(name = tempdev5,
filename = 't:\tempdb5.ndf',
size = 1 GB,
maxsize = unlimited,
filegrowth = 1 GB)
GO
alter database tempdb
add file
(name = tempdev6,
filename = 't:\tempdb6.ndf',
size = 1 GB,
maxsize = unlimited,
filegrowth = 1 GB)
GO
alter database tempdb
add file
(name = tempdev7,
filename = 't:\tempdb7.ndf',
size = 1 GB,
maxsize = unlimited,
filegrowth = 1 GB)
GO
alter database tempdb
add file
(name = tempdev8,
filename = 't:\tempdb8.ndf',
size = 1 GB,
maxsize = unlimited,
filegrowth = 1 GB)
sp_configure 'show advanced options',1
reconfigure
go
-- based up server having 16GBs
sp_configure 'max server memory (MB)',13926
reconfigure
go
-- using this value based upon conversation with bOb Taylor
sp_configure 'cost threshold for parallelism',75
reconfigure
go
sp_configure 'max degree of parallelism',8
reconfigure
go
--- enable if app uses API calls
sp_configure'optimize for ad hoc workloads',1
reconfigure
go
sp_configure 'remote access',1
reconfigure
go
sp_configure 'xp_cmdshell',1
reconfigure
go
--following steps will require a restart of SQL to take effect
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
, N'Software\Microsoft\MSSQLServer\MSSQLServer'
, N'DefaultData'
, REG_SZ
, N'E:\SQL\Data'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
, N'Software\Microsoft\MSSQLServer\MSSQLServer'
, N'DefaultLog'
, REG_SZ
, N'L:\SQL\Logs'
GO
-- modify tempdb and add files
alter database tempdb
modify file
(name = tempdev,
filename = 't:\tempdb.mdf',
size = 1 GB,
maxsize = unlimited,
filegrowth = 1 GB)
GO
alter database tempdb
add file
(name = tempdev2,
filename = 't:\tempdb2.ndf',
size = 1 GB,
maxsize = unlimited,
filegrowth = 1 GB)
GO
alter database tempdb
add file
(name = tempdev3,
filename = 't:\tempdb3.ndf',
size = 1 GB,
maxsize = unlimited,
filegrowth = 1 GB)
GO
alter database tempdb
add file
(name = tempdev4,
filename = 't:\tempdb4.ndf',
size = 1 GB,
maxsize = unlimited,
filegrowth = 1 GB)
GO
alter database tempdb
add file
(name = tempdev5,
filename = 't:\tempdb5.ndf',
size = 1 GB,
maxsize = unlimited,
filegrowth = 1 GB)
GO
alter database tempdb
add file
(name = tempdev6,
filename = 't:\tempdb6.ndf',
size = 1 GB,
maxsize = unlimited,
filegrowth = 1 GB)
GO
alter database tempdb
add file
(name = tempdev7,
filename = 't:\tempdb7.ndf',
size = 1 GB,
maxsize = unlimited,
filegrowth = 1 GB)
GO
alter database tempdb
add file
(name = tempdev8,
filename = 't:\tempdb8.ndf',
size = 1 GB,
maxsize = unlimited,
filegrowth = 1 GB)
Resource Governor |
|
Needed to lay some smack down on some runaway users on my box. So decided to use Resource Govenor. Here are the steps I used.
SELECT * FROM sys.resource_governor_configuration
SELECT * FROM sys.dm_resource_governor_configuration
CREATE RESOURCE POOL Report_Smack_Down;
CREATE WORKLOAD GROUP Smack_Down USING Report_Smack_Down
Alter RESOURCE POOL Report_Smack_Down
WITH (
MAX_CPU_PERCENT = 25,--Specifies the maximum average CPU bandwidth that all requests in resource pool will receive when there is CPU contention
CAP_CPU_PERCENT = 25,--Specifies a hard cap on the CPU bandwidth that all requests in the resource pool will receive.
MIN_CPU_PERCENT = 1, --Specifies the guaranteed average CPU bandwidth for all requests in the resource pool when there is CPU contention
MIN_MEMORY_PERCENT = 1,--Specifies the minimum amount of memory reserved for this resource pool that can not be shared with other resource pools
MAX_MEMORY_PERCENT = 25-- Specifies the total server memory that can be used by requests in this resource pool.
);
ALTER RESOURCE GOVERNOR RECONFIGURE;
--determine how you want to classify connections.
alter FUNCTION dbo.fnSmack_Down_Classifier ()
RETURNS SYSNAME WITH SCHEMABINDING
BEGIN
DECLARE @group VARCHAR(32)
IF SUSER_SNAME() IN ('yo_momma')
SET @group = 'Smack_Down';
ELSE
SET @group = 'default';
RETURN @group
END
GO
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.fnSmack_Down_Classifier)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
--validate the Resource Governor is being used.
SELECT
rpool.name as PoolName,
COALESCE(SUM(rgroup.total_request_count), 0) as TotalRequest,
COALESCE(SUM(rgroup.total_cpu_usage_ms), 0) as TotalCPUinMS,
CASE
WHEN SUM(rgroup.total_request_count) > 0 THEN
SUM(rgroup.total_cpu_usage_ms) / SUM(rgroup.total_request_count)
ELSE
0
END as AvgCPUinMS
FROM
sys.dm_resource_governor_resource_pools AS rpool
LEFT OUTER JOIN
sys.dm_resource_governor_workload_groups AS rgroup
ON
rpool.pool_id = rgroup.pool_id
GROUP BY
rpool.name;
SELECT * FROM sys.resource_governor_configuration
SELECT * FROM sys.dm_resource_governor_configuration
CREATE RESOURCE POOL Report_Smack_Down;
CREATE WORKLOAD GROUP Smack_Down USING Report_Smack_Down
Alter RESOURCE POOL Report_Smack_Down
WITH (
MAX_CPU_PERCENT = 25,--Specifies the maximum average CPU bandwidth that all requests in resource pool will receive when there is CPU contention
CAP_CPU_PERCENT = 25,--Specifies a hard cap on the CPU bandwidth that all requests in the resource pool will receive.
MIN_CPU_PERCENT = 1, --Specifies the guaranteed average CPU bandwidth for all requests in the resource pool when there is CPU contention
MIN_MEMORY_PERCENT = 1,--Specifies the minimum amount of memory reserved for this resource pool that can not be shared with other resource pools
MAX_MEMORY_PERCENT = 25-- Specifies the total server memory that can be used by requests in this resource pool.
);
ALTER RESOURCE GOVERNOR RECONFIGURE;
--determine how you want to classify connections.
alter FUNCTION dbo.fnSmack_Down_Classifier ()
RETURNS SYSNAME WITH SCHEMABINDING
BEGIN
DECLARE @group VARCHAR(32)
IF SUSER_SNAME() IN ('yo_momma')
SET @group = 'Smack_Down';
ELSE
SET @group = 'default';
RETURN @group
END
GO
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.fnSmack_Down_Classifier)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
--validate the Resource Governor is being used.
SELECT
rpool.name as PoolName,
COALESCE(SUM(rgroup.total_request_count), 0) as TotalRequest,
COALESCE(SUM(rgroup.total_cpu_usage_ms), 0) as TotalCPUinMS,
CASE
WHEN SUM(rgroup.total_request_count) > 0 THEN
SUM(rgroup.total_cpu_usage_ms) / SUM(rgroup.total_request_count)
ELSE
0
END as AvgCPUinMS
FROM
sys.dm_resource_governor_resource_pools AS rpool
LEFT OUTER JOIN
sys.dm_resource_governor_workload_groups AS rgroup
ON
rpool.pool_id = rgroup.pool_id
GROUP BY
rpool.name;