Question Re: MS SQL DB: SQL statement /index tuning in Technology Q&A

MS SQL DB: SQL statement /index tuning

waelkensd — Thu, 06 Feb 2014 11:28:49 GMT

Dear community,

I've been dealing with Oracle performance issues lately and was able to tackle them with the use of the RSORASTT tool (described in note http://service.sap.com/sap/support/notes/1299493)

I was looking for some guidelines (blog, or document ) on how to interprete sql statements on ms SQL DB's and troubleshoot performance issues.

In orde to do this on Oracle you can check the buffer gets for example to see whether there are any performance issues on the specific query. Is there anything in ms sql as well which is an instant indicator a query is not performing as it should?

Thanks in advance for your suggestions

Dieter

Re: MS SQL DB: SQL statement /index tuning

S_Sriram — Thu, 06 Feb 2014 12:59:05 GMT

Hi Dieter

In SAP system you can use T-code ST12(Single Transaction Analysis) trace and then check the ABAP & SQL Statements for index tuning, Kindly refer the below link about the ST12 & ST05

http://scn.sap.com/docs/DOC-44143

and also you can find tune your Ms SQL DB for SAP system refer the link

http://scn.sap.com/community/sqlserver/blog/2012/09/28/spring-clean-and-tune-your-sql-server-sap-database

Regards

Sriram

Re: MS SQL DB: SQL statement /index tuning

former_member211576 — Mon, 10 Feb 2014 08:21:38 GMT

Hi Dieter,

For overall SQL server queries performance, you can check DBACockpit -> Performance -> SQL Statements since last server cache reset. You can sort by total time, if a specific query consumes more than 10% of total time(with high number of SQL calls and long executions time), you can double click to see the parameters it used, then press Explain Tree to check execution plan.

For query performance issue on a specific time frame, you can use DBACockpit -> Performance -> History -> SQL Statement History(this is only supported after NW EHP2). You can check queries with high physical reads or high CPU ... and fine tune these SQL statements.

For more information, check Running SAP on SQL Server Blog( Running SAP Applications on SQL Server - Site Home - MSDN Blogs )

http://blogs.msdn.com/b/saponsqlserver/archive/2007/06/05/sap-dbacockpit-and-some-related-sql-scripts-part-1.aspx

http://blogs.msdn.com/b/saponsqlserver/archive/2007/06/12/sap-dbacockpit-and-some-related-sql-scripts-part-2.aspx

SAP DBACockpit and some related SQL Scripts (Part 3) - Running SAP Applications on SQL Server - Site Home - MSDN Blogs

Re: MS SQL DB: SQL statement /index tuning

former_member211576 — Mon, 10 Feb 2014 09:11:40 GMT

Hi Dieter,

I solve all SAP on SQL query performance by using DBACockpit only. However, if you need more in deep knowledge, you can also check these links.

http://blogs.msdn.com/b/saponsqlserver/archive/2009/02/09/sql-execution-plans-part-1-sql-statement-execution-on-an-sap-system.aspx

http://blogs.msdn.com/b/saponsqlserver/archive/2009/02/22/sql-execution-plans-part-2-sql-compilation.aspx

https://blogs.msdn.com/b/saponsqlserver/archive/2009/05/10/sql-execution-plans-part-3-how-to-get-the-plan.aspx

Troubleshooting Performance Problems in SQL Server 2008

Re: MS SQL DB: SQL statement /index tuning

waelkensd — Tue, 18 Feb 2014 11:46:57 GMT

Hi Sriram,,

thanks, I already used those toolds, but they don't go deep enough to my liking. I guess I'm a bit too demanding 😉

Dennis,

thanks for the links to msdn and the info about dbacockpit. I was looking for some info like that. Thanks for this.

Cheers,

Dieter

Message was edited by: Dieter Waelkens