April 29, 2003 at 6:51 am
Each night I am extracting some data from a source onto a SQL Server 2000. Each night I am using T-SQL scripts to extract the data and put into a table on the SQL server, there are about 2.2 million records. There are three indexes on the table but one index is specifically for a report that takes 12 mins to run without the index but 2 mins with the index. However each night I am truncating the table and re-populating it with the source data. Lately the report has started to take 12 mins again. I have drop and re-created the index and it took 2 mins.
I am assuming that the index had been corrupt.
Does any one had any thoughts on this subject and any suggestions for me to avoid this in the future
April 29, 2003 at 7:05 am
This may not be due to corruption of index.
This might be due to the fact that the statistics have not been updated on the index.
Sql*Server maintains statistics on each index and decides which index to use when a query need to be executed depending on statistics.
In your case, I think the statistics has not been updated when the table is repopulated. This is done automatically and you do not have much control over it. However you can force update of statistics on a table using UPDATE STATISTICS command
Try issuing UPDATE STATISTICS before running the report.
April 29, 2003 at 1:11 pm
I agree. If he chooses auto create statistics and auto update statistics in database option, will this work? To me this doesn't work. Could you explain why not.
Thanks!
Robert
April 30, 2003 at 5:18 am
Thanks Rajesh
This has worked and has got the report back to 2 mins. I have not set the DB to auto update I have scripted it into the extraction, so when it has finished inserting the 2.2 million records it will then update the stats for the whole DB
Thanks again
April 30, 2003 at 6:02 am
Just an update to Robert's Query
1. auto update statistics is periodical
Sql*Server periodically launches this routine.
Hence if your report runs before sql*server launches this auto update statistics routine, then the statistics
will not be up to date for the report.
2. Uses Sampling to decide whether statistics on a index is out of date.
So some times sql*server might think that the statistics are still up to date, eventhough there are lot of
changes happened on the table(due to sampling).
Auto Create Statistics:
I would not recommend to use it, as this starts creating indexes for each column and might become big issue for maintenance
April 30, 2003 at 11:25 am
Thanks Rajes, I will follow your idea.
Robert
April 30, 2003 at 11:32 am
Laughing. As a DBA I'm shy about auto-anything. On your job that repopulates the table add a task to reindex or sp_updatestats.
Yes, autoupdate works on a sampling of the table. You can set it to work on Full Scan, but since you are truncate and repopulating every night, that just doesn't give you anything.
Patrick
Quand on parle du loup, on en voit la queue
May 1, 2003 at 4:58 am
I have created this script that seems to have solved the problem. It takes about 5 mins to run at the end of my daily processes on a 12GB database
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON SET ANSI_WARNINGS ON
GO
if exists (select * from sysobjects where id = object_id(N'[dbo].[DWH_UPDATE_STATS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DWH_UPDATE_STATS]
GO
/****************************************************************************************************************/
/**/
/* File: DWH_UPDATE_STATS.SQL*/
/*Description:THIS SCRIPT WILL UPDATE ALL THE STATS IN A DB TO A SAMPLE OF 50 PERCENT*/
/*Warning:*/
/*Author: PAUL EAST*/
/*Copyright: EASTY CORP*/
/*Date and Time: 30/04/2003 12:05*/
/**/
/****************************************************************************************************************/
CREATE PROCEDURE DWH_UPDATE_STATS AS
DECLARE @dbsid varbinary(85)
DECLARE @resample CHAR(8)
SELECT @dbsid = sid
FROM master.dbo.sysdatabases
WHERE name = db_name()
-- required so it can update stats on on ICC/IVs
set ansi_nulls on
set quoted_identifier on
set ansi_warnings on
set ansi_padding on
set arithabort on
set concat_null_yields_null on
set numeric_roundabort off
DECLARE @exec_stmt nvarchar(540)
DECLARE @tablename sysname
DECLARE @uid smallint
DECLARE @user_name sysname
DECLARE @tablename_header varchar(267)
DECLARE ms_crs_tnames CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT name, uid FROM sysobjects WHERE type = 'U'
OPEN ms_crs_tnames
FETCH NEXT FROM ms_crs_tnames INTO @tablename, @uid
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @user_name = user_name(@uid)
SELECT @tablename_header = 'Updating ' + @user_name +'.'+ RTRIM(@tablename)
PRINT @tablename_header
SELECT @exec_stmt = 'UPDATE STATISTICS ' + quotename( @user_name , '[')+'.' + quotename( @tablename, '[')
SET @exec_stmt = @exec_stmt + ' WITH SAMPLE 50 PERCENT'
EXEC (@exec_stmt)
END
FETCH NEXT FROM ms_crs_tnames INTO @tablename, @uid
END
PRINT ' '
PRINT ' '
raiserror(15005,-1,-1)
DEALLOCATE ms_crs_tnames
RETURN
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON SET ANSI_WARNINGS ON
GO
GRANT EXECUTE ON [dbo].[DWH_UPDATE_STATS] TO [public]
GO
What it does, is that it updates the stats with a sample of 50% for each table. It is simply a slightly modified version of the SP sp_updatestats
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply