http://www.sqlmag.com/authors/author/author/5777736/rsscomment/5777736en-USFri, 25 May 2012 09:08:54 GMTFri, 25 May 2012 09:08:54 GMThttp://www.sqlmag.com/article/tsql3/quickly-find-your-worst-performing-t-sql-statements#commentsAnchorThu, 11 Feb 2010 15:49:38 GMT
Very useful stored proc. Thanks.]]>TreaThu, 11 Feb 2010 15:49:38 GMThttp://www.sqlmag.com/article/tsql3/quickly-find-your-worst-performing-t-sql-statements#commentsAnchorhttp://www.sqlmag.com/article/tsql3/quickly-find-your-worst-performing-t-sql-statements#commentsAnchorTue, 17 Mar 2009 00:24:21 GMT
The "Total" columns, like "total_logical_reads", or "total_logical_writes" are accumulated counts based on all executions. So in order to create the average counts per execution is why I divided by the execution_count. You do bring up a good point you want to not be concerned about commands that have only been executed a few times. You really want to focus in and tune those worst T-SQL commands that have executed 1000’s of times, and tune those.]]>GregTue, 17 Mar 2009 00:24:21 GMThttp://www.sqlmag.com/article/tsql3/quickly-find-your-worst-performing-t-sql-statements#commentsAnchorhttp://www.sqlmag.com/article/tsql3/quickly-find-your-worst-performing-t-sql-statements#commentsAnchorFri, 06 Feb 2009 12:21:01 GMT
I don’t understand why you would divide by execution count. Shouldn’t you multiply by exec count so that you get the most expensive queries that are run the highest number of times? Finding expensive queries that are only executed a few times is much less useful than finding even slightly expensive queries that run thousands of times per day.]]>VincentFri, 06 Feb 2009 12:21:01 GMThttp://www.sqlmag.com/article/tsql3/quickly-find-your-worst-performing-t-sql-statements#commentsAnchorhttp://www.sqlmag.com/article/tsql3/quickly-find-your-worst-performing-t-sql-statements#commentsAnchorThu, 11 Dec 2008 16:33:31 GMT
Hi Greg, Do you know why I get the error below when trying to compile the proc in SQL 2005? I beleive I have the code posted by Karen as I just pulled it today. Thanks. Msg 195, Level 15, State 10, Procedure usp_Worst_TSQL, Line 81 ’OBJECT_SCHEMA_NAME’ is not a recognized built-in function name.]]>DurendalThu, 11 Dec 2008 16:33:31 GMThttp://www.sqlmag.com/article/tsql3/quickly-find-your-worst-performing-t-sql-statements#commentsAnchorhttp://www.sqlmag.com/article/tsql3/quickly-find-your-worst-performing-t-sql-statements#commentsAnchorSun, 30 Nov 2008 20:41:24 GMT
Nice script. Had some strange results (statement column was NULL) when I ran the code against a 3rd party DB. Seems these SPs were encypted.]]>JaySun, 30 Nov 2008 20:41:24 GMThttp://www.sqlmag.com/article/tsql3/quickly-find-your-worst-performing-t-sql-statements#commentsAnchorhttp://www.sqlmag.com/article/tsql3/quickly-find-your-worst-performing-t-sql-statements#commentsAnchorWed, 26 Nov 2008 11:39:14 GMT
The StatisticsReturned.xls file does not appear to be included in 100201.zip.]]>KevinWed, 26 Nov 2008 11:39:14 GMThttp://www.sqlmag.com/article/tsql3/quickly-find-your-worst-performing-t-sql-statements#commentsAnchorhttp://www.sqlmag.com/article/tsql3/quickly-find-your-worst-performing-t-sql-statements#commentsAnchorTue, 11 Nov 2008 14:57:59 GMT
What period of time is collected for the report?]]>PeterTue, 11 Nov 2008 14:57:59 GMThttp://www.sqlmag.com/article/tsql3/quickly-find-your-worst-performing-t-sql-statements#commentsAnchorhttp://www.sqlmag.com/article/tsql3/quickly-find-your-worst-performing-t-sql-statements#commentsAnchorTue, 11 Nov 2008 08:24:18 GMT
I checked with Greg Larsen about the compatibility mode. He notes that this code works only on SQL Server 2005 databases in compatibility mode 90. In addition, I posted new code on Nov. 10 that takes care of the issue cdeaton found. Karen Bemowski, senior editor SQL Server Magazine, Windows IT Pro]]>KarenTue, 11 Nov 2008 08:24:18 GMThttp://www.sqlmag.com/article/tsql3/quickly-find-your-worst-performing-t-sql-statements#commentsAnchorhttp://www.sqlmag.com/article/tsql3/quickly-find-your-worst-performing-t-sql-statements#commentsAnchorTue, 11 Nov 2008 03:41:39 GMT
Nice article. I tested it on our datamart and discoverd some nice facts.]]>RonaldTue, 11 Nov 2008 03:41:39 GMThttp://www.sqlmag.com/article/tsql3/quickly-find-your-worst-performing-t-sql-statements#commentsAnchorhttp://www.sqlmag.com/article/tsql3/quickly-find-your-worst-performing-t-sql-statements#commentsAnchorTue, 04 Nov 2008 13:38:30 GMT
I think your db has to be in 90 compatibility mode in order to do this. Can anybody confirm?]]>Leigh AnneTue, 04 Nov 2008 13:38:30 GMThttp://www.sqlmag.com/article/tsql3/quickly-find-your-worst-performing-t-sql-statements#commentsAnchorhttp://www.sqlmag.com/article/tsql3/quickly-find-your-worst-performing-t-sql-statements#commentsAnchorTue, 04 Nov 2008 12:16:43 GMT
This needs to be created in the the master database, or at least that works.]]>CHARLESTue, 04 Nov 2008 12:16:43 GMThttp://www.sqlmag.com/article/tsql3/quickly-find-your-worst-performing-t-sql-statements#commentsAnchorhttp://www.sqlmag.com/article/tsql3/quickly-find-your-worst-performing-t-sql-statements#commentsAnchorTue, 04 Nov 2008 12:16:32 GMT
There appears to be an issue that I am looking into now. Msg 102, Level 15, State 1, Procedure usp_Worst_TSQL, Line 99 Incorrect syntax near ’.’.]]>CHARLESTue, 04 Nov 2008 12:16:32 GMThttp://www.sqlmag.com/article/tsql3/quickly-find-your-worst-performing-t-sql-statements#commentsAnchorhttp://www.sqlmag.com/article/tsql3/avoiding-the-red-zone#commentsAnchorThu, 22 Mar 2007 17:10:48 GMT
Its an very, very helpful article... In conjunction with "EXEC master..xp_fixeddrives" you can have a completly track of your Databases and harddrives spaces. Thanks Greg!!]]>GerardoThu, 22 Mar 2007 17:10:48 GMThttp://www.sqlmag.com/article/tsql3/avoiding-the-red-zone#commentsAnchorhttp://www.sqlmag.com/article/tsql3/avoiding-the-red-zone#commentsAnchorTue, 11 Jul 2006 12:03:41 GMT
Very good!]]>LOUISTue, 11 Jul 2006 12:03:41 GMThttp://www.sqlmag.com/article/tsql3/avoiding-the-red-zone#commentsAnchorhttp://www.sqlmag.com/article/permissions/generating-new-sql-server-logins#commentsAnchorTue, 09 Nov 2004 10:03:45 GMT
exactly what I was looking for.]]>MarkTue, 09 Nov 2004 10:03:45 GMThttp://www.sqlmag.com/article/permissions/generating-new-sql-server-logins#commentsAnchorhttp://www.sqlmag.com/article/permissions/generating-new-sql-server-logins#commentsAnchorThu, 21 Oct 2004 06:26:26 GMT
Excellect tool that should have come from MS.]]>JERRYThu, 21 Oct 2004 06:26:26 GMThttp://www.sqlmag.com/article/permissions/generating-new-sql-server-logins#commentsAnchorhttp://www.sqlmag.com/article/permissions/generating-new-sql-server-logins#commentsAnchorWed, 03 Mar 2004 10:02:41 GMT
This article personally saved me several hous of effort while adding roughly 50 NT logins to a SQL Server, even including the time to examine and slightly modify the code. Thanks for the effort!]]>Stuart Spern, MCDBA, MCSE Wed, 03 Mar 2004 10:02:41 GMThttp://www.sqlmag.com/article/permissions/generating-new-sql-server-logins#commentsAnchorhttp://www.sqlmag.com/article/tsql3/avoiding-the-red-zone#commentsAnchorMon, 03 Mar 2003 22:57:15 GMT
This article is a gem! The author was kind enough to revert to me during my implementation. Bravo Greg!]]>PatrickMon, 03 Mar 2003 22:57:15 GMThttp://www.sqlmag.com/article/tsql3/avoiding-the-red-zone#commentsAnchorhttp://www.sqlmag.com/article/tsql3/avoiding-the-red-zone#commentsAnchorWed, 05 Feb 2003 15:58:55 GMT
I’m unsuccessful in implementing this idea in my environment. I’m sure I’m probably doing something wrong, but when I try to run the jobs in Enterprise Manager, they just fail. Admittedly, I’m pretty new to manual jobs and osql syntax, but I don’t see anything out of the ordinary. It isn’t creating the output files as expected, and I can’t see anything wrong. Anyone got any suggestions for implementing this in other environments? Thanks, Dave]]>DaveWed, 05 Feb 2003 15:58:55 GMThttp://www.sqlmag.com/article/tsql3/avoiding-the-red-zone#commentsAnchor