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 1 of 3

1 2 3 »»»

Targeted Index Performance Improvements

Rate Topic

Display Mode

Topic Options

Author

Message

ianstirk

Posted Thursday, September 17, 2009 12:20 AM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 6:56 AM
Points: 47, Visits: 780

Comments posted to this topic are about the item Targeted Index Performance Improvements

Post #789418

MattTheDBA

Posted Thursday, September 17, 2009 2:53 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 15, 2013 7:02 AM
Points: 190, Visits: 227

If this is run on a production server, would other queries interfer with the results. I assume because you are doing a snapshot, you do not want anything else to be running at the time?

Post #789460

ianstirk

Posted Thursday, September 17, 2009 3:11 AM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 6:56 AM
Points: 47, Visits: 780

Hi Mathew,

The utility records all index activity on the database, not just that which belongs to the SQL you’re running.

So it might be better to schedule the utility to run at a time when you know yours is the only SQL running, or run it on a stand-alone database where you can ensure the only SQL running is yours.

Hope this helps
Ian

Post #789473

MattTheDBA

Posted Thursday, September 17, 2009 3:17 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 15, 2013 7:02 AM
Points: 190, Visits: 227

Cheers

Post #789478

Toby White

Posted Thursday, September 17, 2009 7:45 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, November 09, 2012 2:48 PM
Points: 493, Visits: 636

I am trying to identify the advantage of using this utility versus an "Actual Execution Plan". It seems to give all the same information about which indexes where used, scans/seeks and row count. The execution plan has the additional advantage of only showing stats for the query in question.

Post #789649

ianstirk

Posted Thursday, September 17, 2009 8:05 AM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 6:56 AM
Points: 47, Visits: 780

Hi Toby,

I would hope the output from this utility is much easier to read and interpret, with consolidated results for a batch of SQL. Additionally, the ‘Actual Executing Plan’ option can take quite a lot of resources and time to produce (and interpret).

Also, there may be times when you want the results from all the SQL running on the box over a given time period, in this case reading and consolidating the individual plans would be troublesome.

That said, you’ve quite rightly identified a degree of overlap.

Thanks
Ian

Post #789665

rja.carnegie

Posted Thursday, September 17, 2009 9:42 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, May 16, 2011 5:32 PM
Points: 76, Visits: 151

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.

Post #789759

Mister Sachmo

Posted Thursday, September 17, 2009 12:09 PM

Valued Member

Group: General Forum Members
Last Login: Friday, May 24, 2013 9:35 AM
Points: 56, Visits: 129

so this proc needs to be installed in each database where you want to run it?

Post #789890

ianstirk

Posted Thursday, September 17, 2009 12:16 PM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 6:56 AM
Points: 47, Visits: 780

Hi,

yes the code uses sys.indexes, which is database specific, so it needs to run in each database separately.

Thanks
Ian

Post #789900

JJ B

Posted Thursday, September 17, 2009 3:30 PM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 259, Visits: 2,424

Thank you for sharing your utility, taking the time to explain how it works, and going over the strengths and weaknesses. As you replied to a previous poster, your results are easy to read. This is a nice tool to add to the toolbox.

Thanks!

Post #790008

« Prev Topic | Next Topic »

24 posts, Page 1 of 3

1 2 3 »»»

Permissions