SQL CODE BANK

Changes to SQL Server 2014 AlwaysOn Availability Groups

2015-09-09T22:05:00.000+05:30

AlwaysOn Availability Groups provide high availability for multiple databases, and they can make use of multiple secondary replicas. Each secondary SQL Server replica has its own copy of the protected databases. AlwaysOn Availability Groups continuously synchronize transactions from the primary replica to each of the secondary replicas. This replication can be configured as synchronous or asynchronous to support local high availability or remote disaster recovery.
In addition, here are some major enhancements to AlwaysOn Availability Groups in SQL Server 2014 that you might want to be aware of:

Enhanced Availability for Read-Only Replicas

One of the advantages of AlwaysOn Availability Groups is that the secondary replicas can be used for reporting and backup. With SQL Server 2014, the availability of secondary replicas has been enhanced to allow read workloads to continue to run even in the case of lengthy network failures or the loss of quorum for the Windows Server Failover Cluster.

Increased Number of Replicas

Another important enhancement in SQL Server 2014 is the increased maximum number of secondaries. SQL Server 2012 supported a maximum of four secondary replicas. With SQL Server 2014, AlwaysOn Availability Groups now supports up to eight secondary replicas. The additional secondary replicas can be used to distribute read workloads and provide enhanced recoverability.

Integration with Windows Azure

SQL Server 2014 AlwaysOn Availability Groups use Windows Azure in a couple of different ways. On-premises SQL Server instances can use the new Windows Azure configuration options in the AlwaysOn Availability Group wizard to create one or more asynchronous secondary replicas on Windows Azure Infrastructure as a Service (IaaS) services. This enables you to manually fail over to a SQL Server instance running on Windows Azure. As with a typical secondary replica, Windows Azure replicas can be used to offload reporting workloads and to perform backups.
In addition, you can also use SQL Server 2014 AlwaysOn Availability Groups to provide high availability for SQL Server databases hosted in Windows Azure. SQL Server 2014 instances running on Windows Azure IaaS Services can configure a synchronous secondary replica in Azure for automatic failover in case of server or VM failure.

Enhanced Diagnostics

SQL Server 2014 AlwaysOn Availability Groups diagnostic and troubleshooting message display has been improved to offer more specific information. Additional columns have also been made more discoverable in the AlwaysOn Dashboard.

http://www.microsoft.com/en-us/server-cloud/products/sql-server/

SQL Server – Performance Top Queries by Total CPU Time

2013-12-26T12:11:00.002+05:30

select top 10 rank() over(order by total_worker_time desc,sql_handle,statement_start_offset) as row_no
,       (rank() over(order by total_worker_time desc,sql_handle,statement_start_offset))%2 as l1
,       creation_time
,       last_execution_time
,       (total_worker_time+0.0)/1000 as total_worker_time
,       (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]
,       total_logical_reads as [LogicalReads]
,       total_logical_writes as [logicalWrites]
,       execution_count
,       total_logical_reads+total_logical_writes as [AggIO]
,       (total_logical_reads+total_logical_writes)/(execution_count + 0.0) as [AvgIO]
,   case when sql_handle IS NULL
then ''
else ( substring(st.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1        then len(convert(nvarchar(MAX),st.text))*2      else qs.statement_end_offset    end - qs.statement_start_offset) /2 ) )
end as query_text
,       db_name(st.dbid) as database_name
,       st.objectid as object_id
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
where total_worker_time > 0
order by total_worker_time desc

List current SQL Server tempdb statements

2013-12-26T12:06:00.001+05:30

SELECT ssu.session_id,
(ssu.internal_objects_alloc_page_count + sess_alloc) as allocated,
(ssu.internal_objects_dealloc_page_count + sess_dealloc) as deallocated
, stm.TEXT
from sys.dm_db_session_space_usage as ssu,
sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS stm ,
(select session_id,
sum(internal_objects_alloc_page_count) as sess_alloc,
sum (internal_objects_dealloc_page_count) as sess_dealloc
from sys.dm_db_task_space_usage group by session_id) as tsk
where ssu.session_id = tsk.session_id
and ssu.session_id >50
and ssu.session_id = req.session_id
and ssu.database_id = 2
order by allocated DESC

How to check for SQL ghost records

2013-12-26T11:52:00.000+05:30

Use the sys.dm_db_index_physical_stats view and check the ghost_record_count column.
The Ghost record count is the row count in the index leaf level marked for deletion, but not yet deleted by the database engine

Ghost_record_count is the ghost record count awaiting removal by the ghost cleanup task in the allocation unit.

Version_Ghost_record_count is the ghost records count sustained by a snapshot isolation transaction in an allocation unit.

SELECT
db_name(database_id),
object_name(object_id),
ghost_record_count,
version_ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(N'db_name'), OBJECT_ID(N'table_name'), NULL, NULL , 'DETAILED');

Calculate the Size of Index

2013-12-26T11:49:00.000+05:30

SELECT ix.[name] AS IndexName,
SUM(ps.[used_page_count]) * 8 AS IndexSize_KB
FROM sys.dm_db_partition_stats AS ps
JOIN sys.indexes AS ix
ON ps.[object_id] = ix.[object_id]
AND ps.[index_id] = ix.[index_id]
GROUP BY ix.[name]

SQLOS's memory manager and SQL Server's Buffer Pool

2013-09-20T22:42:00.002+05:30

SQLOS's memory manager consists of several components such as memory nodes, memory clerks, memory caches, and memory objects. Fig 1. depicts memory manager components and their relationship:

----------------

| Memory Object |

----------------

|
|
V
----------------

| Page Allocator |

----------------

/ \

\/ \/

--------------- ---------------

| Memory Clerk | | Caches |

--------------- ---------------

\ /

\/ \/

----------------

| Memory Node |

----------------

Fig. 1

Memory Node

A memory node is not exposed to memory manager clients. It is internal SQLOS's object. The major goal for a memory node is to provide locality of allocation. It consists of several memory allocators. There are three major types of allocators. The first type is a set of page allocators. The second type is virtual allocator leveraging Windows VirtualAlloc APIs. The third type is a shared memory allocator which is fully based on Window's file mapping APIs.

The page allocators are the most commonly used allocators in SQLOS memory manager. The reason they are called page allocators is because they allocate memory in multiple of SQLOS's page. A size of a page is 8k, the same as a size of database page in SQL Server. As you will learn further this is not coincidence.

There are four different types of page allocators. Single page allocator, multi page allocator, large page allocator and reserved page allocator. Single page allocator can only provide one page at the time. Multiple page allocator, as you might guess, provides a set of pages at the time. Large page allocator could be used to allocate large pages. SQLOS and SQL Server use large pages to minimize TLB misses when accessing hot data structures. Currently large pages are supported only on IA64 or x64 hardware with at least 8GB of RAM. A size of a large page on IA64 is 16MB. The last type of page allocators reserved could be used to allocate special set of pages reserved for emergency, i.e. when SQLOS is low on memory. Fig2. shows the memory node and its allocators.

---------------------- ---------------- ---------------------------
| Large Page Allocator |<-------- memory="" node="">| Reserved Page Allocator |
--------------------- / ---------------- \ ---------------------------
/ | \

/ | \

\/ \/ \/

At this point SQL Server doesn't have a dmv, dynamic management view, that would dump a set of all memory nodes and information about their allocators. Dbcc memorystatus, discussed further, comes pretty close but it dumps information about cpu nodes not about memory nodes. You might remember that CPU nodes are proper subset of memory nodes. It means that information presented by dbcc memorystatus is sufficient to understand memory distribution on the system.

Memory Clerks

Memory nodes are hidden from memory manager users. If a client of memory manager needs to allocate memory it first creates a memory clerk. There are four types of memory clerks such as generic, cache store, user store and object store. The latter three a bit convoluted. Along with memory clerk functionality they provide data caching.

