I had to post this ! Met Kalen Delaney in SQL PORT EVENT :)

Kalen Delaney with Paulo Condeça on SQL PORT Meeting.

More information about Kalen Delaney @ AboutKalenDelaney .

Disable Lock Escalation in SQL SERVER 2008 or Higher

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.

Run SSIS Packages in SQL SERVER 2005 x64 with Jet4 OleDB connections (eg:excel)

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: 0×80040154. An OLE DB record is available. Source: “Microsoft OLE DB Service Components” Hresult: 0×80040154 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

OPEN report_cursor

FETCH NEXT FROM report_cursor into @ssis_package

WHILE @@fetch_status=0

BEGIN

SET @stmt = @dtexec_path + + @ssis_package + @params

EXEC xp_cmdshell @stmt

FETCH NEXT FROM report_cursor into @ssis_package

END

CLOSE report_cursor

DEALLOCATE report_cursor

Negative spid in SQL SERVER.

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.

OR

select distinct req_transactionUOW from syslockinfo , ignore the 000000′s

kill ‘GUID’ . ( ghost/orphaned transaction ).

MICROSOFT SQL SERVER PERFORMANCE TUNNING

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 /  :

• CPU
  • 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 ).

• WINDOWS
  • 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 ).

• ANTIVIRUS
  • 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 http://netic.wordpress.com/2008/05/05/microsoft-sql-server-2005-partitioning/ .

Linux find command

Linux command to find files / directories .

find . -name "WhatYouWant" -print

SSMS (SQL SERVER MANAGEMENT STUDIO) slow startup – solved

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 .

Simple command to get Linux disk usage by dir

This is a simple and practical command to get disk usage by dir in Linux -> du -sh *

Analyzing SQL SERVER 2005 LONG RUNNING QUERIES

I’m leaving this tip for you to find what exactly is running on sql server.

SELECT r.session_id, s.HOST_NAME, s.PROGRAM_NAME,
s.host_process_id, r.status, r.wait_time,
wait_type, r.wait_resource,
SUBSTRING(qt.text,(r.statement_start_offset/2) +1,
(CASE WHEN r.statement_end_offset = -1

THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2

ELSE r.statement_end_offset

END -r.statement_start_offset)/2)

AS stmt_executing,r.blocking_session_id,
r.cpu_time,r.total_elapsed_time,r.reads,r.writes,
r.logical_reads, r.plan_handle

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt, sys.dm_exec_sessions s

WHERE r.session_id > 50 and r.session_id=s.session_id
ORDER BY r.session_id, s.host_name, s.program_name, r.status

MICROSOFT SQL SERVER 2005 Partitioning (English Version)

SQL SERVER 2005 Partitioning <- Can read a fully formated .doc in here .

Microsoft worked well in the Microsoft SQL Server 2005 (even better in MSSQL Server 2008), bringing a shine to their RDBMS engine and building it to be a real Enterprise Class Product.

I’m not going to write about all the new features of the OLTP Engine, I will focus this article on Database Partition, giving you a real case scenario.

I. Benefits and Limitations of Partitioning (HEAPS/CLUSTERED/NONCLUSTERED).

II. Implementation Methodology.  
 
III. Partitioning Management.  

 
I. Benefits and Limitations of Partitioning (HEAPS/CLUSTERED/NONCLUSTERED): 
 
+ 
 
Data can be accessed through multiple partitions in parallel, this will results in faster DML.

Different partitions can be managed separately.  
 
Management of history more efficient (without having to leave the table).  
 
- 
 
Limit of 1000 Partitions by Table.

 
Inability to use Indexed Views.  
 

II. Implementation Methodology.

I’m going to focus in the most common scenario:

Typically, in a DW environment there is a large Fact Table (a.k.a the Monster).

In this article the Fact Table contains all the banking movements of all accounts within the last 4 years in the MAIN database and the remaining years in a HISTORY database.

In this case, because there is a chronology to follow, I will use the extract day date column of the table as the KEY to our Partition Strategy.

In this situation, I recommend the creation of a Partitioned Clustered Index because typically all access to the fact table always has the date in the SARG.  

The fact table will be partitioned by month. 
 

Technical approach:  

Storage Arquitecture – 3 RAID-5 Partition (2TB each), 1 RAID 01 for Transaction Log and 1 more RAID 01 for Non-Clustered indexes.

First off all you will need to develop the Partition Function.

The Partition Function defines the values which the partition scheme will bind with the Filegroups.

Our boundary will be the date, and right range(boundary right aligned).

CREATE PARTITION FUNCTION PFFactTableMonthly (SMALLDATETIME)

AS RANGE RIGHT

FOR VALUES

     (

            ’2007-01-01′,’2007-02-01′,’2007-03-01′,’2007-04-01′,’2007-05-01′,

            ’2007-06-01′,’2007-07-01′,’2007-08-01′,’2007-09-01′,’2007-10-01′,

            ’2007-11-01′,’2007-12-01′,’2008-01-01′,’2008-02-01′,’2008-03-01′,

            ’2008-04-01′,’2008-05-01′,’2008-06-01′,’2008-07-01′,’2008-08-01′,

            ’2008-09-01′,’2008-10-01′,’2008-11-01′, ’2008-12-01′,’2009-01-01′,

            ’2009-02-01′

            )

 
Create the Datafiles and assign each file to a differente Filegroup. In this situation 27 Datafiles/Filegroups will be created.

Now that the Filegroups are created, assign them to a Partition Scheme that acts like a ROUTER, because it will forward the data to a specified path.

The Partition Scheme will use the Partition Function to link the logical layer to the physical layer.

CREATE PARTITION SCHEME PSFactTableMonthly

AS PARTITION PFFactTableMonthly

TO (

      FG_FactTable_BASE,FG_FactTable_200701,FG_FactTable_200702,

      FG_FactTable_200703,FG_FactTable_200704,FG_FactTable_200705,

      FG_FactTable_200706,FG_FactTable_200707,FG_FactTable_200708,

      FG_FactTable_200709,FG_FactTable_200710,FG_FactTable_200711,

      FG_FactTable_200712,FG_FactTable_200801,FG_FactTable_200802,

      FG_FactTable_200803,FG_FactTable_200804,FG_FactTable_200805,

      FG_FactTable_200806,FG_FactTable_200807,FG_FactTable_200808,

      FG_FactTable_200809,FG_FactTable_200810,FG_FactTable_200811,

      FG_FactTable_200812,FG_FactTable_200901,FG_FactTable_200902

      )

Now that we have the Partition Scheme, just create the Clustered Index on the Fact table with a FILLFACTOR 90% with PAD_INDEX on.

CREATE CLUSTERED INDEX [IDXC_FACTTABLE] ON MIS.FACTTABLE

      (

      datecolumn

      ) 

      WITH( PAD_INDEX = ON, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF,

      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  

on PSFactTableMonthly (DATECOLUMN) 

III. Partitioning Management. 

Now, that our table is partitioned, you are wondering, what will happen if there is an insert that has a date >= ’2009-03-01′, what will happen is that all the data will be placed in the FG_FactTable_200902 s because the data is right aligned as specified when we created the Partition Function.

To maintain the data equally distributed between Filegroups, you must plan the future growth of you Database and continue adding Filegroups to the Partition Scheme and continuing to add dates to the Partition Function.

Add a FG to the Partition Scheme:

ALTER PARTITION PSFactTableMonthly SCHEME

NEXT USED [FG_FactTable_200903].  

Add a new boundary to the Partition Function:

ALTER PARTITION FUNCTION PFFactTableMonthly ()

SPLIT RANGE (’20090301′)  

Know the detail about the Partition Function :

SELECT * FROM sys.partition_functions

WHERE name = ‘PFFactTableMonthly’ 

Show the boundaries of the Partition Function :

SELECT a.name, b.* FROM sys.partition_functions a, sys.partition_range_values b

WHERE a.function_id = b.function_id

and a.name = ‘PFFactTableMonthly’

Show Allocation Units

SELECT object_name(object_id) AS name,

    partition_id, partition_number AS pnum, rows,

    allocation_unit_id AS au_id, type_desc as page_type_desc,

    total_pages AS pages

FROM sys.partitions p JOIN sys.allocation_units a

   ON p.partition_id = a.container_id

WHERE object_id=object_id(‘schema.FactTable) 
 
 
 
 
 

From my point of view this is the best statement there is available to have a neat overview of your partitioned table, because it shows the INDEX_ID, Partition_Number, Filegroup that is associated the respective Partition_Number, ROWS, PAGES and the date that is associated with the FileGroup. 

SELECT OBJECT_NAME(i.object_id) as Object_Name,

            i.index_id AS Index_ID,

         p.partition_number,

•  
  fg.name AS Filegroup_Name,

•  
  rows,

•  
  au.total_pages,

         CASE boundary_value_on_right

             WHEN 1 THEN ‘less than’

•  
        ELSE ‘less than or equal to’ END as ‘comparison’, value

FROM sys.partitions p JOIN sys.indexes i

      ON p.object_id = i.object_id and p.index_id = i.index_id

       JOIN sys.partition_schemes ps

                ON ps.data_space_id = i.data_space_id

       JOIN sys.partition_functions f

                   ON f.function_id = ps.function_id

       LEFT JOIN sys.partition_range_values rv

     ON f.function_id = rv.function_id

                    AND p.partition_number = rv.boundary_id

     JOIN sys.destination_data_spaces dds

             ON dds.partition_scheme_id = ps.data_space_id

                  AND dds.destination_id = p.partition_number

     JOIN sys.filegroups fg

                ON dds.data_space_id = fg.data_space_id

     JOIN (SELECT container_id, sum(total_pages) as total_pages

                     FROM sys.allocation_units

                     GROUP BY container_id) AS au

                ON au.container_id = p.partition_id

     WHERE OBJECT_NAME(i.object_id) = ‘FactTable’ 

Some useful Tips :

• Always use a rule on DataFiles/FileGroup nomenclature, to make it easier to understand the dependencies.
• When partitioning tables, don’t randomize the creation of the DataFiles, for example put the DF1 on StorageA, DF2 on StorageB, DF3 on StorageC, DF4 on StorageA,DF4 on StorageB…
• For best performance align your non-clustered indexes with the partition.
• After the end of the previous month, rebuild the respective previous partition number.
• Be creative !

Paulo Condeça. paulo at citen dot net