Targeted Index Performance Improvements

Question

Targeted Index Performance Improvements

Viewing 9 posts - 16 through 23 (of 23 total)

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

ianstirk Ten Centuries Points: 1310 More actions · Answer 1

Hi,

Your results suggest, for the passed parameters, only certain indexes are being used. I'm sorry I can't give a more detailed answer, but I don't know the specifics of system you are using.

It might be worthwhile running the routine with some other stored procedures.

It might also be worthwhile running SQL profiler with your stored procedure, to determine what individual SQL statements within your stored procedure are being executed.

Thanks

Ian

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Thanks for the article.

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

Squig SSC-Addicted Points: 433 More actions · Answer 3

Thanks for sharing this, your article is well written and very easy to follow as is the output of the sproc. Any tool that can join the toolbox of a DBA is always welcome.

mark.pointon SSC Enthusiast Points: 190 More actions · Answer 4

Just wanted to extend my thanks for this. I like what its doing but I think that for my purpose I'm running it over a week. I've stored some results for the intial select and i'll do the same select and run a comparison next week. This gives me more change to capture all activity across business critical databases.

Thanks

Bryant McClellan SSCarpal Tunnel Points: 4397 More actions · Answer 5

If you with to do longer term analysis, save the results so that you can measure deltas. Also remember that restarting the instance resets the counters to 0.

------------
Buy the ticket, take the ride. -- Hunter S. Thompson

Neha05 Default port Points: 1494 More actions · Answer 6

Neha05

Default port

Points: 1494

August 3, 2012 at 6:00 am

#1521329

Very well written.

mtassin SSC-Insane Points: 23099 More actions · Answer 7

rja.carnegie (9/17/2009)
I need to read this article carefully, but my immediate reaction is that the indexes that aren't used or don't even exist are the ones that should be considered for attention. And that's what the Tuning Wizard is for - that, and statistics. I will admit that I do not yet "get" statistics.
I am often surprised when I design tables with carefully chosen indexes and SQL Server ignores them, whereas if I was still programming a database "row by agonising row" I myself would use the index strategy I designed for the query. Maybe it's the statistics. I look at the Execution Plan and I'm like, "Why aren't you using the nice index I made for you!" Incidentally, I'm single.

This normally happens for one of two reasons.

1. The index doesn't cover the query 100% and the amount of data being queried is large enough that the index won't help the query.

2. You use non SARG-able statements in the where clause or the Join criteria. Things like DATEADD, DATEDIFF, LEFT, RIGHT, SUBSTRING, etc in the where clause cause SQL to start table or at best NC index scanning.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Ness SSCarpal Tunnel Points: 4282 More actions · Answer 8

Hi Guys,

Nice script but I would not advise putting this on a production box due to the SQL injection risk of the

-- Execute passed SQL.

EXEC (@SQLToRun)

part of the proc.

:w00t:

SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk

Eddie Kramer Valued Member Points: 55 More actions · Answer 9

Include usage of indexes on views?

Something like:

Change: INNER JOIN sys.tables st ON st.[object_id] = si.[object_id]

To: INNER JOIN sys.objects st ON st.[object_id] = si.[object_id] AND st.type IN ('U','V')

Your script will be of use to me. Thanks.