Table size and indexes

Question

Table size and indexes

Viewing 9 posts - 31 through 38 (of 38 total)

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

SQLRNNR SSC Guru Points: 281324 More actions · Answer 1

Eric M Russell - Friday, April 14, 2017 4:40 PM
SQLRNNR - Friday, April 14, 2017 3:45 PM
Eric M Russell - Friday, April 14, 2017 10:03 AM
What you don't want to do is add too many indexes upfront which won't be used.
You mean 999 indexes is not a goal for every table? :Whistling:
For most tables, 3 or 4 is too many.

Agreed

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

SQLRNNR - Monday, April 17, 2017 10:41 AM
Eric M Russell - Friday, April 14, 2017 4:40 PM
SQLRNNR - Friday, April 14, 2017 3:45 PM
Eric M Russell - Friday, April 14, 2017 10:03 AM
What you don't want to do is add too many indexes upfront which won't be used.
You mean 999 indexes is not a goal for every table? :Whistling:
For most tables, 3 or 4 is too many.
Agreed

Heh... gee... you mean that my 4 column table doesn't need 16 indexes to cover every eventuality? 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

SQLRNNR SSC Guru Points: 281324 More actions · Answer 3

Jeff Moden - Monday, April 17, 2017 3:47 PM
SQLRNNR - Monday, April 17, 2017 10:41 AM
Eric M Russell - Friday, April 14, 2017 4:40 PM
SQLRNNR - Friday, April 14, 2017 3:45 PM
Eric M Russell - Friday, April 14, 2017 10:03 AM
What you don't want to do is add too many indexes upfront which won't be used.
You mean 999 indexes is not a goal for every table? :Whistling:
For most tables, 3 or 4 is too many.
Agreed
Heh... gee... you mean that my 4 column table doesn't need 16 indexes to cover every eventuality? 😛

You better create some indexes to help improve the write operations too.

No joke - I had a client that had numerous indexes created for write performance improvement. :crazy:

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

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

SQLRNNR - Tuesday, April 18, 2017 1:36 PM
Jeff Moden - Monday, April 17, 2017 3:47 PM
Heh... gee... you mean that my 4 column table doesn't need 16 indexes to cover every eventuality? 😛
You better create some indexes to help improve the write operations too.
No joke - I had a client that had numerous indexes created for write performance improvement. :crazy:

If the writes are updates or deletes that have to locate the rows they're going to operate on, that can actually work.

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: 281324 More actions · Answer 5

GilaMonster - Tuesday, April 18, 2017 1:50 PM
SQLRNNR - Tuesday, April 18, 2017 1:36 PM
Jeff Moden - Monday, April 17, 2017 3:47 PM
Heh... gee... you mean that my 4 column table doesn't need 16 indexes to cover every eventuality? 😛
You better create some indexes to help improve the write operations too.
No joke - I had a client that had numerous indexes created for write performance improvement. :crazy:
If the writes are updates or deletes that have to locate the rows they're going to operate on, that can actually work.

Yes the row location, it should help with that "read" aspect. The rest of the story is there was always a duplicate index for the read performance too. Removing the "write" indexes helped improve performance many times over. Not to mention the additional gains in free space in the database.

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

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

SQLRNNR - Tuesday, April 18, 2017 2:30 PM
The rest of the story is there was always a duplicate index for the read performance too.

Headdesk

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

Eric M Russell SSC Guru Points: 125255 More actions · Answer 7

SQLRNNR - Tuesday, April 18, 2017 1:36 PM
Jeff Moden - Monday, April 17, 2017 3:47 PM
SQLRNNR - Monday, April 17, 2017 10:41 AM
Eric M Russell - Friday, April 14, 2017 4:40 PM
SQLRNNR - Friday, April 14, 2017 3:45 PM
Eric M Russell - Friday, April 14, 2017 10:03 AM
What you don't want to do is add too many indexes upfront which won't be used.
You mean 999 indexes is not a goal for every table? :Whistling:
For most tables, 3 or 4 is too many.
Agreed
Heh... gee... you mean that my 4 column table doesn't need 16 indexes to cover every eventuality? 😛
You better create some indexes to help improve the write operations too.
No joke - I had a client that had numerous indexes created for write performance improvement. :crazy:

Too bad unused indexes don't eventually drop off automatically the same way that execution plans do.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Eric M Russell - Wednesday, April 19, 2017 7:02 AM
SQLRNNR - Tuesday, April 18, 2017 1:36 PM
Jeff Moden - Monday, April 17, 2017 3:47 PM
SQLRNNR - Monday, April 17, 2017 10:41 AM
Eric M Russell - Friday, April 14, 2017 4:40 PM
SQLRNNR - Friday, April 14, 2017 3:45 PM
Eric M Russell - Friday, April 14, 2017 10:03 AM
What you don't want to do is add too many indexes upfront which won't be used.
You mean 999 indexes is not a goal for every table? :Whistling:
For most tables, 3 or 4 is too many.
Agreed
Heh... gee... you mean that my 4 column table doesn't need 16 indexes to cover every eventuality? 😛
You better create some indexes to help improve the write operations too.
No joke - I had a client that had numerous indexes created for write performance improvement. :crazy:
Too bad unused indexes don't eventually drop off automatically the same way that execution plans do.

I'm actually very happy that they don't. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Eric M Russell SSC Guru Points: 125255 More actions · Answer 9

Jeff Moden - Wednesday, April 19, 2017 7:49 AM
Eric M Russell - Wednesday, April 19, 2017 7:02 AM
Too bad unused indexes don't eventually drop off automatically the same way that execution plans do.
I'm actually very happy that they don't. 😉

Every feature I suggest as potentially useful gets added in the next release, but I'm sure it will be optional, and they won't make it the default option.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho