April 7, 2012 at 3:05 pm
Stefan Krzywicki (4/7/2012)
Perry Whittle (4/7/2012)
Run this and let me know what happens
CREATE NONCLUSTERED INDEX NoddyIDX ON dbo.FTD
(ProdID ASC)
Same problem as the main index. Runs for over an hour and nothing happens.
I'm going to try copying the table, dropping the old one, renaming the new one and creating the indexes on that. I'll have to wait until tomorrow though.
No, stop!!!
Check the DMV sys.dm_exec_requests for an existing index operation on the table.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 7, 2012 at 3:08 pm
Perry Whittle (4/7/2012)
Stefan Krzywicki (4/7/2012)
Perry Whittle (4/7/2012)
Run this and let me know what happens
CREATE NONCLUSTERED INDEX NoddyIDX ON dbo.FTD
(ProdID ASC)
Same problem as the main index. Runs for over an hour and nothing happens.
I'm going to try copying the table, dropping the old one, renaming the new one and creating the indexes on that. I'll have to wait until tomorrow though.
No, stop!!!
Check the DMV sys.dm_exec_requests for an existing index operation on the table.
OK, will do.
Should I do that while one is running? 'cause when the other one had no results after an hour I cancelled it.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 7, 2012 at 3:11 pm
Perry Whittle (4/7/2012)
Stefan Krzywicki (4/7/2012)
Perry Whittle (4/7/2012)
Run this and let me know what happens
CREATE NONCLUSTERED INDEX NoddyIDX ON dbo.FTD
(ProdID ASC)
Same problem as the main index. Runs for over an hour and nothing happens.
I'm going to try copying the table, dropping the old one, renaming the new one and creating the indexes on that. I'll have to wait until tomorrow though.
No, stop!!!
Check the DMV sys.dm_exec_requests for an existing index operation on the table.
I checked while it was stopped and there were no existing index operations on the table.
I started trying to create it again and I see a CREATE INDEX command on the database, but the status is "suspended"
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 7, 2012 at 3:12 pm
Perry Whittle (4/7/2012)
Stefan Krzywicki (4/7/2012)
Perry Whittle (4/7/2012)
Run this and let me know what happens
CREATE NONCLUSTERED INDEX NoddyIDX ON dbo.FTD
(ProdID ASC)
Same problem as the main index. Runs for over an hour and nothing happens.
I'm going to try copying the table, dropping the old one, renaming the new one and creating the indexes on that. I'll have to wait until tomorrow though.
No, stop!!!
Check the DMV sys.dm_exec_requests for an existing index operation on the table.
Yes run it now with all others cancelled.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 7, 2012 at 3:16 pm
Perry Whittle (4/7/2012)
Perry Whittle (4/7/2012)
Stefan Krzywicki (4/7/2012)
Perry Whittle (4/7/2012)
Run this and let me know what happens
CREATE NONCLUSTERED INDEX NoddyIDX ON dbo.FTD
(ProdID ASC)
Same problem as the main index. Runs for over an hour and nothing happens.
I'm going to try copying the table, dropping the old one, renaming the new one and creating the indexes on that. I'll have to wait until tomorrow though.
No, stop!!!
Check the DMV sys.dm_exec_requests for an existing index operation on the table.
Yes run it now with all others cancelled.
The blocking_session_id on the suspended CREATE INDEX is 0
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 7, 2012 at 3:23 pm
Ok so there is something blocking the operation, strange. Can you force the database offline using rollback immediate then bring it back online and after about 15 mins re run the DMV query.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 7, 2012 at 3:24 pm
Blocking session of 0 means it's not blocked by another session
What's the wait type?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 7, 2012 at 3:29 pm
GilaMonster (4/7/2012)
Blocking session of 0 means it's not blocked by another sessionWhat's the wait type?
CXPACKET
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 7, 2012 at 3:30 pm
Perry Whittle (4/7/2012)
Ok so there is something blocking the operation, strange. Can you force the database offline using rollback immediate then bring it back online and after about 15 mins re run the DMV query.
I'm not going to be able to take the database offline until tomorrow. We have processes that need to run today and everything is behind as it is.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 7, 2012 at 3:33 pm
Stefan Krzywicki (4/7/2012)
GilaMonster (4/7/2012)
Blocking session of 0 means it's not blocked by another sessionWhat's the wait type?
CXPACKET
That's not the real wait type.
Only one row in sys.dm_exec_requests? If so check sys.dm_os_waiting_tasks and see what the other wait type is (there will almost always be another)
p.s. Can you run an update statistics (preferably with fullscan) and then try the index again?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 7, 2012 at 3:34 pm
I looked at the Windows System log and several times an hour I see something called the The WMI Performance Adapter service entering a running state and then a stopped state. Should this be running on a SQL Server box?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 7, 2012 at 3:37 pm
GilaMonster (4/7/2012)
Blocking session of 0 means it's not blocked by another sessionWhat's the wait type?
Doh totally misread that one lol
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 7, 2012 at 3:40 pm
Stefan Krzywicki (4/7/2012)
I'm not going to be able to take the database offline until tomorrow..
Don't worry not necessary
You need to find the actual wait type. There should be multiple threads and one or more are waiting on something.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 7, 2012 at 3:43 pm
GilaMonster (4/7/2012)
Stefan Krzywicki (4/7/2012)
GilaMonster (4/7/2012)
Blocking session of 0 means it's not blocked by another sessionWhat's the wait type?
CXPACKET
That's not the real wait type.
Only one row in sys.dm_exec_requests? If so check sys.dm_os_waiting_tasks and see what the other wait type is (there will almost always be another)
p.s. Can you run an update statistics (preferably with fullscan) and then try the index again?
The wait type in dm_os_waiting_tasks is also CXPACKET. I joined
ON R.task_address = W.waiting_task_address
I also saw the resource_description is "exchangeEvent id=Port803bae00 WaitType=e_waitPortOpen nodeId=0"
There's a blocking_task_address listed, but when I query dm_exec_requests with that address, there's no result.
There's only one row for the session id in waiting_tasks.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 7, 2012 at 3:48 pm
Ok, stop the create index, try running it again with maxdop 1 specified.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 31 through 45 (of 61 total)
You must be logged in to reply to this topic. Login to reply