SQL Server 2000 - My query is running slowly, all of sudden

Question

My database query has been running very fast until it changed to very slow recently. No changed have occurred in the database apart from normal data growth.

I have noticed that the database statistics have "never" been updated.

Is there an easy way that I can update these statistics across my entire database so I can see if that is the problem?

I am using SQL Server 2000 Sp4.

Answer 1

Try here

This should speed up your indices and key distribution. Re-analyzing table statistics optimises SQL Server's choice of index for queries, especially for large datasets

Answer 2

Definitely make yourself a weekly task that runs automatically to update the database's statistics.

Answer 3

You can use this

CREATE PROC usp_UPDATE_STATISTICS
(@dbName sysname, @sample int)
AS

SET NOCOUNT ON

DECLARE @SQL nvarchar(4000)
DECLARE @ID int
DECLARE @TableName sysname
DECLARE @RowCnt int

CREATE TABLE ##Tables
(
 TableID INT IDENTITY(1, 1) NOT NULL, 
 TableName SYSNAME NOT NULL
)

SET @SQL = ''
SET @SQL = @SQL + 'INSERT INTO ##Tables (TableName) '
SET @SQL = @SQL + 'SELECT [name] '
SET @SQL = @SQL + 'FROM ' + @dbName + '.dbo.sysobjects ' 
SET @SQL = @SQL + 'WHERE xtype = ''U'' AND [name] <> ''dtproperties'''

EXEC sp_executesql @statement = @SQL

SELECT TOP 1 @ID = TableID, @TableName = TableName
FROM ##Tables
ORDER BY TableID

SET @RowCnt = @@ROWCOUNT

WHILE @RowCnt <> 0
BEGIN

 SET @SQL = 'UPDATE STATISTICS ' + @dbname + '.dbo.[' + @TableName + '] WITH SAMPLE ' + CONVERT(varchar(3), @sample) + ' PERCENT'

 EXEC sp_executesql @statement = @SQL

 SELECT TOP 1 @ID = TableID, @TableName = TableName
 FROM ##Tables
 WHERE TableID > @ID
 ORDER BY TableID

 SET @RowCnt = @@ROWCOUNT

END

DROP TABLE ##Tables


GO

This will update stats on all the tables in the DB. You should also look at indexes and rebuild / defrag as nexessary

Raj

Answer 4

Normal Data Growth is good enough as a reson to justify a slowdown of pretty much any not optimized query.

Scalability issues related db size won't manifest till the data volume grows.

Post your query + rough data volume and we'll help you to see what's what.

Answer 5

We've had a very similar problem with MSSQL 2005 and suddenly slow running queries.

Here's how we solved it: we added (nolock) for every select statement in the query. For example:

select count(*) from SalesHistory with(nolock)

Note that nolock should also be added to nested select statements, as well as joins. Here's an article that gives more details about how performance is increased when using nolock. http://www.mollerus.net/tom/blog/2008/03/using_mssqls_nolock_for_faster_queries.html

Don't forget to keep a backup of your original query obviously. Please give it a try and let me know.