June 24, 2014 at 11:12 am
I'm trying to DROP a Primary Key and it will not drop.
I check the percent_complete and it is stuck at zero percent.
Wait Type is primarily CXPACKET.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 24, 2014 at 11:21 am
Did you drop your nonclustered indexes first?
For details on CXPACKET, Paul's blog on wait stats is at http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/.
June 24, 2014 at 11:29 am
Ed Wagner (6/24/2014)
Did you drop your nonclustered indexes first?For details on CXPACKET, Paul's blog on wait stats is at http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/.
No, I've been bad.:-)
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 24, 2014 at 11:42 am
I dropped the Non Clustered Index and it is in a suspended State and a PAGEIOLATCH_EX Wait Type.
Percent Complete steady at zero percent.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 24, 2014 at 12:05 pm
How about when you create the Indexes, what order do you use?
I'm changing the primary key from CLUSTERED to Non-Clustered.
I'm going to create a Clustered Index on columns that are being Sorted By and per the Execution Plan that is taking up 61%.
Recreating an existing Non Clustered.
Any feedback would be greatly appreciated
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 24, 2014 at 12:17 pm
Welsh Corgi (6/24/2014)
I dropped the Non Clustered Index and it is in a suspended State and a PAGEIOLATCH_EX Wait Type.Percent Complete steady at zero percent.
Probably because it's waiting for the drop of the clustered index to complete. You should drop nonclustered indexes before dropping a cluster, not during.
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
June 24, 2014 at 12:28 pm
ok, thanks.
I should have made a post earlier but I had a production issue.
I was able to Delete all Indexes and the Primary Key.
Now I'm having problems creating a Non CLUSTERED Index.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 24, 2014 at 12:38 pm
Welsh Corgi (6/24/2014)
Now I'm having problems creating a Non CLUSTERED Index.
We can help but we need to know what the problem is.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 24, 2014 at 1:25 pm
Sean Lange (6/24/2014)
Welsh Corgi (6/24/2014)
Now I'm having problems creating a Non CLUSTERED Index.We can help but we need to know what the problem is.
Sorry for not providing better details.
After dropping all of the Indexes and the Primary Key when I Execute a CREATE Index Statement last_wait is SLEEP_TASK and percent_complete remain at zero.
If I cancel it the percent_complete moves pretty quickly.
Other than my process there is barely any activity on the Server.
Thank you.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 25, 2014 at 8:16 am
I ended up renaming the table and recreating it with the proper Indexes and loading the table.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply