SQL Development

Tips for Faster SQL Queries and Identifying Issues

Written by Embarcadero USA on . Posted in SQL Development

Finding the top offending statements for a specific workload can be hard if you are not using the best tool in your toolbox. On SQL Server 2012 and going forward you are able to leverage extended events which are included inside of SQL Server Manager Studio (SSMS) to build your first extended event to capture your workload.

Read this article and watch the short video to see how you can use the new Extended Event tools to find your top offenders.

  • Who/When dropped or altered an object in my database?
  • What is the growth pattern of my database?
  • Who/When/How configuration settings were changed?

You may also be interested in Make SQL Queries Run Faster, an on demand webinar by SQL expert John Sterrett. You will learn some T-SQL performance tuning tips from a DBA who has a developer background.

Learn more about Embarcadero® Rapid SQL®, the intelligent IDE for SQL development, and try Rapid SQL for free.


Tips Developers Must Know Reading Query Plans

Written by Tim DelChiaro on . Posted in SQL Development

If you are a developer, you might have heard this from your DBA – “That query plan has some problems”. This blog would help you in understanding those “problems” which DBA might be talking about. We would talk about tips which can help a developer in finding some of these “problems” themselves without getting deeper into the topic.

There are two types of plans SQL Server engine can generate in general. They are known as “Estimated” plan and “Actual” plan. When we ask for estimated plan from SQL Server it would not execute the query. So this mechanism would be useful in situations where we are dealing with long running queries. Estimated query plan represents the calculation done by optimizer. On the other hand, the actual execution plan executes the query and shows the query plan. Generally, actual execution plans are preferred because they have the capability to show execution statistics like number of rows returned by an operator as part of the query plan.

We are going to focus on graphical query plan because they are a good start point for a developer while doing query tuning. There are multiple ways to generate graphical plan for the query.

Try Rapid SQL Now

  Estimated Execution Plan

Estimated graphical execution plan can be seen using SQL Server Management Studio (SSMS):

  1. “Display Estimated Execution Plan” icon in toolbar.
  2. Using Ctrl + L on keyboard.
  3. Right Click in Query windows and using “Display Estimated Execution Plan”.
  4. In Query option in menu bar and using “Display Estimated Execution Plan”.

Actual Execution Plan

Actual graphical execution plan can be generated and viewed in SSMS using below methods:

  1. “Include Actual Execution Plan” icon in toolbar.
  2. Using Ctrl + M on keyboard.
  3. Right Click in Query windows and using “Include Actual Execution Plan”.
  4. In Query option in menu bar and using “Include Actual Execution Plan”.

Once you have generated the query plan, there are various thing to watch out for. We are calling out some of the basic information to lookout for:

  1. Arrow Thickness: Look at thickness of the arrow in the query plan. The thickness of the arrow is relative to amount of rows passed. More thickness means more rows. This gives visual indication of the possible performance issue which we might want to have a look. If we move the mouse pointer over the arrow, it shows number of rows as a tooltip. Below is an typical example to illustrate the same:


  1. Percentage value in each operator: For each icon, which represents an operator, there would be a cost associated. This cost is relative to overall cost. If we sum all percentage values, they would add up to 100%. Generally more cost represents the area where we need to put our focus and see if something can be done to reduce it. In below image, we have highlighted cost of each operator.
    Find It Fix It Webinar


Click to see full-sized image

  1. Estimated vs Actual rows: Every operator’s tool tip in actual execution plan shows estimated number of rows and actual number of rows. Estimate number of rows represents the number of rows which were estimated by optimizer based on statistics gathered from the underlying table or index. If there is huge difference in values, these might indicate that latest statistics were not given to optimizer. This could be because the statistics were not updated with FULL SCAN. Depending on current state of statistics, one possible solution might be to update the statistics of various tables used in query using full scan option.


Click to see full-sized image

  1. Missing Index: Sometimes optimizer can suggest a missing index in a query plan. We can right click on that detail and use “Missing Index Details” and get more details about missing index which optimizer thinks is useful. This should be treated as quick fix to solve a performance issue related to this particular query. Impact of Index has to be evaluated before implementing in production server.


Click to see full-sized image

  1. Warning Symbol: Graphical query plan can also show warning symbol for many situations. One such Warning - Operator used tempdb to spill data during execution.


SNAGHTML89b27d0

Other type of warning can be got for Type conversion in expression (CONVERT_IMPLICIT(<>)). This may affect "CardinalityEstimate" in query plan choice.


As we sign off, these were some of the common operators or things we expect developers to lookout for when they are working with execution plans. Hope this list can give an initial start to a developer and help in grabbing low hanging fruit while doing performance tuning. Performance tuning in itself is an tough and long topic and we have just touched the surface as part of evaluation of execution plans.


About Pinal Dave
Technology Evangelist & Founder of SQL Authority

Pinal Dave works as a Technology Evangelist (Database and BI) with Microsoft India. He has written over 2000 articles on the subject on his blog at http://blog.sqlauthority.com. During his career he has worked both in India and the US, mostly working with SQL Server Technology – right from version 6.5 to its latest form. Pinal has worked on many performance tuning and optimization projects for high transactional systems. He has been a regular speaker at many international events like TechEd, SQL PASS, MSDN, TechNet and countless user groups.


Rapid SQL Code Templates by Dan Hotka

Written by Tim DelChiaro on . Posted in SQL Development

This is part of a series of guest posts by Dan Hotka.

Hi!

This week I want to show how Rapid SQL can improve your coding productivity and technique by using its templates. A template saves me from having to look up the exact syntax.

To use, simply put your cursor on your code where you want a template and use the key strokes CTRL + Space bar. This brings up the template popup window:


From here, we select the code we want using a double-click operation, in this example, I’d like a CASE statement and it dumps the code into your PL/SQL!

Hide image

Now I can complete the code to my satisfaction.

To add your own templates, this is very similar to the blog I did on using Autoreplace (remember…to create your own coding shortcuts).

Go to Tools à Code Workbench then to the Code Templates tab.

Hide image

Hide image

Notice that you can export and import your settings between releases of Rapid SQL. This is convenient to preserve your code templates.

Hide image

Notice here that we have the Embarcadero multi-database support showing up. IF you are just using SQL Server or just Oracle…you can easily delete the non-pertinent database options.

You can also add your own templates.

I’ve worked with a number of programming languages such as COBOL and C over the years. Most other languages have copy libraries, or chunks of code that is copied in at compile time. PL/SQL does not have this feature.

So…any chunks of code you wish to share across your PL/SQL routines, such as a standardized exception clause…simply add it here and include it with just a couple of key strokes and a mouse action!

The big difference between Auto Replace and Code Templates is Auto Replace is a one-line code snippet and is signaled from the space bar, where Code Templates can contain many lines of code and is signaled with the ctrl+space bar.

CTRL + Space Bar by itself brings up the entire list of options. You select one using a double click operation. As you get used to the short cut name, you can enter this and hit ctrl + space bar and this single code template will instantly load into your code.

What would I do? I’d customize these templates to my coding style and typical options I would have in my code. I’d add my own templates of code I commonly use. I would use templates for what would be copy libraries in other coding languages.

I hope this technique helps you in your day-to-day use of Rapid SQL.

Dan Hotka

Oracle ACE Director

Instructor/Author/CEO

Click here to earn more about Rapid SQL and try it free


Dan Hotka is a training Specialist and an Oracle ACE Director who has over 36 years in the computer industry, and over 30 years of experience with Oracle products. His experience with the Oracle RDBMS dates back to the Oracle V4.0 days. Dan enjoys sharing his knowledge of the Oracle RDBMS. Dan is well-published with12 Oracle books and well over 200 published articles. He is frequently published in Oracle trade journals, regularly blogs, and speaks at Oracle conferences and user groups around the world. 


Executing Explain Plans Using Rapid SQL by Dan Hotka

Written by Tim DelChiaro on . Posted in SQL Development

This is a guest post from Dan Hotka, Oracle ACE Director. Learn more at his webinar Explain Plan Tips and Techniques.

Hi!

My name is Dan Hotka. I am an Oracle ACE Director. One of my many charters with this group is to help spread the good word and technical knowledge about the Oracle RDBMS. I am well published with 12 (going on 14 books) and literally hundreds of articles. I regularly blog as I will here. Maybe we have met at a trade show or user group. I regularly speak at these around the world.

I am hoping to share with you both Oracle technical knowledge and how this knowledge is reflected in Embarcadero tools.

I’ve downloaded the big 3: Rapid SQL, DB Artisan, and DB Power Studio from embarcadero.com. This blog will cover my initial thoughts and some explain plan tips utilizing Rapid SQL.

I’ve just installed Rapid SQL version 8.6.1.

I’ll show you a couple of explain plan tips in and around Rapid SQL.

I like the tool. Of course this is the perfect tool if you have various different database vendors because this tool supports like a dozen different databases. One interface to learn for all the databases! The tips I show will be for the Oracle RDBMS. The tips around the Embarcadero tools should work for you no matter which data source you are connected to.

When displaying explain plans, I like to see the explain plan and the SQL at the same time.

This is easily accomplished.

First…load your SQL into the ISQL editor window (using the Open button) then click the Explain Plan button (see red circle), the button will remain active.


Execute the SQL and the Query Plan tab will appear and populate.


Click to see full-sized image

Hover the mouse over any of these nodes for additional and useful information about that step of the Explain Plan!

Rapid SQL first shows a graphical explain plan. I’m from the old world of tuning…I like the text version so right click in this explain plan tab and select ‘View as Text’.

I like to see the query and explain plan at the same time:

Hide image

This is easy to do. Notice the ISQL window tabs at the bottom of the tool. First, we have to tell Rapid SQL to create the Query Plan in its own window.


Click the Options button (left red circle) and then select ‘Unattached’ for the Result Window…this will create a 2nd tab along the bottom of Rapid SQL when the SQL is executed.

Simply drag the Query Plan tab up slowly, you will see a box appear where the tab will be relocated…

Watch for my webinars and the replay that Embarcadero will host on August 20. I will cover this technique as well as various aspects of the Oracle Explain plan process.

Dan Hotka

Oracle ACE Director

Instructor/Author/CEO


Registration

Written by Yorai A1422 on . Posted in SQL Development

Why join Planet Embarcadero?

Register Now

Already a Member?

Member Sign-In


Check out more tips and tricks in this development video: