Month: October 2013

Converting Delphi datetime to SQL Server

--1of2 -- convert delphi datetime to sql

-- input a delphi float for conversion
declare @input float = 41492.9039269676 -- <<<<<<<<<<< user input

-- seperate the date-part
declare @date int = @input

-- seperate the time-part
declare @time float = (@input - @date)

-- display results
select @input 'delphi', cast(dateadd(dd, @date, '1899-12-30 00:00:00') + @time as datetime) [sql]


--2of2 -- convert sql datetime to delphi

-- input the sql datetime for conversion
declare @input2 datetime = '2013-08-06 21:41:39.290' -- <<<<<<<<<<<< user input

-- display results
select @input2 [sql], cast(@input2 as float)+2 'delphi'

Large Page Extensions

After a re-boot I noticed the message “Cannot use Large Page Extensions: lock memory privilege was not granted.”

To fix this I remoted onto the SQL2008r2 Enterprise Edition server and ran “gpedit.msc”.

Within the tree I navigated to / Computer Configuration / Windows Settings / Security settings / Local Policies / Use Rights ~ / Lock Pages in Memory.

And added the account used to start the SQL Server Service.

CXPacket

The wait type CXPACKET is no biggie. Its the time spend re-assembling the results from parallel queries.

You can change the parameter ‘Max Degree of Parallelism’, to however many cores your big-queries should be shared among. Best practice is the number of core’s in a single numa (max 8).

You can also change the ‘Cost Threshold for Parallelism’ to set the trigger level for big-queries. The default (‘5’) is usually a bit low.

Removing unwanted datafiles

Many people are too precious about scripts. Here’s my simple GUI-based method for removing unwanted datafiles.

1a) fire up the shrink gui (tasks / shink / files)
1b) locate your target datafile
1c) run the 3 ‘shrink actions’ in order, one at a time (2nd one to ‘1’)

2a) Fire up the Database properies files tab
2b) highlight the unwanted datafile, click ‘remove’, and then ‘ok’