European Windows 2019 Hosting BLOG

BLOG about Windows 2019 Hosting and SQL 2019 Hosting - Dedicated to European Windows Hosting Customer

European SQL Server 2022 Hosting :: Understanding SQL Server Index Fragmentation and How to Address It

clock August 21, 2025 08:50 by author Peter

Index fragmentation is a word that many developers and DBAs come across, and some have suffered with its effects while creating and managing databases over the course of an application's lifecycle.

This article will walk you through the process of understanding:

  • What is fragmentation of an index?
  • When and why it occurs
  • The many kinds (external versus internal)
  • How to prevent and correct it
1. Quick Recap: What is an Index?
An index is a data structure that helps locate specific rows quickly. It works by storing sorted values from one or more columns of a table, along with pointers to the corresponding rows.

In SQL Server, data is stored in pages, fixed-size blocks (typically 8KB each). When the database needs to read data, it loads an entire page into memory.

This is more efficient than fetching single rows, as it reduces disk I/O.

When a query is executed:
  • SQL Server checks if an index exists for the queried columns.
  • If it exists, SQL Server uses it to locate the relevant pages.
  • Only those specific pages are read, instead of scanning the whole table.

Query executed
2. What is Index Fragmentation?

Index fragmentation occurs when the logical order of data in an index does not match the physical storage order.
  • Logical Order: An index is designed to provide a quick lookup for data based on specific key values. Ideally, the data within the index pages should be stored in a contiguous, ordered manner based on these key values.
  • Physical Order: How pages are actually stored on disk.
When these orders are out of sync, SQL Server has to jump around more, increasing I/O and slowing queries.

Example scenario
  • You insert new rows into a table with a clustered index.
  • SQL Server tries to keep them in order by key.
  • If the target page is full, SQL Server splits the page into two, moving some rows to the new page.
  • Now the physical page order may no longer match the logical order.

Inseration
Internal vs External Fragmentation

There are two main types of fragmentation:

Internal Fragmentation
  • Occurs inside pages.
  • Pages are only partially filled, leaving empty space.
  • Often happens after deletes or updates that shrink data.
  • Leads to wasted space and more pages to scan.
External Fragmentation
  • Occurs when the order of pages no longer matches their logical sequence.
  • Caused by page splits that scatter data across the file.
  • Leads to more random I/O.
    Logical Order: Page 100 → Page 101 → Page 102
    Physical Order: Page 100 → Page 300 → Page 101 → Page 500 → Page 102
Why Does It Matter?
Fragmentation increases
  • I/O operations (more pages read)
  • CPU usage (more pointer lookups)
  • Query execution time
Especially in large datasets, even small increases in page reads can cause significant slowdowns.

4. How to Fix Fragmentation
REORGANIZE: Defragments the index by physically reordering leaf pages. Lightweight, online.
ALTER INDEX IndexName ON TableName REORGANIZE;

REBUILD: Drops and recreates the index. Removes all fragmentation. Can be offline or online (Enterprise Edition).
ALTER INDEX IndexName ON TableName REORGANIZE;

Important Note
Microsoft recommends:
<5% fragmentation → No action needed.
5–30% fragmentation → REORGANIZE.
>30% fragmentation → REBUILD.


By following these guidelines, you can keep your SQL Server indexes healthy and your queries fast.

HostForLIFEASP.NET SQL Server 2022 Hosting


 

 



European SQL Server 2022 Hosting :: SQL Cheatsheet: A Comprehensive and Easy-to-Use Guide

clock August 8, 2025 08:27 by author Peter

Relational databases are accessed using SQL (Structured Query Language). It facilitates data storage, retrieval, updating, and deletion. Almost every business uses SQL, including banking systems and internet. The ability to use SQL is essential if you wish to work with data. For the most crucial SQL topics, this cheat sheet will provide you with concise and understandable definitions, code examples, and essential points.

1. SELECT Statement
Definition: Used to fetch data from a table.

Example
SELECT name, age FROM Students;

Key Point: Use * to select all columns.

2. WHERE Clause
Definition: Filters records based on a condition.

Example
SELECT * FROM Students WHERE age > 18;

Key Point: Works with operators like =, >, <, BETWEEN, IN, LIKE.

3. ORDER BY
Definition: Sorts the result in ascending or descending order.

Example
SELECT * FROM Students ORDER BY age DESC;

Key Point: Default sort is ascending (ASC).

4. INSERT INTO
Definition: Adds new records to a table.

Example
INSERT INTO Students (name, age) VALUES ('John', 20);

Key Point: Match the column order with the values.

5. UPDATE
Definition: Changes existing records.

Example
UPDATE Students SET age = 21 WHERE name = 'John';


Key Point: Always use WHERE to avoid updating all rows.

6. DELETE
Definition: Removes data from a table.

Example
DELETE FROM Students WHERE name = 'John';


Key Point: Without WHERE, all rows will be deleted.

7. CREATE TABLE
Definition: Creates a new table.
Example
CREATE TABLE Students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);


Key Point: Define data types clearly.

8. ALTER TABLE
Definition: Changes the structure of a table.
Examples
    Add column:
    ALTER TABLE Students ADD email VARCHAR(100);

Drop column:
ALTER TABLE Students DROP COLUMN email;

9. DROP TABLE
Definition: Deletes the table and its data.

Example
DROP TABLE Students;

Key Point: This action cannot be undone.

10. JOINs
Definition: Combines rows from two or more tables.

Example (INNER JOIN)
SELECT Students.name, Marks.score
FROM Students
INNER JOIN Marks ON Students.id = Marks.student_id;

Types

    INNER JOIN: Only matching records.
    LEFT JOIN: All records from left + matches.
    RIGHT JOIN: All from right + matches.
    FULL JOIN: All records from both.

11. GROUP BY
Definition: Groups rows based on a column and applies an aggregate function.

Example:
SELECT age, COUNT(*) FROM Students GROUP BY age;

Key Point: Always use with aggregate functions.

12. HAVING
Definition: Filters groups created by GROUP BY.

Example:
SELECT age, COUNT(*)
FROM Students
GROUP BY age
HAVING COUNT(*) > 1;


Key Point: Use HAVING after GROUP BY.

13. Aggregate Functions
Definition: Perform calculations on multiple values.
Common ones: COUNT(), SUM(), AVG(), MIN(), MAX()

Example
SELECT AVG(age) FROM Students;

14. DISTINCT

Definition: Removes duplicate rows.

Example
SELECT DISTINCT age FROM Students;

15. BETWEEN, IN, LIKE
BETWEEN

SELECT * FROM Students WHERE age BETWEEN 18 AND 25;

IN
SELECT * FROM Students WHERE age IN (18, 21, 23);

LIKE
SELECT * FROM Students WHERE name LIKE 'A%';

16. LIMIT / TOP
Definition: Restricts number of rows returned.
SELECT * FROM Students LIMIT 5;

SELECT TOP 5 * FROM Students;

17. UNION
Definition: Combines results of two SELECT statements.

Example
SELECT name FROM Students
UNION
SELECT name FROM Teachers;


Key Point: Both queries must have the same number of columns.

18. Subquery
Definition: A query inside another query.

Example
SELECT name FROM Students
WHERE age = (SELECT MAX(age) FROM Students);


19. Views
Definition: A virtual table based on the result of a query.

Example
CREATE VIEW Teenagers AS
SELECT * FROM Students WHERE age BETWEEN 13 AND 19;

20. Indexes
Definition: Improve search performance on columns.

Example
CREATE INDEX idx_name ON Students(name);

Key Point: Indexes speed up SELECT but may slow down INSERT/UPDATE.

21. Constraints
Definition: Rules applied on columns.

