What is nested loops join?

What is nested loops join?

The nested loops join uses one join input as the outer input table (shown as the top input in the graphical execution plan) and one as the inner (bottom) input table. The outer loop consumes the outer input table row by row. The inner loop, executed for each outer row, searches for matching rows in the inner input table.
In the simplest case, the search scans an entire table or index; this is called a naive nested loops join. If the search exploits an index, it is called an index nested loops join. If the index is built as part of the query plan (and destroyed upon completion of the query), it is called a temporary index nested loops join. All these variants are considered by the query optimizer.
A nested loops join is particularly effective if the outer input is small and the inner input is preindexed and large. In many small transactions, such as those affecting only a small set of rows, index nested loops joins are superior to both merge joins and hash joins. In large queries, however, nested loops joins are often not the optimal choice.

SELECT * FROM customer, orders 
WHERE customer.customer_num=orders.customer_num 
AND order_date>"01/01/1997";

What is Hash Join?

What is Hash Join?
The optimizer usually uses a hash join when at least one of the two join tables does not have an index on the join column or when the database server must read a large number of rows from both tables. No index and no sorting is required when the database server performs a hash join.

A hash join consists of two activities: first building the hash table (build phase) and then probing the hash table (probe phase). Figure 48 shows the hash join in detail.

In the build phase, the database server reads one table and, after it applies any filters, creates a hash table. Think of a hash table conceptually as a series of buckets, each with an address that is derived from the key value by applying a hash function. The database server does not sort keys in a particular hash bucket.

Smaller hash tables can fit in the virtual portion of database server shared memory. The database server stores larger hash files on disk in the dbspace specified by the DBSPACETEMP configuration parameter or the DBSPACETEMP environment variable.

In the probe phase, the database server reads the other table in the join and applies any filters. For each row that satisfies the filters on the table, the database server applies the hash function on the key and probes the hash table to find a match.

In-memory BI

In-memory BI gained popularity largely due to the success of QlikTech, provider of the in-memory-based QlikView BI product. Following QlikTech’s lead, many other BI vendors have jumped on the in-memory “hype wagon,” including the software giant, Microsoft, which has been aggressively marketing PowerPivot, their own in-memory database engine.

The concept of in-memory business intelligence is not new. It has been around for many years. The only reason it became widely known recently is because it wasn’t feasible before 64-bit computing became commonly available. Before 64-bit processors, the maximum amount of RAM a computer could utilize was barely 4GB, which is hardly enough to accommodate even the simplest of multi-user BI solutions. Only when 64-bit systems became cheap enough did it became possible to consider in-memory technology as a practical option for BI.

When we talk about whether a database is disk-based or in-memory, we are talking about where the data resides while it is actively being queried by an application: with disk-based databases, the data is queried while stored on disk and with in-memory databases, the data being queried is first loaded into RAM.

Relational databases are great as the backbone of operational applications such as CRM, ERP or Web sites, where transactions are frequently and simultaneously inserted, they are a poor choice for supporting analytic applications which usually involve simultaneous retrieval of partial rows along with heavy calculations.

The “traditional best-practice” BI has been based on the following architecture:

We start with business applications that gather the data we would like to analyze.
We create a copy that’s typically called an “operational data store” or ODS, then we use ETL (extraction, transformation, and loading) technology to load data into database structures optimized for business intelligence – a data mart or data warehouse.
To provide better interactivity, an additional data cache is often created for a particular report or cube.
Because this architecture is slow and unwieldy, organizations often create extra data marts for a particular business need.
The result is a vacuum tube: it works, and it’s the best alternative we have right now, but it’s slow, complex, and expensive.



The “In Memory” BI has been based on the following architecture:

“In Memory” BI store all the required data in-memory.
Operations run with the support of massively parallel processing.
Column databases are very well adapted to parallelization: because each column is stored in a separate area of memory, aggregates can be efficiently handed off to a separate processor, or even partitioned across several processorsWith column database, data loading times are no longer a problem.
64 bit addressing has radically increased how easy data to be accessed.

