More information about Kalen Delaney @ AboutKalenDelaney .
For who don’t know, it is know possible to remove Lock Escalation, and this is something that be “granularly” done to a table on SQL SERVER 2008 or Higher without the need to use Trace Flags.
Sample sintax ALTER TABLE table_name_goes_gere SET (LOCK_ESCALATION=DISABLE) .
— PT —
Para quem não sabe, já é possível remover “Lock Escalation”, de uma forma mais “granular” em uma tabela no SQL SERVER 2008 ou superior, sem ser preciso usar Trace Flags.
Exemplo de sintaxe: ALTER TABLE table_name_goes_gere SET (LOCK_ESCALATION = DISABLE).
Find bellow the Lock Escalation Thresholds ( from Books on line ).
Lock escalation is triggered when lock escalation is not disabled on the table by using the ALTER TABLE SET LOCK_ESCALATION option, and when either of the following conditions exists:
A single Transact-SQL statement acquires at least 5,000 locks on a single nonpartitioned table or index.
A single Transact-SQL statement acquires at least 5,000 locks on a single partition of a partitioned table and the ALTER TABLE SET LOCK_ESCALATION option is set to AUTO.
The number of locks in an instance of the Database Engine exceeds memory or configuration thresholds.
If locks cannot be escalated because of lock conflicts, the Database Engine periodically triggers lock escalation at every 1,250 new locks acquired.
To sum up,
I had to solve a situation in a client that wasn’t able to run a couple of SSIS package with SQLSERVER AGENT but they were running ok in BIDS.
Since SQL SERVER was running on a x64 environment, and the SSIS package was connecting to a excel files ( JET 4 OleDB ), it was throwing the following error :
DestinationConnectionExcel” Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: “Microsoft OLE DB Service Components” Hresult: 0x80040154 Description: “Class not registered”. End Error Error: 2011-01-04 12:38:36.75 Code: 0xC00291EC Source: Preparation SQL Task Execute SQL Task Description: Failed to acquire connection “DestinationConnectionExcel”. Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:38:35 Finished: 12:38:36 Elapsed: 0.765 seconds. The package execution failed. The step failed.
To solve, just create a simple process to call DTEXEC @ x86 DTS bin.
I had to make some “special t-sql” to be able to run the SSIS packages because there are no JET drivers for x64 ( connect to excel ). I’m sharing this with you,
-- Since this there is no Jet Driver for x64, processes must be run using x86 DTEXEC
DECLARE @dtexec_path nvarchar(100)
DECLARE @params nvarchar(150)
DECLARE @ssis_package varchar(100)
DECLARE @stmt nvarchar(350)
SET @dtexec_path='D:\mssql\sql2k5\"tools (x86)"\90\DTS\Binn\dtexec /SQL "\'
SET @params ='" /SERVER "DbInstanceName" /DECRYPT PasswordGoesHere /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E'
CREATE TABLE #report_to_execute ( report_path varchar(100) )
INSERT INTO #report_to_execute (report_path)
SELECT 'SSIS_PACKAGE_1' UNION ALL SELECT 'SSIS_PACKAGE_2' UNION ALL SELECT 'SSIS_PACKAGE_3'
DECLARE report_cursor CURSOR
FOR select report_path from #report_to_execute
FETCH NEXT FROM report_cursor into @ssis_package
SET @stmt = @dtexec_path + + @ssis_package + @params
EXEC xp_cmdshell @stmt
FETCH NEXT FROM report_cursor into @ssis_package
When you have a negative spid ( ex: -2) in SQL SERVER, it is related with Microsoft Distributed Transaction Coordinator (MSDTC).
You can do it from 2 different ways:
Outside SQL SERVER, in component services > DTC > Transactions > rollback the transaction.
select distinct req_transactionUOW from syslockinfo , ignore the 000000’s
kill ‘GUID’ . ( ghost/orphaned transaction ).
I’m writing this article to help everyone who whats “more juice” from a MS SQL SERVER INFRASTRUCTURE .
Instead of writing lots of articles i will update this post every week. I will mix lots of concepts in this post, ranging from Physical Infrastructure/ Operating System/ SQL SERVER PARAMETERS / T-SQL .
I will write a more detailed article related with SQL SERVER PERFORMANCE TUNING, but for now, i will just suggest the following tips for a better SQL SERVER ARCHITECTURE / :
- DISABLE HYPERTHREADING – This will give better performance during high workloads, you can read this great article from Slava Oks’s ( http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx ).
- Configure the NIC (Network Interface Card) for “MAXIMIZE DATA THROUGHPUT FOR NETWORK APPLICATIONS” instead of “MAXIMIZE DATA THROUGHPUT FOR FILESHARING“, this will give an overall boost of 10-20 % .
- Don’t forget that windows server is configured as a file-server out-of-the-box !
- Configure Windows Server to favor BACKGROUND SERVICES and PROGRAMS !
- STORAGE INFRASTRUCTURE
- Use different volumes for SYSTEM & PAGEFILE (KEEP A FIXED SIZE FOR SWAP).
- Avoid RAID-5 , use and abuse from RAID-10, I understand that this is impossible mos of the timesw, because of the costs that are related with this type of configuration, performance has a price, and this one is quite expensive.
- Change the registry key HKLM\CurrentControlSet\ Control\FileSystem\ContigFileAllocSize to 64, this will ajust the minimum contiguous file allocation to 64KB.
- USE MULTIPLE LUNs / FILEGROUPS / DATAFILES.
- USE DISKPAR to align LUN partition with the underlying disk clusters.
- ENABLE MPIO – Multi-Path IO .
- FORMAT the DATA Drives with NTFS 64kb block size ( SQL Server pages are 8192 bytes, so the default NTFS block size (4096) reads only ½ a page and effectively doubles the number of I/O operations. Also consider formatting the DATA drives in 64KB blocks, since SQL Server commonly does an eight page read-ahead to improve performance)
- FORMAT LOG Drives with default block size ( 4kb ).
- Make sure the antivirus doesn’t scan SQL SERVER related processes and *mdf/*ldf/*ndf*/*mdb .
- SQL SERVER PARAMETERS
- Pre-Allocate memory for SQL SERVER .
- Consider changing the lock table for more than 5000 minimum locks ( default ) if the application makes many lock requests, this is particularly true in Biztalk Environments . A simple way to achieve this magic number, is to monitor the lock requests / second and give more 15 % than the maximum value observed .
- Optimize TEMPDB, create one datafile for each processor core and put it on a separate LUNs.
- Consider reducing MAXDOP ( MAX DEGREE OF PARALLELISM ) if you having lots of CX-PACKET WAITS.
- SQL SERVER PARTITIONING
- This is a great feature available only in Enterprise Edition and is a great for VLBD, you can read an article that i wrote for sql-server-performance.com ( http://www.sql-server-performance.com/articles/reporting/Data_Warehouse_Partition_Strategies_p1.aspx ), there is also a portuguese version of it in https://netic.wordpress.com/2008/05/05/microsoft-sql-server-2005-partitioning/ .
Linux command to find files / directories .
find . -name "WhatYouWant" -print
SSMS (SQL SERVER MANAGEMENT STUDIO) slow startup and how to optimize SSMS startup time :
To sump up, everytime you open SSMS it will check some certs, so, do the following if you want faster load times :
Open IE > TOOLS > OPTIONS > ADVANCED > UNCHECK PUBLISHER CERTIFICATE REVOCATION .