When you run a process on you Windows machine, you can set the process
priority to 'low', if you don't want the process to take resources away form
other processes running in the same timeframe.
Does anyone know of a similar technique for long running queries on Sql
server 2000 or 2005?
I have a series on long running analytical queries, where the query
execution time is not important, and I donâ't want them to take any resources
away form other queries running in the same timeframe.
I would like a way to tell the Sql server â' â'run this, but if anything else
happens, give that the first priority!â'
Any suggestions?pehuan
I'm afraid you cannot manage it. Perhaps it's time to start an optimization
of the long running queries.
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don?t want them to take any
resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server ? ?run this, but if anything
else
> happens, give that the first priority!?
> Any suggestions?
>
>|||Sorry, but i don´t think there is a way to do this.
Jens SUessmeyer.
"pehuan" <pehuan@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
> form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don't want them to take any
> resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server - "run this, but if anything
> else
> happens, give that the first priority!"
> Any suggestions?
>
>|||I know that DB2 has something called the â'Query Patrollerâ' that can be useed
to proactively and dynamically control the flow of queries against your DB2
database in the following key ways:
a. Define separate query classes for queries of different sizes to better
share system resources among queries and to prevent smaller queries from
getting stuck behind larger ones
b. Give queries submitted by certain users high priority so that these
queries run sooner
c. Automatically put large queries on hold so that they can be cancelled or
scheduled to run during off-peak hours
d. Track and cancel runaway queries
The purpose of this is to allow you to regulate your database's query
workload so that small queries and high-priority queries can run promptly and
your system resources are used efficiently.
I donâ't thing these thing are unreasonable requirements for a database
server in the enterprise. Iâ'm surprised the this seems so hard to do on Sql
server.
Have anyone looked into the features on the Sql server 2005? Any
improvements there?
Are there any of you guys that has been struggling with the same problems â'
and found a solution or a workaround?
"pehuan" wrote:
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I donâ't want them to take any resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server â' â'run this, but if anything else
> happens, give that the first priority!â'
> Any suggestions?
>
>|||Read up this article
http://www.databasejournal.com/features/mssql/article.php/3427581
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:66FFBDA0-98CA-44F3-A4ED-A3B43958C546@.microsoft.com...
> I know that DB2 has something called the ?Query Patroller? that can be
useed
> to proactively and dynamically control the flow of queries against your
DB2
> database in the following key ways:
> a. Define separate query classes for queries of different sizes to better
> share system resources among queries and to prevent smaller queries from
> getting stuck behind larger ones
> b. Give queries submitted by certain users high priority so that these
> queries run sooner
> c. Automatically put large queries on hold so that they can be cancelled
or
> scheduled to run during off-peak hours
> d. Track and cancel runaway queries
> The purpose of this is to allow you to regulate your database's query
> workload so that small queries and high-priority queries can run promptly
and
> your system resources are used efficiently.
> I don?t thing these thing are unreasonable requirements for a database
> server in the enterprise. I?m surprised the this seems so hard to do on
Sql
> server.
> Have anyone looked into the features on the Sql server 2005? Any
> improvements there?
> Are there any of you guys that has been struggling with the same
problems ?
> and found a solution or a workaround?
>
> "pehuan" wrote:
> > When you run a process on you Windows machine, you can set the process
> > priority to 'low', if you don't want the process to take resources away
form
> > other processes running in the same timeframe.
> >
> > Does anyone know of a similar technique for long running queries on Sql
> > server 2000 or 2005?
> >
> > I have a series on long running analytical queries, where the query
> > execution time is not important, and I don?t want them to take any
resources
> > away form other queries running in the same timeframe.
> >
> > I would like a way to tell the Sql server ? ?run this, but if anything
else
> > happens, give that the first priority!?
> >
> > Any suggestions?
> >
> >
> >|||Hi Uri,
Thanks for the article â' however there question here is not whether spâ's or
queries can be executed asynchronously from the application, but rather how
the machine resource consumption of particular queries can be controlled so
the wonâ't affect the execution time of other queries or spâ's running in
parallel.
Cheers
pehuan
"Uri Dimant" wrote:
> Read up this article
> http://www.databasejournal.com/features/mssql/article.php/3427581
> "pehuan" <pehuan@.discussions.microsoft.com> wrote in message
> news:66FFBDA0-98CA-44F3-A4ED-A3B43958C546@.microsoft.com...
> > I know that DB2 has something called the â'Query Patrollerâ' that can be
> useed
> > to proactively and dynamically control the flow of queries against your
> DB2
> > database in the following key ways:
> >
> > a. Define separate query classes for queries of different sizes to better
> > share system resources among queries and to prevent smaller queries from
> > getting stuck behind larger ones
> >
> > b. Give queries submitted by certain users high priority so that these
> > queries run sooner
> >
> > c. Automatically put large queries on hold so that they can be cancelled
> or
> > scheduled to run during off-peak hours
> >
> > d. Track and cancel runaway queries
> >
> > The purpose of this is to allow you to regulate your database's query
> > workload so that small queries and high-priority queries can run promptly
> and
> > your system resources are used efficiently.
> >
> > I donâ't thing these thing are unreasonable requirements for a database
> > server in the enterprise. Iâ'm surprised the this seems so hard to do on
> Sql
> > server.
> >
> > Have anyone looked into the features on the Sql server 2005? Any
> > improvements there?
> >
> > Are there any of you guys that has been struggling with the same
> problems â'
> > and found a solution or a workaround?
> >
> >
> > "pehuan" wrote:
> >
> > > When you run a process on you Windows machine, you can set the process
> > > priority to 'low', if you don't want the process to take resources away
> form
> > > other processes running in the same timeframe.
> > >
> > > Does anyone know of a similar technique for long running queries on Sql
> > > server 2000 or 2005?
> > >
> > > I have a series on long running analytical queries, where the query
> > > execution time is not important, and I donâ't want them to take any
> resources
> > > away form other queries running in the same timeframe.
> > >
> > > I would like a way to tell the Sql server â' â'run this, but if anything
> else
> > > happens, give that the first priority!â'
> > >
> > > Any suggestions?
> > >
> > >
> > >
>
>|||On a multi-processor box, you can use OPTION(MAXDOP 1) with the query, so
that it only uses 1 processor, but that is as far as you can get.
--
Jacco Schalkwijk
SQL Server MVP
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
> form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don't want them to take any
> resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server - "run this, but if anything
> else
> happens, give that the first priority!"
> Any suggestions?
>
>
Showing posts with label queries. Show all posts
Showing posts with label queries. Show all posts
Wednesday, March 28, 2012
Long running queries with low priority
When you run a process on you Windows machine, you can set the process
priority to 'low', if you don't want the process to take resources away form
other processes running in the same timeframe.
Does anyone know of a similar technique for long running queries on Sql
server 2000 or 2005?
I have a series on long running analytical queries, where the query
execution time is not important, and I don’t want them to take any resourc
es
away form other queries running in the same timeframe.
I would like a way to tell the Sql server – “run this, but if anything e
lse
happens, give that the first priority!”
Any suggestions?pehuan
I'm afraid you cannot manage it. Perhaps it's time to start an optimization
of the long running queries.
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I dont want them to take any
resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server run this, but if anything
else
> happens, give that the first priority!
> Any suggestions?
>
>|||Sorry, but i dont think there is a way to do this.
Jens SUessmeyer.
"pehuan" <pehuan@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
> form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don't want them to take any
> resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server - "run this, but if anything
> else
> happens, give that the first priority!"
> Any suggestions?
>
>|||On a multi-processor box, you can use OPTION(MAXDOP 1) with the query, so
that it only uses 1 processor, but that is as far as you can get.
Jacco Schalkwijk
SQL Server MVP
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
> form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don't want them to take any
> resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server - "run this, but if anything
> else
> happens, give that the first priority!"
> Any suggestions?
>
>
priority to 'low', if you don't want the process to take resources away form
other processes running in the same timeframe.
Does anyone know of a similar technique for long running queries on Sql
server 2000 or 2005?
I have a series on long running analytical queries, where the query
execution time is not important, and I don’t want them to take any resourc
es
away form other queries running in the same timeframe.
I would like a way to tell the Sql server – “run this, but if anything e
lse
happens, give that the first priority!”
Any suggestions?pehuan
I'm afraid you cannot manage it. Perhaps it's time to start an optimization
of the long running queries.
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I dont want them to take any
resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server run this, but if anything
else
> happens, give that the first priority!
> Any suggestions?
>
>|||Sorry, but i dont think there is a way to do this.
Jens SUessmeyer.
"pehuan" <pehuan@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
> form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don't want them to take any
> resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server - "run this, but if anything
> else
> happens, give that the first priority!"
> Any suggestions?
>
>|||On a multi-processor box, you can use OPTION(MAXDOP 1) with the query, so
that it only uses 1 processor, but that is as far as you can get.
Jacco Schalkwijk
SQL Server MVP
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
> form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don't want them to take any
> resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server - "run this, but if anything
> else
> happens, give that the first priority!"
> Any suggestions?
>
>
Long running queries with low priority
When you run a process on you Windows machine, you can set the process
priority to 'low', if you don't want the process to take resources away form
other processes running in the same timeframe.
Does anyone know of a similar technique for long running queries on Sql
server 2000 or 2005?
I have a series on long running analytical queries, where the query
execution time is not important, and I don’t want them to take any resources
away form other queries running in the same timeframe.
I would like a way to tell the Sql server – “run this, but if anything else
happens, give that the first priority!”
Any suggestions?
pehuan
I'm afraid you cannot manage it. Perhaps it's time to start an optimization
of the long running queries.
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I dont want them to take any
resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server run this, but if anything
else
> happens, give that the first priority!
> Any suggestions?
>
>
|||Sorry, but i dont think there is a way to do this.
Jens SUessmeyer.
"pehuan" <pehuan@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
> form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don't want them to take any
> resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server - "run this, but if anything
> else
> happens, give that the first priority!"
> Any suggestions?
>
>
|||On a multi-processor box, you can use OPTION(MAXDOP 1) with the query, so
that it only uses 1 processor, but that is as far as you can get.
Jacco Schalkwijk
SQL Server MVP
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
> form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don't want them to take any
> resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server - "run this, but if anything
> else
> happens, give that the first priority!"
> Any suggestions?
>
>
priority to 'low', if you don't want the process to take resources away form
other processes running in the same timeframe.
Does anyone know of a similar technique for long running queries on Sql
server 2000 or 2005?
I have a series on long running analytical queries, where the query
execution time is not important, and I don’t want them to take any resources
away form other queries running in the same timeframe.
I would like a way to tell the Sql server – “run this, but if anything else
happens, give that the first priority!”
Any suggestions?
pehuan
I'm afraid you cannot manage it. Perhaps it's time to start an optimization
of the long running queries.
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I dont want them to take any
resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server run this, but if anything
else
> happens, give that the first priority!
> Any suggestions?
>
>
|||Sorry, but i dont think there is a way to do this.
Jens SUessmeyer.
"pehuan" <pehuan@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
> form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don't want them to take any
> resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server - "run this, but if anything
> else
> happens, give that the first priority!"
> Any suggestions?
>
>
|||On a multi-processor box, you can use OPTION(MAXDOP 1) with the query, so
that it only uses 1 processor, but that is as far as you can get.
Jacco Schalkwijk
SQL Server MVP
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
> form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don't want them to take any
> resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server - "run this, but if anything
> else
> happens, give that the first priority!"
> Any suggestions?
>
>
long running queries quit - audit logout
I have a web based application that launches sql queries using ADO from
ASP/VBScript. Typically 2 - 10 queries are executed in a loop, and the
result sets are sent off to Crystal Reports at the end of the run with the
report being displayed in a browser window. In general this all works well
is has been very reliable.
Lately I've been having trouble with some of these queries on databases that
have gotten quite large (25 - 40G) but only a couple of the tables have
rowcounts in the tens of millions, most are relatively small. The behavior
is that the first query runs, sometimes for up to 90 minutes, seems to
complete, and then the pid is gone. Looking at the profile trace, I can see
that the query executed nearly 13 M reads with a duration of over 90
minutes. The line following the SQL:BatchCompleted event is Audit Logout,
and thats the last trace of the process. Under "normal" operation, the next
query would start and they would all run until the loop is complete. I've
seen this now with 2 completely different sets of queries, on 2 different
dbs, yet the symptoms are the same. It completes a very simple query that
returns a single datetime value. Then the first "real" query runs, anywhere
from 60 - 90 minutes or more, and then the process goes away.
It seems as if I've reached some threshold or internal timeout. I have the
connection time and query timeouts set to very large values (1000 minutes),
so it's not that.The client process is of course IIS. This is running SQL
Server 2000 SP3, NT 4.0, IIS 5.5.
I have had sets of queries that have taken as long as 3 hours to complete,
but each individual query probably never exceeded 45 minutes in duration.
Can anyone think of what is causing this to happen -- why does the pid go
away after the first query completes? Is there some other internal timeout
somewhere that I'm running into?
Any advice is appreciated,
-GaryYou might be experiencing lock_timeout. See if it helps:
http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_1yr8.asp
"Gary" <gld@.hotmail.com> wrote in message
news:bisSc.243431$IQ4.52808@.attbi_s02...
> I have a web based application that launches sql queries using ADO from
> ASP/VBScript. Typically 2 - 10 queries are executed in a loop, and the
> result sets are sent off to Crystal Reports at the end of the run with the
> report being displayed in a browser window. In general this all works well
> is has been very reliable.
> Lately I've been having trouble with some of these queries on databases
that
> have gotten quite large (25 - 40G) but only a couple of the tables have
> rowcounts in the tens of millions, most are relatively small. The behavior
> is that the first query runs, sometimes for up to 90 minutes, seems to
> complete, and then the pid is gone. Looking at the profile trace, I can
see
> that the query executed nearly 13 M reads with a duration of over 90
> minutes. The line following the SQL:BatchCompleted event is Audit Logout,
> and thats the last trace of the process. Under "normal" operation, the
next
> query would start and they would all run until the loop is complete. I've
> seen this now with 2 completely different sets of queries, on 2 different
> dbs, yet the symptoms are the same. It completes a very simple query that
> returns a single datetime value. Then the first "real" query runs,
anywhere
> from 60 - 90 minutes or more, and then the process goes away.
> It seems as if I've reached some threshold or internal timeout. I have the
> connection time and query timeouts set to very large values (1000
minutes),
> so it's not that.The client process is of course IIS. This is running SQL
> Server 2000 SP3, NT 4.0, IIS 5.5.
> I have had sets of queries that have taken as long as 3 hours to complete,
> but each individual query probably never exceeded 45 minutes in duration.
> Can anyone think of what is causing this to happen -- why does the pid go
> away after the first query completes? Is there some other internal timeout
> somewhere that I'm running into?
> Any advice is appreciated,
> -Gary
>
>|||Thanks for the tip. I checked a server and it's returning -1 (infinite) for
this. I'm thinking that the problem is the client connection to the database
dropping sometime during the processing of a query, and when the statement
complete event occurs, SQL detects that the connection is gone and therefore
the audit logoff event.
"oj" <nospam_ojngo@.home.com> wrote in message
news:O7lkZQ$fEHA.3632@.TK2MSFTNGP09.phx.gbl...
> You might be experiencing lock_timeout. See if it helps:
> http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_1yr8.asp
>
ASP/VBScript. Typically 2 - 10 queries are executed in a loop, and the
result sets are sent off to Crystal Reports at the end of the run with the
report being displayed in a browser window. In general this all works well
is has been very reliable.
Lately I've been having trouble with some of these queries on databases that
have gotten quite large (25 - 40G) but only a couple of the tables have
rowcounts in the tens of millions, most are relatively small. The behavior
is that the first query runs, sometimes for up to 90 minutes, seems to
complete, and then the pid is gone. Looking at the profile trace, I can see
that the query executed nearly 13 M reads with a duration of over 90
minutes. The line following the SQL:BatchCompleted event is Audit Logout,
and thats the last trace of the process. Under "normal" operation, the next
query would start and they would all run until the loop is complete. I've
seen this now with 2 completely different sets of queries, on 2 different
dbs, yet the symptoms are the same. It completes a very simple query that
returns a single datetime value. Then the first "real" query runs, anywhere
from 60 - 90 minutes or more, and then the process goes away.
It seems as if I've reached some threshold or internal timeout. I have the
connection time and query timeouts set to very large values (1000 minutes),
so it's not that.The client process is of course IIS. This is running SQL
Server 2000 SP3, NT 4.0, IIS 5.5.
I have had sets of queries that have taken as long as 3 hours to complete,
but each individual query probably never exceeded 45 minutes in duration.
Can anyone think of what is causing this to happen -- why does the pid go
away after the first query completes? Is there some other internal timeout
somewhere that I'm running into?
Any advice is appreciated,
-GaryYou might be experiencing lock_timeout. See if it helps:
http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_1yr8.asp
"Gary" <gld@.hotmail.com> wrote in message
news:bisSc.243431$IQ4.52808@.attbi_s02...
> I have a web based application that launches sql queries using ADO from
> ASP/VBScript. Typically 2 - 10 queries are executed in a loop, and the
> result sets are sent off to Crystal Reports at the end of the run with the
> report being displayed in a browser window. In general this all works well
> is has been very reliable.
> Lately I've been having trouble with some of these queries on databases
that
> have gotten quite large (25 - 40G) but only a couple of the tables have
> rowcounts in the tens of millions, most are relatively small. The behavior
> is that the first query runs, sometimes for up to 90 minutes, seems to
> complete, and then the pid is gone. Looking at the profile trace, I can
see
> that the query executed nearly 13 M reads with a duration of over 90
> minutes. The line following the SQL:BatchCompleted event is Audit Logout,
> and thats the last trace of the process. Under "normal" operation, the
next
> query would start and they would all run until the loop is complete. I've
> seen this now with 2 completely different sets of queries, on 2 different
> dbs, yet the symptoms are the same. It completes a very simple query that
> returns a single datetime value. Then the first "real" query runs,
anywhere
> from 60 - 90 minutes or more, and then the process goes away.
> It seems as if I've reached some threshold or internal timeout. I have the
> connection time and query timeouts set to very large values (1000
minutes),
> so it's not that.The client process is of course IIS. This is running SQL
> Server 2000 SP3, NT 4.0, IIS 5.5.
> I have had sets of queries that have taken as long as 3 hours to complete,
> but each individual query probably never exceeded 45 minutes in duration.
> Can anyone think of what is causing this to happen -- why does the pid go
> away after the first query completes? Is there some other internal timeout
> somewhere that I'm running into?
> Any advice is appreciated,
> -Gary
>
>|||Thanks for the tip. I checked a server and it's returning -1 (infinite) for
this. I'm thinking that the problem is the client connection to the database
dropping sometime during the processing of a query, and when the statement
complete event occurs, SQL detects that the connection is gone and therefore
the audit logoff event.
"oj" <nospam_ojngo@.home.com> wrote in message
news:O7lkZQ$fEHA.3632@.TK2MSFTNGP09.phx.gbl...
> You might be experiencing lock_timeout. See if it helps:
> http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_1yr8.asp
>
Long Running Queries in Procedure
derek@.programmer.net May 19, 12:57 pm show options
From: "d...@.programmer.net" <d...@.programmer.net> - Find messages by this
author
Date: Thu, 19 May 2005 09:57:29 -0700
Local: Thurs,May 19 2005 12:57 pm
Subject: Long Running Queries in Procedure
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse
Hi,
Has anyone ever seen a query take longer in a stored procedure than it
does when running in Query Analyzer? I have a set of about 10 queries
that run for 18 minutes when running in a schedule job (which runs a
stored procedure) but only for about 4 minutes when I run them manually
in Query Analyzer.
I'm trying to trim time out of a daily procedure that takes about 3
hours and I've already done some performance tuning - that's why I know
how long they take in Query Analyzer. However, when I time how long
the individual portions of the proc run - I get different results than
those I receive when I run the queries myself.
Any thoughts?
DerekUse SQL Server Profiler to monitor performance related events. Whatever, the
issue it will turn up there.
"Derek Fisher" <DerekFisher@.discussions.microsoft.com> wrote in message
news:E1976E3E-7CF3-4475-8600-6FEFA92370B8@.microsoft.com...
> derek@.programmer.net May 19, 12:57 pm show options
> From: "d...@.programmer.net" <d...@.programmer.net> - Find messages by this
> author
> Date: Thu, 19 May 2005 09:57:29 -0700
> Local: Thurs,May 19 2005 12:57 pm
> Subject: Long Running Queries in Procedure
> Reply | Reply to Author | Forward | Print | Individual Message | Show
> original | Remove | Report Abuse
> Hi,
>
> Has anyone ever seen a query take longer in a stored procedure than it
> does when running in Query Analyzer? I have a set of about 10 queries
> that run for 18 minutes when running in a schedule job (which runs a
> stored procedure) but only for about 4 minutes when I run them manually
> in Query Analyzer.
>
> I'm trying to trim time out of a daily procedure that takes about 3
> hours and I've already done some performance tuning - that's why I know
> how long they take in Query Analyzer. However, when I time how long
> the individual portions of the proc run - I get different results than
> those I receive when I run the queries myself.
>
> Any thoughts?
>
> Derek
>
>|||Did you looked for locks on tables ? Are your queries very "cost" intensive
or "lock" intensive ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Derek Fisher" <DerekFisher@.discussions.microsoft.com> schrieb im
Newsbeitrag news:E1976E3E-7CF3-4475-8600-6FEFA92370B8@.microsoft.com...
> derek@.programmer.net May 19, 12:57 pm show options
> From: "d...@.programmer.net" <d...@.programmer.net> - Find messages by this
> author
> Date: Thu, 19 May 2005 09:57:29 -0700
> Local: Thurs,May 19 2005 12:57 pm
> Subject: Long Running Queries in Procedure
> Reply | Reply to Author | Forward | Print | Individual Message | Show
> original | Remove | Report Abuse
> Hi,
>
> Has anyone ever seen a query take longer in a stored procedure than it
> does when running in Query Analyzer? I have a set of about 10 queries
> that run for 18 minutes when running in a schedule job (which runs a
> stored procedure) but only for about 4 minutes when I run them manually
> in Query Analyzer.
>
> I'm trying to trim time out of a daily procedure that takes about 3
> hours and I've already done some performance tuning - that's why I know
> how long they take in Query Analyzer. However, when I time how long
> the individual portions of the proc run - I get different results than
> those I receive when I run the queries myself.
>
> Any thoughts?
>
> Derek
>
>|||JT,
This will probably sound like a stupid question, but I'm unfamiliar with
using Profiler. Do you have any suggestions on how to setup the trace - wha
t
events to add, etc? If not, any suggestions on helpful articles about the
Profiler?
Derek
"JT" wrote:
> Use SQL Server Profiler to monitor performance related events. Whatever, t
he
> issue it will turn up there.
> "Derek Fisher" <DerekFisher@.discussions.microsoft.com> wrote in message
> news:E1976E3E-7CF3-4475-8600-6FEFA92370B8@.microsoft.com...
>
>|||Jens,
I'm not sure how to know. How would I tell?
Derek
"Jens Sü?meyer" wrote:
> Did you looked for locks on tables ? Are your queries very "cost" intensiv
e
> or "lock" intensive ?
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Derek Fisher" <DerekFisher@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:E1976E3E-7CF3-4475-8600-6FEFA92370B8@.microsoft.com...
>
>|||You can use the statemtent sp_lock and sp_who and sp_who2 to identify the
blockings issue (if you have one). ANother oprtion would be to monitor the
events in the profiler, just check on the lock properties to monitor. If you
are not familiar with the profiler you can look in the current activitie in
your EM, to see wheter a lock or perhaps deadlocks occured.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Derek Fisher" <DerekFisher@.discussions.microsoft.com> schrieb im
Newsbeitrag news:C80C8BFB-3227-4306-AB80-B7DB2F34B695@.microsoft.com...
> Jens,
> I'm not sure how to know. How would I tell?
> Derek
> "Jens Smeyer" wrote:
>
From: "d...@.programmer.net" <d...@.programmer.net> - Find messages by this
author
Date: Thu, 19 May 2005 09:57:29 -0700
Local: Thurs,May 19 2005 12:57 pm
Subject: Long Running Queries in Procedure
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse
Hi,
Has anyone ever seen a query take longer in a stored procedure than it
does when running in Query Analyzer? I have a set of about 10 queries
that run for 18 minutes when running in a schedule job (which runs a
stored procedure) but only for about 4 minutes when I run them manually
in Query Analyzer.
I'm trying to trim time out of a daily procedure that takes about 3
hours and I've already done some performance tuning - that's why I know
how long they take in Query Analyzer. However, when I time how long
the individual portions of the proc run - I get different results than
those I receive when I run the queries myself.
Any thoughts?
DerekUse SQL Server Profiler to monitor performance related events. Whatever, the
issue it will turn up there.
"Derek Fisher" <DerekFisher@.discussions.microsoft.com> wrote in message
news:E1976E3E-7CF3-4475-8600-6FEFA92370B8@.microsoft.com...
> derek@.programmer.net May 19, 12:57 pm show options
> From: "d...@.programmer.net" <d...@.programmer.net> - Find messages by this
> author
> Date: Thu, 19 May 2005 09:57:29 -0700
> Local: Thurs,May 19 2005 12:57 pm
> Subject: Long Running Queries in Procedure
> Reply | Reply to Author | Forward | Print | Individual Message | Show
> original | Remove | Report Abuse
> Hi,
>
> Has anyone ever seen a query take longer in a stored procedure than it
> does when running in Query Analyzer? I have a set of about 10 queries
> that run for 18 minutes when running in a schedule job (which runs a
> stored procedure) but only for about 4 minutes when I run them manually
> in Query Analyzer.
>
> I'm trying to trim time out of a daily procedure that takes about 3
> hours and I've already done some performance tuning - that's why I know
> how long they take in Query Analyzer. However, when I time how long
> the individual portions of the proc run - I get different results than
> those I receive when I run the queries myself.
>
> Any thoughts?
>
> Derek
>
>|||Did you looked for locks on tables ? Are your queries very "cost" intensive
or "lock" intensive ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Derek Fisher" <DerekFisher@.discussions.microsoft.com> schrieb im
Newsbeitrag news:E1976E3E-7CF3-4475-8600-6FEFA92370B8@.microsoft.com...
> derek@.programmer.net May 19, 12:57 pm show options
> From: "d...@.programmer.net" <d...@.programmer.net> - Find messages by this
> author
> Date: Thu, 19 May 2005 09:57:29 -0700
> Local: Thurs,May 19 2005 12:57 pm
> Subject: Long Running Queries in Procedure
> Reply | Reply to Author | Forward | Print | Individual Message | Show
> original | Remove | Report Abuse
> Hi,
>
> Has anyone ever seen a query take longer in a stored procedure than it
> does when running in Query Analyzer? I have a set of about 10 queries
> that run for 18 minutes when running in a schedule job (which runs a
> stored procedure) but only for about 4 minutes when I run them manually
> in Query Analyzer.
>
> I'm trying to trim time out of a daily procedure that takes about 3
> hours and I've already done some performance tuning - that's why I know
> how long they take in Query Analyzer. However, when I time how long
> the individual portions of the proc run - I get different results than
> those I receive when I run the queries myself.
>
> Any thoughts?
>
> Derek
>
>|||JT,
This will probably sound like a stupid question, but I'm unfamiliar with
using Profiler. Do you have any suggestions on how to setup the trace - wha
t
events to add, etc? If not, any suggestions on helpful articles about the
Profiler?
Derek
"JT" wrote:
> Use SQL Server Profiler to monitor performance related events. Whatever, t
he
> issue it will turn up there.
> "Derek Fisher" <DerekFisher@.discussions.microsoft.com> wrote in message
> news:E1976E3E-7CF3-4475-8600-6FEFA92370B8@.microsoft.com...
>
>|||Jens,
I'm not sure how to know. How would I tell?
Derek
"Jens Sü?meyer" wrote:
> Did you looked for locks on tables ? Are your queries very "cost" intensiv
e
> or "lock" intensive ?
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Derek Fisher" <DerekFisher@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:E1976E3E-7CF3-4475-8600-6FEFA92370B8@.microsoft.com...
>
>|||You can use the statemtent sp_lock and sp_who and sp_who2 to identify the
blockings issue (if you have one). ANother oprtion would be to monitor the
events in the profiler, just check on the lock properties to monitor. If you
are not familiar with the profiler you can look in the current activitie in
your EM, to see wheter a lock or perhaps deadlocks occured.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Derek Fisher" <DerekFisher@.discussions.microsoft.com> schrieb im
Newsbeitrag news:C80C8BFB-3227-4306-AB80-B7DB2F34B695@.microsoft.com...
> Jens,
> I'm not sure how to know. How would I tell?
> Derek
> "Jens Smeyer" wrote:
>
long running queries
Hi Everyone
I wanted to generate a script which will return all the queries which are running more than 5 minutes.
I want the text of the sql query so that i can tune the query if required.
I have used the profiler and have filtered the query which are running setting Duration parameter.
I actually wanted to set a automated method which will send me by mail the list of all queries which are running more than the desired time.
May be some store proc using system tables. which I will call in a job and the job will run periodically.
Thanks in advance.
You can save the results of the profiler trace to a SQL Server table, and then either ;
1) Have a trigger on the table to send an email, or
2) Use the user defined performance monitoring counters to do this - Use a SQL Agent job to periodically query the table, and set the user defined counter if the value exceeds the threshold.
Let me know if you need more details on setting up either of the above.
|||Hi Rod,
Do you know other than Profiler, is there another less resource intensive way to record the SQL statements that run longer than a certain time? I have a very critical server that is very sensitive and always has blocking issue. I would like to use the least resource intensive way to get the statements that run long thus caused the blocking. I saw an article saying that we can use ODBC trace, to save the long running queries to a log file when configure the system DSN. http://msdn2.microsoft.com/en-us/library/ms403323.aspx Do you know whether the ODBC trace is less intensive than Profiler or not?
Also, where I should set it? on the SQL server that I want to find out the long query? Just create a DSN that points to the blocking issue db and save long query to a log file? Then all the long queries will be logged to the file as long as I turn on the trace? But all user applications on that server won't use that DSN though. The ASP pages set their own connection string. What the file looks like? Do I need to use other tool to interpret the log file? I use SQL server 2000 Enterprise Edition sp4.
Thanks a lot,
-Jessie
long running queries
Hi Everyone
I wanted to generate a script which will return all the queries which are running more than 5 minutes.
I want the text of the sql query so that i can tune the query if required.
I have used the profiler and have filtered the query which are running setting Duration parameter.
I actually wanted to set a automated method which will send me by mail the list of all queries which are running more than the desired time.
May be some store proc using system tables. which I will call in a job and the job will run periodically.
Thanks in advance.
You can save the results of the profiler trace to a SQL Server table, and then either ;
1) Have a trigger on the table to send an email, or
2) Use the user defined performance monitoring counters to do this - Use a SQL Agent job to periodically query the table, and set the user defined counter if the value exceeds the threshold.
Let me know if you need more details on setting up either of the above.
|||Hi Rod,
Do you know other than Profiler, is there another less resource intensive way to record the SQL statements that run longer than a certain time? I have a very critical server that is very sensitive and always has blocking issue. I would like to use the least resource intensive way to get the statements that run long thus caused the blocking. I saw an article saying that we can use ODBC trace, to save the long running queries to a log file when configure the system DSN. http://msdn2.microsoft.com/en-us/library/ms403323.aspx Do you know whether the ODBC trace is less intensive than Profiler or not?
Also, where I should set it? on the SQL server that I want to find out the long query? Just create a DSN that points to the blocking issue db and save long query to a log file? Then all the long queries will be logged to the file as long as I turn on the trace? But all user applications on that server won't use that DSN though. The ASP pages set their own connection string. What the file looks like? Do I need to use other tool to interpret the log file? I use SQL server 2000 Enterprise Edition sp4.
Thanks a lot,
-Jessie
sqlLong Running OPENXML Query
Hi,
I have a long running OPENXML query in a SP that runs for around 7 mins, it
runs 6 queries doing inserts and updates from the XML to the DB. The XML is
around 2.5MB in size.
On my test server which has 1 physical XEON CPU, SQL Server uses 50% of both
logical processors and all works fine.
However my live server has 2 physical CPUs but we only have a single proc
license. The SQL Server is configured to use 2 logical CPUs but just the 1
physical CPU. When the long running query begins the query runs on just 1
logical processor and all other connections to the SQL server timeout.
Does anyone know how to get the query to use both logical processors at 50%
and thus allow other connections to the server?
Thanks
Jason
Hi Jason,
Thanks for your posting!
From your descriptions, I understood you can make two CPU balance the
workload in test environment but failed to do so in live machine. If I have
misunderstood your concern, please feel free to point it out.
Based on my knowlegde, please check SQL Server Properties to see whether
you are using that two logic processor in SQL Server and selected Use.
However, we do not have a way to compose the query to use both logical
processor.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||What version of SQL Server 2000 are you running? Have you upgraded to SP4
(which should give you some improvement on the OpenXML front).
Best regards
Michael
"Jason" <techno@.noemail.nospam> wrote in message
news:%23JxasEJiFHA.3960@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a long running OPENXML query in a SP that runs for around 7 mins,
> it
> runs 6 queries doing inserts and updates from the XML to the DB. The XML
> is
> around 2.5MB in size.
> On my test server which has 1 physical XEON CPU, SQL Server uses 50% of
> both
> logical processors and all works fine.
> However my live server has 2 physical CPUs but we only have a single proc
> license. The SQL Server is configured to use 2 logical CPUs but just the 1
> physical CPU. When the long running query begins the query runs on just 1
> logical processor and all other connections to the SQL server timeout.
> Does anyone know how to get the query to use both logical processors at
> 50%
> and thus allow other connections to the server?
> Thanks
> Jason
>
|||Hi
Thanks for your replies.
We are running SP4. I have rewritten the query which has improved the speed
and also used NOLOCK to avoid locks being taken out for the duration of the
transaction.
The problem I still have is that SQL Server is only using 1 logical
processor for everything that it does. On the processor tab of the SQL
Properties window I have the following settings,
CPU 0 and 1 are checked, 2 and 3 are not.=20
Max worker threads is 255
Boost SQL server priority is checked
Parallism
Use all available processors is checked
Min query plan threshold is 5
When any SQL activity occurs the Task Manager performance window shows 25%
CPU usage on the left and out of the 4 available logical processors only 1
spikes.
Any ideas why its not using 50%?
Thanks
Jason
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23KJXNUdiFHA.576@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> What version of SQL Server 2000 are you running? Have you upgraded to SP4
> (which should give you some improvement on the OpenXML front).
> Best regards
> Michael
> "Jason" <techno@.noemail.nospam> wrote in message
> news:%23JxasEJiFHA.3960@.TK2MSFTNGP12.phx.gbl...
proc[vbcol=seagreen]
1[vbcol=seagreen]
1
>
|||OpenXML is implemented as a remote provider, so I believe parallelism is
disabled for OpenXML. I'm hardly an expert on the way SQL schedules, though.
Best regards
Michael
"Jason" <techno@.noemail.nospam> wrote in message
news:O$m92M3iFHA.1968@.TK2MSFTNGP14.phx.gbl...
> Hi
> Thanks for your replies.
> We are running SP4. I have rewritten the query which has improved the
> speed
> and also used NOLOCK to avoid locks being taken out for the duration of
> the
> transaction.
> The problem I still have is that SQL Server is only using 1 logical
> processor for everything that it does. On the processor tab of the SQL
> Properties window I have the following settings,
> CPU 0 and 1 are checked, 2 and 3 are not.=20
> Max worker threads is 255
> Boost SQL server priority is checked
> Parallism
> Use all available processors is checked
> Min query plan threshold is 5
> When any SQL activity occurs the Task Manager performance window shows 25%
> CPU usage on the left and out of the 4 available logical processors only 1
> spikes.
> Any ideas why its not using 50%?
> Thanks
> Jason
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:%23KJXNUdiFHA.576@.tk2msftngp13.phx.gbl...
> proc
> 1
> 1
>
I have a long running OPENXML query in a SP that runs for around 7 mins, it
runs 6 queries doing inserts and updates from the XML to the DB. The XML is
around 2.5MB in size.
On my test server which has 1 physical XEON CPU, SQL Server uses 50% of both
logical processors and all works fine.
However my live server has 2 physical CPUs but we only have a single proc
license. The SQL Server is configured to use 2 logical CPUs but just the 1
physical CPU. When the long running query begins the query runs on just 1
logical processor and all other connections to the SQL server timeout.
Does anyone know how to get the query to use both logical processors at 50%
and thus allow other connections to the server?
Thanks
Jason
Hi Jason,
Thanks for your posting!
From your descriptions, I understood you can make two CPU balance the
workload in test environment but failed to do so in live machine. If I have
misunderstood your concern, please feel free to point it out.
Based on my knowlegde, please check SQL Server Properties to see whether
you are using that two logic processor in SQL Server and selected Use.
However, we do not have a way to compose the query to use both logical
processor.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||What version of SQL Server 2000 are you running? Have you upgraded to SP4
(which should give you some improvement on the OpenXML front).
Best regards
Michael
"Jason" <techno@.noemail.nospam> wrote in message
news:%23JxasEJiFHA.3960@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a long running OPENXML query in a SP that runs for around 7 mins,
> it
> runs 6 queries doing inserts and updates from the XML to the DB. The XML
> is
> around 2.5MB in size.
> On my test server which has 1 physical XEON CPU, SQL Server uses 50% of
> both
> logical processors and all works fine.
> However my live server has 2 physical CPUs but we only have a single proc
> license. The SQL Server is configured to use 2 logical CPUs but just the 1
> physical CPU. When the long running query begins the query runs on just 1
> logical processor and all other connections to the SQL server timeout.
> Does anyone know how to get the query to use both logical processors at
> 50%
> and thus allow other connections to the server?
> Thanks
> Jason
>
|||Hi
Thanks for your replies.
We are running SP4. I have rewritten the query which has improved the speed
and also used NOLOCK to avoid locks being taken out for the duration of the
transaction.
The problem I still have is that SQL Server is only using 1 logical
processor for everything that it does. On the processor tab of the SQL
Properties window I have the following settings,
CPU 0 and 1 are checked, 2 and 3 are not.=20
Max worker threads is 255
Boost SQL server priority is checked
Parallism
Use all available processors is checked
Min query plan threshold is 5
When any SQL activity occurs the Task Manager performance window shows 25%
CPU usage on the left and out of the 4 available logical processors only 1
spikes.
Any ideas why its not using 50%?
Thanks
Jason
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23KJXNUdiFHA.576@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> What version of SQL Server 2000 are you running? Have you upgraded to SP4
> (which should give you some improvement on the OpenXML front).
> Best regards
> Michael
> "Jason" <techno@.noemail.nospam> wrote in message
> news:%23JxasEJiFHA.3960@.TK2MSFTNGP12.phx.gbl...
proc[vbcol=seagreen]
1[vbcol=seagreen]
1
>
|||OpenXML is implemented as a remote provider, so I believe parallelism is
disabled for OpenXML. I'm hardly an expert on the way SQL schedules, though.
Best regards
Michael
"Jason" <techno@.noemail.nospam> wrote in message
news:O$m92M3iFHA.1968@.TK2MSFTNGP14.phx.gbl...
> Hi
> Thanks for your replies.
> We are running SP4. I have rewritten the query which has improved the
> speed
> and also used NOLOCK to avoid locks being taken out for the duration of
> the
> transaction.
> The problem I still have is that SQL Server is only using 1 logical
> processor for everything that it does. On the processor tab of the SQL
> Properties window I have the following settings,
> CPU 0 and 1 are checked, 2 and 3 are not.=20
> Max worker threads is 255
> Boost SQL server priority is checked
> Parallism
> Use all available processors is checked
> Min query plan threshold is 5
> When any SQL activity occurs the Task Manager performance window shows 25%
> CPU usage on the left and out of the 4 available logical processors only 1
> spikes.
> Any ideas why its not using 50%?
> Thanks
> Jason
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:%23KJXNUdiFHA.576@.tk2msftngp13.phx.gbl...
> proc
> 1
> 1
>
Monday, March 26, 2012
Long Queries Hang VS; Other Productivity Killers
Whenever I run long SQL in VS whether it's in the Data tab or in Preview
mode, I can't seem to get a report out of it. I'm talking SQL in excess of
200 lines.
I can run the SQL fine in Query Analyzer but running it in the Data tab, it
freezes VS, and when I run the Preview, the report never renders. I have even
tried hardcoding all parameters, creating a stored procedure, and running the
report off a view. None have resolved the issue.
On a possibly related note, my connection to the database times out
intermittently, saying:
--
Microsoft Development Environment
--
An error occurred while executing the query.
General network error. Check your network documentation.
--
OK
--
This absolutely kills me over the course of a day. I have to shut down VS
and restart it.
Is my Reporting Services VS package install corrupt? What do I do?How many fields are being returned? How many rows of data?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> Whenever I run long SQL in VS whether it's in the Data tab or in Preview
> mode, I can't seem to get a report out of it. I'm talking SQL in excess of
> 200 lines.
> I can run the SQL fine in Query Analyzer but running it in the Data tab,
it
> freezes VS, and when I run the Preview, the report never renders. I have
even
> tried hardcoding all parameters, creating a stored procedure, and running
the
> report off a view. None have resolved the issue.
> On a possibly related note, my connection to the database times out
> intermittently, saying:
> --
> Microsoft Development Environment
> --
> An error occurred while executing the query.
> General network error. Check your network documentation.
> --
> OK
> --
> This absolutely kills me over the course of a day. I have to shut down VS
> and restart it.
> Is my Reporting Services VS package install corrupt? What do I do?|||For one report in particular, about 727 rows, and today it's taking just over
4 minutes to run. The other has over 60,000 rows and I'm just going to have
to work on limiting that severely.
"Bruce L-C [MVP]" wrote:
> How many fields are being returned? How many rows of data?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > Whenever I run long SQL in VS whether it's in the Data tab or in Preview
> > mode, I can't seem to get a report out of it. I'm talking SQL in excess of
> > 200 lines.
> >
> > I can run the SQL fine in Query Analyzer but running it in the Data tab,
> it
> > freezes VS, and when I run the Preview, the report never renders. I have
> even
> > tried hardcoding all parameters, creating a stored procedure, and running
> the
> > report off a view. None have resolved the issue.
> >
> > On a possibly related note, my connection to the database times out
> > intermittently, saying:
> >
> > --
> > Microsoft Development Environment
> > --
> > An error occurred while executing the query.
> >
> > General network error. Check your network documentation.
> > --
> > OK
> > --
> >
> > This absolutely kills me over the course of a day. I have to shut down VS
> > and restart it.
> >
> > Is my Reporting Services VS package install corrupt? What do I do?
>
>|||The 727 rows should not be a problem. Is that one causing an issue from the
data tab?
It doesn't surprise me that the 60,000 row one has problems.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> For one report in particular, about 727 rows, and today it's taking just
over
> 4 minutes to run. The other has over 60,000 rows and I'm just going to
have
> to work on limiting that severely.
> "Bruce L-C [MVP]" wrote:
> > How many fields are being returned? How many rows of data?
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > Whenever I run long SQL in VS whether it's in the Data tab or in
Preview
> > > mode, I can't seem to get a report out of it. I'm talking SQL in
excess of
> > > 200 lines.
> > >
> > > I can run the SQL fine in Query Analyzer but running it in the Data
tab,
> > it
> > > freezes VS, and when I run the Preview, the report never renders. I
have
> > even
> > > tried hardcoding all parameters, creating a stored procedure, and
running
> > the
> > > report off a view. None have resolved the issue.
> > >
> > > On a possibly related note, my connection to the database times out
> > > intermittently, saying:
> > >
> > > --
> > > Microsoft Development Environment
> > > --
> > > An error occurred while executing the query.
> > >
> > > General network error. Check your network documentation.
> > > --
> > > OK
> > > --
> > >
> > > This absolutely kills me over the course of a day. I have to shut down
VS
> > > and restart it.
> > >
> > > Is my Reporting Services VS package install corrupt? What do I do?
> >
> >
> >|||For the most part, Mr. 727 will run on the data tab, but clocks endlessly on
Preview. The report does consist of about 20 fields, but I would still expect
it to render.
Sometimes I just lose connectivity and have to close out VS. I'm not sure if
it's related, but it could be I suppose.
Any ideas?
"Bruce L-C [MVP]" wrote:
> The 727 rows should not be a problem. Is that one causing an issue from the
> data tab?
> It doesn't surprise me that the 60,000 row one has problems.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> > For one report in particular, about 727 rows, and today it's taking just
> over
> > 4 minutes to run. The other has over 60,000 rows and I'm just going to
> have
> > to work on limiting that severely.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > How many fields are being returned? How many rows of data?
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > > Whenever I run long SQL in VS whether it's in the Data tab or in
> Preview
> > > > mode, I can't seem to get a report out of it. I'm talking SQL in
> excess of
> > > > 200 lines.
> > > >
> > > > I can run the SQL fine in Query Analyzer but running it in the Data
> tab,
> > > it
> > > > freezes VS, and when I run the Preview, the report never renders. I
> have
> > > even
> > > > tried hardcoding all parameters, creating a stored procedure, and
> running
> > > the
> > > > report off a view. None have resolved the issue.
> > > >
> > > > On a possibly related note, my connection to the database times out
> > > > intermittently, saying:
> > > >
> > > > --
> > > > Microsoft Development Environment
> > > > --
> > > > An error occurred while executing the query.
> > > >
> > > > General network error. Check your network documentation.
> > > > --
> > > > OK
> > > > --
> > > >
> > > > This absolutely kills me over the course of a day. I have to shut down
> VS
> > > > and restart it.
> > > >
> > > > Is my Reporting Services VS package install corrupt? What do I do?
> > >
> > >
> > >
>
>|||Sometimes it runs from the data tab. Sometimes (like now), it hoses VS. The
query is 309 lines long and is pretty complex...Still, I'm having a heck of a
time. Could it be my RS client install, or could it be my machine?
"Bruce L-C [MVP]" wrote:
> The 727 rows should not be a problem. Is that one causing an issue from the
> data tab?
> It doesn't surprise me that the 60,000 row one has problems.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> > For one report in particular, about 727 rows, and today it's taking just
> over
> > 4 minutes to run. The other has over 60,000 rows and I'm just going to
> have
> > to work on limiting that severely.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > How many fields are being returned? How many rows of data?
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > > Whenever I run long SQL in VS whether it's in the Data tab or in
> Preview
> > > > mode, I can't seem to get a report out of it. I'm talking SQL in
> excess of
> > > > 200 lines.
> > > >
> > > > I can run the SQL fine in Query Analyzer but running it in the Data
> tab,
> > > it
> > > > freezes VS, and when I run the Preview, the report never renders. I
> have
> > > even
> > > > tried hardcoding all parameters, creating a stored procedure, and
> running
> > > the
> > > > report off a view. None have resolved the issue.
> > > >
> > > > On a possibly related note, my connection to the database times out
> > > > intermittently, saying:
> > > >
> > > > --
> > > > Microsoft Development Environment
> > > > --
> > > > An error occurred while executing the query.
> > > >
> > > > General network error. Check your network documentation.
> > > > --
> > > > OK
> > > > --
> > > >
> > > > This absolutely kills me over the course of a day. I have to shut down
> VS
> > > > and restart it.
> > > >
> > > > Is my Reporting Services VS package install corrupt? What do I do?
> > >
> > >
> > >
>
>|||Did you say you tried it as a stored procedure and you still had problems.
20 fields should not be a problem. 309 lines of a query could be a problem
in the IDE (not necessarily when deployed). I suggest try deploying it and
see if it works to determine if it is an IDE only problem. Next, try having
it in a stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
news:0C35989D-938D-4EE6-B1F3-746130C76E80@.microsoft.com...
> Sometimes it runs from the data tab. Sometimes (like now), it hoses VS.
The
> query is 309 lines long and is pretty complex...Still, I'm having a heck
of a
> time. Could it be my RS client install, or could it be my machine?
> "Bruce L-C [MVP]" wrote:
> > The 727 rows should not be a problem. Is that one causing an issue from
the
> > data tab?
> >
> > It doesn't surprise me that the 60,000 row one has problems.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> > > For one report in particular, about 727 rows, and today it's taking
just
> > over
> > > 4 minutes to run. The other has over 60,000 rows and I'm just going to
> > have
> > > to work on limiting that severely.
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > How many fields are being returned? How many rows of data?
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > > > Whenever I run long SQL in VS whether it's in the Data tab or in
> > Preview
> > > > > mode, I can't seem to get a report out of it. I'm talking SQL in
> > excess of
> > > > > 200 lines.
> > > > >
> > > > > I can run the SQL fine in Query Analyzer but running it in the
Data
> > tab,
> > > > it
> > > > > freezes VS, and when I run the Preview, the report never renders.
I
> > have
> > > > even
> > > > > tried hardcoding all parameters, creating a stored procedure, and
> > running
> > > > the
> > > > > report off a view. None have resolved the issue.
> > > > >
> > > > > On a possibly related note, my connection to the database times
out
> > > > > intermittently, saying:
> > > > >
> > > > > --
> > > > > Microsoft Development Environment
> > > > > --
> > > > > An error occurred while executing the query.
> > > > >
> > > > > General network error. Check your network documentation.
> > > > > --
> > > > > OK
> > > > > --
> > > > >
> > > > > This absolutely kills me over the course of a day. I have to shut
down
> > VS
> > > > > and restart it.
> > > > >
> > > > > Is my Reporting Services VS package install corrupt? What do I do?
> > > >
> > > >
> > > >
> >
> >
> >|||Yes, I did give it a shot as a stored procedure, with no luck.
Well, tomorrow's another day. I'll try deploying it and seeing what happens.
Thanks for the help.
Mike
"Bruce L-C [MVP]" wrote:
> Did you say you tried it as a stored procedure and you still had problems.
> 20 fields should not be a problem. 309 lines of a query could be a problem
> in the IDE (not necessarily when deployed). I suggest try deploying it and
> see if it works to determine if it is an IDE only problem. Next, try having
> it in a stored procedure.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> news:0C35989D-938D-4EE6-B1F3-746130C76E80@.microsoft.com...
> > Sometimes it runs from the data tab. Sometimes (like now), it hoses VS.
> The
> > query is 309 lines long and is pretty complex...Still, I'm having a heck
> of a
> > time. Could it be my RS client install, or could it be my machine?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > The 727 rows should not be a problem. Is that one causing an issue from
> the
> > > data tab?
> > >
> > > It doesn't surprise me that the 60,000 row one has problems.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> > > > For one report in particular, about 727 rows, and today it's taking
> just
> > > over
> > > > 4 minutes to run. The other has over 60,000 rows and I'm just going to
> > > have
> > > > to work on limiting that severely.
> > > >
> > > > "Bruce L-C [MVP]" wrote:
> > > >
> > > > > How many fields are being returned? How many rows of data?
> > > > >
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > > > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > > > > Whenever I run long SQL in VS whether it's in the Data tab or in
> > > Preview
> > > > > > mode, I can't seem to get a report out of it. I'm talking SQL in
> > > excess of
> > > > > > 200 lines.
> > > > > >
> > > > > > I can run the SQL fine in Query Analyzer but running it in the
> Data
> > > tab,
> > > > > it
> > > > > > freezes VS, and when I run the Preview, the report never renders.
> I
> > > have
> > > > > even
> > > > > > tried hardcoding all parameters, creating a stored procedure, and
> > > running
> > > > > the
> > > > > > report off a view. None have resolved the issue.
> > > > > >
> > > > > > On a possibly related note, my connection to the database times
> out
> > > > > > intermittently, saying:
> > > > > >
> > > > > > --
> > > > > > Microsoft Development Environment
> > > > > > --
> > > > > > An error occurred while executing the query.
> > > > > >
> > > > > > General network error. Check your network documentation.
> > > > > > --
> > > > > > OK
> > > > > > --
> > > > > >
> > > > > > This absolutely kills me over the course of a day. I have to shut
> down
> > > VS
> > > > > > and restart it.
> > > > > >
> > > > > > Is my Reporting Services VS package install corrupt? What do I do?
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Have you tried changing the connection string of the data source to increase
the time out?
try
"data source=ServerName;initial catalog=DatabaseName;Connection Timeout=30"
The connection timeout has a default of 15.
"Bassist695" wrote:
> Yes, I did give it a shot as a stored procedure, with no luck.
> Well, tomorrow's another day. I'll try deploying it and seeing what happens.
> Thanks for the help.
> Mike
> "Bruce L-C [MVP]" wrote:
> > Did you say you tried it as a stored procedure and you still had problems.
> > 20 fields should not be a problem. 309 lines of a query could be a problem
> > in the IDE (not necessarily when deployed). I suggest try deploying it and
> > see if it works to determine if it is an IDE only problem. Next, try having
> > it in a stored procedure.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > news:0C35989D-938D-4EE6-B1F3-746130C76E80@.microsoft.com...
> > > Sometimes it runs from the data tab. Sometimes (like now), it hoses VS.
> > The
> > > query is 309 lines long and is pretty complex...Still, I'm having a heck
> > of a
> > > time. Could it be my RS client install, or could it be my machine?
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > The 727 rows should not be a problem. Is that one causing an issue from
> > the
> > > > data tab?
> > > >
> > > > It doesn't surprise me that the 60,000 row one has problems.
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > > news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> > > > > For one report in particular, about 727 rows, and today it's taking
> > just
> > > > over
> > > > > 4 minutes to run. The other has over 60,000 rows and I'm just going to
> > > > have
> > > > > to work on limiting that severely.
> > > > >
> > > > > "Bruce L-C [MVP]" wrote:
> > > > >
> > > > > > How many fields are being returned? How many rows of data?
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Bruce Loehle-Conger
> > > > > > MVP SQL Server Reporting Services
> > > > > >
> > > > > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > > > > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > > > > > Whenever I run long SQL in VS whether it's in the Data tab or in
> > > > Preview
> > > > > > > mode, I can't seem to get a report out of it. I'm talking SQL in
> > > > excess of
> > > > > > > 200 lines.
> > > > > > >
> > > > > > > I can run the SQL fine in Query Analyzer but running it in the
> > Data
> > > > tab,
> > > > > > it
> > > > > > > freezes VS, and when I run the Preview, the report never renders.
> > I
> > > > have
> > > > > > even
> > > > > > > tried hardcoding all parameters, creating a stored procedure, and
> > > > running
> > > > > > the
> > > > > > > report off a view. None have resolved the issue.
> > > > > > >
> > > > > > > On a possibly related note, my connection to the database times
> > out
> > > > > > > intermittently, saying:
> > > > > > >
> > > > > > > --
> > > > > > > Microsoft Development Environment
> > > > > > > --
> > > > > > > An error occurred while executing the query.
> > > > > > >
> > > > > > > General network error. Check your network documentation.
> > > > > > > --
> > > > > > > OK
> > > > > > > --
> > > > > > >
> > > > > > > This absolutely kills me over the course of a day. I have to shut
> > down
> > > > VS
> > > > > > > and restart it.
> > > > > > >
> > > > > > > Is my Reporting Services VS package install corrupt? What do I do?
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||No, I haven't tried that. I don't believe the queries are timing out.
Wouldn't I get an error message to indicate that?
At present, there is no timeout set, which I believe means that the query
will not time out.
I'm going to try the deploy-and-see method...Let's hope it's my IDE.
Mike
"Carl Finnegan" wrote:
> Have you tried changing the connection string of the data source to increase
> the time out?
> try
> "data source=ServerName;initial catalog=DatabaseName;Connection Timeout=30"
> The connection timeout has a default of 15.
> "Bassist695" wrote:
> > Yes, I did give it a shot as a stored procedure, with no luck.
> >
> > Well, tomorrow's another day. I'll try deploying it and seeing what happens.
> > Thanks for the help.
> >
> > Mike
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Did you say you tried it as a stored procedure and you still had problems.
> > > 20 fields should not be a problem. 309 lines of a query could be a problem
> > > in the IDE (not necessarily when deployed). I suggest try deploying it and
> > > see if it works to determine if it is an IDE only problem. Next, try having
> > > it in a stored procedure.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > news:0C35989D-938D-4EE6-B1F3-746130C76E80@.microsoft.com...
> > > > Sometimes it runs from the data tab. Sometimes (like now), it hoses VS.
> > > The
> > > > query is 309 lines long and is pretty complex...Still, I'm having a heck
> > > of a
> > > > time. Could it be my RS client install, or could it be my machine?
> > > >
> > > > "Bruce L-C [MVP]" wrote:
> > > >
> > > > > The 727 rows should not be a problem. Is that one causing an issue from
> > > the
> > > > > data tab?
> > > > >
> > > > > It doesn't surprise me that the 60,000 row one has problems.
> > > > >
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > > > news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> > > > > > For one report in particular, about 727 rows, and today it's taking
> > > just
> > > > > over
> > > > > > 4 minutes to run. The other has over 60,000 rows and I'm just going to
> > > > > have
> > > > > > to work on limiting that severely.
> > > > > >
> > > > > > "Bruce L-C [MVP]" wrote:
> > > > > >
> > > > > > > How many fields are being returned? How many rows of data?
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > > Bruce Loehle-Conger
> > > > > > > MVP SQL Server Reporting Services
> > > > > > >
> > > > > > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > > > > > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > > > > > > Whenever I run long SQL in VS whether it's in the Data tab or in
> > > > > Preview
> > > > > > > > mode, I can't seem to get a report out of it. I'm talking SQL in
> > > > > excess of
> > > > > > > > 200 lines.
> > > > > > > >
> > > > > > > > I can run the SQL fine in Query Analyzer but running it in the
> > > Data
> > > > > tab,
> > > > > > > it
> > > > > > > > freezes VS, and when I run the Preview, the report never renders.
> > > I
> > > > > have
> > > > > > > even
> > > > > > > > tried hardcoding all parameters, creating a stored procedure, and
> > > > > running
> > > > > > > the
> > > > > > > > report off a view. None have resolved the issue.
> > > > > > > >
> > > > > > > > On a possibly related note, my connection to the database times
> > > out
> > > > > > > > intermittently, saying:
> > > > > > > >
> > > > > > > > --
> > > > > > > > Microsoft Development Environment
> > > > > > > > --
> > > > > > > > An error occurred while executing the query.
> > > > > > > >
> > > > > > > > General network error. Check your network documentation.
> > > > > > > > --
> > > > > > > > OK
> > > > > > > > --
> > > > > > > >
> > > > > > > > This absolutely kills me over the course of a day. I have to shut
> > > down
> > > > > VS
> > > > > > > > and restart it.
> > > > > > > >
> > > > > > > > Is my Reporting Services VS package install corrupt? What do I do?
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
mode, I can't seem to get a report out of it. I'm talking SQL in excess of
200 lines.
I can run the SQL fine in Query Analyzer but running it in the Data tab, it
freezes VS, and when I run the Preview, the report never renders. I have even
tried hardcoding all parameters, creating a stored procedure, and running the
report off a view. None have resolved the issue.
On a possibly related note, my connection to the database times out
intermittently, saying:
--
Microsoft Development Environment
--
An error occurred while executing the query.
General network error. Check your network documentation.
--
OK
--
This absolutely kills me over the course of a day. I have to shut down VS
and restart it.
Is my Reporting Services VS package install corrupt? What do I do?How many fields are being returned? How many rows of data?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> Whenever I run long SQL in VS whether it's in the Data tab or in Preview
> mode, I can't seem to get a report out of it. I'm talking SQL in excess of
> 200 lines.
> I can run the SQL fine in Query Analyzer but running it in the Data tab,
it
> freezes VS, and when I run the Preview, the report never renders. I have
even
> tried hardcoding all parameters, creating a stored procedure, and running
the
> report off a view. None have resolved the issue.
> On a possibly related note, my connection to the database times out
> intermittently, saying:
> --
> Microsoft Development Environment
> --
> An error occurred while executing the query.
> General network error. Check your network documentation.
> --
> OK
> --
> This absolutely kills me over the course of a day. I have to shut down VS
> and restart it.
> Is my Reporting Services VS package install corrupt? What do I do?|||For one report in particular, about 727 rows, and today it's taking just over
4 minutes to run. The other has over 60,000 rows and I'm just going to have
to work on limiting that severely.
"Bruce L-C [MVP]" wrote:
> How many fields are being returned? How many rows of data?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > Whenever I run long SQL in VS whether it's in the Data tab or in Preview
> > mode, I can't seem to get a report out of it. I'm talking SQL in excess of
> > 200 lines.
> >
> > I can run the SQL fine in Query Analyzer but running it in the Data tab,
> it
> > freezes VS, and when I run the Preview, the report never renders. I have
> even
> > tried hardcoding all parameters, creating a stored procedure, and running
> the
> > report off a view. None have resolved the issue.
> >
> > On a possibly related note, my connection to the database times out
> > intermittently, saying:
> >
> > --
> > Microsoft Development Environment
> > --
> > An error occurred while executing the query.
> >
> > General network error. Check your network documentation.
> > --
> > OK
> > --
> >
> > This absolutely kills me over the course of a day. I have to shut down VS
> > and restart it.
> >
> > Is my Reporting Services VS package install corrupt? What do I do?
>
>|||The 727 rows should not be a problem. Is that one causing an issue from the
data tab?
It doesn't surprise me that the 60,000 row one has problems.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> For one report in particular, about 727 rows, and today it's taking just
over
> 4 minutes to run. The other has over 60,000 rows and I'm just going to
have
> to work on limiting that severely.
> "Bruce L-C [MVP]" wrote:
> > How many fields are being returned? How many rows of data?
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > Whenever I run long SQL in VS whether it's in the Data tab or in
Preview
> > > mode, I can't seem to get a report out of it. I'm talking SQL in
excess of
> > > 200 lines.
> > >
> > > I can run the SQL fine in Query Analyzer but running it in the Data
tab,
> > it
> > > freezes VS, and when I run the Preview, the report never renders. I
have
> > even
> > > tried hardcoding all parameters, creating a stored procedure, and
running
> > the
> > > report off a view. None have resolved the issue.
> > >
> > > On a possibly related note, my connection to the database times out
> > > intermittently, saying:
> > >
> > > --
> > > Microsoft Development Environment
> > > --
> > > An error occurred while executing the query.
> > >
> > > General network error. Check your network documentation.
> > > --
> > > OK
> > > --
> > >
> > > This absolutely kills me over the course of a day. I have to shut down
VS
> > > and restart it.
> > >
> > > Is my Reporting Services VS package install corrupt? What do I do?
> >
> >
> >|||For the most part, Mr. 727 will run on the data tab, but clocks endlessly on
Preview. The report does consist of about 20 fields, but I would still expect
it to render.
Sometimes I just lose connectivity and have to close out VS. I'm not sure if
it's related, but it could be I suppose.
Any ideas?
"Bruce L-C [MVP]" wrote:
> The 727 rows should not be a problem. Is that one causing an issue from the
> data tab?
> It doesn't surprise me that the 60,000 row one has problems.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> > For one report in particular, about 727 rows, and today it's taking just
> over
> > 4 minutes to run. The other has over 60,000 rows and I'm just going to
> have
> > to work on limiting that severely.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > How many fields are being returned? How many rows of data?
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > > Whenever I run long SQL in VS whether it's in the Data tab or in
> Preview
> > > > mode, I can't seem to get a report out of it. I'm talking SQL in
> excess of
> > > > 200 lines.
> > > >
> > > > I can run the SQL fine in Query Analyzer but running it in the Data
> tab,
> > > it
> > > > freezes VS, and when I run the Preview, the report never renders. I
> have
> > > even
> > > > tried hardcoding all parameters, creating a stored procedure, and
> running
> > > the
> > > > report off a view. None have resolved the issue.
> > > >
> > > > On a possibly related note, my connection to the database times out
> > > > intermittently, saying:
> > > >
> > > > --
> > > > Microsoft Development Environment
> > > > --
> > > > An error occurred while executing the query.
> > > >
> > > > General network error. Check your network documentation.
> > > > --
> > > > OK
> > > > --
> > > >
> > > > This absolutely kills me over the course of a day. I have to shut down
> VS
> > > > and restart it.
> > > >
> > > > Is my Reporting Services VS package install corrupt? What do I do?
> > >
> > >
> > >
>
>|||Sometimes it runs from the data tab. Sometimes (like now), it hoses VS. The
query is 309 lines long and is pretty complex...Still, I'm having a heck of a
time. Could it be my RS client install, or could it be my machine?
"Bruce L-C [MVP]" wrote:
> The 727 rows should not be a problem. Is that one causing an issue from the
> data tab?
> It doesn't surprise me that the 60,000 row one has problems.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> > For one report in particular, about 727 rows, and today it's taking just
> over
> > 4 minutes to run. The other has over 60,000 rows and I'm just going to
> have
> > to work on limiting that severely.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > How many fields are being returned? How many rows of data?
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > > Whenever I run long SQL in VS whether it's in the Data tab or in
> Preview
> > > > mode, I can't seem to get a report out of it. I'm talking SQL in
> excess of
> > > > 200 lines.
> > > >
> > > > I can run the SQL fine in Query Analyzer but running it in the Data
> tab,
> > > it
> > > > freezes VS, and when I run the Preview, the report never renders. I
> have
> > > even
> > > > tried hardcoding all parameters, creating a stored procedure, and
> running
> > > the
> > > > report off a view. None have resolved the issue.
> > > >
> > > > On a possibly related note, my connection to the database times out
> > > > intermittently, saying:
> > > >
> > > > --
> > > > Microsoft Development Environment
> > > > --
> > > > An error occurred while executing the query.
> > > >
> > > > General network error. Check your network documentation.
> > > > --
> > > > OK
> > > > --
> > > >
> > > > This absolutely kills me over the course of a day. I have to shut down
> VS
> > > > and restart it.
> > > >
> > > > Is my Reporting Services VS package install corrupt? What do I do?
> > >
> > >
> > >
>
>|||Did you say you tried it as a stored procedure and you still had problems.
20 fields should not be a problem. 309 lines of a query could be a problem
in the IDE (not necessarily when deployed). I suggest try deploying it and
see if it works to determine if it is an IDE only problem. Next, try having
it in a stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
news:0C35989D-938D-4EE6-B1F3-746130C76E80@.microsoft.com...
> Sometimes it runs from the data tab. Sometimes (like now), it hoses VS.
The
> query is 309 lines long and is pretty complex...Still, I'm having a heck
of a
> time. Could it be my RS client install, or could it be my machine?
> "Bruce L-C [MVP]" wrote:
> > The 727 rows should not be a problem. Is that one causing an issue from
the
> > data tab?
> >
> > It doesn't surprise me that the 60,000 row one has problems.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> > > For one report in particular, about 727 rows, and today it's taking
just
> > over
> > > 4 minutes to run. The other has over 60,000 rows and I'm just going to
> > have
> > > to work on limiting that severely.
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > How many fields are being returned? How many rows of data?
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > > > Whenever I run long SQL in VS whether it's in the Data tab or in
> > Preview
> > > > > mode, I can't seem to get a report out of it. I'm talking SQL in
> > excess of
> > > > > 200 lines.
> > > > >
> > > > > I can run the SQL fine in Query Analyzer but running it in the
Data
> > tab,
> > > > it
> > > > > freezes VS, and when I run the Preview, the report never renders.
I
> > have
> > > > even
> > > > > tried hardcoding all parameters, creating a stored procedure, and
> > running
> > > > the
> > > > > report off a view. None have resolved the issue.
> > > > >
> > > > > On a possibly related note, my connection to the database times
out
> > > > > intermittently, saying:
> > > > >
> > > > > --
> > > > > Microsoft Development Environment
> > > > > --
> > > > > An error occurred while executing the query.
> > > > >
> > > > > General network error. Check your network documentation.
> > > > > --
> > > > > OK
> > > > > --
> > > > >
> > > > > This absolutely kills me over the course of a day. I have to shut
down
> > VS
> > > > > and restart it.
> > > > >
> > > > > Is my Reporting Services VS package install corrupt? What do I do?
> > > >
> > > >
> > > >
> >
> >
> >|||Yes, I did give it a shot as a stored procedure, with no luck.
Well, tomorrow's another day. I'll try deploying it and seeing what happens.
Thanks for the help.
Mike
"Bruce L-C [MVP]" wrote:
> Did you say you tried it as a stored procedure and you still had problems.
> 20 fields should not be a problem. 309 lines of a query could be a problem
> in the IDE (not necessarily when deployed). I suggest try deploying it and
> see if it works to determine if it is an IDE only problem. Next, try having
> it in a stored procedure.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> news:0C35989D-938D-4EE6-B1F3-746130C76E80@.microsoft.com...
> > Sometimes it runs from the data tab. Sometimes (like now), it hoses VS.
> The
> > query is 309 lines long and is pretty complex...Still, I'm having a heck
> of a
> > time. Could it be my RS client install, or could it be my machine?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > The 727 rows should not be a problem. Is that one causing an issue from
> the
> > > data tab?
> > >
> > > It doesn't surprise me that the 60,000 row one has problems.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> > > > For one report in particular, about 727 rows, and today it's taking
> just
> > > over
> > > > 4 minutes to run. The other has over 60,000 rows and I'm just going to
> > > have
> > > > to work on limiting that severely.
> > > >
> > > > "Bruce L-C [MVP]" wrote:
> > > >
> > > > > How many fields are being returned? How many rows of data?
> > > > >
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > > > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > > > > Whenever I run long SQL in VS whether it's in the Data tab or in
> > > Preview
> > > > > > mode, I can't seem to get a report out of it. I'm talking SQL in
> > > excess of
> > > > > > 200 lines.
> > > > > >
> > > > > > I can run the SQL fine in Query Analyzer but running it in the
> Data
> > > tab,
> > > > > it
> > > > > > freezes VS, and when I run the Preview, the report never renders.
> I
> > > have
> > > > > even
> > > > > > tried hardcoding all parameters, creating a stored procedure, and
> > > running
> > > > > the
> > > > > > report off a view. None have resolved the issue.
> > > > > >
> > > > > > On a possibly related note, my connection to the database times
> out
> > > > > > intermittently, saying:
> > > > > >
> > > > > > --
> > > > > > Microsoft Development Environment
> > > > > > --
> > > > > > An error occurred while executing the query.
> > > > > >
> > > > > > General network error. Check your network documentation.
> > > > > > --
> > > > > > OK
> > > > > > --
> > > > > >
> > > > > > This absolutely kills me over the course of a day. I have to shut
> down
> > > VS
> > > > > > and restart it.
> > > > > >
> > > > > > Is my Reporting Services VS package install corrupt? What do I do?
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Have you tried changing the connection string of the data source to increase
the time out?
try
"data source=ServerName;initial catalog=DatabaseName;Connection Timeout=30"
The connection timeout has a default of 15.
"Bassist695" wrote:
> Yes, I did give it a shot as a stored procedure, with no luck.
> Well, tomorrow's another day. I'll try deploying it and seeing what happens.
> Thanks for the help.
> Mike
> "Bruce L-C [MVP]" wrote:
> > Did you say you tried it as a stored procedure and you still had problems.
> > 20 fields should not be a problem. 309 lines of a query could be a problem
> > in the IDE (not necessarily when deployed). I suggest try deploying it and
> > see if it works to determine if it is an IDE only problem. Next, try having
> > it in a stored procedure.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > news:0C35989D-938D-4EE6-B1F3-746130C76E80@.microsoft.com...
> > > Sometimes it runs from the data tab. Sometimes (like now), it hoses VS.
> > The
> > > query is 309 lines long and is pretty complex...Still, I'm having a heck
> > of a
> > > time. Could it be my RS client install, or could it be my machine?
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > The 727 rows should not be a problem. Is that one causing an issue from
> > the
> > > > data tab?
> > > >
> > > > It doesn't surprise me that the 60,000 row one has problems.
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > > news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> > > > > For one report in particular, about 727 rows, and today it's taking
> > just
> > > > over
> > > > > 4 minutes to run. The other has over 60,000 rows and I'm just going to
> > > > have
> > > > > to work on limiting that severely.
> > > > >
> > > > > "Bruce L-C [MVP]" wrote:
> > > > >
> > > > > > How many fields are being returned? How many rows of data?
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Bruce Loehle-Conger
> > > > > > MVP SQL Server Reporting Services
> > > > > >
> > > > > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > > > > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > > > > > Whenever I run long SQL in VS whether it's in the Data tab or in
> > > > Preview
> > > > > > > mode, I can't seem to get a report out of it. I'm talking SQL in
> > > > excess of
> > > > > > > 200 lines.
> > > > > > >
> > > > > > > I can run the SQL fine in Query Analyzer but running it in the
> > Data
> > > > tab,
> > > > > > it
> > > > > > > freezes VS, and when I run the Preview, the report never renders.
> > I
> > > > have
> > > > > > even
> > > > > > > tried hardcoding all parameters, creating a stored procedure, and
> > > > running
> > > > > > the
> > > > > > > report off a view. None have resolved the issue.
> > > > > > >
> > > > > > > On a possibly related note, my connection to the database times
> > out
> > > > > > > intermittently, saying:
> > > > > > >
> > > > > > > --
> > > > > > > Microsoft Development Environment
> > > > > > > --
> > > > > > > An error occurred while executing the query.
> > > > > > >
> > > > > > > General network error. Check your network documentation.
> > > > > > > --
> > > > > > > OK
> > > > > > > --
> > > > > > >
> > > > > > > This absolutely kills me over the course of a day. I have to shut
> > down
> > > > VS
> > > > > > > and restart it.
> > > > > > >
> > > > > > > Is my Reporting Services VS package install corrupt? What do I do?
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||No, I haven't tried that. I don't believe the queries are timing out.
Wouldn't I get an error message to indicate that?
At present, there is no timeout set, which I believe means that the query
will not time out.
I'm going to try the deploy-and-see method...Let's hope it's my IDE.
Mike
"Carl Finnegan" wrote:
> Have you tried changing the connection string of the data source to increase
> the time out?
> try
> "data source=ServerName;initial catalog=DatabaseName;Connection Timeout=30"
> The connection timeout has a default of 15.
> "Bassist695" wrote:
> > Yes, I did give it a shot as a stored procedure, with no luck.
> >
> > Well, tomorrow's another day. I'll try deploying it and seeing what happens.
> > Thanks for the help.
> >
> > Mike
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Did you say you tried it as a stored procedure and you still had problems.
> > > 20 fields should not be a problem. 309 lines of a query could be a problem
> > > in the IDE (not necessarily when deployed). I suggest try deploying it and
> > > see if it works to determine if it is an IDE only problem. Next, try having
> > > it in a stored procedure.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > news:0C35989D-938D-4EE6-B1F3-746130C76E80@.microsoft.com...
> > > > Sometimes it runs from the data tab. Sometimes (like now), it hoses VS.
> > > The
> > > > query is 309 lines long and is pretty complex...Still, I'm having a heck
> > > of a
> > > > time. Could it be my RS client install, or could it be my machine?
> > > >
> > > > "Bruce L-C [MVP]" wrote:
> > > >
> > > > > The 727 rows should not be a problem. Is that one causing an issue from
> > > the
> > > > > data tab?
> > > > >
> > > > > It doesn't surprise me that the 60,000 row one has problems.
> > > > >
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > > > news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> > > > > > For one report in particular, about 727 rows, and today it's taking
> > > just
> > > > > over
> > > > > > 4 minutes to run. The other has over 60,000 rows and I'm just going to
> > > > > have
> > > > > > to work on limiting that severely.
> > > > > >
> > > > > > "Bruce L-C [MVP]" wrote:
> > > > > >
> > > > > > > How many fields are being returned? How many rows of data?
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > > Bruce Loehle-Conger
> > > > > > > MVP SQL Server Reporting Services
> > > > > > >
> > > > > > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > > > > > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > > > > > > Whenever I run long SQL in VS whether it's in the Data tab or in
> > > > > Preview
> > > > > > > > mode, I can't seem to get a report out of it. I'm talking SQL in
> > > > > excess of
> > > > > > > > 200 lines.
> > > > > > > >
> > > > > > > > I can run the SQL fine in Query Analyzer but running it in the
> > > Data
> > > > > tab,
> > > > > > > it
> > > > > > > > freezes VS, and when I run the Preview, the report never renders.
> > > I
> > > > > have
> > > > > > > even
> > > > > > > > tried hardcoding all parameters, creating a stored procedure, and
> > > > > running
> > > > > > > the
> > > > > > > > report off a view. None have resolved the issue.
> > > > > > > >
> > > > > > > > On a possibly related note, my connection to the database times
> > > out
> > > > > > > > intermittently, saying:
> > > > > > > >
> > > > > > > > --
> > > > > > > > Microsoft Development Environment
> > > > > > > > --
> > > > > > > > An error occurred while executing the query.
> > > > > > > >
> > > > > > > > General network error. Check your network documentation.
> > > > > > > > --
> > > > > > > > OK
> > > > > > > > --
> > > > > > > >
> > > > > > > > This absolutely kills me over the course of a day. I have to shut
> > > down
> > > > > VS
> > > > > > > > and restart it.
> > > > > > > >
> > > > > > > > Is my Reporting Services VS package install corrupt? What do I do?
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
long insert time
I have a table with about half a million records.
Select queries run fine.
This test query I ran in QA took over 1 minute to run.
insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
What are some possible causes?Two possible causes are blocking and insert trigger(s) on the table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:OpY%23$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>|||Tibor Karaszi wrote:
> Two possible causes are blocking and insert trigger(s) on the table.
>
I don't have any triggers. Funny thing, I re-ran the query about 5
times. The first 4 times it took over a minute to perform. The 5th
time, it took 2 seconds. I performed it again and it only took 2 seconds.
Any idea for the change in performance?|||Blocking is still a possible reason.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi wrote:
> > Two possible causes are blocking and insert trigger(s) on the table.
> >
> I don't have any triggers. Funny thing, I re-ran the query about 5
> times. The first 4 times it took over a minute to perform. The 5th
> time, it took 2 seconds. I performed it again and it only took 2 seconds.
> Any idea for the change in performance?
>|||Another reason could be an autogrow kicking in...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:u5gzVvYQEHA.3596@.tk2msftngp13.phx.gbl...
> Blocking is still a possible reason.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK2MSFTNGP12.phx.gbl...
> > Tibor Karaszi wrote:
> >
> > > Two possible causes are blocking and insert trigger(s) on the table.
> > >
> >
> > I don't have any triggers. Funny thing, I re-ran the query about 5
> > times. The first 4 times it took over a minute to perform. The 5th
> > time, it took 2 seconds. I performed it again and it only took 2 seconds.
> >
> > Any idea for the change in performance?
> >
>|||Hi,
As Tiber pointed out this execution time diffence while inserting a record
will be due to Blocking. So to see the blocking:-
While you execute the insert statement, if it is delaying open a new query
analyzer window and execute SP_WHO command.
In the result look for the column BLK, In the normal case all the values
will be "0". If there is a block then a SPID will displayed in BLK field.
If blocked understand the statement that user( SPID - blocked ) is doing by
using the below command:-
DBCC INPUTBUFFER(SPID) -- Replace the SPID with the SPID blocked.
Thanks
Hari
MCDBA
"Won Lee" <noemail@.nospam.com> wrote in message
news:OpY#$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>|||Tibor Karaszi wrote:
> Another reason could be an autogrow kicking in...
>
OK. Looking up Blocking in Books Online.
Next time I have this issue, I will be write down the DB size to see if
the autogrow is part of the problem. Thanks.sql
Select queries run fine.
This test query I ran in QA took over 1 minute to run.
insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
What are some possible causes?Two possible causes are blocking and insert trigger(s) on the table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:OpY%23$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>|||Tibor Karaszi wrote:
> Two possible causes are blocking and insert trigger(s) on the table.
>
I don't have any triggers. Funny thing, I re-ran the query about 5
times. The first 4 times it took over a minute to perform. The 5th
time, it took 2 seconds. I performed it again and it only took 2 seconds.
Any idea for the change in performance?|||Blocking is still a possible reason.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi wrote:
> > Two possible causes are blocking and insert trigger(s) on the table.
> >
> I don't have any triggers. Funny thing, I re-ran the query about 5
> times. The first 4 times it took over a minute to perform. The 5th
> time, it took 2 seconds. I performed it again and it only took 2 seconds.
> Any idea for the change in performance?
>|||Another reason could be an autogrow kicking in...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:u5gzVvYQEHA.3596@.tk2msftngp13.phx.gbl...
> Blocking is still a possible reason.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK2MSFTNGP12.phx.gbl...
> > Tibor Karaszi wrote:
> >
> > > Two possible causes are blocking and insert trigger(s) on the table.
> > >
> >
> > I don't have any triggers. Funny thing, I re-ran the query about 5
> > times. The first 4 times it took over a minute to perform. The 5th
> > time, it took 2 seconds. I performed it again and it only took 2 seconds.
> >
> > Any idea for the change in performance?
> >
>|||Hi,
As Tiber pointed out this execution time diffence while inserting a record
will be due to Blocking. So to see the blocking:-
While you execute the insert statement, if it is delaying open a new query
analyzer window and execute SP_WHO command.
In the result look for the column BLK, In the normal case all the values
will be "0". If there is a block then a SPID will displayed in BLK field.
If blocked understand the statement that user( SPID - blocked ) is doing by
using the below command:-
DBCC INPUTBUFFER(SPID) -- Replace the SPID with the SPID blocked.
Thanks
Hari
MCDBA
"Won Lee" <noemail@.nospam.com> wrote in message
news:OpY#$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>|||Tibor Karaszi wrote:
> Another reason could be an autogrow kicking in...
>
OK. Looking up Blocking in Books Online.
Next time I have this issue, I will be write down the DB size to see if
the autogrow is part of the problem. Thanks.sql
long insert time
I have a table with about half a million records.
Select queries run fine.
This test query I ran in QA took over 1 minute to run.
insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
What are some possible causes?Two possible causes are blocking and insert trigger(s) on the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:OpY%23$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl
..
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>|||Tibor Karaszi wrote:
> Two possible causes are blocking and insert trigger(s) on the table.
>
I don't have any triggers. Funny thing, I re-ran the query about 5
times. The first 4 times it took over a minute to perform. The 5th
time, it took 2 seconds. I performed it again and it only took 2 seconds.
Any idea for the change in performance?|||Blocking is still a possible reason.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK2MSFTNGP12.phx.gbl
..
> Tibor Karaszi wrote:
>
> I don't have any triggers. Funny thing, I re-ran the query about 5
> times. The first 4 times it took over a minute to perform. The 5th
> time, it took 2 seconds. I performed it again and it only took 2 seconds.
> Any idea for the change in performance?
>|||Another reason could be an autogrow kicking in...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:u5gzVvYQEHA.3596@.tk2msftngp13.phx.gbl...
> Blocking is still a possible reason.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK
2MSFTNGP12.phx.gbl...
>|||Hi,
As Tiber pointed out this execution time diffence while inserting a record
will be due to Blocking. So to see the blocking:-
While you execute the insert statement, if it is delaying open a new query
analyzer window and execute SP_WHO command.
In the result look for the column BLK, In the normal case all the values
will be "0". If there is a block then a SPID will displayed in BLK field.
If blocked understand the statement that user( SPID - blocked ) is doing by
using the below command:-
DBCC INPUTBUFFER(SPID) -- Replace the SPID with the SPID blocked.
Thanks
Hari
MCDBA
"Won Lee" <noemail@.nospam.com> wrote in message
news:OpY#$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>|||Tibor Karaszi wrote:
> Another reason could be an autogrow kicking in...
>
OK. Looking up Blocking in Books Online.
Next time I have this issue, I will be write down the DB size to see if
the autogrow is part of the problem. Thanks.
Select queries run fine.
This test query I ran in QA took over 1 minute to run.
insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
What are some possible causes?Two possible causes are blocking and insert trigger(s) on the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:OpY%23$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl
..
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>|||Tibor Karaszi wrote:
> Two possible causes are blocking and insert trigger(s) on the table.
>
I don't have any triggers. Funny thing, I re-ran the query about 5
times. The first 4 times it took over a minute to perform. The 5th
time, it took 2 seconds. I performed it again and it only took 2 seconds.
Any idea for the change in performance?|||Blocking is still a possible reason.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK2MSFTNGP12.phx.gbl
..
> Tibor Karaszi wrote:
>
> I don't have any triggers. Funny thing, I re-ran the query about 5
> times. The first 4 times it took over a minute to perform. The 5th
> time, it took 2 seconds. I performed it again and it only took 2 seconds.
> Any idea for the change in performance?
>|||Another reason could be an autogrow kicking in...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:u5gzVvYQEHA.3596@.tk2msftngp13.phx.gbl...
> Blocking is still a possible reason.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK
2MSFTNGP12.phx.gbl...
>|||Hi,
As Tiber pointed out this execution time diffence while inserting a record
will be due to Blocking. So to see the blocking:-
While you execute the insert statement, if it is delaying open a new query
analyzer window and execute SP_WHO command.
In the result look for the column BLK, In the normal case all the values
will be "0". If there is a block then a SPID will displayed in BLK field.
If blocked understand the statement that user( SPID - blocked ) is doing by
using the below command:-
DBCC INPUTBUFFER(SPID) -- Replace the SPID with the SPID blocked.
Thanks
Hari
MCDBA
"Won Lee" <noemail@.nospam.com> wrote in message
news:OpY#$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>|||Tibor Karaszi wrote:
> Another reason could be an autogrow kicking in...
>
OK. Looking up Blocking in Books Online.
Next time I have this issue, I will be write down the DB size to see if
the autogrow is part of the problem. Thanks.
long insert time
I have a table with about half a million records.
Select queries run fine.
This test query I ran in QA took over 1 minute to run.
insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
What are some possible causes?
Two possible causes are blocking and insert trigger(s) on the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:OpY%23$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>
|||Tibor Karaszi wrote:
> Two possible causes are blocking and insert trigger(s) on the table.
>
I don't have any triggers. Funny thing, I re-ran the query about 5
times. The first 4 times it took over a minute to perform. The 5th
time, it took 2 seconds. I performed it again and it only took 2 seconds.
Any idea for the change in performance?
|||Blocking is still a possible reason.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi wrote:
>
> I don't have any triggers. Funny thing, I re-ran the query about 5
> times. The first 4 times it took over a minute to perform. The 5th
> time, it took 2 seconds. I performed it again and it only took 2 seconds.
> Any idea for the change in performance?
>
|||Another reason could be an autogrow kicking in...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:u5gzVvYQEHA.3596@.tk2msftngp13.phx.gbl...
> Blocking is still a possible reason.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK2MSFTNGP12.phx.gbl...
>
|||Hi,
As Tiber pointed out this execution time diffence while inserting a record
will be due to Blocking. So to see the blocking:-
While you execute the insert statement, if it is delaying open a new query
analyzer window and execute SP_WHO command.
In the result look for the column BLK, In the normal case all the values
will be "0". If there is a block then a SPID will displayed in BLK field.
If blocked understand the statement that user( SPID - blocked ) is doing by
using the below command:-
DBCC INPUTBUFFER(SPID) -- Replace the SPID with the SPID blocked.
Thanks
Hari
MCDBA
"Won Lee" <noemail@.nospam.com> wrote in message
news:OpY#$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>
|||Tibor Karaszi wrote:
> Another reason could be an autogrow kicking in...
>
OK. Looking up Blocking in Books Online.
Next time I have this issue, I will be write down the DB size to see if
the autogrow is part of the problem. Thanks.
Select queries run fine.
This test query I ran in QA took over 1 minute to run.
insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
What are some possible causes?
Two possible causes are blocking and insert trigger(s) on the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:OpY%23$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>
|||Tibor Karaszi wrote:
> Two possible causes are blocking and insert trigger(s) on the table.
>
I don't have any triggers. Funny thing, I re-ran the query about 5
times. The first 4 times it took over a minute to perform. The 5th
time, it took 2 seconds. I performed it again and it only took 2 seconds.
Any idea for the change in performance?
|||Blocking is still a possible reason.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi wrote:
>
> I don't have any triggers. Funny thing, I re-ran the query about 5
> times. The first 4 times it took over a minute to perform. The 5th
> time, it took 2 seconds. I performed it again and it only took 2 seconds.
> Any idea for the change in performance?
>
|||Another reason could be an autogrow kicking in...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:u5gzVvYQEHA.3596@.tk2msftngp13.phx.gbl...
> Blocking is still a possible reason.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK2MSFTNGP12.phx.gbl...
>
|||Hi,
As Tiber pointed out this execution time diffence while inserting a record
will be due to Blocking. So to see the blocking:-
While you execute the insert statement, if it is delaying open a new query
analyzer window and execute SP_WHO command.
In the result look for the column BLK, In the normal case all the values
will be "0". If there is a block then a SPID will displayed in BLK field.
If blocked understand the statement that user( SPID - blocked ) is doing by
using the below command:-
DBCC INPUTBUFFER(SPID) -- Replace the SPID with the SPID blocked.
Thanks
Hari
MCDBA
"Won Lee" <noemail@.nospam.com> wrote in message
news:OpY#$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>
|||Tibor Karaszi wrote:
> Another reason could be an autogrow kicking in...
>
OK. Looking up Blocking in Books Online.
Next time I have this issue, I will be write down the DB size to see if
the autogrow is part of the problem. Thanks.
Subscribe to:
Posts (Atom)