Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chris_wood99
Yak Posting Veteran
70 Posts |
 Posted - 2012-03-06 : 11:17:53
|
So ! I inherited Blackbaud CRM Performance. Has anyone come across this before ???I did the obvious and checked the hardware (recently upgraded) 64 bit windows, 64gb RAM, 12 processors, all files speperated correctly and placed on dedicated server on with its own 10 array partitions.before I started to baseline the server and attempt to capture a few pieces of evil code I start to browse the tables, it froze EM. so I went into information_Schema tables to look. This is where alarm bells start ringing. some figures67606 tables 6850 views 119723 stored proceudres and functions!13861[%Export%] tables in total 64GB4572 [%Audit tables] – (contains over 129M rows of data – 108GB) 118142 stored proceduresjudging from the horrendous names. e.g. [EXPORT_3c03a6fc_aa27_472e_a3cd_3fde8abbee4b]didn't take a genius to work out these are system generated, but also it seems to be system generating stored procs and functions. To cap it all off their are system generated audit tables by default, that are inserted into by triggers on normal and system generated tables!The application also lets users build their own queries from a expanding tree that can link tables back to itself, which it stores in static query tables!, i think these populate the export tables, but their seems to be no archiving strategy whatsoever. Among other things guids are used througout as ID fields  The tables are also heavily indexed. At last count there was 700 indexes that are not on unique keys, primary keys or clustered indexes not used since last reboot (3 months ago) obviously being 3rd party app, i cant touch these !Im writing a document for management with all facts and figures in, but im trying to list all the problems with this design.. so far i've got...no reuse of query plans.indexes that are far wider than they should be.procedure cache must be bloated massively by unused stor procs.seen queries with 30 joins!So my question is ? what is the biggest drian on resources, where should I try to concentrate my efforts... i've start byt asking for a script to stop auditing and truncate all the tables as this seems is not embedded into the very core of the app like the guids. any suggestions or points I should be making ?? not really come accross an app this bad before, dont know what to say if im asked about the performance tuning! |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
Blackbaud Application architecture