Are the posted questions getting worse?

Question

Are the posted questions getting worse?

Viewing 15 posts - 41,071 through 41,085 (of 66,688 total)

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

TomThomson SSC Guru Points: 104773 More actions · Answer 1

GilaMonster (8/26/2013)
So the current list is:
No need to index small tables
No need to index tables that fit into memory
Indexes enforce physical order of data
Seeks are better than scans
Clustered index seek/scan is better than a nonclustered index seek/scan
Indexes are good. More indexes are better
You only need one (clustered) index

Here are two I used to hear a lot:

If you have an index on column A and an index on column B and an index on (A,B) the optimiser will never choose the index on (A,B)

If you have an index on (A,B) there's no point in having an index on B.

And don't forget the good old

The optimiser never uses an index on a table variable

or its "fundamentalist" variant

You can't have indexes on table variables

Tom

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Both of those covered in the 'considerations for nonclustered indexes' section.

One more added - Indexes with a leading bit column are useless

Edit: and another - Optimiser's choice of seek/scan is affected by fragmentation

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

GilaMonster (8/26/2013)
One more added - Indexes with a leading bit column are useless

I'll probably attend just for this one. I would currently argue that you'd be better off leading with the other key columns in order to see the index get used more often and adding the bit column later in the index, or if your most common queries where bitColumn = 1 having a filtered index with the other key columns and a filter on the bit column. As always I'm sure I'll be proved incorrect in your session, but I'm definitely interested in learning the why.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

I didn't say they're the best thing out there. Just that they're not useless (which many say they are)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Jack Corbett (8/26/2013)
GilaMonster (8/26/2013)
One more added - Indexes with a leading bit column are useless
I'll probably attend just for this one. I would currently argue that you'd be better off leading with the other key columns in order to see the index get used more often and adding the bit column later in the index, or if your most common queries where bitColumn = 1 having a filtered index with the other key columns and a filter on the bit column. As always I'm sure I'll be proved incorrect in your session, but I'm definitely interested in learning the why.

Had an index like that on several tables at a previous employer. The bit flag indicated if a row was historical or current. Most queries against this table were for current data which was less than 10% of the total data. Was a very useful index to have, especially on a SQL Server 2000 system.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

33 slides for 3 hours. Should be enough.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

GilaMonster (8/26/2013)
33 slides for 3 hours. Should be enough.

With that many slides you could easily stretch it into an 8hr preso :hehe::w00t:

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

Steve Jones - SSC Editor SSC Guru Points: 736078 More actions · Answer 8

SQLRNNR (8/26/2013)
GilaMonster (8/26/2013)
33 slides for 3 hours. Should be enough.
With that many slides you could easily stretch it into an 8hr preso :hehe::w00t:

33 slides is about 16 minutes for me 😛

TomThomson SSC Guru Points: 104773 More actions · Answer 9

Steve Jones - SSC Editor (8/26/2013)
SQLRNNR (8/26/2013)
GilaMonster (8/26/2013)
33 slides for 3 hours. Should be enough.
With that many slides you could easily stretch it into an 8hr preso :hehe::w00t:
33 slides is about 16 minutes for me 😛

I guess Gail gets more audience interaction than you do, then. 😀 She must be a more interesting speaker. 🙂

Tom

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

Jack Corbett (8/26/2013)
I would currently argue that you'd be better off leading with the other key columns in order to see the index get used more often and adding the bit column later in the index, or if your most common queries where bitColumn = 1 having a filtered index with the other key columns and a filter on the bit column.

Having an index with a leading bit column is certainly pretty "useless" from a cardinality estimation point of view. Data-distribution information (the statistics histogram) is only created for the first column in the index key. Placing the bit column later in the index key, but before any columns used in inequality tests is a reasonable fix for that, and might be more generally useful, as you say..

