Strange Issue

Question

Post reply

Strange Issue

Sean Pearce

SSCoach

Points: 15999
More actions
November 8, 2012 at 4:31 am

#284401

Hi,
I have been working on an issue which has me puzzled. I have actually just solved the problem, so it's not urgent, but I still don't understand what might have been wrong.
The problem was a month end procedure running for hours when it has previously run in minutes.
Since the last successful run we have done two things which might be part of it. First we moved the reporting table to another database on the same server. Second we upgraded from 2008 R2 to 2012.
The solution turned out to be simple. I simply scripted the table as drop and create and ran the script.
My question is: "Why"
I tried the following before trying the drop and create.
1. The table was a heap. I created a clustered index.
2. I added OPTION(RECOMPILE) to the affected query.
3. I compared execution plans between dev and production. They were the same.
When running the query, I noticed the logical_reads were skyrocketing while reads and writes were stable.
Any ideas?
The SQL Guy @ blogspot[/url]
@SeanPearceSQL
About Me[/url]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Pearce SSCoach Points: 15999 More actions · Answer 1

OK, just discovered an interesting fact.

Our esteemed "senior" (sic) DBA setup the new server.

TempDb has 1 file, on C:, starting at 8Mb and growth of 10%.

#$%#$^$&^#%^&@@$$%

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

GSquared SSC Guru Points: 260824 More actions · Answer 2

Artoo22 (11/8/2012)
OK, just discovered an interesting fact.
Our esteemed "senior" (sic) DBA setup the new server.
TempDb has 1 file, on C:, starting at 8Mb and growth of 10%.
#$%#$^$&^#%^&@@$$%

Even I know better than that!

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Sean Pearce SSCoach Points: 15999 More actions · Answer 3

It gets better.

Loads of files set to grow at 1MB, including my 30Gb reporting db. The rest on 10%, including a 175Gb store.

They should have asked me to check this server before going live.

Anyway, thanks for listening.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Go have a look on my blog for a post titled something like 'statistics and the ascending datetime column' (google's the easiest way.

Sorry, don't have link handy.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sean Pearce SSCoach Points: 15999 More actions · Answer 5

Thanks Gail. I have read your article previously, but I don't think it applies in this case. The update in question is joining on AccountID, an INT, which is the clustered key.

I actually fixed the process by introducing a temp table for updating and I dump the contents into the reporting table at the end of the proc.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Sean Pearce SSCoach Points: 15999 More actions · Answer 6

I just realised I have been discussing two seperate issues on the same thread, doh.

The month end report was the actual issue on one server. The dodgy server configuration was another server, unrelated.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]