Heap Files

What is a heap? Quite simply a heap is a table without a clustered index. Data within a table is grouped together into allocation units based on the column data types. Data within these allocation units are stored in pages. Each page is of size 8KB. Group of 8 pages (64K) is stored together and they are referred as Extent. Depending on the data types in the heap, each heap structure will have one or more allocation units to store and manage the data for a specific partition. At a minimum, each heap will have one IN_ROW_DATA allocation unit per partition. If the table conatains any LOB data columns it will also have one LOB_DATA allocation unit per partition, and if the table conatins any variable length columsn that exceed 8k (8060 bytes) it will also have one ROW_OVERFLOW_DATA allocation unit per partition. To recap, if the data of table is not physically stored in a particular order i.e. (clustered index) it is known as a Heap Structure.

Heaps can be identified by querying the sys.partitions table for index_id=0. Heaps have one row in sys.partitions, with index_id = 0 for each partition used by the heap. By default, a heap has a single partition, however when a heap has multiple partitions, each partition has a heap structure that contains the data for that specific partition. For example, if a heap has three partitions, there are three heap structures.

The data pages and the rows within a heap are not in any specific order and are not linked. The only logical connection between data pages is the information recorded in the IAM pages. An IAM (Index Allocation Map) page tracks approximately 4GB worth of space in a single file also known as GAM interval. The column first_iam_page in the sys.system_internals_allocation_units system view points to the first IAM page in the chain of IAM pages that manage the space allocated to the heap in a specific partition. SQL Server uses the IAM pages to move through the heap.

The following illustration shows how the SQL Server Database Engine uses IAM pages to retrieve data rows in a single partition heap.

SSIS to Excel 2007 Export Issues

Ever tried to export to Excel 2007 from SSIS and experienced issues with the output file then this blog entry may help. The default Microsoft Excel 2007 file fomat, with the file extension .xslx (a Zip file that contains multiple XML files). However Excel can also be saved to a binary format, with the file extension .xlsb.  Excel supported file formats.

The SQL Server Books Online topic, How to: Connect to an Excel Workbook, contains incomplete but potentially misleading information when instructing one to set Extended Properties = "Excel 12.0" on the OLE DB Connection Manager.

• Importing. The driver doesn’t care which of the 2 values (listed below) you use for Extended Properties when you are importing from Excel 2007.  The driver infers the proper Excel file format from the existing input file.
• Exporting. When you are exporting to Excel 2007, however, Extended Properties = "Excel 12.0" creates an Excel binary file. This would normally not be an issue however if you gave your file the standard l .xslx file extension, the Excel application later complains that the file format is incorrect (because it’s really .xlsb) and refuses to open the file.

To correct this simply use the property settings outlined below:

• Default file extension of .xslx (Excel XML format), and Extended Properties = "Excel 12.0 Xml"
• Non-default file extension of .xslb (Excel binary format), and Extended Properties = "Excel 12.0"

Moving tempdb

Moving the tempdb database in SQL Server is a simple process but does require the service to be restarted.

By default, the tempdb database is created in the same location as master, model and msdb.  tempdb is used to store temporary user created objects (e.g. temp tables), temporary internal objects (e.g. work tables for sorting) and any row version data.  Each time SQL Server is started all the objects in tempdb are deleted but the file sizes are retained. 

The primary reasons for wanting to move the tempdb are to increase performance or deal with file size issues.

Step 1: Determine\confirm the logical file names of of the data files for tempdb.  You can run the following command in SQL Server 2000 \2005\2008:

USE tempdb GO

EXEC sp_helpfile

GO

name fileid filename

——— —— ——————————-

tempdev 1 c:\Data\MSSQL\data\tempdb.mdf

templog 2 c:\Data\MSSQL\data\templog.ldf

Unless modified the default will be 1 data file and 1 log file with the standard names tempdev and templog.

Step2: ALTER DATABASE Setting the FILENAME parameter to the location where you’d like each file.

USE master GO

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = ‘D:\tempdb\Data\tempdb.mdf’)

GO

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = ‘D:\tempdb\Log\tempdb.ldf’)

GO

Step 3: Restart SQL Server (Services)

Step 4: Delete old tempdb files

Volla you are done.

Disabling SQL Server Network Protocols

 

Posted in SQL Server on May 8, 2009 by ldgaller

I had a recent requirement to disable all but TCP\IP in the SQL Server Network protocol stack on about 200 of our remote database servers. This is something we normally do as part of our server builds but was missed on the golden image for these servers. As this was going to be deployed via RemoteWare I opted to create and execute a VBScript via command line rather then a T-SQL utilizing the undocumented xp_regwrite.  As a bonus there is a bit of code to change the server audit level which ended up being part of an overall security configuration adjustment. Enjoy, however be sure and test thuroughly in your environment. LDG

Script:

‘——————————————
‘Auth: L.D.Galler
‘Date: 20090508
‘Desc: Script to disable all but tcp\ip on
‘        a given sql server instance & Set Audit Level to ALL

‘Usage: SQLServerNetworkProtocolDefaults [-s server] [-r] [-i] [-l login] [-p password] [-w]

‘[-s server]     – server name
‘[-r]            – restart the server after disabling protocols
‘[-i]            – use integrated security
‘[-l login]      – login
‘[-p password]   – password
‘[-w seconds]    – how many seconds keep trying start the server

‘Notes:
‘You need to specify either [-i] or both [-l] and [-p].
‘If you specify both, [-i] will take over
‘-w defaults to 30 if not specified
‘
‘Audit Level Registry Value
‘None         0×00000000 (0)
‘Success     0×00000001 (1)
‘Failure     0×00000002 (2)
‘All         0×00000003 (3)

‘CSCRIPT SQLServerNetworkProtocolDefaults.vbs
‘——————————————-
Option Explicit

Dim oArgs, sServer, bRestart, bIntegratedSec, sUserid, sPwd, iWait, iNum,sAuditLevel,bSuccess
sServer = “(local)”
bRestart = False
bIntegratedSec = True
sUserid = “”
sPwd = “”
iWait = 30
sAuditLevel = “3″
bSuccess=False

‘verify arguments
Dim bLS, bPS
bLS = False
bPS = False

iNum = 0
Set oArgs = WScript.Arguments
While iNum < oArgs.Count
    Select Case LCase(oArgs(iNum))
        Case “-s”:
            iNum = iNum + 1
            If iNum < oArgs.Count Then
                sServer = oArgs(iNum)
            Else
                Call DisplayUsage
            End If
        Case “-r”:
            bRestart = True
        Case “-i”:
            bIntegratedSec = True
        Case “-l”:
            iNum = iNum + 1
            If iNum < oArgs.Count Then
                sUserid = oArgs(iNum)
                bLS = True
            Else
                Call DisplayUsage
            End If
        Case “-p”:
            iNum = iNum + 1
            If iNum < oArgs.Count Then
                sPwd = oArgs(iNum)
                bPS = True
            Else
                Call DisplayUsage
            End If
        Case “-w”:
            iNum = iNum + 1
            If iNum < oArgs.Count Then
                iWait = CInt(oArgs(iNum))
            Else
                Call DisplayUsage
            End If
        Case Else
            Call DisplayUsage
    End Select
    iNum = iNum + 1
WEnd

‘verify that [i] or both [-l] and [-p] were specified
If bIntegratedSec Or (bLS And bPS) Then
    Call DisableAllButTCP (sServer, bRestart, bIntegratedSec, sUserid, sPwd, iWait)
    Call SetLoginAuditLevel
Else
    Call DisplayUsage
