August 6, 2008 at 5:46 am
Hello,
As said in my subject and description, our (new) 3rd party product uses cursors, many cursors, tons of cursors. They're all over the place, in producedures, triggers, functions, you name it.
Queries or updates on a few thousands records that would normally run in a split seconds take minutes. I saw triggers where they reprocess the same set of records to validate different conditions in separate cursors.
I think you all get the picture. Unfortunately, having them change that is not an option so now I'm trying to find ways to speed things up by tuning the server and the database.
Here's the environment.
Super Micro machine.
Windows 2003 R2
SQL 2005 SP2
32 Gigs of memory
Two 2.4Ghz quad core CPU's
We have 3 mirrored SCSI SATA disks
The database, transaction logs and tempdb each have their own disk.
We are still at the training stage but there's gonna be around 75 people using this application when we go live and I'm afraid it's gonna be slow. I don't have much experience with SQL servers so I'm wondering if anyone could suggest things that would help. What could I do to help speed things up without risk of interfering with their application ?
August 6, 2008 at 5:52 am
I see, you are affected by CAS -short for Crappy Application Syndrome.
CAS is a serious condition the only cure is major surgery -kill the CAS causing agent but in some cases this is not possible because of zillions of political reasons.
You can live with CAS like a diabetic can live with diabetis.
How to make things better?
Three words: Processors, Indexes and Memory.
Flood your box with as many processors as it can hold
Pump-up memory to the maximum your hardware/software supports.
Analyze the CAS queries and start building your own indexes to help them run better.
Also, be sure your TempDB database is made up of as many datafiles as core processors you have in the box... try to distribute datafiles in different disks.
Good luck! 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 6, 2008 at 7:05 am
If you are having performance problems with this "new" application before implementation, now is the time to correct the issue and seek a new vendor for the software.
The problem will only get worse.
One option may be to switch to MySQL or Oracle if the product supports them. They are cursor-based database engines that perform much better with row-by-row processing (since that's basically all they do).
Throwing hardware at a problem like this is at best a short-term solution. You are about to implement software that was probably not well-designed for the technology platform it is running in. The only real answer to that is to not do it. Make sure your management knows that there is an issue that is inherant in the product. It is likely to be a very high cost over time.
August 6, 2008 at 7:41 am
Changing the application at this time is simply not an option for many reasons. I realize that there's no way throwing hardware at the problem will completely compensate for a badly designed application. However as I am nothing more than a self-trained DBA I'm sure there are many things I don't know or can't even begin to fathom that can be done in the server and database configurations to help speed things up a little.
For instance, as I was searching about memory on the forum I stumped on a post that suggests it's a good practice to create one tempdb for each CPU in the server. Since I'm using two quad core cpu's, would it be wise to have 8 tempdb's ? If so, should I split them on different drives ?
Another thing I'm wondering about is that our hardware guys told me we have 5 hard drives in the server. If I understood him well, the main 3 drives are mirrored but one of the mirror drives has been partitioned and 2 of the 3 main drives are mirrored on logical drives. Since my tempdb, database and transaction logs are each on their own drive, does it slow down my database that 2 of these drives are actually mirrored on a single one ?
Memory: I read here and on MSDN Forums that I should set the maximum memory to leave enough for the OS and that the minimum memory should bet set at about 1 Gig lower than max. I don't understand this as it conflicts with something I read a few weeks ago about how SP2 changed the way SQL Server uses memory. As I recall, SQL Server would only use x% of the first 8 gigs, x% of the next 16 and x% of the balance (my numbers are probably wrong here but it was something like that), apparently these percentages were reduced in SP2. There was an example given that a server with 8 Gigs had 6 used by SQL Server and went down to 4 after SP2 was installed. If that is true then why would someone need to make sure to leave memory available to the OS ? Isn't that what I just described does ? We're using Windows 2003 R2, SQL 2005 Standard 64 bits and I've enabled AWE memory allocation. Should I still bother about the memory settings ? I've replaced the insane default max value that is put there by 128 Gigs, since our server "only" has 32 I figured this would give SQL all the memory it can possibly use.
Boost SQL Server Priority: Is it good practice to use this ? Does it improve performance in any ways ? Our server will only do two things, run one instance of SQL Server and one instance of OLAP which will serve a few small cubes and one I would classify as medium (around 250,000 records with approximately 35 dimensions and 15 measures). Would "Boost SQL Server Priority" have negative impact on the servers ability to properly run the OLAP Service ?
August 6, 2008 at 10:23 am
Even with SP2, you still need to tell SQL Server what it's max memory is. It will consume everything if you leave the max at the default. I have SP2 installed and it does behave this way. I'm not sure what the forum is talking about unless they are giving approximate calculations to help you determine where to set it at.
Another thing to note - when you set the max memory, that is only for the engine. It does not account for SSAS, SSIS, SSRS or any other services you are running so be sure to leave room for them as well.
Overall, I would definitely suggest adding more memory to the box so everything has what it needs. As far as adding processors, that would only be necessary if you see all of your cores going over 80% utilization. If they are under, say, 50% utilization, I wouldn't bother with the expense.
August 7, 2008 at 5:04 am
I understand being stuck with a bad application, but since you are still implementing it, now is the time to try really, really hard to cut out the cancer before it spreads.
Ok, onto the other items. You can only have one TempDB. It can have multiple file groups, but it only really uses the primary file group for most operations, so I cannot see any use in splitting it into multiple file groups in most cases. Since you have a database that is lots of row-by-row operations, TempDB may not be a big concern to you.
Only having 5 physical drives kinda sucks. If you are going to have a read/write intensive system, having several controller cards and lots of drives can be of real benefit. Here is the thing on drives - you want a fast write operation (so go for a fast RAID configuration rather than one that gives you more drive space). You also want as many read and write heads as possible and separate your conflicting operations. For example, if you have log files being written to and data files being updated, have the log files on a different set of physical disks so the write heads do not have to move around a lot between operations. Putting TempDB and the log files on the same drive is usually not good. Mirrored drives are usually ok for data files, try to stay away from RAID 5 or RAID 6 because the XOR operations for the parity maps slows things down.
The memory thing - you either don't remember the article correctly, or mis-read it. SQL Server internally cuts it's available memory into some individual pieces. Some of this memory can be used for caching data and some of it can be used for caching execution plans. The sp2 change increased the percentage of memory allocated to caching execution plans because of a somewhat common performance problem happening on large databases in which the memory space for storing the plans would run low and the server spent too many resources trading out memory. You only have control over the overall allocated memory - for this you should leave something for other applications and the OS. Start with dynamic memory allocation allowing all memory and do some testing. Decrease the amount of memory slowly until you see a performance drop. Then try setting the minimum to the maximum to see if the server is happier with a fixed memory allocation. On dedicated SQL 2005 servers without reporting services, SSIS, or SSAS running, I have found dynamic memory allocation to actually be better in most cases, but this really needs some testing for the individual system.
You will have to test the Boost priority on your system. If you use MOLAP storage, the SQL Server does not do anything for your Analysis Services queries - it only gets used during processing and then SSAS handles all query requests - even drillthrough requests. Memory management for SSAS is very different and you will need to leave memory for SSAS when you configure your max memory on your SQL Server. You really have to be running a load against both while trying to set the memory settings for the SQL Server. I would highly recommend finding another server for Analysis Services. If the cubes are small, find a workstation with two drives you can mirror and a bunch of memory if you have to. Getting this off of your SQL Server will be a big improvement.
August 7, 2008 at 7:27 am
Thanks everyone for your answers, they're all very helpful. I'd like to comment on Michael's points.
Michael Earl (8/7/2008)
I understand being stuck with a bad application, but since you are still implementing it, now is the time to try really, really hard to cut out the cancer before it spreads.
Unfortunately this is simply not an option and I think most I.T managers would agree with me. Once you've commited to buying a product, invested lot of money, time and effort in it you can't just drop it a few months before going into production, trash the 200,000$ you spent on it and just start all over again. We're not talking about an "off the shelf" package or some general applications like CRM here. This application is very specific to our industry and there isn't many options available on the market. Had I known from the beginning that it was poorly coded I would have recommended against buying it but now it's too late, we'll just have to live with it and try to do everything we can to make it run smoothly.
As was recommended by Paul, I intended on building my own indexes, I also plan on rewriting some of their stored procedures to speed up the slowest ones.
Ok, onto the other items. You can only have one TempDB. It can have multiple file groups, but it only really uses the primary file group for most operations, so I cannot see any use in splitting it into multiple file groups in most cases. Since you have a database that is lots of row-by-row operations, TempDB may not be a big concern to you.
I didn't understand at first why you were saying that I can only have one tempdb as that seemed so obvious but after re-reading my post I realized it was me who didn't put it right. I didn't mean multiple tempdb's, what I meant was multiple files as recommended in this Microsoft document and that I've seen on many other places.
Points 7 and 8 recommend to split the tempdb into multiple files, using one per CPU. The basic concept as I understand it is that SQL Sever fills them proportionally, since a CPU can only perform one task at a time, having 1 tempdb file per CPU maximizes disk bandwith because each thread then uses it's own tempdb file. I'm probably not explaning this properly but that's the basic idea anyway. The one thing I've been trying to find and couldn't however is whether the tempdb transaction log should be split in multiple files as well or not. I am also wondering if the same logic would apply to databases, as I have 8 cpu's (2 quad core) should I split my database and transaction log into 8 files each ?
Only having 5 physical drives kinda sucks. If you are going to have a read/write intensive system, having several controller cards and lots of drives can be of real benefit. Here is the thing on drives - you want a fast write operation (so go for a fast RAID configuration rather than one that gives you more drive space). You also want as many read and write heads as possible and separate your conflicting operations. For example, if you have log files being written to and data files being updated, have the log files on a different set of physical disks so the write heads do not have to move around a lot between operations. Putting TempDB and the log files on the same drive is usually not good. Mirrored drives are usually ok for data files, try to stay away from RAID 5 or RAID 6 because the XOR operations for the parity maps slows things down.
After having another discussion with our tech guys, it turns out that the 5th drive was only temporary and it's been removed before I even had a chance to use it. What I thought was a physical drive was actually a 2nd partition on drive 0. We have 4 drives in the server, drive 0 has two partitions as logical drives C and E, drive 1 has a single partition as drive I, the other two disks are used for mirroring. I believe they're on a RAID 1 but I'll have to verify that. I had the 5th drive put back in yesterday as drive N onto which I moved my tempdb. The way things are now, transaction logs are on E, databases are on I and tempdb is on N so they all have their own physical drive now.
The memory thing - you either don't remember the article correctly, or mis-read it. SQL Server internally cuts it's available memory into some individual pieces. Some of this memory can be used for caching data and some of it can be used for caching execution plans. The sp2 change increased the percentage of memory allocated to caching execution plans because of a somewhat common performance problem happening on large databases in which the memory space for storing the plans would run low and the server spent too many resources trading out memory. You only have control over the overall allocated memory - for this you should leave something for other applications and the OS. Start with dynamic memory allocation allowing all memory and do some testing. Decrease the amount of memory slowly until you see a performance drop. Then try setting the minimum to the maximum to see if the server is happier with a fixed memory allocation. On dedicated SQL 2005 servers without reporting services, SSIS, or SSAS running, I have found dynamic memory allocation to actually be better in most cases, but this really needs some testing for the individual system.
We don't run reporting services nor SSIS right now but as we start building our own reports in a few months there's a good chance we will use reporting services and probably IIS to make reports available on our Intranet. I'm not sure on the best approach now that you've highlighted for me how memory usage works. I guess we're definitely gonna have to bring it up to 64 Gigs (or more) if we want to run everything on the same machine. Hardware is not really an issue for us and we can easily put up another server for IIS, reporting services and SSAS. Licensing it however could prove difficult to get approved by our management ($$$$$). I'll have to look into this as I don't know if I can split SQL services on multiple machines without acquiring licenses for each one. Can I legally put the database engine on one machine and the other services on another machine with the same license ?
You will have to test the Boost priority on your system. If you use MOLAP storage, the SQL Server does not do anything for your Analysis Services queries - it only gets used during processing and then SSAS handles all query requests - even drillthrough requests. Memory management for SSAS is very different and you will need to leave memory for SSAS when you configure your max memory on your SQL Server. You really have to be running a load against both while trying to set the memory settings for the SQL Server. I would highly recommend finding another server for Analysis Services. If the cubes are small, find a workstation with two drives you can mirror and a bunch of memory if you have to. Getting this off of your SQL Server will be a big improvement.
If it turns out that I have to run IIS, Reporting Services and SSAS on the same machine then I guess boosting SQL server priority will not be an option as it would most probably slow everything else down. I think however that with 64 Gigs of RAM (or maybe 128), I could set my cpu and io affinities to use only 6 CPU's for the database engine and keep the other two for the OS an other applications without too much hinder on the database. That of course will have to be tested to be sure.
August 7, 2008 at 7:55 am
eric (8/7/2008)
After having another discussion with our tech guys, it turns out that the 5th drive was only temporary and it's been removed before I even had a chance to use it. What I thought was a physical drive was actually a 2nd partition on drive 0. We have 4 drives in the server, drive 0 has two partitions as logical drives C and E, drive 1 has a single partition as drive I, the other two disks are used for mirroring. I believe they're on a RAID 1 but I'll have to verify that. I had the 5th drive put back in yesterday as drive N onto which I moved my tempdb. The way things are now, transaction logs are on E, databases are on I and tempdb is on N so they all have their own physical drive now.
I think you'll want to move your logs to a different physical drive. If hardware truly is not an issue, you should look into a SAN solution. Your OS should be on a dedicated spindle. Since your C and E drives are both on drive 0, you'll hurt your performance having the logs on E since you'll be sharing that spindle with the OS.
August 7, 2008 at 8:10 am
Sorry, I was really unclear on the TempDB thing. Doing any splitting up of files onto the same physical disks is useless - and often detremental. The benefit to multiple files is the additional read and write heads on the disks. With multiple CPU's, you can often help performance by splitting things up because you end up with a full set of physical resources for individual operations (a CPU, a seat of read and write heads, and a block of memory). It does not matter if it is TempDB or not. You do not have enough drives to do much with this. Your best bet is put the log files on one drive and the data files on the other.
As far as the licensing, you have to license each individual server. So, if you install MSSQL on one server and Analysis Services on another you need two individual licenses. However, you may not need the same edition or number of licenses depending on the purpose of the server. If you split them onto individual servers, don't look at them as the same product, choose what you need for the part of SQL you are using. Mosing SSAS onto it's own server may just be a handful of users and only need standard edition of SSAS - so you may get away with less than $2000 worth of licensing. Scaling up your server is going to have diminishing return and get way more expensive than the software licenses. Scaling out and buying additional licenses is usually cheaper overall and it also give you the option of increasing hardware on a single piece later - if you need it.
August 7, 2008 at 8:52 am
To say the truth, splitting up files on the same disk didn't make much sense to me but since I'm no hardware specialist I thought maybe the IO's from each thread would be "channeled up" or something and that the data would be flushed to disk faster.
That said, after splitting up my tempdb in 4 files (I only have dual core CPU's at this time because the vendor screwed up) I ran a procedure that took about 25 minutes bofor and it still takes just about the same time.
It was definitely worth trying it anyway but as it doesn't seem to achieve anything I'll just put anything back into a single file which makes managing it a lot easier.
That brings me to my next set of questions about files, how do I size them ? I know, that's the scary question that nobody likes to answer :w00t: so I'll make it easy on all of you.
Since my server isn't used for anything else, all the disk space in the machine is at my disposal. I understand that the think I don't want is to have a file that keeps autogrowing so I have to reserve enough space to avoid that. My question is, if I make them too big would that hinder performances ? Say I have 32 gigs of tempdb and only 8 are used, is there any overhead on the SQL Server managing 24 Gigs of unused space ? Obviously that question also applies to databases and transaction logs.
P.S: Thank you to whoever runs SQL Server Central and to all the people answering our newbie questions, it's nice to know there's a place where we can validate our assumptions and know if we're doing things right or not.
August 8, 2008 at 5:48 am
Too big leaves a lot of room for fragmentation on the disk. Try to size it reasonably. I usually take a guess for where to start based on the initial data and the expected volume and try to plan for a regular expansion of the file (monthly usually, sometimes an automated weekly deal) during off hours.
August 8, 2008 at 6:53 am
Assuming the 3rd party code is not encrypted and that you have access to the cursor definitions, I suggest you ensure that each cursor definition has supporting indexes by generating and examining the query plans for each cursor query, and creating or changing indexes as needed. Also, check any queries, procedures, functions or other code within the cursor loop itself for possible index optimizations.
August 8, 2008 at 6:59 am
I would be VERY careful with any structural changes. Even adding an index could violate a licensing/maintenance agreement. It could also cause blocking or slow something else down.
Don't ever give a vendor an excuse to point the finger at you. Leave the database optimization to them and just yell really loud when you see something that is wrong.
August 8, 2008 at 7:01 am
Also, you mention you use 3 mirrored SCSI SATA drives. SCSI and SATA aren't the same thing. SATA is notoriously slow on write operations -- you will get much better performance with SCSI. Striping in addition to mirroring will also improve performance for db writes. Check Performance Monitor for disk write queueing on your disk drives to determine load.
August 8, 2008 at 9:35 am
Michael Earl (8/8/2008)
I would be VERY careful with any structural changes. Even adding an index could violate a licensing/maintenance agreement. It could also cause blocking or slow something else down.Don't ever give a vendor an excuse to point the finger at you. Leave the database optimization to them and just yell really loud when you see something that is wrong.
THIS.
Don't put your own job on the line for some vendor who's skimped on development costs by hiring a bunch of underqualified developers. By making any changes to their code at all, you automatically assume that responsibility.
If you're already having these issues before implementation time, I'd make sure my butt was covered first. That includes changes to indexes.
If you have some legal agreement in writing from the third party that you can make changes or additions to indexes, then I would do it. But only with a written agreement FROM THEM. If this thing turns out to be a total flop, you can bet your bottom dollar that there will be some kind of litigation by your company to try to recover damages and lost revenue from that vendor and you really want to make sure that you are covered.
With that being said, and with a written agreement from them that you can make index changes, and given what you know about SQL Server, you have a couple of options.
1. Get a qualified SQL Server DBA to help you tune the indexes / server config. A lot of DBAs do freelance work, and this situation is very common.
2. Use the database engine tuning advisor. This isn't your best option, but if you're in a pinch and you have no other choices, you gotta do what you gotta do.
There's no magic hidden setting that you've missed that will make all the performance problems go away here. I've got over 15 years of experience working with the SQL Server platform and I've seen this situation so many times throughout my career. And yes, it typically happens with someone who's purchased software designed for a very specific vertical market. For software developers in these vertical markets, the margin is razor-thin, so they skimp wherever possible to try to make it.
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply