Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Get Performance Tips Directly From SQL Server Expand / Collapse
Author
Message
Posted Thursday, May 28, 2009 12:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 10:03 PM
Points: 48, Visits: 836
Comments posted to this topic are about the item Get Performance Tips Directly From SQL Server
Post #724827
Posted Thursday, June 11, 2009 1:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 1:25 AM
Points: 3, Visits: 142
Another way is to use :

select * from sys.dm_db_missing_index_details

Post #732805
Posted Thursday, June 11, 2009 2:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:50 AM
Points: 24, Visits: 142
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
Posted Thursday, June 11, 2009 2:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 10:03 PM
Points: 48, Visits: 836
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
Posted Thursday, June 11, 2009 3:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:50 AM
Points: 24, Visits: 142
Sorted it - it was the compatibility level of the database - it was set to 80 but need to be at least 90.
Post #732840
Posted Thursday, June 11, 2009 8:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 6, 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
Posted Thursday, June 11, 2009 8:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 10:03 PM
Points: 48, Visits: 836
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
Posted Thursday, June 11, 2009 2:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, June 11, 2009 2:56 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 10:03 PM
Points: 48, Visits: 836
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
Posted Thursday, June 11, 2009 4:18 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 1:51 PM
Points: 43, Visits: 251
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 »

Add to briefcase 1234»»»

Permissions Expand / Collapse