Query Plans & Statistics

Gurus,

I'm trying to get an application finished that works like Query Analizer in
terms of returning query plans and statistics.

Problem the co-author is having:

>In using ADO to connect to SQL Server, I'm trying to retrieve multiple
>datasets AND statistics that are usually returned via the OnInfoMessage
>event. For those that are familiar with SQL Server, I need the results
>returned by the SET STATISTICS IO ON and SET STATISTICS PROFILE ON options.
>Anyone had any luck doing this before?

Can anyone shed any light on this please?

Thanks.

BTW if anyone wants to take a look at the tool so far - to see what I'm
delving into:
http://81.130.213.94/myforum/forum_posts.asp?TID=78&PN=1

Much Appreciated!!How about Object Browser pane? This one comes handy very often, you know?|||I'm not sure what you mean by Object Browser Pane - unless you mean something in Microsoft Query analizer...??

The problem I have is that this is a new tool that doesn't have any panes..just pains...arghhh.

Thanks for the help though!|||Here's some more info from the original post

http://81.130.213.94/myforum/forum_posts.asp?TID=26&PN=1

SET STATISTICS IO ON
SELECT 1 FROM sometable

IO statistics are returned as messages in the 2nd (blank) recordset. Good.

SET STATISTICS PROFILE ON
SELECT 1 FROM sometable

Execution plan is returned in the 2nd recordset. Good.

SET STATISTICS IO ON
SET STATISTICS PROFILE ON
SELECT 1 FROM sometable

2 recordsets are returned, the 2nd containing the execution plan. But the
IO statistics are nowhere to be found. Help!

SET STATISTICS IO ON
SET STATISTICS PROFILE ON
SELECT 1 FROM sometable
PRINT 1

3 recordsets are returned. The 2nd one contains the execution plan, the
third is blank but contains '1' as a info message. The IO statistics are
nowhere to be found. Help!sql

Query Performance SQL Server7.0 with SP2

Hi Gurus
I m using SQL Server7.0 with SP2 on Compaq Prolient ML310 PIV 2.2Ghz
Now the problem is when i m executing the "select * statment " on a table with 21000 rows it is taking ard 1min to result for the same.and if i m using the same query without any service pack the result is coming in 4-5 Secs. but with the base system ( without service pack) my system get freeze frequently.
REPLY URGENT
TIASunil
I'd recommend you to apply SP3.
Do you have WHERE clause in your query?
Do you really need all columns from the table (I mean why SELECT * )?
"Sunil Dara" <anonymous@.discussions.microsoft.com> wrote in message
news:9B88D9D5-67A9-467C-BF9F-A7DFDFE32688@.microsoft.com...
> Hi Gurus
> I m using SQL Server7.0 with SP2 on Compaq Prolient ML310 PIV 2.2Ghz
> Now the problem is when i m executing the "select * statment " on a table
with 21000 rows it is taking ard 1min to result for the same.and if i m
using the same query without any service pack the result is coming in 4-5
Secs. but with the base system ( without service pack) my system get freeze
frequently.
> REPLY URGENT
> TIA

Query Performance

Hello SQL Gurus,
From the query below, I am using 2 TOP functions to return the desired row. I am wondering if someone can shed some light on how to AVOID using 2 TOP statements and combine into just one select query?

select TOP 1 * from (select TOP 2 Num from A order by Num) X order by Num desc

Truly Appreciate your help as this performance issue has been bugging in my head for quite some time...

Sincerely,
-Lawrence

You could write it like below:

select min(num)

from A

where num > (select min(num) from A)

-- or

select max(num) from (select top 1 num from A order by num) X

But your TOP query should perform fine if you have an index on Num column. Can you compare above queries with yours and see if there is any difference? You can compare the execution plan in query analyzer.

Query Performance

Hi Gurus,

I have run DBCC SHOWCONTIG statement against the table X as shown below:

DBCC SHOWCONTIG (X)
GO

and received the following result:

DBCC SHOWCONTIG scanning 'X' table...
Table: 'X' (885578193); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 1400
- Extents Scanned.......................: 300
- Extent Switches.......................: 300
- Avg. Pages per Extent..................: 4.7
- Scan Density [Best Count:Actual Count]......: 50.00% [200:400]
- Logical Scan Fragmentation ..............: 21.43%
- Extent Scan Fragmentation ...............: 33.33%
- Avg. Bytes Free per Page................: 2107.1
- Avg. Page Density (full)................: 73.97%

Which one of the following is more recommended way to increase query performance against the table X?

A. Run DBCC DBREINDEX statement with high fill factor value to rebuild the clustered index.
B. Run DBCC DBREINDEX statement with low fill factor value to rebuild the clustered index.
C. Drop and re-create the clustered index by using DROP INDEX and CREATE INDEX statements with low fill factor value.
D. Drop and re-create the clustered index by using DROP INDEX and CREATE INDEX statements with high fill factor value.
E. Set the truncate log on checkpoint option for the database which contain the Employee table.

Thank youIf they are asking for high query performance, then you don't want a low fill factor, you want as much data per data page. That being said, I would choose (A). (D) would be OK too but it is one more process as you would drop the clustered, recreate it, then any non-clustereds would be rebuilt, that's three steps instead of two.

HTH|||HTH,

Thanks very much.