performance question

Question

performance question

ucbus 92278

Mr or Mrs. 500

Points: 554
More actions
April 15, 2010 at 8:19 am

#223316

Hello:
I am on application development side and NOT a DBA. However I am tasked to improve the performance of our SQL server and I need tips on how to go about. Our application uses a Stored procedure which formats an XML from the data retrieving from some 16 tables. The tables are hierarchial ( Parent-child-grandchild) with a single key controlling the retrieving process. The process works fine, but takes more time (70 sec-80 sec) whenever it has to generate huge xml ( of order > 1.5 MB). I would like to improve if there is anything can be done on the database to improve the performance.
Here are my limitation: The stored procedure is business critical and would not want to modify unless it is needed.
I can modify the application and not sure how it improves the query performance.
My question is:
1. What SQL server tools (FREE ofcourse, if not free please let me know the approx price) that would do a real-time monitoring of the queries while the application is running and suggest best course of action.
2. I am not sure if "reorg/runstats" similar to what is done in DB2, can be done on the database to improve the performance.
3. How to know if indexes are needed, how to go about creating if needed.
Again I am not a DBA, appreicate your help with some details.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

SQL Server probably has all the tools you need already available.

My first suggestion would be to post the table definition, the stored procedure, and some sample data here, and see what we can do to help you with it. Before you post any data, make sure (a) it will work for the stored procedure, and (b) it doesn't contain any "real data" (phone numbers, social security numbers, real addresses, etc.). You might also need to check if it's okay to post code online, since some companies don't allow that.

Do you know how to look at and save an execution plan for a query?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

Look at Books Online a.k.a. BOL in the SQL Server box - that will be your primary source of documentation.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

WayneS SSC Guru Points: 95461 More actions · Answer 3

ucbus 92278 (4/15/2010)
Our application uses a Stored procedure which formats an XML from the data retrieving from some 16 tables. The tables are hierarchial ( Parent-child-grandchild) with a single key controlling the retrieving process. .... The process works fine, but takes more time (70 sec-80 sec) whenever it has to generate huge xml ( of order > 1.5 MB).

How are you building the XML? By building a string? or are you using the FOR XML clause of the SELECT statement? If you're manually building the string, you will be able to drastically speed this up by shifting over to using FOR XML. (Massive sting manipulation on MS it notoriously slow.)

As an example, see Using XML to Enhance the Performance of String Manipulations[/url]

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ucbus 92278 Mr or Mrs. 500 Points: 554 More actions · Answer 4

GSquared:

Thanks

You might also need to check if it's okay to post code online, since some companies don't allow that.

No my company does not allow. Also it is too hard for me to make up mock tables and data to show the example.

Do you know how to look at and save an execution plan for a query?

No. I do not. I do NOT Know how to do it. Can your share more details?

Also I do not have access to the DB server as of yet. But will there be some monitoring tool that makes a client connection like SSMS and will allow monitoring ? are there any tools that show if new indexes are needed or DB reorg or runstats to be done? How one would perform these tasks like reorg/runstats ( apologies for DB2 lingo)

Look at Books Online a.k.a. BOL in the SQL Server box - that will be your primary source of documentation.
_____________________________________
Pablo (Paul) Berzukov

Thanks Pablo, are these availabe on Web?

How are you building the XML? By building a string? or are you using the FOR XML clause of the SELECT statement? If you're manually building the string, you will be able to drastically speed this up by shifting over to using FOR XML. (Massive sting manipulation on MS it notoriously slow.)

Wayne:

I am making extensive use of FOR XML 🙂

Please share your ideas.

WayneS SSC Guru Points: 95461 More actions · Answer 5

ucbus 92278 (4/15/2010)
Look at Books Online a.k.a. BOL in the SQL Server box - that will be your primary source of documentation.
Thanks Pablo, are these availabe on Web?

Yes, at http://msdn.microsoft.com/en-us/library/ms130214(v=SQL.100).aspx

Also, in SSMS, press the F1 key.

How are you building the XML? By building a string? or are you using the FOR XML clause of the SELECT statement? If you're manually building the string, you will be able to drastically speed this up by shifting over to using FOR XML. (Massive sting manipulation on MS it notoriously slow.)
Wayne:
I am making extensive use of FOR XML 🙂

Generating a 1.5mb XML string is nothing when using FOR XML... it should be able to do that in < 1 sec. It might come down to checking the execution plan for this query... the query and/or indexes might need tweaking, and this could be causing the query to take this long.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ucbus 92278 Mr or Mrs. 500 Points: 554 More actions · Answer 6

. It might come down to checking the execution plan for this query... the query and/or indexes might need tweaking, and this could be causing the query to take this long.

Wayne:

Thanks for the link. I will go thru. That is exactly what I am thinking. With limited DBA skills, I am not sure how to check execution plan, indexes etc. Can you throw some pointers?

WayneS SSC Guru Points: 95461 More actions · Answer 7

ucbus 92278 (4/15/2010)
. It might come down to checking the execution plan for this query... the query and/or indexes might need tweaking, and this could be causing the query to take this long.
Wayne:
Thanks for the link. I will go thru. That is exactly what I am thinking. With limited DBA skills, I am not sure how to check execution plan, indexes etc. Can you throw some pointers?

Hmm, second time this has come up today. Check the excellent recommendations at this forum: http://www.sqlservercentral.com/Forums/Topic902942-146-1.aspx

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95461 More actions · Answer 8

ucbus 92278 (4/15/2010)
GSquared:
Thanks
You might also need to check if it's okay to post code online, since some companies don't allow that.
No my company does not allow. Also it is too hard for me to make up mock tables and data to show the example.

It would really be helpful to see the stored procedure... there are a lot of good people here that can help you optimize it.

Without being able to see it, all we can do is provide some guidelines:

1. Ensure columns used in the JOIN and WHERE clauses are indexed.

2. Don't use subqueries in the WHERE clause (where they have to be executed for EVERY row).

3. Don't use functions on fields in the JOIN or WHERE clauses.

4. Ensure that you aren't doing any implicit conversions. (i.e. if you have a bit field which is indexed, and your where clause has <bit field> = 1, this is an implicit conversion.. the bit field will be converted to an integer, and then it can't use the index. Try <bit field> = convert(bit,1).

5. Subqueries in the select column list can usually be rewritten into a derived table that is used in the where clause, with a huge performance gain.

6. Don't use "catch-all" queries. (i.e. where (@expr1 = <some value> and field1 = @expr1) OR (@expr2 = <some value> and field2 = @expr2) OR .... ) In order to ensure that all conditions are evaluated, this essestially forces a table scan on all of your tables... VERY BAD.

This should get you started, but it would be best if you can get permission to post the table structures / code for us to look at.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2