April 7, 2012 at 9:37 am
I have an index on a large-ish table. The table is around 775,000,000 rows with half a million or so being added daily.
The index is a non-clustered index and is important for our queries. I've been dropping it (before our nightly load) and then creating it nightly.
It has been running fine for over a year. Thursday night it stopped finishing the creation step. It starts and then just hangs. I tried to let it run again last night, but it just never completed. Normally it takes 30 minutes to an hour to create, it ran for 8 hours and never completed. I'd tried to create it manually yesterday, but that ran for 3 hours with no result.
Here's the index
CREATE NONCLUSTERED INDEX IX_FTD_ProdID ON dbo.FTD
(ProdID ASC)
INCLUDE ( TransTypeID, Units, Price, TransDesc)
WITH (SORT_IN_TEMPDB = ON)
I don't know why this would stop working all of a sudden. I looked in the Error log and there's a lot of instances of
SQL Server has encountered 281 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb08.ndf] in database [tempdb] (2). The OS file handle is 0x0000000000000B58. The offset of the latest long I/O is: 0x000000075a0000
I have 16 tempdb files, one for each processor, and the error is across all of them at different times. It also hits msdb and master. These are all on the same hard drive.
Our hardware guy says he's checked the disks and there's nothing wrong there and CHECKDB was run on the database and found no problems.
Everything else seems to run just fine. We stopped and started the SQL Server service, but haven't tried rebooting the server yet.
Things that use that index are running slower, but nothing else seems to be.
I'm going to try turning off SORT_IN_TEMPDB. Maybe there's a problem in tempdb. I'll run a checkdb on it too.
Any ideas?
EDIT: Forgot to mention: SQL Server 2008 R2
--------------------------------------
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 9:44 am
CHECKDB on tempdb showed no problems.
--------------------------------------
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 11:22 am
Snapshot isolation?
The warnings point to something slowing down the IOs on teh TempDB drive. Check that there hasn't been any changes to the Io subsystem recently
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 11:26 am
GilaMonster (4/7/2012)
Snapshot isolation?The warnings point to something slowing down the IOs on teh TempDB drive. Check that there hasn't been any changes to the Io subsystem recently
What do you mean by "snapshot isolation"?
And I'll ask our server people about the IO subsystem.
Thanks!
--------------------------------------
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 11:33 am
Can you supply the table definition too?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 7, 2012 at 11:37 am
Table Definition
[dbo].[FTD](
[TransDetailID] [int] NOT NULL,
[TransID] [uniqueidentifier] NOT NULL,
[ProdID] [int] NULL,
[FP] [int] NOT NULL,
[TransTypeID] [smallint] NULL,
[Units] [smallint] NULL,
[Price] [smallmoney] NULL,
[UnitOfMeasure] [varchar](10) SPARSE NULL,
[ComboSeq] [int] SPARSE NULL,
[ComboID] [int] SPARSE NULL,
[TransDesc] [varchar](50) NULL,
[TenderID] [smallint] SPARSE NULL,
[PromoID] [int] SPARSE NULL,
[ScannedItem] [bit] SPARSE NULL,
[ProductWeight] [float] SPARSE NULL,
[Dollars] AS (case when isnull([UNITS],(0))<>(0) then [Units]*[Price] else [Price] end),
CONSTRAINT [PK_FTD_TransID_TransDetailID] PRIMARY KEY CLUSTERED
(
[TransID] ASC,
[FP] ASC,
[TransDetailID] ASC
)
--------------------------------------
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 11:42 am
Stefan Krzywicki (4/7/2012)
GilaMonster (4/7/2012)
Snapshot isolation?The warnings point to something slowing down the IOs on teh TempDB drive. Check that there hasn't been any changes to the Io subsystem recently
What do you mean by "snapshot isolation"?
Are you using snapshot isolation?
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 11:47 am
GilaMonster (4/7/2012)
Stefan Krzywicki (4/7/2012)
GilaMonster (4/7/2012)
Snapshot isolation?The warnings point to something slowing down the IOs on teh TempDB drive. Check that there hasn't been any changes to the Io subsystem recently
What do you mean by "snapshot isolation"?
Are you using snapshot isolation?
Only for master and msdb. I checked sys.databases to be sure.
--------------------------------------
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:03 pm
Other things to consider
How many logical drives are you using?
What are the physical disk configs underneath these?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 7, 2012 at 12:08 pm
Perry Whittle (4/7/2012)
Other things to considerHow many logical drives are you using?
What are the physical disk configs underneath these?
System dbs on C
log files on G
data files on H
That's all the drives on the machine.
--------------------------------------
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:37 pm
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?
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:42 pm
Stefan Krzywicki (4/7/2012)
Perry Whittle (4/7/2012)
Other things to considerHow many logical drives are you using?
What are the physical disk configs underneath these?
System dbs on C
log files on G
data files on H
That's all the drives on the machine.
What physical disk configurations sit under these?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 7, 2012 at 12:43 pm
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?
Up until yesterday, it had been running in 30 minutes to an hour.
--------------------------------------
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:44 pm
Perry Whittle (4/7/2012)
Stefan Krzywicki (4/7/2012)
Perry Whittle (4/7/2012)
Other things to considerHow many logical drives are you using?
What are the physical disk configs underneath these?
System dbs on C
log files on G
data files on H
That's all the drives on the machine.
What physical disk configurations sit under these?
I'm sorry, I don't know how to answer that question.You mean other than each of those being a different physical drive?
--------------------------------------
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:46 pm
Stefan Krzywicki (4/7/2012)
Well, I had been running the index creation with WITH SORT_IN_TEMPDB = ON, so wouldn't the sort space be happening on C?
Yes it would hit C drive
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 61 total)
You must be logged in to reply to this topic. Login to reply