none
SQL services use 100% Harddisk when queries

    Question

  • I developed  my own app to query the data from MSSQL

    I use MSSQL 2008 R2

    when I run the query in MSSMS, it's work fine and has very fast response

    but when I run with C# application (integrated query inside).

    it take about 5000ms for 1 query and when I exterminate the task manager, I found that the services of my database engine using disk load about 5-7 MB/s (but the % is 100%)  

    after some experiment. I found out that if I want to make the query faster in my C# app, I have to open the MSSMS "together" (and make any select top 1000) with my query in C# app

    anyone know what is MSSMS do to increase the performance of any running services?


    **Note that it happens only some PC not all of them. 
    Friday, March 07, 2014 7:34 AM

All replies

  • Management Studio is a client application, nothing more. it doesn't speed up anything.

    Nevertheless, when you first make a select top 1000, the data pages concerned by the query are read from the disk then stored in the SQL Server Buffer pool. Next time the same pages will be queried, they will be retrived directly from memory...

    JS

    Friday, March 07, 2014 8:12 AM
  • assume that there are 3 tables
    I just select top 1000 from table 1

    but the services will increase the speed of 2 and 3

    any suggestion to make the data in to pool of memory?
    • Edited by MegaTamako Friday, March 07, 2014 8:39 AM
    Friday, March 07, 2014 8:38 AM
  • Hello,

    The difference query performance may occurs when the connection parameters and SET options might be different between SSMS and your application. You can try to use SQL Profiler to monitor which SET commands are used by both SSMS and application  and find differences.

    What's more, if you had run the same query on the SSMS multiple times, then it will  be fast as results are being cached.

    You can also refer to Erland Sommarskog's article about the same topic:
    Slow in the Application, Fast in SSMS?Understanding Performance Mysteries

    Regards,
    Fanny Liu   


    Fanny Liu
    TechNet Community Support

    Monday, March 10, 2014 9:12 AM