September 1, 2009 at 7:39 am
Great. Now which of those queries is the slow one?
From management studio you can script table and you can script indexes. Don't think they can be done as one step.
The output of sp_help on the table should be adequate.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2009 at 8:02 am
Gila,
sp_help returns a whole bunch of grids.
Can I get results as Text?
September 1, 2009 at 8:12 am
I assumed you can figure out what are the slowest parts from that STATISTICS output.
Am I wrong?
Anyway,
I didn't run all the pieces of code separately again today but from
what I remember from yesterday's experience I think the slowest blocks are
those where I query [CrediSpreadCurve] view.
One of the tables in that view, [CurveQuote] has 63 million records.
I think the slowest parts are:
Row number from 'DSR_Validation.xls"
------------------------------------
82
204
222
September 1, 2009 at 9:49 am
riga1966 (9/1/2009)
Gila,sp_help returns a whole bunch of grids.
Can I get results as Text?
Sure, switch output to text rather than grid.
You can use STATISTICS TIME to see which queries take what time. I detailed the technique here - http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2009 at 11:14 am
I used SET STATISTICS ON option
and it returned:
...
SQL Server Execution Times:
CPU time = 63 ms, elapsed time = 88 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 63 ms, elapsed time = 64 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 718 ms, elapsed time = 920 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 9 ms, elapsed time = 9 ms.
...
How do I know which CPU time refers to which SQL statement?
September 1, 2009 at 11:19 am
Count the queries or add PRINT statements.
Did you check the articles I referenced? I'm sure this is mentioned in one of them
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2009 at 11:57 am
I am stll not sure how to count queries.
Here is what I mean.
This query has 4 lines:
print 'location=1'
select count(*) from BondSpread where COB = '8/31/2009'
print 'location=2'
select count(*) from BondSpread where COB > '8/26/2009'
With SET STATISTICS TIME ON it returns these results:
SQL Server parse and compile time:
CPU time = 110 ms, elapsed time = 113 ms.
location=1
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 121 ms, elapsed time = 121 ms.
location=2
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 93 ms, elapsed time = 122 ms.
It looks like it diisplays results for 5 queries.
So how do you match a query and CPU/Elapsed time?
September 1, 2009 at 12:38 pm
riga1966 (9/1/2009)
It looks like it diisplays results for 5 queries.
No, 4 queries.
The first is listed as parse and compile time, so it's the time to parse and compile the entire batch.
The second is the execution time of the print statement
The third is the execution time of the first query
The fourth is the execution time of the second print
The fifth is the execution time of the second query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2009 at 1:07 pm
I inserted prints everywhere.
For example, this query
if @statsFlag = 1 print '\\\ insert #CreditSpreadCurve_subset'
insert
#CreditSpreadCurve_subset
(COB,
Instrument,
Spread)
select
COB,
Instrument,
Spread
from
CreditSpreadCurve
where
COB = @COB1 or COB = @COB2
if @statsFlag = 1 print '\\\ insert #CreditSpreadCurve_subset \\\ end'
produced these STATS:
\\\ insert #CreditSpreadCurve_subset
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 855 ms, elapsed time = 855 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
\\\ insert #CreditSpreadCurve_subset \\\ end
So the Execution Time for this query is 855 ms?
Is it the right approach overall?
September 1, 2009 at 1:16 pm
riga1966 (9/1/2009)
So the Execution Time for this query is 855 ms?Is it the right approach overall?
Yup. The first execution time was for the first print. The second was for the query, the third was for the second print.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2009 at 1:17 pm
I updated the ATTACHMENTS post.
Go to:
http://www.sqlservercentral.com/Forums/Topic780116-360-2.aspx
and look for the post that starts with:
ATTACHMENTS. UPDATED!
-------------------------
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply