Query is running slowly in morning times and it is running normally in evening times.

Question

Post reply

Query is running slowly in morning times and it is running normally in evening times.

MasterDB

Hall of Fame

Points: 3054
More actions
June 8, 2011 at 1:53 am

#241040

Hi Folks,
am executing a query in morning times and it is taking more than 35 mins to execute the query which fetches only 6000 records. and same query is executing normally in evening times ,takes 2-3 mins to execute.
i've created few indices, which the query is fetching the records from the tables. And there is no improvement in the performance.
Can anyone help me to fix this performance issue.
TIA..

Viewing 11 posts - 1 through 10 (of 10 total)

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

M&M SSC-Insane Points: 21700 More actions · Answer 1

Information provided is not sufficient. Please refer to below link by Gail Shaw and post as per the link.

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

M&M

MasterDB Hall of Fame Points: 3054 More actions · Answer 2

Thanks for your reply,

But i could not find any help with that link.

i want to know why it is taking more time to execute in morning times when compared to evening times?

Dwayne Dibley SSCrazy Points: 2350 More actions · Answer 3

Perhaps your server is under a heavier load in the morning.

Perhaps it hasn't woken up properly...

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

Have you checked the execution plans in the morning and in the afternoon? Use the dynamic manage objects (DMO) such as sys.dm_exec_query_stats and sys.dm_exec_query_plan, to pull the plan out of cache. Compare the morning to the evening.

You said you created indexes, but did you create them based on your knowledge of the execution plans so that you knew that the indexes were going to make a difference?

You could also be looking at blocking. Search in the scripts here on SSC to get a blocking monitor script and use that to see if you're experiencing your problem.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

srivathsani-296624 Ten Centuries Points: 1208 More actions · Answer 5

Are you running any sql jobs in the morning because of which the the query performance is slow?Compare teh execution plans of the queries by running in evening and morning.You might get some idea.You can also use profiler to the events running in that instance of sql server at that time.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Can you post the query?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

MasterDB Hall of Fame Points: 3054 More actions · Answer 7

Hi folks,

Thanks for your reply's,

I've executed the execution plan on both the timings and what i came to know is the usage of Tempdb is 97% which is more at afternoon timings.

Is that creates any performance probs?

if yes, kindly suggest me what to do?

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 8

An execution plan isn't going to tell you that tempdb is 97% of usage. It's giong to tell you that you have index scans instead of seeks or a loop join where you should have a merge or hash (or vice versa), or that extra sort operations are being performed or stuff like that.

It's really hard to know what's going on unless you post the two different execution plans. Actual plans are best, but estimated plans are OK.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

I addition to reviewing the query plans you also need to check IOs done by each query and most importantly do wait stats and file IO stall analyses during the executions to see how the load on the server is affecting the query runs. Oh, and run sp_whoisactive while each is running too to see things like blocking, cpu/read/tempdb usage, query plans, etc.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 10

Provide Execution PLan

and check IOs of particular Query ,execute these queries in the morning and send the result

For Memory Presure

==================

select * from sys.dm_os_performance_counters

where counter_name like 'page life%'

For Queries Utilizations

========================

SELECT ST.TEXT,SP.SPID,WAITTIME,LASTWAITTYPE,CPU,PHYSICAL_IO,STATUS,HOSTNAME,PROGRAM_NAME,CMD,LOGINAME FROM SYS.SYSPROCESSES SP

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST

WHERE STATUS !='SLEEPING'

ORDER BY CPU DESC

My Blog
www.aureus-salah.com