Importing process slow - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Importing process slow

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2007-02-14 : 18:48:33

Importing process is taking a long time.. what could possibly the reason? I don;t see any locking processes...

Which areas should i look at to find the cause?

I am trying to find out the tables/SPs that are used by the importing process.The Importing process is running by the third part application, and I am tryng to find out the tables/sps that are used for the importing process. How can I find thiese impomation?

will rebuiling index help to gain the performance improvement?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-14 : 19:12:17

Run SQL Profiler to determine what it is running. You will then be able to see where the problems are.

It's impossible for us to say if the reindex will help as we don't know how fragmented your system is.

How often do you run UPDATE STATISTICS?

Tara Kizer

jung1975
Aged Yak Warrior

503 Posts

Posted - 2007-02-14 : 19:25:43

I run rebuild index once a week...

Is it going to update statistics if I run DBCC rebuildindex?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-14 : 19:31:00

I can't find any documentation that suggests that DBCC DBREINDEX also updates the statistics. I've never heard of that either. So I'd say no.

Tara Kizer

jung1975
Aged Yak Warrior

503 Posts

Posted - 2007-02-14 : 19:54:55

I have your update statistics script.. Do i have to run this after DBCC DBREINDEX? Isn;t it going to interfere with the user;s transactions? Users can still use/access the database during this process right?

CREATE PROC isp_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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-14 : 20:41:02

DBCC DBREINDEX is the one that will severely impact users. You can run them whenever you want, but they should be done when there is very little activity on the system. We run update stats nightly.

Tara Kizer

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-14 : 22:51:19

quote:
Originally posted by tkizer

I can't find any documentation that suggests that DBCC DBREINDEX also updates the statistics. I've never heard of that either. So I'd say no.

Tara Kizer

DBCC DBREINDEX updates the statistics. You can confirm it like this:


use pubs
go
create statistics Authors_Stats_au_fname
on Authors ( au_fname ) 
with fullscan

waitfor delay '00:00:02.000'

select
	'Index Name' = convert(varchar(30),i.name), 
	'Statistics Date' = STATS_DATE(i.id, i.indid)
from
	sysobjects o
	join
	sysindexes i
	on o.id = i.id
where
	o.name = 'Authors'

dbcc dbreindex ('Authors','',0)

select
	'Index Name' = convert(varchar(30),i.name), 
	'Statistics Date' = STATS_DATE(i.id, i.indid)
from
	sysobjects o
	join
	sysindexes i
	on o.id = i.id
where
	o.name = 'Authors'
go

drop statistics Authors.Authors_Stats_au_fname

Results:


Index Name                     Statistics Date                                        
------------------------------ ------------------------------------------------------ 
UPKCL_auidind                  2007-02-14 23:01:04.933
aunmind                        2007-02-14 23:01:04.933
Authors_Stats_au_fname         2007-02-14 23:05:51.757

(3 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Index Name                     Statistics Date                                        
------------------------------ ------------------------------------------------------ 
UPKCL_auidind                  2007-02-14 23:05:53.760
aunmind                        2007-02-14 23:05:53.770
Authors_Stats_au_fname         2007-02-14 23:05:53.770

(3 row(s) affected)

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2007-02-15 : 06:12:34

"I can't find any documentation that suggests that DBCC DBREINDEX also updates the statistics"

As MVJ says DBCC REIDNEX will update stats. I seem to remember some issue about REIDNEX of a clustered index (possible a NON-unique c.i.) which had a knock on effect on all the Child Indexes, can't remember what the issue was though, and whether it was related to Statistics even - getting too old for this game!

However DEFRAG will NOT update stats.

Kristen

jung1975
Aged Yak Warrior

503 Posts

Posted - 2007-02-15 : 07:48:07

Thanks all,

I've found this article... As you said, DBCC REINDEX updates the statistics...

http://www.sql-server-performance.com/ak_inside_sql_server_maintenance_plans.asp

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-15 : 12:55:53

Thanks for the correction.

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources