I have build an application that handles data stored in a MS SQL server database. This server is hosted externally of our company and has to be connected to via SSH because of company rules.

On a regular working day it takes about 7 minutes to retrieve a list of data, while it takes less than one minute to do the same outside office hours.

The database is relatively simple. There are 3 main tables which have a relation to each other on one field which is also set as primary key. These tables have relations with several other small tables where lists are stored. So the fields in the main tables are integer fields related to a small table where that integer points to a text field.

I'm trying to figure out why it takes 7 minutes during office hours and less than one 1 outside office hours.

There are about 12 users.

Anyone have some tips for me?

rg, Eric

Edit: SQL code:

,t9.name AS Categorie 
,t7.name AS Klant 
, (CASE WHEN t11.RapportNaam IS NULL THEN t5.FullName ELSE t5.FullName END) AS     AangenomenDoor  
, (CASE WHEN t11.RapportNaam IS NULL THEN t1.AangenomenOp ELSE t1.AangenomenOp END) AS     AangenomenOp 
, (CASE WHEN t11.RapportNaam IS NULL THEN t1.aangenomenop ELSE t8.UitgevoerdOp END) AS     UitgevoerdOp 
, (CASE WHEN t11.RapportNaam IS NULL THEN t5.FullName ELSE t8.UitgevoerdDoor END) AS UitgevoerdDoor 
, (CASE WHEN t11.RapportNaam IS NULL THEN 'Via ESIT' ELSE t11.RapportNaam END) AS Rapport 
,t2.Name AS PrioCode 
, t4.offertenummer 
, t4.ponummer 
, CASE WHEN t4.hersteldoor=0 THEN '' 
       WHEN t4.hersteldoor=1 THEN 'Aannemer' 
       WHEN t4.hersteldoor=2 THEN 'Eigen personeel'
       WHEN t4.hersteldoor=3 THEN 'Operator' 
       ELSE 'Aannemer' END AS TeHerstellenDoor 
, t12.refnraannemer 
, t12.offertebedrag 
, t13.name as Operator 
, t4.operatorRefNr 
,(CASE WHEN t1.IsManco ='true' AND t1.IsOpgelost = 'false' THEN 'Manco' 
       WHEN t1.IsConstatering ='true' THEN 'Constatering' 
       ELSE '' END) AS IsManco 
,(CASE WHEN t1.IsOpgelost ='true' THEN 'Direct opgelost' 
       WHEN t6.verwijderd = 'true' THEN 'Verwijderd' 
       ELSE t3.name END) AS VerwerkingsGroep 
,(CASE WHEN (year(t4.DatumTechnischGereed)<2000 or t4.technischgereed='false') THEN NULL     ELSE CONVERT(VARCHAR,t4.DatumTechnischGereed,20) END) AS DatumTechnischGereed 
,(CASE WHEN (year(t4.DatumAdministratiefGereed)<2000 OR t4.administratiefgereed='false')     THEN NULL ELSE CONVERT(VARCHAR,t4.DatumAdministratiefGereed,20) END) AS     DatumAdministratiefGereed 
,(CASE WHEN (year(t4.DatumFinancieelVerwerkt)<2000 OR t4.financieelverwerkt='false')     THEN NULL ELSE CONVERT(VARCHAR,t4.DatumFinancieelVerwerkt,20) END) AS     DatumFinancieelVerwerkt 
, (CASE WHEN t4.financieelverwerkt = 'true' THEN 'Ja' ELSE '' END) AS financieelVerwerkt 
, (CASE WHEN t4.AdministratiefGereed = 'true' THEN 'Ja' ELSE '' END) AS     AdministratiefGereed 
, (CASE WHEN t4.TechnischGereed = 'true' THEN 'Ja' ELSE '' END) AS TechnischGereed 
, t10.Name AS Aannemer 
, t6.verwijderd 
, t14.fullname AS MutatieDoor 
,(CASE WHEN (year(t4.MutatieOp)<2000) THEN NULL ELSE t4.MutatieOp END) AS MutatieOp 
FROM MainTickets AS t1 
LEFT JOIN PrioCode AS t2 ON t1.PrioCode =t2.iD 
LEFT JOIN TicketVerwerking AS t4 ON t1.TicketNummer =t4.TicketNummer 
LEFT JOIN Verwerking AS t3 ON t4.VerwerkingsGroep =t3.ID 
LEFT JOIN Gebruikers AS t5 ON t1.AangenomenDoor =t5.ID 
LEFT JOIN TicketNummers AS t6 ON t1.TicketNummer=t6.TicketNummer 
LEFT JOIN Klanten AS t7 ON t1.Klant=t7.ID 
LEFT JOIN SubTickets AS t8 ON t8.TicketNummer =t1.TicketNummer 
LEFT JOIN Categorie AS t9 ON t9.ID =t1.Categorie 
LEFT JOIN Aannemers AS t10 ON t10.ID =t4.aannemeropdracht 
LEFT JOIN FormIDs AS t11 ON t8.formidcode=t11.FormIDcode 
LEFT JOIN OfferteAannemers AS t12 ON (t4.offertenummer=t12.offertenummer AND     t12.aannemer=t4.aannemeropdracht)
LEFT JOIN Operators AS t13 ON t4.operator=t13.id 
LEFT JOIN Gebruikers AS t14 ON t4.mutatiedoor =t14.ID 
ORDER BY t1.TicketNummer 
  • I ran an fragmentation count query but I don't know if this is bad or not:
    – Eric
    Jan 17, 2013 at 15:03
  • @dezso Yes, there is no where condition here. This table should show all tickets. I'm trying to get my customer to understand this is or will be a problem but I'm not there yet...
    – Eric
    Jan 17, 2013 at 19:39
  • 1
    @Eric How many are there? Jan 17, 2013 at 19:43
  • 1
    You realize this does nothing at all: CASE WHEN t11.RapportNaam IS NULL THEN t5.FullName ELSE t5.FullName END? It's just t5.FullName. There are other problems with the query.
    – ErikE
    Jan 17, 2013 at 20:03
  • 1
    And all these YEAR(DateColumn) < 2000 should be converted to DateColumn < '2000-01-01' Jan 17, 2013 at 22:08

2 Answers 2


During work hours, take a look at sys.dm_exec_requests and see what the wait_type column says. This will tell you what the requests are waiting for.

Right before working hours, you could run DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR); and then look at sys.dm_os_wait_stats to see the accumulated stats during the day.

Do you have indexes defined on any of these tables? If not, then table scans might be clogging your I/O system, and locks might be causing some blocking.

If people are running SQL Profiler traces during work hours, those could be slowing down the entire system. You could detect those by quering sys.traces.

Running a careful profiler trace yourself might reveal some interesting facts about this query. Try capturing a Showplan XML Statistics Profile event when it runs. Maybe the query processor is choosing a very bad query plan. Maybe the plan is generating intermediate tables that have a ridiculous number of records, or maybe there is a bad nested loop that would work better with an index or with a merge join.

Since this is an external server, another possibility is that you are simply overwhelming network bandwidth during working hours.

You mentioned in a comment that there was a lot of data coming back. Adding some filters in the query might help-- if that is an acceptable solution for your application.

  • Not the really the answer, but it helped. Also Gulli Meels answer helped. Next step is to redesign data retrieval with filter before sending to sql instead of filtering after. Thanks all.
    – Eric
    Jan 18, 2013 at 15:57
  • @Eric: Good idea! I was assuming you needed all that data, but if you can filter it at the server, that should help enormously. (I updated my answer to mention this.) Jan 18, 2013 at 17:36
  • @PaulWilliams: and some indexes that will go along those WHERE clauses might not hurt at all. A careful Profiler trace would be actually a server trace (with stored procedures, not Profiler) :-).
    – Marian
    Jan 18, 2013 at 19:27

Check and compare execution plan when the query run during office hours and after office hours.

If plans are not same then a different plan might be causing issue .If the plans are same then it could be network issue or it might be possible that server has too much load during office hours due to high workload and thus waits are more..

  • 1
    Same query can generate different plan and those are quite different from each other and thus could take totally different time to execute. With such a high diff in time(e.g. OP's query) could be caused by diff plan. With same plan, at different time the execution time could be different at different time of the day depending on the workload e.g. query is CPU intensive and there are lots of queries which are using CPU during office hours and thus query execution takes time. There are so many other causes but first you have to rule out that both plans are same or not and then start next step.
    – Gulli Meel
    Jan 18, 2013 at 2:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.