Where do I start from ?
-
Following with a user complain about SQL timeout I bumped into one issue with a view. I ran the Object Execution Statistics report and saw that one particular SP, which querying a view containing about 3 tables and 12 views, is coming up with high numbers. I am not a dba specialist and would like to know where to start from ?
Jean-Luc
www.corobori.com -
There's a documented process for submitting performance issues. In the footer of Jeff Moden's forum posts there's a link.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
For starters, are you sure that the query highlighted is place to start?
If it's executed once a month as part of a some process, then I would ignore it.
This report also shows the number of executions, correct? If a proc executes 100000 times, and takes 2 seconds, getting that down to 1 second would be a far greater benefit than tuning the proc that runs once.
If you are attempting to tune the entire instance, that's a different set of steps than attempting to tune a single procedure or query.
For an individual query, I would execute it an capture the actual execution plan. That's probably where I would start.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ - Michael L John wrote:
For starters, are you sure that the query highlighted is place to start?
If it's executed once a month as part of a some process, then I would ignore it.
This report also shows the number of executions, correct? If a proc executes 100000 times, and takes 2 seconds, getting that down to 1 second would be a far greater benefit than tuning the proc that runs once.
If you are attempting to tune the entire instance, that's a different set of steps than attempting to tune a single procedure or query.
For an individual query, I would execute it an capture the actual execution plan. That's probably where I would start.
I have to admit that a rating of more than 90% of the I/O is difficult to ignore.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Corobori wrote:
Following with a user complain about SQL timeout I bumped into one issue with a view. I ran the Object Execution Statistics report and saw that one particular SP, which querying a view containing about 3 tables and 12 views, is coming up with high numbers. I am not a dba specialist and would like to know where to start from ?
Have you determined that view is the actual cause of the timeouts? And, yeah... I agree... whether it is or not, it's a problem that needs to be fixed. Like Steve posted, though, we're going to need a whole lot more information to try to help. See the second link in my signature line below. It may even be the answer to "Where do I start".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:Michael L John wrote:
For starters, are you sure that the query highlighted is place to start?
If it's executed once a month as part of a some process, then I would ignore it.
This report also shows the number of executions, correct? If a proc executes 100000 times, and takes 2 seconds, getting that down to 1 second would be a far greater benefit than tuning the proc that runs once.
If you are attempting to tune the entire instance, that's a different set of steps than attempting to tune a single procedure or query.
For an individual query, I would execute it an capture the actual execution plan. That's probably where I would start.
I have to admit that a rating of more than 90% of the I/O is difficult to ignore.
Agreed. But, I have reports that are as well tuned as I can get them, and run once a month to do billing. They basically hit everything in the whole environment. The execution time is about 30 seconds, but they do show up as the highest consumer of CPU.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
Agreed. You do have to know which battles to pick. We don't actually know how often that query is executed.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply