Editions Of SQL Server

There are different editions of SQL Server with different features. Which will provide wide range of options to choose from to match our business requirements.

• Enterprise: This is the high-end edition with the full features. It supports systems up to 2 TB RAM. The maximum size of the database is 524 PB.

• Standard: It has less features than Enterprise, but it is a good choice when advanced functions (such as data compression, compressed backups, indexed views, etc.) are not required for the business. It supports systems up to 4 CPU and 64 GB RAM.

• Workgroup: it is suitable for remote offices of a larger company. It supports systems up to 2 CPU and 4 GB RAM.

• Web: it is designed for web applications. It supports 4 CPU without memory limitations.

• Developer: similar to Enterprise, but licensed to only one user for development, testing and demo. It can be easily upgraded to Enterprise without reinstallation.

• Express: free entry-level database. It can utilize only 1 CPU and 1 GB memory, the maximum size of the database is 10 GB.

• Compact: free embedded database for mobile application development. The maximum size of the database is 4 GB.

Log File Architecture in SQL Server

 Whenever any query is processed, the data will be passed to Data file. Below is the process how a query is processed in SQL Server/ Importance of Log File Architecture:

• Database has Data file and Log file.
• The query statement is passed to the buffer cache and log cache.
• The data in the buffer cache is called as Dirty Data or Dirty Blocks.
• Buffer cache contains the dirty data (the updated data corresponding to the query given in the application).
• The process of writing the data from buffer cache to the data files of the database in the form of chuncks is
  called as Checkpoint Process.
• Each chunck contains 512 KB.
• The query is written into the log file from the log cache.
• If any type of failure occurs while writing data to the data file, then the query in the log file is executed at the last commit transaction processed (refer commit process down) and the remaining data is written to the database whenever we start the server.
• This process of writing data to the database after a failure from the log file is called as Recovery.
• Procedure Cache contains the execution plan.
• Context Cache contains the data of the stored procedure.
• Server Level Data Structure contains the Server level information.

Commit Process:

• As soon as commit statement is written to the log file it throws a token to the user that commit is completed successfully (Ex. 1 row affected), this process is called as Commit Process.

What is an execution plan?

Every day, out in the various discussion boards devoted to Microsoft SQL Server, the same types of questions come up again and again: Why is this query running slow? Is my index getting used? Why isn’t my index getting used? Why does this query run faster than this query?. The correct response is probably different in each case, but in order to arrive at the answer you have to ask the same return question in each case: have you looked at the execution plan? An execution plan, simply put, is the result of the query optimizer’s attempt to calculate the most efficient way to implement the request represented by the T-SQL query you submitted.

Execution plans can tell you how a query will be executed, or how a query was executed. They are, therefore, the DBA’s primary means of troubleshooting a poorly performing query. Rather than guess at why a given query is performing thousands of scans, putting your I/O through the roof, you can use the execution plan to identify the exact piece of SQL code that is causing the problem. For example, it may be scanning an entire table-worth of data when, with the proper index, it could simply backpack out only the rows you need. All this and more is displayed in the execution plan.

The aim of this chapter is to enable you to capture actual and estimated execution plans, in either graphical, text or XML for ­mat, and to understand the basics of how to interpret them. In order to do this, we’ll cover the following topics:

• A brief backgrounder on the query optimizer – execution plans are a result of the optimizer’s calculations so it’s useful to know at least a little bit about what the optimizer does, and how it works.
• Actual and Estimated execution plans – what they are and how they differ
• Capturing and interpreting the different visual execution plan formats – we’ll investigate graphical, text and XML execution plans for a very basic SELECT query
• Automating execution plan capture – using the SQL Server Profiler tool