Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

SELECT 
t1.TicketNummer 
,t1.SiteNummer 
,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 
,t8.VraagNummer 
,t8.Uploaded 
,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 
share|improve this question
6  
People watching cat videos on YouTube, thus using the office connections bandwidth? –  FreshPhilOfSO Jan 17 '13 at 13:48
3  
Monitor your network with perfmon and see peak times vs. off-peak times. You should be able to see a similar correlation with what your end users are finding. –  Thomas Stringer Jan 17 '13 at 14:31
4  
Two (unrelated) things I don't understand: why don't you use meaningful table aliases so that you know at once what belongs where? And do you really need to query a - let me count - 14 table join without a single WHERE condition? That may be a huge amount of data, therefore network traffic... –  dezso Jan 17 '13 at 19:25
1  
@Eric How many are there? –  dezso Jan 17 '13 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 '13 at 20:03
show 7 more comments

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
    
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 '13 at 15:57
1  
@Eric: RBAR? –  Marian Jan 18 '13 at 16: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.) –  Paul Williams Jan 18 '13 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 '13 at 19:27
add comment

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..

share|improve this answer
    
... and then ... ? –  swasheck Jan 17 '13 at 19:06
    
Please see the edited answer –  Gulli Meel Jan 17 '13 at 19:14
    
i'm not sure how helpful that would be (though fringe cases are possible). what it will show are areas for improvement within the context of the query. –  swasheck Jan 17 '13 at 19:27
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 '13 at 2:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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