Targeted Index Performance Improvements

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ian Stirk » Targeted Index Performance Improvements

24 posts, Page 3 of 3

«««1 2 3

Targeted Index Performance Improvements

Rate Topic

Display Mode

Topic Options

Author

Message

Neha05

Posted Friday, August 03, 2012 6:00 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, April 15, 2013 11:42 PM
Points: 424, Visits: 55

Very well written.

Post #1339756

mtassin

Posted Friday, August 03, 2012 7:31 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 7:50 PM
Points: 3,226, Visits: 64,188

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
For tips on how to post your problems

Post #1339821

Ness

Posted Friday, August 03, 2012 7:58 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:35 AM
Points: 123, Visits: 612

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

Post #1339849

Eddie Kramer

Posted Tuesday, August 07, 2012 8:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 25, 2013 7:48 AM
Points: 7, Visits: 73

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.

Post #1341310

« Prev Topic | Next Topic »

24 posts, Page 3 of 3

«««1 2 3

Permissions