A filtered index is also an option, though they can be a bit of a double-edged sword. Having the filtering columns in the keys means Halloween Protection might be needed more often; having a filter on a column that is not in the keys can cause incorrect results and/or suboptimal query plans. There again, just using a filtered index at all can also result in suboptimal plans, so the answer isn't simple (how strange! ha ha).

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Sean Lange (8/23/2013)
SQLRNNR (8/23/2013)
Sean Lange (8/23/2013)
SQLRNNR (8/23/2013)
Koen Verbeeck (8/23/2013)
ChrisM@Work (8/23/2013)
GilaMonster (8/23/2013)
Fal (8/22/2013)
Speaking of fruit. The missus and I are getting a new kitchen in and are trying to finalise a few issues.
We were thinking of getting a stone benchtop, but depending on the stone you may need to oil it, or it can suffer from thermal shock. Has anyone had any bad experiences with stone benchtops?
My Mother got granite worktops in her kitchen some years back. Beautiful and very strong. Polished, sealed and completely waterproof. Only downside - plates, cups or glasses dropped onto it will break.
Do you know which granite, Gail? I spent a while talking to the guys who came in to fit my quartz - they said they preferred black granite to white because it rarely broke during fitting. I appreciate you have your own source of granite in SA and it may well be different to ours.
The friends I was talking about have white granite. Really beautiful. There seemed to be no issues with the fitting.
I have black granite with brown specks. We love it.
I have cheap crappy white formica...we hate it.
I am not a fan of formica.
Me neither but apparently our builder liked to use cheap everything. I have been slowly replacing all the cheap parts over the last 6-7 years. My kitchen is getting closer to the top of the list but it is going to be a pricey one because I have to expand it in the process. Our remodeling goes a lot slower than most because I do it all myself. Makes it a lot cheaper but also a LOT slower.

The estimate for fitting my kitchen was nearly £3000. I've saved that by doing the work myself (except electrical and plumbing final connections and fitting the worktop), and as you know when you spend an unreasonable amount of time getting something straight and level you can do a better job than the professionals. But it does take a hell of a lot of time, in my case four months. Assembling and mounting the units was mostly quick and easy. It's that last 10% which takes the time.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 12

ChrisM@Work (8/27/2013)
The estimate for fitting my kitchen was nearly £3000.

Was that really for the fitting only? Or including cupboards, sink, hub, worktop etc?

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

L' Eomot Inversé (8/26/2013)
Steve Jones - SSC Editor (8/26/2013)
SQLRNNR (8/26/2013)
GilaMonster (8/26/2013)
33 slides for 3 hours. Should be enough.
With that many slides you could easily stretch it into an 8hr preso :hehe::w00t:
33 slides is about 16 minutes for me 😛
I guess Gail gets more audience interaction than you do, then. 😀 She must be a more interesting speaker. 🙂

More that my slides are mostly quite broad. I can talk about the point on a single slide for 5-10 minutes easily

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

Jan Van der Eecken (8/27/2013)
ChrisM@Work (8/27/2013)
The estimate for fitting my kitchen was nearly £3000.
Was that really for the fitting only? Or including cupboards, sink, hub, worktop etc?

Jan - it was for constructing and fitting the units & trim and fitting the hob, oven and sink ready for connection to the services. The worktop was a completely separate purchase - direct from the supplier, saving about £1000 on the quote from the kitchen company.

This quote for fitting seems high but there's actually an astonishing amount of work involved. Merely assembling the cabinets is trivial.

Houses in the UK are constructed differently to those in the US and walls are rarely flat or square, so it's standard practice here to template a stone worktop to ensure a snug fit to the wall profile and accurate angles between sections of worktop. That used to be done with acrylic sheet IIRC but these days it's done with a laser device mounted on a tripod, and a tiny reflective wedge placed at key points along the wall and key corners of the units. Templating and fitting take up about half of the cost of a stone worktop.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 15

GilaMonster (8/27/2013)
L' Eomot Inversé (8/26/2013)
Steve Jones - SSC Editor (8/26/2013)
SQLRNNR (8/26/2013)
GilaMonster (8/26/2013)
33 slides for 3 hours. Should be enough.
With that many slides you could easily stretch it into an 8hr preso :hehe::w00t:
33 slides is about 16 minutes for me 😛
I guess Gail gets more audience interaction than you do, then. 😀 She must be a more interesting speaker. 🙂
More that my slides are mostly quite broad. I can talk about the point on a single slide for 5-10 minutes easily

Each one just says "Databases!"

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams