Strange Query Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Strange Query Problem

Author

Topic

MarcoMarco
Starting Member

2 Posts

Posted - 2009-09-29 : 10:47:15

Hi,

I have a table with around 300k records and a stored procedure that performs 2 queries on this table. The first counts the number of records matching a certain critera and the second will return the first 5000 Ids(integers) thats matches that criteria.

One specific set of parameters for this query returns a count of 35k and the first 5000 Ids. I run the query and its fine and it stays fine for a period of time(around 2 to 3 months), then it just hangs and eventually times out no matter how many times I try it or even if it try it 2 to 3 days after.
I go to the stored procedure and get a script that modifies it, make no changes and execute it so it updates. The query is then fine again for a few months.

This is the 4th times its happened and it always happenes when im passing the same set of parameters. A different combination of parameters are fine.

It looks to be like something gets cached and the cache gets removed when I update the procedure.

The query im using is as below:

SELECT @TotalContacts = COUNT(*)
FROM Table u
JOIN
(
SELECT UserId
FROM Table2
WHERE AgentId = @AgentId
)AS c ON u.UserId = c.UserId
WHERE u.AgentId = @AgentId

SET ROWCOUNT @MaxResults;

SELECT u.UserId
FROM Table u
JOIN
(
SELECT UserId
FROM Table2
WHERE AgentId = @AgentId
) AS c ON u.UserId = c.UserId
WHERE u.AgentId = @AgentId
ORDER BY DateCreated DESC

Can anyone help?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-29 : 10:52:16

declare vars and set them to the value of your given parameters and use them in your queries, that should solve your problem.
It is called <parameter sniffing>.

No, you're never too old to Yak'n'Roll if you're too young to die.

MarcoMarco
Starting Member

2 Posts

Posted - 2009-09-29 : 11:08:22

Thanks,

I'll give this ago.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources