| Author |
 Topic  |
|
|
influent
Constraint Violating Yak Guru
USA
361 Posts |
 Posted - 05/24/2012 : 16:09:48
|
| My company has several situations where employees need to see very-near-real-time data that typically would be considered OLAP data, such as the number of orders a customer has placed this year. In most of these situations we use stored procedures executed against the OLTP database that take 2-15 seconds (which is reasonable but annoying) but use a lot of CPU and result in CXPACKET waits. In some situations I've created tables that hold e.g. "#orders this year" and I've created a job that updates it every 5 minutes or whatever. Is this typically what people do in these situations or is there a better solution? We're using replication to another server to do reporting but replication is so finicky I hate to point production apps to the reporting server. |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
influent
Constraint Violating Yak Guru
USA
361 Posts |
Posted - 05/24/2012 : 17:52:05
|
Replication's been running without issue for several months now but we used to have problems that forced us to reinitialize replication, which takes half an hour. Now the only time we have to reinitialize is after monthly code/schema updates, which is fine. But I just remembered that some of the data comes from a separate database which is not replicated.
If I change MAXDOP to 1 then the waits go away but the query takes 3-4 times longer. I'll check for missing indexes but I've checked several times so I doubt I missed any.
The primary query I'm concerned with is a pretty complex query but the main cause of the slowness is that it's non-sargable. It needs to return data for all customers whose company name or last name or customer number contains the specified search text. |
 |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
influent
Constraint Violating Yak Guru
USA
361 Posts |
Posted - 05/24/2012 : 20:38:24
|
| Are you using transactional replication? I'd LOVE to know how you're modifying the schema of the publisher without having to reinit. Thanks as always Tara. |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
| |
Topic |
|
new tables/jobs to minimize cxpacket waits?
