Syndication

Blog Stats

  • Blogs - 45
  • Posts - 1724
  • Articles - 61
  • Comments - 9487
  • Trackbacks - 440

Bloggers

Welcome

Powered By:

Latest Posts

Convert FILETIME to SYSTEM time using T-SQL

Today I answered a question where OP wanted to convert a 64-bit filetime value to a system time. After a little reading about FILETIME, I learnt that the number is a value for 100 ns passed since January 1st 1601. This gets little tricky since the normal date zero for SQL Server is January 1st 1900.

But with a little arithmetic things worked out. The most cumbersome part was the INT limit for passing parameters to DATEADD function, but that was easy to overcome too. However, I am not convinced the actual number for the nanosecond value is returned "reversed" from system API originally. If that's not the case, just skip the REVERSE function in the derived table d.

See the code below


DECLARE @Sample TABLE
        (
            f CHAR(5),
            lastmod CHAR(16),
            filedate DATETIME
        )

INSERT  @Sample
VALUES  ('File1', '0d4081fc676aac10', '2/5/2010 10:20'),
        ('File2', '0c03f1fc676aac10', '2/5/2010 10:20'),
        ('File3', '0c18164bf2f7ac10', '12/17/2009 10:43'),
        ('File4', '0bbe32fc676aac10', '2/5/2010 10:20'),
        ('File5', '0a6a82fc676aac10', '2/5/2010 10:20'),
        ('File6', '46bd5b9873db9c10', '4/14/2009 14:30'),
        ('File7', '0a88d2fc676aac10', '2/5/2010 10:20')

;WITH cteYak
AS (
    SELECT  f,
            filedate,
            CAST('' AS XML).value('xs:hexBinary(sql:column("Yak"))', 'BINARY(8)') AS Bin
    FROM    (
                SELECT  f,
                        filedate,
                        REVERSE(lastmod) AS Yak
                FROM    @Sample
            ) AS d
)
SELECT  f,
        filedate,
        DATEADD(SECOND, Sec, DATEADD(MINUTE, Peso, 0)) AS Peso
FROM    (
            SELECT  f,
                    filedate,
                    CAST((CAST(Bin AS BIGINT) - CAST(94354848000000000.0 AS BIGINT)) / 10000000 / 60 AS INT) AS Peso,
                    CAST((CAST(Bin AS BIGINT) - CAST(94354848000000000.0 AS BIGINT)) / 10000000 % 60 AS INT) AS Sec
            FROM    cteYak
        ) AS d

posted @ 2/8/2010 5:55 PM by Peter Larsson with 0 comment(s)

Ad-Hoc Rollup by date/time Interval

I often use aggregate queries to rollup data by an arbitrary date/time interval.  I'll share some techniques that I use to accomplish the task in case you find these useful, using the same table below:

CREATE TABLE dbo.WebStats

(

      RequestTimestamp datetime NOT NULL,

      Page varchar(255) NOT NULL

);

CREATE CLUSTERED INDEX WebStats_cdx ON dbo.WebStats(RequestTimestamp, Page);

 

INSERT INTO dbo.WebStats (RequestTimestamp, Page)

VALUES

      ('2010-01-01T00:00:00', 'Default.aspx')

      ,('2010-01-01T00:00:15', 'Default.aspx')

      ,('2010-01-01T00:01:05', 'Order.aspx')

      ,('2010-01-01T00:01:30', 'Default.aspx')

      ,('2010-01-01T00:01:40', 'OrderStatus.aspx')

      ,('2010-01-01T00:02:05', 'Default.aspx')

      ,('2010-01-01T00:03:05', 'ProductInfo.aspx')

      ,('2010-01-01T00:03:30', 'Default.aspx');

GO

 

Simple Rollup

Without an auxiliary table, a little DATEADD magic can do the trick.  Here's an example that summarizes web page requests by minute for the specified date/time range:

DECLARE

      @StartTimestamp datetime = '2010-01-01T00:00:00'

      ,@EndTimestamp datetime = '2010-01-02T00:00:00';

 

SELECT

      DATEADD(minute, DATEDIFF(minute, @StartTimestamp, RequestTimestamp), @StartTimestamp) AS Interval,

      COUNT(*) AS PageRequests

FROM dbo.WebStats

GROUP BY

      DATEADD(minute, DATEDIFF(minute, @StartTimestamp, RequestTimestamp), @StartTimestamp)

ORDER BY

      Interval; 

 

Results:

Interval

PageRequests

2010-01-01 00:00:00.000

2

2010-01-01 00:01:00.000

3

2010-01-01 00:02:00.000

1

2010-01-01 00:03:00.000

2

2010-01-01 00:29:00.000

1

2010-01-01 00:31:00.000

1

2010-01-01 00:42:00.000

1

2010-01-01 02:01:00.000

2

2010-01-01 02:03:00.000

2

2010-01-01 02:31:00.000

1

2010-01-01 02:44:00.000

1

2010-01-01 02:49:00.000

1

 

Arbitrary Intervals

The simple rollup method works well for any of the pre-defined units provided by the DATEADD function (year, quarter, month, day, hour, minute, second or week).  However, it lacks the flexibility to roll up to an arbitrary interval like 15 minutes or 30 seconds.  A little DATEADD/DATEDIFF math addresses this gap.  Below is an example of a 30-minute interval rollup using this technique:

DECLARE

      @StartTimestamp datetime = '2010-01-01T00:00:00'

      ,@EndTimestamp datetime = '2010-01-01T04:00:00'

      ,@IntervalSeconds int = 1800; --30 minutes

SELECT

      DATEADD(second

            ,DATEDIFF(second, @StartTimestamp

            ,RequestTimestamp)

            / @IntervalSeconds * @IntervalSeconds, @StartTimestamp) AS Interval

      ,COUNT(*) AS PageRequests

FROM dbo.WebStats

WHERE

      RequestTimestamp >= @StartTimestamp

      AND RequestTimestamp < @EndTimestamp

GROUP BY

      DATEADD(second

            ,DATEDIFF(second, @StartTimestamp

            ,RequestTimestamp) / @IntervalSeconds * @IntervalSeconds, @StartTimestamp)

ORDER BY

      Interval;

 

Interval

PageRequests

2010-01-01 00:00:00.000

9

2010-01-01 00:30:00.000

2

2010-01-01 02:00:00.000

4

2010-01-01 02:30:00.000

3

 

Missing Intervals

You probably noticed that periods with no activity at all are omitted rather than reporting a zero value.  One method to include the missing intervals is with an outer join to a temporal table containing all the desired intervals.  Ideally, the temporal table would be a permanent one but I've found it impractical to maintain such a table for ad-hoc needs.  Fortunately, a utility numbers CTE is a handy way to generate the needed intervals dynamically.  The example below provides up to 65,536 interval values and can be easily extended as needed.

DECLARE

      @StartTimestamp datetime = '2010-01-01T00:00:00'

      ,@EndTimestamp datetime = '2010-01-01T04:00:00'

      ,@IntervalSeconds int = 1800; --30 minutes

 

WITH

      T2 AS (SELECT 0 AS Num UNION ALL SELECT 0),

      T4 AS (SELECT 0 AS Num FROM T2 AS A CROSS JOIN T2 AS B),

      T256 AS (SELECT 0 AS Num FROM T4 AS A CROSS JOIN T4 AS B CROSS JOIN T4 AS C CROSS JOIN T4 AS D),

      T65536 AS (SELECT ROW_NUMBER() OVER(ORDER BY A.Num) AS Num FROM T256 AS A CROSS JOIN T256 AS B)

SELECT

      DATEADD(second

            ,(Num-1) * @IntervalSeconds, @StartTimestamp) AS Interval

      ,COUNT(WebStats.RequestTimestamp) AS PageRequests

FROM T65536

LEFT JOIN dbo.WebStats ON

      WebStats.RequestTimestamp >= DATEADD(second, (Num-1) * @IntervalSeconds, @StartTimestamp)

      AND WebStats.RequestTimestamp < DATEADD(second, Num * @IntervalSeconds, @StartTimestamp)

WHERE

      Num <= DATEDIFF(second, @StartTimeStamp, @EndTimestamp) / @IntervalSeconds

GROUP BY

      DATEADD(second

            ,(Num-1) * @IntervalSeconds, @StartTimestamp)

ORDER BY

      Interval;  

 

Interval

PageRequests

2010-01-01 00:00:00.000

9

2010-01-01 00:30:00.000

2

2010-01-01 01:00:00.000

0

2010-01-01 01:30:00.000

0

2010-01-01 02:00:00.000

4

2010-01-01 02:30:00.000

3

2010-01-01 03:00:00.000

0

2010-01-01 03:30:00.000

0

 

posted @ 2/7/2010 1:51 PM by Dan Guzman with 0 comment(s)

SQL Server Blogs I Read

Peter Larsson recently scrubbed his laptop but didn’t bring over his favorites and made a plea for interesting SQL Server related blogs.  My reading list of SQL Server blogs and sites is up to 83 sites in Google Reader and I thought I’d share my list in hopes of finding more.  Here’s the link to the OMPL file that you can import into a feed reader: SQL Server Blogs OPML file.

Below is the complete list of SQL Server blogs I read regularly.  If you’re writing about SQL Server and not on that list please use the contact me form and let me know about your blog.  I’d like to get you added to the list.

Bill subscriptions in Google Reader

posted @ 2/5/2010 7:16 AM by Bill Graziano with 1 comment(s)

The impossible thing happened...

Yesterday I got the brilliant idea to format my laptop and reinstall Windows 7 from scratch. I've had a few problem last month, mostly due to upgrading the preinstalled Vista to Windows 7.
Said and done, I boldly finished the installation and thought everything was ok. I mean, my email is IMAP so all email are stored at my ISP, all my relevant documents are on another partition on the harddrive and so on...

But..!
Now I have noticed I forgot to save my Favorites Links.

So now I need your help. I can try to remember all my favorites (about 80 of them), or I can pledge to you to post interesting SQL-related blogs to me, so I can quickly check them and add them to my Favorites again.


Thank you

//Peter

posted @ 2/3/2010 8:02 AM by Peter Larsson with 7 comment(s)

Invitation to startup meeting for PASS Scania

PASS Scania is a new PASS chapter in Sweden which will promote the interest, networking and knowledge for professional SQL Server-users such as developers, DBA's and BI-specialist in Skåne. The Chapter also has a business alliance with an existing usergroup SQLUG.
 
All developers who use SQL Server platform in their work is welcome to participate in the seminars with start february 4th 2010. Both consultants and employees are welcome.
 
Membership and seminars are free and activities are mainly sponsored events.
 
First meeting is held in feburary where new members have the opportunity to present themself, we will present the agenda for 2010 and we will also demonstrate the new Microsoft PowerPivot platform.
Further seminars will include how to read and interpret execution plans, how to decide for an index strategy and query performance considerations.
 
Welcome to PASS Scania first event on the 4th of February 2010 at S:t Gertruds in Malmö at 18:00. More about agenda is found on www.pass-scania.se where you also register. Number of seats are limited and are restricted to sequence of registrations."
 
//Peter

posted @ 1/27/2010 4:09 PM by Peter Larsson with 1 comment(s)

Convert binary value to string value

With SQL Server 2008, we can easily use

DECLARE @bin VARBINARY(MAX)
SET     @bin = 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8

SELECT  CONVERT(VARCHAR(MAX), @bin, 2)


But how can we do this in SQL Server 2005? You can't use a simple CAST or CONVERT, because you get the ASCII representation of the binary values.

So, here is how you can do the conversion with SQL Server 2005 by using XML.

-- Prepare value
DECLARE @bin VARBINARY(MAX)
SET     @bin = 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8

-- Display the results
SELECT @bin AS OriginalValue,
        CAST('' AS XML).value('xs:hexBinary(sql:variable("@bin"))', 'VARCHAR(MAX)') AS ConvertedString

posted @ 1/27/2010 9:38 AM by Peter Larsson with 4 comment(s)

Clustering for Mere Mortals (Pt1)

This past Saturday I enjoyed the hospitality of the Tampa SQLSaturday (#32) team.  My presentation was “Clustering for Mere Mortals”.  Unlike many clustering presentations, I had demonstration content.  This resulted in the most asked question being “Do you have instructions on how you built that?”  This is the first part of those instructions.

Here is what the cluster consists of (Virtually):

One Windows 2003 R2 (x64) Server acting as Domain Controller, DNS Server, DHCP Server, File Server and iSCSI Target Server

Two Windows 2008 R2 Servers as cluster Nodes.

All this runs on a Lenovo W500 laptop with an Intel Core 2 Duo T9600 processor, 8 GB RAM and a 320 GB Internal Hard disk running Windows 7 Enterprise (x64).  The total disk footprint is slightly under 50 GB for all virtual guests.

One of the toughest decisions was choosing a virtualization platform.  I didn’t want to run Windows Server 2008 R2 on my notebook since that took away Hibernation and Sleep modes, plus getting the drivers for WiFi, Bluetooth, etc. working is a real pain.  VMWare was out since I wanted this to run Microsoft VHDs I downloaded for training as well as my new demo cluster.  Virtual PC was out since I needed 64-bit guest capability to run Windows 2008 R2 as a guest.  I found a winning candidate in VMLite.  VMLite is a Windows implementation of Sun’s VirtualBox platform.  It supports VDI, VMDK and VHD disk formats as well as 64-bit guests.  Incidentally, it is also free.

Based on prior experience with the above virtualization products, I chose to create a VM folder off of the root of my C: drive.  Below that I created a Machines folder and a HardDrives folder.  Set I then these as default locations in VMLite.  I also copied the VMLiteGuestAdditions.iso file to this location so it was easy to find later.

Like most Sun products (cough Java cough) it is semi-hostile to Windows users.  Still, this was the option with the lowest suck factor so I started building.  VMLIte does have a few quirks you have to learn to work around.  VMLite uses a UUID to identify virtual disk files to its Virtual Media Manager.  Evidently unique file names and paths are not adequate, probably because XML isn’t involved.  So if you copy a VHD file it will not allow you to “register” it with the Virtual Media Manager without a “mother may I?” step.   Many of the VHDs downloaded from Microsoft also have identical UUIDs, even ones for different training classes.  Common base OS image is the most likely explanation.  Anyway, to fix this is actually pretty simple

Open a command prompt

Change directories to the VMLite executable folder (C:\Program Files\VMLite\VMLite Workstation on my system).  Look for the VBoxManage.exe file. 

Type:  VBoxManage InternalCommands SetUUID <path\Filename to VHD>  Note:  Do not put quotes around the path and filename.

Using this technique you can now add any VHD file to the Virtual Media Manager tool in VMLite .

Interestingly enough, VMLite will not create a VHD file when you define a new Virtual Machine.  It natively will create a VDI (Sun Virtual Device) file.  You can use the Windows 7 Disk Management tool to create a VHD file with the correct characteristics and then detach it from the Host OS for use in a VM.

There are some other unexpected behaviors of VMLite (remember I did mention user-hostile?)  but no show-stoppers.  Finding them will be left as an exercise for the student. J

Start building OS base images and generally working in VMLite to get familiar with it.  At this point, you should be able to create new VMs, import existing VHDs, and generally do basic functions and navigation within VMLite.  We still have a ways to go to build a cluster, but we have a solid foundation to start on.

 

 

 

 

 

 

posted @ 1/26/2010 1:33 PM by Geoff N. Hiten with 0 comment(s)

Get date from ISO week number

This function is just the opposite of this one, http://weblogs.sqlteam.com/peterl/archive/2009/05/27/Extended-ISO-week-function.aspx.
The function in the link returns the ISO week number from a given date, and the function below returns the monday's date from an ISO week.
Or you can use the function blogged here http://weblogs.sqlteam.com/peterl/archive/2009/12/01/How-to-get-a-date-from-Year-week-and-weekday.aspx.


CREATE FUNCTION dbo.fnISOMonday
(
    @theYear SMALLINT,
    @theWeek TINYINT
)
RETURNS DATETIME
AS
BEGIN
    RETURN  (
                SELECT  DATEADD(DAY, 7 * @theWeek - 7, CurrentYear)
                FROM    (
                            SELECT  DATEADD(DAY,(DATEDIFF(DAY, '17530101', Jan4) / 7) * 7, '17530101') AS CurrentYear,
                                    DATEADD(DAY,(DATEDIFF(DAY, '17530101', DATEADD(YEAR, 1, Jan4)) / 7) * 7, '17530101') AS NextYear
                            FROM    (
                                        SELECT  DATEADD(YEAR, @theYear - 1900, 3) AS Jan4
                                        WHERE   @theYear BETWEEN 1900 AND 9999
                                                AND @theWeek BETWEEN 1 AND 53
                                    ) AS x
                        ) AS d
                WHERE   DATEADD(DAY, 7 * @theWeek - 7, CurrentYear) < NextYear
            )
END

posted @ 1/26/2010 10:57 AM by Peter Larsson with 0 comment(s)

Announcing Baby Ben

Happy to announce the arrival of Benjamin Micah Caldwell.  Mother and baby are both doing well.  Big sister thinks he’s just another baby doll for her to play with.  Sleep is at a premium, but everyone is adapting well.

posted @ 1/24/2010 11:02 AM by AjarnMark with 3 comment(s)

Celko Stumper - The Class Scheduling Problem

Joe Celko has posted a new Stumper - The Class Scheduling Problem
here http://www.simple-talk.com/sql/t-sql-programming/celkos-sql-stumper-the-class-scheduling-problem/

Here is one suggestion to solve the problem. It's linear in time so it should be very fast. It's based on the "Descending order" approach, and the "paths" columns are used to store valid room and classes.

-- Initialize and find the valid combinations
DECLARE  @HowManySeatsFree INT = 0 -- Set to zero for maximum seating, and to 1 for letting in late pupils.

DECLARE  @Source TABLE
        (
            room_nbr CHAR(2),
            class_nbr CHAR(2),
            recID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED
        )

INSERT      @Source
            (
                room_nbr,
                class_nbr
            )
SELECT      r.room_nbr,
            c.class_nbr
FROM        dbo.Rooms AS r
INNER JOIN  dbo.Classes AS c ON c.class_size <= r.room_size - @HowManySeatsFree
ORDER BY    r.room_size DESC,
            c.class_size DESC

-- Iterate the possibilities and return the unique answers
;WITH cteYak(recID, room_nbr, roomPath, class_nbr, classPath, isPresent)
AS (
    SELECT  recID,
            room_nbr,
            '/' + CAST(room_nbr AS VARCHAR(MAX)) + '/' AS roomPath, -- List of taken rooms
            class_nbr,
            '/' + CAST(class_nbr AS VARCHAR(MAX)) + '/' AS classPath, -- List of taken classes
            CAST(0 AS BIGINT)
    FROM    @Source
    WHERE   recID = 1

    UNION ALL

    SELECT      recID,
                room_nbr,
                CASE isPresent -- If room never encountered before (isPresent=0), take it!
                    WHEN 0 THEN roompath + CAST(room_nbr AS VARCHAR(MAX)) + '/'
                    ELSE roompath
                END AS roompath,
                class_nbr,
                CASE isPresent -- If class never encountered before (isPresent=0), take it!
                    WHEN 0 THEN classpath + CAST(class_nbr AS VARCHAR(MAX)) + '/'
                    ELSE classpath
                END AS classpath,
                isPresent
    FROM    (
                SELECT      s.recID,
                            s.room_nbr,
                            y.roomPath,
                            s.class_nbr,
                            y.classpath,
                            CHARINDEX('/' + CAST(s.room_nbr AS VARCHAR(MAX)) + '/', y.roompath)
                            + CHARINDEX('/' + CAST(s.class_nbr AS VARCHAR(MAX)) + '/', y.classpath) AS isPresent -- See if room or class is already taken. If so, isPresent is greater than 0, otherwise it will be 0.
                FROM        @Source AS s
                INNER JOIN  cteYak AS y ON y.recID + 1 = s.recID
        ) AS d
)
SELECT  room_nbr,
        class_nbr
FROM    cteYak
WHERE   isPresent = 0 -- Only present the combination never taken/found before
OPTION  (MAXRECURSION 0) -- Allow up to 32767 possible combinations.

posted @ 1/23/2010 1:31 PM by Peter Larsson with 5 comment(s)

SQL Server – Find the most expensive operations in Execution plans

Execution plans! Don’t you just love them? They’re the first thing you look at when tuning a query or a stored procedure. But what do you do if you have a gigantic query play with 10’s of nodes? how do you find the most complex one? Where do you start?

What I’ve usually done in situations like that is to first find the node/statement with the highest cost and work from there. Now the highest cost can be IO, CPU, Row number or the good old SubTree cost which gives us a number based on all those counters. Let’s see how.

With SQL Server 2005 we got the option to show our execution plans in XML in two different ways: using the SET SHOWPLAN_XML ON option or choosing the Show Execution Plan XML option in the execution plan context menu.

These are the two queries we’ll work with:

SELECT  SalesOrderID, OrderDate
FROM    Sales.SalesOrderHeader SOH


SELECT  *
FROM    Sales.SalesOrderHeader SOH
        join Sales.SalesOrderDetail SOD on SOH.SalesOrderID = SOD.SalesOrderID
 
They generate pretty simple plans that are perfect for our demo.
 
This is a heavily trimmed part of the upper queries execution plan XML:
 
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.0.2531.0">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="..." StatementId="1" StatementCompId="1" ... >
          <StatementSetOptions ... />
          <QueryPlan CachedPlanSize="8" CompileTime="1" CompileCPU="1" CompileMemory="152">
            <RelOp ... > ... </RelOp>
          </QueryPlan>
        </StmtSimple>
        <StmtSimple StatementText="..." StatementId="2" StatementCompId="2" ... >
          <StatementSetOptions ... />
          <QueryPlan CachedPlanSize="40" CompileTime="6" CompileCPU="6" CompileMemory="512">
            <RelOp ... > ... </RelOp>
              </Merge>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

 

First thing to do is to remove the xmlns=http://schemas.microsoft.com/sqlserver/2004/07/showplan attribute part from the ShowPlanXML node. This is needed so that we don’t have problems with namespaces further on which simplifies the querying. You can leave the xmlns attribute but then you’ll have to use WITH XMLNAMESPACES to query it properly. From this XML we can extract the costliest nodes and statements with the following code:

-- notice the removed
-- xmlns=http://schemas.microsoft.com/sqlserver/2004/07/showplan 
-- attribute
declare @xml xml = '
<ShowPlanXML Version="1.1" Build="10.0.2531.0">
... Shortened XML data...
</ShowPlanXML>'
-- go through all the execution plan nodes, get the attributes and sort on them
SELECT c.value('.[1]/@EstimatedTotalSubtreeCost', 'nvarchar(max)') as EstimatedTotalSubtreeCost,
       c.value('.[1]/@EstimateRows', 'nvarchar(max)') as EstimateRows,
       c.value('.[1]/@EstimateIO', 'nvarchar(max)') as EstimateIO,
       c.value('.[1]/@EstimateCPU', 'nvarchar(max)') as EstimateCPU,
       -- this returns just the node xml for easier inspection
       c.query('.') as ExecPlanNode        
FROM   -- this returns only nodes with the name RelOp even if they are children of children
       @xml.nodes('//child::RelOp') T(c)
ORDER BY EstimatedTotalSubtreeCost DESC

-- go through all the SQL Statements, get the attributes and sort on them
SELECT c.value('.[1]/@StatementText', 'nvarchar(max)') as StatementText,
       c.value('.[1]/@StatementSubTreeCost', 'nvarchar(max)') as StatementSubTreeCost,
       c.value('.[1]/@StatementEstRows', 'nvarchar(max)') as StatementEstimateRows,
       c.value('.[1]/@StatementOptmLevel', 'nvarchar(max)') as StatementOptimizationLevel,
       -- this returns just the statement xml for easier inspection
       c.query('.') as ExecPlanNode
FROM   -- this returns only nodes with the name StmtSimple
       @xml.nodes('//child::StmtSimple') T(c)
ORDER BY StatementSubTreeCost DESC

In the results from the upper queries we can see that the ExecPlanNode is the XML of the processed node. By clicking on it we can inspect just that single node for further details.

PlanOperatorCostResults

We can see that the results are nicely sorted by cost and finding most expensive parts of your plan is easy. This may not look like a big deal but imagine having a stored procedure that generates 50 complex execution plans. Try going through that by hand and see how long you last.

posted @ 1/21/2010 12:14 PM by Mladen Prajdić with 11 comment(s)

What three events brought you here?

Paul Randal recently blogged about three life-changing events that brought him to where he is today.  His blog is a very interesting read and idea, and so I bring to you my three events that brought me to where I am today in my technical career as a SQL Server DBA.

Right out of high school and during the first two years of college, I worked as a student worker for the District Attorney's Office at the County of San Diego performing data entry and other forgettable tasks.  During this job, I worked with a previous classmate whose mom worked in another division at the County of San Diego.  It was communicated to her mom that I was interested in a technical career.  The mom was able to get an interview for me with the DBA team in the Information Services division at the County of San Diego.  The position was only as a student worker, but it was a technical position so I jumped at the opportunity when the job was offered to me.  I remained a student worker in the DBA team until I graduated from college with a Bachelor of Science in Mathematics with emphasis in Computer Science.  I was then hired full-time to be a Junior DBA in the same group.

I remained a Junior DBA until the IT department was outsourced to Computer Sciences Corporation.  This is the second significant event.  Rather than take the generous severance package that was offered to any employees who did not wish to be part of the outsourcing, I decided to instead transfer to CSC as an Intermediate DBA.  CSC was located in San Diego, so I didn't need to move or commute far away.  At CSC, I met my husband which is a very significant event in my life but not relevant to this blog. 

After about two years at CSC, I was so unhappy with my management that it drove me to find another job.  I had been interviewing for about six months without any success when I happened to be at an interview and saw a former co-worker there.  It turns out that I was interviewing for his position which he had already vacated (he was just there visiting and picking up some of his things).  I surely wasn't qualified for the position, but this chance encounter is the third significant event.  He contacted me a few weeks later and let me know of a DBA position that had opened up in his group at the new company, Qualcomm Incorporated.  I interviewed for it a few days later.  Although they were looking for a Senior DBA, my former co-worker decided to take a chance with me since he had mentored me at the County of San Diego and was aware of my possibilities.  They hired me as an Intermediate DBA.  The rest is history. 

I have been at Qualcomm for 8 years and love every minute of it.  I am now a Staff DBA (one level higher than Senior).  My mentor still works at the same company as me, however he has moved on to other things.  I am eternally grateful to him for all of the things that I learned and for taking a chance when there were more seasoned candidates interviewing for the position. 

If it weren't for my former classmate's mom landing me an interview for a student worker position in the DBA team, I don't know where I would be in my technical career.  I suppose I would have eventually made it into a DBA position since the skills match me perfectly, but it probably would have been a long journey to get there.

If it weren't for transferring to the new company during the IT outsourcing, I may never have ended up at my current company where I absolutely love my job.  I also wouldn't have met my husband.

And finally if it weren't for my mentor and former co-worker, I wouldn't be where I am today.  I owe my career to him.  Hopefully he accepts my gratitude instead of a monetary gift, haha!

posted @ 1/19/2010 10:14 AM by Tara Kizer with 3 comment(s)

SQL Server Links - 19 January 2010

The Bit Bucket (Greg Low): IDisposable : Stored Procedures - Time for a real contract? [sqlblog.com/blogs/greg_low/]

Interesting case of watching log file growth during a perf test [www.sqlskills.com/BLOGS/PAUL/]

Improvement in minimizing lockhash key collisions in SQL Server 2008R2 and its impact on concurrency [blogs.msdn.com/sqlserverstorageengine/]

Microsoft and Intel Push One Million iSCSI IOPS [blog.fosketts.net]

The Truth About Hyper-V Memory Overcommit [www.vcritical.com]

A Few Useful Queries for SQL Server 2008 Integrated Full Text Search (iFTS) [glennberrysqlperformance.spaces.live.com]

SYS2 DMVs on CodePlex [sqlblog.com/blogs/davide_mauri/]

Intel Nehalem-EX and Database Performance [glennberrysqlperformance.spaces.live.com]

Iron and Cloud [www.sqlmag.com] - Interesting editorial about SQLAzure

PASS Processes and Results posted [www.allenkinsel.com]

Free eBook on SQL Server Maintenance Plans Now Available [www.sqlservercentral.com/blogs/aloha_dba/]

SQL Server Waits and Queues [www.sqlservercentral.com/blogs/pearlknows/]

How to increase SQL Database Full Backup speed using compression and Solid State Disks [henkvandervalk.com] - Great post on backup performance.

10 Hot Scalability Links for January 13, 2010 [highscalability.com]

posted @ 1/19/2010 9:02 AM by Bill Graziano with 0 comment(s)

Star Trek Online Open Beta, Thumbnail Review

I figure there's got to be some crossover between the SQL Server folks and the MMO folks, so here ya go.

I downloaded and played the new Star Trek: Online open beta this week, just to see if it was going to be worthwhile.  So here's some of my thoughts on it.

It's an MMO, Massively Multiplayer Online game.  Typically that means you'll buy a box at your local retail outlet for $49.99 and then pay a monthly subscription fee, probably $15. 

It's also a Role Playing Game.  I was looking forward to it, thinking it'd be a really interesting social experiment to get people to work together to run a ship.

Wrong.

So it's an MMO, and you're basically a one-man army, you have a ship of your very own, that you can fly using your mouse.  As long as you don't want to go upside down.  It's the Federation, son, YOU WILL FLY UPRIGHT AT ALL TIMES.  The weapons and upgrades are the predictable "more power" upgrades you'd get at the beginning of any MMO.  There seemed to be a lot more MMO jargon used in the weapon descriptions (weapons have DPS, armor/shield penetration, etc) that might be opaque to people who haven't done a lot with MMO's before.  That is likely to change, this is a beta. 

Basically there are 2 "modes" to the game.  Ship mode, where you're flying your ship, and ground mode, where you're running around hallways as a person. 

The ship combat was...well, boring.  Fly around (but not upside down) and shoot things.  make sure your weakest shield is facing away from your enemy.  Hold down the space bar to auto-fire.  It takes about 2 minutes to master.  The shield thing is interesting too, if by "interesting" you mean "really simple and cheesed-out".  There are 4 quadrants to your shield:  fore/aft/starboard/port.  If you fly above or below someone, it's pretty much *random* what's going to get hit.  Your energy weapons have a 200 degree firing arc, facing either forward or aft, but ignoring up and down.  Last time I checked, space combat was supposed to be 3D.  So it's a 2-d based combat where you can fly in 3 dimensions, sort of. 

You can customize your ship pretty much on the fly.  Whenever you're out of combat, you can completely replace your weapons with any that you salvage (loot) off another ship you kill.  You know, what would take a drydock and several weeks takes 2 seconds in MMO-land. 

Then occasionally you'll touch down on the planet and be the away team.  Now this is Borg-era Star Trek, and they let you, the captain, the person responsible for the ship, beam down into dangerous situations with the away team.  Hrm.  Yeah, so that regulation obviously didn't make it into the RPG.  When you're on the ground (or in another ship) the game acts like the world's most dumbed-down third person shooter.  Click your target, click your attack.  Repeat until target dies.  You have 3 attacks (2 shooting and a melee knockback) and that's...well, that's it.  You have medical packs too.  I never really felt in enough mortal peril to use one though.

One of the things I like about MMO's is specialization.  I can pick a character class, learn that character class completely, and then I'll be a fairly good player.  ST:O went a different way.  You choose a character class, but I'm not exactly sure why, since no matter what class you pick you get the same skill trees, leveling track, and role.  You're the captain.  That's it.  Except when you're not the captain, and you're having to learn the roles of all the other classes so you can populate your bridge with good officers.  And then know all the other skill trees, so you can level those officers as they gain experience.

It's like they took the worst parts of most RPG's (having to micro-manage a team of NPC's), the worst space flight game ever, and a really bad 3rd person shooter and crammed them into one package.

So far: Bad character management, bad space flight, and bad third person shooter.  So how's the social part?

Um.  Yeah.  There's a chat box, and it scrolls the Star Trek equivalent of Barrens chat.  People asking where different things are.  Someone actually (jokingly) asked where Mankrik's Wife was (a WoW joke) just to make the scene complete.  You *can* team up and there's a system called "Fleets" which look like a guild based system.  I didn't stick around long enough, the game mechanics were, frankly, boring me to tears. 

The graphics are...fine.  I guess.  I have a midrange video card, there was no video lag (lots of server lag though).  Everything looked pretty enough, which for an MMO means "about 2 generations behind any other rendering system".  Phasers look like phasers, photon torpedos look like photon torpedos.  Nothing earth-shatteringly pretty or mind-numbingly awful.

There are lots of loading screens.  In the WoW universe you can travel from the far southern end of the world to nearly the northern tip of the world with no loading screens and smooth transitions.  If you attempted to walk that distance it would take probably a couple of hours, which is why there are lots of fast-travel options in WoW.  There were loading screens inside ST:O within the same building.  Small building.  An area that took 30-45 seconds to run across would have a loading screen to get to it. 

There were lots of glitches, which I mainly account to server lag.  I'm not sure that a shaky launch is going to do these guys any favors though when it comes to winning enough market share to be viable.  The only thing that Age of Conan did right was get have stable servers at launch and through beta. ST:O's operations people really don't understand how to seed torrents, it took me about 2 days to download the client software through their system, and the torrent wasn't seeded at all before it went live.  You know it's going to be fun when uTorrent says under "estimated time" 3y3d. 

There were running glitches (run along, suddenly you rewind to 50 yards back down the hall), ship transition glitches (you beam up and you see your character running in space).  Ship flight glitches (you'd rewind to about 50...uh...probably meters back sometimes).  I'm going to attribute most of the glitchiness to the beta quality server software.  That's fine, that's why you have betas.  The gameplay mechanics, however, were just awful, and that's something you do in design, not in beta.

A pretty horrible game so far.  I hope it gets better, but I'm not going to be buying it.

posted @ 1/18/2010 8:13 PM by Chris Miller with 2 comment(s)

SQL Server - How many times is the subquery executed?

Adam Machanic launced his second TSQL tuesday: Invitation for T-SQL Tuesday #002: A Puzzling Situation. The theme is to show something interesting and not expected by the first look. You can follow posts for this theme on Twitter by looking at #TSQL2sDay hashtag.

Ok here we go. Let me ask you this: How many times will the subquery in the below SQL statement be executed?

SELECT  (SELECT COUNT(*) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOH.SalesOrderID) AS c, *
FROM    Sales.SalesOrderHeader SOH
ORDER BY c DESC

 

How about the subquery in this in the below SQL statement be executed? It’s the same query only we’re repeating the subquery in the order by

SELECT  (SELECT COUNT(*) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOH.SalesOrderID) AS c, *
FROM    Sales.SalesOrderHeader SOH
ORDER by (SELECT COUNT(*) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOH.SalesOrderID) DESC

 

If you said once you’d be wrong. At first look I also thought it would run once because SQL server query optimizer would be smart enough to realize we’re ordering by the same values as we’re selecting. However this isn’t a case. Let’s take a look at the IO cost of both queries:

Query #1:

-- Table 'SalesOrderHeader'. Scan count 1, logical reads 703
-- Table 'SalesOrderDetail'. Scan count 1, logical reads 1238

Query #2:

We can see the table SalesOrderDetail being scanned twice. Once for select part and once for order by part.

-- Table 'SalesOrderHeader'. Scan count 1, logical reads 703
-- Table 'SalesOrderDetail'. Scan count 2, logical reads 2476 <- READ TWICE! ONCE FROM SELECT AND ONCE FROM ORDER BY!

 

Of course the second example isn’t a way to write SQL but it sure is interesting how stuff works.

posted @ 1/12/2010 2:05 PM by Mladen Prajdić with 1 comment(s)