Getting decent sql server performance despite a slow drive

Question

Getting decent sql server performance despite a slow drive

Viewing 15 posts - 31 through 45 (of 51 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Paul White NZ (4/6/2010)
We do not have enough information to say how Marts time should be best spent, and Marts has already said that he is converting to set-based code as and when time allows.

Heh... on the subject of converting cursors to while loops, we do. It's a waste of time even if the cursor isn't necessarily optimal. 😉

--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)

Paul White SSC Guru Points: 150468 More actions · Answer 2

Marts (4/6/2010)
I can check with my IT guy to see if something can be arranged. Does the tempdb storage need to be quick, or just seperated from other stuff?

In general, tempdb benefits from being low-latency, rather than having a necessarily huge ultimate throughput. Once you have low latency, separating it from other I/O paths can have benefits too. Local (direct-attach) storage would give you both 🙂

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 3

Jeff Moden (4/6/2010)
Heh... on the subject of converting cursors to while loops, we do. It's a waste of time even if the cursor isn't necessarily optimal. 😉

In general, I would agree with you. However, consider that a static cursor stores a full copy of the cursor's input set in tempdb (which is remote, over iSCSI remember). In the case of this particular system, a WHILE loop running over data pages in memory may be significantly faster because of this.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Paul White NZ (4/6/2010)
Marts (4/5/2010)
On our current setup without any modifications, I could move the logs and/or the tempdb to the system drive (raid 1), away from the data files. Would you think that could help at all and if so, should I move logs or tempdb or both?
Moving the logs to dedicated disk (i.e. not shared with anything else at all) is generally the best first move. That said, I do appreciate the restrictions you have here, so if the only immediate option is to move either tempdb or logs away from the data and onto the system drive, I would move the logs. You will not get the full benefit, but it might be a start, if it is easy to try.
As an aside, I have to say to the others suggesting that you hire a professional consultant: read back for the comments about how tight funds are. Any money available would probably be better spent on an upgrade to Windows 2003 and SQL Server 2005.
You appear to be using the software RAID provided by Windows, is that correct?

1) you may have the best "general" first move, but why not do what is BEST for the system, which is to do as I recommended and check the file IO stall scenario and review your data access patterns and then configure drives and/or place files appropriately?

2) I DID note the OP's statement about cost controls and that in no way takes away from my statement about getting a consultant for a day or two. In fact it can SAVE money, at least if the right consultant is brought on board.

3) I do not see what benefit an upgrade to Win2003 or SQL 2005 would provide. It would also require testing and work which they seem to have no bandwidth whatsoever to accomplish.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

Marts (4/6/2010)
I can check with my IT guy to see if something can be arranged. Does the tempdb storage need to be quick, or just seperated from other stuff?
Hmm chkdsk /F did not fix everything in a single pass I hope it does this time.

Your tempdb may not even be used much. Check for IO usage and stalls (see sys.dm_io_virtual_file_stats in BOL). Besides, you only have a few disks to play with so you, as I have recommended several times, REALLY need to know what your apps ask of the IO system so you can serve it up as best as possible given your configuration.

Oh, as for the 3drive RAID 5 with a hot spare: assuming you DO have IO stall problems, I would honestly advise buying a spare disk and simply have it available to plug in if you get a drive failure. That extra drive may really be needed to serve up data instead of sitting there doing nothing for you.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Paul White SSC Guru Points: 150468 More actions · Answer 6

TheSQLGuru (4/6/2010)
1) you may have the best "general" first move, but why not do what is BEST for the system, which is to do as I recommended and check the file IO stall scenario and review your data access patterns and then configure drives and/or place files appropriately?

That was a good suggestion, and I fully support it. Nevertheless, I/O stall only shows you the problems you have right now. Setting the storage up according to general good design principles can also have benefits, beyond the current needs of the system. I'm sure Marty would be pleased to hear your suggestions for monitoring and correcting I/O stall issues, even if you just point him in the direction of the usual references on this subject.

2) I DID note the OP's statement about cost controls and that in no way takes away from my statement about getting a consultant for a day or two. In fact it can SAVE money, at least if the right consultant is brought on board.

Again, I would not disagree - necessarily. I just get the impression that it is hard to get the company to spend any money at all. Finding a good consultant that can teach as well as do, is not always easy, though.

3) I do not see what benefit an upgrade to Win2003 or SQL 2005 would provide. It would also require testing and work which they seem to have no bandwidth whatsoever to accomplish.

As I posted before, Win2K3 + SQL2K5 would support the 4GB RAM currently installed. The current OS and SQL Server limit him to 2GB RAM. Unless you think doubling SQL Server's accessible memory would not help? 😉

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 7

TheSQLGuru (4/6/2010)
Check for IO usage and stalls (see sys.dm_io_virtual_file_stats in BOL).

Not available in SQL Server 2000.

Use fn_virtual_file_stats

SSC article: http://www.sqlservercentral.com/articles/Administration/filestatsexample/862/

Also:

Bob Dorr's definitive work: SQL Server 2000 I/O Basics - highly recommended for its general content

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

That's what I get for trying to rush out a few posts before taking my daughter to school - missed several points! Thanks for clarifications Paul. 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Marts SSC Eights! Points: 937 More actions · Answer 9

Anecdotally (ie not looking too hard yet) I am seeing some positive effects of moving the log files into their own disk array, cpu is lower, several queries that get hit a lot are running faster and I see fewer deadlocks.

I think this will at least get the server off my back for a while, and hopefully once I have the IT guy fix the system mirror, I can move the tempdb there.

I can see a specific trigger that is making a certain query run very slowly, so that would appear to be the next "piece of low hanging fruit", again thanks for the advice.

I have one more small question, when I was assigning the log files to the new location, I was unable to use EM to move or remove the primary log file, so I made it not expand, and shrunk it to 1mb, is this satisfactory, or should I move it to the seperate mirror and how do I do that?

Marts

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

If the primary is empty then it doesn't matter where it is. 🙂 Just avoid putting things there to avoid fragmentation as it grows back out.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Paul White SSC Guru Points: 150468 More actions · Answer 11

TheSQLGuru (4/6/2010)
That's what I get for trying to rush out a few posts before taking my daughter to school - missed several points! Thanks for clarifications Paul. 🙂

Absolutely no problem! 😎

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 12

Marts (4/6/2010)
I have one more small question, when I was assigning the log files to the new location, I was unable to use EM to move or remove the primary log file, so I made it not expand, and shrunk it to 1mb, is this satisfactory, or should I move it to the seperate mirror and how do I do that?

As Mr Guru says, it is not hugely important, but I would tend to want to neaten things up here - but that may be just me 😉

Take a look here: Moving User Databases

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Marts SSC Eights! Points: 937 More actions · Answer 13

Not just you 😉 I keep looking at those orphaned files and want to move them.

When I created the new expandable log files, I created two per database that was moved, one per spindle, was that correct, or is the nature of a log file different, meaning that one file is more appropriate.

Marts

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 14

Marts (4/6/2010)
Not just you 😉 I keep looking at those orphaned files and want to move them.
When I created the new expandable log files, I created two per database that was moved, one per spindle, was that correct, or is the nature of a log file different, meaning that one file is more appropriate.
Marts

It is absolutely improper to have more than one transaction log file per database.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Paul White SSC Guru Points: 150468 More actions · Answer 15

TheSQLGuru (4/6/2010)
It is absolutely improper to have more than one transaction log file per database.

Yes. Log files are different from data files - there is no proportional-fill algorithm.

From Transaction Log Physical Architecture:

"If the log contains multiple physical log files, the logical log will move through all the physical log files before it wraps back to the start of the first physical log file."

This is rarely desirable. A final, related point: it is important to get the initial log file size right. I know I post a lot of links, but the subject is complex, and good explanations already exist. See:

http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx

http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi