SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Creating indexed view seems to be stuck
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lebedev
Posting Yak Master

USA
126 Posts

Posted - 04/06/2013 :  17:28:43  Show Profile  Reply with Quote
Hello,

I am creating an indexed view on a large database and it seems that it is stuck on CREATE CLUSTERED INDEX after completing CREATE VIEW. It's been sitting on CREATE CLUSTERED INDEX for 3 hours now. I checked Activity Monitor and the process that's creating the indexed view is not blocked by anything. CPU usage on the server is around average and disk queue length is way below 1.

Is there a way to check if creating clustered index is making progress? Maybe using some system views?

Thanks,

Alec

ahmeds08
Aged Yak Warrior

India
620 Posts

Posted - 04/08/2013 :  07:11:01  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
try using Adam machanic's 'sp_whoisactive',that should give you some thing more.

mohammad.javeed.ahmed@gmail.com
Go to Top of Page

lebedev
Posting Yak Master

USA
126 Posts

Posted - 04/08/2013 :  20:09:50  Show Profile  Reply with Quote
I'll give it a try, thanks!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36823 Posts

Posted - 04/08/2013 :  20:36:58  Show Profile  Visit tkizer's Homepage  Reply with Quote
How big is the table? What is it waiting on? Did you specify the ONLINE=ON option?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

lebedev
Posting Yak Master

USA
126 Posts

Posted - 04/09/2013 :  12:43:45  Show Profile  Reply with Quote
I am creating a CLUSTERED INDEX on an indexed view. I don't know how big the result set of the view is because it takes a long time to run it.

The statement I executed is this:
CREATE UNIQUE CLUSTERED INDEX IDX_PHYSICAL_COPY_ZONE_ROLLUP_IDX_0 ON dbo.PHYSICAL_COPY_ZONE_ROLLUP (ZoneId, IsMarkedDeleted, LastModYearUTC, LastModMonthUTC);

According to sp_whoIsActive the sql_text of the corresponding statement is:
insert [dbo].[PHYSICAL_COPY_ZONE_ROLLUP] select * from [dbo].[PHYSICAL_COPY_ZONE_ROLLUP]

I think it is a good idea to run it with ONLINE=ON because as it stands the statement is blocking a lot of other activity, but is not itself blocked.

Thanks
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/09/2013 :  13:22:26  Show Profile  Visit russell's Homepage  Reply with Quote
What is the result of sp_spaceused 'PHYSICAL_COPY_ZONE_ROLLUP'
Go to Top of Page

lebedev
Posting Yak Master

USA
126 Posts

Posted - 04/09/2013 :  13:31:45  Show Profile  Reply with Quote
The result of sp_spaceused 'PHYSICAL_COPY_ZONE_ROLLUP' is NULLs and 0s. That's probably because PHYSICAL_COPY_ZONE_ROLLUP is still just a view and not an indexed view since CREATE CLUSTERED INDEX takes too long to complete.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/09/2013 :  13:37:32  Show Profile  Visit russell's Homepage  Reply with Quote
sorry, meant of the source table(s).

is the create index being blocked?

Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/09/2013 :  13:42:33  Show Profile  Reply with Quote
quote:
I don't know how big the result set of the view is because it takes a long time to run

How big are the underlying tables?
Are you certain that your view definition isn't generating a cross join affect (cartesian product)?

Be One with the Optimizer
TG
Go to Top of Page

lebedev
Posting Yak Master

USA
126 Posts

Posted - 04/09/2013 :  21:38:28  Show Profile  Reply with Quote
We tested this view in QA and I also created it on a customer database which is about 1/3 the size of the current database, which took 30 minutes.
View creation is not being blocked, but it is blocking other activity.
Here is the view definition and I realize that we inner join on a LIKE condition, but we need to do that. PHYSICAL_COPY table has 50M rows in the current database where the view is taking days to create. There are 10K rows in ZONE table.

SELECT YEAR(DATEADD(MINUTE, case when last_modified_timestamp is null then 0 when last_modified_timestamp / 60000 BETWEEN 0 AND 2147483647 then last_modified_timestamp / 60000 when last_modified_timestamp < 0 then 0 else 2147483647 end , {d '1970-01-01'})) as LastModYearUTC,
MONTH(DATEADD(MINUTE, case when last_modified_timestamp is null then 0 when last_modified_timestamp / 60000 BETWEEN 0 AND 2147483647 then last_modified_timestamp / 60000 when last_modified_timestamp < 0 then 0 else 2147483647 end , {d '1970-01-01'})) as LastModMonthUTC,
z.ID as ZoneId, pc.IS_MARKED_DELETED IsMarkedDeleted, COUNT_BIG(*) Cnt, SUM(ISNULL(SIZE, 0)) Size
from dbo.ZONE z
inner join dbo.PHYSICAL_COPY pc on pc.REPOSITORY_TYPE = z.REPOSITORY_TYPE and pc.CANONICAL_LOCATION_CHECKSUM like z.CANONICAL_LOCATION_CHECKSUM + '%' and pc.CANONICAL_LOCATION like z.CANONICAL_LOCATION + '%'
where z.IS_MARKED_DELETED = 0
group by z.ID,
YEAR(DATEADD(MINUTE, case when last_modified_timestamp is null then 0 when last_modified_timestamp / 60000 BETWEEN 0 AND 2147483647 then last_modified_timestamp / 60000 when last_modified_timestamp < 0 then 0 else 2147483647 end , {d '1970-01-01'})),
MONTH(DATEADD(MINUTE, case when last_modified_timestamp is null then 0 when last_modified_timestamp / 60000 BETWEEN 0 AND 2147483647 then last_modified_timestamp / 60000 when last_modified_timestamp < 0 then 0 else 2147483647 end , {d '1970-01-01'})),
pc.IS_MARKED_DELETED

Edited by - lebedev on 04/09/2013 21:39:21
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000