One can think of a memory clerk as a bag of statistics. It supports the same type of allocators as memory nodes as well as it enables large memory consumers to hook into memory brokerage infrastructure. (I will describe infrastructure in one of the next posts). There are several global memory clerks provided by SQLOS. SQLOS's middle and large memory consumers are encouraged to use their own clerk so that one could understand memory consumption by a component. Memory clerks infrastructure enables us to track and control amount of memory consumed by a memory component. Each CPU node has a list of memory clerks that we can safely walk during runtime. SQL Server implements sys.dm_os_memory_clerks dmv to dump clerk information. In addition combined clerk information could be derived from dbcc memory status.

Memory objects

SQLOS's memory object is a heap. A memory object requires a memory clerk to allocate its memory. We support three types of memory objects. A variable memory objects is a regular heap. An incremental memory object is a mark/shrink heap. This allocation policy is very handy during compilations and execution processes. Usually both of the processes happen in two phases. First phase is to grow memory usage and the second is to shrink memory usage. If the process is isolated we don't have to call any of destructors when freeing memory. It significantly improves performance. The last type of memory object is fixed size. As you can guess components can use such policy when they need to allocate objects of a given size.

A payload for a given memory object is 8kb. It is exactly the same as a SQLOS's page size. It also means that a memory object could be created from memory clerk leveraging single page allocator. (This is yet another very important point! Keep this in mind until I will cover SQL Server's Buffer Pool) SQL Server exposes a dmv to dump all memory objects in its process: sys.dm_os_memory_objects.

If you notice both memory clerks and memory objects dmvs expose page allocator column. Also I depicted page allocator in Fig.1. Under the hood memory object uses memory clerks's page allocator interface to allocate pages. This is useful to know when you want to join memory clerk and memory object dmvs.

So far I have described how SQLOS's memory manager structured inside. Now it is time to start talking how all this fits into SQL Server.

Buffer Pool

Now we got to the point where the life becomes very interesting. In this part all the pieces that I covered so far including memory management should start fall in their places.

Remember SQL Server has two memory settings that you can control using sp_conifigure. They are max and min server memory. I am not sure if you know but these two setting really control the size of the buffer pool. They do not control overall amount of physical memory consumed by SQL Server. In reality we can't control amount of memory consumed by SQL Server because there could be external components loaded into server's process.

When SQL Server starts, during initialization, Buffer Pool first decides how much of VAS it needs to reserve for its usage. It bases its decision on the amount of physical memory, RAM, present on the box. If amount of physical memory is equal or larger than amount of VAS it can use, remember that VAS is limited resource especially on x86, it will leave 256MB of VAS for external components plus a number of threads SQL Server is configured to use multiplied by 512KB. You might remember that 512KB is SQL Server's thread stack size. In default configuration with physical memory larger than 2GB, Buffer Pool will leave 256MB+256*512KB = 384MB of VAS space. Some people name this region as MemToLeave but in reality it is in correct. SQL Server might end up using this part of VAS itself and I will show you how it could happen latter on. You might also remember -g parameter that some people recommend to use when SQL Server starts outputting "Can't Reserve Virtual Address Space" errors. First 256MB is exactly what -g parameter controls. If you specify -g 512MB, amount of VAS that BP won't use is 512MB+256*512KB = 640MB. There is no point in specifying -g 256MB. This input parameter is the same as default value.

Once BP decides amount of VAS it will use. It reserves all of it right a way. To observe such behavior you might want to monitor SQL Server's virtual bytes from perfmon or you could use vasummary view I talked about in my previous posts. In normal case Buffer Pool can't get this much memory in one chunk so if you take a closer look at SQL Server's VAS you will see several large regions reserved. This behavior is very different from many other servers that you might have seen. Some people report it as a VAS leak in SQL Server. In reality this behavior is by design.

Buffer Pool commits pages on demand. Depending on internal memory requirements and external memory state, it calculates its target, amount of memory it thinks it should commit before it can get into memory pressure. To keep system out of paging target is constantly recalculated. Target memory can't exceed max memory that represents max server memory settings. Even if you set min server memory equal to max server memory Buffer Pool will only commit its memory on demand. You can observe this behavior by monitoring corresponding profiler event.

The size of SQL Server database page is 8KB. Buffer Pool is a cache of data pages. Consequently Buffer Pool operates on pages of 8KB in size. It commits and decommits memory blocks of 8KB granularity only. If external components decide to borrow memory out of Buffer Pool they can only get blocks of 8KB in size. These blocks are not continues in memeory. Interesting, right? It means that Buffer Pool can be used as underneath memory manager forSQL Server components as long as they allocate buffers of 8KB. (Sometimes pages allocated from BP are referred as stolen)

Here is where SQLOS and Buffer Pool meet. See Fig.3

-----------------
| Memory Node |
-----------------
|
|
V
------------------------
| Single Page Allocator |
------------------------
|
|
V
-----------------
| Buffer Pool |
-----------------

Fig. 3

SQLOS' memory manager can be dynamically configured to use specific single page allocator. This is exactly what SQL Server does during a startup it configures Buffer Pool to be SQLOS's single page allocator. From that point on all dynamic single page allocations are provided by Buffer Pool. For example remember that memory object's payload is 8KB. When a component creates a memory object the allocation is served by SQLOS's single page allocator which is BP.

When describing the memory manager I mentioned that every large component has its own memory clerk. It means that Buffer Pool has its own memory clerk as well. How is it possible, BP leverages SQLOS memory clerk but SQLOS' memory manager relies on BP? This is common chicken and egg problem that you often can observe in operating systems. The key here is that Buffer Pool never uses any type of page allocator from SQLOS. It only leverages Virtual and AWE SQLOS's interfaces.

-----------------
| Buffer Pool |
-----------------
|
|
V
--------------------------
| Memory Clerk (VM/AWE) |
--------------------------
|
|
V
-----------------
| Memory Node |
-----------------

Fig. 4

All SQL Server's components optimized for 8KB allocations so that they can allocate memory through SQLOS's single page allocator and consequently through Buffer Pool. However there are cases when a component requires large buffers. If it happens allocation will be either satisfied by memory node's multi page allocator or by virtual allocator. As you might guess that memory will be allocated outside of Buffer Pool. This is exactly why I don’t like term MemToLeave, SQL Server does allocate memory out of that area!

Buffer Pool and AWE mechanism

When describing SQLOS memory manager and Buffer Pool, the discussion would be incomplete without describtion of how AWE fits in all of this. It is really important to understand how Buffer Pool allocates its memory when SQL Server configured to use AWE mechanisms. First, please remember, BP leverages SQLOS's memory clerk interfaces to allocate both VAS and physical pages through AWE. Second, there are several differences that you need to keep in mind. First BP reserves VAS in 4MB chunks instead of "single" large region. This enables SQL Server to release VAS when process is under VAS pressure. (We didn't have all bits and pieces to do this when server is not configured to use AWE mechanisms). Then it allocates all of its memory using AWE mechanism on demand. This is very big difference between SQL2000 and Yukon. In SQL Server 2000 BP would allocate all of its memory when using AWE mechanism right a way.

Buffer Pool is a preferred memory allocator for the whole server. In AWE mode it allocates its memory leveraging AWE mechanism. It means that all allocations allocated through SQLOS's single page allocator will come from pages allocated through AWE. This is what many people really missing. Let me make the point again: When Server is configured for AWE mode, most of it allocations are allocated through AWE mechanism. This is exactly the reason why you won't see private bytes and memory usage growing for SQL Server in this mode.

Since data pages are use relative addressing, i.e. self contained, Buffer Pool can map and unmap them into and out of process's VAS. Other components could have done the same if they were not relying on the actual allocation address. Unfortunately there are no components right now other than BP that can take advantage of AWE mechanism.

Optimising System Memory for SQL Server

2013-09-20T22:15:00.004+05:30

Virtual Memory and Memory Management
On the x86 family of processors, all processes are provided with 4Gb of virtual memory. By default, the first 2Gb is allocated to the operating system kernel and the latter 2Gb to the user process. This virtual memory isn’t real ‘physical’ memory – as processes make memory allocations, physical storage is provided, mixed between physical memory and the system paging file*. Windows transparently handles copying data to and from the paging file, so that the application can allocate more memory than physically exists in the machine and, so multiple applications can have equal access to the machine’s physical RAM.
* (this is how multiple applications can run on a system with 512Mb of RAM, each with a virtual address space of 4GB – it’s not real memory, but it seems like it to the application)

Tuning Virtual Memory
Windows NT 4.0 introduced the concept of the /3Gb switch (added to the [operating systems] section of the boot.ini file), which allows system administrators to modify the allocation of virtual memory between the OS and user processes. By adding the /3GB switch (and restarting), Windows will allocate just 1Gb to the kernel mode address space, allowing a process’s user mode address space to increase to 3Gb.
In addition to the /3GB switch, Windows XP, Windows Server 2003 & 2008 includes the /USERVA switch. This allows finer granularity over the amount of virtual memory provided, to the kernel mode and user mode address space. To use the /USERVA switch, simply indicate how much memory should be provided for user mode address space. For example, /USERVA=2560 configures 2.5GB for user mode space and leaves the remaining 1.5GB for the kernel. When using the /USERVA switch, the /3GB switch must also be present.
To add either the the /3GB or /USERVA switches, go to System Properties -> Startup and Recovery and click Edit under System Startup. Once reconfigured, you’re [operating systems] section should look something like this:

[operating systems] multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /noexecute=optout /fastdetect /3GB
[operating systems] multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /noexecute=optout /fastdetect /3GB /USERVA=2560

More information on the /3GB and /USERVA switches can be found in the Microsoft KB articles 316739 and 810371.
Utilising all Physical Memory: Physical Address Extension (PAE)
PAE support was added by Intel starting with the Pentium Pro family and later* and provides a memory-mapping model, which allows Windows to access to up to 64Gb of physical memory, rather than the standard 4Gb. In PAE mode, the memory management unit implements page directory entries (PDEs) and page table entries (PTEs) that are 36-bits wide (rather than the standard 32-bits) and adds a page directory pointer table to manage these high-capacity tables and indexes into them, allowing the operating system to recognise up to 64Gb.
In practice, this means that although Windows processes are still given a 4Gb allocation of virtual memory (virtual memory is still allocated using 32-bit pointers, limiting their maximum size to 4Gb), multiple processes can immediately benefit from the increased RAM as they are less likely to encounter physical memory restrictions and begin paging.
A specific version of the Windows kernel is required to use PAE, either Ntkrnlpa.exe for uniprocessor systems, or Ntkrpamp.exe for multiprocessor systems, both are located in the \i386\Driver.cab file. No additional work needs to be undertaken by the system administrator, apart from adding the /PAE switch in a similar fashion to the /3GB or /USERVA switches. If however you are running hardware that supports hot-adding memory , the PAE switch will be added by default (hot-add memory is only supported by Windows Server 2003 Enterprise and Datacenter editions). Note: 64-bit versions of Windows do not support PAE. The PAE switch can be added with or without the /3GB switch, as detailed later.
To manually add the PAE switch add the following to your boot.ini file:

[operating systems] multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /noexecute=optout /fastdetect /PAE

The following table details the maximum physical memory that a Windows version can recognise, with the PAE switch enabled:

Windows 2000 Server – 4Gb Maximum
Windows 2000 Advanced Server – 8Gb RAM Maximum
Windows 2000 Datacenter Server – 32Gb RAM Maximum

Windows Server 2003 Web Edition – 2Gb RAM Maximum
Windows Server 2003 Standard Edition – 4Gb RAM Maximum
Windows Server 2003 Enterprise Edition – 32Gb RAM Maximum
Windows Server 2003 Datacenter Edition – 64Gb RAM Maximum

Windows Server 2008 Web Edition – 4Gb RAM Maximum
Windows Server 2008 Standard Edition – 4Gb RAM Maximum
Windows Server 2008 Enterprise Edition – 64Gb RAM Maximum
Windows Server 2008 Datacenter Edition – 64Gb RAM Maximum
Windows Server 2008 Datacenter Edition (Server Core) – 64Gb RAM Maximum

Unless you have a system with more than 4Gb of physical memory, there is little point in enabling PAE; however, PAE can be enabled on Windows XP SP2, Windows Server 2003 and later 32-bit versions of Windows, to support hardware enforced Data Execution Prevention (DEP).
I’ve provided only a brief overview of the Physical Address Extensions here; for more background reading please see the following: Microsoft KB articles 283037 and 268363, Windows Hardware Developer Central article Physical Address Extension – PAE Memory and Windows.
* the PAE extension is supported on AMD chipsets, although I can’t find any hard evidence on the AMD website.

Addressing Windows Extensions (AWE)
Unlike the PAE switch, the AWE facility in Windows exists to allow applications – such as SQL Server – to access more than 4GB of physical memory. AWE removes the 4Gb physical memory limit of 32-bit software architecture by enabling code to allocate large chunks of physical memory and then, map access to the physical memory into a window of virtual memory that is 32-bit addressable. Because AWE allows the OS to allocate memory above the 4Gb boundary, there is little point enabling it on a system with 4Gb or less of physical RAM.
One thing to note with AWE memory is that it is never swapped to the system paging file (i.e. disk). If you review the AWE API, you’ll see that the methods refer to physical memory allocation: AWE memory is physical memory that is never swapped to or from the system paging file. This explains why that in order to use the ‘Use AWE to Allocate Memory’ flag in SQL Server, requires the ‘Lock Pages in Memory’ Local Security Policy setting (see Using Address Windowing Extensions (AWE) with SQL Server) – pages can only be locked in memory if this local security policy is set. This also explains why (or how) applications such as SQL Server, Exchange etc. when using AWE to allocate memory can consume such great amounts of physical RAM.

Best Practice Configurations
Based on the information provided above, Microsoft recommend the following physical memory / operating system memory switch combinations:

4Gb Physical RAM – /3GB switch (or /USERVA switch)
> 4Gb Physical RAM – /3GB and /PAE switch
> 16Gb Physical RAM – /PAE switch

A Final Note about the /3GB Switch

You will notice that in the table above recommends that a server with greater than 16Gb of physical RAM should not be configured with the /3GB switch. When you apply the /3GB switch, you limit the size of the virtual memory address space available to the kernel to 1Gb (from the usual 2Gb), which is too small for the virtual memory manager to store the memory mapping tables needed to access more than 16Gb of RAM. As a result, the memory manager imposes a virtual memory limit of 16 GB on a system with both the /3GB and /PAE enabled. Even if a system has 32 GB or more of physical memory, if both options are enabled, only 16 GB of memory will be recognised.
However, although 16Gb is a hard upper limit imposed by the kernel, most workloads will actually show decreased throughput on systems with 12Gb of memory, and many on systems/workloads with as low as 8GB of memory. Therefore, ensure you thoroughly test the use of the /3GB switch within you UAT/reference environment before applying it to live systems.
I’m going to close Part I here because there is plenty for the reader to take onboard before we start to look at how these considerations affect SQL Server. Part II will be

SQL SERVER Architecture – Terminology

2013-09-20T22:05:00.000+05:30

The major components of SQL Server are:

Relational Engine
Storage Engine
SQL OS

Now we will discuss and understand each one of them.

1) Relational Engine:

Also called as the query processor, Relational Engine includes the components of SQL Server that determine what your query exactly needs to do and the best way to do it. It manages the execution of queries as it requests data from the storage engine and processes the results returned.

Different Tasks of Relational Engine:

Query Processing
Memory Management
Thread and Task Management
Buffer Management
Distributed Query Processing

2) Storage Engine:

Storage Engine is responsible for storage and retrieval of the data on to the storage system (Disk, SAN etc.). to understand more, let’s focus on the concepts.

When we talk about any database in SQL server, there are 2 types of files that are created at the disk level – Data file and Log file. Data file physically stores the data in data pages. Log files that are also known as write ahead logs, are used for storing transactions performed on the database.

Let’s understand data file and log file in more details:

Data File: Data File stores data in the form of Data Page (8KB) and these data pages are logically organized in extents.

Extents: Extents are logical units in the database. They are a combination of 8 data pages i.e. 64 KB forms an extent. Extents can be of two types, Mixed and Uniform. Mixed extents hold different types of pages like index, system, data etc (multiple objects). On the other hand, Uniform extents are dedicated to only one type (object).

Pages: As we should know what type of data pages can be stored in SQL Server, below mentioned are some of them:

Data Page: It holds the data entered by the user but not the data which is of type text, ntext, nvarchar(max), varchar(max), varbinary(max), image and xml data.
Index: It stores the index entries.
Text/Image: It stores LOB ( Large Object data) like text, ntext, varchar(max), nvarchar(max), varbinary(max), image and xml data.
GAM & SGAM (Global Allocation Map & Shared Global Allocation Map): They are used for saving information related to the allocation of extents.
PFS (Page Free Space): Information related to page allocation and unused space available on pages.
IAM (Index Allocation Map): Information pertaining to extents that are used by a table or index per allocation unit.
BCM (Bulk Changed Map): Keeps information about the extents changed in a Bulk Operation.
DCM (Differential Change Map): This is the information of extents that have modified since the last BACKUP DATABASE statement as per allocation unit.

Log File: It also known as write ahead log. It stores modification to the database (DML and DDL).

Sufficient information is logged to be able to:
- Roll back transactions if requested
- Recover the database in case of failure
- Write Ahead Logging is used to create log entries
  - Transaction logs are written in chronological order in a circular way
  - Truncation policy for logs is based on the recovery model

SQL OS:

This lies between the host machine (Windows OS) and SQL Server. All the activities performed on database engine are taken care of by SQL OS. It is a highly configurable operating system with powerful API (application programming interface), enabling automatic locality and advanced parallelism. SQL OS provides various operating system services, such as memory management deals with buffer pool, log buffer and deadlock detection using the blocking and locking structure. Other services include exception handling, hosting for external components like Common Language Runtime, CLR etc.

SQL Server 2014: A Closer Look

2013-09-20T21:56:00.000+05:30

Microsoft SQL Server 2014 was announced by Quentin Clark during the Microsoft TechEd 2013 keynote. Designed and developed with our cloud-first principles in mind, SQL Server 2014 builds on the momentum of SQL Server 2012, released just 14 months ago. We are excited to share a closer look at some of the exciting capabilities included in SQL Server 2014 that will help you unlock real-time insights with mission critical and cloud performance.
SQL Server 2014 helps organizations by delivering:

Mission Critical Performance across all database workloads with In-Memory for online transaction processing (OLTP), data warehousing and business intelligence built-in as well as greater scale and availability
Platform for Hybrid Cloud enabling organizations to more easily build, deploy and manage database solutions that span on-premises and cloud
Faster Insights from Any Data with a complete BI solution using familiar tools like Excel

Mission Critical Performance with SQL Server 2014
SQL Server 2014 delivers new in-memory capabilities built into the core database for OLTP and data warehousing, which complement existing in-memory data warehousing and business intelligence capabilities for a comprehensive in-memory database solution. In addition to in-memory, there are new capabilities to improve the performance and scalability for your mission critical applications.
In Memory Built-In

New In-Memory OLTP – built in to core SQL Server database and uniquely flexible to work with traditional SQL Server tables allowing you to improve performance of your database applications without having to refresh your existing hardware. We are seeing customers such as EdgeNet and bwin achieve significant performance gains to scale and accelerate their business.
Enhanced In-Memory ColumnStore for Data Warehousing – now updatable with even faster query speeds and with greater data compression for more real-time analytics support.
New buffer pool extension support to non-volatile memory such as solid state drives (SSDs) – Increase performance by extending SQL Server in-memory buffer pool to SSDs for faster paging.
New Enhanced Query Processing – speeds all SQL Server queries regardless of workload.

Enhanced Availability, Security and Scalability

Enhanced AlwaysOn – Built upon the significant capabilities introduced with SQL Server 2012, delivers mission critical availability with up to 8 readable secondaries and no downtime during online indexing operations.
Greater scalability of compute, networking and storage with Windows Server 2012 R2 –

- Increased scale – Continue to benefit from scale for up to 640 logical processors and 4TB of memory in a physical environment and up to 64 virtual processors and 1TB of memory per VM.
- Network Virtualization – Abstracts networking layer so that it you can easily migrate SQL Server from one datacenter to another.
- Storage Virtualization with Storage Spaces – Create pools of storage and storage tiers allowing your hot data to access the premium storage and cold data to access standard storage improving resilience, performance and predictability.

Enhanced Resource Governance – With Resource Governor, SQL Server today helps you with scalability and predictable performance, and in SQL Server 2014, new capabilities allow you to manage IO, in addition to compute and memory to provide more predictable performance.
Enhanced Separation of Duties – Achieve greater compliance with new capabilities for creating role and sub-roles. For example, a database administrator can now manage the data without seeing sensitive data or personally identifiable information.

Platform for Hybrid Cloud
SQL Server 2014 creates a strong platform for hybrid cloud where cloud scale can be leveraged to extend the scalability and availability of on-premises database applications as well as reduce costs.
Simplified Cloud Backup and Disaster Recovery

Backup to Azure Storage – reduce costs and achieve greater data protection by backing up your on-premises database to Azure Storage at an instance level. Optimize backup policy with intelligence built in to SQL Server that monitors and tracks backup usage patterns to provide optimal cloud backup. Backups can be automatic or manual, and in case of an on-premises failure, a backup can be restored to a Windows Azure Virtual Machine.
AlwaysOn integration with Windows Azure Infrastructure Services – Benefit from Microsoft’s global data centers by deploying a Windows Azure Virtual Machine as an AlwaysOn secondary for cost-effective global data protection. Increase performance and scale reporting for your global business units by running reporting off the readable secondaries in Windows Azure. Run backups on the secondaries in Windows Azure to increase data protection and performance.
SSMS Wizard for deploying AlwaysOn secondaries in Window Azure – Easily deploy an AlwaysOn secondaries to Windows Azure Virtual Machine with a point and click experience within SQL Server Management Studio (SSMS).

Easy Migration of On-Premises SQL Servers to Windows Azure Virtual Machines

SSMS Migration Wizard for Windows Azure Infrastructure Services – Easily migrate an on-premises SQL Server database to a Windows Azure Virtual machine with a point and click experience in SSMS. The newly deployed database application can be managed through SSMS or System Center 2012 R2.

Faster Insights on Any Data
SQL Server 2014 is at the heart of our modern data platform which delivers a comprehensive BI solution that simplifies access to all data types big and small with additional solutions like HDInsight, Microsoft’s 100% Apache compatible Hadoop distribution and project code name “Data Explorer”, which simplifies access to internal or external data. New data platform capabilities like Polybase included in Microsoft Parallel Data Warehouse allows you to integrate queries across relational and non-relational data using your existing SQL Server skills.
With SQL Server 2014, you can accelerate insights with our new in-memory capabilities with faster performance across workloads. You can continue to refine and manage data using Data Quality Services and Analysis Services in SQL Server and finally analyze the data and unlock insights with powerful BI tools built into Excel and SharePoint.

What is the difference between Checkpoint and Lazy Writer

2013-09-20T21:54:00.000+05:30

