April 12, 2006 at 1:55 pm
We have SQL Server 2000 EE running on Windows 2000 Advanced Server. When we run "select top 10 * from X" , it takes 4 to 15 minutes to complete and it was taking only couple of seconds last week.
This table X has 100 million rows and is around 172GB in size.
I ran the update statistics on the table, still it takes the same amount of time.
I am now running DBCC defrag against this table , may be that will help. Do you have any other suggestions / ideas. Cannot run DBCC reindex because it is blocking all the users.
Thanks in advance.
April 12, 2006 at 2:41 pm
Have you tried the alternative of : "SET ROWCOUNT 10" & then running your select to see if it makes a difference?!
Has your table grown considerably since last week (that's a HUGE table)?! Is it possible to archive some of it?!
**ASCII stupid question, get a stupid ANSI !!!**
April 12, 2006 at 3:20 pm
Also, do you have a lot of other activity on the server. Could be a blocking issue is preventing you from pulling any faster. Also try using sp_upatestats, a large database like this may go a lon tim before it occurrs even with the auto upate stats option turned on and can make poor index choices if stats are too stale.
April 12, 2006 at 3:22 pm
Is your select as plain vanilla as you say? Are you using DISTINCT/WHERE/ORDER/GROUP?
April 12, 2006 at 3:49 pm
There is no DISTINCT/WHERE/ORDER/GROUP. It's just a simple "Select top 10 * from table X"
Autoupdatestats is already enabled and I tried running "set rowcount 10 select * from table X". But it still takes the same amount of time.
This table earlier had 150 million rows and we archived 50 million rows 2 week ago.
I cannot archive further until next year.
April 12, 2006 at 3:59 pm
Again I woul run sp_updatestats to see if it causes any impact. However with that said have you tried looking at the estimated execuion plan to see if anything jumps out? Also, can you describe the server hardware and the database layout across any drive arrays so we can get a better feel for the beast you are trying to tame?
April 13, 2006 at 1:24 am
Hi,
If you take 50 million rows out of the table then I would suspect that the stats will need updating. The execution plan may even tell you that the stats are missing or need updating.
Nevertheless, a couple of questions I would also ask.
Are other queries on that table running more slowly?
What happens if you run the query with the NOLOCK hint, does it run significantly faster?
SELECT TOP 10 * FROM Table x WITH (NOLOCK)
David
If it ain't broke, don't fix it...
April 13, 2006 at 4:04 am
Try using UPDATE STATISTICS tablename WITH FULLSCAN. sp_updatestats uses either the last sample size, or the default (calculated internally). Then dbcc freeprocache to toss out existing plan.
April 13, 2006 at 7:22 am
While I totally agree with everyone that you should update the statistics after a large archive, I disagree that it will have anything to do with this problem. The query is just trying to bring back 10 rows, any 10 rows. Normally these can be found in cache already, and if not then a handful of IOs should do it.
What is the execution plan? I'm guessing a clustered index scan or a table scan, either of which will stop after 10 rows. In QA turn on server statistics, how many reads does the query do?
What happens if you add a where clause that hits a nonclustered index? Then select just the indexed columns instead of *, does it get better? Did the defrag help at all? Is the table clustered, and if so how does the cluster relate to your archive selection?
April 13, 2006 at 8:46 am
I ran "SELECT TOP 10 * FROM TableX WITH (NOLOCK)" and it ran in 5 seconds.
Why it executed so fast? Any ideas.
Thanks for all suggestions.
April 13, 2006 at 9:11 am
Nobody is doing any updates on TableX currently, therefore I don't understand why ""SELECT TOP 10 * FROM TableX WITH (NOLOCK)" executed so quickly.
April 13, 2006 at 9:20 am
Look at "Current Activity" in SQL Eneterprise Manager and see what else is going on in the database.
April 13, 2006 at 9:56 am
There are no updates against the table X, but "select top 10 from table X with (nolock)" executed in 5 seconds. I don't understand why?
Hardware description: It's an HP DL760 with 8GB RAM, 8 CPU's and attached to SAN. It's not a cluster.
Execution plan of ""select top 10 from table X" shows clustered index scan and it does not say statistics are stale.
April 13, 2006 at 10:08 am
If it runs quickly using with ( nolock ) then try this. Execute the original query without the lock hint and in another window execute the following:
create table #locks
(
spid SMALLINT ,
status NCHAR(30),
login NCHAR(128),
hostname NCHAR(128),
blkby CHAR(5) ,
dbname NCHAR(128),
command NCHAR(16),
cputime INTEGER ,
diskiO INTEGER ,
lastbatch varchar(20) ,
programname NCHAR(128),
spid2 SMALLINT
)
insert into #locks
exec sp_who2
select * from #locks
where ltrim( rtrim(blkby) ) <> '.'
drop table #locks
If the select returns any rows you have blocking occuring. Then you should be able to isolate the cause of the blocking.
Hope this helps,
dab
April 13, 2006 at 11:10 am
I ran trace when I am excuting "Select top 10 * from tableX" and I captured all the Lock events.
In the profiler I am seeing "Lock:Acquired" and "Lock:Released" event continously. I don't see any other events. I stopped the trace after almost 100,000 rows.
Can somebody suggest what's going on?
I tried running the same query against other databases that has data for the same table for older years, and there the profiler shows "Lock:Acquired" and "Lock:Released" event 10 to 15 times and I get the results from them quickly.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply