Home » Performance Tuning

Performance Tuning

sql server DBA 999

How to Resolve SQL Server Error 5172 – Fix SQL Server Header Error 5172

How to Resolve SQL Server Error 5172 – Fix SQL Server Header Error 5172

SQL Server stores its physical data in the primary database file, which contains the data by pages which store data. The header information of a .mdf file is stored by the first page, called a header page. This page contains the important information of this database file like file size, signature etc…. When a user attempts to attach the MDF / LDF database file stored on their system, a message box appears “The header for the “databasename.mdf” is not a valid database file header. The FILE SIZE property is incorrect. (SQL Server error 5172). It occurs when the header information of a .mdf file becomes corrupted.

Read the rest
sql server DBA 999

In Memory Optimized Tables SQL 2014

    In Memory Optimized Tables SQL 2014

What are Memory Optimized Tables in SQL Server?
Memory Optimized tables are newly introduced in SQL Server and they are fully ACID compliant data storage structures, which stores the data mainly within the system memory(RAM), allowing users to access data very very quickly compared to the stored File system stored Table.

The transactional consistency is maintained in Memory Optimized Tables in SQL Server 2014 by:

In-Memory Row Versioning:
Memory Optimized tables maintains various versions of the same row dispersed over the in-memory data structure of the table which allows the reading and writing of the rows concurrently without transactional inconsistency issues. It is equivalent to SNAPSHOT ISOLATION LEVEL where … Read the rest

sql server DBA 999

CTE vs Temp Table vs Temp variables

Temp tables: they are just like any normal tables except that they are created in the TempDB database. They persist until dropped, or until the connection that created them disappears. They are visible in the procedure that created them and any procedures that it calls. Just like normal tables and have primary keys, constraints, indexes and column statistics which are kept for the table which makes query execution really fast in most cases.

Temp tables have space assigned to them in the TempDB database and are generally accessed only from memory, unless the server is under memory pressure or the amount of data in the table is large.

Table Variables: These tables behave very much like other variables in their … Read the rest

sql server DBA 999

Temporary Variables vs Temporary Tables SQL Server 2008/2005

Myth: A table variable is a memory-only structure.
This is not True. A Temp table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables.

Note: If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).

Normally whether to go with Temp Variables or Temp Tables depends on:
•The number of rows that are inserted to the table.
•The number of recompilations the query is saved from.
•The type of queries and their dependency on indexes and statistics for performance.
Some other important advantages of temporary tables:
•Non-clustered … Read the rest

sql server DBA 999

Parameter Sniffing in Stored Procedures in SQL Server 2008/2008 R2

Parameter Sniffing in Stored Procedures in SQL Server 2005/2008/2008 R2
Today let me cover one of a good topic on Parameter Sniffing in Stored Procedures:

Paramater Sniffing is not a unexpected behaviour when passing parameters into Store Procedure.


CREATE procedure [dbo].[Usp_Test_SP]
@Category as [nvarchar](60)=null
as
select Category ,Description,WorkOrder,Date Added from TestTable
where (sc.session_class_desc like @Category +'%' or @Category is null)

Say about SP causes a index seek + bookmark lookup then this compiled plan will be cached and reused for subsequent executions of the procedure.

But when sometime if rows returned are very high like 30% of Table rows then compiled plan may result in Full table scan and this is stored for future use.

and thus some executions will … Read the rest

sql server DBA 999

Adding Data Collectors on SQL Server 2008/2008 R2/2012/2014/2016

Adding SQL Server Data Collectors to SQL Server is very important for Troublshooting issues and Root cause analysis of many issues normally occuring on SQL Servers.

These counters are helpful for both Virtual or Physical SQL Servers. They are available in all versions like sql server 2008 data collector, sql server 2012 data collector, sql server 2014 data collector

    The Counters related to SQL Instance which are really important are:


SQLServer:Access MethodsFreeSpace Scans/sec
SQLServer:Access MethodsFull Scans/sec
SQLServer:Access MethodsIndex Searches/sec
SQLServer:Access MethodsPages Allocated/sec
SQLServer:Access MethodsWorkfiles Created/sec
SQLServer:Access MethodsWorktables Created/sec
SQLServer:Buffer ManagerBuffer cache hit ratio
SQLServer:Buffer ManagerCheckpoint pages/sec
SQLServer:Buffer ManagerDatabase pages
SQLServer:Buffer ManagerFree list stalls/sec
SQLServer:Buffer ManagerFree pages
SQLServer:Buffer ManagerLazy writes/sec
SQLServer:Buffer ManagerPage life expectancy
SQLServer:Buffer ManagerReadahead pages/sec
SQLServer:Buffer ManagerReserved pages
SQLServer:Buffer
Read the rest

sql server DBA 999

What is SQL Server Health Check? Why is it important?

What is SQL Server Health Check? Why is it important?

A SQL Server health check looks at all aspects of your SQL Server environment in terms of best practices in the areas of Performance, Configuration, Security, Disaster Recovery and the ability of your environment to scale to projected future loads.

With a SQL Server Health Check, one should make a report of our findings and recomendation/solutions.

Fixing the Problems
You may choose to have us fix some or all of the problems found by the SQL Server health check or
you may prefer to have your own team to discuss and work through them.

What are the important aspects of SQL Server Health Check?
SQL Server Disaster Recovery Plan:
1.Design … Read the rest

sql server DBA 999

Memory configuration and using AWE /3GB /USERVA in SQL Servers 2005/2008 in 64 bit and 32 bit installations

In this post we will discuss various memory configurations in 64 bit and 32 bit installations of SQL Server:

In 32 bit OS systems, AWE was used for addressing the issue to access the memory above the 32 bit process limitation. It also enables SQL Server to lock pages in memory.
Servers that are x86 or 32 bit have a VAS region that is 2^32 in size or roughly speaking 4 GB. This is by default split 50/50 into kernel mode and user mode VAS regions. To work around this limitation, the /3GB and /USERVA boot.ini switches can be used to change the user/kernel mode proportions from 50/50 to 75/25 (/3GB) or a user defined proportion (/USERVA) to allow SQL … Read the rest

sql server DBA 999

SQL Server 2008/2005 – Comparing Index rebuild/Reorganize and increase in Transaction Log file

Comparing ALTER INDEX options Index Rebuild and Reorganize:

1.Index Rebuild requires building the new index before dropping the old one. This means there has to be enough free space in the database to accommodate the new index; otherwise the database will grow to provide the required free space. This can be problematic for large indexes. Index Reorganize only requires 8KB of additional space in the database.
2.Index Rebuild can use multiple CPUs so the operation runs faster. Index Reorganize is always single-threaded.
3.Index Rebuild may require long-term locks on the table that can limit concurrent operations. Index REORGANIZE doesn’t hold blocking locks and it’s always been an online operation.
4.Index Rebuild can use minimal-logging to reduce transaction log growth. Index … Read the rest

sql server DBA 999

SQL Server 2008 Indexes: Types, Performance gain, Lookup and more…

Indexes are secret for faster query execution when designed properly.
Types of indexes in SQL Server :

1. Clustered Indexes: A clustered index determines the physical order of data in a table. A clustered index is analogous to a telephone directory, which arranges data by last name.
2.Non-Clustered Indexes: Nonclustered indexes have the same B-tree structure as clustered indexes, except for the following significant differences:
• The data rows of the underlying table are not sorted and stored in order based on their nonclustered keys.
• The leaf layer of a nonclustered index is made up of index pages instead of data pages.

Nonclustered indexes can be defined on a table or view with a clustered index or a heap. … Read the rest