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. 
    • Moved by SSISJoost Friday, March 07, 2014 1:15 PM not ssis related
    Friday, March 07, 2014 7:30 AM

Answers

  • The issue is solved 

    I change the option "Auto Close" to False

    the speed is extremely faster
    • Marked as answer by MegaTamako Friday, March 21, 2014 8:42 AM
    Friday, March 21, 2014 8:42 AM

All replies

  • http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f092e40b-7879-47b9-9af8-b43ff1d7c0b1/sql-services-use-100-harddisk-when-queries?forum=sqlintegrationservices
    Friday, March 07, 2014 7:33 AM
  • Request the moderator to merge this duplicate thread with :

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f092e40b-7879-47b9-9af8-b43ff1d7c0b1/sql-services-use-100-harddisk-when-queries?forum=sqlintegrationservices

    Thanks.


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Friday, March 07, 2014 12:45 PM
  • There are many possible reasons, but start with

    EXEC sp_configure 'show advanced', 1
    RECONFIGURE
    EXEC sp_configure 'max server memory', 1000
    RECONFIGURE

    That limits the amount of memory SQL Server can use for the buffer cache to 1GB. This may not give better performance, but maybe more consistent performance.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, March 07, 2014 10:31 PM
  • In order to speed up the query you need properly created indexes... What does the query do? Can you show us the execution plan ?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Saturday, March 08, 2014 8:27 AM
    Answerer
  • >but when I run with C# application (integrated query inside).

    It is better to write server side stored procedures and call them from client C# apps.

    BOL: "Benefits of Using Stored Procedures


            

    The following list describes some benefits of using procedures.

    Reduced server/client network traffic          

    The commands in a procedure are executed as a single batch of code. This can significantly reduce network traffic between the server and client because only the call to execute the procedure is sent across the network. Without the code encapsulation provided by a procedure, every individual line of code would have to cross the network.

    Stronger security          

    Multiple users and client programs can perform operations on underlying database objects through a procedure, even if the users and programs do not have direct permissions on those underlying objects. The procedure controls what processes and activities are performed and protects the underlying database objects. This eliminates the requirement to grant permissions at the individual object level and simplifies the security layers.

    The EXECUTE AS clause can be specified in the CREATE PROCEDURE statement to enable impersonating another user, or enable users or applications to perform certain database activities without needing direct permissions on the underlying objects and commands. For example, some actions such as TRUNCATE TABLE, do not have grantable permissions. To execute TRUNCATE TABLE, the user must have ALTER permissions on the specified table. Granting a user ALTER permissions on a table may not be ideal because the user will effectively have permissions well beyond the ability to truncate a table. By incorporating the TRUNCATE TABLE statement in a module and specifying that module execute as a user who has permissions to modify the table, you can extend the permissions to truncate the table to the user that you grant EXECUTE permissions on the module.

    When calling a procedure over the network, only the call to execute the procedure is visible. Therefore, malicious users cannot see table and database object names, embed Transact-SQL statements of their own, or search for critical data.

    Using procedure parameters helps guard against SQL injection attacks. Since parameter input is treated as a literal value and not as executable code, it is more difficult for an attacker to insert a command into the Transact-SQL statement(s) inside the procedure and compromise security.

    Procedures can be encrypted, helping to obfuscate the source code. For more information, see SQL Server Encryption.

    Reuse of code          

    The code for any repetitious database operation is the perfect candidate for encapsulation in procedures. This eliminates needless rewrites of the same code, decreases code inconsistency, and allows the code to be accessed and executed by any user or application possessing the necessary permissions.

    Easier maintenance          

    When client applications call procedures and keep database operations in the data tier, only the procedures must be updated for any changes in the underlying database. The application tier remains separate and does not have to know how about any changes to database layouts, relationships, or processes.

    Improved performance          

    By default, a procedure compiles the first time it is executed and creates an execution plan that is reused for subsequent executions. Since the query processor does not have to create a new plan, it typically takes less time to process the procedure.

    If there has been significant change to the tables or data referenced by the procedure, the precompiled plan may actually cause the procedure to perform slower. In this case, recompiling the procedure and forcing a new execution plan can improve performance. "

    LINK: http://technet.microsoft.com/en-us/library/ms190782.aspx

    Optimization:

    http://www.sqlusa.com/articles/query-optimization/


    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012





    • Edited by Kalman Toth Saturday, March 08, 2014 9:59 AM
    Saturday, March 08, 2014 9:54 AM
  • It is better to write server side stored procedures and call them from client C# apps.

    Maybe. But it is unlikely to resolve surendark's issue. There is not really any intrinsic benefit for performance with stored procedures. You can achieve the same performance with SQL sent from the client than with stored procedures. You have to be a little more careful though. (Then again, the same care is needed if you use dynamic SQL in your stored procedures.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, March 08, 2014 10:11 AM
  • does not work
    Thursday, March 20, 2014 4:26 AM
  • Can you try the stored procedure approach?

    sp can be tested independently from the app code in SSMS.

    Can you post the code & table/index DDL? Thanks.


    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Thursday, March 20, 2014 7:10 AM
  • The issue is solved 

    I change the option "Auto Close" to False

    the speed is extremely faster
    • Marked as answer by MegaTamako Friday, March 21, 2014 8:42 AM
    Friday, March 21, 2014 8:42 AM