April 7, 2012 at 12:48 pm
Stefan Krzywicki (4/7/2012)
GilaMonster (4/7/2012)
So H is getting hit by the index read, the sort space and the new index? Ow. Could be that you've just surpassed what the underlying IO subsystem can handle.Got historical benchmarks?
Well, I had been running the index creation with WITH SORT_IN_TEMPDB = ON, so wouldn't the sort space be happening on C?
Sorry, I read that as 'system on C'
Still curious about historical vs current IO benchmarks.
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 12:49 pm
Stefan Krzywicki (4/7/2012)
I'm sorry, I don't know how to answer that question.You mean other than each of those being a different physical drive?
Yes, exactly. Get your server admin to confirm the underlying physical setup, My guess is you have a mirrored array for drive C and maybe have a failed disk
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 7, 2012 at 12:52 pm
Perry Whittle (4/7/2012)
Stefan Krzywicki (4/7/2012)
I'm sorry, I don't know how to answer that question.You mean other than each of those being a different physical drive?Yes, exactly. Get your server admin to confirm the underlying physical setup, My guess is you have a mirrored array for drive C and maybe have a failed disk
I'm pretty sure each of these are a single drive physically on the server.
I had our server admin check the hardware and he said there's no problems there. From some research I'd done on the error I saw in the log, checking the hardware was the first thing I thought to do.
--------------------------------------
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 12:54 pm
GilaMonster (4/7/2012)
Stefan Krzywicki (4/7/2012)
GilaMonster (4/7/2012)
So H is getting hit by the index read, the sort space and the new index? Ow. Could be that you've just surpassed what the underlying IO subsystem can handle.Got historical benchmarks?
Well, I had been running the index creation with WITH SORT_IN_TEMPDB = ON, so wouldn't the sort space be happening on C?
Sorry, I read that as 'system on C'
Still curious about historical vs current IO benchmarks.
Oh, no problem.
Is there a way to access this information now or would I have had to be capturing it all along. All I have is historical run times.
--------------------------------------
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 12:54 pm
Single drive, no RAID, no redundancy?
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 12:56 pm
Stefan Krzywicki (4/7/2012)
Is there a way to access this information now or would I have had to be capturing it all along. All I have is historical run times.
You would have to have been capturing it all along.
Without that, there's no way to tell whether the IO has been gradually heading towards max capacity or if's a sudden jump, or a sudden drop in IO throughput.
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 12:59 pm
One other thing to note, we're running the backups on this machine through EMC software's SQL Server backup system. Yesterday and today it has been far slower than normal and has been showing failures in the SQL error log, though our admin who runs the backups swears there have been no failures.
I'm thinking it doesn't report failures (at least where he knows to look for them), but they appear in the SQL log. If they cause "lost" IO operations where it and Windows can't recover or terminate them, it would cause (at least by my admittedly limited understanding of such things) problems like the ones I reported earlier. I didn't mention this earlier because I wanted to try to eliminate other possible sources of the problem and I thank you both for your assistance with that.
I mention it now in case it would help in diagnosing any other possible causes.
--------------------------------------
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 1:01 pm
GilaMonster (4/7/2012)
Single drive, no RAID, no redundancy?
As far as I know, yes. It is the impression I've gotten from meetings we've had about the system and our attempts to get a SAN. I could be wrong though. I'll ask now.
--------------------------------------
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 1:03 pm
Before going any further I would like you to confirm exactly the configuration of the logical disks, also is this server a virtual machine by any chance?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 7, 2012 at 1:04 pm
GilaMonster (4/7/2012)
Stefan Krzywicki (4/7/2012)
Is there a way to access this information now or would I have had to be capturing it all along. All I have is historical run times.You would have to have been capturing it all along.
Without that, there's no way to tell whether the IO has been gradually heading towards max capacity or if's a sudden jump, or a sudden drop in IO throughput.
Crap, that's something that I'd never thought to capture.
--------------------------------------
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 1:05 pm
Perry Whittle (4/7/2012)
Before going any further I would like you to confirm exactly the configuration of the logical disks, also is this server a virtual machine by any chance?
I have emailed our server person. I'll post a response here when I hear back.
It is not a virtual machine, though they plan on going that direction at some point in the future.
--------------------------------------
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 1:14 pm
I would be very surprised if a single drive can handle the IO loads you're talking about.
Have you checked the windows system and application logs to make sure there are no IO errors reported.
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 1:19 pm
GilaMonster (4/7/2012)
I would be very surprised if a single drive can handle the IO loads you're talking about.Have you checked the windows system and application logs to make sure there are no IO errors reported.
No, good idea, I'll look.
Another strange thing. I went to try to create the index without using the tempdb and it told me it or statistics for it already existed, but I don't see the index listed under the table. I'm trying to drop it, but I'm having problems with that as well.
--------------------------------------
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 1:26 pm
Run this and let me know what happens
CREATE NONCLUSTERED INDEX NoddyIDX ON dbo.FTD
(ProdID ASC)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 7, 2012 at 3:00 pm
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.
--------------------------------------
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 - 16 through 30 (of 61 total)
You must be logged in to reply to this topic. Login to reply