Internal architecture of TempDB

The internal architecture of TempDB is largely the same as a user database, using the same page and extent structures for data storage. The key differences lie in its non-durable nature, minimal logging, and a set of operational restrictions that optimize it for temporary, high-concurrency workloads. 

TempDB is a globally shared system database used by SQL Server to store temporary objects, intermediate query results, worktables, version store, and spill data. It is structurally similar to a user database but behaves differently internally — it is recreated on every SQL restart, always uses Simple Recovery Model, supports minimal logging, does not support backup/restore, and does not perform crash recovery. TempDB performance is critical because almost every workload uses it; therefore, multiple data files, metadata optimizations, and caching mechanisms are used to reduce contention.

Below objects TempDB Store Internally

Ø  #Temp Tables

Ø   ##Global Temp Tables

Ø   @Table Variables (metadata + spills)

Ø   Table-Valued Parameters when spilled

Ø  Worktables created internally by SQL Server for

o   Sorting
o   Hash joins
o   Hash aggregates
o   Cursors
o   Spools
o   ORDER BY without index
o   DISTINCT
o   GROUP BY

Ø  Version Store

o   Read Committed Snapshot Isolation (RCSI)
o   Snapshot Isolation
o   Online Indexing
o   MARS

Ø  Row Overflow & Spill Data

o   Sort spill to disk
o   Hash spill to disk

o   Memory Grant overflow

TempDB is Re-created on every SQL Restart and System table copied from model database with Empty, clean state each startup and Space is not recovered per session; engine manages cleanup. TempDB uses the same storage structures as a normal DB like Pages, GAM, SGAM, PFS, IAM , DCM/BCM etc.

TempDB uses minimal logging because It never needs crash recovery. It never needs to rollback after restart. No point-in-time recovery. No backups allowed. TempDB is ALWAYS in Simple Recovery Model we cannot change it.

TempDB Internal Architecture vs User Database

Feature	TempDB	User Database
Persistence	Non-persistent	Persistent
Recreated on Restart	Yes	No
Recovery Model	Always Simple	Simple / Full / Bulk Logged
Logging	Minimal	Full logging based on recovery model
Crash Recovery	No	Yes
Backup Allowed	No	Yes
Restore Allowed	No	Yes
Shared Across Server	Yes	No
Supports Snapshot Isolation	Stores version store	Uses own version store
Read Only?	No	No
Metadata Caching	Optimized	Normal
Multiple Files Needed	Recommended	Optional
Performance Critical	EXTREMELY	Depends

TempDB functions as the SQL Server instance's global scratchpad, optimized for speed and temporary data management, whereas user databases are designed for permanent, reliable data storage.  

Indicators in SSRS

SQL Server Reporting Services provides several ways to analyze the data. Indicators are minimal gauges that convey the state of a single data value at a glance and are mostly used to represent the state value of Key Performance Indicator (KPI, a measurable value which has business significance with a specific target or goal that indicates whether things are going good or bad). Indicators can be used in either dashboards or free-form reports but more commonly are used in tabular or matrix reports to visualize data in rows or columns. 

Step by step implement Indicators in SSRS.

Here I am using AdventureWorksDW2008R2 data base and want to implement indicator based on the number of product sales unit. My sql query is below

select P.EnglishProductName,Count(FIS.ProductKey)as [Number of sales Unit] from FactInternetSales FIS Inner join DimProduct P ON P.ProductKey=FIS.ProductKey Group by P.EnglishProductName

Now open reporting service project.

Create new Report.

Create new data source.

Now create data set using the above sql query.

Click ok.

Now in report design insert a table having three columns as below.

In 3^rd Column we need to insert Indicator.

Click ok. You will get the following window

According to you requirements you can select the indicators. Click ok.

Select Indicator properties.

You will get below screen.

In general tab you can write the name of the Indicator. If you want to write the tool tip you can.

Now select Value and States Tab

A.     Select the value for the indictor.

B.    Select the Measurement unit either % or Numeric.

C.    Set the Icon, Color Minimum values and maximum value for the Icon

If you want to add more icon click on the add

In place of icon you can put your image for that you need to click on the Image button and browse the image.

Similarly for removing the icon you need to click delete button.

If you want to perform any action select Action tab.

D.   Click ok.

Now you can review the report you will get the desirer report.

 

Gauges Report in SSRS

The Gauge data region is a one-dimensional data region that displays a single value in your dataset. An individual gauge is always positioned inside a gauge panel, where we can add child or adjacent gauges. We can use the gauge panel to create multiple gauges inside a single gauge panel that share common functions such as filtering, grouping, or sorting.

We can use gauges to perform many tasks in a report:

Ø  Display key performance indicators (KPIs) in a single radial or linear gauge.

Ø  Place a gauge inside a table or matrix to illustrate values inside each cell.

Ø  Use multiple gauges in a single gauge panel to compare data between fields.

There are two types of gauges: radial and linear. The following illustration shows the basic elements of a single radial gauge in the gauge panel.

Step by Step creating Gauge report

Create data source connection

Create new data set. User this query. I am using AdventureWorksDW2008R2 database.

select p.EnglishProductName, Sum(SalesAmount)as [Total sales] from FactInternetSales f Inner join DimProduct p on P.ProductKey=f.ProductKey Group by  p.EnglishProductName

Report design we take table

In next column we are inserting Gauge.

Select the Gauge type. There are two type of Gauge

Ø  Redail

Ø  Linear

Here I am selecting Redial

Select Gauge data set Values.

Select the Gauge properties.

Write the Gauge name if you want to set the tool tip give the value for it.

Now select Gauge scale properties.

Here you can set the scale value and interval. You can also define the layout and action, Major and minor tick marks, borders etc.

Now view in browser. You will get this report.