Reading the 'Estimated Execution Plan'

[snakehips2000]

Can someone pease tell what the "Estimated Row Size" in Query Analyzer is? I'd presumed it was the number of columns , but the figure says 315 and I only have 10 columns in my query!

I'm looking at it because my query takes 3 minutes to return 800,000 rows. I've got a clustered index on a single field in the table which the Execution Plan says it is using. Is 3 mins what I should expect? (By the way, The OPTION(FAST, number ofrows) command in the SELECT statement doesn't seem to have any effect).

 

[SQLBill]

Rows are the horizontal bits of data, one item from each column:


Column1 Column2 Column3
Row1 Row1 Row1
Row2 Row2 Row2

Estimated Row Size is usually the amount of rows QA 'thinks' it will return due to that part of the query.

-SQLBill

 

[snakehips2000]

So if its one from each column, how come it says 315 when I have only 10 columns in my query?

 

[SQLBill]

You have 10 COLUMNS but how many ROWS do you have? When you run a query you really aren't retrieving COLUMNS, you are retrieving ROWS, but only the data in the ROWS that are from the COLUMNS you request.

myTable

ID LName Fname MI AGE
1 smith bill w 31
2 jones cathy l 21
3 hardy john j 20
4 fleming bill w 44

SELECT LName, Fname
FROM myTable
WHERE AGE < 35

Returns two COLUMNS (LName and Fname) but three ROWS.

LName Fname
smith bill
jones cathy
hardy john

Does that explain it better?

-SQLBill

 

[SQLSister]

POst your select statement and we can look to see if there is a way to optimize it.

One thing to consider is if the index field is the best one for this particular query.

 

[snakehips2000]

Here is a sample of my problem. First the SPROC:
CREATE PROCEDURE aTest
AS
SELECT Supplier
FROM WorkTables.dbo.twOldProductsReport
GO

The table contains 856,846 rows and has a clustered index, IXSupplier (based on 1 field, Supplier). This is the only index on the table.

When I run the SPROC FROM Query Analyzer, it take 17 secs to return the recordset. After that, If I look at the Estimated Execution Plan,it says:

Clustered Index Scan
Estimated Row Count 856,846
Estimated Row Size 106
Estimated I/O Cost 6.9
Estimated CU Cost 0.471
Estimated No. of Executions 1
Estimated Cost 14.735 (99%)
Estimated Sub-Tree Cost 14.7
Argument: OBJECT: WorkTables.dbo.twOldProductsReport.IXSupplier

Needless to say as I add more field to the query, it takes longer to return the recordset with the result that my requirement of 10 fields ends up taking over 3 minutes. Is this the best I can expect to achieve?

 

[DBADoug]

How much memory, how many CPU's, and what is their speed, and what type of disk subsystem are you using? If you run the query immediately after runnihg it for a second try, does the query take the same amount of time to run the second time?

I'm thinking your server just may not be up to snuff to execute this any faster than it is doing, no matter how you optimize it. Since the table is clustered, you're eliminating the need for bookmark lookups.

 

[SQLBill]

How long has your database been in use?
Have you ever reindexed it? Defragged it?

Your query is doing a INDEX SCAN which means (in my understanding) that it has to 'look' through the whole index to find what you want.

It's quicker (again in my understanding) to do a INDEX SEEK.

I think your indexes might be fragmented. You can do a DBCC SHOWCONTIG to check for fragmentation. To 'fix' the indexes you have two choices DBCC REINDEX or DBCC INDEXDEFRAG.

Refer to the BOL for more information on those commands.

-SQLBill

BOL = Books OnLine = SQL Server Help. Found at Query Analyzer HELP or Start>Programs>Microsoft SQL Serve>Books OnLine.