Pinal Dave - SQL Server Performance Tuning Expert

SQL SERVER – SP Performance Improvement without changing T-SQL

April 10, 2007

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

There are two ways, which can be used to improve the performance of Stored Procedure (SP) without making T-SQL changes in SP. Do not prefix your Stored Procedure with sp_. In SQL Server, all system SPs are prefixed with sp_. When any SP is called which begins sp_ it is…

SQL SERVER – 2005 Reserved Keywords

April 9, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

Microsoft SQL Server 2005 uses reserved keywords for defining, manipulating, and accessing databases. Reserved keywords are part of the grammar of the Transact-SQL language that is used by SQL Server to parse and understand Transact-SQL statements and batches. It is not legal to include the reserved keywords in a Transact-SQL…

SQL SERVER – Search Text Field – CHARINDEX vs PATINDEX

April 8, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Function, SQL Scripts, SQL Server, SQL Server Security, SQL Utility

We can use either CHARINDEX or PATINDEX to search in TEXT field in SQL SERVER. The CHARINDEX and PATINDEX functions return the starting position of a pattern you specify. Both functions take two arguments. With PATINDEX, you must include percent signs before and after the pattern, unless you are looking…

SQL SERVER – DBCC Commands Introduced in SQL Server 2005

April 7, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Server DBCC

SQL Server 2005 has introduced following two documented and five undocumented DBCC Commands. I was able to find documentation for only first one online. If you find any documentation of any other DBCC Commands please add comments. It will be helpful to all of us. Documented: freesessioncache () — no…

SQL SERVER – Fix: Server: Msg 7391, Level 16, State 1, Line 1

April 6, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

DBA, SQL Download, SQL Error Messages, SQL Server DBCC, SQL Server Security

I have received this error many times on different servers in my careers. There is no single fix for this Error. Server: Msg 7391, Level 16, State 1, Line 1 can happen due to many reasons. I have used various of this reasons with few of my servers. Please refer…

SQL SERVER – Performance Optimization of SQL Query and FileGroups

April 5, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

Best Practices, SQL Download, SQL Index

It is suggested to place transaction logs on separate physical hard drives. In this manner, data can be recovered up to the second in the event of a media failure. In SQL 2005 When database is created without specifying a transaction log size, the transaction log will be re-sized to…

SQL SERVER – Fix: HResult 0x274D, SQLCMD Level 16, State 1 Error: Microsoft SQL Native Client : Login timeout expired

April 4, 2007

Pinal Dave

SQL

SQL Error Messages, SQL Server, SQL Server Security, sqlcmd

While Working with SQLCMD in SQL Server 2005 I encountered following error. Let us learn in this blog post how we can solve Fix: HResult 0x274D, Level 16, State 1 Error: Microsoft SQL Native Client : Login timeout expired.