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 very worried about their application development and security, but haven't had the time to tend to their database and Structured Query Language code structure.
The purpose of this blog is to share with you a bit of our knowledge to help you begin the path for growth and success by optimizing your SQL Server.
To send Queries to the SQL Server, the client calls the appropriate DLL to open connections, send queries, and receive results. After establishing a connection with the server, the client enters the SQL query string. Then the ODBC driver breaks the SQL string into a tabular data stream and passes the query to a Protocol-specific network library. The Network Library then 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 to generate results. The SQL Server has its own server-side network library that passes reconstituted data 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 then invokes the parser, which performs a syntax check on the entire SQL batch.
The query is now processed for optimization, looking for form clause, verifies the table's existence, identifies the columns in select values, and verifies existence in corresponding tables. It 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 in this process can take sub-seconds, minutes, days or hours. It will all depend on 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, the SQL Server itself has seven different steps to follow before the query is finally executed. It must identify the table and the columns, optimize the query, compile the query execution plan, check permissions, execute the plan, and return results.
Your ability to create effective code to help the SQL Server run those queries in seconds or sub-seconds will affect the process. You can begin with following using tools in SQL server like set show plan on, following these query optimization steps; Parse the query, build a query tree, analyze 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. Contact us today to learn more.
Want Your Database to Soar Like an Eagle?
Check out our ultimate database checklist to help you check on your database’s performance and maintenance.
