| Author |
Topic |
|
lebedev
Posting Yak Master
126 Posts |
 Posted - 2013-04-06 : 17:28:43
|
| 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
737 Posts |
Posted - 2013-04-08 : 07:11:01
|
| try using Adam machanic's 'sp_whoisactive',that should give you some thing more.mohammad.javeed.ahmed@gmail.com |
 |
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2013-04-08 : 20:09:50
|
| I'll give it a try, thanks! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2013-04-09 : 12:43:45
|
| 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 |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-04-09 : 13:22:26
|
| What is the result of sp_spaceused 'PHYSICAL_COPY_ZONE_ROLLUP' |
|
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2013-04-09 : 13:31:45
|
| 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. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-04-09 : 13:37:32
|
| sorry, meant of the source table(s).is the create index being blocked? |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-04-09 : 13:42:33
|
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 OptimizerTG |
|
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2013-04-09 : 21:38:28
|
| 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)) Sizefrom 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 = 0group 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 |
|
|
|
|
Creating indexed view seems to be stuck