April 7, 2012 at 3:49 pm
GilaMonster (4/7/2012)
Ok, stop the create index, try running it again with maxdop 1 specified.
OK, I'll let you know how it goes.
--------------------------------------
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:51 pm
Unfortunately I can't supply you a query as I'm not at my computer. As Gail said kill the current session id for the create index and re run with maxdop set to 1
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 7, 2012 at 3:53 pm
Perry Whittle (4/7/2012)
Unfortunately I can't supply you a query as I'm not at my computer. As Gail said kill the current session id for the create index and re run with maxdop set to 1
Yep, thanks to both of you for all the help.
I might not be have time to report the results of this until tomorrow, but I will definately report.
--------------------------------------
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:54 pm
Stefan Krzywicki (4/7/2012)
Perry Whittle (4/7/2012)
Unfortunately I can't supply you a query as I'm not at my computer. As Gail said kill the current session id for the create index and re run with maxdop set to 1Yep, thanks to both of you for all the help.
I might not be have time to report the results of this until tomorrow, but I will definately report.
Well tomorrow starts in 5 minutes, so....
😉
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:59 pm
GilaMonster (4/7/2012)
Stefan Krzywicki (4/7/2012)
Perry Whittle (4/7/2012)
Unfortunately I can't supply you a query as I'm not at my computer. As Gail said kill the current session id for the create index and re run with maxdop set to 1Yep, thanks to both of you for all the help.
I might not be have time to report the results of this until tomorrow, but I will definately report.
Well tomorrow starts in 5 minutes, so....
😉
LOL
--------------------------------------
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 4:03 pm
Stefan Krzywicki (4/7/2012)
Perry Whittle (4/7/2012)
Unfortunately I can't supply you a query as I'm not at my computer. As Gail said kill the current session id for the create index and re run with maxdop set to 1Yep, thanks to both of you for all the help.
I might not be have time to report the results of this until tomorrow, but I will definately report.
Please do keep us informed, would have been nice to find out the root cause of this. As a side note how much Ram does the server have and what is the max server ram setting? Please also confirm whether the hardware is hardware based NUMA (you'll see this in the SQL server log at startup)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 7, 2012 at 9:43 pm
Perry Whittle (4/7/2012)
Stefan Krzywicki (4/7/2012)
Perry Whittle (4/7/2012)
Unfortunately I can't supply you a query as I'm not at my computer. As Gail said kill the current session id for the create index and re run with maxdop set to 1Yep, thanks to both of you for all the help.
I might not be have time to report the results of this until tomorrow, but I will definately report.
Please do keep us informed, would have been nice to find out the root cause of this. As a side note how much Ram does the server have and what is the max server ram setting? Please also confirm whether the hardware is hardware based NUMA (you'll see this in the SQL server log at startup)
I will look into your 2 most recent questions.
The creation of the index you suggested did complete after 2 hours. That's much slower than it should be, but at least it completed.
--------------------------------------
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 8, 2012 at 3:07 am
Stefan Krzywicki (4/7/2012)
The creation of the index you suggested did complete after 2 hours. That's much slower than it should be, but at least it completed.
Ok the index i suggested was basic with no included columns. Drop this index and try creating your original one
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 8, 2012 at 5:51 am
Perry Whittle (4/8/2012)
Stefan Krzywicki (4/7/2012)
The creation of the index you suggested did complete after 2 hours. That's much slower than it should be, but at least it completed.Ok the index i suggested was basic with no included columns. Drop this index and try creating your original one
I need to give it a new name because SQL Server is saying it (my original one) or statistics for it exist.
It doesn't show up when I look for it and I don't seem to be able to drop it. (Drop hangs like Create) Do I need to create statistics for it or is there another way to fix that?
--------------------------------------
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 9, 2012 at 7:11 am
Stefan Krzywicki (4/8/2012)
Perry Whittle (4/8/2012)
Stefan Krzywicki (4/7/2012)
The creation of the index you suggested did complete after 2 hours. That's much slower than it should be, but at least it completed.Ok the index i suggested was basic with no included columns. Drop this index and try creating your original one
I need to give it a new name because SQL Server is saying it (my original one) or statistics for it exist.
It doesn't show up when I look for it and I don't seem to be able to drop it. (Drop hangs like Create) Do I need to create statistics for it or is there another way to fix that?
Your index may not exist but stats for it may. Query sys.stats to find out if there are stats there and if there are run DROP STATISTICS schema.table.stats_name
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 9, 2012 at 8:15 am
Jack Corbett (4/9/2012)
Stefan Krzywicki (4/8/2012)
Perry Whittle (4/8/2012)
Stefan Krzywicki (4/7/2012)
The creation of the index you suggested did complete after 2 hours. That's much slower than it should be, but at least it completed.Ok the index i suggested was basic with no included columns. Drop this index and try creating your original one
I need to give it a new name because SQL Server is saying it (my original one) or statistics for it exist.
It doesn't show up when I look for it and I don't seem to be able to drop it. (Drop hangs like Create) Do I need to create statistics for it or is there another way to fix that?
Your index may not exist but stats for it may. Query sys.stats to find out if there are stats there and if there are run DROP STATISTICS schema.table.stats_name
Great, thanks! I should have known there'd be something in sys or dmvs for 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 10, 2012 at 5:30 am
HW is supposed to be ok, how about the batteries ? ( I/O caching on/off will be noticeable )
Did you try a scenario "disable index + load + enable index by rebuild" ?
( in stead of drop index and re-creating it )
Maybe the engine can use a less brutal way for allocating space for the index rebuild.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 11, 2012 at 9:11 am
Jack Corbett (4/9/2012)
Stefan Krzywicki (4/8/2012)
Perry Whittle (4/8/2012)
Stefan Krzywicki (4/7/2012)
The creation of the index you suggested did complete after 2 hours. That's much slower than it should be, but at least it completed.Ok the index i suggested was basic with no included columns. Drop this index and try creating your original one
I need to give it a new name because SQL Server is saying it (my original one) or statistics for it exist.
It doesn't show up when I look for it and I don't seem to be able to drop it. (Drop hangs like Create) Do I need to create statistics for it or is there another way to fix that?
Your index may not exist but stats for it may. Query sys.stats to find out if there are stats there and if there are run DROP STATISTICS schema.table.stats_name
I looked at the statistics for this table. There's a single row for the Primary Key and a single row for the new index that had been successfully created. There is none for the old index, but there are 15 rows for statistics that start with _WA_Sys_ They're all listed as auto_created.
--------------------------------------
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 11, 2012 at 9:19 am
Yeah, the _WA_sys are auto-created statistics.
Something very odd there... Still can't create that index?
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 11, 2012 at 9:24 am
GilaMonster (4/11/2012)
Yeah, the _WA_sys are auto-created statistics.Something very odd there... Still can't create that index?
Last time I tried to create the index, it told me I couldn't because it already existed, so I figured I'd start the query to create it and sure enough, this time it tried to run. I guess the statistics were removed by some automatic cleanup.
I now have a simpler version of the index on the table (no included columns) and the queries that run against that table are now as fast as or faster than when the old index was on there, so I'm not going to mess with it for now and leave it as is.
I appreciate the assistance everyone gave. If I figure anything further out, I'll post it here.
--------------------------------------
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
Viewing 15 posts - 46 through 60 (of 61 total)
You must be logged in to reply to this topic. Login to reply