Ask Index creation principle

hi,
i want to know the basic principle to create simple index or composite
index, e.g.
Table1
col1, col2, col3, col4, col5
select * from Table1 where col1 = 0
select * from Table1 where col1 = 1 and col2 = 'testing'
select * from Table 1 where col1= 2 and col4 = 5 and col5 = 'abc'
should i create simple index on col1, col2, col4 and col5 respectively? or
create
1. a simple index on col1
2. a composite index on col1 and col2
3. a composite index on col1, col4 and col5
thanks!
Hello Mullin,
When designing an index strategy one has to find a balance. This balance
is goverened by typical workloads sent to the server. So, what you need
to do is run profiler on your machine for a typical workload - this
could be for 24 hour period, or the working day for instance.
You then need to look though your profiler trace (tracing to a table
will be ideal) for the most common queries to your database. If this
table appears regularly in your common queries, then make sure the most
popular queries are indexed.
You also need to rely on the user experience of the application using
this data. If the user experience is satisfactory then you don't need to
add indexes. Only add indexes where necessary as they add overhead.
So, from your example below, let's say that query number 2 appears the
most in your profiler trace. You could then place a composite index on
col1 and col2.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Mullin Yu wrote:
> hi,
> i want to know the basic principle to create simple index or composite
> index, e.g.
> Table1
> col1, col2, col3, col4, col5
> select * from Table1 where col1 = 0
> select * from Table1 where col1 = 1 and col2 = 'testing'
> select * from Table 1 where col1= 2 and col4 = 5 and col5 = 'abc'
> should i create simple index on col1, col2, col4 and col5 respectively? or
> create
> 1. a simple index on col1
> 2. a composite index on col1 and col2
> 3. a composite index on col1, col4 and col5
> thanks!
>

Ask Index creation principle

hi,
i want to know the basic principle to create simple index or composite
index, e.g.
Table1
col1, col2, col3, col4, col5
select * from Table1 where col1 = 0
select * from Table1 where col1 = 1 and col2 = 'testing'
select * from Table 1 where col1= 2 and col4 = 5 and col5 = 'abc'
should i create simple index on col1, col2, col4 and col5 respectively? or
create
1. a simple index on col1
2. a composite index on col1 and col2
3. a composite index on col1, col4 and col5
thanks!Hello Mullin,
When designing an index strategy one has to find a balance. This balance
is goverened by typical workloads sent to the server. So, what you need
to do is run profiler on your machine for a typical workload - this
could be for 24 hour period, or the working day for instance.
You then need to look though your profiler trace (tracing to a table
will be ideal) for the most common queries to your database. If this
table appears regularly in your common queries, then make sure the most
popular queries are indexed.
You also need to rely on the user experience of the application using
this data. If the user experience is satisfactory then you don't need to
add indexes. Only add indexes where necessary as they add overhead.
So, from your example below, let's say that query number 2 appears the
most in your profiler trace. You could then place a composite index on
col1 and col2.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Mullin Yu wrote:
> hi,
> i want to know the basic principle to create simple index or composite
> index, e.g.
> Table1
> col1, col2, col3, col4, col5
> select * from Table1 where col1 = 0
> select * from Table1 where col1 = 1 and col2 = 'testing'
> select * from Table 1 where col1= 2 and col4 = 5 and col5 = 'abc'
> should i create simple index on col1, col2, col4 and col5 respectively? or
> create
> 1. a simple index on col1
> 2. a composite index on col1 and col2
> 3. a composite index on col1, col4 and col5
> thanks!
>

Ask Index creation principle

hi,
i want to know the basic principle to create simple index or composite
index, e.g.
Table1
col1, col2, col3, col4, col5
select * from Table1 where col1 = 0
select * from Table1 where col1 = 1 and col2 = 'testing'
select * from Table 1 where col1= 2 and col4 = 5 and col5 = 'abc'
should i create simple index on col1, col2, col4 and col5 respectively? or
create
1. a simple index on col1
2. a composite index on col1 and col2
3. a composite index on col1, col4 and col5
thanks!Hello Mullin,
When designing an index strategy one has to find a balance. This balance
is goverened by typical workloads sent to the server. So, what you need
to do is run profiler on your machine for a typical workload - this
could be for 24 hour period, or the working day for instance.
You then need to look though your profiler trace (tracing to a table
will be ideal) for the most common queries to your database. If this
table appears regularly in your common queries, then make sure the most
popular queries are indexed.
You also need to rely on the user experience of the application using
this data. If the user experience is satisfactory then you don't need to
add indexes. Only add indexes where necessary as they add overhead.
So, from your example below, let's say that query number 2 appears the
most in your profiler trace. You could then place a composite index on
col1 and col2.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Mullin Yu wrote:
> hi,
> i want to know the basic principle to create simple index or composite
> index, e.g.
> Table1
> col1, col2, col3, col4, col5
> select * from Table1 where col1 = 0
> select * from Table1 where col1 = 1 and col2 = 'testing'
> select * from Table 1 where col1= 2 and col4 = 5 and col5 = 'abc'
> should i create simple index on col1, col2, col4 and col5 respectively? or
> create
> 1. a simple index on col1
> 2. a composite index on col1 and col2
> 3. a composite index on col1, col4 and col5
> thanks!
>

ASC/DESC Clustered Index - will it make a difference in this scenario?

Imagine the following scenario-

Identity(1,1) column ID is primary key and only clustered index key.

Rows will be inserted regularly into this table, hundreds per day.

Queries will be mostly selecting on the most recent records.

In a year, the row will have half a million records or so and only the most recent records will be used. There will be a forward-rolling hot spot, of most recent records.

Does the direction of the ID column in the clustered index make a difference?

I'm thinking no, because query plan will go to that leaf in an index seek regardless of whether it is old or new, "bottom" or "top" of index, especially if the query is very specific on the ID.

I've read this

http://mattadamson.blogspot.com/2005/05/choosing-between-ascending-or.html

but it didn't address (or perhaps didn't need to) this sort of scenario.
You would get the hot spot regardless of the direction of the sort. I do question why you would create this key as a clustered index, though. You may find better performance in the application that uses this data if another index more appropriate to the application's access is chosen for the clustered index. (Your primary key is many times not your best choice for your clustered index.)|||

Allen White wrote:

You would get the hot spot regardless of the direction of the sort. I do question why you would create this key as a clustered index, though. You may find better performance in the application that uses this data if another index more appropriate to the application's access is chosen for the clustered index. (Your primary key is many times not your best choice for your clustered index.)

Not trying to avoid the hotspot. The primary key as IDENTITY as clustered index is very oftentimes, in my experience, the best way to go. I'm not alone, check out articles by Kim Tripp among others. I understand that in this scenario the inserts and selects are all going to be happening in the hotspot, but that's where leveraging SNAPSHOT isolation can help.

I'm pretty certain that ASC/DESC won't make a difference in this scenario but I'd welcome any more input.

ASC/DESC Clustered Index - will it make a difference in this scenario?

Imagine the following scenario-

Identity(1,1) column ID is primary key and only clustered index key.

Rows will be inserted regularly into this table, hundreds per day.

Queries will be mostly selecting on the most recent records.

In a year, the row will have half a million records or so and only the most recent records will be used. There will be a forward-rolling hot spot, of most recent records.

Does the direction of the ID column in the clustered index make a difference?

I'm thinking no, because query plan will go to that leaf in an index seek regardless of whether it is old or new, "bottom" or "top" of index, especially if the query is very specific on the ID.

I've read this

http://mattadamson.blogspot.com/2005/05/choosing-between-ascending-or.html

but it didn't address (or perhaps didn't need to) this sort of scenario.
You would get the hot spot regardless of the direction of the sort. I do question why you would create this key as a clustered index, though. You may find better performance in the application that uses this data if another index more appropriate to the application's access is chosen for the clustered index. (Your primary key is many times not your best choice for your clustered index.)|||

Allen White wrote:

You would get the hot spot regardless of the direction of the sort. I do question why you would create this key as a clustered index, though. You may find better performance in the application that uses this data if another index more appropriate to the application's access is chosen for the clustered index. (Your primary key is many times not your best choice for your clustered index.)

Not trying to avoid the hotspot. The primary key as IDENTITY as clustered index is very oftentimes, in my experience, the best way to go. I'm not alone, check out articles by Kim Tripp among others. I understand that in this scenario the inserts and selects are all going to be happening in the hotspot, but that's where leveraging SNAPSHOT isolation can help.

I'm pretty certain that ASC/DESC won't make a difference in this scenario but I'd welcome any more input.

Arithabort side affects

i recently needed to add an index to a computed column. I know its risky and that the side affects can be quite a pain (the cursorlocation on an adodb recordset setting caught me out for a start!) but i am struggling with scheduled jobs.
Stored procedures that are called from my applications have either been altered to have the required quoted_identifier and/or arithabort settings changed to comply with the new index, or tha app itself calls the set statement on the connection object just prior to the call to the sproc and this seems to work OK, but any sheduled job that references the table will not execute, even a one line exec sproc statement will not execute. It happily runs in QA, but never runs as part of a job because of the arithabort settings. I've tried changing them in the sproc, deleting and re-creating the sproc, i've added them as statements as part of the job step just before the call to the sproc and nothing lets the job execute. What do I do now as i need these jobs to run and i do not want to havre to remove the index.

DaveAlter database test
set arithabort on

This works, ie the scheduled jobs will now run. Can i expect anything untoward to jump out of the closet now with this change at the database level?

Dave