Post reply

T-SQL Performance 1

  • February 17, 2012 at 7:56 am

    #1448524

    Thomas Abraham (2/17/2012)

    danielfountain (2/17/2012)

    Agreed - unfortunately due to the likelyhood of nincompoops its still a viable option!

    We're big on "Quality Initiatives" where I work, and our biggest customer is always sending along a Six Sigma Black Belt to work on them. However, my idea for a simple, but effective, quality program has yet to be adopted: "hire fewer idiots" :w00t:

    In that case maybe you should concentrate on improving recruitment standards (which may mean firing half of the HR department and refusing to use recruitment agencies whose staff are not technically aware as well as HR aware).

    Tom

  • February 17, 2012 at 8:19 am

    #1448548

    bitbucket-25253 (2/16/2012)

    The comments so far are in themselves a wonderful lesson.

    And for that I thank SQL Kiwi, Hugo Kornelis and others to numerous to mention.

    This discussion supports your point well. I always feel pity (well, OK, annoyance actually) for the people who "join" the discussion well along by making a point which indicates they have not bothered to read the valuable thread of comments previously submitted.

  • February 17, 2012 at 8:33 am

    #1448566

    L' Eomot Inversé (2/17/2012)

    I guess FORCESEEK always works, though (?).

    Nothing always works :-). FORCESEEK will result in a plan with a seek operation on the hinted table (or index + columns in the extended syntax after R2 SP1), or in query compilation failure (error 8622). It cannot be used to force the seek that occurs on a partitioned heap with no indexes though (shame, it would have made for a classic QotD: "can FORCESEEK be used successfully on a table with no indexes at all..."). There is a related FORCESCAN hint (again, introduced in R2 SP1).

    Of course that means it should be labelled with a trefoil (purple on yellow background), a skull and crossbones (black on white baground) and red text reading"High explosive, corrosive, and risk of allergic reaction; use only when necessary and even then with great care".

    Oh I don't know. Anything is dangerous in the wrong hands; it's just another hint to be used when all else fails, and with due care.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • February 17, 2012 at 8:43 am

    #1448574

    The seek with no indexes:

    USE tempdb;

    -- Partitioning stuff

    CREATE PARTITION FUNCTION PF(integer) AS RANGE RIGHT FOR VALUES (NULL);

    CREATE PARTITION SCHEME PS AS PARTITION PF ALL TO ([PRIMARY]);

    -- Partitioned heap

    CREATE TABLE #T (col1 integer) ON PS (col1);

    INSERT #T VALUES (NULL);

    -- Seek (query plan below)

    SELECT t.col1 FROM #T AS t WHERE $PARTITION.PF(col1) = 2;

    -- Sadly, an error :(

    SELECT t.col1 FROM #T AS t WITH (FORCESEEK) WHERE $PARTITION.PF(col1) = 2;

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • February 17, 2012 at 8:54 am

    #1448583

    I find strange that when I tested this on my SQL Server 2008 box I always got Index Seeks (never a scan, even when the tables was defined with a VARCHAR(100) column and the stored procedure's parameter was a NVARCHAR(20) one).

    Can anybody explain this behaviour?

  • February 17, 2012 at 8:58 am

    #1448588

    msurasky-905715 (2/17/2012)

    I find strange that when I tested this on my SQL Server 2008 box I always got Index Seeks (never a scan, even when the tables was defined with a VARCHAR(100) column and the stored procedure's parameter was a NVARCHAR(20) one).

    Can anybody explain this behaviour?

    Read the first reply to this thread.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • February 17, 2012 at 9:33 am

    #1448607

    The easiest one this year... thanks!

  • February 17, 2012 at 9:46 am

    #1448622

    I chose yes for a different reason, upon researching I found this article:

    http://stackoverflow.com/questions/440944/sql-server-query-fast-but-slow-from-procedure">

    http://stackoverflow.com/questions/440944/sql-server-query-fast-but-slow-from-procedure

    I didn't think of the implicit conversion but ANSI_NULLS sounded like a reasonable option for index seek.

    Has anybody had any experience with ANSI_NULLS and what si your opinion about it?

    Thanks,

    "El" Jerry

    "El" Jerry.

    "A watt of Ottawa" - Gerardo Galvan

    To better understand your help request, please follow these best practices.[/url]

  • February 17, 2012 at 10:22 am

    #1448642

    Nice easy and straight forward question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • February 17, 2012 at 11:54 am

    #1448713

    Revenant (2/17/2012)

    The easiest one this year... thanks!

    Actually I chose NO because I knew that for a small enough table and/or the right lack of selectivity of the where predicate for the data actually in the table the optimiser would choose a scan anyway (it would choose a scan because that would give better performance than a seek). I'd forgotten about FORCESEEK, so I got it wrong - that was me being sloppy, since SQL 2008 R2 with latest SP and patches is my favorite toy, so I ought to know about it, so I'm not saying that for me it was hard. But other people may still be living with SQL 2008 or even SQL 2005 (or even SQL 7) and might not know about this shiny new hint, so for them it might be hard - they'd have to read up 2008 R2, using up to date documentation not the documentation of the RTM version, and not just SQL 2008 doumentation, to find the answer (unless they didn't know that the optimiser would take account of any statistics available to it when choosing a plan, in which case they would get it right by accident). I suspect the proportion of correct answers may be higher than it would have been if everone was aware that the optimiser might prefer a scan over seek plus bookmark lookups (wrong terminology: I've forgotten what those are called now, and I'm too lazy to hunt for the current term) when the scan would cost less.

    Tom

  • February 17, 2012 at 12:01 pm

    #1448720

    good question to round out the week - tks

  • February 17, 2012 at 12:12 pm

    #1448726

    SQL Kiwi (2/17/2012)

    L' Eomot Inversé (2/17/2012)

    I guess FORCESEEK always works, though (?).

    Nothing always works :-). FORCESEEK will result in a plan with a seek operation on the hinted table (or index + columns in the extended syntax after R2 SP1), or in query compilation failure (error 8622). It cannot be used to force the seek that occurs on a partitioned heap with no indexes though (shame, it would have made for a classic QotD: "can FORCESEEK be used successfully on a table with no indexes at all..."). There is a related FORCESCAN hint (again, introduced in R2 SP1).

    Of course that means it should be labelled with a trefoil (purple on yellow background), a skull and crossbones (black on white baground) and red text reading"High explosive, corrosive, and risk of allergic reaction; use only when necessary and even then with great care".

    Oh I don't know. Anything is dangerous in the wrong hands; it's just another hint to be used when all else fails, and with due care.

    Interesting but I don't think I want my developers to discover the FORCESEEK hint.

  • February 17, 2012 at 12:28 pm

    #1448728

    Good straightforward question. Thanks for submitting.

    http://brittcluff.blogspot.com/

  • February 17, 2012 at 2:15 pm

    #1448809

    I like it. I ran into this exact same issue when tuning some users queries.

    It is a little interesting how poorly this implicit conversion performs since most all data used in system views, tables and procedures is nvarchar but a good portion of existing user data is still varchar.

  • February 17, 2012 at 9:22 pm

    #1448860

    Cliff Jones (2/17/2012)

    Interesting but I don't think I want my developers to discover the FORCESEEK hint.

    This is completely the wrong approach, in my opinion. I prefer to work with developers to share knowledge rather than hoping they stay in the dark. The most successful places I have worked have all had a healthy relationship between DBAs and developers, with regular sessions for each team to share ideas and techniques. I find that working positively with developers produces benefits for everyone.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

Viewing 15 posts - 31 through 45 (of 56 total)

You must be logged in to reply to this topic. Login to reply