Wierd behaviour - 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 2005 Forums

Transact-SQL (2005)

Wierd behaviour

Author

Topic

hrishi_des
Yak Posting Veteran

76 Posts

Posted - 2007-09-14 : 09:46:22

Hi,

I have a partitioned table having a clustered index, and a non clustered index, we run stored procedure after we populate (using BCP) data into partitioned table.

After 5 runs, the stored procedure timings becomes wierd i.e. it takes 6 hours to complete 6th trial, 30 mins to complete 7th, 6 hrs to complete 8th trial, 50 mins to complete 9th trial and this oscillation goes on.

What i know is there lot of disc reads happen when SP takes more time.

Will doing update statistics on table/index or changing fill factor would help?

Thanks in advance,

Regards,
Hrishikesh Desai

When solution is simple, God is answering….

Kristen
Test

22859 Posts

Posted - 2007-09-14 : 10:04:16

"[i]we populate (using BCP) data[i/]"

Assuming you are importing a large-ish amount of data then:

Reindex all indexes on all tables imported

or Defrag and Update statistics (again, on all indexes on all tables imported)

I can't remember in SQL 2005 whether this act alone will reliably get the Sprocs to dump their cached query plans, or whether you need to recompile them [you would need recompile in SQL2000, but I think that is fixed in SQL2005]

Stop/Start the SQL Server would have the same effect - if you are able to do that.

Kristen

hrishi_des
Yak Posting Veteran

76 Posts

Posted - 2007-09-17 : 04:50:07

Hi Kirsten,

Thanks for the reply, I am not sure if i can reindex all indexes as it will take lot of time, please correct me if i am wrong.

But i will try UPDATE STATISTICS.

Best,
Hrishikesh Desai

When solution is simple, God is answering….

Kristen
Test

22859 Posts

Posted - 2007-09-17 : 11:33:49

"I am not sure if i can reindex all indexes as it will take lot of time"

If you have a large database then yes, it may take a long time.

You can:

Just reindex tables / indexes that are fragmented

Reindex sections of the database on different days - e.g. Table names A-F on Monday, G-N on Tuesday ...

But you do need to reindex tables / index that NEED it regularly for good performance

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources