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.
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...
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
TechNet Community Support