Types
    PRIMARY KEY: Unique and not null.
    FOREIGN KEY: Links to another table’s primary key.
    UNIQUE: Ensures all values are different.
    NOT NULL: Prevents null values.
    CHECK: Validates a condition.


Example
CREATE TABLE Students (
  id INT PRIMARY KEY,
  age INT CHECK (age >= 0)
);

22. DEFAULT Constraint
Definition: Sets a default value for a column if none is provided.

Example
CREATE TABLE Students (
  id INT,
  name VARCHAR(50),
  status VARCHAR(10) DEFAULT 'active'
);

Key Point: Helps avoid NULL when no value is provided.

23. CASE Statement
Definition: Adds conditional logic inside a query.

Example
SELECT name,
       CASE
         WHEN age >= 18 THEN 'Adult'
         ELSE 'Minor'
       END AS age_group
FROM Students;


Key Point: Works like if-else in SQL.

24. EXISTS

Definition: Checks whether a subquery returns any row.

Example
SELECT name FROM Students s
WHERE EXISTS (
  SELECT 1 FROM Marks m WHERE m.student_id = s.id
);


Key Point: Faster than IN in many cases for large datasets.

25. NOT IN vs NOT EXISTS
Caution: NOT IN fails if any NULL exists in subquery. Use NOT EXISTS for safer logic.

Example
SELECT name FROM Students
WHERE NOT EXISTS (
  SELECT 1 FROM Graduates WHERE Students.id = Graduates.id
);


26. COALESCE and ISNULL
Definition: Replace NULL with a default value.

Example
SELECT name, COALESCE(email, 'Not Provided') FROM Students;

Key Point: COALESCE returns the first non-null value from a list.

27. Window Functions
Definition: Performs calculation across a set of rows related to the current row.
Common ones: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()

Example
SELECT name, age,
       RANK() OVER (ORDER BY age DESC) AS age_rank
FROM Students;

Key Point: Unlike aggregate functions, window functions do not collapse rows.

28. Common Table Expressions (CTE)
Definition: A temporary named result set used in a query.

Example
WITH Teenagers AS (
  SELECT * FROM Students WHERE age BETWEEN 13 AND 19
)
SELECT * FROM Teenagers;


Key Point: Makes complex queries cleaner and readable.

29. Stored Procedures
Definition: A saved group of SQL statements that can be executed repeatedly.

Example
CREATE PROCEDURE GetAllStudents
AS
BEGIN
  SELECT * FROM Students;
END;


Execution
EXEC GetAllStudents;

30. Functions (User-Defined Functions)
Definition: Return a value based on input parameters.

Example
CREATE FUNCTION GetFullName (@first VARCHAR(50), @last VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
  RETURN (@first + ' ' + @last);
END;

31. Transactions

Definition: A set of SQL operations that execute together or not at all.

Example
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 100 WHERE id = 1;
UPDATE Accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;


Key Point: Use ROLLBACK to undo changes if something fails.

32. ACID Properties
Definition: Ensures reliable processing of transactions.

  • Atomicity: All or nothing
  • Consistency: Valid state before/after
  • Isolation: Transactions do not affect each other
  • Durability: Changes are permanent

33. Normalization

  • Definition: Organizing data to reduce redundancy.
  • Common Forms: 1NF, 2NF, 3NF
  • Key Point: Normalize to avoid data anomalies. Denormalize only when performance requires it.

34. Foreign Key
Definition: A column that links to another table’s primary key.

Example

CREATE TABLE Marks (
  id INT PRIMARY KEY,
  student_id INT,
  FOREIGN KEY (student_id) REFERENCES Students(id)
);


35. Triggers

Definition: SQL code that runs automatically on data change (INSERT, UPDATE, DELETE).

Example
CREATE TRIGGER LogDelete
AFTER DELETE ON Students
FOR EACH ROW
BEGIN
  INSERT INTO DeletedRecords (name) VALUES (OLD.name);
END;


36. SET Operators (UNION ALL, INTERSECT, EXCEPT)
UNION ALL: Includes duplicates.
INTERSECT: Returns common rows between two queries.
EXCEPT: Returns rows in first query but not in second.


Example
SELECT name FROM Students
INTERSECT
SELECT name FROM Teachers;


37. Backup and Restore
Backup Example (SQL Server)
BACKUP DATABASE School TO DISK = 'D:\backup\school.bak';


Restore Example
RESTORE DATABASE School FROM DISK = 'D:\backup\school.bak';

38. TEMPORARY Tables
Definition: Temporary tables exist only during the session or transaction.

Example (MySQL)
CREATE TEMPORARY TABLE TempStudents (
  id INT,
  name VARCHAR(50)
);

Key Point: Good for storing intermediate results. Automatically dropped at session end.

39. TABLE Variables (SQL Server)
Definition: A variable to temporarily hold table data.

Example
DECLARE @Temp TABLE (id INT, name VARCHAR(50));
INSERT INTO @Temp VALUES (1, 'John');

40. MERGE Statement (UPSERT)
Definition: Performs INSERT, UPDATE, or DELETE in one statement based on a match.

Example
MERGE INTO Students AS target
USING (SELECT 1 AS id, 'John' AS name) AS source
ON target.id = source.id
WHEN MATCHED THEN
  UPDATE SET name = source.name
WHEN NOT MATCHED THEN
  INSERT (id, name) VALUES (source.id, source.name);


Key Point: Use cautiously. Complex logic can lead to bugs if not tested well.

41. Recursive CTE
Definition: A CTE that refers to itself. Useful for hierarchical data (e.g., org charts, folder trees).

Example
WITH OrgChart AS (
  SELECT id, manager_id, name FROM Employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.manager_id, e.name
  FROM Employees e
  JOIN OrgChart o ON e.manager_id = o.id
)
SELECT * FROM OrgChart;


42. JSON Data Handling
Definition: Querying and storing JSON in SQL columns (supported in PostgreSQL, MySQL, SQL Server).

Example (PostgreSQL)
SELECT data->>'name' AS name FROM users WHERE data->>'age' = '25';

Key Point: Useful when data structure is flexible or semi-structured.

43. PIVOT and UNPIVOT

Definition
    PIVOT: Converts rows to columns.
    UNPIVOT: Converts columns to rows.

Example (SQL Server):
SELECT * FROM
(SELECT subject, score FROM Marks) AS SourceTable
PIVOT (
  MAX(score) FOR subject IN ([Math], [Science])
) AS PivotTable;

Key Point: Makes reporting easier. Avoid overuse in core logic.

44. ROLLUP and CUBE
Definition: Extensions to GROUP BY for creating subtotals and grand totals.

ROLLUP
SELECT department, role, COUNT(*)
FROM Employees
GROUP BY ROLLUP (department, role);

CUBE
GROUP BY CUBE (department, role);

Key Point: Saves time when generating hierarchical reports.

45. WITH TIES
Definition: Returns additional rows that match the last value in an ORDER BY ... TOP query.

Example:
SELECT TOP 3 WITH TIES name, score
FROM Marks
ORDER BY score DESC;

Key Point: Useful when ranks are tied and you want to return all top scorers.

46. SEQUENCES
Definition: Auto-incrementing number generator independent of tables.

Example:
CREATE SEQUENCE student_seq START WITH 1 INCREMENT BY 1;
SELECT NEXT VALUE FOR student_seq;

Key Point: More flexible than IDENTITY in some databases.

47. IDENTITY vs SEQUENCE
IDENTITY: Tied to a table. Auto-increments per insert.
SEQUENCE: Independent object. Can be reused across tables or manually advanced.

48. LOCKING & ISOLATION LEVELS
Definition: Controls how transactions read/write shared data.

Levels

  • READ UNCOMMITTED: Dirty reads allowed
  • READ COMMITTED: Only committed data
  • REPEATABLE READ: Prevents non-repeatable reads
  • SERIALIZABLE: Strictest, full isolation

Key Point: Choose based on consistency vs performance trade-off.

49. DEADLOCK

  • Definition: Two or more sessions wait forever for resources locked by each other.
  • Fix: Reduce transaction size, always lock objects in the same order, use proper isolation levels.

50. EXECUTION PLAN

  • Definition: Visual or textual explanation of how SQL Server/MySQL/PostgreSQL will execute the query.
  • Key Point: Use EXPLAIN, SHOW PLAN, or right-click → “Display Estimated Execution Plan” in SQL Server Management Studio.

51. ANALYZE / UPDATE STATISTICS
Definition: Refreshes metadata so the query planner makes better decisions.

Example (PostgreSQL):
ANALYZE Students;

SQL Server:
UPDATE STATISTICS Students;

52. PARTITIONING

  • Definition: Divides large tables into smaller, more manageable parts (partitions).
  • Key Point: Boosts performance on very large tables. Query optimizer uses partition elimination.

53. Sharding

  • Definition: Horizontal partitioning across databases or servers.
  • Key Point: Needed for very high scale. Not supported by default in many RDBMS — requires custom implementation or external tools.

54. Temporal Tables (System-Versioned Tables)
Definition: Track historical changes automatically.

Example (SQL Server 2016+):
CREATE TABLE StudentsHistory (
  id INT,
  name VARCHAR(50),
  VALID_FROM DATETIME2 GENERATED ALWAYS AS ROW START,
  VALID_TO DATETIME2 GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (VALID_FROM, VALID_TO)
) WITH (SYSTEM_VERSIONING = ON);

HostForLIFEASP.NET SQL Server 2022 Hosting

 



European SQL Server 2022 Hosting :: Track Performance with the Query Store

clock August 5, 2025 08:54 by author Peter

The Query Store: What is it?
With SQL Server 2016, a performance monitoring tool called the Query Store was added. It records query execution history, query plans, and performance metrics over time, much like a black box recorder. Because Query Store retains historical data, it is simpler to troubleshoot performance issues that have already occurred, in contrast to standard DMVs that only provide live statistics.

Why Use Query Store?

  • Monitor how query performance changes over time.
  • Detect and troubleshoot query regressions (when performance drops).
  • Compare performance before and after deployments.
  • Identify and force optimal plans for queries.
  • Track resource usage (CPU, IO, duration) per query.

How to Enable Query Store?
Option 1. T-SQL

ALTER DATABASE [YourDatabaseName]
SET QUERY_STORE = ON;

Option 2. SSMS GUI

  • Right-click the database → Properties
  • Navigate to the Query Store page
  • Set Operation Mode (Requested) to Read Write
  • Adjust other settings like capture mode and retention as needed


What Data does Query Store Capture?

Category Description
Query Text The full T-SQL text of the executed query
Query Plan The execution plan used to execute the query
Runtime Stats CPU time, duration, reads/writes, executions, etc.
Wait Stats (SQL 2017+) Time spent waiting on resources like locks or memory
Plan Forcing Force SQL Server to always use a specific plan

Internal Views for Query Store Analysis

  • sys.query_store_query: Stores each normalized query
  • sys.query_store_plan: Stores query execution plans
  • sys.query_store_runtime_stats: Stores execution metrics
  • sys.query_store_wait_stats: Captures wait types per query (SQL 2017+)

Built-in SSMS Reports
SSMS provides graphical reports for easier analysis.

  • Right-click the database
  • Select Reports → Standard Reports → Query Store
  • Explore reports like.
    • Top Resource Consuming Queries
    • Tracked Queries
    • Query Plan Changes
    • Regressed Queries

Plan Forcing Example
If SQL Server chooses a suboptimal plan, you can force a better one.

Steps to Force a Plan via T-SQL

EXEC sp_query_store_force_plan
    @query_id = 102,
    @plan_id = 301;


To Unforce a Plan
EXEC sp_query_store_unforce_plan
    @query_id = 102,
    @plan_id = 301;


Note: Forced plans remain in use until manually unforced or if the plan becomes invalid due to schema changes.

How to Clean Query Store?
Remove Specific Plan or Query

EXEC sp_query_store_remove_plan @plan_id = 123;
EXEC sp_query_store_remove_query @query_id = 456;


Clear All Data from Query Store
ALTER DATABASE [YourDatabaseName]
SET QUERY_STORE CLEAR;

Query Store Modes

Mode Description
Off Query Store is disabled
Read Only Existing data is visible but not updated
Read Write Captures and stores new execution data

Limitations and Considerations

  • Slight overhead on heavily loaded systems (~1–5%)
  • Disk space usage can grow—monitor data size regularly
  • Not available in SQL Server 2014 or earlier
  • Retention settings control how long historical data is kept

Conclusion
The Query Store is a powerful feature for any SQL Server environment focused on performance, stability, and transparency. It simplifies identifying performance problems and helps maintain consistent performance by allowing query plan control. Use it proactively in development, staging, and production environments to avoid surprises and ensure optimal performance.

HostForLIFEASP.NET SQL Server 2022 Hosting

 



European SQL Server 2022 Hosting :: How to Create a Data Mart from the Ground Up: A Guide for BI Teams Focused on Data Warehouses and SQL

clock August 1, 2025 08:01 by author Peter

Specifically, we will leverage data warehouse ideas, SQL methodologies, and BI-focused schema architecture to design and implement a Data Mart in this article.

Step 1. Understand Business Requirements for the Data Warehouse

  • Engage business stakeholders to define reporting needs.
  • Identify key facts (e.g., total sales, loan balance) and dimensions (e.g., product, branch, customer).
  • Define the grain of the data mart (e.g., "daily branch sales").

Step 2. Design the Data Warehouse Schema (Dimensional Modeling)
Use a Star Schema or Snowflake Schema, optimized for SQL queries.

  • Fact Table (e.g., Fact_Sales): Contains numeric metrics and foreign keys
  • Dimension Tables (e.g., Dim_Customer, Dim_Product): Contain descriptive attributes to slice and filter data

Step 3. Source to Target Mapping (STM)

  • Identify source systems (e.g., OLTP DBs like Core Banking, ERP).
  • Document field-level mappings from source to warehouse tables.
  • Define SQL-based transformations (e.g., date format conversion, category lookup).

Step 4. Build the ETL in SQL or ELT with dbt
Using SQL or dbt (Data Build Tool).

  • Extract: Pull raw data from staging tables.
  • Transform: Use SQL CTEs or dbt models to clean and join.
  • Load: Insert transformed data into fact/dimension tables.

Tips

  • Use SCD Type 2 for dimensions
  • Maintain surrogate keys
  • Use indexes and partitions for performance

Step 5. Connect to BI Tools

  • Expose the SQL-based data mart to Power BI, Tableau, or Looker.
  • Use pre-aggregated views for performance.
  • Provide semantic consistency by using dimensional hierarchies.

Step 6. Testing and Optimization

  • Write SQL test cases for row counts, null checks, and data accuracy.
  • Schedule SQL scripts or dbt jobs for refresh.
  • Monitor performance with query logs or warehouse query history.

Conclusion
Building a Data Mart using SQL and data warehousing best practices is essential for scalable and high-performing BI. With the right design and automation in tools like dbt or SQL scripts, your organization can gain accurate, fast insights. In the next article, we’ll walk through building a Sales Data Mart in SQL + dbt with dimensional modeling and snapshotting logic.

HostForLIFEASP.NET SQL Server 2022 Hosting



About HostForLIFE

HostForLIFE is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2019 Hosting, ASP.NET 5 Hosting, ASP.NET MVC 6 Hosting and SQL 2019 Hosting.


Month List

Tag cloud

Sign in