June 2025 Discount: Comprehensive Database Performance Health Check | Testimonials

Home

SQL SERVER – 2005 – Mechanisms to Ensure Integrity and Consistency of Databases – Locking and Row Versioning

Pinal Dave
SQL, SQL Performance, SQL Server, SQL Tips and Tricks
Database, SQL Lock
Today I was going through Book On Line while researching something, I come across one interesting small article about two mechanisms to ensure integrity and consistency of databases – 1) Locking 2) Row Versioning Let us see their definition from Book Online Itself. Locking Each transaction requests locks of different…
Read More

SQL SERVER – 2005 – Analysis Services Query Performance Top 10 Best Practices

Pinal Dave
SQL, SQL Performance, SQL Server, SQL Tips and Tricks
Best Practices, Database, SQL Documentation
Analysis Services Query Performance Top 10 Best Practices Optimize cube and measure group design Define effective aggregations Use partitions Write efficient MDX Use the query engine cache efficiently Ensure flexible aggregations are available to answer queries. Tune memory usage Tune processor usage Scale up where possible Scale out when you…
Read More

SQL SERVER – 2005 – Find Nth Highest Record from Database Table – Using Ranking Function ROW_NUMBER

Pinal Dave
SQL, SQL Performance, SQL Server, SQL Tips and Tricks
MVP, SQL Function, SQL Joins, SQL Scripts
I have previously written SQL SERVER – 2005 – Find Nth Highest Record from Database Table where I have shown query to find 4th highest record from database table. Everytime when I write blog I am always very eager to read comments of readers. Some of regular readers are industry…
Read More

SQL SERVER – 2005 – A Simple Way To Defragment All Indexes In A Database That Is Fragmented Above A Declared Threshold

Pinal Dave
SQL, SQL Performance, SQL Server, SQL Tips and Tricks
SQL Error Messages, SQL Index, SQL Scripts, SQL Server DBCC
Just a day ago, I received email from regular reader Rajiv Kayasthy about a script which demonstrates the A Simple Way To Defragment All Indexes In A Database That Is Fragmented Above A Declared Threshold. He found this script on TechNet BOL and was attempting to run on SQL Server…
Read More

SQL SERVER – SELECT 1 vs SELECT * – An Interesting Observation

Pinal Dave
SQL Performance
SQL Scripts, SQL Server

Many times I have seen issue of SELECT 1 vs SELECT * discussed in terms of performance or readability while checking for existence of rows in table. I ran quick 4 tests about this observed that I am getting same result when used SELECT 1 and SELECT *. I think smart readers of this blog will come up the situation when SELECT 1 and SELECT * have different execution plan when used to find existence of rows.

Read More