SQL Server & Network Packet Size

I've seen a number of references to packet size on my research into optimising SQL & SSIS.
The default packet size is 512 Bytes and the maximum SQL server supports is 32767.

So, the question is, " Why can't i just change it ? "

Hardware support is the answer
Connecting clients and the network switches need to support the packet size (e.g. 9000 for Jumbo frames)
Network Cards need to support the speed (and for that support to be enabled via the drivers in Windows)

Your OS also needs to support it. In the case of Hyper-V (pre Windows 2008 R2), the virtual NIC cannot support Jumbo frames.

If your environment is suitable however, here's the configuration code to do it >

EXEC SP_CONFIGURE 'show advanced option', '1'; 
RECONFIGURE;
EXEC SP_CONFIGURE;

EXEC SP_CONFIGURE 'network packet size (B)', '9000';
RECONFIGURE WITH OVERRIDE;
RECONFIGURE;

EXEC SP_CONFIGURE

Link : Gigabit Ethernet Jumbo Frames

Database Settings : Forced Parameterization

I originally looked at Forced Parameterization here.

By default Parameterization is SIMPLE, it can be set to FORCED however (in sql 2005+).

My reason for this is that developing stored procedures apparently isn't feasible for our current project. The web developers don't have the skills to maintain them and don't want to learn them. The 'increase in development time' is apparently not worth it. Similarly there is no love for table functions or even for sargable sql. Sql is (and will remain) embedded in the web application :(

With the speed at which decisions are made and the product changes direction I am reluctantly letting it go. I don't agree because (as a dba) I'd like to encourage every opportunity to optimise the database environment. Balancing my DBA role, SSIS development tasks and aspirations to persue the BI track, I have enough to think of.

Rant aside, Forced Parameterization will allow the creation of less query plans by parameterizing values within submitted sql queries. Later queries will be compared in their parameterized form against those in the plan cache.

For example;

With Parameterization = SIMPLE, the following plans could all exist >

SELECT Forename FROM Person.Contact WHERE ID = 1

SELECT Forename FROM Person.Contact WHERE ID = 3

SELECT Forename FROM Person.Contact WHERE ID = 6

SELECT Forename FROM Person.Contact WHERE ID = 13

With Parameterization = FORCED, only the 1 plan would be stored >

SELECT Forename FROM Person.Contact WHERE ID = (@P1)

I covered Viewing and clearing the plan cache here. It's a useful post for debugging query caching.

Setting Parameterization to FORCED >

ALTER DATABASE AdventureWorks SET PARAMETERIZATION FORCED

Returning Parameterization to SIMPLE >

ALTER DATABASE AdventureWorks SET PARAMETERIZATION SIMPLE

Setting all dbs on a server to FORCED parameterization >

exec sp_msforeachdb @command1= 'ALTER DATABASE ? SET PARAMETERIZATION FORCED

Links :
SQL Authority
How SQL Server 2005 "Forced Parameterization" cut ad-hoc query CPU usage by 85%
Strictly Software : Optimizing a query with Forced Parameterization

SQL Server blocked access to procedure 'sys.sp_OACreate'

Executed as user: Domain\SQLServiceAgent. SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.

The message tells us exactly what to do, use sp_configure -

sp_configure 'show advanced options', 1
GO 
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1
GO 
RECONFIGURE;
GO 
sp_configure 'show advanced options', 1
GO 
RECONFIGURE;

SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

Was faced with this error today when configuring a new server,
" SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified "

So, after checking that your Browser service is up and running, is it accessible?  By this I mean, not blocked by a firewall, ISA etc,
PortQry is a free tool to help determine this (a bit more advanced than telneting)

You use it like this >

portqry.exe -n yourservername -p UDP -e 1434

Links :

Download PortQry version 2
MSDN blogs

Affinity & Affinity I/O - Some notes

Processor Affinity settings bind SQL Server activity to specific processors
If you are unlucky enough to be sharing a server with another application, this would be where to prevent  SQL using all CPUs.

By default, the affinity settings are 'automatic' i.e. use all processors.

 
Affinity mask (Processor Affinity) - Controls processors as this can be degrative to performance.

Affinity I/O mask (I/O Affinity) - controls server I/O (you nominate processors to be used for i/o activity)

Never mark the same processors for affinity mask and affinity i/o mask. (see Technet link for details)

Update 10/10/2010 :
Because changing I/O Affinity requires a restart of the SQL Server Service, The radio buttons 'Configured values' and 'Running values' at the bottom of the Affinity screen will show differences in configuration until the restart occurs.

Technet : Affinity Mask Option

John Daskalakis : SQL Server 2008 and Processors

