August 8, 2008 at 12:18 pm
Wow, lots of answer for me today......and lots to reply to ;), this is good, I'm learning new stuff :D.
Let's start with Michael: About disk fragmentation, the disks where I keep my data and my tempdb are both both dedicated to SQL Server and only contain those files. I thought that once they're created they would be contiguous and that there would be no fragmentation as long as none of the files grows or new files are created. If I'm mistaking here then I will have to analyze space usage for a while when we go live with this and adjust accordingly.
I do agree with you that "tampering" with their procedures and data structures could be an issue down the line and I don't plan on making any changes without getting their approval on this. The thing anyway is that they built a developing environment which stores metadata in some tables where everything is defined. The tables, indexes, procedures, functions, etc are all created by their engine. It wouldn't be wise for me to add indexes directly into the database since they would be lost if they made any change to the structure from within their engine. Their code isn't encrypted so at least I can analyze it to come up with indexes that will match the fields hierarchy on which most of our queries are done and ask them to add it.
Bret: About SCSI SATA, I was referring to SAS drives which if I understand properly are like SATA drives but using SCSI commands. I don't know however if these drives are faster than SATA drives.
Jeremy: Thanks for joining the discussion. As I said above in replying to Michael, I fully understand the implications of fooling around with the data structure of a 3rd party application and I do not intend to do anything "behind their back". I'll try to get them to work with me on this and try to optimize the indexes to suit our needs.
August 8, 2008 at 1:59 pm
Obviously, if the vendor can improve their stuff themselves, then that's the way to go.
Just as a side note FYI, there is legal precedence existing that excludes customer-added indexes from the legal definition of "source code" and thus protected assets under licensing law. In one case where a customer's added index fixed a vendor's unresolved and business-critical performance issue for which the vendor had charged a seven-figure amount to (unsuccessfully) correct (and for which it was being sued), it was successfully argued that they were equivalent to upgrading hardware: improvements to performance that did not affect the "fit, form and function" of the software. Since the vendor could demonstrate no change to "fit, form, and function," the change was judged to be separate from "the product" -- the same as if the client had upgraded their hardware, as the judge pointed out -- and thus separate from the licensing agreement.
Also obviously, index additions should be treated like any other change and appropriately change managed so that you can roll it back temporarily or permanently when and during any upgrades, etc. Indexes are usually relatively easy to drop and recreate, if such upgrade/update events can be planned.
Plus, you can let the vendor know about what you found, and they'll probably add it to their next release anyway and be thankful for the free consulting work they got out of you.
August 8, 2008 at 4:13 pm
bret.lowery (8/8/2008)
Just as a side note FYI, there is legal precedence existing that excludes customer-added indexes from the legal definition of "source code" and thus protected assets under licensing law. In one case where a customer's added index fixed a vendor's unresolved and business-critical performance issue for which the vendor had charged a seven-figure amount to (unsuccessfully) correct (and for which it was being sued), it was successfully argued that they were equivalent to upgrading hardware: improvements to performance that did not affect the "fit, form and function" of the software. Since the vendor could demonstrate no change to "fit, form, and function," the change was judged to be separate from "the product" -- the same as if the client had upgraded their hardware, as the judge pointed out -- and thus separate from the licensing agreement.
Bret probably meant to add that he is not a lawyer and that he is not giving anyone reading this legal advice and that your warranty might differ from this and that anyone reading this should get the advise of their own legal counsel before engaging in anything that might be contractually risky.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 9, 2008 at 9:04 am
Another thing to keep in mind is that not all cursors are bad- it's possible that they are the appropriate tools to use for the task at hand.
August 9, 2008 at 9:23 am
mstrong86 (8/9/2008)
Another thing to keep in mind is that not all cursors are bad- it's possible that they are the appropriate tools to use for the task at hand.
No, they're pretty much all bad. They usually come about because the developer cannot be bothered to figure out how to do it properly, with set-based code.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 9, 2008 at 10:49 am
There's a lot of stuff out there recommending multiple tempdb files. Her are just a few:
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/17/948.aspx
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1276989,00.html
http://www.sqlservercentral.com/Forums/Topic515052-146-1.aspx
http://msdn.microsoft.com/en-us/library/ms175527.aspx
An alternative view is here:
http://www.sqljunkies.com/WebLog/odds_and_ends/comments/21985.aspx
The best disk set up would be something like:
OS & SQL Installation RAID 1
SQL Log files RAID 1 + 0 (not RAID 5 as it has poor write performance)
tempdb RAID 1 + 0 (not RAID 5 as it has poor write performance)
SQL Data files RAID 1 + 0 (RAID 5 would be OK for data of your application mostly reads data and does few writes) (leave room for growth + a restore of the largest DB + 20% free space)
You also need to think about where to put your backups - preferably not on same spindles as logs, data or tempdb - could be on a network share. Remember, SQL Server does not delete a backup until the next has completed, so you need space for n + 1 backups where n is the number of days you decide to keep backups. Again, allow for growth.
Therefore at least 4 - 5 separate sets of physical spindles if you can manage it. SANs are great IF THEY ARE SET UP PROPERLY! Many SAN engineers think they can create 1 big RAID 5 array out of a few large disks and then carve it up - this will not perform well with a database server. Best performance = lots of smaller disks & separate sets of spindle for database objects.
Try to size data and log files so they don't need to auto grow for at least 12 months. Review this every few months - grow them manually rather than relying on autogrow.
Remember to defrag your disks regularly (weekly?) and run chkdsk from time to time. Also keep your indexes and stats as up to date as possible.
Hope this helps.
August 10, 2008 at 9:25 am
CAS - Crappy Application Syndrome - I like it a lot, Paul!
Here's what I would do...
... NOTHING...
At least nothing overt for the time being. You've already informed management that it's CAS/RBAR on steroids... you've already informed management that it's full of worst practices... you've already informed management that it's not scalable and that it's going to slow way down under even minor loads. Management has made the decision to send good money after bad. They've made a huge mistake and you have to give them the opportunity to fail or they'll continue to make the very same mistake in the future.
You know where the system is going to fall apart... start wrtiting code to fix it. Then, let that CAS system crash and burn. When managment finally asks what to do, tell them you can fix it. Then, fix it with your code. Make sure that you and your team brag about fixing it and how the app would have worked correctly if it had been built inhouse.
I think it's a shame that some companies (management) know the cost of everything and the value of nothing especially as it relates to CAS and outsourcing. It's time to teach them the error of their ways by giving them the opportunity to fail and then feeding them porkchops every single day... with a slingshot, of course!:)
Is that method "professional"? Hell no... but, remember... you tried the professional route and they still haven't listened... time to get their attention and let them fail.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 11, 2008 at 5:32 am
I think you have gotten some great advice on this post. One area I haven't seen covered is what kind of feedback are you giving to this software company?
Having recently been hired by a similar (or , I hope not, the same?) software provider to speed things up, I have discovered cursors are everywhere. Developers have been hired for their front end coding skills and not their expertise in SQL Server. As this company prides itself on building a useful and flexible product, they really do want to receive feedback from clients.
Be realistic about turnaround time and pick your battles, but if you tell this company your top concerns and ask them what they are doing to address it, it might be the start of a great relationship.
August 11, 2008 at 6:38 am
Andrew Whettam (8/9/2008)
OS & SQL Installation RAID 1SQL Log files RAID 1 + 0 (not RAID 5 as it has poor write performance)
tempdb RAID 1 + 0 (not RAID 5 as it has poor write performance)
The other thing about RAID5 as opposed to RAID10 for a database server is that RAID5 is not only less robust then RAID10 - basically, if a second disc dies while your R5 config is recovering from a failure - you're dead in the water, and better hope your DR process is slick and effective; but it's also several orders of magnitude slower while recovering than R10. Think 20% of your normal performance if you're lucky. Been in the situation where the dB server for an ERP system on R5 had a run of failures (and disks often fail in batches, quite close together) and the poor bloody network munky was working like trojan until the wee small hours bunging in new disks while hoping the next failure wouldn't happen until the system had recovered. Also - on an 80 user system, we were struggling to get a dozen users onto the system while this was going on with response times in periods usually only used by geologists.
Nice overview of the whys and wherefores in Art Kagel's seminal quasi-polemic at
http://www.miracleas.com/BAARF/RAID5_versus_RAID10.txt
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
August 12, 2008 at 8:18 am
For all DBA's here who have the ears of any influential people at Microsoft, for the sake of all that is good and pure in this world, have them deprecate cursors in all future versions of SQL Server.
I have a programming background but I have slowly gotten into the set-based mindframe of writing efficient SQL scripts. I mean:
insert into target_database(dbname, dbid)
select name, db_id(name) from sys.databases
is much more elegant than:
declare @dbname sysname
create table #dblist
(
dbname sysname
)
insert into #dblist(dbname)
select name from sys.databases
while (select count(*) from #dblist) > 0
begin
select top 1 @dbname = dbname from #dblist
delete from #dblist where dbname = @dbname
insert into target_database
select @dbname, dbid(@dbname)
end
Granted this is an overly simplified example.
If I have to do something in a loop, I don't bother with cursors but use while blocks similar to what I used in the second example. If the table is massive, I use a counter instead of doing a select count(*). And even when using a loop, I preload temp tables with as much data as necessary to minimize the time expended. (I've even started playing with tally tables [/url] to further reduce using loops).
After my little diatribe, has anyone here ever come across a situation, when designing the databases or queries from scratch, that they HAD to use cursors?
Thanks.
Gaby Abed
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
August 12, 2008 at 1:19 pm
Once in v7 where I was doing some rather complex statistical work and the cursor was faster. Other than that quick and dirty DBA type rubbish looping through stuff from systables
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
August 12, 2008 at 1:42 pm
Simply put, there's too much legacy code and legacy mindfame out there. Marketing disaster. I'd prefer some optimizer enhancements that detect cursor calls and decide on a set-based query plan.
August 12, 2008 at 6:33 pm
andrew gothard (8/12/2008)
Once in v7 where I was doing some rather complex statistical work and the cursor was faster. Other than that quick and dirty DBA type rubbish looping through stuff from systables
Did any of that work involve "triangular joins"? Those aren't set based and a cursor will frequently be faster... sometimes, like in a simple running total example on a million rows, thousands of times faster.
http://www.sqlservercentral.com/articles/T-SQL/61539/
But, there's even a way around that (and many similar problems not listed) without an explicit cursor or While loop OR UDF using a single set-based update to solve a procedureal problem...
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
--Jeff Moden
Change is inevitable... Change for the better is not.
August 13, 2008 at 5:07 am
Jeremy Brown (8/12/2008)
Simply put, there's too much legacy code and legacy mindfame out there. Marketing disaster. I'd prefer some optimizer enhancements that detect cursor calls and decide on a set-based query plan.
Technically, that's what the engine does. The individual cursor loops are translated into individual set based operations - that's why they're so inefficient - you get a shedload of set based operations that are not optimised for the actions you're after rather than one which is.
Much easier to just shoot anyone who uses them
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
August 13, 2008 at 5:10 am
But, there's even a way around that (and many similar problems not listed) without an explicit cursor or While loop OR UDF using a single set-based update to solve a procedureal problem...
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
Oh - I'd use a UDF now, but they didn't have them in v7, hence the cursor for that one occasion for that particular problem
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply