Methods of checking performance

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Methods of checking performance

Author

Topic

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2010-01-06 : 14:24:13

I would like to check the performance implications of how a view is scripted. I have a view that summarises data from several other tables. Some fields can be scripted in one of three ways :-

A function
A sub-query
An Apply clause

I would like to find a tool that will report on performance for these different options. At the moment we just have SQL Server Management Studio - perhaps there is something within this or do we need a separate tool?

thanks for your thoughts,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-06 : 15:07:51

I don't quite understand your question, but you can compare the performance of different queries that produce the same result set by running them in the same batch and comparing the execution plans. This can be done within SSMS.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 00:53:08

As Tara suggested, you can make use execution plan in SSMS to get an idea of query costs. you can enable option 'display actual execution plan' from top menu.

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2010-01-07 : 05:03:12

Thank you very much Tara and Visakh - I have checked using the execution plan - and am new to performance tuning so I now have another question or two (sorry...) Given the script and the timings copied below
1 What can I do to improve the performance
2 It appears from the execution plan subtree costs that the Function is the quickest BUT if you monitor the execution time to return the results this takes 00:00:01 to return 1500 records - the other methods are noticeably quicker and show an execution time of 00:00:00 seconds. This compounds in the script for the full view (about 50 columns) which takes 6 seconds to return 1500 records. Why is the function slower but has the lowest subtree cost?
3 I expected the Apply clause to be quicker than the sub-query - although the script is almost identical - perhaps it is only quicker in a real situation by virtue of using it once (select * ...) rather then multiple sub-queries (one for each column) - please can you comment.

I hope I have provided enough information - please get back to me if anything is not clear

I have compared 4 scripts. -The first is just a straight select of the primary key from the People Table - the next three produce the same data using 2) An Apply clause 3) A sub-query 4) A function

SELECT P.PEOPLE_ID AS People_ID FROM People P

SELECT JCSCROA.ReportsToA AS FKLineManager FROM People P
OUTER APPLY (SELECT Top 1 J.* FROM JobDetail J
      WHERE J.People_id = P.People_id AND J.Currentrecord = 'Yes' AND J.SCRPRIMARYROLE = 'T' 
      ORDER BY J.DateCommencementInJob desc) JCSCROA

Select (SELECT Top 1 J.ReportsToA FROM JobDetail J
      WHERE J.People_id = P.People_id AND J.Currentrecord = 'Yes' AND J.SCRPRIMARYROLE = 'T' 
      ORDER BY J.DateCommencementInJob desc) AS FKLineManager FROM People P

SELECT [dbo].[Test](P.people_id)As FKLineManager FROM People P

The execution plan gives the following information
- - - - - - - - - - - - - - - - - - - - - - - - - - Totals
Straight Select from table
- - - -Index Scan OC_IX_KnownAs
- - - - 0.0101227 - - - - - - - - - - - - - - - - 0.0101227

Apply Clause
- - - -Sort Top N Sort - -Index Spool Eager Spool - - Clustered Index Scan [IX_JobDetail]
- - - - 95% - - - - - - - - - - - 3% - - - - - - - - - -2% - - - - - - - - - - - - - - - - - - 100.00%
- - - - 17.935 - - - - - - - - - 0.832032 - - - - - -0.269681 - - - - - - - - - - - - - - 19.036713

Sub-Query
- - - -Sort Top N Sort - - Index Spool Eager Spool - Clustered Index Scan [IX_JobDetail]
- - - - 95% - -- - - - - - - 3% - - - - - - - - - - - - - - 2% - - - - - - - - - - - - - - - - - - - 100.00%
- - - - 17.9348 - - - - - - 0.831854 - - - - - - - - - - 0.269681 - - - - - - - - - - - - - - - 19.036335

Function
- - - - Compute Scalar - - Index Scan OC_IX_People_KnonwAs
- - - - 1% - - - - - - - - - - 99%- - - - - - - - - - - - - - - - - - - - - - 100.00%
- - - - 0.0102732 - - - - - - 0.0101227- - - - - - - - - - - - - - - - - - - 0.0203959

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources