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 |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2012-02-28 : 12:53:14
|
| There's a process running in my Great Plains database that occasionally times out and I'm trying to speed it up. The process is kicked off by a lengthy stored procedure with around 50 parameters. When I view the execution plan I just get the high level percentages, nothing useful. I've tried declaring and setting the params as variables and getting the execution plan for the contents of the sproc but it does so many different things and the only thing that takes a large percentage actually only takes a second to run out of several minutes. Is there a way I can run the sproc and capture which queries or tables were used for the longest? |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-02-28 : 13:09:59
|
| Query plan probably shows more than you think. But SET STATISTICS IO ON on and run it. |
 |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2012-02-28 : 14:31:31
|
| Good idea. With the high number of tables that are being used and the process taking 3.5 minutes, I expected some physical reads, but there are none at all. There are tons of logical reads, depending on the table, and two tables with high scan counts. Is it likely that the tables with high scan counts are lacking indexes or otherwise responsible for most of the time taken by the sproc? |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-02-28 : 14:53:55
|
| yes.In general you want to elimate scans.However you mentioned that the sp has 50 parameters. This is generally a sign of a sp that is trying to do too much.Why not post the sp....Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2012-02-28 : 15:10:22
|
| Well, Microsoft wrote it and it really does need to do a lot. I'm certain posting it wouldn't help because it mostly just calls other sprocs, some of which call other sprocs, etc., not to mention that the schema has thousands of tables. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-02-28 : 16:25:50
|
| Well, look for the ones with high number of reads and scan counts and start figuring out how to make the number lower (optimizing the proc, creating/rebuilding indexes etc).Generally speaking, Performance Tuning is the art of reducing reads. |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2012-02-28 : 20:11:21
|
| Thanks. A lot of the tables are heaps, and converting a couple to clustered helped speed things up, but once I converted a third one the view that uses these tables stopped using parallelism. Does that make any sense? SQL won't even let me force parallelism on the view now, much less use it by default. I updated stats just in case but that didn't help. |
|
|
|
|
|
|
|
|
nested query plan analysis?