tempdb Filegroup and Files

Question

tempdb Filegroup and Files

mike.bishop-593913

SSC-Addicted

Points: 478
More actions
May 20, 2008 at 7:47 am

#187976

I have recently added 3 files to the primary file group of the tempdb database. The server has 8 dual core CPUs. Each file is equal size and all seems well but an issue has just popped up which I'm hoping there's a quick answer why.
Basically there is a process that creates and populates a temporary table. The data in this table needs to be in a specific order. While there is not a clustered index or an order by clause in the process that extracts the data the data always returned it in the order that it was inserted prior to the creation of the new data files. Since the introduction of the new files the data is returned all mixed up.
We can easily fix this by applying an order by clause and this is just fine but what I need to know is why is this happening so I can assess if I need to change any other procedures and processes.
I'm guessing that the data is striped across the files and when it is returned it is compiled in how the data was pulled from the various files which will be dependent on which file(s) it was originally placed. Maybe I've got this one completely wrong but if not I need to ensure that any process that extracts data from a temporary table in a specified order, that I address this before there are any more serious issues.
Thanking you in advance for help.

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

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

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

In SQL Server, unless you specify an order by, the order of rows returned is not guaranteed. You've just been lucky up until now.

I would suggest, any time you need rrows in a specific order, ensure there is an order by present.

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

mike.bishop-593913 SSC-Addicted Points: 478 More actions · Answer 2

Thanks... I have always taken this approach, to implicitly order a query result however this environment which we are trying to improve has a whole raft of bad practices which have lay dormant for however long and unfortunately through an attempt to improve performance I'm sure a few more issues will be woken from their rest!

Thanks again for the response

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 3

I agree... clustered index with an index hint on it or and order by is required and you've been lucky with the order so far.

What are you doing that requires such an order?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mike.bishop-593913 SSC-Addicted Points: 478 More actions · Answer 4

Trust me you don't want to know what's going on but here is a quick answer.

A SQL db is used to populate a handheld device. A number of DDL and DML statements are written to the temp table and then sent to the mobile device and executed. What make this worse is that the updates on the handheld also follow the same process of creating a number of DML statements and sending them to the main SLQ db to be executed. CRAZY.

This is an old application and will be rewritten at some point, but then we all say that.

While the issue is not welcomed it has highlighted a bad practice within this organisation so I hope that it will at least serve as an incentive to consider the standard of code before it is released in future.

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 5

Is the handheld VARCHAR(MAX) capable or VARCHAR(8000) capable? Will the "commands" fit all in a VARCHAR(8000)? Might be able to plow up an old trick or two in that area...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mike.bishop-593913 SSC-Addicted Points: 478 More actions · Answer 6

The problem is not so much of a matter regarding how to either improve the coding or how to integrate the data to and from the handhelds. This issue is about advising my BOSS that this was not as a direct result of the new data files. It is my opinion that introduction of these new files merely highlighted a problem that to this date had not either been noticed or occurred.

The creation of these new files just exposed the bad code. What makes this worse is that there was an order by line that had been commented out. I have spoken with my manager and repeated the comments along with my agreement and it has been decided that we will react to any other issues that are now shown as a result of the new files. Unfortunately with absolutely no documentation, not even an ERD and over 2400 database object we are not in a position to take proactive measures.

Thank you anyway for taking the time to read and reply. It is much appreciated.

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 7

Thanks for the feedback, Mike. I've been involved with such undocumented legacy systems before and I know the hell you're going to go through. Just remember the old saying... "When you're going though hell, keep going". 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)