Row	Checkpoint	Lazy writer
1	Checkpoint is used by sql engine to keep database recovery time in check	Lazy writer is used by SQL engine only to make sure there is enough memory left in sql buffer pool to accommodate new pages
2	Check point always mark entry in T-log before it executes either sql engine or manually	Lazy writer doesn’t mark any entry in T-log
3	To check occurrence of checkpoint , we can use below query *select from ::fn_dblog(null,null) WHERE [Operation] like ‘%CKPT’**	To check occurrence of lazy writer we can use performance monitor SQL Server Buffer Manager Lazy writes/sec
4	Checkpoint only check if page is dirty or not	Lazy writer clears any page from memory when it satisfies all of 3 conditions. 1. Memory is required by any object and available memory is full 2. Cost factor of page is zero 3. Page is not currently reference by any connection
5	Checkpoint is affected by two parameters 1. Checkpoint duration: is how long the checkpoint can run for. 2. Recovery interval: affects how often it runs.	Lazy writer is affected by 1. Memory pressure 2. Reference counter of page in memory
6	Check point should not be very low , it can cause increasing recovery time of database	No. of times lazy writer is executing per second should always be low else it will show memory pressure
7	Checkpoint will run as per defined frequency	No memory pressure, no lazy writer
8	Checkpoint tries to write as many pages as fast as possible	Lazy writer tries to write as few as necessary
9	checkpoint process does not put the buffer page back on the free list	Lazy writer scans the buffer cache and reclaim unused pages and put it n free list
10	We can find last run entry of checkpoint in Boot page	Lazy writer doesn’t update boot page
11	Checkpoint can be executed by user manually or by SQL engine	Lazy writer cant be controlled by user
12	It keeps no. of dirty pages in memory to minimum	It helps to reduce paging
13	Auto frequency can be controlled using recovery interval in sp_configure	Works only @ memory pressure , It uses clock algorithm for cleaning buffer cache
14	It will be automatically executed before every sql statement which requires consistent view of database to perform task like (Alter, backup, checkdb, snapshot …..)	It kicks pages out of memory when reference counter of page reaches to zero
15	Command : Checkpoint	No command available
16	It comes in picture to find min lsn whenever t-log truncates	No entry in T-log
17	Checkpoint is affected by Database recovery model	Lazy writer doesn’t get impacted with recovery model of database
18	To get checkpoint entry in error log DBCC TRACEON(3502, -1)	Not Applied
19	Members of the SYSADMIN, DB_OWNER and DB_BACKUPOPERATOR can execute checkpoint maually	Not Applied

How do I fix "Cannot connect to WMI Provider" error?

2013-08-07T20:38:00.002+05:30

The following article explains how to fix the following error message:

Cannot connect to WMI provider. You do not have permission or the server is unreachable.
Note that you can only manage SQL Server 2005 servers with SQL Server Configuration Manager.
Invalid Namespace.

This error is caused when the .mof files are damaged during the MS SQL Server 2005 installation. The error will typically occur when trying to run the MS SQL 2005 Configuration Manager. This error can be resolved by running a command line script.

To fix this error, please follow these steps:

Log into your server through Remote Desktop Connection (instructions for connecting to your server through RDC can be found here).
From the Start menu, select Run.
Type cmd and click OK.
Change to the directory c:\Program Files\Microsoft SQL Server\90\Shared\
Type mofcomp "C:\Program Files\Microsoft SQL Server\90\Shared\sqlmgmproviderxpsp2up.mof" and press enter.

Refer MS KB article: http://support.microsoft.com/kb/956013

You should no longer receive the above error message.

Configure SSIS to use Named Instances

2013-06-07T19:14:00.004+05:30

SQL Server family has various services like Database Services (SQL Server), Analysis Services, Reporting Services which supports Named Instance of the services but there are few others like Integration Services which does support named instance. This means, we can have one and only one service of Integration Services on each server.

By default, the MSDB folder of Integration Services points to MSDB of default instance of SQL Server. But we can change this to point any (one or more than one) named instance of SQL server.

To point MSDB folder of Integration Services to any other instance of SQL Server, we need to change the configuration file (MsDtsSrvr.ini.xml). Following are the steps to do that:

1.Browse the file MsDtsSrvr.ini.xml. Depending on Version of SQL Server installed, you can get this at C:\Microsoft SQL Server\90\DTS\ or C:\Microsoft SQL Server\100\DTS\

2.Create a backup copy of the file.

3.Open the configuration file in a text editor like BIDS, NotePad.

4.To configure a named instance instead of the default instance, change the existing ServerName entry to ServerName\InstanceName under the Folder node of type SQLServerFolder.

5.To configure a named instance in addition to the default instance, add a new Folder node of type SQLServerFolder under the TopLevelFolders node.

6.Save and close the configuration file.

7.Restart the SSIS service.

Important Scripts to find out Replication details.

2013-05-24T18:33:00.006+05:30

Script to run on Distribution database

This script returns completed setup replication information. Unless an orphan article exists, this will return a complete set of replication information. I also added the distribution agent job name to show how easy it is to pull in other configuration information.

USE Distribution
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Get the publication name based on article
SELECT DISTINCT
srv.srvname publication_server
, a.publisher_db
, p.publication publication_name
, a.article
, a.destination_object
, ss.srvname subscription_server
, s.subscriber_db
, da.name AS distribution_agent_job_name
FROM MSArticles a
JOIN MSpublications p ON a.publication_id = p.publication_id
JOIN MSsubscriptions s ON p.publication_id = s.publication_id
JOIN master..sysservers ss ON s.subscriber_id = ss.srvid
JOIN master..sysservers srv ON srv.srvid = p.publisher_id
JOIN MSdistribution_agents da ON da.publisher_id = p.publisher_id
AND da.subscriber_id = s.subscriber_id
ORDER BY 1,2,3

Script to run on Publisher database

This script returns what publications has been setup. This will go through all the published databases and return information if the database has replication enabled. Sometimes, I just want to see the publication name and subscriber server names (no articles) to see what servers are being used with replication other times I want all of the information, so I added a variable called @Detail and if you set @Detail = 'Y' it will return data with the article list. Any other value will only return the publisherDB, publisherName and SubscriberServerName.
-- Run from Publisher Database
-- Get information for all databases

DECLARE @Detail CHAR(1)
SET @Detail = 'Y'
CREATE TABLE #tmp_replcationInfo (
PublisherDB VARCHAR(128),
PublisherName VARCHAR(128),
TableName VARCHAR(128),
SubscriberServerName VARCHAR(128),
)
EXEC sp_msforeachdb
'use ?;
IF DATABASEPROPERTYEX ( db_name() , ''IsPublished'' ) = 1
insert into #tmp_replcationInfo
select
db_name() PublisherDB
, sp.name as PublisherName
, sa.name as TableName
, UPPER(srv.srvname) as SubscriberServerName
from dbo.syspublications sp
join dbo.sysarticles sa on sp.pubid = sa.pubid
join dbo.syssubscriptions s on sa.artid = s.artid
join master.dbo.sysservers srv on s.srvid = srv.srvid
'
IF @Detail = 'Y'
SELECT * FROM #tmp_replcationInfo
ELSE
SELECT DISTINCT
PublisherDB
,PublisherName
,SubscriberServerName
FROM #tmp_replcationInfo
DROP TABLE #tmp_replcationInfo

Script to run on Subscriber database

This script returns what article(s) is/are being replicated to the subscriber database. I also use this to find orphaned subscribers. This is rather simple since there is not much information to pull.

-- Run from Subscriber Database

SELECT distinct publisher, publisher_db, publication
FROM dbo.MSreplication_subscriptions
ORDER BY 1,2,3

The following scripts will provide the name of the database on which merge replication is setup

select namefromsys.databaseswhereis_merge_published= 1

Drop article from Existing Publication (Transactional Replication)

2013-05-24T18:27:00.001+05:30

EXEC sp_dropsubscription
@publication = 'Publication name',
@article = N'Table Name',
@subscriber = 'Subscriber Server Name';
GO

EXEC sp_droparticle
@publication = 'Publication name',
@article = N'Table Name',
@force_invalidate_snapshot = 0

Adding new article to Existing Publication (Transactional Replication)

2013-05-24T18:25:00.001+05:30

Adding new article to Existing Publication (Transactional Replication)

First of all run the Exec sp_helppublication in publication database and checked the following fields

1. Immediate_sync
2. Allow_anonymous

Both the fields were set to ON as they showed a value 1 which is enabled. If the Immediate_sync is enabled, every time you add a new article it will cause the entire snapshot to be applied and not the one for the particular article alone. Usually, the Immediate_sync publication property is set to true if we allowed anonymous subscriptions while creating the publication through the Create Publication wizard. To prevent the complete snapshot, run the script below.

Step 1:- Disable the two fields in publication database

EXEC sp_changepublication
@publication = ‘Publication name’,
@property = N'allow_anonymous',
@value = 'false'
GO

EXEC sp_changepublication
@publication = Publication name’,
@property = N'immediate_sync',
@value = 'false'
GO

Step 2:- Add new article in publication database

    exec sp_addarticle
    @publication = N'Publication name’,
    @article = N'Table Name',
    @source_object = N'Table Name',
        @destination_table = N'Table Name'
         GO

exec sp_addsubscription

          @publication = N'Publication name’,
          @subscriber = N'Subscriber Server Name',
          @destination_db = N'Destination Database Name',
          @subscription_type = N'Subcription Type' [Pull\Push]

Step 3:- Now start the snapshot agent in publisher, it worked perfectly. You can see that only the particular table added was replicated. So from now on to apply the snapshots of the entire articles you need to reinitialize the subscriptions since the Immediate_sync is set to off.

IBM’s entrant in the SQL-on-Hadoop

2013-05-10T19:43:00.001+05:30

IBM’s entrant in the SQL-on-Hadoop competition has been flying under the radar, but is available as a technology preview. Called Big SQL, it’s a big deal if IBM wants to be a major player in the Hadoop space.

IBM has a SQL-on-Hadoop product in the works called Big SQL. The company announced the technology preview version in March, and is offering up a cloud-based demo environment for a select group of early users.

As a refresher, the big difference between SQL on Hadoop and the Hadoop connectors that were popular a couple years ago is that SQL-on-Hadoop products query the data where it resides — in HDFS or HBase — rather than pulling it into a relational database environment to analyze it. We have been talking for months about the emergence of a large SQL-on-Hadoop market, but IBM’s name was conspicuously absent from that discussion. The company has Hadoop software called BigInsights and lots of SQL expertise, so it only made sense that IBM would get into the game at some point.

Details on Big SQL are still pretty sparse save for a few high-level blog posts and an instructional video (embedded below), but it looks to take the standard approach, as Cloudera is doing with Impala, of enabling access through traditional tools via JDBC and ODBC drivers.

Ultimately, I think the advent of big data will enable some new types of querying techniques quite a bit different than the SQL queries we’ve come to know and love over the past couple decades. But SQL is still the language du jour and might never go away, so there’s a lot of value to be had if people can put their SQL skills to work on data stored inside Hadoop or other environments, and if companies can work toward a nirvana where all the data is stored in a single place rather than across database environments.

That IBM got this message and got into the game isn’t surprising at all, but it is important. Lots of large companies buy IBM’s software. If it wants them to follow it into the world of big data and Hadoop, it has to give them the tools they need to use it.

NuoDB Starlings Release 1.1

2013-05-08T22:55:00.001+05:30

Today NuoDB announced the immediate availability of the NuoDB Starlings Release 1.1. The new release features .NET & Windows 64-bit support helping developers who wish to avoid the common scalability and performance issues associated with traditional databases. And that’s not all. We’ve addressed three areas of customer need.

The new release delivers user benefit in 3 key areas:
Microsoft 64-bit and .NET support

Substantial performance and productivity enhancements
User experience improvements.

What’s New in 1.1

Microsoft Enhancements

64 bit support for Windows Server, Windows 7 and 8 for high performance

Full support for Visual Studio 2012, LINQ and Entity Framework for a more integrated developer experience

.NET driver to avoid the common scalability and performance issues associated with Microsoft SQL Server

Azure compatibility for running/deploying NuoDB easily in the cloud.

Productivity & Performance Improvements

33% increase in scale-out performance

20-50% improvement for heavy workloads

Built-in schema browsing and querying for Integrated SQL development

Database demand simulation tools, including a sample eCommerce schema and configurable load scenarios for improved provisioning of both predictable and unpredictable web traffic.

Improved User Experience

New NuoDB DevCenter– a central, easy to use location for all NuoDB developer resources, now integrated with the NuoDB Console for faster access

SQL Explorer fully integrated into the NuoDB Console for dropping and creating web tables without ever leaving the browser.

Find All Databases name with Size

2013-05-08T16:47:00.001+05:30

SELECT d.name,
ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- Skip system databases
GROUP BY d.name
ORDER BY d.name

How Many Tables in a Database Having Column Name like ‘Name’.

2013-05-03T19:53:00.005+05:30

USE AdventureWorks2008R2
GO

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%name%'
ORDER BY schema_name, table_name;

Select columns with NULL values only

2013-05-03T19:46:00.000+05:30

declare @col varchar(255), @cmd varchar(max)
DECLARE getinfo cursor for
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = 'TABLE_1'
OPEN getinfo
FETCH NEXT FROM getinfo into @col
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM TABLE_1 WHERE [' + @col + '] IS NOT NULL) BEGIN print ''' + @col + ''' end'
    EXEC(@cmd)
    FETCH NEXT FROM getinfo into @col
END
CLOSE getinfo
DEALLOCATE getinfo

Memory - Performance Counters

2013-05-02T17:28:00.003+05:30

SQL Server provides numerous counters for monitoring and evaluating performance of your system. Although there are no hard and fast rules for performance monitoring, you should audit your system periodically to ensure that it lives up to user expectations. The first step in performance monitoring efforts should be collecting baseline information. A baseline has a collection of performance counter values while the system isn't heavily utilized and performs well. Next you should take benchmarks at various times of day and during different days of week to figure out acceptable ranges for the performance counters you intend to monitor. For example, your tests could reveal that buffer cache hit ratio values between 93.5% and higher are acceptable, but any value below 93.5% indicates sub-optimal performance.

Then setup continuous monitoring and define alerts which notify you if any performance counter values fall below (or rise above) a predefined threshold. Although we try to provide some guidelines for performance counter values adequate range for each counter will vary from one environment to next; this is why it's important to create a baseline and determine acceptable values for each counter.

You should watch several memory related counters very closely as they present a clear indication of acceptable or sub-optimal performance. Memory bottleneck typically leads to excessive paging. You can reduce or eliminate memory related performance issues by:

Adding memory to the server computer.
Altering SQL Server and operating system configuration.
Eliminating unnecessary processes on the database server.
Directing some user activity from the current server to another server.

A large majority of these memory performance counters are available through Memory Manager, Plan Cache and Buffer Manager objects.

SQL Server Buffer Manager provides the following counters:

AWE lookup maps / sec

Shows the number of times per second data pages requested by SQL Server were found in the buffer pool and then mapped as AWE memory and made part of server's virtual address space.

AWE stolen maps / sec

Shows the number of times data pages were taken from the free list in buffer pool and mapped as AWE memory.

AWE unmap calls / sec

When a buffer is unmapped it is excluded from virtual address space; buffers are unmapped because they haven't been used and to make room for new buffers. This counter shows the number of times buffers were un-mapped per second.

AWE unmap pages / sec

Shows the number of data pages unmapped per second.

AWE write maps / sec

Shows the number of times per second that it is necessary to map in a dirty buffer so it can be written to disk. This means that no free buffers are available so SQL Server has to write to a "dirty" buffer, meaning a buffer that contains data changes. If the value is high, then the server could use additional memory.

Note: AWE related counters are only available if AWE enabled option is turned on.

Buffer cache hit ratio

Percentage of pages that were found in the buffer pool without having to incur a read from disk. The ratio is calculated as total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses. Since reading from the cache is much less expensive than reading from disk, servers that have a high buffer cache hit ratio perform well. For optimal performance this ratio should remain above 99%. However, in most environments a number above 95% is acceptable. If you use AWE enabled option you should never see this counter drop below 99% unless the total amount of RAM is not sufficient for your application's load.

Checkpoint pages per second

Shows how many pages are flushed per second by the CHECKPOINT process. If this value is too high, then you might want to increase the recovery interval or dedicate more RAM to SQL Server. By increasing the value of recovery interval, you advise SQL Server to checkpoint the databases less frequently thereby reserving more resources for user applications. The flip side of the coin, however, is that databases will take longer to recover in case of server failure (recovery process entails rolling back failed transactions and rolling forward the successful transactions).

Database pages

Number of pages in the buffer pool with database content. The higher the value of this counter, the more queries are sent to your server. If the number keeps increasing considerably above your baseline, consider allowing more memory for SQL Server usage by modifying the value of "max server memory" setting.

Free list stalls / sec

Number of times a request for a "free" page had to wait for one to become available. High value indicates that the server could use additional memory.

Lazy writes / sec

How many times per second lazy writer has to flush dirty pages out of the buffer cache instead of waiting on a checkpoint. Typically dirty pages are flushed out of cache by the checkpoint process. If the value of this counter is higher than 20, then server could use additional RAM.

Look at Lazy Writes/sec which tells you that number of times the buffer pool flushed dirty pages to disk outside of the CHECKPOINT process. This should be near zero.

Page life expectancy

Shows on average how long each data page is staying in buffer cache before being flushed out to make room for other pages. If value is less than 300 seconds than additional memory could be helpful.

Page lookups / sec

Number of requests per second to find the page in the buffer pool.

Page reads / sec

Shows the number of data pages read per second.

Page writes / sec

Shows the number of data pages written per second.

Readahead pages / sec

Shows the number of data pages read per second in anticipation of their use.

Procedure cache pages

Number of pages used to store compiled queries; indicates the total number of stored procedures in cache.

Reserved pages

Shows the number of buffer pool reserved pages.

Stolen pages

Number of pages acquired from buffer cache and used for miscellaneous server purposes (including procedure cache).

Target pages

Shows the ideal number of pages in buffer pool.

Total pages

Total number of pages in buffer pool (including data pages, free and stolen pages).

Another group of important memory related counters are exposed through Cache Manager object in SQL Server 2000 and Plan Cache object with SQL Server 2005. These objects allow you to monitor the memory usage for storing ad-hoc Transact-SQL statements, stored procedures and triggers.

Cache Hit Ratio

Ratio between cache hits and lookups. The value of this counter should never fall below 90%.

Cache Object Counts

Shows the number of objects in cache.

Cache Pages

Number of 8KB pages in cache.

Cache Objects in use

Shows the number of objects in cache that are currently being used.

Each counter has the following instances:

_total - information on all types of instances.
SQL Plans - query plans for ad-hoc Transact-SQL statements, or Transact-SQL statements prepared using sp_prepare or sp_cursorprepare system procedures. The execution plans are cached for reuse by identical statements executed at a later time. With SQL Server 2000 this instance is called "ad hoc SQL Plans".
Object Plans - query plans for stored procedures, user-defined functions and triggers. With SQL Server 2000 this instance is further broken down into procedure plans, replication procedure plans and trigger plans.
Bound Trees - normalized trees for views, rules, computed columns and check constraints. With SQL Server 2000 this instance is called "misc. Normalized Trees".
Extended stored procedures - if you use extended procedures this instance will show the number of these objects referenced in cache.
Temporary tables & table variables - information for temp tables and table variables referenced in cache.

SQL Server Memory Manager object exposes the following counters:

Connection Memory

Total amount of dynamic memory the server is using for maintaining connections

Granted Workspace Memory

Total amount of memory currently granted to executing processes such as hash, sort, bulk copy, and index creation operations.

Lock Blocks

A lock block is a single locked object - a table, row, page, etc. This counter shows the total number of currently used lock blocks.

Lock Blocks Allocated

Current number of allocated lock blocks. By default SQL Server allocates locks dynamically as needed. However, you can override the default behavior by modifying locks configuration parameter.

Memory Grants Outstanding

Number of processes that have successfully acquired workspace memory grant. High value of this counter indicates peak user activity.

Memory Grants Pending

Number of processes waiting on a workspace memory grant. If this counter has a high value SQL Server could benefit from additional memory. You should first examine the database design, queries and indexes however to ensure the system is properly tuned before purchasing additional RAM.

Optimizer Memory

Memory reserved for query optimization. Ideally, the value of this counter should remain relatively static. If this isn't the case you might be using dynamic SQL execution excessively.

SQL Cache Memory

Total memory reserved for dynamic SQL statements.

Target Server Memory

Total amount of memory in KB that the server can consume.

Total Server Memory

The amount of committed memory in KB from the buffer pool. If this counter has a high values, as compared to the total amount of RAM available on server then SQL Server can benefit from additional memory.

The Target Server Memory counter shows SQL Server estimate of how much RAM it would need to operate effectively. The total memory counter shows what is actually used. The goal is to keep total memory less than target memory. If over time total memory number is higher than target your server could benefit from additional RAM.

In addition to examining SQL Server specific counters related to memory, you should also inspect the value of the following operating system level counters:

Total Server Memory (KB) counter < Target Server Memory (KB) counter=That means SQL Server has enough memory to run efficiently

Total Server Memory (KB) counter is >= Target Server Memory (KB) counter = This indicates that SQL Server may be under memory pressure and could use access to more physical memory.

Page faults / sec

Shows the average number of pages faulted per second. This counter includes both hard faults (those that require disk access) and soft faults (where the faulted page is found elsewhere in physical memory.) Most processors can handle large numbers of soft faults without significant consequence. However, hard faults, which require disk access, can cause significant delays. You need to ensure this value stays as low as possible.

Pages / sec

Shows the rate at which pages are read from or written to disk to resolve hard page faults. This counter is a primary indicator of the kinds of faults that cause system-wide delays. Although some spikes are normal the value of this counter should remain close to zero.

Look at Free Pages/sec and Free List Stalls/sec. You don't want to see Free Pages bottom out which will result in a Free List Stall while the buffer pool has to free pages for usage.

Available Bytes

The amount of physical memory, in bytes, immediately available for allocation to a process or for system use. Your system should maintain at least 50MB of available memory. If the value falls below this threshold then the server is short on memory or the application (SQL Server) isn't releasing the memory back to the operating system as it should.

Process: Working Set

Shows the current size of the Working Set of the process in bytes. The Working Set is the set of memory pages touched recently by the threads in the SQL Server process. If the value of this counter is consistently below the amount configured by min server memory option then SQL Server is configured to have more memory than it really needs.

Cloudera’s CEO sizes up the SQL-on-Hadoop market

2013-04-30T23:00:00.002+05:30

Cloudera’s Impala engine for interactive SQL queries on Hadoop data is now generally available, and CEO Mike Olson gives his lay of the competitive landscape.

There is no shortage of confidence in the Hadoop space, and market leader Cloudera bolstered its own on Tuesday with the general availability of its Impala SQL query engine for Hadoop. And if CEO Mike Olson’s comments are any indication, we’re in for a long ride of competitive jockeying and oneupmanship as Cloudera and its peers go all Microsoft or Google and create myriad new data-processing engines to turn their Hadoop distributions into bona fide platforms.
Launched as a private beta in May 2012 and made public in October, Impala is Cloudera’s attempt to address the growing demand for interactive SQL analytics on Hadoop data. It’s essentially a massively parallel database designed to share the same storage platform and metadata as Hadoop MapReduce, only it is its own separate processing engine.

Impala actually uses the same “nearly ANSI” version of SQL as does current standard bearer Hive, but that technology (created by Facebook in 2009 as a data warehouse layer for Hadoop) doesn’t run nearly fast enough to sate many users’ desire for interactive analytics. This is because Hive transforms SQL queries into MapReduce jobs, meaning every one is processed against the entire corpus of data in the Hadoop Distributed File System.

Sizing up the competition

Only Cloudera isn’t the first to have the idea, nor is it alone in trying to sell interactive SQL on Hadoop. The idea was first commercialized by Boston-based startup Hadapt in 2011, and is now being pushed by numerous startups and larger Hadoop players. Among them: Pivotal (formerly EMC) Greenplum, MapR (with Drill), Hortonworks (with Stinger), Drawn to Scale, Splice Machine, Jethro Data and Citus Data.

But Cloudera is arguably the biggest name pushing SQL on Hadoop, and CEO Mike Olson thinks Impala stands out for several reasons — not the least of which is that it exists as a product. “Nobody else is shipping production-grade SQL query support on Hadoop,” he told me during a recent call. “At least not in open source.” He seems content to let the startups do their things, instead focusing his attention on Cloudera’s big three Hadoop-distribution competitors in Pivotal, MapR and Hortonworks. Greenplum and Pivotal SVP Scott Yara was full of confidence — and R&D budget– when the company announced the Pivotal HD distribution and HAWQ technology in February, but Olson claims the approach requires a siloed DBMS within HDFS and is a “rearguard defensive strategy” to protect the company’s sunk costs in its database technology.

As for Hortonworks, Olson questions the wisdom of its Stinger initiative to boost Hive’s speed, noting that “Hive never got good while it was running standalone on MapReduce.” Hortonworks also partners with vendors such as Teradata to let their platforms access Hadoop data in its native format, but those approaches still require sending data over the network. “It’s not the way you would build it if you woke up in the 2000s and were building this anew,” Olson said.

Olson acknowledged that the MapR-led Apache Drill project is cut from the same cloth as Impala (that is, being a Google Dremel clone designed specifically for Hadoop), but “the difference is we’re shipping code.” Being generally available and ready for production workloads means Cloudera can lock down users and market share before many even have a chance to experiment with Drill. He all but dismissed questions over the readiness of Impala, spurred by rumblings in the Hadoop space that Cloudera rushed it into public beta in order to get on the scoreboard against more fully baked offerings.
“I don’t feel we’re under the gun competitively to pull it out of beta because no one else has product in the market,” Olson said. “I have no problems … calling this GA quality.” He did, however, acknowledge that Impala is shipping with a “minium viable feature set” that the company has plans to build on in the near future. Impala Senior Product Manager Justin Erickson noted a few issues of concern, including around the number of concurrent users Impala can support, but said they have been addressed during the beta period.

One piece of a larger platform

Really, though, the whole point of Impala and its competitors is to turn Hadoop from a tool for batch analytics and mass storage into a platform that can handle nearly all of companies’ data-processing needs. In that regard, it appears we’re just getting started. Cloudera, MapR, Pivotal Greenplum and Hortonworks are already pushing their own products and projects, and Olson said “it’s absolutely our intent” to enhance Cloudera’s platform with even more open-source products — perhaps even more database technologies a la HBase — that will let users do more stuff with more types of data. Over time, this strategy could result in Hadoop displacing the current breed of databases and data warehouses and becoming the single data store atop of which users run whatever applications they so desire. For now, though, especially when it comes to Impala and the data warehouse incumbents, Olson is taking a measured approach. “The likelihood that we’re going to knock them off in the near term,” he said, “… it would be a tough fight to win.”

TSQL command to check if SQL Agent services is started or down.

2013-04-30T18:06:00.000+05:30

SET
NOCOUNT ON

DECLARE
@ISCOUNT varchar(100)

DECLARE
@ISCOUNT2 integer

BEGIN

SELECT @ISCOUNT2 = count(1)

FROM master.sys.sysprocesses

WHERE program_name like N'%SQLAgent%'

IF @ISCOUNT2 < 0

BEGIN

SET @ISCOUNT = convert(varchar(100),'FAILURE: SQL Server Agent service is NOT started!')

END

ELSE

BEGIN

SET @ISCOUNT = convert(varchar(100),'SQL Server Agent service is started.')

END

PRINT @ISCOUNT

END

GO

Oracle Unveils SPARC Servers with the World’s Fastest Microprocessor

2013-04-29T21:32:00.002+05:30

New SPARC Servers Redefine the Economics of Enterprise Computing: Deliver Extreme Performance and Value for Database and Enterprise Applications, Trump the Competition on Multiple Business-Critical Workloads

Redwood Shores, Calif. – March 26, 2013

News Facts

Oracle today announced a complete refresh of their midrange and high-end SPARC server lines with new SPARC T5 and M5 servers running Oracle Solaris.

Oracle’s new SPARC T5 midrange servers are based on the SPARC T5, the world’s fastest microprocessor.

Businesses today need the computing power to exploit Big Data to maximum advantage, to use analytics to discover growth and opportunities, and turn social streams into market intelligence. Traditional systems simply don’t have the horsepower to handle those demanding tasks.

Oracle’s new SPARC T5 servers have set 17 world records(1) and are the world’s best platforms for enterprise computing at any scale, delivering the best value for database and enterprise applications.

Oracle’s SPARC T5-8 is the fastest single server for Oracle Database(2).

Oracle’s SPARC T5-8 is also the fastest single server for Oracle Middleware with a 12x cost performance advantage over the IBM Power 780(3).

Oracle’s SPARC T5-8 server has a 2.5x price performance advantage over the IBM P780 3-node cluster, when including hardware and software costs(4).

Oracle's SPARC T5-8 server has a 7x price advantage over a similar IBM Power 780 configuration for database on a server-to-server basis(5).

http://www.oracle.com/us/corporate/press/1923343

Get Top Queries Tiring SQL Server CPUs

2013-04-19T21:04:00.000+05:30

use tempdb
go
IF object_id('tempdb..##FindTopCPUQueries_set1') is not null DROP TABLE [dbo].[##FindTopCPUQueries_set1]
GO
declare @ServerTime datetime = getdate()
, @ConvertMiliSeconds bigint = 1000
, @FilterMoreThanMiliSeconds bigint = 1
, @FilterHours bigint = 2
, @execution_count bigint = 2
, @debugFlg bit = 0
if @debugFlg=1 select @ServerTime as ServerTime, @ConvertMiliSeconds as ConvertMiliSeconds
, @FilterMoreThanMiliSeconds as FilterMoreThanMiliSeconds, @FilterHours as FilterHours
, @execution_count as execution_count
select TOP 300
@@servername as servername,@ServerTime as runtime
,isnull(db_name(QueryText.dbid),'PreparedSQL') as DBName
,SUBSTRING(QueryText.text, (QueryStats.statement_start_offset/2)+1,
(isnull((
CASE QueryStats.statement_end_offset
WHEN -1 THEN DATALENGTH(QueryText.text)
WHEN 0 THEN DATALENGTH(QueryText.text)
ELSE QueryStats.statement_end_offset
END - QueryStats.statement_start_offset),0)/2)
+ 1) AS QueryExecuted
,total_worker_time AS total_worker_time
,QueryStats.execution_count as execution_count
,statement_start_offset,statement_end_offset
,(case when QueryText.dbid is null then OBJECT_NAME(QueryText.objectid) else OBJECT_NAME(QueryText.objectid, QueryText.dbid) end) as ObjectName
,query_hash
,plan_handle
,sql_handle
into ##FindTopCPUQueries_set1
from sys.dm_exec_query_stats as QueryStats
cross apply sys.dm_exec_sql_text(QueryStats.sql_handle) as QueryText
where QueryStats.query_hash IN
(
select QueryStatsBaseTable.query_hash
from sys.dm_exec_query_stats QueryStatsBaseTable
where last_execution_time > DATEADD(hh,-@FilterHours,GETDATE())
group by query_hash
having (sum(total_worker_time)/sum(execution_count))>@ConvertMiliSeconds and sum(execution_count)>@execution_count
)
ORDER BY total_worker_time/execution_count DESC;
if @debugFlg=1 select * from ##FindTopCPUQueries_set1 order by QueryExecuted
IF object_id('tempdb..##FindTopCPUQueries_set2') is not null DROP TABLE [dbo].[##FindTopCPUQueries_set2]
select
servername,runtime,max(DBName) as DBName,max(QueryExecuted) as QueryExecuted,(sum(total_worker_time)/sum(execution_count))/@ConvertMiliSeconds as AvgCPUTime
,sum(execution_count) as execution_count,query_hash, max(ObjectName) as ObjectName
into ##FindTopCPUQueries_set2
from ##FindTopCPUQueries_set1
group by query_hash,servername,runtime
order by AvgCPUTime desc
select * from ##FindTopCPUQueries_set2
--where QueryExecuted like 'select TOP 300%'
order by AvgCPUTime desc