End If
‘ Tells the script to stop and exit.
‘——————————————————
Sub DisableAllButTCP(ServerName, Restart, Integrated, Login, Password, Wait)
    ‘create the instance of SQLServer object
    Dim oServer
    Set oServer = CreateObject(“SQLDMO.SQLServer”)

    ’set login information
    If Integrated Then
        oServer.LoginSecure = True
    Else
        oServer.Login = Login
        oServer.Password = Password
    End If

    ’set server name
    oServer.Name = ServerName

    ‘connect to the server
    oServer.Connect

    ‘get registry information for the server
    Dim oRegistry
    Set oRegistry = oServer.Registry

    ’set registry key to enable only TCP/IP
    oRegistry.SuperSocketList = “tcp”

    Set oRegistry = Nothing

    ‘restart the server
    If Restart Then
        ’stop the server
        WScript.Echo “Stopping the server”
        On Error Resume Next
        oServer.Shutdown(True)
        On Error Goto 0
        oServer.DisConnect

        ‘try to restart
        WScript.Echo “Trying to start the server”
        Dim iCount, bReady
        iCount = 0
        bReady = False
        On Error Resume Next
        ‘try a few times
        While (iCount < Wait) And Not bReady
            oServer.Start False, ServerName
            If Err <> 0 Then
                ‘delay 1s between retries
                WScript.Sleep 1000
            Else
                bReady = True
            End If
            iCount = iCount + 1
            Err = 0
        WEnd
        On Error Goto 0
        If bReady Then
            WScript.Echo “Server restarted successfully”
        Else
            WScript.Echo “Could not restart the server”
        End If
    End If

    oServer.DisConnect

    Set oServer = Nothing
End Sub
‘———————————————————
Sub DisplayUsage
    WScript.Echo “Usage: SQLServerNetworkProtocolDefaults [-s server] [-r] [-i] [-l login] [-p password] [-w]“
    WScript.Echo “”
    WScript.Echo “       [-s server]     – server name”
    WScript.Echo “       [-r]            – restart the server after disabling protocols”
    WScript.Echo “       [-i]            – use integrated security”
    WScript.Echo “       [-l login]      – login”
    WScript.Echo “       [-p password]   – password”
    WScript.Echo “       [-w seconds]    – how many seconds keep trying start the server”
    WScript.Echo “”
    WScript.Echo “You need to specify either [-i] or both [-l] and [-p]. If you specify both, [-i] will take over”
    WScript.Echo “Server name defaults to “”(local)”" if not specified”
    WScript.Echo “-w defaults to 30 if not specified”
WScript.Quit
End Sub
‘———————————————————
Sub SetLoginAuditLevel
Dim objShell, RegLocate, RegLocate1
Set objShell = WScript.CreateObject(“WScript.Shell”)
On Error Resume Next
RegLocate = “HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer\AuditLevel”

objShell.RegWrite RegLocate,”3″,”REG_DWORD”

End Sub

SSIS SQL Server provider logging (Can’t log to sysdtslog90 table)

I recently migrated from SQL Server 2005 to SQL Server 2008 for our Data Warehouse and analytical reporting (SSIS, SSAS). In SQL Server 2005 I had created several template SSIS projects and a general infrastructure for auditing and error handling. Everything ported fine except for some reason I was not capturing the SSIS logging to the normal dbo.sysdtslog90 table, which is created by default in the database specified in the connection object parameter when you turn the SQL Server logging provider on. I quickly found out that contrary to popular belief SQL Server 2008 (SSIS) does not log to dbo.sysdtslog90 but instead logs to the msdb.dbo.sysssislog table.

Search All Databases for a Table

I just had a user send me an application error stating that a table the process was trying to create already existed. The user running the process did not know which of the five application databases the process was referring to and not wanting to run a query against all five databases I created the following simple stored procedure that searches all the databases on a server for a given table name. It has been tested on SQL 2k5 & SQL 2k8.

 

Create PROCEDURE [Util].usp_SearchAllDBforTable
    @tablename sysname
AS
declare @cmd1 varchar(500)
set @cmd1 = ‘USE ?
SELECT [TABLE_CATALOG]
      ,[TABLE_SCHEMA]
      ,[TABLE_NAME]
      ,[TABLE_TYPE]
  FROM [INFORMATION_SCHEMA].[TABLES]
  WHERE [TABLE_NAME] = ”’ + @tablename + ””
exec sp_MSforeachdb  @command1=@cmd1
GO

SSIS: Using IF Then Else logic in Derived Columns

A lot of people tend to use a custom script in order to accomplish If Then Else logic simply because the interface for the derived column data object does not have an IF\IIF operator.. In order to place an IF statement for the derived column you would use the following syntax:

