May 21, 2008 at 2:22 pm
Hello All,
I'm working with some very large databases. Since some tables have gotten into the billions of rows, my queries have often timed out after 30 seconds even though "no time out" has been set on the server and in my local management studio.
The only way I can run those queries locally is in the New Database Engine Query Window not the regular Query window.
All my work is in .sql files but they only open as regular queries.
Does anyone know how to open .sql files as Database Engine Queries?
I appreciate the help!
Skål - jh
May 22, 2008 at 6:01 am
If your query execution timeout is set to zero ("0") in Management Studio, you shouldn't be seeing a 30 second timeout. Are these queries crossing servers in some way that would cause them to become involved with the Remote Procedure Calls? They have a different timeout setting on the server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 22, 2008 at 6:36 am
Yup query time out is set to zero in managemetn studio and on the server itself. Not cross-server, just remote from my local machine. And not all queries time out, but things like count(*) or more than top(100) against the larger tables crap out right away.
They will run on the server, or remotely using Database Engine Query instead of the regular one.
The whole team cannot term. serv. into the server at once to run dev queries, so it would just be helpful if our working .sql files could be opened in Database Engine windows rather than having to copy and paste the problem queries around to run them remotely.
Thanks for the thought we'll check RPC settings as well, our DBAs out at the data center do not expreience this and are stumped too, they wonder if it's a network thing to our office but we cannot find any solid clues.
Skål - jh
May 22, 2008 at 6:52 am
Sorry I wasn't helpful. It's just not inherent in the tool. We have a large number of developers & dba's hitting a large number of servers. I sometimes wish we could set timeouts just so that fewer of the really stupid queries would be allowed to run forever, but they do run forever (well, until they chew up enough resources that they trip an alert or someone complains about being blocked or getting deadlocks... you know). If it's not a key part of the tool, it has to be somewhere else in the topology. If you weren't using SSMS, I'd just assume it was the .NET default of 30 seconds.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 22, 2008 at 7:09 am
Any hint helps, you never know. We'll look at those RCP settings too.
This is a strange one.
Skål - jh
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply