Things to check for a slow running SQL query

Question

1.00/5 (1 vote)

See more:

I have a SQL query embedded in a stored procedure. Everyday it takes 30 min of run time for its execution. but for the past on week the execution time for that query is increasing like day 2 it took 40 min and day three 45 min and day 4 55 min and so on... i am not sure why its behaving like that and any suggestions what I need to look for when I encounter this kind of scenarios?

The Stored procedure contains just a big query of SELECT.

What I have tried:

I just looked for any locks on the tables that is being used in the SELECT query.

Posted 30-Aug-16 2:42am

dinesh42

Updated 31-Aug-16 14:42pm

Add a Solution

Comments

[no name] 30-Aug-16 8:46am

So, you have not actually tried anything? Why didn't you run the SQL Profiler and see what it had to say?

Patrice T 30-Aug-16 8:53am

Your Query is constructed badly.
If your show the query, it may help to see the reason.

4 solutions

Solution 4

The first thing to do is to check the query itself against the database structure.
Without the query and database structure, not much cab be devised.

Posted 31-Aug-16 14:42pm

Patrice T

Solution 3

It could be your indexes need maintenance, you can do this from SQL Server Management Studio by right clicking the index and select 'rebuild' or 'reorganize'.
See: How to: Rebuild an Index (SQL Server Management Studio)[^]
See: Reorganizing and Rebuilding Indexes[^]

Posted 30-Aug-16 7:58am

RickZeeland

Solution 2

As Dave Kreskowiak mentioned, there's a lot of things to check.

I'd suggest to follow the instructions provided on the CodeProject Knowledge Base[^]. For example:
Improving the performance of queries using SQL Server: Part 1[^]
Improving the performance of queries using SQL Server: Part 2[^]
SQL Server Performance Tips and Guidelines[^]

Posted 30-Aug-16 7:53am

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Accepted Answer · 2016-08-30T04:31:00

There's a bunch of stuff that can cause problems, from a poorly written query, poorly written stored procedures, poorly written views, improper indexing setup on tables, resource problems on the server, ...

We have nothing to go on at all so there's no way you're getting a definite answer like do this" and expect it to fix anynthing.

Your first step would be to run the SQL Profiler to get an idea of where the bottlenecks are. Take a look at the execution plans and get with a DBA to help you on this.