QlikView is a relatively non-invasive, business-focused product that uses in-memory technology to present data in an associative architecture. QlikView is a technology aimed at helping analysts and business people get insights from information as part of a highly visual process. When business users explore data in QlikView, they use associations and patterns that are more like those in the human mind than in a traditional database, releasing users from the limitations of those databases. QlikView’s claim to value is that it gets business users working quickly, collaboratively and flexibly, without making business intelligence an IT project in and of itself. The idea is that, through associative search, and displays that gray out (but still show) data that doesn’t fit in the framework of the query, users can not only answer questions quickly, but can even find the answers to the questions they didn’t ask — but should have. Its accelerated and accessible brand of business intelligence is called “business discovery.”

The beauty of it is that with QlikView:

Metadata management is optional and pervasive. QlikView customers use metadata only when and where it adds value. QlikView creates metadata automatically. Whether or not it is used is up to the designers and developers.
Our focus is on QlikView itself. QlikView’s metadata focus is on helping stakeholders understand and manage the QlikView environment. Developers and designers get a clear picture of how well their QlikView applications were built and gain insights that help them maintain their applications.
Developers can introduce metadata usage over time. With QlikView, developers do not have to create a metadata layer ahead of time. They can define and collect metadata after they have created, tested, and even deployed applications. Project teams can instead focus on getting the right business information and analytical tools in the hands of the right people at the right time.

Self-Service Business Intelligence and Analytics

Self-Service Business Intelligence and Analytics

Self-service means different things to different people, who want to interact and analyze data to discover new insights on their own and ability to consume it. Match self-service capabilities to your users: information consumers will be interested in reporting with little to no interactivity; more data-savvy users will want drill-down analysis, data querying, and report authoring capabilities; and analysts will want visual data discovery that helps them answer new questions as they arise.

Traditional BIs Vs QlikView :
Traditional BI suites usually remind us of rigid and complex UIs involving extensive usage of SQLs and resemble visual front-end extensions of databases.
Unlike traditional BI’s, QlikView sits on your existing data warehouses and infuses a broad set of self-service BI capabilities.Most traditional BI platforms don't fit into this. Controllers are usually not offered alongside models and views, that's why they are considered as visual extensions of your databases.Traditional report centric BI vendors have been vying to fit in the self service capability into their systems for a long time. Traditional BI architectures typically require mobile reporting layers.In contrast, QlikView offers a unified architecture for delivering cross platform Business discovery capabilities on mobile allowing users to benefit from a consistent experience across devices. Making full use of rich HTML5 features, QlikView offers appealing layouts, compelling visualizations and a bag full of touch interactions on new age mobile devices, thus delivering an user experience on par with native apps.



How QlikView offer Self-Service BI?
QlikView enables IT pros to deliver self-service BI, providing a competitive advantage to the business. With QlikView, IT allows business users to create their own analyses so they can arrive at innovative decisions. QlikView lets IT serve the business like never before—all while assuring strict data security, quality, and governance. Combined with the direct data access capabilities offered by QlikView’s Direct Discovery, virtually unlimited data can be addressed. It lets IT deliver a consistent, highly-responsive user experience, while freeing it up to focus on core competencies.

 QlikView enables IT professionals to deliver far more analytics and BI to the business than with any other approach. With its rapid application development capability, requests from the business for new apps can be answered much more quickly, changes to existing applications made easily and with its self-service approach, business users can get answers to their own questions without turning back to IT for a new report. This all contributes to the success of QlikView and makes IT groups who provide it to an organization true ‘champions to the business’.

ho want to interact and analyze data to discover new insights on their own - See more at: http://www.logianalytics.com/blog/5-keys-self-service-success#sthash.im9sszXi.dpuf

ho want to interact and analyze data to discover new insights on their own - See more at: http://www.logianalytics.com/blog/5-keys-self-service-success#sthash.im9sszXi.dpuf

Self-service means different things to different people - See more at: http://www.logianalytics.com/blog/5-keys-self-service-success#sthash.im9sszXi.dpuf

Self-service means different things to different people - See more at: http://www.logianalytics.com/blog/5-keys-self-service-success#sthash.im9sszXi.dpuf

How does SQL Server internally store the dates?

SQL Server  uses 8 bytes to store a datetime value—the first 4 for the date and the second 4 for the time. SQL Server can interpret both sets of 4 bytes as integers. For the date portion, the value SQL Server stores is the number of days before or after a base date of January 1, 1900. Because of this storage protocol, SQL Server assumed the date of January 1, 1900, when you didn't supply the date, SQL Server internally stored a value of 0. A negative number represents a date earlier than January 1, 1900.

SQL Server stores the second integer for the time as the number of clock ticks after midnight. A second contains 300 ticks, so a tick equals 3.3 milliseconds (ms). You can see the values for days and clock ticks by converting a datetime value to a binary(8) value and using the substring function to extract each set of 4 bytes. 

Firewall ports to open for SQL Server 2008

Firewall ports to open for SQL Server 2008

SQL Admin Connection- 1434 (TCP)
SQL Debugger - 135 (TCP)
SQL Service Broker - 4022 (TCP)
SQL TCP Browser - 2382 (TCP)
SQL UDP Browser - 1434 (UDP)
SQL Server - 1433 (TCP)  Note: This may have to be open on the client side as well
SQL Dynamic Port - 49172 (TCP)

Client ports are assigned a random value between 1024 and 5000

SELECT DISTINCT

    local_tcp_port

FROM sys.dm_exec_connections

WHERE local_tcp_port IS NOT NULL

Basic Database Terms

Basic Database Terms

A session is a single connection to SQL Server, identified by a unique SessionID value. It is initiated through an application when the open method is used on a connection object or through a tool like SSMS when the File | Connect menu item is selected. Even though multiple sessions may originate from the same application (and many query windows opened by the same user using the same SSMS instance), as far as SQL Server is concerned, these are all completely separate SQL Server sessions.

                        Locking occurs when a SQL Server session takes "ownership" of a resource by acquiring a lock, prior to performing a particular action on that resource, such as reading or updating. Locking will stay in effect until SQL Server releases the locks. Note that locking itself is not a problem; it has very little measurable impact on any aspect of our systems, including performance, except when it results in blocking or deadlocking, or when we are performing excessive monitoring of our system locks.

                        Blocking occurs when at least two sessions desire concurrent access to the same resource. One session acquires a lock on the resource, in order to perform some action, and so renders that resource temporarily unavailable to other sessions. As a result, other sessions requiring the same resource are temporarily blocked. Typically, the blocked sessions will gain control of the resource after the blocking session releases the locks, so that access to the resource is serialized. Note that not all concurrent access will cause blocking; it is dependent on the operations being performed by the sessions, which determines the type of locks that are acquired.

                A deadlock occurs when two sessions mutually block each other. Neither one can release the resources it holds until it acquires a lock on the resource the other session holds. A deadlock can also involve more than two sessions, trapped in a circular chain of dependencies. For example, session A may hold a resource that session B wants, and in turn session A is waiting for session C to release a resource. Session B may also hold a resource that session C wants. So session A is blocking B and is blocked by C, session B is blocking C and is blocked by A, and session C is blocking A and is blocked by B. None of the three sessions can proceed.

• Pressure is a term used to indicate a state where competition for access to a certain resource is causing performance issues. In a database with well-designed tables and queries, SQL Server acquires and releases locks quickly and any blocking is fleeting, and undetectable by the end-user. However, in certain circumstances, such as when long-running transactions hold locks on a resource for a long time, or where a very high number of sessions all require access to the same shared resource, blocking issues can escalate to the point where one session that is blocked, in turn blocks other sessions, which in turn block others. As the "queue" of blocked sessions grows longer, so the load on the system increases and more and more users start to experience unacceptable delays. In such cases, then we say that the resource is experiencing pressure.

Transactions The simplest definition of a transaction is that it is a single unit of work; a task or set of tasks that together form an "all-or-nothing" operation. If some event interrupts a transaction in the middle, so that not all of it was completed, the system should treat the transaction as if it never occurred at all. Transactions can apply to other kinds of systems besides databases, but since this is a database-specific book, we'll be concerned only with database transactions. A transaction can be short, like changing the price of one book in the inventory, or long, like updating the quantity sold of every inventory item at the beginning of an accounting period.