SQL Server Performance Forum – Threads Archive

SHOWCONTIG Results looks OK BUT..

I have an ASP application with a SQL Server back-end. When the users’ query returns a large amount of data – say 3000 plus – I get a timeout error.
I’ve got non-clustered indexes on fields in the tables I’ve used in the where clause, I’ve run UPDATE STATISTICS and I ran this..The results seems to show that the table is not fragmented – which was my initial thought… Where do I go from here? Why the timeout? The error code is ASP 0113 if anyone has seen this one before. Is this error to do with SQL or am I looking in the wrong direction? TIA This is the results returned when I run DBCC SHOWCONTIG on the table: Pages Scanned 18655
Extents Scanned 2332
Extent Switches 2331
Avg. Pages per Extent 8.0
Scan Denisty [Best Count:Actual Count] 100.00%[2332:2332]
Logical Scan Fragmentation 3.16%
Extent Scan Fragmentation 18.35%
Avg. Bytes Free per Page 31.5
Avg. Page Density (full) 99.61%

Did you try, rebuild index?.
I ask because what you show is for one index only, or table itself.
Also run Profiler or, if you nlow what query is, see execution plan. Luis Martin
Moderator
SQL-Server-Performance.com

Is it sql timing out or the time to reach the asp display to the front end? Can you run the same query in QA without it timing out?

Lookup "ASP 0113" in google. It appears to be a common question and there are lots of links about it. I’m not sure which one will match your situation, and I’m not much of an asp person. Chris

Hi Chris,
It takes seconds to run in the QA. It seems to time out when it is time to display the ASP page.

quote:Originally posted by ChrisFretwell Is it sql timing out or the time to reach the asp display to the front end? Can you run the same query in QA without it timing out?

Hi Luis,
Yes, you are right. I ran this for the table I am reporting from. I also rebuilt the indexes for all the tables being used by the query. I will run Profiler. I did have a look at the execution plan but I didn’t see anything untoward.
Thank you.

quote:Originally posted by LuisMartin Did you try, rebuild index?.
I ask because what you show is for one index only, or table itself.
Also run Profiler or, if you nlow what query is, see execution plan. Luis Martin
Moderator
SQL-Server-Performance.com

Hi Chris,
I did a lookup in google however suggestions seems to say increase the value of the command/connection timeout value which is not really dealing with the issue. I can do this but the users do not want to wait 20 minutes to see the results of a query they have submitted OR as it is in my case when the timeout limit is reached, it times out without displaying any results!! Aaaarrrrgghhh!! Thanks …

quote:Originally posted by ChrisFretwell Lookup "ASP 0113" in google. It appears to be a common question and there are lots of links about it. I’m not sure which one will match your situation, and I’m not much of an asp person. Chris

How about Index Tunnign Wizard?
Luis Martin
Moderator
SQL-Server-Performance.com

It sounds like its not sql but the asp side that is causing the problem — well, how many seconds does it take in QA?
If its already lightning fast in QA then looking at sql performance tuning, indexing etc wont gain you minutes lost in getting the data out to the asp. One of the pages I saw when I googled the error talked about how to put more traces to determine exactly where the slowdown is occuring. It was asp related so I didnt really pay much attention (I’m purely sql)

]]>