Post reply

SQL query is intermittenly slow

  • January 27, 2010 at 3:26 am

    #214892

    Hi,

    One of our application have a complex SELECT query and it has been doing its job in 20-30 minutes and suddenly it has been running for more than 10Hrs and not finishing at all. But again after next load i has completed in 20minutes. This has happened 3 times. We have identified the high usage of resources and query plan is also very costly. So done a fine tuning to rewrite the query and has worked in considerable amount of time at the same data set. But the difficult part was to convince the reason of this strange behaviour to my managers, because the query works sometimes in 20-30 minutes and then next load it wont complete in even 10 hours, then again it completes in 20-30 minutes. Also there are no any changes made in application as well as the environment.

    We have a Windows server and 12GB is allocated for this database server, where SQL Server 2005 installed. Following are the statistics of the tables used in SQL query

    SELECT '1' as SrNo , 'A' as TableName, 5 as Occurance, 53054095 as NoOfRecor, '3267690' as WeeklyDataGrowth

    union all

    SELECT '2','B', 3, 13694808, '202384' as WeeklyDataGrowth

    union all

    SELECT '3', 'C', 1, 12431574, '219309' as WeeklyDataGrowth

    union all

    SELECT '4', 'D', 2, 12222512, '211747' as WeeklyDataGrowth

    union all

    SELECT '5', 'E', 1, 8376945, '179472' as WeeklyDataGrowth

    union all

    SELECT '6', 'F', 2, 675881, 'NA' as WeeklyDataGrowth

    union all

    SELECT '7', 'G', 6, 396355, '4039' as WeeklyDataGrowth

    union all

    SELECT '8', 'H', 3, 330169, '6232' as WeeklyDataGrowth

    union all

    SELECT '9', 'I', 1, 253453, '5090' as WeeklyDataGrowth

    union all

    SELECT '10', 'J', 8, 238166, '2086' as WeeklyDataGrowth

    union all

    SELECT '11', 'K', 3, 188076, '2505' as WeeklyDataGrowth

    union all

    SELECT '12', 'L', 1, 184398, '2501' as WeeklyDataGrowth

    union all

    SELECT '13', 'M', 1, 7156, '30' as WeeklyDataGrowth

    union all

    SELECT '14', 'N', 6, 1721, 'NA' as WeeklyDataGrowth

    union all

    SELECT '15', 'O', 1, 101, 'NA' as WeeklyDataGrowth

    union all

    SELECT 16, 'P', 1, 101, 'NA' as WeeklyDataGrowth

    union all

    SELECT 17, 'Q', 1, 1, 'NA' as WeeklyDataGrowth

    On the above table 'Occurance' refers to the number of times the same table is scanned. We have identified the use of extensive parellelism and restricted usign the MAXDOP option , but it was not working. Even we tried to do update stats, reindexing as well. All these have methods have not succeeded.

    Could somebody please tell me what is the exact reason of this unexpected behaviour and how can I explain this to my mangers?

    Thanks in advance

  • January 27, 2010 at 4:00 am

    #1109196

    there could be several reasons

    1.problem with application itself

    2.there could be blocking on some tables becoz of that it takes a long time to execute.

    when your app executes the report it waits for the table to get unlocked before proceeding.

  • January 27, 2010 at 4:19 am

    #1109205

    Thanks for your reply,

    There were no any locks at that time.

  • January 27, 2010 at 6:27 am

    #1109234

    Seems to be Parameter Sniffing..

    Look at this article http://www.sqlmag.com/Articles/ArticleID/94369/94369.html?Ad=1

    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • January 27, 2010 at 7:30 am

    #1109289

    Thanks Bru...

    On 'parameter sniffing' : We have restarted the server and i feel thus the cache will be cleared. Please correct me if i am wrong

    The other thing is, I feel could be due the latest plans which is calculated based on the latest data set. But my worry is SQL server is generating a 'quickly running' query plan in one day and then next day it is generating another 'very poorly running' query plan and then next day another 'quickly running' query plan.

    I have added the table info in a SELECT statemet, thanks for the etiquetteinfo as well

Viewing 5 posts - 1 through 4 (of 4 total)

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