feoloap astra

feoloap astra

1620145479

SQL Performance Tuning: 7 Practical Tips for Developers

Being able to execute a SQL performance tuning is a vital skill for software teams that rely on relational databases. Vital isn’t the only adjective that we can apply to it, though. Rare also comes to mind, unfortunately.

Many software professionals think that they can just leave all the RDBMS settings as they came by default. They’re wrong. Often, the default settings your RDBMS comes configured with are far from being the optimal ones. Not optimizing such settings result in performance issues that could’ve been easily avoided.

Some programmers, on the other hand, believe that even though SQL performance tuning is important, only DBAs should do it. They’re wrong as well.

First of all, not all companies will even have a person with the official title “DBA.” It depends on the size of the company, more than anything.

But even if you have a dedicated DBA on the team, that doesn’t mean you should overwhelm them with tasks that could’ve been performed by the developers themselves. If a developer can diagnose and fix a slow query, then there’s no reason why they shouldn’t do it. The relevant word here, though, is can—most of the time, they can’t.

How do we fix this problem? Simple: we equip developers with the knowledge they need to find slow SQL queries and do performance tuning in SQL Server. In this post, we’ll give you seven tips to do just that.

Tip: Find application errors and performance problems instantly with Stackify Retrace
Troubleshooting and optimizing your code is easy with integrated errors, logs and code level performance insights.
Try today for free
What Is SQL Performance Tuning?
Before we show you our list of tips you can use to do SQL performance tuning on your software organization, I figured we should define SQL performance tuning.

So what is SQL performance tuning? I bet you already have an idea, even if it’s a vague one.

In a nutshell, SQL performance tuning consists of making queries of a relation database run as fast as possible.

As you’ll see in this post, SQL performance tuning is not a single tool or technique. Rather, it’s a set of practices that makes uses of a wide array of techniques, tools, and processes.

7 Ways to Find Slow SQL Queries
Without further ado, here are seven ways to find slow SQL queries in SQL Server.

  1. Generate an Actual Execution Plan
    In order to diagnose slow queries, it’s crucial to be able to generate graphical execution plans, which you can do by using SQL Server Management Studio. Actual execution plans are generated after the queries run. But how do you go about generating an execution plan?

Begin by clicking on “Database Engine Query”, on the SQL Server Management Studio toolbar.

After that, enter the query and click “Include Actual Execution Plan” on the Query menu.

Finally, it’s time to run your query. You do that by clicking on the “Execute” toolbar button or pressing F5. Then, SQL Server Management Studio will display the execution plan in the results pane, under the “Execution Pane” tab.

  1. Monitor Resource Usage
    Resource usage is an essential factor when it comes to a SQL database performance. Since you can’t improve what you don’t measure, you definitely should monitor resource usage.

So how can you do it?

If you’re using Windows, use the System Monitor tool to measure the performance of SQL Server. It enables you to view SQL Server objects, performance counters, and the behavior of other objects.

Using System Monitor allows you to monitor Windows and SQL Server counters simultaneously, so you can verify if there’s any correlation between the performance of the two.

  1. Use the Database Engine Tuning Advisor
    Another important technique for SQL performance tuning is to analyze the performance of Transact-SQL statements that are run against the database you intend to tune.

You can use the Database Engine Tuning Advisor to analyze the performance implications.

But the tool goes beyond that: it also recommends actions you should take based on its analysis. For instance, it might advise you to create or remove indexes.

  1. Find Slow Queries With SQL DMVs
    One of the great features of SQL Server is all of the dynamic management views (DMVs) that are built into it. There are dozens of them and they can provide a wealth of information about a wide range of topics.

There are several DMVs that provide data about query stats, execution plans, recent queries and much more. These can be used together to provide some amazing insights.

For example, the query below can be used to find the queries that use the most reads, writes, worker time (CPU), etc.

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC – logical reads
– ORDER BY qs.total_logical_writes DESC – logical writes
– ORDER BY qs.total_worker_time DESC – CPU time
The result of the query will look something like this below. The image below is from a marketing app I made. You can see that one particular query (the top one) takes up all the resources.

By looking at this, I can copy that SQL query and see if there is some way to improve it, add an index, etc.

Find slow SQL queries with DMVs
Pros: Always available basic rollup statistics.
Cons: Doesn’t tell you what is calling the queries. Can’t visualize when the queries are being called over time.

  1. Query Reporting via APM Solutions
    One of the great features of application performance management (APM) tools is the ability to track SQL queries. For example, Retrace tracks SQL queries across multiple database providers, including SQL Server. Retrace tells you how many times a query was executed, how long it takes on average, and what transactions called it.

This is valuable information for SQL performance tuning. APM solutions collect this data by doing lightweight performance profiling against your application code at runtime.

Below is a screenshot from Retrace’s application dashboard showing which SQL queries take the longest for a particular application.

SQL Performance Tuning With Retrace Top Queries
Retrace Top SQL Queries
Retrace collects performance statistics about every SQL query being executed. You can search for specific queries to find potential problems.

Retrace View All SQL Queries
Retrace View All SQL Queries
By selecting an individual query, you see how often that query is called over time and how long it takes. You also see what webpages use the SQL query and how it impacts their performance.

Retrace SQL Performance Over Time
Retrace SQL Performance Over Time
Since Retrace is a lightweight code profiler and captures ASP.NET request traces, it even shows you the full context of what your code is doing.

Below is a captured trace showing all the SQL queries and other details about what the code was doing. Retrace even shows log messages within this same view. Also, notice that it shows the server address and database name that’s executing the query. You can also see how many records were returned.

Retrace Web Transaction Trace
Retrace Web Transaction Trace
As you can see, Retrace provides comprehensive SQL reporting capabilities as part of its APM capabilities. It also provides multiple monitoring and alerting features around SQL queries.

Pros: Detailed reporting across apps, per app, and per query. Shows transaction traces detailed how queries are used. Starts at just $10 a month. Is always running once installed.

Cons: Doesn’t provide the number of reads or writes per query.

  1. SQL Server Extended Events
    The SQL Server Profiler was around for a very long time. It was a very useful tool to see in real-time what SQL queries are being executed against your database, but it’s currently deprecated. Microsoft replaced it with SQL Server Extended Events.

This is sure to anger a lot of people but I can understand why Microsoft is doing it. Extended Events works via Event Tracing (ETW).

This has been the common way for all Microsoft related technologies to expose diagnostic data. ETW provides much more flexibility. As a developer, I could easily tap into ETW events from SQL Server to collect data for custom uses. That’s really cool and really powerful.

SQL Server Extended Events
MORE: Introducing SQL Server Extended Events

Pros: Easier to enable and leave running. Easier to develop custom solutions with.

Cons: Since it is fairly new, most people may not be aware of it.

  1. SQL Azure Query Performance Insights
    I’m going to assume that SQL Azure’s performance reporting is built on top of Extended Events. Within the Azure Portal, you can get access to a wide array of performance reporting and optimization tips that are very helpful.

Note: These reporting capabilities are only available for databases hosted on SQL Azure.

In the screenshot below, you can see how SQL Azure makes it easy to use your queries that use the most CPU, Data IO, and Log IO. It has some great basic reporting built into it.

In the screenshot you can see how SQL Azure makes it easy to use your queries that use the most CPU, Data IO, and Log IO. It is has some great basic reporting built into it.
SQL Azure Top Queries
You can also select an individual query and get more details to help with SQL performance tuning.

You can also select an individual query and get more details to help with SQL performance tuning.
SQL Azure Query Details
Pros: Great basic reporting.
Cons: Only works on Azure. No reporting across multiple databases.

Summary
Next time you need to do some performance tuning with SQL Server, you’ll have a few options at your disposal to consider. Odds are that you’ll use more than one of these tools depending on what you are trying to accomplish.

Try Stackify’s free code profiler, Prefix, to write better code on your workstation. Prefix works with .NET, Java, PHP, Node.js, Ruby, and Python.

If you’re using an APM solution like Retrace, be sure to check what kind of SQL performance functionality it has built-in. If you don’t have an APM solution or aren’t sure what it is, be sure to read this: What is Application Performance Management and 10 critical features that developers need in APM.

What is GEEK

Buddha Community

Cayla  Erdman

Cayla Erdman

1594369800

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Models for SQL exist. In any case, the SQL that can be utilized on every last one of the major RDBMS today is in various flavors. This is because of two reasons:

1. The SQL order standard is genuinely intricate, and it isn’t handy to actualize the whole standard.

2. Every database seller needs an approach to separate its item from others.

Right now, contrasts are noted where fitting.

#programming books #beginning sql pdf #commands sql #download free sql full book pdf #introduction to sql pdf #introduction to sql ppt #introduction to sql #practical sql pdf #sql commands pdf with examples free download #sql commands #sql free bool download #sql guide #sql language #sql pdf #sql ppt #sql programming language #sql tutorial for beginners #sql tutorial pdf #sql #structured query language pdf #structured query language ppt #structured query language

Ruth  Nabimanya

Ruth Nabimanya

1621850444

List of Available Database for Current User In SQL Server

Introduction

When working in the SQL Server, we may have to check some other databases other than the current one which we are working. In that scenario we may not be sure that does we have access to those Databases?. In this article we discuss the list of databases that are available for the current logged user in SQL Server

Get the list of database
Conclusion

#sql server #available databases for current user #check database has access #list of available database #sql #sql query #sql server database #sql tips #sql tips and tricks #tips

Christa  Stehr

Christa Stehr

1594456938

Offshore Software Development - Best Practices

With the rise of globalization and the worldwide lockdown due to the pandemic, most of the work has been done by remote working processes and professionals from their homes. This lockdown has proved the efficiency of remote development and enhanced the trust in offshore software development industry.

To make the most out of the benefits of offshore software development, you should understand the crucial factors that affect offshore development. This is why you should read this guide for the best practices when hiring an offshore software development company. Despite the size and the industry of the business, offshore software development is not beneficial for every entrepreneur in many aspects to make the optimum use of talents in technology across the globe.

Here are some of the top reasons why offshore development is beneficial for your business.

  • Offshore development teams can work on flexible timing to provide you with the best possible software development practices.
  • Get access to the talents across the world from your home to develop the top of the line software with the help of offshore development companies.
  • Assured high quality and next-generation technology expertise with duly NDA signed with respect to the priorities of the business.
  • With flexible recruitment models, you can hire the freelance developers, remote development team, or an entire offshore development company with respect to the size of your business.
  • Build high-end software applications from one corner of the world by hiring software developers across the world.
  • Get immediate access to the best resources without hiring them on a permanent basis.

To avail of all these benefits, you should have clear goals, a list of requirements, and features that are mandatory for your software product.

Here are a few tips to help you find the best offshore software development company. Build a top-notch software application by following the listed best practices.

#web development #how to start offshore software development company #offshore meaning #offshore software development best practices #offshore software development company #offshore software development company in india #offshore software development cost #offshore software development statistics #outsource software development

Fredy  Larson

Fredy Larson

1595059664

How long does it take to develop/build an app?

With more of us using smartphones, the popularity of mobile applications has exploded. In the digital era, the number of people looking for products and services online is growing rapidly. Smartphone owners look for mobile applications that give them quick access to companies’ products and services. As a result, mobile apps provide customers with a lot of benefits in just one device.

Likewise, companies use mobile apps to increase customer loyalty and improve their services. Mobile Developers are in high demand as companies use apps not only to create brand awareness but also to gather information. For that reason, mobile apps are used as tools to collect valuable data from customers to help companies improve their offer.

There are many types of mobile applications, each with its own advantages. For example, native apps perform better, while web apps don’t need to be customized for the platform or operating system (OS). Likewise, hybrid apps provide users with comfortable user experience. However, you may be wondering how long it takes to develop an app.

To give you an idea of how long the app development process takes, here’s a short guide.

App Idea & Research

app-idea-research

_Average time spent: two to five weeks _

This is the initial stage and a crucial step in setting the project in the right direction. In this stage, you brainstorm ideas and select the best one. Apart from that, you’ll need to do some research to see if your idea is viable. Remember that coming up with an idea is easy; the hard part is to make it a reality.

All your ideas may seem viable, but you still have to run some tests to keep it as real as possible. For that reason, when Web Developers are building a web app, they analyze the available ideas to see which one is the best match for the targeted audience.

Targeting the right audience is crucial when you are developing an app. It saves time when shaping the app in the right direction as you have a clear set of objectives. Likewise, analyzing how the app affects the market is essential. During the research process, App Developers must gather information about potential competitors and threats. This helps the app owners develop strategies to tackle difficulties that come up after the launch.

The research process can take several weeks, but it determines how successful your app can be. For that reason, you must take your time to know all the weaknesses and strengths of the competitors, possible app strategies, and targeted audience.

The outcomes of this stage are app prototypes and the minimum feasible product.

#android app #frontend #ios app #minimum viable product (mvp) #mobile app development #web development #android app development #app development #app development for ios and android #app development process #ios and android app development #ios app development #stages in app development

Introduction to Recursive CTE

This article will introduce the concept of SQL recursive. Recursive CTE is a really cool. We will see that it can often simplify our code, and avoid a cascade of SQL queries!

Why use a recursive CTE ?

The recursive queries are used to query hierarchical data. It avoids a cascade of SQL queries, you can only do one query to retrieve the hierarchical data.

What is recursive CTE ?

First, what is a CTE? A CTE (Common Table Expression) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. For example, you can use CTE when, in a query, you will use the same subquery more than once.

A recursive CTE is one having a subquery that refers to its own name!

Recursive CTE is defined in the SQL standard.

How to make a recursive CTE?

A recursive CTE has this structure:

  • The WITH clause must begin with “WITH RECURSIVE”
  • The recursive CTE subquery has two parts, separated by “UNION [ALL]” or “UNION DISTINCT”:
  • The first part produces the initial row(s) for the CTE. This SELECT does not refer to the CTE name.
  • The second part recurses by referring to the CTE name in its FROM clause.

Practice / Example

In this example, we use hierarchical data. Each row can have zero or one parent. And it parent can also have a parent etc.

Create table test (id integer, parent_id integer);

insert into test (id, parent_id) values (1, null);

insert into test (id, parent_id) values (11, 1);
insert into test (id, parent_id) values (111, 11);

insert into test (id, parent_id) values (112, 11);

insert into test (id, parent_id) values (12, 1);

insert into test (id, parent_id) values (121, 12);

For example, the row with id 111 has as ancestors: 11 and 1.

Before knowing the recursive CTE, I was doing several queries to get all the ancestors of a row.

For example, to retrieve all the ancestors of the row with id 111.

While (has parent)

	Select id, parent_id from test where id = X

With recursive CTE, we can retrieve all ancestors of a row with only one SQL query :)

WITH RECURSIVE cte_test AS (
	SELECT id, parent_id FROM test WHERE id = 111
	UNION 
	SELECT test.id, test.parent_id FROM test JOIN cte_test ON cte_test.id = test.parent_id

) SELECT * FROM cte_test

Explanations:

  • “WITH RECURSIVE”:

It indicates we will make recursive

  • “SELECT id, parent_id FROM test WHERE id = 111”:

It is the initial query.

  • “UNION … JOIN cte_test” :

It is the recursive expression! We make a jointure with the current CTE!

Replay this example here

#sql #database #sql-server #sql-injection #writing-sql-queries #sql-beginner-tips #better-sql-querying-tips #sql-top-story