June 2025 Discount: Comprehensive Database Performance Health Check | Testimonials

Home

SQL SERVER – 2005 Best Practices Analyzer Tutorial – Sample Example

Pinal Dave
SQL, SQL Performance, SQL Server, SQL Tips and Tricks
Best Practices, Database, SQL Download, SQL Scripts, SQL Utility
Yesterday I posted small note about SQL SERVER – 2005 Best Practices Analyzer (July BPA). I received many request about how BPA is used. Some of readers has asked me to provide sample tutorial which can help start using BPA. This utility has many uses for best practice. I have…
Read More

SQL SERVER – Comparison : Similarity and Difference #TempTable vs @TempVariable

Pinal Dave
SQL, SQL Performance, SQL Server, SQL Tips and Tricks
SQL Scripts, SQL TempDB
#TempTable and @TempVariable are different things with different scope. Their purpose is different but highly overlapping. TempTables are originated for the storage and & storage & manipulation of temporal data. TempVariables are originated (SQL Server 2000 and onwards only) for returning date-sets from table-valued functions. Common properties of #TempTable and…
Read More

SQL SERVER – 2005 Comparison SP_EXECUTESQL vs EXECUTE/EXEC

Pinal Dave
SQL, SQL Performance, SQL Server, SQL Tips and Tricks
SQL Cursor, SQL Download, SQL Scripts, SQL Stored Procedure
Common Properties of SP_EXECUTESQL and EXECUTE/EXEC The Transact-SQL statements in the sp_executesql or EXECUTE string are not compiled into an execution plan until sp_executesql or the EXECUTE statement are executed. The strings are not parsed or checked for errors until they are executed. The names referenced in the strings are…
Read More

SQL SERVER – Comparison Index Fragmentation, Index De-Fragmentation, Index Rebuild – SQL SERVER 2000 and SQL SERVER 2005

Pinal Dave
SQL, SQL Performance, SQL Server, SQL Tips and Tricks
DBA, SQL Index, SQL Scripts, SQL Server DBCC, SQL Server Security, SQL Utility
Index Fragmentation: When a page of data fills to 100 percent and more data must be added to it, a page split occurs. To make room for the new data, SQL Server must move half of the data from the full page to a new page. The new page that…
Read More

SQL SERVER – De-fragmentation of Database at Operating System to Improve Performance

Pinal Dave
SQL, SQL Performance, SQL Server, SQL Tips and Tricks
Computer Network, DBA, SQL Index, SQL Scripts
This issues was brought to me by our Sr. Network Engineer. While running operating system level de-fragmentation using either windows de-fragmentation or third party tool it always skip all the MDF file and never de-fragment them. He was wondering why this happens all the time. The reason MDF file are…
Read More