For some reason when I run a simple SELECT statement against a table containing ~63k rows the query will never complete, and I either have to cancel it or wait for a timeout. I'm running the queries in SQL Query Analyzer to troubleshoot a timeout problem I first noticed in an ASP application.
Trying to get all of the rows results in the timeout:
select * from calldetailrecord ORDER BY dateTimeOrigination ASC
When I limit the rows to 42,223 the query runs in 4 sec:
select TOP 42223 * from calldetailrecord ORDER BY dateTimeOrigination ASC
The COUNT() takes 0 sec to run:
select COUNT(*) from calldetailrecord
dateTimeOrigination is a non-unique index:
INDEX [NONUI_dateTimeOrigination] ON [dbo].[CallDetailRecord] ([dateTimeOrigination])
and it's actually an integer field (think UNC time in seconds).
so has anyone seen anything like this? i can't copy the table to another DB, I can't try to run more complex queries/filters against the table without running into the timeout ... it's strange. the sqlserver instance is configured to dynamically take between 0 and 256MB of physical RAM.
as i'm playing with Cisco's DB I can't upgrade to SQL2k, or re-index the table.
Have you tried running an execution plan on both queries ? How long will the query run before it returns data ? What happens when you don't use the order by clause ? While you run the long query - check out the system resources - run some performance monitors on sql server and see if anything stands out. You can also use profiler to help diagnose.
i've run the execution plan, but nothing shows up during the timeout scenarious. it's all normal (and show's 0% resources for the top clause) when it's within the 42k restriction.
the query takes 4s to run with the restriction, and will not return data in grid mode if the restriction is not on, or set higher than 42223 records. in text mode with show-results-as-processed switched on, it starts slowing down after approximately 30000 records, then just goes in fits and starts until the client times out.
the performance monitor never goes over 30% cpu utilization during this process (either way, restriction or no), and there were no real memory spikes.
i'm not that proficient with sql profiler ... all of the replication information quickly obscures the sought-after information. and i wouldn't know how to troubleshoot it if i saw it, i fear. =\
i didn't mention the replication before, though. this machine has replication set up (i'm fairly sure it's instant-update) with a subscriber SQL7 DB. could this be related to the problem? any way i could "snapshot" the data before running the query? it's weird though, that this doesn't happen with a TOP 42223 clause ...
the table contains ~60k rows, and the client times out somewhere between 6 and 8 minutes (variable). it's funny though, the client timeout property (the client is SQL Query Analyzer) is set to 0s, which means no timeout ...
Which service pack are you running ? If you have not upgraded to service pack 3, then do so. The next step you will have to roll up your sleeves and use profiler. Just start out using the transact-sql event and examine profiler while you do a simple query - next run the query that is timing out. If you are unsure of how to interpret the results, you can post them. Also, how often is this machine being updated by replication ? Which replication are you using ?
We're running SP3 ("Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3)"). The machine is running Transactional / Instant-Update replication on two separate DBs, to two separate subscribers (both are "pull" subscribers). Only one of the replication configurations relates to the DB in question.
Is there any way to filter out the replication messages, so I only get the valid information from my SQL Server Profile trace? Also, the last time I did this I *could* see the query, but it didn't provide any useful information as to what was going on behind the scenes -- just the fact that a query was submitted (no details re: how long it took to return or not-return rows). This is probably my issue, I don't know how to configure it for real troubleshooting.
Run the profiler until the timeout occurs - check the profiler and see what information returns when the timeout occurs. How often are transactions being published to this machine ? The other replicated server - does it have the same problem and does the other table that is replicated on this machine has the same problem ?