June 3, 2008 at 9:27 am
I generally look to scans & reads as the primary measure of how well a procedure or statement is performing, but when you're in the GUI, looking at the information the gui gives you just seems useful somehow.
Except for table variables, I've never seen it radically off in most cases, but then I'm usually not working on subtle issues. More often than not, the process that says it's using 98% of the total cost, is.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 3, 2008 at 9:54 am
I would use SET STATISTICS IO ON and execute in SSMS. Also use profiler to get good IO metrics as well as CPU, duration, rowcounts. Use the stored procedure RPC completed and TSQL batch completed events.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 3, 2008 at 11:07 am
TheSQLGuru (6/3/2008)
I would use SET STATISTICS IO ON and execute in SSMS. Also use profiler to get good IO metrics as well as CPU, duration, rowcounts. Use the stored procedure RPC completed and TSQL batch completed events.
Yep.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 3, 2008 at 6:16 pm
Grant Fritchey (6/3/2008)
Amen to table variables and bad statistics. Especially table variables. I'd still love to see what Jeff's got on the subject.
I'm still trying to find the example with the 2%/98% thing where the 98% ran about a bazillion times faster than the 2% thing. I did, however, find an old example that, I believe, I gave Gail for her blog about 6 or 8 months ago. These two items have an identical execution plan and both take 50% of the batch according to the estimated execution plan and actual execution plan...
SET STATISTICS TIME ON
SELECT TOP 10 *
FROM dbo.JBMTest
ORDER BY SomeLetters2
SET ROWCOUNT 10
SELECT *
FROM dbo.JBMTest
ORDER BY SomeLetters2
SET ROWCOUNT 0
SET STATISTICS TIME OFF
Just in case you need the table I test against, here's my ol' workhorse...
--===== Create and populate a 1,000,000 row test table.
-- Column "RowNum" has a range of 1 to 100,000 unique numbers
-- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers
-- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings
-- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers
-- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times
-- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'
-- for all rows.
-- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)
-- Jeff Moden
SELECT TOP 1000000
SomeID = IDENTITY(INT,1,1),
SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,
SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),
SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),
SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
SomeHex12 = RIGHT(NEWID(),12)
INTO dbo.JBMTest
FROM Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN
--===== A table is not properly formed unless a Primary Key has been assigned
-- Takes about 1 second to execute.
ALTER TABLE dbo.JBMTest
ADD PRIMARY KEY CLUSTERED (SomeID)
I'll keep looking for that other example...
--Jeff Moden
Change is inevitable... Change for the better is not.
June 3, 2008 at 6:19 pm
Sorry... forgot to post the results from above...
[font="Courier New"]SQL Server Execution Times:
CPU time = 2469 ms, elapsed time = 2628 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 8593 ms, elapsed time = 10209 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.[/font]
Pretty big difference for execution plans that are identical...
--Jeff Moden
Change is inevitable... Change for the better is not.
June 3, 2008 at 7:17 pm
Haven't found the precise example I'm looking for, yet, but I did find this one... it's why so many people think recursion is a good thing... 😉
SET NOCOUNT ON
--=============================================================================
-- Recursive CTE does the count
--=============================================================================
PRINT '========== Recursive CTE =========='
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @Top INT
SET @Top = 100000
;WITH cteTally
AS (
SELECT 1 AS N
UNION ALL
SELECT N+1 FROM cteTally WHERE N<@Top
)
SELECT N
INTO #Test1
FROM cteTally
OPTION (MAXRECURSION 0)
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',100)
go
--=============================================================================
-- ROW_NUMBER CTE does the count
--=============================================================================
PRINT '========== ROW_NUMBER CTE =========='
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @Top INT
SET @Top = 100000
;WITH cteTally
AS (
SELECT TOP(@Top) ROW_NUMBER() OVER(ORDER BY sc1.Type) AS N
FROM Master.dbo.spt_Values sc1,
Master.dbo.spt_Values sc2
)
SELECT *
INTO #Test2
FROM cteTally
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',100)
GO
DROP TABLE #Test1,#Test2
Execution plans say the recursive CTE takes 37% of the batch and that the Row_Number CTE takes 63%... but if you look at the results, nothing could be further from the truth...
[font="Courier New"]========== Recursive CTE ==========
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'Worktable'. Scan count 2, logical reads 600001, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 4828 ms, elapsed time = 14743 ms.
====================================================================================================
========== ROW_NUMBER CTE ==========
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'spt_values'. Scan count 2, logical reads 18, physical reads 5, read-ahead reads 12, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 157 ms, elapsed time = 233 ms.
====================================================================================================
[/font]
If I can ever find the example I had when the poorest performer was 2% and the best was 98%, I post it... but, in the meantime, this example and the previous one should explain why I say that I never trust the % of batch as an indication of performance. I'm like everyone else... I used to... but not any more... I don't even use it as a "starting point" ESPECIALLY if I'm troubleshooting someone else's code for performance.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 4, 2008 at 5:42 am
Sweet. Thanks for posting that Jeff.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 4, 2008 at 7:24 am
Heh... you should see how a Cursor or While loop pans out... thin lines aren't always a good thing on the execution plan... 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
June 4, 2008 at 11:38 am
Yes, there are plenty of places where the batch percent is just dead wrong. I'm more thinking in terms of simple select/update/delete commands, where you have multiple of those in one proc.
For example, if a select command has two tables and a view in the From clause, and the two tables are listed as 10% each, and the view as 80%, I have to say, I'm gonna take a look at the view first, before I start digging into the indexes, fragmentation, stats, etc, for the two tables. Will it be the right place to start every single time? Nope. Will it save me time more often than not? In my experience, yes.
But I don't even look at the execution plan stats if I see a table variable, recursive CTE, inline function (in Select, Where, Order By, Group By, Having), or more than one copy of the same derived table. Even looking at the stats in those cases is a complete waste of time.
Like all tools, I have learned to find it useful in certain situations, and useless in others. I'd never use a chisel to pound in a nail, but that certainly doesn't mean chisels are useless. (I've used a blowtorch for carving, and once had to use a brick to pound nails, but I don't recommend either of them.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 4, 2008 at 11:45 am
IO Timing stats have always been the best in my experience.
Just follow your statements line by line and see how long they are taking against how long you expect them to take. Generally the problem can be identified quickly and is usually a matter of a table scan being run instead of a clustered index seek.
June 4, 2008 at 3:51 pm
One technique I use when tunning is to make the sp a query then slowly add the lines as I run it again and again. Seems to make it easier to troubleshoot at least at the beginning.
Honestly though after it gets to big I usually just run profile against it.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply