January 21, 2015 at 7:53 pm
Wayne West (1/21/2015)
I hate to ask this, but is your canned system running an application server? At a prior job we had one that required no stored procedures in the database. The system started slowing down as DB file size grew and the numbers on the server showed it was not working hard. The solution was a faster server, which really ticked me off.The vendor wouldn't admit that their model of using an application server and forcing RBAR over millions of rows was a bad thing.
There's a reason why that vendor is not mentioned on my resume.
Let me guess. No clustered indexes and no PK's either? And their reasoning is "portability"?
--Jeff Moden
January 21, 2015 at 7:56 pm
burfos (1/21/2015)
Yes it is an application with separate application servers and it is my least liked system at this site for many many reasons reasons some of which are...- 9448 tables, 12590 views, 13754 indexes, 23 stored procs :/
- The developers have a very bad habit of building views upon views upon views and then query said view for one column making the underlying query unnecessarily big. I have expressed my recommendations to query the tables directly where possible
- Over kill on indexes in some tables, half the datafile size is tables, the other half is indexes
- would not surprise me if a lot of operations are RBAR
- The developers are not the best at sql development - I have to explain a lot to them about why certain things they do are bad. Included in this is having a lock on a table to get a unique incrementing ID, do all the processing for a certain function, before unlocking that table so another process can do the same thing. This means a lot of blocking... albeit in short time frames but still not good. I believe it is because their code must rely on the ids to be in incremental fashion and cannot have one id missing in the order... if that makes sense. Apparently they cannot use auto increment id columns?!
- The underlying vendor app requires sql password to be no more than 8 characters... wtf
- The developers "resolution" to issues with the application process on the app server maxing the cpu is to reboot the server... no investigation...
- Developers say the require 13 environments of this application for their development. I beg to differ.
Y-o-w-c-h. And it's just you "against" all of them?
--Jeff Moden
January 21, 2015 at 8:18 pm
Jeff Moden (1/21/2015)
Y-o-w-c-h. And it's just you "against" all of them?
Oh there are about 8524 clustered indexes but 43 primary keys
Myself and another junior/intermediate DBA vs 8 developers for this db and associated warehouse db. Supporting 90+ instances around 1000 user databases. The one in question though is one of their critical ones.
Did I mention fortnightly release cycles for the db code... mmmm good baseline.
I think the main problem is a poorly designed 3rd party application that is designed to be customizable for a customer. These developers do the customizing putting their code over the top but I don't think they are the strongest with sql development and best practices (hence things like the lack of stored procs). Probably rightly so, they say the vendor coded objects they cannot change/make better as it would void support but there is a lot they could do better - not that I am saying I know everything there is to know about the right way to do things though, I am sure there are things that I do that could be done better. Saying that, the customer isnt shouting about performance although I think some of the end users would have a different opinion.
January 22, 2015 at 8:43 am
silly question but I have to ask... are you running Update Stats on a regular basis?
SAN issues are the most difficult to pin down. It could be the BIOS, firmware or anything in between SQL Server and the storage.
January 22, 2015 at 8:49 am
May want to run sp_blitzindex from Ozar to get a handle on what your indexes are really like, in some cases unused indexes are as much a problem as missing, or duplicate indexes.
January 22, 2015 at 2:53 pm
Jeff Moden (1/21/2015)
Wayne West (1/21/2015)
I hate to ask this, but is your canned system running an application server? At a prior job we had one that required no stored procedures in the database. The system started slowing down as DB file size grew and the numbers on the server showed it was not working hard. The solution was a faster server, which really ticked me off.The vendor wouldn't admit that their model of using an application server and forcing RBAR over millions of rows was a bad thing.
There's a reason why that vendor is not mentioned on my resume.
Let me guess. No clustered indexes and no PK's either? And their reasoning is "portability"?
Portability was the name of their scamgame. Their code base would theoretically run against MS, Oracle, and Informix, so it went lowest common denominator for their DML. For example, their memo fields were an unlimited number of 60 character records that had a field that tied it to a table, then a record number, then a sequence number to get them in the right order. I had to write a UDF to make them display properly for reports. They would only grudgingly give us schema info, so creating reports was a special joy.
I once almost gave the ERP manager an apoplectic fit by suggesting that we bring up a test server, start a utility billing run, then pull the plug on the SQL box to see if it could recover cleanly. Apparently the vendor was not using the transaction log properly (something that I'd been telling them for 3 years), contrary to what they said they were doing. And this for a 1.5+ TB ERP system for a city > 100,000 residents. They bought the system before they had a relational database expert on-staff, thus they ended up with a POS ERP system.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
January 22, 2015 at 5:48 pm
update stats weekly but I am going to dig in to this a bit deeper to see it it can be done better.
Yes have had a look at unused and duplicate indexes. Provided a report to the developers quite some time ago with those along with the most expensive queries etc. They fixed up some of the expensive queries but the unused indexes apparently are mostly vendor ones and could not be touched due to not voiding support.
I think this is something I will just have to keep looking at to find areas that can be improved within the limitations. The customer isnt complaining, just the san guy querying the response times as it makes his monthly reporting look bad.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply