How we can re-fresh a view

Question

How we can re-fresh a view

Mathew M.Varghese

SSCommitted

Points: 1929
More actions
June 5, 2012 at 4:11 am

#274996

Hi, recently I moved my production DB from One server to another server.
After move user view are responding slowly.
Can i re-compile the view to get the new metadata of the view?
Which option is the best ? “sp_refreshview” or Drop and re-create the view?
Please advice
Mathew

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

it the other server a different SQL server version? When moving a database to another version, changes int he SQL engine make rebuilding the statistics manditory,as they are used differently,a dn performance can suffer until it gets done.

rebuild your indexes (which automatically rebuilds the statistics on the columns in the indexes), and then update statistics on the other columns next seperately.

here's a simple script that would just rebuild all statistics :

DECLARE @Exec VARCHAR(MAX)

SELECT @Exec = ''

SELECT @Exec = 'UPDATE STATISTICS dbo.[' + CONVERT(VARCHAR(200),name)

+ '] WITH FULLSCAN ; ' + CHAR(13) + CHAR(10)

+ @Exec FROM sys.tables ORDER BY name DESC

PRINT LEN(@Exec)

PRINT @Exec

EXEC(@Exec)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mathew M.Varghese SSCommitted Points: 1929 More actions · Answer 2

Dear Lowel,

My version is sql server 2000 ENT.edit and i did the following

1.Re-index all tables

2. Update statistics with Full scan.

After that performance are increased.

But still some sql statement working slow . Can I re-build the view to reach optimum performance?. If so what statement i have to use for the same?

Mathew

Lowell SSC Guru Points: 323482 More actions · Answer 3

...some sql statement working slow...

that's going to require an analysis of the specific queries themselves. If you post the actual execution plan of a query that is slow, we can help.

the things, in general, to start looking at are:

Are there clustered indexes on all the tables invloved?

are there indexes to support the query?

is There a WHERE statement being used? if not...that's a table scan of all the data.

are there columsn that are being selected that are not used (ie a VIEW of 50 columns, but you need only three columns)

Are the statistics up to date?

Are you using catch all queries?

Are the WHERE statement parameters SARG-able?

Are any funcitons being used in the WHERE statement?

Are there any inline SELECT statements to get a a single or correlated value (i.e. ColumnName = (SELECT...)

Could the Indexes benefit from INCLUDE columns?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mathew M.Varghese SSCommitted Points: 1929 More actions · Answer 4

Hi,

Please see the following details , execution plan of one of the slow sql stmts.