T-SQL Performance 1

Question

T-SQL Performance 1

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

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

TomThomson SSC Guru Points: 104773 More actions · Answer 1

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

Rose Bud Hall of Fame Points: 3147 More actions · Answer 2

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.

Paul White SSC Guru Points: 150467 More actions · Answer 3

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

Paul White SSC Guru Points: 150467 More actions · Answer 4

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

msurasky-905715 SSC Enthusiast Points: 195 More actions · Answer 5

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?

Paul White SSC Guru Points: 150467 More actions · Answer 6

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

Revenant SSC-Forever Points: 42467 More actions · Answer 7

Revenant

SSC-Forever

Points: 42467

February 17, 2012 at 9:33 am

#1448607

The easiest one this year... thanks!

EL Jerry SSCertifiable Points: 7063 More actions · Answer 8

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]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

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

TomThomson SSC Guru Points: 104773 More actions · Answer 10

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

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 11

OzYbOi d(-_-)b

SSCarpal Tunnel

Points: 4054

February 17, 2012 at 12:01 pm

#1448720

good question to round out the week - tks

Cliff Jones SSChampion Points: 10547 More actions · Answer 12

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.

Britt Cluff SSCertifiable Points: 5083 More actions · Answer 13

Good straightforward question. Thanks for submitting.

http://brittcluff.blogspot.com/

DBA_Dom SSCrazy Points: 2902 More actions · Answer 14

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.

Paul White SSC Guru Points: 150467 More actions · Answer 15

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