Effect of indexes in SQL Server Performance Tuning

Question

Post reply

Effect of indexes in SQL Server Performance Tuning

VSSGeorge

SSCrazy Eights

Points: 8146
More actions
June 7, 2013 at 10:23 pm

#274894

I have a couple of questions.
a) What is the effect of indexes in SQL Server Performance Tuning?
b) I have a SQL statement below, which is running slow. How to tune the query?
Select col1, col2 from Table T
Where col2 = ‘Sydney’
Here col1 is the primary key, col2 is having a non clustered index on it.

Viewing 11 posts - 1 through 10 (of 10 total)

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

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

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

http://www.amazon.com/Server-Query-Performance-Tuning-ebook/dp/B008E6HOIS/

and indexing

http://www.sqlservercentral.com/articles/Indexing/68439/

http://www.sqlservercentral.com/articles/Indexing/68563/

http://www.sqlservercentral.com/articles/Indexing/68636/

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

Dird SSCertifiable Points: 5081 More actions · Answer 2

GilaMonster (6/8/2013)
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Wow, great articles 😮 didn't know about server-side traces, can't wait to test on prod ^_^ a couple questions though:

1. You said you were going to add the modified date to the index but ended creating a completely new index (different name); does that mean the old index was no longer required?

2. Why did the sort go? Wouldn't the index have been sorted on the id and date asc? While the query wants date desc

3. I assume individual select statements of SQL:StmtCompleted would also be found in SQL:BatchCompleted?

Dird

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

Dird (6/8/2013)
1. You said you were going to add the modified date to the index but ended creating a completely new index (different name); does that mean the old index was no longer required?

I did add the modified date to the index. There's no ALTER INDEX ... ADD COLUMN, so to add a column to an index, you drop the index and recreate it, and I like my index names to list the key columns, hence different name.

2. Why did the sort go? Wouldn't the index have been sorted on the id and date asc? While the query wants date desc

SQL is perfectly capable of doing a backwards scan of an index

3. I assume individual select statements of SQL:StmtCompleted would also be found in SQL:BatchCompleted?

A batch is a set of one or more statements.

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

Dird SSCertifiable Points: 5081 More actions · Answer 4

GilaMonster (6/8/2013)
I did add the modified date to the index. There's no ALTER INDEX ... ADD COLUMN, so to add a column to an index, you drop the index and recreate it, and I like my index names to list the key columns, hence different name.

OK I went back & saw I misread it. I assumed you were going to have an index on ForumID, CreatedBy, LastPoster, LastModified.

GilaMonster (6/8/2013)
SQL is perfectly capable of doing a backwards scan of an index

Thanks for the info 😮

Dird

Dird SSCertifiable Points: 5081 More actions · Answer 5

Gail, does it only write out to file at the end of the trace or is it just because I'm not stressing my instance enough?

Dird

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 6

Trace results are buffered so yes, sometimes there will be captured results that have not yet been written to the file. Check the column "event count" in sys.traces to see how many events your trace has captured while it is running. When you stop the trace or the buffer fills, data will be written to disk.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

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

No, it writes continuously.

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

Dird SSCertifiable Points: 5081 More actions · Answer 8

GilaMonster (6/8/2013)
No, it writes continuously.

Only if you fill the buffer it seems.

buffer_countbuffer_sizefile_positionreader_spidstart_timelast_event_timeevent_count

2128131072NULL2013-06-08 16:43:42.0602013-06-08 16:48:40.92316

Mine runs for 20 mins then goes from 0kb to 128kb since I just ran like 5 queries. I guess 128kb would fill up fast in a real environment though :hehe:

What I'm surprised at is that it doesn't empty the buffer when you stop/pause the trace (exec sp_trace_setstatus 2, 0)

Dird

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 9

Maybe not stop, try clearing the trace, that will definitely force a flush to disk.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Dird SSCertifiable Points: 5081 More actions · Answer 10

opc.three (6/8/2013)
Maybe not stop, try clearing the trace, that will definitely force a flush to disk.

Yep. "sp_trace_setstatus 2, 2" sorted it.

Dird