Get Performance Tips Directly From SQL Server

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 » Get Performance Tips Directly From SQL Server...

36 posts, Page 1 of 4

1 2 3 4 »»»

Get Performance Tips Directly From SQL Server

Rate Topic

Display Mode

Topic Options

Author

Message

ianstirk

Posted Thursday, May 28, 2009 12:37 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 1:08 AM
Points: 47, Visits: 761

Comments posted to this topic are about the item Get Performance Tips Directly From SQL Server

Post #724827

Rampall Sona

Posted Thursday, June 11, 2009 1:40 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:59 AM
Points: 3, Visits: 137

Another way is to use :

select * from sys.dm_db_missing_index_details

Post #732805

Mark Jerrom

Posted Thursday, June 11, 2009 2:32 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 4:00 AM
Points: 12, Visits: 96

Msg 102, Level 15, State 1, Procedure dba_SearchCachedPlans, Line 27
Incorrect syntax near '.'.

Any ideas anyone? THe SQL looks good to me.

Post #732827

ianstirk

Posted Thursday, June 11, 2009 2:43 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 1:08 AM
Points: 47, Visits: 761

Hi,

The code in the article seems to run ok (just run it in myself). But the code in the resources section (perftips.sql ) has formatting errors!

Remember the routine only works on SQL Server 2005 or higher.

Thanks
Ian

Post #732830

Mark Jerrom

Posted Thursday, June 11, 2009 3:01 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 4:00 AM
Points: 12, Visits: 96

Sorted it - it was the compatibility level of the database - it was set to 80 but need to be at least 90.

Post #732840

brian.bosman

Posted Thursday, June 11, 2009 8:39 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 2:58 PM
Points: 3, Visits: 56

How does accessing this table directly compare to the indexing hints you get when you use the 'Display Estimated Execution Plan' command in Management Studio?

Post #733069

ianstirk

Posted Thursday, June 11, 2009 8:52 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 1:08 AM
Points: 47, Visits: 761

Hi Brian,

I think it is the same. For indexes you won’t see much difference other than it shows you what is missing from the all plans that have been run already across all the batches/sprocs rather than an individual batch/sproc.

As well as being useful for everyday performance maintenance, I use this routine to get a plan who’s SQL that has been running slow (perhaps the SQL takes a long time to run or I don’t have permission to run it, or I don’t know the params etc).

Hope this helps
Ian

Post #733085

tom.christopher

Posted Thursday, June 11, 2009 2:47 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, July 26, 2012 1:46 PM
Points: 2, Visits: 25

How long a period are the usecounts representing? Since db inception? Or some statistics flush?

Post #733359

ianstirk

Posted Thursday, June 11, 2009 2:56 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 1:08 AM
Points: 47, Visits: 761

Hi,

Typically the usecount represents the number of times the plan has run since it was last recompiled (maybe due to stats changing, environment variables changing, etc), or since the sql server was rebooted, or since the service has been restarted….

Thanks
Ian

Post #733364

Chris Rutherford

Posted Thursday, June 11, 2009 4:18 PM

SSC Rookie

Group: General Forum Members
Last Login: Sunday, December 16, 2012 7:29 PM
Points: 43, Visits: 228

Hi Ian,

Thanks for this, quite nice and I can already see a few databases I'd run this one against. Appreciate your post.

Kind regards,

Chris Rutherford

Post #733399

« Prev Topic | Next Topic »

36 posts, Page 1 of 4

1 2 3 4 »»»

Permissions