Group Policy to Enable Instant File Initialization

If you run SQL Server using Network Service accounts, you'll need the following location in Group Policy to allow Instant File Initialization.

Grant 'Perform Volume Maintainence Tasks' to the SQL Server service account as shown below -








http://www.sqlskills.com/blogs/Kimberly/post/Instant-Initialization-What-Why-and-How.aspx

Disk Formatting & Partition Alignment for SQL Server

Cluster Size

SQL Server reads and writes in 64K blocks.

Therefore , format drives you provision for SQL data & logs with a cluster size of 64K.

Simple Really :)

Partition Alignment

Before you format the drives however, there is the matter of Partition Alignment.

The following formulae are published by Microsoft to help determine partition alignment.

Partition_Offset / Stripe_Unit_Size = integer (this is the most important)


Stripe_Unit_Size / File_Allocation_Unit_Size   = integer

In the absence of the stripe size info, the alignment should at the very least be changed from it’s 32K default when establishing the partition.

If we can’t find Stripe size information, the best we can do is use an alignment value of 1024K (1MB) which is common to many SANs and is compatible with the 64K Cluster size.

NB : Alignment is handled automatically on Windows 2008.

IT Knowledgebase : How much performance are you losing my not aligning your drives?
MSDN : Disk Partition Alignment Best Practices for SQL Server


To set alignment >

C:\>DISKPART

Microsoft DiskPart version 5.1.3565

Copyright (C) 1999-2003 Microsoft Corporation.
On computer: DEV008

DISKPART>SELECT DISK 1

Disk 1 is now the selected disk

DISKPART>CREATE PARTITION PRIMARY ALIGN=1024

DiskPart succeeded in creating the specified partition


You'll then want to format the drive in a 64K cluster size. 

23/07/2009 Update - Brilliant post on Disk Alignment in Windows 2008

Moving TempDB / Splitting TempDB to multiple files

Script as below.
You need to restart SQL for tempdb to be recreated in the new locations...

USE master
GO

-- move tempdb data
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev,
FILEGROWTH = 10% ,
MAXSIZE = UNLIMITED,
SIZE=1000MB ,
FILENAME = 'D:\databases\tempdb_1.mdf')
GO

-- move tempdb log
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, SIZE=1, FILENAME = 'D:\databases\templog.ldf')
GO

-- additional processor? split tempdb into equal filesizes, 1 per processor...

ALTER DATABASE tempdb ADD FILE
(NAME = tempdev_2,
FILEGROWTH = 10% ,
MAXSIZE = UNLIMITED,
SIZE=1000MB ,
FILENAME = 'D:\databases\tempdb_2.mdf')
GO

Configuring Certificate for MSX (Master/Target Server Environment)

The certificates allow the SQL Servers to utilize SSL (required for the master target environment) and also a much more secure way of protecting our SQL login information which is transmitted in clear text across the network from our internet facing servers. Enabling SSL allows us to better protect these logins because they would be encrypted."
1) Install Certificate. - Import .pfx file provided by operations onto the sql server.
#1.1 - double click pfx file to begin certificate import. wizard will confirm file name. click 'next' to confirm
#1.2 - provide private key password, click next
#1.3 - select 'place certificates in following store' , select 'personal' , OK
#1.4 - click next on confirmation page, 'hopefully recieve the message - 'the import was successful'

2) Associate the certificate with sql instance
#2.1 - start > run > regedit {enter]
#2.2 - Use Regedit to navigate the registry to >
\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\\SQLServerAgent\

Set MsxEncryptChannelOptions(REG_DWORD) to 2


Useful Links

Troubleshooting MSX >
http://blogs.ameriteach.com/chris-randall/2007/8/23/sql-server-2005-troubleshooting-multi-server-administration-.html

setting encryption options on target servers >
http://msdn.microsoft.com/en-us/library/ms365379.aspx

configuring certificate for use by ssl (by mmc) >
http://support.microsoft.com/kb/316898

configuring certificate for use by ssl (commands) >
http://msdn.microsoft.com/en-us/library/ms186362.aspx

Limit SQL Server memory

Set memory available to a sql instance
In this example we do so to 500MB (this was for testing instances on local pc)

sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'max server memory', 500
go
reconfigure
go

SQL 2005 : Enabling XP_cmdshell

This is considered a big security 'no no', with access to external functionality preferred by developing a CLR assembly. With cmdshell enabled, users can effectively run any command :(

EXEC sp_configure 'show advanced option', '1'
RECONFIGURE;
GO

EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE;

-- remember to turn advanced options off again!
EXEC sp_configure 'show advanced option', '0'
RECONFIGURE
GO