The database compatibility was changed a few times. It did not affect
The problems occur when a report writer (Business Objects) fires a SQL query to
SQL 7. There are no hard coded creations of temp tables or SELECT INTO
statements in these queries.
I do not know how the queries are translated within SQL 7. The temp database is
growing to extreme proportions during the query. (1 GB)
How can we influence temporaty table stuff? The problem is that the queries are
generated automatically, so we can not influence them. So if possible we would
like to take care of the problem by changing some preferences in SQL. Is this
Yes, we have updated the statistics and rebuilt all indexes. DBCC was all fine
> What is your database compatiblity level set to? Use sp_dbcmptlevel
> (documented within BOL) to find out.
> Do your stored procedures create temp tables within them? If so it is
> recommended to create the temp tables first (CREATE TABLE #...) instead of
> creating them in the middle of the procedure. Also, SELECT x INTO #b FROM y
> should be rewritten to do the create table up front and then insert into the
> temp table.
> have you updated statistics?
> run dbcc?
> > After upgrading from SQL 6.5 to SQL 7 performance dropped dramatically.
> > Queries that used to take 1 minute in 6.5 now have to be killed after an
> > hour.
> > The strange thing is that almost no memory is used although 400MB was
> > dedicated to SQL Server. The CPU usage in SQL 7 is about 50%, it used to
> > be about 90% in 6.5. The hard disk activity stops after about 30 seconds
> > of querying. After that nothing seems to happen.
> > To avoid the influence of network stuff we ran all client processes on
> > the server.
> > So SQL 7 does not use the available resources and we don't know the
> > reason. Does anybody have a clue?
> > additional data:
> > SQL Server 7.0 service pack 1
> > Windows NT 4.0 Service Pack 5
> > Server: Compaq Proliant