Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase «««123

Targeted Index Performance Improvements Expand / Collapse
Posted Friday, August 3, 2012 6:00 AM


Group: General Forum Members
Last Login: Tuesday, June 16, 2015 2:34 AM
Points: 424, Visits: 60
Very well written.
Post #1339756
Posted Friday, August 3, 2012 7:31 AM

Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, September 16, 2016 12:22 PM
Points: 3,849, Visits: 72,507
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
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1339821
Posted Friday, August 3, 2012 7:58 AM



Group: General Forum Members
Last Login: Thursday, September 22, 2016 4:11 AM
Points: 193, Visits: 967
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.

part of the proc.

Every day is a school day, and don't trust anyone who tells you any different.
Post #1339849
Posted Tuesday, August 7, 2012 8:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 7, 2016 2:04 PM
Points: 7, Visits: 124
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 »

Add to briefcase «««123

Permissions Expand / Collapse