MS SQL Server is a fabulous product, but it does need some care and attention. We have found that many companies in today's mid-market tier have grown faster than expected. Thus they were worried about their application development, and security but haven't had the time to tend to their database and optimizing SQL code structure. In this article, we would like to share with you a bit or our knowledge to help you begin the path for growth and success by optimizing your SQL Server.
To send Queries to SQL Server the client calls the appropriate DLL to open connections and sent queries and receive results. After opening the connection to the server, the client passes the SQL query string. Then the ODBC driver breaks the SQL string into a tabular data stream and passes the query to Protocol-specific network library. The Network Library converts the Tabular Data Stream into the underlying network protocol breaks down the query statement into network packets and routes them to SQL Server.
The TDS is used to send all communication to and from SQL Server
After receiving a query SQL Server performs a series of operations in the query to generate results. SQL Server has its own server-side network library, which passes the reconstituted date to the Open Data Services. ODS determines what type of request is being made and passes the reconstituted query to the Query Processor. The Query Processor invokes the parser which performs a syntax check on the entire SQL batch.
The query is now processed for optimization, looking for from clause, verifies the table's existence, identifies the columns in select values and verifies existence in corresponding tables. Optimizes the query, compiles the query execution plan and finally checks the permission, executes the plan and returns the results.
The result set is returned through the TDS
The determining factors of this process taking subseconds, minutes, days or hours. Is determined by the completeness and cleanness of the SQL code, the use of indexes in the database, hardware settings and optimal performance, the "fat," Client application logic, the network general usage and whether the pipe access is wide enough. All of these aspects can be tracked, and determined by using tools within and added onto SQL Server.
However, as you have seen the SQL Server itself has seven different steps to follow before the query is finally executed. It must identify the table, identify the columns, optimize the query, compile the query execution plan, check permissions, execute the plan and return results.
In this process lies a lot of your ability to create effective code to help the SQL Server run those queries in seconds or subseconds. You can begin with following using tools in SQL server like set showplan on, following these query optimization steps; Parse the query, build a query tree, analyze the ways to access the data and select the fastest route.
At Soaring Eagle consulting we mentor our clients, hold training classes and can manage the performance and optimization of your data for you. If you would like to read more, please email: sales@soaringeagle.guru