({Boolean Expression}?{True Part}:{False Part})

So if I were checking against a ProductType column to determine whether I should use WholesalePrice or RetailPrice You could simply write up something like the following.

([ProductType ]==99?[WholesalePrice ]:[RetailPrice ])

In order to use multiple entries you would just need to expand the syntax. For example:

([ProductType ]==99?[x]:([ProductType ]==1?[y]:[z]))

A benefit to using the derived column data object instead of a custom script task is that the process is so much faster. In my experience with large ETL sets of data substituting the derived column for the custom script task cuts the processing for that event by 50%.

SQL Server service account locked

I received the following message in the SQL Server error log after an Active Directory change inadvertently locked one of our sql service accounts.

“The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0×2098. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.”

The SPN is essentially a mapping between a principal name and the Windows account that started the server instance service. This is needed because the client will use the server’s hostname and the TCP/IP port to which it connects to compose an SPN. If the SPN mapping has not been performed, then the Windows security layer will be unable to determine the account associated with the SPN and Kerberos authentication will not be used. In an attempt to facilitate this, the SQL Server 2005 instance will automatically try to register the SPN with the AD at startup if TCP/IP is enabled. This message results from the fact that only a domain administrator or a Local System account has the authority to register an SPN. Therefore, under a normal account, SQL Server will be unable to register the SPN for the instance. This should normally not prevent the services from starting but for us it acted as a cryptic indicator that there was an issue with AD. Had we not discovered that the account was locked we would have had all sorts of issues once the cached credentials for the service account had expired.

Property IsLocked is not available for Login

One of my users phoned stating that their application sql logon was unable to connect. I checked the error logs and indeed saw that the logon attempts where failing. I then went to check the account settings in management studio and received the follow error when right clicking on the logon properties:

TITLE: Microsoft SQL Server Management Studio
——————————
Cannot show requested dialog.
——————————
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
——————————
Property IsLocked is not available for Login ‘[xxxx]‘. This property may
not exist for this object, or may not be retrievable due to
insufficient access rights. (Microsoft.SqlServer.Smo)

Apparently the account had been disabled or was in some kind of funky state. About to drop and re-create the logon I decided to try and re-enable the account. Thankfully that resolved the issue and I did not have to drop and re-create the logon. I am still trying to determine root cause but if you encounter the same issue then run the following where “thelogon” is the logon in question and “thepassword” is the password for the account:

ALTER LOGIN [thelogon] WITH PASSWORD=N’thepassword’, DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF 
GO  
ALTER LOGIN [thelogon] ENABLE

Optimizing Integration Services Lookups

Be selective about the lookup columns

Most designers would use the obvious default behavior of the Lookup transform, selecting a table or view to look up in. However, choosing a table will be interpreted as “SELECT *”. This will cause the transformation to fetch un-needed data. That is a waste of effort, memory and resources. I recommend choosing “Use results of an SQL query” instead of naming a table, and in the query selecting only the columns that are used.

Enable memory restriction

Two things happen when memory restriction is enabled on a Lookup: First, the amount of memory that the Lookup is allowed to use is limited. A cache policy is used and new rows are added to the cache on demand. Second, the new rows are added to the cache individually. In other words, SSIS will query the relational database whenever data for a row cannot be located in the internal cache kept by SSIS. These are single-row queries, unlike the large set-based table queries that occur when memory restriction is not enabled. The benefit of enabling memory restriction is that large lookups can be performed which might not be possible otherwise. The cost is that single row queries are used, which collectively are usually slower than a single table query. However, I have found that this performance cost is not always as bad as one might expect, if appropriate indexes are present on the lookup table.

Conclusion

Most of the time you will get good performance from Lookup transforms with default settings. Sometimes, lookups can become significant in the performance of an SSIS package especially  if processing a large amount of source rows. I have given a few tips that I have found make the biggest difference in lookup performance. I hope you find them useful in your package designs.

• Be selective about the columns you request in a lookup
• Enable memory restriction if the lookup is using too much memory
• Be sure you have an appropriate index on the lookup table when using memory restriction
• Consider enabling memory restriction if the lookup refers to a small number of rows from the lookup table