August 18, 2009 at 2:42 am
Hi Experts,
I have a database conencted to a banking application which is a very critical one. I am a newbie in performance tuning and i need expert adice.
How can i track a particular query causing the performance issue?
TIA
August 18, 2009 at 2:51 am
SQLProfiler is the tool to use
try this
http://pentonizer.com/sql-server/introduction-to-sql-profiler/
If you are running over an extended period server-side traces are more efficent
August 18, 2009 at 3:13 am
Yes..use profiler. Probably you can collect the data for week's time. Save the files seperately for each day. Then you can create temporary table with the columns you had in the profiler and import all the data into temp table.
Once you have the details in temp table you can anlayse it the way you want.
There's an article on this topic. Please search for that.
August 18, 2009 at 3:39 am
Just as a side note to the opinions above, you should be aware, that Profiler can be quite resource intensive. If this banking application is stable and you set profiler up correctly, you shouldn't have any issues. Just make sure you only set profiler up to look for what is needed.
Here is another link, that I have found very useful. Check out Finding the Causes of Poor Performance in SQL parts 1 and 2, these really helped me a lot.
http://www.simple-talk.com/sql/performance/
----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
August 18, 2009 at 3:56 am
Use a server-side trace rather than profiler, it's a lot less intensive. See the two articles that William mentioned (disclaimer, I wrote them)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 18, 2009 at 11:42 pm
Thanks All 🙂
Gila you are genius in SQL server...Those articles are excellent
August 19, 2009 at 6:31 am
In addition to the server side trace, you can also, in real time, query the information about procedures and their behavior from dynamic management views such sys.dm_exec_query_stats.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 20, 2009 at 11:36 am
It continually amazes me how often in the SQL Server world that a complete newbie gets thrown into the deep end of the pool and made responsible for mission-critical stuff. Does this happen in the Oracle/DB2/Mainframe world as well? I bet not. This routine occurrence is great for my business as a consultant but I sure feel sorry for the workerbee and the companies that do this to them!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply